Material de Apoio – Agosto 2019


Hello everybody, good morning.

 

Seja bem-vindo a mais um post da sessão Material de Apoio. Como você tem passou estes dias desde o nosso último post? Eu acredito que esteja tudo bem, pois não faz tanto tempo assim desde o nosso último encontro aqui no meu blog.

 

Posso dizer que o mês de julho foi bastante intenso e repleto de atividades, mesmo estando alguns dias em casa e curtindo um pequeno Descanso em conjunto com a minha família, me dediquei muito a participar a manter uma das minhas principais atividades como MVP em Data Platform, cuidar dos fóruns MSDN/TechNet aqui no Brasil dedicados exclusivamente ao Microsoft SQL Server, não foi fácil, por incrível que parece nestes últimos 40 dias o volume de participantes e questões cresceu de forma considerável, isso é muito bom, várias e várias questões interessantes e desafiadoras, e como eu sempre digo aprender não custa nada, na verdade custa sim, custa você querer e se dedicar. Não é mesmo!

 

Mas qual é o motivo de iniciar mais este post falando sobre os fóruns MSDN/TechNet, calma, fique tranquilo, daqui a pouco você vai descobrir. Dando continuidade, estou de volta cumprindo a minha missão de colaborar e compartilhar com a comunidade técnica mais um post desta que é uma das sessões mais importantes existentes no meu blog.

Espero que você esteja gostando do conteúdo aqui disponibilizado, como também, possa me ajudar a torná-lo ainda melhor no decorrer do tempo com a sua participação.

 

O post de hoje

Seja bem-vindo a mais um post da sessão Material de Apoio, sendo o quarto post da sessão em 2019 e de número 164 no total da mesma.

Para aqueles que já acompanham o meu blog a um certo tempo, os posts dedicados a sessão Material de Apoio, possuem o objetivo de compartilhar o conhecimento de recursos, funcionalidades e procedimentos que podemos realizar no Microsoft SQL Server.

Hoje será um pouco diferente, estou trazendo alguns dos mais recentes scripts catalogados nos meses de junho e julho, oriundos de questões apresentadas nos fóruns MSDN/TechNet, é justamente ai que esta importante ferramenta disponível na internet aparece, todos os arquivos compartilhados neste post, que atualmente estão compondo a minha galeria de códigos formada ao longo dos anos de trabalho como DBA e atualmente como Professor de Banco de Dados, foram concebidos com base no entendimento das respectivas dúvidas relacionadas ao Microsoft SQL Server em seus mais diversos recursos e funcionalidades.

Destaco que os arquivos a seguir, não necessariamente foram considerados como respostas para as questões ou dúvidas, como também, não representam a melhor solução, na verdade os respectivos códigos e scripts satisfazem um possível entendimento da minha parte para com o possível entendimento e interpretação do que o participante post.

Neste post você vai encontrar arquivos relacionados com os seguintes temas:

◦ 1 – Bloqueios de Querys e Transações;
◦ 2 – Cláusula Where;
◦ 3 – Comando Begin
◦ 4 – Comando Case When;
◦ 5 – Comando Case;
◦ 6 – Comando Create Table
◦ 7 – Comando Declare;
◦ 8 – Comando End;
◦ 9 – Comando Insert;
◦ 10 – Comando Select;
◦ 11 – Comando Set;
◦ 12 – Comando Union All;
◦ 13 – Comando Union;
◦ 14 – Common Table Express
◦ 15 – Concatenação de Strings;
◦ 16 – Consumo de Energia;
◦ 17 – DataType Int,
◦ 18 – DataType SmallInt,
◦ 19 – DataType TinyInt;
◦ 20 – Derived Table
◦ 21 – Encontrando HeadBlockers;
◦ 22 – Extended Stored Procedure XP_Regread;
◦ 23 – Função Stuff();
◦ 24 – Gerando Totais e Subtotais;
◦ 25 – Isolation Level Read Committed;
◦ 26 – Isolation Level Read Uncommitted;
◦ 27 – Movimentação de Saldo de Estoque;
◦ 28 – Níveis de Hierarquia de dados;
◦ 29 – Níveis de Isolamento;
◦ 30 – Operador Cross Apply;
◦ 31 – Operador Lógico Condicional While;
◦ 32 – Operador Outer Apply;
◦ 33 – Transferindo o Saldo de Estoque para o próximo mês;
◦ 34 – Visão de Compatibilidade sys.sysprocesses.

Espero que este conteúdo possa lhe ajudar em seus atividades profissionais e acadêmicas. Por questões de compatibilidade com a plataforma WordPress.com, todos os arquivos estão renomeados com a extensão .docx ao final do seu respectivo nome, sendo assim, após o download torna-se necessário remover esta extensão, mantendo somente a extensão padrão .sql.

Material de Apoio

1 – Material de Apoio – Agosto – 2019 – Consultando as configurações de energia do servidor.sql

2 – Material de Apoio – Agosto – 2019 – Entendendo os operador Apply em conjunto com Cross e Outer.sql

3 – Material de Apoio – Agosto – 2019 – Identificando o nível de isolamento atualmente em uso.sql

4 – Material de Apoio – Agosto – 2019 – Montando uma Derived Table.sql

5 – Material de Apoio – Agosto – 2019 – Analisando movimentação de Saldos e transportando para o próximo mês caso necessário.sql

6 – Material de Apoio – Agosto – 2019 – Concatenação de string diretamente no select + Stuff.sql

7 – Material de Apoio – Agosto – 2019 – Encontrando HeadBlockers e resolvendo bloqueios.sql

8 – Material de Apoio – Agosto – 2019 – Gerando totais e subtotais parciais agrupando dados sem nível de hierarquia.sql

9 – Material de Apoio – Agosto – 2019 – Transaction Isolation Levels – Read Committed e Read Uncommitted.sql

10 – Material de Apoio – Agosto – 2019 – Criando uma agenda com intervalos de 30 minutos.sql

Fique a vontade para copiar, editar, compartilhar e distribuir estes arquivos com seus contatos, aproveite se possível deixe seu comentário, críticas, sugestões e observações.

Nota: Todos os arquivos disponibilizados foram obtidos ou criados com autorização de seus autores, sendo estes, passíveis de direitos autorais.

Links

Caso você queira acessar os posts anteriores da sessão, não perca tempo utilize os links listados abaixo:

https://pedrogalvaojunior.wordpress.com/2019/06/25/material-de-apoio-junho-2019/

https://pedrogalvaojunior.wordpress.com/2019/04/17/material-de-apoio-abril-2019/

https://pedrogalvaojunior.wordpress.com/2019/02/04/material-de-apoio-fevereiro-2019/

https://pedrogalvaojunior.wordpress.com/2018/12/18/material-de-apoio-dezembro-2018/

Agradecimento

Quero agradecer imensamente a sua visita, sinto-me honrado e orgulhoso de contar com a sua presença.

Não deixe de acessar os outros posts das demais sessões, o próximo post desta sessão será publicado no mês de outubro, até lá, continue aproveitando cada momento da sua vida, desfrutando com muita sabedoria os momentos de galeria e também os desafios que são colocados ao seu redor.

A strong embrace, great health, success, we meet soon.

Thanks.

#26 – Para que serve

Conheça uma pequena introdução sobre os Níveis de Isolamento, destacando de forma prática o Read Uncommitted.


Olá, pessoal, bom dia.

Como vocês estão? Já fazem alguns meses que não nos encontramos nesta sessão em meu blog, fico feliz em te encontrar novamente. Seja bem-vindo a mais um post da sessão Para que Serve, post de número 26, em mais um dia de muitas atividades, afazeres e compromissos profissionais, domésticos (sim, eu adoro ajudar a minha esposa, cuidar da nossa casa…..) e claro acadêmicos.

Neste post quero destacar uma parte de um dos recursos mais importantes, impactantes e tradicionais do Microsoft SQL Server existente deste sua primeira versão, o qual esta totalmente relacionamento com o comportamento de nossas transações, querys e processamentos que possam estar sendo realizados neste momento em nossos servidores ou instâncias.

Sendo direto e reto no assunto, você que esta neste momento lendo este post e trabalhando com seus dados, tabelas e bancos no SQL Server esta fazendo uso dele sem talvez saber que ele exista, me refiro ao tradicionais Níveis de Isolamento de Transações ou Transaction Isolation Levels.

Você se lembra da existência deste recurso e o quanto ele é importante? Pois bem, caso não se lembra, a partir deste post e provavelmente os próximos 2 ou 3 futuros serão dedicados nesta sessão a apresentar de forma simples, prática e muito didática como podemos fazer uso deste recurso em nossas transações, seus comportamentos, vantagens e desvantagens (isso se elas existirem) e principalmente os riscos ao fazer uso talvez de uma forma não muito indicada.

Sendo assim, chegou a hora de conhecer um pouco mais sobre o post de número 25 da sessão Para que serve. Mas uma vez, bem vindo ao #26 – Para que serve – Apresentando os nível de isolamento Read Uncommitted.

Espero que você esteja animado para conhecer um pouco mais sobre este recurso, caso já conheça, continue lendo este post, sempre podemos aprender algo novo….

Continue Lendo “#26 – Para que serve”

Controlando o comportamento, bloqueios e versionamento de linhas com o Set Transaction Isolation Level

Dica – Controlando o comportamento, bloqueios e versionamento de linhas com o Set Transaction Isolation Level


Pessoal, boa tarde.

A dica de hoje tem o objetivo de apresentar como podemos realizar e controlar do comportamento, bloqueio e versionamento de linhas no SQL Server através da instrução Transact-SQL SET TRANSACTION ISOLATION LEVEL.

A instrução SET TRANSACTION ISOLATION LEVEL, esta presente no SQL Server a muito tempo, desde de versões como SQL Server 7 ou 2000 esta instrução vem passando por atualizações e aperfeiçoamentos, com a finalidade de proporcionar cada vez mais uma forma mais simples, eficiente e eficaz nas atividades de Controle, Comportamento, e Bloqueios de dados e objetos, como também o Versionamento de Linhas existentes em nossas tabelas.

Pois bem, como o SET TRANSACTION ISOLATION LEVEL, consegui realizar este tipo de funcionalidade. Para isso existe um dos conceitos mais importantes no SQL Server, chamado de Transact Isolation Level ou Níveis de Isolamento de Transações, responsáveis em proporcionar ao SQL Server algumas formas de comportamento durante o acesso do usuário as dados, tabelas e bancos, bem como, estabelecer formas de garantir a validação e integridade dos dados.

Os níveis de isolamento de transaçãi são descritos em termos de quais efeitos colaterais de simultaneidade são permitidos, como leituras sujas ou leituras fantasma.

Níveis de isolamento da transação controlam:

  • Se são feitos bloqueios quando os dados são lidos, e que tipo de bloqueio é solicitado;
  • Por quanto tempo os bloqueios de leitura são mantidos;
  • Se uma linha de referência de operação de leitura foi modificada por outra transação:
    • Bloqueia até que o bloqueio exclusivo na linha seja liberado;
    • Recupera a versão confirmada da linha existente no momento em que a instrução ou transação foi iniciada; e
    • Lê a modificação de dados não confirmados.

A seguir apresento uma breve descrição de cada Nível de Isolamento de Transação existente no SQL Server 2008:

READ UNCOMMITTED –  Leitura Não Confirmada: Especifica que as instruções podem ler linhas que foram modificadas por outras transações, mas que ainda não foram confirmadas.

Transações em execução em nível READ UNCOMMITTED não emitem bloqueios compartilhados para impedir que outras transações modifiquem os dados lidos pela transação atual.

As transações em READ UNCOMMITTED também não são bloqueadas por bloqueios exclusivos que impediriam a transação atual de ler linhas que foram modificadas, mas não confirmadas, por outras transações.

Quando essa opção está definida, é possível ler modificações não confirmadas, chamadas de leituras sujas. Os valores nos dados podem ser alterados e linhas podem aparecer ou desaparecer do conjunto de dados antes do término da transação. Essa opção tem o mesmo efeito de definir NOLOCK em todas as tabelas em todas as instruções SELECT em uma transação. Esse é o menos restritivo dos níveis de isolamento.

No SQL Server, você também pode minimizar a contenção de bloqueios e, ao mesmo tempo, proteger as transações contra leituras sujas de modificações de dados não confirmadas, usando:

  • O nível de isolamento READ COMMITTED com a opção de banco de dados READ_COMMITTED_SNAPSHOT definida como ON.
  • O nível de isolamento SNAPSHOT.

READ COMMITTED – Leitura Confirmada: Especifica que as instruções não podem ler dados que foram modificados, mas que ainda não foram confirmados por outras transações. Isso impede leituras sujas.

Os dados podem ser alterados por outras transações entre instruções individuais dentro da transação atual, resultando em leituras não repetíveis ou dados fantasmas. Essa é a opção padrão do SQL Server.

O comportamento de READ COMMITTED depende da configuração da opção de banco de dados READ_COMMITTED_SNAPSHOT:

  • Se READ_COMMITTED_SNAPSHOT estiver definido como OFF (o padrão), o Mecanismo de Banco de Dados usará bloqueios compartilhados para impedir que outras transações modifiquem linhas enquanto a transação atual estiver executando uma operação de leitura.

  • Os bloqueios compartilhados também bloqueiam a instrução de ler linhas modificadas por outras transações até que a outra transação seja concluída. O tipo de bloqueio compartilhado determina quando ele será liberado. Os bloqueios de linha são liberados antes que a próxima linha seja processada. Os bloqueios de página são liberados quando a próxima página é lida e bloqueios de tabela são liberados quando a instrução é finalizada.

  • Quando a opção de banco de dados READ_COMMITTED_SNAPSHOT for ON, você poderá usar a dica de tabela READCOMMITTEDLOCK para solicitar bloqueio compartilhado, em vez do controle de versão de linhas, para instruções individuais em transações em execução no nível de isolamento READ COMMITTED.

REPEATABLE READ – Leitura Repetível: Especifica que as instruções não podem ler dados que foram modificados, mas que ainda não foram confirmados por outras transações e que nenhuma outra transação pode modificar dados que foram lidos pela transação atual até que a transação atual seja concluída.

  • Os bloqueios compartilhados são colocados em todos os dados lidos por cada instrução na transação, sendo mantidos até que a transação seja concluída. Isso impede que outras transações modifiquem qualquer linha que tenha sido lida pela transação atual.
  • Outras transações podem inserir novas linhas que correspondam às condições de pesquisa das instruções emitidas pela transação atual.
  • Então, se a transação atual tentar a instrução novamente, ela recuperará as novas linhas, o que resultará em leituras fantasmas. Como os bloqueios compartilhados são mantidos até o término da transação, em vez de serem liberados ao final de cada instrução, a simultaneidade é menor que o nível de isolamento READ COMMITTED padrão. Use essa opção apenas quando necessário.

SNAPSHOT – Instantâneo: Especifica que os dados lidos por qualquer instrução em uma transação serão a versão transacionalmente consistente que existia no início da transação. A transação pode reconhecer apenas modificações de dados que estavam confirmadas antes do início da transação.

  • Modificações de dados efetuadas por outras transações após o início da transação atual não são visíveis para as instruções em execução na transação atual. O efeito será como se as instruções em uma transação obtivessem um instantâneo dos dados confirmados conforme existiam no início da transação.
  • Exceto quando um banco de dados está sendo recuperado, as transações SNAPSHOT não requerem bloqueios ao ler dados.
  • Transações SNAPSHOT que leem dados não bloqueiam outras transações de gravar dados. Transações que gravam dados não bloqueiam transações SNAPSHOT de ler dados.

Durante a fase de reversão de uma recuperação de banco de dados, as transações SNAPSHOT solicitarão um bloqueio se houver uma tentativa de ler dados que se encontram bloqueados por outra transação que está sendo revertida. A transação SNAPSHOT será bloqueada até que aquela transação seja revertida. O bloqueio será liberado tão logo seja concedido.

  • A opção de banco de dados ALLOW_SNAPSHOT_ISOLATION deve ser definida como ON para que uma transação que usa o nível de isolamento SNAPSHOT seja iniciada. Se uma transação que usa o nível de isolamento SNAPSHOT acessar dados em vários bancos de dados, ALLOW_SNAPSHOT_ISOLATION deve ser definida como ON em cada banco de dados.

Uma transação iniciada com outro nível de isolamento não pode ser definida com o nível de isolamento SNAPSHOT; isso causaria a anulação da transação. Se uma transação for iniciada no nível de isolamento SNAPSHOT, você poderá alterar seu nível de isolamento e retorná-la para o SNAPSHOT. Uma transação é iniciada na primeira vez em que ela acessa dados.

Uma transação em execução sob o nível de isolamento SNAPSHOT pode exibir as alterações feitas por essa transação. Por exemplo, se a transação executar um UPDATE em uma tabela e, em seguida, emitir uma instrução SELECT na mesma tabela, os dados modificados serão incluídos no conjunto de resultados.

SERIALIZABLE – Serializável: Especifica o seguinte: As instruções não podem ler dados que foram modificados, mas que ainda não foram confirmados por outras transações.

  • Nenhuma outra transação pode modificar dados lidos pela transação atual até que a transação atual seja concluída.
  • Outras transações não podem inserir linhas novas com valores chave que estejam no intervalo de chaves lido por qualquer instrução da transação atual até que esta seja concluída.

Bloqueios de intervalo são colocados no intervalo de valores chave que corresponde às condições de pesquisa de cada instrução executada em uma transação. Isso bloqueia que outras transações atualizem ou insiram qualquer linha que seja qualificada para qualquer uma das instruções executadas pela transação atual.

Isto significa que, se qualquer uma das instruções de uma transação for executada uma segunda vez, ela lerá o mesmo conjunto de linhas. Os bloqueios de intervalo são mantidos até que a transação seja concluída.

Esse é o mais restritivo dos níveis de isolamento, pois ele bloqueia intervalos de chaves inteiros até que a transação seja concluída. Como a simultaneidade é menor, use essa opção apenas quando necessário. Essa opção tem o mesmo efeito de definir HOLDLOCK em todas as tabelas em todas as instruções SELECT de uma transação.

Observação: A escolha de um Nível de Isolamento deve ser feita com muito cuidado e levando-se em consideração suas regras de comportamento e póssiveis impactos que esta alteração pode proporcionar.

Normalmente toda e qualquer transação especifica um nível de isolamento que define o grau em que uma transação deve ser isolada de modificações de recurso ou de dados feitas por outras transações.

Escolhendo um Nível de Isolamento de Transação

Escolher um nível de isolamento da transação não afeta os bloqueios obtidos para proteger as modificações de dados. Uma transação sempre obtém um bloqueio exclusivo em quaisquer dados que modifica e mantém tal bloqueio até que a transação seja concluída, sem considerar o conjunto de níveis de isolamento para a transação em questão.

Vale ressaltar que para operações de leitura, níveis de isolamento da transação definem principalmente o nível de proteção dos efeitos das modificações feitas por outras transações.

Outro detalhe importante, esta relacionado a mudança do  nível de isolamento para um nível inferior, o que pode proporcional para o SQL Server um aumento na capacidade e possibilidade de muitos usuários acessarem dados ao mesmo tempo, além de um possível aumento no número de efeitos de simultaneidade (como leituras sujas ou atualizações perdidas) que os usuários podem encontrar.

Inversamente, um nível de isolamento mais alto reduz os tipos de efeito de simultaneidade que os usuários podem encontrar, mas requer mais recursos do sistema e aumenta as chances de uma transação bloquear outra. Escolher o nível de isolamento apropriado depende de equilibrar os requisitos de integridade de dados do aplicativo em relação à sobrecarga de cada nível de isolamento.

Os chamdos níveis de isolamento mais altos, como o serializável, garante que uma transação recuperará exatamente os mesmos dados toda vez que repetir uma operação de leitura, mas faz isto executando um nível de bloqueio que provavelmente causará impacto em outros usuários em sistemas multiusuários.

O mais baixo nível de isolamento, leitura de dados não confirmados, pode recuperar dados que foram modificados mas não foram confirmados por outras transações. Todos os efeitos colaterais de simultaneidade podem acontecer em leitura não confirmada, mas não há nenhum bloqueio de leitura ou controle de versão, assim a sobrecarga é minimizada.

A Tabela 1 a seguir mostra os efeitos colaterais de simultaneidade habilitados por níveis de isolamento diferentes:

Nível de isolamento Leitura suja Leitura não repetível Fantasma
Leitura não confirmada Sim Sim Sim
Leitura confirmada Não Sim Sim
Leitura repetível Não Não Sim
Instantâneo Não Não Não
Serializável Não Não Não

Tabela 1 – Efeitos colaterais de simultaneidade aplicados a cada Nível de Isolamento de Transações.

Agora que já conhecemos um pouco dos Níveis de Isolamento de Transações, seus impactos, efeitos colaterais e possíves situações de uso, podemos utilizar a SET TRANSACTION ISOLATION LEVEL.

Utilizando a instrução SET TRANSACTION ISOLATION LEVEL:

Para proporcionar uma forma fácil de realizar o Controle, Comportamento, Blqueios e Versionamento de dados a Linguagem Transact-SQL possui em seu conjunto de comandos, a instrução SET TRANSACTION ISOLATION LEVEL.

A partir do momento em que esta instrução repassa para o SQL Server qual Nível de Isolamento de Transação deve ser utilizado, todas as mudanças em sua forma de comportamento são aplicadas de forma online, sem necessitar de qualquer tipo de processo de reinicialização dos serviços utilizados pelo servidor ou instância SQL Server.

Sintaxe:

 
SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
[ ; ]

Apenas uma única opção de nível de isolamento pode ser definida por vez, permanecendo definida para aquela conexão até que seja explicitamente alterada. Todas as operações de leitura executadas na transação operam sob as regras do nível de isolamento especificado, a menos que uma dica de tabela na cláusula FROM de uma instrução especifique comportamento de bloqueio ou controle de versão diferente para uma tabela.

Ao alterat uma transação de um nível de isolamento para outro, os recursos lidos após a alteração são protegidos de acordo com as regras do novo nível. Recursos lidos antes da alteração continuam sendo protegidos de acordo com as regras do nível anterior.

Por exemplo: Ao mudar uma transação de READ COMMITTED para SERIALIZABLE, os bloqueios compartilhados adquiridos após a alteração serão, nesse caso, mantidos até o término da transação.

Se você emitir SET TRANSACTION ISOLATION LEVEL em uma Stored Procedure ou Trigger, quando o controle retornar para o objeto retornar, o nível de isolamento será redefinido como o nível que estava em vigor quando o objeto foi invocado.

Por exemplo: Ao definir REPEATABLE READ em um lote e o lote chamar um procedimento armazenado que define o nível de isolamento como SERIALIZABLE, a configuração do nível de isolamento será revertida para REPEATABLE READ quando o procedimento armazenado retornar o controle para o lote.

Para ilustrar como podemos utilizar a SET TRANSACTION ISOLATION LEVEL, apresento a seguir alguns exemplos:

1 – Definindo o SET TRANSACTION ISOLATION LEVEL para uma Sessão:

USE BANCO1;
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO

BEGIN TRANSACTION;
GO

SELECT *  FROM PRODUTOS;
GO

SELECT * FROM FORNECEDORES;
GO

COMMIT TRANSACTION;
GO

Observação: Neste exemplo, cada instrução Transact-SQL processada, o SQL Server irá manter todos os bloqueios compartilhados até o término da transação.

2 – Definindo o Nível de Isolamento de Transações Serializable:

USE AdventureWorks2008R2;
GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
SELECT BusinessEntityID FROM HumanResources.Employee;
GO

Observação: Para este exemplo o SQL Server vai realizar a sequência de instruções em série, respeitando uma definição interna da ordem correta de execução até que a última instrução seja executada.

3 – Para determinar o nível de isolamento da transação definido atualmente:

USE AdventureWorks2008R2;
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO

DBCC USEROPTIONS;
GO

Observação: Use a instrução DBCC USEROPTIONS, como mostrado no exemplo a seguir. O conjunto de resultados pode ser diferente do conjunto de resultados em seu sistema.

 

Bom pessoal chegamos ao final de mais uma dica, espero que vocês tenham gostado e este material possa ser útil no seu dia-a-dia.

 

Mais uma vez obrigado por sua visita, nos encontramos em breve com mais algumas dicas.

 

Até mais.

 

Utilizando Backup de Filegroup no SQL Server – Final

Dica – Utilizando Backup de Filegroup no SQL Server – Final.


Boa tarde, pessoal!!!

Estou de volta mais uma vez para finalizar esta série de artigos sobre Backup de Filegroup.

— Parte Final —

Bom mas nem tudo pode ser considerado fácil, aparentemente nosso ambiente esta integro e funcional, mas não é bem assim. Se tentarmos inserir ou consultar dados em uma das tabelas armazenada no filegroup Secondary, recebemos uma mensagem de erro informando que este filegroup encontra-se em offline, conforme apresenta a Figura 12.

Figura 12. Tentativa de consulta a tabela Saldos, mas filegroup Secondary em offline.

Isso ocorreu pois realizamos uma restauração parcial do nosso ambiente, mas não se atentamos para o detalhe que também será necessário liberar o filegroup Secondary para uso.

Consultando a system catalog view Sys.Database_Files, poderemos encontrar mais informações sobre o atual status no nosso banco de dados e seus respectivos filegroups, podemos observar a coluna state_desc, que apresenta o status atual dos arquivos, conforme a Figura 13.

Figura 13. Informações sobre o status de cada filegroup.

Analisando as informaões apresentadas na Figura 13, observamos que o Arquivo de Dados Primário e o Arquivo de
Log
de Transações estão apresentando o status de OnLine, ao contrário do segundo arquivo de dados, definido como Recovery_Pending.

Para resolvermos este pequeno obstáculo, é muito simples, basta realizar a restauração do filegroup Secondary e posteriormente restaurar novamente o Log de Transações sobre este banco, desta forma, o SQL Server ira entender que este arquivo esta liberado para ser utilizado, conforme apresenta a Listagem 10.

Listagem 10. Liberando o filegroup Secondary para uso

— Bloco 1 —

Use master

Go

Restore Database SQLMagazine

File = ‘SQLMagazine_Secondary_Dados’,

Filegroup = ‘Secondary’

From Disk = ‘C:\SQL\Backup-Secondary-SQLMagazine.bak’

With File=1,  Replace,  NoRecovery

Go

— Bloco 2 —

Use master

Go

Restore Log SQLMagazine

From Disk = ‘C:\SQL\Backup-Log-SQLMagazine.bak’

With Recovery

Go

Agora sim, nosso ambiente esta totalmente operacional, pronto para ser utilizado, ambos os filegroups estão liberados para manipulação de dados e objetos, para confirmar, podemos utilizar novamente a system catalog view Sys.Database_Files,
conforme apresenta a Figura 14, de acordo com a coluna state_desc.

Figura 14. Informações sobre cada filegroup, liberados para uso, com status OnLine.

Como podemos observar, o processo de restauração do segundo filegroup também foi realizado, com uma única diferença em relação ao filegroup Primary. Não tivemos a necessidade utilizar a opção Partial, pois não estavamos restaurando o filegroup Primary.
Havendo a necessidade de restauração do filegroup Secondary, deveríamos repetir somente os passos apresentados anteriormente na Listagem 10.

Conclusão

A utilização de diversos filegroups em um banco de dados pode trazer vários benefícios, como: maior disponibilidade, contigência, melhorias de performance e segurança. Em relação a administração poderemos ter um pouco mais de esforços necessários para manter o ambiente operacional, mas nada que acabe impedindo sua implementação.

Como podemos observar, uma das vantagens em seu utilizar dois ou mais filegroups esta vinculada com a possibilidade de manter nosso ambiente em funcionamento, mesmo que possam ocorrer problemas de disponibilidade. O artigo demonstrou de forma simples e prática como realizar a recuperação de objetos e seus dados, sem gerar a impossibilidade de acesso ao Banco de Dados.

Além disso, foi possível entender alguns conceitos relacionados a Backup e Restauração de dados, bem como, os comandos básicos utilizados em cada recursos, possibilitando entender suas diferenças e características.

De que se trata o artigo:

Neste artigo, apresentaremos uma das estratégias para se trabalhar com Filegroups, o chamado Backup de Filegroups, onde temos a disposição a possibilidade de gerar cópias de seguranças dos nossos grupos de arquivos de forma completa ou isoladamente, auxiliando na manutenção e disponibilidade de nosso ambiente.

Para que serve:

O Backup de Filegroup tem como objetivo proporcionar um ambiente mais seguro para retenção e recuperação de dados. Através desta estratégia de backup, temos a possibilidade de recuperar parte de um banco de dados, sem ter a necessidade de
recuperar o banco como um todo.

Outro detalhe importante, o Backup de Filegroup pode ser facilmente implementado, sem necessitar qualquer tipo de aquisição de Hardware ou Software. Em combinação com o serviço de tarefas agendadas existentes no SQL Server, chamado de SQL
Server Agent, torna-se possível programar a cópia isolada de partes do banco de dados em horários específicos, melhorando ainda mais a segurança das informações manipuladas anteriormente a última cópia de segurança(Backup de Dados).

Em que situação o tema útil:

Este tipo de funcionalidade pode ser útil em ambientes com grande manipulação de informações, necessidade de contigência isolada dos dados, ou ainda que necessitam ser armazenadas de forma segura e não permitam qualquer tipo de perda. Em caso de qualquer falha, através do Backup de Filegroups torna-se possível recuperar as áreas manipuladas de um Banco de Dados sem afetar outras áreas que estão integras ou necessariamente não necessitam ser recuperadas através de processo de Restauração de Dados(Restore).

Notas – Backup: Permite o backup integral ou parcial do banco de dados do SQL Server ou de arquivos individuais ou de grupos de arquivos do banco de dados. Não é possível criar backups no nível de tabela.
O backup e a restauração do SQL Server funcionam em todos os sistemas operacionais suportados, se forem sistemas de 64 ou 32 bits. O escopo de um backup de dados (um backup de dados) pode ser um banco de dados inteiro, um banco de dados parcial ou um conjunto de arquivos ou grupos de arquivos.

Restore: Restauração é o processo de copiar dados de um backup e aplicar transações em log aos dados para fazer roll-forward até o ponto de recuperação de destino. Um backup de dados ou diferencial contém registros de log de transações suficientes para permitir roll-froward nas transações ativas como parte da restauração de cada backup.

Cada backup contém também log suficiente para reverter transações não confirmadas para trazer o banco de dados a um estado operacional consistente e utilizável. O processo de reverter transações não confirmadas, se houver, e trazer o banco de dados online é conhecido como recuperação.

Modelos de Recuperação de Banco de Dados: Os modelos de recuperação são projetados para controlar a manutenção de log de transações. Existem três modelos de recuperação: simples, completo e bulk-logged.
Geralmente, um banco de dados usa o modelo de recuperação completa ou o modelo de recuperação simples.

Referências

Books OnLine do SQL Server 2008.

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.pt/s10de_6tsql/html/89a4658a-62f1-4289-8982-f072229720a1.htm

Books OnLine do SQL Server 2008.

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.pt/s10de_4deptrbl/html/0331befe-dc11-462e-a6f2-da7b08f0a672.htm

Books OnLine do SQL Server 2008.

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.pt/s10de_6tsql/html/877ecd57-3f2e-4237-890a-08f16e944ef1.htm

MSDN Library – Backup (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms186865.aspx

MSDN Library – Backup Overview (SQL Server)

http://msdn.microsoft.com/en-us/library/ms175477.aspx

MSDN Library – Restore Restrictions Under the Simple Recovery Model

http://msdn.microsoft.com/en-us/library/ms189323.aspx