Manter uma documentação completa (lista de logins, databases, user, jobs, etc..) de um servidor SQL Server é um Best Practice que pode nos economizar um tempo precioso na hora de reinstalar ou migrar um servidor, recriar ou copiar logins e databases entre servidores, etc... Neste artigo estarei descrevendo uma stored procedure que você poderá executar diariamente em seus servidores de forma a manter uma documentação completa dos mesmos.
Quanto tempo você poderia ganhar se todas as vezes que for migrar ou reinstalar um servidor ou 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 e linked servers do servidor?
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 o default database de cada login.
4. Gera um script com todos os devices de backup.
5. Gera um script com todos os jobs.
6. Gera um script com todos os linked servers.
7. Gera um script com todos os usuários e roles (grupos) existentes em cada database.
8. Gera um script com as configuração globais do servidor (sp_configure).
A STORED PROCEDURE
Na verdade todo este trabalho é realizado por um conjunto de 9 stored procedures onde cada uma delas possui uma função específica. A procedure usp_docservidor é a procedure principal e deve ser executada passando como referência o caminho onde os scripts deverão ser armazenados.
Exemplo:
-- Se estiver conectado na base Pubs
EXEC usp_docservidor 'C:/temp/doc_servidor'
ou
-- Se estiver conectado a qualquer outra base diferente da Pubs
EXEC pubs..usp_docservidor 'C:/temp/doc_servidor'
Nota: O script cria todas as procedures na base de dados Pubs. 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 outras 8 procedures, também passando como referência o caminho onde os scripts deverão ser armazenados. Embora estas 8 stored procedures sejam chamadas pela procedure principal, elas também podem ser executadas individualmente.
Exemplo:
EXEC pubs..usp_doclogins 'C:/temp/doc_servidor'
EXEC pubs..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 tem como função gerar um script contendo 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: Tem como função gerar um script com 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 um script 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 para reassociar os logins a seus respectivos default databases.
O script do arquivo permite executar um attach dos databases, porém os respectivos arquivos .mdf e .ldf dos databases devem estar em suas localizações originais. Caso tenha copiado os arquivo .mdf e .ldf de um database para outro servidor, altere o script de forma a apontar para o caminho (letras do disco onde os arquivos foram copiados) do novo servidor. 
4. usp_docdevices: Cria um script 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: 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: 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 um script que permitirá reconfigurar as configurações globais do servidor de acordo com os últimos valores em execução (sp_configure).
8. sp_hexadecimal: Esta procedure é criada no banco de dados MASTER e é utilizada pela procedure usp_doclogins para recuperar a senha dos logins.
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.
ARQUIVOS DE SAÍDA
Cada uma das sete procedures chamadas pela procedure principal usp_docservidor, irá criar dois arquivo 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 do servidor. Isso permite 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_Monday_WINXPPRO.sql
Databases_Tuesday_WINXPPRO.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.
DOWNLOAD
O script completo com a criação procedures tanto para o ambiente SQL Server 2000 quanto para o SQL Server 2005 pode ser baixado clicando sobre os respectivos links.
Script para SQL Server 2000: usp_docservidor.sql
Script para SQL Server 2005: usp_docservidor_2005.sql
Nota: O script também criará um Job de nome Documenta_Servidor que executará a procedure usp_docservidor diariamente as 17:00hs. Por default o Job salva os scripts e um arquivo de log (Documenta_Servidor.log) no caminho C:/temp/doc_servidor.
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).
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. Para reassociar os usuários a seus respectivos logins, execute em cada base de dados o script para Associar Usuários Orfã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 em um ambiente de teste antes de colocá-lo em produção.
Qualquer dúvida sobre o script, envie um post para um de nossos fórums.
Um abraços a todos
Nilton Pinheiro
Nota: Review
|
Comunidade:
Membros:
