Automatizando restores dos arquivos de backups

Hello galera, como têm passado? Estou passando bem rapinho apenas para compartilhar com vocês uma rotina administrativa bem legal que usamos em nossos ambientes para validar os arquivos gerados nos backups diários. Sabemos que a única forma de termos certeza se os backups são válidos para serem usados em ocasiões inoportunas, ou seja, em casos de desastre, é testando os mesmos na pratica através de restaurações periódicas. Pensando por esse ponto e da dificuldade que tínhamos em selecionar os arquivos para serem restaurados, decidimos elaborar uma stored procedure que pudêssemos agendar através de um job o tempo de testes dos arquivos. Levamos em consideração a lógica já usada pelo SQL Server, no qual restaura um banco de dados considerando a regra de menor esforço, entregando o banco de dados mais atualizada possível. A grande maioria dos ambientes que atuamos utiliza os seguintes tipos de backups:

  • Backup full diário;
  • Backup diferencial diário;
  • Backup de log diário.

Numa restauração de menor esforço o recomendado seria de restaurar na seguinte sequência:

  • Último backup full;
  • Último diferencial;
  • E os últimos backups de logs.

Assim teríamos um ambiente bem atualizado com baixíssima perda de dados.

Meu sócio então se dedicou a desenvolver uma procedure que passamos chamar de Sp_Restore. O objetivo seria de selecionar os arquivos mais recentes de backups e automatizar a restauração através de um job no SQLServerAgent. Tudo ficou fácil até mesmo na elaboração de um relatório mensal, onde o suporte de TI dos clientes passou a receber por email com todos os testes realizamos. Segue o script de criação da Sp_Restore abaixo.

Lembrando que é importante definir um login com permissão de leitura no banco msdb.

Version: 1.0
Data criação: 23/11/2017
Autor: Walmik Villas-Bôas (http://www.consultiba.com.br)

USE [DB_Consulti]
GO

/****** Object: StoredProcedure [dbo].[sp_Restore] Script Date: 04/16/2018 17:02:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_Restore]
@DBOrigem nvarchar(128)
, @DBDestino nvarchar(128)
, @CaminhoFull nvarchar(520)=”
, @CaminhoDiff nvarchar(520)=”
, @CaminhoLog nvarchar(520)=”
, @ServerRemoto nvarchar(128)=”
, @User nvarchar(128)=”
, @Pwd nvarchar(128)=”
AS
DECLARE @backup_set_id int, @media_set_id int, @backup_start_date datetime, @database_name nvarchar(256), @physical_device_name nvarchar(520), @type char(1)
DECLARE @ArqNome sysname, @ArqPath nvarchar(520)
DECLARE @TSQL nvarchar(4000)=”, @PATH nvarchar(4000)=N’ WITH REPLACE’, @Midia int=0, @Posicao int=0

DECLARE Caminho_Cursor CURSOR FOR
SELECT name, physical_name FROM sys.master_files WHERE database_id = db_id(@DBDestino) AND type IN (0,1) order by type desc, name asc
OPEN Caminho_Cursor;
FETCH NEXT FROM Caminho_Cursor
INTO @ArqNome, @ArqPath;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @Path=@Path + N’ , MOVE ”’ + @ArqNome + N”’ TO ”’ + @ArqPath + N””
FETCH NEXT FROM Caminho_Cursor
INTO @ArqNome, @ArqPath;
END
CLOSE Caminho_Cursor;
DEALLOCATE Caminho_Cursor;

DECLARE @StrSQL nvarchar(max), @cSQL nvarchar(max)
IF @ServerRemoto=”
BEGIN
SET @StrSQL='(SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type
FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id
WHERE bs.database_name=”’ + @DBOrigem + ”’ AND bs.type=”D” and bs.checkpoint_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=”’ + @DBOrigem + ”’ ORDER BY database_backup_lsn DESC))
UNION ALL
(SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type
FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id
WHERE bs.database_name=”’ + @DBOrigem + ”’ and bs.type=”I” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=”’ + @DBOrigem + ”’ ORDER BY database_backup_lsn DESC)
AND bs.checkpoint_lsn=(SELECT MAX(I.checkpoint_lsn) FROM (SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type, bs.checkpoint_lsn, bs.database_backup_lsn, bs.differential_base_lsn, bs.first_lsn, bs.last_lsn
FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id
WHERE bs.database_name=”’ + @DBOrigem + ”’ and bs.type=”I” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=”’ + @DBOrigem + ”’ ORDER BY database_backup_lsn DESC)) AS I))
UNION ALL
(SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type
FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id
WHERE bs.database_name=”’ + @DBOrigem + ”’ and bs.type=”L” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=”’ + @DBOrigem + ”’ ORDER BY database_backup_lsn DESC)
AND bs.checkpoint_lsn>=(SELECT bs.first_lsn FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id WHERE bs.database_name=”’ + @DBOrigem + ”’ and bs.type=”I” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=”’ + @DBOrigem + ”’ ORDER BY database_backup_lsn DESC)
AND bs.checkpoint_lsn=(SELECT MAX(I.checkpoint_lsn) FROM (SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type, bs.checkpoint_lsn, bs.database_backup_lsn, bs.differential_base_lsn, bs.first_lsn, bs.last_lsn FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id WHERE bs.database_name=”’ + @DBOrigem + ”’ and bs.type=”I” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=”’ + @DBOrigem + ”’ ORDER BY database_backup_lsn DESC)) AS I))
) ORDER BY backup_set_id’
SET @cSQL = ‘set @cursor = cursor forward_only static for ‘ + @StrSQL + ‘ open @cursor;’
END
ELSE
BEGIN
SET @StrSQL=’select b.* from openrowset(”SQLNCLI”, ”Server=’ + @ServerRemoto + ‘;UID=’ + @User + ‘;PWD=’ + @Pwd + ”’,”(SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type
FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id
WHERE bs.database_name=””’ + @DBOrigem + ””’ AND bs.type=””D”” and bs.checkpoint_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=””’ + @DBOrigem + ””’ ORDER BY database_backup_lsn DESC))
UNION ALL
(SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type
FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id
WHERE bs.database_name=””’ + @DBOrigem + ””’ and bs.type=””I”” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=””’ + @DBOrigem + ””’ ORDER BY database_backup_lsn DESC)
AND bs.checkpoint_lsn=(SELECT MAX(I.checkpoint_lsn) FROM (SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type, bs.checkpoint_lsn, bs.database_backup_lsn, bs.differential_base_lsn, bs.first_lsn, bs.last_lsn
FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id
WHERE bs.database_name=””’ + @DBOrigem + ””’ and bs.type=””I”” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=””’ + @DBOrigem + ””’ ORDER BY database_backup_lsn DESC)) AS I))
UNION ALL
(SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type
FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id
WHERE bs.database_name=””’ + @DBOrigem + ””’ and bs.type=””L”” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=””’ + @DBOrigem + ””’ ORDER BY database_backup_lsn DESC)
AND bs.checkpoint_lsn>=(SELECT bs.first_lsn FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id WHERE bs.database_name=””’ + @DBOrigem + ””’ and bs.type=””I”” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=””’ + @DBOrigem + ””’ ORDER BY database_backup_lsn DESC)
AND bs.checkpoint_lsn=(SELECT MAX(I.checkpoint_lsn) FROM (SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type, bs.checkpoint_lsn, bs.database_backup_lsn, bs.differential_base_lsn, bs.first_lsn, bs.last_lsn FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id WHERE bs.database_name=””’ + @DBOrigem + ””’ and bs.type=””I”” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=””’ + @DBOrigem + ””’ ORDER BY database_backup_lsn DESC)) AS I))
)”) AS b ORDER BY b.backup_set_id’
SET @cSQL = ‘set @cursor = cursor forward_only static for ‘ + @StrSQL + ‘ open @cursor;’
END

DECLARE @Restore_Cursor as cursor
exec sp_executesql @cSQL, N’@cursor cursor output’, @Restore_Cursor output
FETCH NEXT FROM @Restore_Cursor
INTO @backup_set_id, @media_set_id, @backup_start_date, @database_name, @physical_device_name, @type;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @Posicao=@Posicao+1
IF @type=’D’
BEGIN
IF @CaminhoFull=”
BEGIN
SET @TSQL=@TSQL + N’RESTORE DATABASE ‘ + @DBDestino + N’ FROM DISK = ”’ + @physical_device_name + N”” + @PATH
END
ELSE
BEGIN
SET @TSQL=@TSQL + N’RESTORE DATABASE ‘ + @DBDestino + N’ FROM DISK = ”’ + @CaminhoFull + REVERSE(LEFT(REVERSE(@physical_device_name),CHARINDEX( ‘\’,REVERSE(@physical_device_name))-1)) + N”” + @PATH
END
END
IF @type=’I’
BEGIN
IF @CaminhoDiff=”
BEGIN
SET @TSQL=@TSQL + N’RESTORE DATABASE ‘ + @DBDestino + N’ FROM DISK = ”’ + @physical_device_name + N””
END
ELSE
BEGIN
SET @TSQL=@TSQL + N’RESTORE DATABASE ‘ + @DBDestino + N’ FROM DISK = ”’ + @CaminhoDiff + REVERSE(LEFT(REVERSE(@physical_device_name),CHARINDEX( ‘\’,REVERSE(@physical_device_name))-1)) + N””
END
END
IF @type=’L’
BEGIN
IF @CaminhoLog=”
BEGIN
SET @TSQL=@TSQL + N’RESTORE LOG ‘ + @DBDestino + N’ FROM DISK = ”’ + @physical_device_name + N””
END
ELSE
BEGIN
SET @TSQL=@TSQL + N’RESTORE LOG ‘ + @DBDestino + N’ FROM DISK = ”’ + @CaminhoLog + REVERSE(LEFT(REVERSE(@physical_device_name),CHARINDEX( ‘\’,REVERSE(@physical_device_name))-1)) + N””
END
END
if @@CURSOR_ROWS<>@Posicao
BEGIN
if @type=’D’
BEGIN
SET @TSQL=@TSQL + N’ , NORECOVERY;’ + CHAR(10)
END
ELSE
BEGIN
SET @TSQL=@TSQL + N’ WITH NORECOVERY;’ + CHAR(10)
END
END
ELSE
BEGIN
if @type=’D’
BEGIN
SET @TSQL=@TSQL + N’ , RECOVERY;’ + CHAR(10)
END
ELSE
BEGIN
SET @TSQL=@TSQL + N’ WITH RECOVERY;’ + CHAR(10)
END
END
EXEC sp_executesql @TSQL;
–PRINT @TSQL
SET @TSQL=”
FETCH NEXT FROM @Restore_Cursor
INTO @backup_set_id, @media_set_id, @backup_start_date, @database_name, @physical_device_name, @type;
END
CLOSE @Restore_Cursor;
DEALLOCATE @Restore_Cursor;

GO

Script para agendar num job.

USE [DB_Consulti]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[sp_Restore]
@DBOrigem = N’BD_Origem’,
@DBDestino = N’BD_Destino’,
@CaminhoFull = N’H:\BACKUP DBs\BACKUPS DOS BANCOS\FULL\’,
@CaminhoDiff = N’H:\BACKUP DBs\BACKUPS DOS BANCOS\DIFF\’,
@CaminhoLog = N’H:\BACKUP DBs\BACKUPS DOS BANCOS\LOG\’,
@ServerRemoto = N’Ip_do_Servidor’,
@User = N’consulti’,
@Pwd = N’Senha_Login’

As variáveis @CaminhoFull, @CaminhoDiff e @CaminhoLog podem ser opcionais a depender da estrutura de cada ambiente se for uma restauração local.

Bem, acho que por hoje é só. Espero que aproveitem o script e aprimorem para compartilhar. Não esqueçam de destacar os méritos a meu sócio Walmik, pois é de criação dele a Sp_Restore.

Abraços à todos.

Anúncios

Erro ao gerar dados de performance para Azure SQL Database Calculator

Olá pessoal, quanto tempo heim?? Quero compartilhar com vocês um dilema que eu e meu sócio (Walmik Villas-Bôas) passamos recentemente quando fomos analisar um cenário de um cliente, no qual pretende migrar seu ambiente local de bancos  de dados SQL Server para nuvem Azure. Seguindo os protocolos de análise, a primeira coisa que fazemos quando recebemos uma demanda dessa espécie é certificar quanto de recurso é consumido pelo workload do cliente. No mercado existem diversas ferramentas/scripts que fazem esse trabalho e trazem resultados bem elaborados de consumo de recursos pelo servidor de SQL Server. No entanto costumamos usar um script bem simples e difundido pela comunidade de SQL Server, desenvolvido pelo Justin Henriksen, onde coleta informações por certo período de tempo dos contadores: Processor – % Processor Time, Logical Disk – Disk Reads/sec, Logical Disk – Disk Writes/sec e Database – Log Bytes Flushed/sec. São as métricas cruciais para carregarmos na calculadora do azure e termos uma visão geral dos resultados com recomendação do modelo a migrar nosso ambiente. Porém o script postado pelo nosso colega Justin não atende todos os ambientes pelo mundo afora e necessita algumas vezes de adaptações para cumprir com seu objetivo. Nesse cliente em específico foi nos retornado o erro que até então não tínhamos passado, onde a mensagem dizia que o contador de desempenho % Processor Time não existia na categoria de Processor. Nos pareceu estranho, pois é um contador comum de qualquer sistema operacional, mas tem um detalhe bem importante que deveríamos ter observado antes, o idioma do sistema operacional e da plataforma de dados. Analisando com cautela e aprofundando na pesquisa percebemos que é usada a descrição do display do contador, o qual algumas vezes pode mudar a depender do idioma. Atenção em observar não só o idioma do sistema operacional, mas também do SQL Server.

Com as mudanças realizadas nos scripts do PowerShell e do Command tudo funcionou perfeitamente.

Quero deixar claro que os scripts não são as únicas formas de coleta de dados de desempenho para ser usado na calculadora do Azure. É possível utilizar o Perfmon do sistema operacional também. Basta apenas definir os contadores na mesma sequência que citei acima e definir para que a coleta seja armazenada em arquivo csv no formato de log “Separado por vírgula”.

Por hoje é só. Espero que aproveitem a dica.

Bom carnaval à todos.

Se beber não dirija. Se dirigir não beba!!

Fonte original do script: https://justinhenriksen.wordpress.com/

Azure SQL Database DTU Calculator: http://dtucalculator.azurewebsites.net/

SqlDtuPerfmonPtr – Command

<?xml version=”1.0″ encoding=”utf-8″?>
<configuration>
<appSettings>
<!– PROCESSOR COUNTER –>
<add key=”ProcessorCategory” value=”Processador”/>
<add key=”ProcessorInstance” value=”_Total”/>
<add key=”ProcessorCounter” value=”% tempo de processador”/>
<!– DISK COUNTERS –>
<add key=”DiskCategory” value=”LogicalDisk”/>
<add key=”DiskInstance” value=”_Total”/>
<add key=”DiskCounter1″ value=”Disk Reads/sec”/>
<add key=”DiskCounter2″ value=”Disk Writes/sec”/>
<!– SQL COUNTER –>
<add key=”SqlCategory” value=”SQLServer:Databases”/>
<add key=”SqlInstance” value=”_Total”/>
<add key=”SqlCounter” value=”Log Bytes Flushed/sec”/>
<!– SECONDS TO WAIT FOR COLLECTING COUNTERS –>
<add key=”SampleInterval” value=”1″/>
<!– TOTAL NUMBER OF SAMPLES TO COLLECT –>
<add key=”MaxSamples” value=”3600″/>
<!– DISK LOCATION OF THE OUTPUT FILE –>
<add key=”CsvPath” value=”C:\sql-perfmon-log.csv”/>
</appSettings>
<startup>

<supportedRuntime version=”v4.0″ sku=”.NETFramework,Version=v4.0″/></startup>
</configuration>

SqlDtuPerfmonEnglish – Command

<?xml version=”1.0″ encoding=”utf-8″?>
<configuration>
<appSettings>
<!– PROCESSOR COUNTER –>
<add key=”ProcessorCategory” value=”Processor”/>
<add key=”ProcessorInstance” value=”_Total”/>
<add key=”ProcessorCounter” value=”% Processor Time”/>
<!– DISK COUNTERS –>
<add key=”DiskCategory” value=”LogicalDisk”/>
<add key=”DiskInstance” value=”_Total”/>
<add key=”DiskCounter1″ value=”Disk Reads/sec”/>
<add key=”DiskCounter2″ value=”Disk Writes/sec”/>
<!– SQL COUNTER –>
<add key=”SqlCategory” value=”SQLServer:Databases”/>
<add key=”SqlInstance” value=”_Total”/>
<add key=”SqlCounter” value=”Log Bytes Flushed/sec”/>
<!– SECONDS TO WAIT FOR COLLECTING COUNTERS –>
<add key=”SampleInterval” value=”1″/>
<!– TOTAL NUMBER OF SAMPLES TO COLLECT –>
<add key=”MaxSamples” value=”3600″/>
<!– DISK LOCATION OF THE OUTPUT FILE –>
<add key=”CsvPath” value=”C:\sql-perfmon-log.csv”/>
</appSettings>
<startup>

<supportedRuntime version=”v4.0″ sku=”.NETFramework,Version=v4.0″/></startup>
</configuration>

sql-perfmon-ptbr – PowerShell

<#
.SYNOPSIS
Collect counters required for DTU Calculator and log as CSV.

.DESCRIPTION
Collect counters required for DTU Calculator and log as CSV.
Default log file location is C:\sql-perfmon-log.csv.
Counters are collected at 1 second intervals for 1 hour or 3600 seconds.
No support or warranty is supplied or inferred.
Use at your own risk.

.PARAMETER DatabaseName
The name of the SQL Server database to monitor.

.INPUTS
Parameters above.

.OUTPUTS
None.

.NOTES
Version: 1.0
Creation Date: May 1, 2015
Modified Date: June 17, 2016
Author: Justin Henriksen ( http://justinhenriksen.wordpress.com )
#>

Set-ExecutionPolicy -Scope Process -ExecutionPolicy Unrestricted -Force

$ErrorActionPreference = “Stop”
$VerbosePreference = “Continue”

cls

Write-Output “Collecting counters…”
Write-Output “Press Ctrl+C to exit.”

$counters = @(“\Processador(_Total)\% tempo de processador”,
“\LogicalDisk(_Total)\Leituras de disco/s”,
“\LogicalDisk(_Total)\Gravações de disco/s”,
“\SQLServer:Databases(_Total)\Solicitações do Pool de Logs/s”)

Get-Counter -Counter $counters -SampleInterval 1 -MaxSamples 3600 |
Export-Counter -FileFormat csv -Path “C:\sql-perfmon-log.csv” -Force

sql-perfmon-Eng – PowerShell

<#
.SYNOPSIS
Collect counters required for DTU Calculator and log as CSV.

.DESCRIPTION
Collect counters required for DTU Calculator and log as CSV.
Default log file location is C:\sql-perfmon-log.csv.
Counters are collected at 1 second intervals for 1 hour or 3600 seconds.
No support or warranty is supplied or inferred.
Use at your own risk.

.PARAMETER DatabaseName
The name of the SQL Server database to monitor.

.INPUTS
Parameters above.

.OUTPUTS
None.

.NOTES
Version: 1.0
Creation Date: May 1, 2015
Modified Date: June 17, 2016
Author: Justin Henriksen ( http://justinhenriksen.wordpress.com )
#>

Set-ExecutionPolicy -Scope Process -ExecutionPolicy Unrestricted -Force

$ErrorActionPreference = “Stop”
$VerbosePreference = “Continue”

cls

Write-Output “Collecting counters…”
Write-Output “Press Ctrl+C to exit.”

$counters = @(“\Processor(_Total)\% Processor Time”,
“\LogicalDisk(_Total)\Disk Reads/sec”,
“\LogicalDisk(_Total)\Disk Writes/sec”,
“\SQLServer:Databases(_Total)\Log Bytes Flushed/sec”)

Get-Counter -Counter $counters -SampleInterval 1 -MaxSamples 3600 |
Export-Counter -FileFormat csv -Path “C:\sql-perfmon-log.csv” -Force

 

 

Mapeando uma letra no SSMS – SQL Server

Olá galera, tudo bem? Ando sumido do blog, mas são as demandas do dia a dia atribulados como sempre, no qual tenho agradecido muito de estar assim (risos!!!). Sempre lembro que é nessa agonia e correria que tenho aprendido muito com o SQL Server. Não só nos bate papos com colegas e amigos da área, mas em estudos e pesquisas em livros, fóruns e blogs em busca de solucionar problemas. Tentarei ser bem rápido neste post, pois passei apenas para comentar sobre um comando muito conhecido da galera de TI e que tem me ajudado bastante na construção de algumas rotinas administrativas dos ambientes que administro. É ele o xp_cmdshell, onde o mesmo nada mais é um comando do Windows que passa uma cadeia de caracteres para execução. Tenho utilizado para definir mapeamentos de redes na plataforma SQL Server, no qual posso apontar meus backups para serem alocados em locais distintos na rede e até mesmo restaurados desses locais. Abaixo segue um exemplo de como criar um mapeamento usando a letra P.

Inicialmente devemos habilitar o comando xp_cmdshell, onde esse vem desabilitado por default no SQL Server. Executando a consulta abaixo no SSMS estaremos habilitando a opção Show Advanced Options que nos permitirá ter acesso a xp_cmdshell.

use master
go

exec sp_configure ‘Show Advanced Options’,1
reconfigure
go

exec sp_configure ‘xp_cmdshell’,1
reconfigure
go

sp_conf

xp_cmd

Posteriormente devemos definir a letra do mapeamento e o caminho que ficará exposto no SQL Server através do comando abaixo.

exec xp_cmdshell ‘net use P: \\192.168.1.191\backup’

Após configurarmos o mapeamento é possível conferirmos nas propriedades da instancia em Configurações de Banco de Dados -> Locais Padrão de Banco de Dados como mostra a imagem abaixo.

resultado

Por fim é importante desabilitarmos as opções default do SQL Server com os comandos:

use master
go

exec sp_configure ‘xp_cmdshell’,0
reconfigure
go

exec sp_configure ‘Show Advanced Options’,0
reconfigure
go

Se eventualmente errou a letra ou quer alterar a mesma é passível também de exclusão do mapeamento seguindo os passos:

use master
go

exec sp_configure ‘Show Advanced Options’,1
reconfigure
go

exec sp_configure ‘xp_cmdshell’,1
reconfigure
go

exec xp_cmdshell ‘Net use P: /delete’

exec sp_configure ‘xp_cmdshell’,0
reconfigure
go

exec sp_configure ‘Show Advanced Options’,0
reconfigure
go

Por hoje é só. Espero que aproveitem a dica e aprimorem para compartilhar.

Bom final de semana à todos.

Continuar lendo “Mapeando uma letra no SSMS – SQL Server”

Encontrando as consultas mais lentas no servidor

Olá pessoal, tudo bem? Essa semana vou contribuir com um script bem útil para quem trabalha com tuning de consultas. Existem diversos aspectos que podem comprometer o desempenho num banco de dados. Já é de conhecimento de todos que grande parte desses problemas de performance que encontramos está muito relacionado às consultas mal modeladas. Bem, no primeiro instante que recebemos um reclamação de lentidão no ambiente de dados, por recomendação, é sempre bom partirmos para a análise das esperas presentes na instância, para diante dos resultados, identificarmos quais os caminhos devemos tomar para solucionar o problema. Porém, não será esse o nosso propósito neste poste. Deixarei essas questões para os próximos postes. Abaixo considero a identificação das cincos consultas mais lentas no servidor.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT TOP 5
CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))
AS [Total Elapsed Duration (s)]
, qs.execution_count
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE
qs.statement_end_offset
END – qs.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
INNER JOIN sys.dm_exec_cached_plans cp
ON qs.plan_handle=cp.plan_handle
ORDER BY total_elapsed_time DESC

Um exemplo abaixo mostra o que a consulta gera como resultado.

23

Percebam que o retorno do resultado traz informações importantes, como duração decorrida da consulta, quantidade de vezes que foi executada, a consulta em si e o plano em cache, no qual considero ser o dado mais importante neste script, pois é através dele que poderemos analisar de forma coerente as decisões tomadas pelo SQL Server para trazer os Results Sets, além de nos dar uma ideia de como é possível melhoramos as consultas.

Por hoje é só, pois preciso também de descanso. (Risos!!).

Bom feriado à todos.

Estimando o tempo final dos jobs no SQL Server

Olá pessoal, como têm indo a semana? Antes de começar a escrever quero que possam refletir sobre a seguinte mensagem: “O conhecimento não é pra ser guardado a sete chaves e sim pra ser compartilhado, dividido, pois conhecimento guardado é igual dinheiro num cofre, perde o valor e não rende nada.”

Essa senana quero falar rapidinho sobre uma dica que costumo usar as vezes nos ambientes que administro. Quem nunca se perguntou ou foi perguntado: “Em quanto tempo conclui o job que mandou rodar?”. E ai o que você faz? Com o script abaixo será possível programar e informar aos usuários quando a tarefa em execução será concluída. Ressaltando mais uma vez sobre a importância da leitura do livro DMVs SQL Server em Ação.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT r.percent_complete
, DATEDIFF(MINUTE, start_time, GETDATE()) AS Age
, DATEADD(MINUTE, DATEDIFF(MINUTE, start_time, GETDATE()) /
percent_complete * 100, start_time) AS EstimatedEndTime
, t.Text AS ParentQuery
, SUBSTRING (t.text,(r.statement_start_offset/2) + 1,
((CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2
ELSE r.statement_end_offset
END – r.statement_start_offset)/2) + 1) AS IndividualQuery
, start_time
, DB_NAME(Database_Id) AS DatabaseName
, Status
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE session_id > 50
AND percent_complete > 0
ORDER BY percent_complete DESC

 

Percebam a junção da DMV e DMF, pois é através delas que encontramos informações suficientes para determinar o término dos jobs administrativos que rodamos em nosso ambiente. Vou explicitar apenas o conceito da sys.dm_exec_requests, pois a sys.dm_exec_sql_text foi adicionada no quadro do artigo anterior. Caso queira dar uma olhada Exibindo as últimas consultas SQL em execução.

DMV Descrição
sys.dm_exec_requests

Contém detalhes de pedidos em execução no SQL Server.

Um exemplo abaixo mostra o que a consulta gera como resultado.

Sem título

As colunas apresentam informações sobre o percentual decorrido do job, o tempo em minutos de quanto o trabalho foi executado, o tempo final estimado, a instrução executado no job, o horário de ínicio e a base de dados envolvida.

Bem, por hoje acho que é só. Espero que o script possa contribuir no dia a dia dos profissionais mais experientes e iniciantes.

Abraços, até a próxima!

Exibindo as últimas consultas SQL em execução

Olá pessoal,  tudo bem? Espero que tenham tido uma semana de muitos problemas no ambiente de vocês (risos), no qual sempre nos deixa super apreensivos. Afinal de contas, o problema faz parte da nossa rotina, como profissional de tecnologia, assim dizia a lei de Murphy “Se alguma coisa tem chances de sair errado certamente sairá, e da pior maneira possível!“. Por isso a necessidade de nos aperfeiçoarmos sempre para estarmos preparados para definir soluções eficazes e eficientes no ambiente que estamos responsáveis. Busquemos viver os momentos difíceis como uma grande oportunidade de aprender e crescer na profissão de escolhemos seguir. São desses momentos de caus que adquirimos experiências fantásticas e descobrirmos que o profissional experiente é aquele que encara o problema de frente sem medo dos riscos e desafios, seguro que pode dar conta do recado.

Bem, a partir de hoje irei iniciar uma série de exposição de consultas que certamente serão de grande utilidade no dia a dia de um administrador de banco de dados. Serão postagens curtas e objetivas, no qual abordaram informações que podem ser importantes na determinação do comportamento de usuários como até teste de desempenho de um ambiente.

Confesso que a leitura do livro DMVs SQL Server em ação, escrito por Ian W. Stirk tem me ajudado bastante em meu trabalho. Considero um livro de fundamental importância para quem trabalha com administração de bancos de dados SQL Server, pois através de uma gramática de fácil compreensão e de scripts escritos de forma muito simples, tem facilitado bastante a minha vida.

Hoje irei aborda uma instrução que trás informação detalhadas das últimas consultas executadas em um banco de dados. Quem nunca se deparou com o seguinte argumento do usuário: “…o único comando que executei foi…”. Nós sabemos que as vezes sempre a uma discrepância no que o usuário diz quando queremos extrair algo dele para nos ajudar a resolver determinado problema e com o que realmente foi feito por ele.

O script abaixo nos ajudará a eliminar essa diferença.

SELECT c.session_id, s.host_name, s.login_name, s.status
, st.text, s.login_time, s.program_name, *
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS st
ORDER BY c.session_id

Percebam que estamos usando na consulta objetos DMVs e DMF. É a junção delas que nos trás as informações que precisamos analisar.

DMVs/DMF Descrição
sys.dm_exec_connections Contém detalhes de conexão do SQL Server.
sys.dm_exec_sessions Contém detalhes de sessões do SQL Server
sys.dm_exec_sql_text DMF que retorna ao texto SQL identificado por um lado sql_handle.

Um exemplo abaixo mostra o que a consulta gera como resultado.

1

Por hoje é só. Espero que o script possa contribuir no dia a dia de vocês.

Até a próxima!

Quantificando o impacto da atualização no sistema

Olá galera, segue um script bem interessante que extrair da leitura do livro DMVs SQL Server em ação, escrito por Ian W. Stirk. É um script útil no dia a dia de muitos administradores de banco, pois nos auxilia a quantificar o impacto que uma atualização pode causar no ambiente de dados. O uso do script é bem simples, executa  a consulta antes de qualquer atualização, seja de substituição de versão do SQL Server ou um service pack, salvar a saida num arquivo excel  e após a atualização utiliza o mesmo script para comparar os resultados e compreender se a mudança foi benéfica ou prejudicial. Para os administradores que se preocupam em acompanhar as atualizações de seus servidores fica a dica.

Abraços!

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
total_elapsed_time, total_worker_time, total_logical_reads
, total_logical_writes, total_clr_time, execution_count
, statement_start_offset, statement_end_offset, sql_handle, plan_handle
INTO #prework
FROM sys.dm_exec_query_stats

SELECT
total_elapsed_time, total_worker_time, total_logical_reads
, total_logical_writes, total_clr_time, execution_count
, statement_start_offset, statement_end_offset, sql_handle, plan_handle
INTO #postwork
FROM sys.dm_exec_query_stats

SELECT
SUM(p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0)) AS [TotalDuration]
, SUM(p2.total_worker_time – ISNULL(p1.total_worker_time, 0)) AS [Total Time on CPU]
, SUM((p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0)) –
(p2.total_worker_time – ISNULL(p1.total_worker_time, 0))) AS [Total Time Waiting]
, SUM(p2.total_logical_reads – ISNULL(p1.total_logical_reads, 0)) AS [TotalReads]
, SUM(p2.total_logical_writes – ISNULL(p1.total_logical_writes, 0)) AS [TotalWrites]
, SUM(p2.total_clr_time – ISNULL(p1.total_clr_time, 0)) AS [Total CLR time]
, SUM(p2.execution_count – ISNULL(p1.execution_count, 0)) AS [Total Executions]
, DB_NAME(qt.dbid) AS DatabaseName
FROM #prework p1
RIGHT OUTER JOIN
#postwork p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset =ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
GROUP BY DB_NAME(qt.dbid)

SELECT
SUM(p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0)) AS [TotalDuration]
, SUM(p2.total_worker_time – ISNULL(p1.total_worker_time, 0)) AS [Total Time on CPU]
, SUM((p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0))
– (p2.total_worker_time – ISNULL(p1.total_worker_time, 0))) AS [Total Time Waiting]
, SUM(p2.total_logical_reads – ISNULL(p1.total_logical_reads, 0)) AS [TotalReads]
, SUM(p2.total_logical_writes – ISNULL(p1.total_logical_writes, 0)) AS [TotalWrites]
, SUM(p2.total_clr_time – ISNULL(p1.total_clr_time, 0)) AS [Total CLR time]
, SUM(p2.execution_count – ISNULL(p1.execution_count, 0)) AS [Total Executions]
, DB_NAME(qt.dbid) AS DatabaseName
, qt.text AS [Parent Query]
FROM #prework p1
RIGHT OUTER JOIN
#postwork p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset =ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
GROUP BY DB_NAME(qt.dbid), qt.text
ORDER BY [TotalDuration] DESC

SELECT
p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0) AS [TotalDuration]
, p2.total_worker_time – ISNULL(p1.total_worker_time, 0) AS [Total Time on CPU]
, (p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0))
– (p2.total_worker_time – ISNULL(p1.total_worker_time, 0)) AS [Total Time Waiting]
, p2.total_logical_reads – ISNULL(p1.total_logical_reads, 0) AS [TotalReads]
, p2.total_logical_writes – ISNULL(p1.total_logical_writes, 0) AS [TotalWrites]
, p2.total_clr_time – ISNULL(p1.total_clr_time, 0) AS [Total CLR time]
, p2.execution_count – ISNULL(p1.execution_count, 0) AS [Total Executions]
, SUBSTRING (qt.text,p2.statement_start_offset/2 + 1,
((CASE WHEN p2.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE p2.statement_end_offset
END – p2.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
FROM #prework p1
RIGHT OUTER JOIN
#postwork p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset = ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset = ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
ORDER BY [TotalDuration] DESC

DROP TABLE #prework
DROP TABLE #postwork