Júlio Battisti Livros e Cursosa sua sala de aula na internet

Nos capítulos anteriores aprendemos a criar Bancos de Dados, tabelas e índices. Também aprendemos a fazer o backup e o restore de Bancos de Dados. No Capítulo 6 aprendemos a configurar a segurança no SQL Server 2005 e a integração com a segurança do Windows. Agora, já temos um Banco de Dados configurado e razoavelmente seguro. Porém existem uma série de outras tarefas que devem ser executadas para garantir, além da disponibilidade e segurança, o desempenho e a confiabilidade do Banco de Dados.

Neste capítulo aprenderemos a utilizar uma série de ferramentas que auxiliam o DBA a administrar, monitorar e otimizar os Bancos de Dados de uma ou mais instâncias do servidor SQL Server 2005.

Iniciaremos o capítulo aprendendo a criar um “Plano de Manutenção” para o Banco de Dados. Iremos criar este Plano de Manutenção utilizando o assistente Maintenance Plan.

Depois aprenderemos a utilizar os chamados comandos DBCC – Database Consistency Checker. Estes comandos são utilizados para efetuar uma série de verificações de consistência em um Banco de Dados, em resumo, nos ajudam a diagnosticar e corrigir problemas que possam existir nas tabelas e índices do banco de dados. Com isso, trabalhamos de maneira pró-ativa, procurando nos antecipar aos problemas, antes mesmo que eles aconteçam.

Também aprenderemos a importar dados para uma tabela do Banco de Dados e exportar dados do SQL Server para fontes externas. Continuaremos o capítulo aprendendo a utilizar o DTS – Data Transformation Services. Na verdade, DTS é o nome da ferramenta que coordena o acesso a diversas fontes de dados no SQL Server 2000. No SQL Server 2005 esta ferramenta é bem mais poderosa e mais fácil de utilizar.

Esta é uma ferramenta que permite a integração entre fontes de dados nos mais diversos formatos, através da tecnologia OLE-DB. Por exemplo, podemos consolidar nas tabelas de um Banco de Dados do SQL Server 2005, dados originados do Mainframe, combinados com dados do Microsoft Access e dados do DB2 da IBM. Ou quem sabe, para misturar um pouco mais, vamos também adicionar dados de uma planilha do Excel e de um Banco de Dados no formato Dbase (.dbf). Esta integração é possível, graças ao Business Intelligence Development Studio, que é como é conhecido todo o ambiente do DTS, no SQL Server 2005. Mas o mais interessante é que não estamos limitados a integrar dados de fontes diversas, também podemos transformá-los (T de Transformation).

Na continuação aprenderemos a utilizar uma ferramenta de monitoração de performance do Windows 2000 Server / Windows Server 2003: o console Desempenho. Através deste console podemos medir o desempenho de uma série de objetos/contadores relacionados com o SQL Server 2005. Por exemplo, podemos determinar o número de transações por segundo, o número de logins por segundo, etc. Vamos fazer alguns exemplos práticos para aprender a utilizar este console. O console Desempenho pode ser utilizado para monitorar o desempenho de uma série de objetos/contadores, não estando limitado a objetos/contadores do SQL Server 2005. Assim, podemos, por exemplo, monitorar o uso de recursos críticos para o servidor Windows, tais como a utilização do processador, da memória RAM e das operações de I/O de disco. Porém, neste capítulo nos limitaremos à análise de desempenho dos objetos/contadores relacionados com o SQL Server 2005.

Explicarei em detalhes o conceito de objetos/contadores na seção sobre desempenho.

A próxima e última ferramenta, a ser analisada neste capítulo, é o Profiler. Esta ferramenta permite a monitoração das atividades de cada instância do servidor SQL Server 2005, separadamente. Também podemos monitorar as atividades de Banco de Dados. Além de monitorar, a atividade pode ser salva e depois executada novamente contra o Banco de Dados. Também podemos salvar os dados capturados em uma tabela, em um arquivo ou em um Script SQL.

Know-how em: Criação de um Plano de Manutenção Para um Banco de Dados do SQL SERVER 2005

Pré-Requisitos:

• Fundamentos apresentados na Parte I.
• Fundamentos sobre segurança no SQL Server 2005.
• Saber utilizar o Enterprise Manager.

Metodologia:

• Apresentação dos passos necessários para a criação de um plano de manutenção.

Técnica:

• Criação de um plano de manutenção utilizando o SQL Server Management Studio.

Um Plano de Manutenção define uma série de tarefas que devem ser executadas no Banco de Dados para garantir o bom funcionamento do banco, com bom desempenho e garantia de disponibilidade dos dados, através de uma estratégia adequada de backup/restore. Com o Plano de Manutenção também são definidas as tarefas de backup para evitar a perda de informações, além de tarefas que verificam a integridade dos objetos do Banco de Dados. Esta série de tarefas pode ser criada de uma maneira consolidada, através da utilização da criação de um Plano de Manutenção (Maintenance Plan).

Você pode criar um plano de manutenção manualmente, mas a maneira mais fácil e prática é criarmos um Plano de Manutenção do Banco de Dados, utilizando o assistente Maintenance Plan Wizard. Este assistente, ao ser executado, cria e agenda para execução periódica, vários Jobs, sendo que cada Job executa uma determinada tarefa. Os Jobs, criados pelo assistente Database Maintenance Plan Wizard, executam as seguintes tarefas:

• Reorganizam os dados nas páginas de dados e índices através da reconstrução dos índices com um novo valor para o parâmetro Fill Factor. Isto garante uma melhor distribuição dos dados, com uma melhora no desempenho. Para maiores informações sobre o parâmetro Fill Factor, consulte o Capítulo 4.
• Fazem a compactação do Banco de Dados, removendo páginas de dados que estão vazias.
• Atualizam uma série de informações sobre os índices do Banco de Dados. Estas informações são chamadas de “Indexes Statistics”. Com essa atualização, o SQL Server 2005 tem condição de tomar melhores decisões na hora de executar uma consulta. O resultado final é um desempenho bem melhor.
• Fazem uma verificação interna na consistência dos dados, para certificar-se de que os dados não estão corrompidos ou em um estado inconsistente.
• Agendam o backup do Banco de Dados e do Log de transações. Além disso, podemos configurar um período durante o qual os backups deverão ser mantidos. Porém para manter um histórico dos backups, devemos levar em consideração as necessidades de armazenamento e mídias, no caso de estarmos efetuando o backup em fita magnética.

A replicação de dados pode também ser utilizada para manter cópias idênticas dos dados em dois ou mais servidores. Aprenderemos sobre replicação no Capítulo 8. No Capítulo 14 aprenderemos sobre novos recursos do SQL Server 2005, os quais também podem ser utilizados para duplicação de dados, tais como Database Snapshot e Database Mirror.

Para que você possa criar um plano de manutenção, usando o Maintenance Plan Wizard, o login que você está utilizando, deve ser um membro da seguinte role: MaintenanceUserRole. Evidentemente que, por padrão, a conta sa tem estas permissões, bem como todas as contas pertencentes ao grupo Administradores, do Windows. Você pode atribuir a permissão de criar planos de manutenção para qualquer login. Para isso, basta navegar até a opção Security, da instância a ser configurada, expandir a opção Logins, clicar com o botão direito do mouse no login a ser configurado e, no menu de opções que é exibido, clicar em Propriedades. Será aberta a janela de propriedades do login, com a guia General selecionada por padrão. Clique na guia Database Access. Na lista Databases acessible by this login, marque o banco de dados msdb. Na lista Database roles for: msdb, na parte de baixo da janela, marque a role MaintenanceUserRole e clique em OK. Pronto, o login foi configurado, com permissão de criar um Plano de Manutenção, usando o assistente.

Agora vamos aprender a criar um Plano de Manutenção, utilizando o Maintenance Plan Wizard, ou melhor, o Assistente de Plano de Manutenção.

Exemplo prático: Para criar um plano de manutenção para o Banco de Dados AdventureWorks, da instância SERVIDOR\SQL2005, siga os passos indicados a seguir:

1. Abra o SQL Server Management Studio (Iniciar -> Programas -> Microsoft SQL Server 2005 -> SQL Server Management Studio).

2. Na janela Object Explorer, localize a instância SERVIDOR\SQL2005 e dê um clique no sinal de +, ao lado desta instância, para exibir as opções disponíveis.

3. Nas opções que surgem, dê um clique no sinal de + ao lado da opção Management, para exibir as opções disponíveis.

4. Dentre as opções que são exibidas, clique com o botão direito do mouse na opção Maintenance Plans. No menu que surge, clique na opção Maintenance Plan Wizard.

5. Será exibida a tela inicial do assistente, com uma mensagem informando sobre o que pode ser feito com o assistente. Dê um clique no botão Next, seguindo para a próxima etapa. Surgirá a tela indicada na Figura 7.1.

Figura 7.1 Definindo a instância do SQL Server 2005, na qual será criado o plano.

6. Nesta etapa você define em qual instância do SQL Server 2005 será criado o plano de manutenção. Por padrão, vem selecionada a instância dentro da qual estava a opção Management -> Maintenance Plans, na qual você clicou com o botão direito do mouse. Vamos aceitar a instância sugerida, pois é exatamente nesta que queremos criar um plano de manutenção.  Dê um clique no botão Next, seguindo para a próxima etapa do assistente.

7. Nesta etapa você define quais tarefas farão parte do plano de manutenção. Por padrão, vem selecionadas todas as tarefas: Chek database integrity, Shrink database, Defragment index(es), Re-index, Update statistics, History cleanup, Launxh SQL Server agent job, Backup database (full), Backup database (Differential) e Backup database (Transaction Log). Deixe todas as opções marcadas, com exceção da opção Backup database (Differential). Dê um clique no botão Next, seguindo para a próxima etapa do assistente.

8. Nesta etapa você define a ordem em que as tarefas serão executadas. Para alterar a ordem, você marca uma determinada tarefa e clica no botão Move up..., para movê-la para cima na lista ou Move down..., para movê-la para baixo na lista. Não vamos alterar a ordem sugerida. Dê um clique no botão Next, seguindo para a próxima etapa do assistente.

9. Abra  a lista <Select one or more>. Ao abrir esta lista, serão disponibilizadas diversas opções. Nesta etapa podemos definir se o plano de manutenção incluirá todos os Bancos de Dados da instância (All databases), ou apenas os do sistema (All system databases (master, model, and msdb)), ou se incluirá todos os Bancos de Dados do usuário (All user databases (all databases other than master, model, and msdb)) ou apenas os selecionados (These specific databases). A opção These specific databases já vem selecionada por padrão. Na lista de Bancos de Dados podemos selecionar aqueles que queremos incluir no Plano de Manutenção.

No nosso exemplo iremos incluir apenas o Banco de Dados AdventureWorks.

Certifique-se de que o Banco de dados AdventureWorks esteja selecionado na lista de Bancos de Dados. Sua janela deve estar semelhante a indicada na Figura 7.2:

Figura 7.2 Selecionando o banco de dados AdventureWorks.

10. Clique em OK para fechar a lista de opções e certifique-se de que a opção Include indexes, esteja selecionada, para garantir que a otimização dos índices do banco de dados AdventureWorks, seja incluída no plano de manutenção. Dê um clique no botão Avançar para ir para a próxima etapa do assistente.

11. Nesta etapa, definiremos para quais bancos de dados, serão criadas tarefas de otimização. Abra  a lista <Select one or more>. Ao abrir esta lista, serão disponibilizadas diversas opções, já descritas anteriormente. A opção These specific databases já vem selecionada por padrão. Na lista de Bancos de Dados podemos selecionar aqueles que queremos incluir no Plano de Manutenção. No nosso exemplo iremos incluir apenas o Banco de Dados AdventureWorks. Certifique-se de que o Banco de dados AdventureWorks esteja selecionado na lista de Bancos de Dados. Clique em OK para fechar a lista de opções. Ao selecionar um banco de dados, serão habilitadas as poções de otimização e recuperação de espaço. Aceite as configurações sugeridas e dê um clique no botão Next para ir para a próxima etapa do assistente.

12. Nesta etapa podemos definir uma série de configurações a respeito da otimização dos índices e das páginas de dados do Banco de Dados. Nesta etapa você tem três listas de opções. Na primeira lista você pode quais bancos de dados serão incluídos no plano de manutenção, para terem os índices de suas tabelas e views, desfragmentados. Abra a primeira lista (Database(s):) e selecione somente o banco de dados AdventureWorks. Na segunda lista (Object), você seleciona a opção Table – para otimizar somente os índices das tabelas, ou a opção View, para otimizar somente os índices das Views, ou a opção Tables and Views, para otimizar os índices tanto das tabelas quanto das Views. Selecione a opção Tables and Views. Ao selecionar a opção Table and Views, automaticamente, serão selecionadas todas as tabelas e views. Se você selecionar uma opção individual, como por exemplo Table, na terceira lista, você poderá marcar somente determinadas tabelas, para ter os índices otimizados. Com as opções selecionadas, sua janela deve estar semelhante a indicada na Figura 7.3.       

Figura 7.3 Definindo otimizações dos índices

13. Dê um clique no botão Next para ir para a próxima etapa do assistente.

14. Nesta etapa você seleciona para quais bancos de dados, serão criadas tarefas, que irão recriar os índices, quando as tarefas forem executadas. As três primeiras listas tem funcionamento idêntico as três listas da Figura 7.3. Na primeira lista você marca um ou mais bancos de dados, na segunda lista você seleciona as opções Table, Views ou Tables and Views e na terceira lista, os objetos, individualmente, dependendo de qual opção foi selecionada na segunda lista. Esta tela tem uma série de opções avançadas, que somente um DBA experiente deverá alterar, ou seja, somente altere estas opções se você conhecer exatamente o que está fazendo. Para o nosso exemplo, na primeira lista, selecione o banco de dados AdventureWorks e, na segunda lista, selecione a opção Tables and Views. Mantenha as demais opções desta etapa, inalteradas. Dê um clique no botão Next para ir para a próxima etapa do assistente.

15. Nesta etapa você seleciona para quais bancos de dados, serão criadas tarefas, que atualizar as estatísticas das tabelas e views, quando as tarefas forem executadas. As três primeiras listas tem funcionamento idêntico as três listas da Figura 7.3. Na primeira lista você marca um ou mais bancos de dados, na segunda lista você seleciona as opções Table, Views ou Tables and Views e na terceira lista, os objetos, individualmente, dependendo de qual opção foi selecionada na segunda lista. Para o nosso exemplo, na primeira lista, selecione o banco de dados AdventureWorks e, na segunda lista, selecione a opção Tables and Views. Mantenha as demais opções desta etapa, inalteradas. Dê um clique no botão Next para ir para a próxima etapa do assistente.

16. Nesta etapa você seleciona quais opções de históricos serão limpas, quando as tarefas do plano de manutenção, forem executadas e a periodicidade que estas tarefas (específicas para limpeza dos históricos), devem ser executadas. Nesta etapa, você pode marcar as opções Backup and Restory history, SQL Server Agent Job history e Database Maintenance Plan History. Por padrão, estas três opções vem selecionadas. Na parte de baixo da janela, você define quais dados devem ser excluídos do histórico. Por padrão, vem selecionada a opção 4 Week(s), significando que serão criadas tarefas, no plano de manutenção, para excluir dados que tenham sido gravados a mais de quatro semanas, nos históricos selecionados. Nesta etapa vamos aceitar as configurações padrão, indicadas na Figura 7.4. Dê um clique no botão Next para ir para a próxima etapa do assistente.

Figura 7.4 Definindo opções para limpar dados dos históricos.

17. Nesta etapa são exibidos os jobs já existentes, criados anteriormente. Você pode marcar um ou mais job para ser executado, também como parte do plano de manutenção. No nosso exemplo, incluiremos todos os Jobs já existentes, como parte do plano de manutenção. Certifique-se de que todos os jobs estejam selecionados. Dê um clique no botão Next para ir para a próxima etapa do assistente.

18. Nesta etapa você irá definir para quais bancos de dados, serão criadas tarefas de backup, como parte do plano de execução. Abra a lista Databases e, abaixo da opção These specific databases, marque o banco de dados AdventureWorks e clique em OK.  As demais opções serão habilitadas. Você pode definir se o backup será feito em disco ou em fita, em qual pasta será feito o backup (no caso de um backup em disco), se os backups já existentes devem ser sobrescritos ou não e assim por diante. Defina as opções desejadas e clique em Next, para seguir para a próxima etapa do assistente.

19. Nesta etapa você pode criar tarefas que farão o backup diferencial de um ou mais bancos de dados. Vamos incluir um backup diferencial do banco de dados AdventureWorks, como parte do plano de manutenção. Na lista Database(s):, selecione o banco de dados AdventureWorks e clique em OK. Aceite as demais opções e clique em Next, para seguir para a próxima etapa do assistente.

20. Nesta etapa você pode criar tarefas que farão o backup do log de transações de um ou mais bancos de dados. Vamos incluir um backup do log do banco de dados AdventureWorks, como parte do plano de manutenção. Na lista Database(s):, selecione o banco de dados AdventureWorks e clique em OK. Aceite as demais opções e clique em Next, para seguir para a próxima etapa do assistente.

21. Nesta etapa você pode definir uma conta conhecida como Proxy Account. Se for configurada uma conta como Proxy Account, as tarefas serão executadas no contexto desta conta, ou seja, a conta configurada como Proxy Account, deverá ter todas as permissões necessárias para executar as tarefas do plano de manutenção. Para o nosso exemplo, não irei configurar uma conta como Proxy Account. Clique em Next, para seguir para a próxima etapa do assistente.

22. Nesta etapa você define em qual pasta será gravado um relatório sobre o plano de manutenção. Por padrão vem selecionado C:\. Vamos aceitar as configurações sugeridas. Clique em Next, para seguir para a próxima etapa do assistente.

23. Será exibida a tela final do assistente. Se você precisar alterar alguma opção, utilize o batão Back. Para finalizar o assistente e criar o plano de manutenção, clique em Finish. O SQL Server 2005 mostrará o progresso da criação do plano de manutenção, conforme indicado na Figura 7.5:

Figura 7.5 Progresso da criação do plano de manutenção.

24. Uma vez concluída a criação do plano de manutenção, será exibida a janela com o resultado da criação. Clique em Close para fechar esta janela. Pronto, o plano de manutenção foi criado.

Você pode alterar um Plano de Manutenção quando for necessário. Para isso basta localizar o Plano de Manutenção, clicar com o botão direito do mouse no mesmo, e no menu que surge, clicar em Modify. Na Figura 7.6 temos a janela de propriedades do Plano de Manutenção criado neste exemplo. Observe que bem no lado esquerdo da tela, é exibido o painel Maintenance Tasks, Neste painel, é exibida a lista de tarefas que compõem o plano de manutenção.

Figura 7.6 Propriedades do Plano de Manutenção.

No lado direito da tela, são exibidas as guias que dão acesso as propriedades do plano de manutenção. Dê um clique na guia Subplan Editor. Observe que as diversas tarefas que compõem o plano de manutenção, são representadas, na forma de um diagrama de blocos seqüencial, onde uma tarefa é executada após a outra, conforme indicado na Figura 7.7:

Figura 7.7 podemos ver os diversos Jobs criados pelo Plano de Manutenção.

Você pode, facilmente, acessar os comandos T-SQL associados a cada uma das tarefas. Por exemplo, clique na primeira tarefa – Check database..., para selecioná-la. Estando o retângulo da primeira tarefa selecionado, clique com o botão direito do mouse nele e, no menu de opções que é exibido, clique em Edit. Será aberta a janela de propriedades da tarefa. Clique no botão View T-SQL. Será exibido os comandos T-SQL que serão executados, quando a tarefa for executada. Clique em Close para fechar a janela de comandos T-SQL. Clique em OK para fechar a janela de propriedades do Job.

Para fazer com que todas as tarefas do Plano de Manutenção sejam executadas, imediatamente, basta clicar no plano de execução com o botão direito do mouse e, no menu de opções que é exibido, clicar em Execute. Para acessar o histórico de execução, clique com o botão direito do mouse e, no menu de opções que é exibido, clique em View History.

25.   Feche o SQL Server Management Studio.

O Assistente para criação de um plano de manutenção é um bom auxiliar para ajudar na criação de planos de manutenção, básicos. Claro que, depois que você já for um DBA mais experiente, mais rodado, você, provavelmente, utilizará o assistente apenas como um ponto de partida. Depois, com base na sua experiência, irá personalizar as tarefas criadas pelo assistente e, até mesmo, criar novas tarefas.

Know-how em: Comandos DBCC – Database Consistency Checker

Pré-Requisitos:

• Noções sobre o log de transações.
• Fundamentos apresentados na Parte I.
• Saber utilizar a janela para execução de comandos T-SQL.

Metodologia:

• Apresentação dos principais comandos DBCC.

No SQL SERVER 2005, através da linguagem T-SQL, temos uma série de comandos para manutenção e otimização de tabelas e índices, comandos estes conhecidos como “comandos DBCC”. Este grupo de comandos é conhecido como “comandos DBCC”, porque todos iniciam com o prefixo DBCC. A grande maioria destes comandos é utilizada para verificação da consistência física e lógica de um Banco de Dados e de seus elementos, tais como tabelas e índices. Em muitas situações, o comando além de fazer a verificação, é capaz de corrigir os problemas encontrados.

Podemos dividir os comandos DBCC em quatro categorias, conforme descrito na Tabela 7.1.

Tabela 7.1 Categorias para os comandos DBCC.

Vamos estudar os principais comandos em cada uma destas categorias.