Restore Database com SSIS – Parte 01

Olá Pessoal!

Demorou mas.. “I’m here..”… \o/..

O post de hoje será sobre uma das tarefas fundamentais de um administrador de banco de dados (DBA) que é garantir que os dados possam ser recuperados no caso de um desastre.  No nosso dia a dia, adquirimos várias coisas que possam nos deixar tranquilos com nossos bens como por exemplo, uma apólice de seguro para carro ou para sua casa. Apesar de caro e rezarmos para nunca usarmos é uma maneira que escolhemos para nos assegurar contra qualquer “imprevisto”.  Enfim, voltando para o nosso assunto, os backups são as apólices de seguro contra qualquer eventualidade na empresa em que trabalhamos. O backup será o responsável por recuperarmos os dados que foram perdidos e no final das contas o negócio voltar a fazer as suas operações empresariais rotineiras. Costumo dizer que ele é o responsável pelo “final feliz” de qualquer situação.. (rsrs).

Muitas vezes precisamos fazer um restore de uma base em um ambiente diferente do que nós temos, seja ele para fins de teste de backup ou para um ambiente de homologação para que a equipe de desenvolvimento faça seus testes antes de subir uma versão nova. Nesses casos existem diversas formas de resolver o problema como fazer um backup remoto e trazer a base para o ambiente que você precisa ou criar um job para fazer um backup copy only e depois fazer o restore apartir do arquivo gerado. Nesse post para de atender essa necessidade criaremos um pacote para fazer um restore através de um FTP que encontra-se em um servidor de domínio diferente para isso utilizaremos o SSIS, WINSCP e um pouco de powershell.

No final da criação do pacote, o mesmo ficará assim:

1

FTP com WinSCP

Para configurar os FTP devemos primeiro baixar e instalar o WinSCP para depois configurarmos um site. A imagem abaixo mostra como ficará após a configuração do site:

2

Agora vamos criar um arquivo de texto contendo os seguintes passos:

  1. Abrir o site criado no WinSCP;
  2. Encontrar o arquivo de backup;
  3. Copiar o arquivo para o diretório na máquina onde será feito o restore

A criação desse aquivo de texto será o que o WinSCP utilizará para pegar o arquivo no diretório que foi definido para o backup ser salvo e levá-lo para a pasta no outro servidor onde será feito o restore.

Abra o notepad e coloque o seguinte script:

option batch on

option confirm off

open FTP_DATABASE

get /BACKUP/DATABASE/NOME_DATABASE_FULL_201411301300.BAK S:\RESTORE\RESTORE_DATABASE\

exit

3

Agora com o SQL Server Data Tools aberto vamos colocar um “Execute Process Task” e configurar as propriedades: Executable, Arguments e WorkingDirectory.

4

Parâmetros configurados:

Executable C:\Program Files (x86)\WinSCP\WinSCP.exe
Arguments -script=C:\FTP\ftpdatabase.txt
WorkingDirectory C:\Program Files (x86)\WinSCP

Obs: Note que no parâmetro “Arguments” foi criado um diretório chamado FTP dentro do C:\ da máquina onde esta sendo criado o pacote.

Por hoje é só gente 😦 …

No próximo post daremos continuidade a criação do pacote. Espero que tenham gostado, quaisquer dúvidas , críticas ou sugestões serão bem vindas caso queiram comentar.

Abraços,

Cibelle Castro.

Descobrindo Auditoria no SQL SERVER 2008 – Parte 7

Olá Pessoal,

É com muita satisfação que hoje finalmente termino os posts sobre auditoria (“para a nossa alegria”).  Aos que ainda não viram os outros segue os links:

E agora mão a obra! Nesse post vamos criar um Database Audit Specification, o mesmo irá auditar todos os comando de Insert, Update, Delete ou Select. Para criar um Database Audit Specification você deve escolher o banco de dados que deseja fazer a auditoria. No nosso caso será o AdventureWorks.

1) Clique com o botão direito em “Database Audit Specification” e selecione “New Audit Specification”, como mostrado na figura abaixo:

1

2) Na janela “Create Database Audit Specification” você pode aceitar o nome sugerido ou caso queira pode digitar seu próprio nome. Como disse anteriormente acho válido sempre escolhermos um nome que tenha algum sentido, pois a pessoa que for administrar ou dar uma manutenção ou está ali de bobeira olhando o servidor (tomara que seja algum estagiário porque se for alguém que você não tem a menor ideia, pode sentar e chorar! rs). Brincadeiras a parte, na opção Name digite:

DatabaseAuditSpecificationSelectInsertDeleteUpdate.

3) Em seguida selecione a partir da caixa suspensa o Server Audit Object que armazenaremos os dados da auditoria. Dessa vez vamos armazenar os dados da auditoria em um arquivo, utilizaremos o Server Audit Object criado anteriormente. Selecione o Target TargetFileSSMS.

2

4) Na seção “Audit Action Types”, você deve especificar o tipo de ação de auditoria que deseja capturar. No primeiro combo selecione a opção SELECT, com o objetivo de gravar toda atividade do comando SELECT para uma determinada tabela, ou para a base inteira.

3

5) Agora devemos escolher “Object Class”. Vamos auditar mais de uma tabela de um banco especifico então selecione a segunda opção dessa coluna, ou seja, OBJECT.

4

6) Devemos selecionar qual o OBJECT será auditado, para isto clique no botão “…” da coluna Object Name.

5

7)  Na janela “Select Objects” clique no botão “Object Types”. Escolha o tipo “Tables” e desabilite as outras opções.

6

8) Selecione o botão “Browse..” e escolha as tabelas que serão monitoradas no banco AdventureWorks. Selecione as seguintes tabelas:

  • HumanResources.Employee
  • Person.Contact
  • Production.Location
  • Production.UnitMeasure

Clique no botão OK.

7

9) A próxima etapa será escolher o “Principal Name”, ou seja, qual o usuário que queremos auditar. Nessa opção podemos selecionar Users e Roles. Para fazer isso, clique no botão “” da coluna “Principal Name” e na janela “Select Objects” clique no botão “Browser…”. Agora você pode ver quais “Principals” pode escolher, nesse caso nos vamos escolher a opção “public”, pois o objetivo dessa auditoria é identificar qualquer pessoas que execute a cláusula SELECT nas tabelas selecionadas.

8

10) Concluímos o nosso primeiro filtro, vamos repetir as operações descritas acima para os comandos INSERT, UPDATE e DELETE.

11) Uma vez concluída as configurações clique em OK para concluirmos a criação do Database Audit Specification.

12) Assim como Server Audit Specification a especificação de banco de dados é criado desabilitado, se você observar verá que ele tem uma seta vermelha indicando que esta desativada, para habilita-la clique sobre o mesmo como botão direito e escolha a opção “Enable Database Audit Specification”.

13) Agora vamos selecionar, inserir, excluir e atualizar alguns registros das tabelas que estamos auditando para ver se tudo aquilo que configuramos esta funcionando conforme o esperado. Execute os scripts:

——————————
–AUDITANDO COMANDO SELECT
——————————
USE AdventureWorks
GO

SELECT
C.FirstName + ‘ ‘ + C.LastName AS ‘Nome Completo’,
E.VacationHours AS ‘Férias (horas)’,
E.SickLeaveHours AS ‘Afastamento por motivo de doença (horas)’,
E.LoginID AS ‘Login’
FROM
HumanResources.Employee E
LEFT JOIN Person.Contact C ON E.ContactID = C.ContactID
ORDER BY [Nome Completo];

—————————-
–AUDITANDO COMANDO SELECT
—————————-
SELECT * FROM Production.Location

—————————-
–AUDITANDO COMANDO UPDATE
—————————-
UPDATE Person.Contact
SET FirstName = ‘Cibelle Castro’,
emailAddress = ‘cibascastro@outlook.com.br’
WHERE ContactID = 6

—————————-
–AUDITANDO COMANDO DELETE
—————————-
DELETE FROM Production.UnitMeasure WHERE Name = ‘Boxes’

 —————————-
–AUDITANDO COMANDO INSERT
—————————-
INSERT INTO Production.Location  VALUES (‘Metal’,0.00,0.00,GETDATE())

14) Os dados foram armazenados em um arquivo que foi gerado na pasta C:\Auditoria.

9

 

15) Vamos visualizar os dados auditados utilizando a função fn_get_audit_file, que é responsável em retornar as informações de um arquivo de auditoria.

10

/* RETORNA INFORMAÇÕES DE UM ARQUIVO DE AUDITORIA. SINTAXE:
fn_get_audit_file ( file_pattern, {default | initial_file_name | NULL }, {default | audit_file_offset | NULL } ) */

SELECT
CONVERT(VARCHAR(10),EVENT_TIME,103) AS [DATA], –DATA EM QUE A AÇÃO FOI ACIONADA
CONVERT(VARCHAR(12),EVENT_TIME,114) AS [HORA], –HORA EM QUE A AÇÃO FOI ACIONADA
SERVER_INSTANCE_NAME AS [NOME INSTÂNCIA], –NOME DA INSTÂNCIA DE SERVIDOR NO QUAL A AUDITORIA OCORREU
DATABASE_NAME AS [NOME DO BANCO], –O CONTEXTO DO BANCO DE DADOS NO QUAL A AÇÃO ACONTECEU. PERMITE VALOR NULO. RETORNA NULL PARA AUDITORIAS REALIZADAS NO NÍVEL DE SERVIDOR.
SERVER_PRINCIPAL_NAME AS [LOGIN], –LOGON ATUAL. PERMITE VALOR NULO
OBJECT_NAME,
STATEMENT AS [CONSULTA EXECUTADA],
FILE_NAME AS [CAMINHO DO ARQUIVO]
FROM
fn_get_audit_file(‘C:\Auditoria\TargetFileSSMS_D59592D1-4057-49EB-A421-CF2DF6BB2F47_0_129603920978160000.sqlaudit’,DEFAULT,DEFAULT)

16) O resultado da consulta acima foi:

11

Perceba que foram retornados os dados do select, insert, update e delete. Uma parte essencial de qualquer estratégia de segurança de dados é um plano de segurança que possui regras com a capacidade de rastrear quem acessou ou tentou acessar dados, mas para ter essas informações precisamos realizar auditorias regulamente. Isso nos permite a capacidade de detectar prevenir qualquer ação maliciosa ou acessos ilegítimos.

Vimos no decorrer dos artigos como criar uma auditoria nos mais diversos seguimentos, vimos também que o SQL Server Audit, é uma ferramenta poderosa que pode ser utilizada para coletar quase todas as atividades que acontece dentro dos nossos servidores, banco de dados ou um objeto especifico.

Até a próxima pessoal!

Cibelle Castro. 🙂

Como executar Script no Powershell v4 através do SQL Server Agent?

9

Olá People!

Essa semana precisei fazer um script para apagar os arquivos de log de um determinado diretório depois que o job do backup diferencial terminasse. Até ai tudo bem, resolvi fazer o mesmo usando powershell e uma vez pronto colocaria como um segundo passo no job (lembrando que desde o SQL Server 2008, somos capazes de criar Jobs para executar os scripts do Windows PowerShell).

O script foi criado e testado no Ambiente de Script Integrado (ISE) e obtive os resultados esperados (vou disponibilizar em outro post o script feito). Tudo lindo, tudo funcionado o que faltava era apenas colocar o código no job e correr para o abraço.

Existem duas formas de executar scripts do PowerShell no SQL Server Agent. Ele pode ser executado como:

  • PowerShell job step
  • Command prompt (CmdExec) job step

Escolhi a opção “PowerShell job step” e criei o step que comentei no inicio do post. Ao executar o job, o mesmo, não conseguiu ser executado com sucesso. A pegadinha é que o PowerShell no SQL executa a versão 2 (o SQL Server que estou usando é o 2012 com Windows Server 2012 R2 Datacenter), independentemente de a última versão do PowerShell instalado no servidor e o script que tinha feito não tinha suporte para a sintaxe utilizada.

Caso queira ter certeza se o PS utilizado no seu servidor é a versão 2, basta executar PowerShell a partir do SQL Server Management Studio (SSMS).

1) Clique com o botão direito na instância e selecione a opção “Start PowerShell”.

1

2) Será aberta uma tela conforme a imagem abaixo com o SQL Server PowerShell executando.

2

3) Para saber qual a versão que esta sendo executada digite: $PSVersionTable. O resultado deverá ser assim:

3

4) Agora vamos olhar a versão do PowerShell que roda na sua máquina ou no seu servidor. Vá em Iniciar e procure a aplicação do PS que existe no seu sistema operacional.

5) Digite mais uma vez o comando: $PSVersionTable.

6) O resultado da instrução executada foi a seguinte:

5

Uma vez identificado a diferença de versões o que precisamos fazer é ajustar o script para executar no SQL Server Agent. Ao invés de termos o script diretamente no campo “Command” do step, vamos salvar o código em um arquivo e chamá-lo com a seguinte instrução:

Start-Process PowerShell -ArgumentList “& ‘E:\Script PowerShell\DeleteLogFiles.ps1′”

O step ficou configurado  assim:

7

Agora só executar novamente o job e ver uma janela parecida com essa:

8

Fiz o mesmo teste em outra máquina com no SQL Server 2014 com o Windows Server 2012 R2 Datacenter e a versão do PowerShell retornada através do SSMS foi a mesma que tem no sistema operacional, neste caso a versão 4.

10

Consequentemente consegui executar o script direto no job sem precisar salvar em um arquivo como fizemos anteriormente.

11

Inté a próxima pessoal!

Cibelle Castro 🙂

Descobrindo Auditoria no SQL SERVER 2008 – Parte 6

Olá Pessoal,

Hoje nós vamos continuar aquela série de Auditoria que comecei algum tempo atrás e não terminei de postar aqui no blog, para quem não viu segue os links dos posts anteriores:

Vamos lá! Agora que criamos o nosso Server Audit Objects precisamos adicionar especificação de auditoria correspondente. Se quisermos fazer uma auditoria no nível de instância, teremos que criar um Server Audit Specification. Vamos auditar toda vez que for gerado um backup ou restore no servidor. O nome do Action Group utilizado nesse exemplo será BACKUP_RESTORE_GROUP.

1) Vamos criar o Server Audit Specification. Abra o SSMS vá em Security \Server Audit Specification \New Server Audit Specification..

1

2) Preencha o campo “Name”, nesse exemplo coloquei o nome da especificação de auditoria + action group = ServerAuditSpecificationBackupRestore. Estou fazendo isso para facilitar o entendimento de quem esta administrando o servidor de banco de dados, ou para facilitar uma possível manutenção.

3) Na opção “Audit” devemos preencher com um dos Server Audit Objects criado anteriormente. Nesse caso vou querer armazenar os dados auditados no Application Log. Selecione a opção “TargetApplicationLogTSQL” (criado anteriormente). Conforme figura abaixo:

2

4) No campo Audit Action Type, selecione BACKUP_RESTORE_GROUP. Ao fazer isso as outras opções de configuração serão desabilitadas. Clique no botão OK.

3

5) Agora vamos habilitar o Server Audit Specification criado, para iniciarmos a coleta dos dados. Podemos fazer de duas maneiras, clicando com o botão direito em cima do objeto adicionado e selecionando a opção “Enable Server Audit Specification“, ou via TSQL.

4

6) Se fosse através de consulta o script ficaria dessa maneira: ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecificationBackupRestore] WITH (STATE = ON) GO

7) O script abaixo cria um banco de dados, depois uma tabela, faz a inserção dos dados e por fim faz o backup e um restore para vermos se os dados realmente estão sendo auditados.

————————————
–CRIAR BANCO DE DADOS AUDITORIA
————————————
IF (SELECT DB_ID (‘AUDITORIA’)) IS NULL
BEGIN
CREATE DATABASE AUDITORIA
END
GO

———————-
–CRIAR TABELA
———————-
USE AUDITORIA
GO

CREATE TABLE TABServerSpecification
(ID Int Identity(1,1) PRIMARY KEY,
Nome VarChar(250)DEFAULT NewID())

———————-
— INSERT
———————-

INSERT INTO TABServerSpecification DEFAULT VALUES
GO 10000

————————————
–FAZER BACKUP DO BANCO AUDITORIA
————————————

BACKUP DATABASE Auditoria
TO DISK = ‘C:\Auditoria\Auditoria.bak’
WITH FORMAT;
GO

————————————
— DROP NO BANCO AUDITORIA
————————————
USE MASTER
GO

DROP DATABASE AUDITORIA
GO

————————————
–RETORE BANCO DE DADOS AUDITORIA
————————————
RESTORE DATABASE AUDITORIA
FROM DISK = ‘C:\Auditoria\Auditoria.bak’
WITH RECOVERY
GO

8) Vamos verificar se o que acabamos de fazer foi realmente auditado, para isso procure a opção “Error Logs”. Selecione “Current…” com o botão direito e clique em “View Agent Log”.

8

9) Selecione a seguinte opção: Windows NT.

7

Verifique que todas as linhas que possuem uma chave foram os dados auditados, no nosso caso o backup e o restore que fizemos.

Então é isso gente! Até a próxima. 🙂

Cibelle Castro.

Relatório do ano 2013 – WordPress

Olá pessoal,

Hoje estou compartilhando o relatório anual que WordPress envia mostrando as estatísticas do ano que passou, ou seja de 2013.

Aqui está um trecho:

Um teleférico em São Francisco detém 60 pessoas. O blog foi visto cerca de 2.100 vezes em 2013, se fosse um “cable car” levaria cerca de 35 viagens para transportar tantas pessoas \o/…

Click here to see the complete report.

Cibelle Castro 🙂