Quanto tempo você poderia ganhar se todas as vezes que for migrar ou reinstalar um servidor ou ainda migrar logins e databases entre servidores você já tiver em mãos todos os scripts dos logins e senhas, attach dos databases, users de cada database, default database de cada login, jobs, devices de backup, linked servers, configuração original da instância entre outros?
Pois bem, neste artigo estarei descrevendo uma stored procedure que quando executada realiza as seguintes atividades:
1. Gera um script de attach para todos os databases existentes no servidor.
2. Gera um script com todos os logins e suas respectivas senhas.
3. Gera um script com todos os usuários e roles (grupos) existentes em cada database.
4. Gera um script com o default database de cada login.
5. Gera um script com todos os devices de backup (caso exista).
6. Gera um script com todos os jobs.
7. Gera um script com todos os linked servers.
8. Gera um script com as configuração globais do servidor (sp_configure).
9. Gera um script com as permissões de nível servidor (instância)
10. Cria um SQL job que executa a procedure principal de documentação diariamente as 05:00pm
A STORED PROCEDURE
Na verdade todo este trabalho é realizado por uma função e um conjunto de 10 stored procedures (usp_) onde cada uma delas possui uma função específica. A procedure usp_docservidor é a procedure principal e deve ser executada passando como parâmetro o caminho onde os scripts deverão ser armazenados. Por default o caminho é C:\temp\doc_servidor
Exemplo:
EXEC usp_docservidor ‘C:\temp\doc_servidor’
Nota: O script cria todas as procedures na base de dados master. No entanto, as mesmas podem ser criadas em qualquer base de sua preferência. Basta alterar o nome da base no início do script e dentro da procedure principal.
Ao chamar a procedure principal, a mesma chama as demais 9 procedures e a funcão, também passando como parâmetro o caminho onde os scripts deverão ser armazenados. Embora estas 9 stored procedures sejam chamadas pela procedure principal, elas também podem ser executadas individualmente como exemplificado abaixo:
Exemplo:
EXEC usp_doclogins ‘C:\temp\doc_servidor’
EXEC usp_docusers ‘C:\temp\doc_servidor’
O nome das procedures e a função de cada uma delas são descritas abaixo:
1. usp_doclogins: Esta procedure cria o arquivo Logins_DayOfWeeK_SERVERNAME.sql documentando todos os logins e suas respectivas senhas. Para levar estes logins para outro servidor, basta pegar o script e executá-lo no servidor desejado.
2. usp_docusers: Cria o arquivo Users_DayOfWeeK_SERVERNAME.sql documentando todos os usuários e roles (grupos) existentes em cada banco de dados. Para levar estes usuários e grupos para uma base em outro servidor, basta pegar o script e executá-lo na base do servidor desejado.
3. usp_docdbs: Cria o arquivo Databases_DayOfWeeK_SERVERNAME.sql que permitirá executar um attach dos databases existentes no servidor na order em que foram criados, ou seja, mantendo seus dbids. A procedure também cria um script (arquivo Defaultdb_DayOfWeeK_SERVERNAME.sql) que permite reassociar os logins a seus respectivos default databases.
Vale ressaltar aqui que o script do arquivo permite executar um attach dos databases, porém os arquivos .mdf e .ldf dos databases devem estar em suas localizações originais. Ou sejam caso tenha copiado os arquivos .mdf e .ldf de um database para outro servidor, altere o script de forma a apontar para o caminho correto (letras do disco onde os arquivos foram copiados) do novo servidor.
4. usp_docdevices: Embora a utilização de devices de backup já não é mais comum nos dias de hoje este script cria o arquivo DevicesBackup_DayOfWeeK_SERVERNAME.sql que permitirá recriar os devices de backup existentes no servidor. Para recriar estes devices em outro servidor, basta pegar o script e executá-lo no servidor desejado. Neste caso é preciso ficar atendo ao caminho (letra do disco) onde os devices serão recriados.
5. usp_docjobs: Cria o arquivo Jobs_DayOfWeeK_SERVERNAME.sql e tem como função gerar um script que permitirá recriar todos os jobs existentes no servidor. Para recriar estes jobs em outro servidor, basta pegar o script e executar no servidor desejado. Neste caso, lembre-se de alterar o valor da variável @server_name para o nome do servidor de destino.
6. usp_doclinkedsrv: Cria o arquivo LinkedServers_DayOfWeeK_SERVERNAME.sql e tem como função gerar um script que permitirá recriar todos os linked servers existentes no servidor. Para recriar estes linked servers em outro servidor, basta pegar o script e executar no servidor desejado.
7. usp_docconfig: Cria o arquivo sp_configure_DayOfWeeK_SERVERNAME.sql gerando o script que permitirá reconfigurar uma nova instância de acordo com os últimos valores em execução (sp_configure).
8. usp_docserverperm: Cria o arquivo ServerPermissions_DayOfWeeK_SERVERNAME.sql gerando um script com todas as permissões no nível servidor/instância caso exista alguma, exemplo VIEW ANY DEFINITION. Assim fica fácil reconfigurar estas permissões em casos de reinstalação de uma instância ou mesmo migrações.
9. sp_hexadecimal: Esta procedure é utilizada pela procedure usp_doclogins para recuperar a senha dos logins.
Além das 9 stored procedures citadas também é criada a função fn_substchar, que é usada pela procedure usp_docjobs, um job e a própria procedure principal usp_docservidor.
Bom, como se pôde notar, estes scripts simplificam a realização de várias atividades, entre elas:
a) Migrar ou reinstalar um servidor.
b) Migrar logins entre servidores ou simplesmente recriá-los no mesmo servidor.
c) Transferir databases e logins entre servidores.
d) Recriar os jobs, devices de backup e linked servers ou transferí-los para outro servidor.
e) Reassociar logins a seus default databases.
f) Recriar os usuários dos databases.
g) Reconfigurar as configurações globais de um servidor/instância.
h) Recuperar possíveis permissões de nível servidor, que muitas vezes acabam sendo esquecidas em uma migração ou reinstalação 🙂
ARQUIVOS DE SAÍDA
Após a execução da procedure principal usp_docservidor você notará a criação de vários arquivos no caminho passado como referência. Os arquivos são criados no formato *.err e *_<dia_da_semana>_<servername>.sql, onde <dia_da_semana> é o dia da semana (em inglês) em que o arquivo foi criado e <servername> é o nome da instância SQL (@@servername). Isso lhe permitirá ter armazenado os scripts criados durante toda a semana. O arquivo de erro (*.err) é substituído a cada vez que as procedures são executadas.
Exemplo:
Databases.err
Databases_Thursday_SQLAG1.sql
Databases_Friday_SQLAG1.sql
Nota: Vale lembrar que se a documentação for realizada mais de uma vez ao dia, os arquivos serão substituídos refletindo sempre a última execução.
Os arquivos gerados durante o processo de documentação do servidor são os seguintes:
— Arquivos gerados pela procedure usp_doclogins
Logins.err ==> contém erros encontrados durante a documentação dos Logins.
Logins_<dia_da_semana>_<servername>.sql ==> contém o script que permitirá recriar ou migrar os Logins.
— Arquivos gerados pela procedure usp_docusers
Users.err ==> contém erros encontrados durante a documentação dos usuários dos databases.
Users_<dia_da_semana>_<servername>.sql ==> contém o script que permitirá recriar os usuários e grupos existentes em cada database.
— Arquivos gerados pela procedure usp_docdbs
Databases.err ==> contém erros encontrados durante a documentação dos databases.
Databases_<dia_da_semana>_<servername>.sql ==> contém o script que permitirá executar o attach dos databases.
Defaultdb.err ==> contém erros encontrados durante a documentação dos Default Databases.
Defaultdb_<dia_da_semana>_<servername>.sql ==> contém o script que permitirá associar os logins a seus default databases.
— Arquivos gerados pela procedure usp_docdevices
DeviceBackup.err ==> contém erros encontrados durante a documentação dos devices de backup (se existir).
DeviceBackup_<dia_da_semana>_<servername>.sql ==> contém o script que permitirá recriar ou migrar os devices de backup
— Arquivos gerados pela procedure usp_docjobs
Jobs.err ==> contém erros encontrados durante a documentação dos Jobs (se existir).
Jobs_<dia_da_semana>_<servername>.sql ==> contém o script que permitirá recriar ou migrar os Jobs.
— Arquivos gerados pela procedure usp_doclinkedsrv
LinkedServer.err ==> contém erros encontrados durante a documentação dos Linked Servers (se existir).
LinkedServer_<dia_da_semana>_<servername>.sql ==> contém o script que permitirá recriar ou migrar os Linked Servers.
— Arquivos gerados pela procedure usp_docconfig
sp_configure.err ==> contém erros encontrados durante a documentação da sp_configure.
sp_configure_<dia_da_semana>_<servername>.sql ==> contém o script que permitirá reconfigurar as configuração globais do servidor.
— Arquivos gerados pela procedure usp_docserverperm
ServerPermissions.err ==> contém erros encontrados durante a documentação da sp_configure.
ServerPermissions_<dia_da_semana>_<servername>.sql ==> contém o script que permitirá recriar as permissões de nível servidor em uma instância.
DOWNLOAD
O script completo com a criação das procedures tanto para o ambiente SQL Server 2000 quanto para o SQL Server 2005 ou superior pode ser baixado clicando sobre os respectivos links.
Script para SQL Server 2000: Doc-SQLServer-Package-SQL2000.sql
Script para SQL Server 2005 ou superior: Doc-SQLServer-Package.sql
UM CENÁRIO
Como visto, existem várias situações onde estes scripts poderão ser úteis no dia-a-dia de um DBA, mas apenas como exemplo, vamos supor um cenário onde o DBA perdeu seu servidor e precisará reinstalá-lo do zero. No entanto, este DBA tem como regra deixar os arquivos das bases em um disco diferente e portanto, os arquivos de suas bases estão íntegros no outro disco.
Suponhamos que o DBA tenha agendado um job para executar o script de documentação todos os dias as 17:00hs. Após a reinstalação do servidor, os scripts podem ser executados na seguinte ordem.
1. Script para recriar os logins.
2. Script para attachar os databases.
3. Script para associar o default database dos logins.
4. Script para recriar os devices de backup, os jobs e os linked server.
5. Script para reconfigurar as configurações globais do servidor sp_configure).
6. Script para reciar as permissões de nível servidor.
Nota: Como os logins tiveram que ser recriados, pode acontecer de os usuários dentro das bases ficarem em um estado que chamamos de órfãos.
Bom pessoal, como puderam notar os scripts gerados pela documentação possui 1001 utilidades, mas pode ser muito útil principalmente no momento de reinstalação ou migração de um servidor.
A documentação trata do servidor como um todo mas fiquem a vontade para adaptá-lo às suas reais necessidades e não esqueçam de testá-lo, testá-lo e testá-lo em um ambiente de teste antes de colocá-lo em produção.
Um abraços a todos
Nilton Pinheiro