MCDBA Brasil


  • Home
  • Sobre
  • Contato

Livros





Links Rápidos

SQL Server Builds (All Versions/Editions)


Download SQL Server 2017 (trial)


SQL Server 2017 Feature Pack


SQL Server 2016 Feature Pack


Cumulative Update SQL Server 2017 builds


Cumulative Update SQL Server 2016 builds


Cumulative Update SQL Server 2014 builds


Cumulative Update SQL Server 2012 builds


SQL Server 2005/2008 Samples Databases


Documentando o Servidor SQL Server


Analisando a Performance do Servidor-CheckList


Virtual PASS PT


Faça parte do maior virtual chapter do PASS com conteúdos técnicos em Português sobre SQL Server.

Todos os meses um evento Online para você! Acompanhe aqui os WebCasts já agendados

Sindicalize seu blog ou site ao VirtualPASSPT

SQL Server Blogs

SQL Server Query Processing Team


SQL Programmability & API Development Team


SQL Server Manageability Team


Latin America Support Team


Database + Disk + Performance


Microsoft SQL Server Support


SQL CLR Team


SQL Query Optimization Team


SQL 2005 Code Samples


SQL Server Express Team


SQL SMO Samples


SQL Storage Engine Team


SQL CAT Team


SQL Protocols Team


PSS SQL Server Engineers


Slava Oks on SQLOS


Ken Henderson’s blog


LUTI@Microsoft Blog


kimberly L. Trip’s blog


Fernando Garcia Blog

Artigos

Documentando o Servidor SQL Server

por Nilton Pinheiro dezembro 28, 2017 Nenhum comentário

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: empdoc_servidor

Exemplo:
EXEC usp_docservidor ‘C: empdoc_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: empdoc_servidor’
EXEC usp_docusers ‘C: empdoc_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

Avaliação:
Compartilhe:
  • Anterior Brigando com os Erros 17182, 17826 e 171207 anos atrás
  • Próximo MCDBABRASIL está de volta7 anos atrás

Deixe uma resposta Cancelar resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

MVP Reconnect Award

Categorias

  • Artigos (359)
  • Dica da Semana (95)
  • Documentação (54)
  • Downloads (113)
  • MSDE 2000 (3)
  • Sem categoria (1)
  • Tutoriais (9)

Posts recentes

  • #FechouBrasil #PartiuPortugal
  • Brigando com o erro “The cached MSI file is missing”
  • MCDBABRASIL está de volta
  • Documentando o Servidor SQL Server
  • Brigando com os Erros 17182, 17826 e 17120

SQL Server AlwaysOn Video Series

Video1: Introdução ao SQLServer2012 AlwaysOn


Video2: Introdução ao SQLServer2012 AlwaysOn Availability Group


Video3: Introdução ao SQLServer2012 AlwaysOn AVG-Demo


Video4: Introdução ao SQLServer2012 AlwaysOn Listener


Video5: Introdução ao SQLServer2012 AlwaysOn Readable Secondaries


Video6: Introdução ao SQLServer2012 AlwaysOn Readable Secondaries-Demo


Video7: Introdução ao SQLServer2012 AlwaysOn Failover Clustering


Serie SQL Server Failover Clustering End-to-End

Parte 1: Configuração da Rede e Ambiente


Parte 2: Configurando o Windows 2008 R2 Domain Controler e DNS


Parte 3: Preparando os nós para o Failover Cluster


Parte 4: Configurando um Failover Cluster de 2 nós


Parte 5: Configurando as LUNs no iSCSI Software Target (Parte 1)


Parte 6: Configurando as LUNs no iSCSI Software Target (Parte 2)


Parte 7: Apresentando as LUNs para os nós do Failover Cluster


Parte 8: Configurando os discos no Failover Cluster


Parte 9: Instalando a primeira instância virtual do SQL Server 2008


Parte 10: Instalando a segunda instância virtual do SQL Server 2008


Parte 11: Instalando e Configurando o MSDTC no Failover Cluster


Parte 12: Configurando Mount Points no Cluster e SQL Server 2008


Vídeo Extra: Removendo uma Instância do SQL Server 2008 R2 em Cluster


Alta Disponibilidade no SQL Server 2008 R2: Failover Clustering Overview


Alta Disponibilidade no SQL Server 2008 R2: Failover Clustering na Prática

Menu

  • Home
  • Sobre
  • Contato

Mais

  • RSS Feeds
2025 MCDBA Brasil.