COMO MONITORAR FALHAS DE CONECTIVIDADE USANDO O SQL SERVER.

Hoje, irei dar uma dica de como usar o SQL Server para monitorar as indisponibilidades de rede existentes em nosso servidor.
É bastante comum nos depararmos com situações onde precisamos descobrir se um determinado problema está relacionado com uma indisponibilidade de rede momentânea ou não.
Existem diversas ferramentas para realização de monitoramento de recursos de rede, uma delas é o hostmonitor. Existem outras dezenas de ferramentas, mas se você não dispõe de uma ferramenta de monitoramento ou, simplesmente, deseja realizar um simples teste com o próprio SQL Server, então, você vai gostar dessa dica.
Bom, para monitorar eventuais problemas de conectividade em uma instância com o SQL Server, podemos realizar um teste simples usando o SSMS ou o QA (Query analyze), para isso, podemos executar os seguintes comandos:
while 1 <> 2
begin
select getdate()
waitfor delay ’00:00:01
end
Executando os comandos acima, o SQL Server irá entrar no loop cada vez que houver uma indisponibilidade de link e irá esperar 1 segundo para executar nova verificação. No script acima, quando houver uma indisponibilidade, será logada a data e hora da indisponibilidade, mas, você pode usar a imaginação e adaptar o script para realizar outas tarefas, como alertas, logar as informações de indisponibilidade em tabela ou em arquivos….
No Exemplo abaixo(Tela 1.1), vemos o script em ação:
Podemos constatar que, quando houve indisponibilidade do link,  foi registrado um evento como resultado de um simples select. Vale resaltar que o script ficará infinitamente em execução até a interrupção do usuário ou alteração da lógica do script.
(Tela 1.1)
Para automatizar nosso monitoramento, podemos criar um arquivo contendo nosso script de verificação de indisponibilidade e juntamente com o comando SQLCMD ou OSQL, conectar a algum servidor para executar o nosso script.
No exemplo abaixo, podemos ver que utilizo os seguintes parâmetros no comando SQLCMD(Tela 1.2):
-S, para informar o nome do servidor.
-E, para informar que a conexão com o servidor será como “trust connection”, ou seja, com o usuário que estou logado.
-i, para informar o arquivo de “input”, que no nosso caso é o script de monitoramento(“C:monitoramentomonitora.sql”).
-o, para informar um arquivo de saída, ou seja, o nosso arquivo de log dos eventos de indisponibilidade
(Tela 1.2)
Bom, espero que tenha ficado clara e objetiva essa demonstração.
Até a próxima!
Abraço, Emanuel Peixoto

COMO REALIZAR UM DEPLOY DE VÁRIOS PACOTES SSIS PARA O MSDB USANDO O DEPLOYMENT UTILITY

É como muito prazer que estou fazendo essa demonstração, devido a uma dúvida que surgiu no fórum do MSDN (http://forums.microsoft.com/MSDN-BR/ShowPost.aspx?PostID=4227104&SiteID=21).
A dúvida seria a respeito de importar para o MDSB vários pacotes criados em um projeto do BIDS.
Bom, a solução para esse caso é a utilização do utilitário de deploy do BIDS(DeploymentUtility)
Segue um passo-a-passo que preparei:
No nosso exemplo, iremos realizar o deploy dos pacotes existentes em um projeto de teste do BIDS(Tela 1.1)
Tela 1.1
O primeiro passo é tornar a funcionalidade de deployment “ativada”, para isso, devemos recorrer às propriedades do nosso projeto e ativá-las setando a opção “createDeploymentUtility” como “True” e configurar também o local onde serão armazenados os arquivos com os metadados dos pacotes e o arquivo de manifeto.
Após habilitarmos esse recurso de deploy, podemos realizar um rebuild em nosso projeto para que seja criado o manifestfile, juntamente com os arquivos *.dtsx do nosso projeto (Tela 1.3).
Tela 1.3
Feito isso, podemos constatar que foram gerados os arquivos de deploy no destino desejado, ou seja, no destino configurado nas propriedade do DeploymentUtility do BIDS.(Tela 1.4).
Tela 1.4
Pronto, agora basta executar o arquivo de manifesto para dar início ao deploy.
No nosso caso, o utilitário criou o arquivo “Teste deploy MSDB.SSISDeploymentManifest”, ou seja, o arquivo de manifesto sempre será criado tendo como prefixo o nome do projeto.
Ao executá-lo, irá iniciar o utilitário de deploy (tela 1.5).
Tela 1.5
Nesse ponto da instalação, poderemos escolher onde desejamos armazenar nossos pacotes, no nosso caso, escolheremos realizar o deploy dos pacotes no SQL Server.
Feito isso, o próximo passo é definir os parâmetros de conexão, como nome do servidor, credenciais, Bla,bla,blá…. (Tela 1.6)
Tela 1.6
A partir de agora, podemos avançar e aguardar o wizard finalizar.
Ao logarmos no SQL Server Integration Services através do SSMS, iremos verificar que nossos pacotes foram importados com sucesso (Tela 1.7)
Tela 1.7
Pronto, agora basta aplicar os conceitos aprendidos nesse exemplo para criar rotinas de deploy.
Até a proxima….
Emanuel Peixoto.

BACKUP CUSTOMIZADO

Devido a existência de dúvidas constantes com procedimentos de backup no fórum MSDN e TECHNET, então, resolvi criar uma rotina customizada de backup para compartilhar com nossa comunidade de SQL Server.
 
O código, abaixo, avalia os arquivos existentes no local de armazenamento do backup e, de acordo com o período de retenção(permanência) dos backups, os arquivos que estiverem fora do escopo de retenção serão removidos e, em seguida, é iniciada a rotina de backup, sendo possível definir que tipo de backup será realizado (full, diferencial, log). 
 
O exemplo, abaixo, reproduz uma das formas de realização de backups, podendo ser melhorada ou apenas servir de base para novas procedimentos.
 
Para usar o exemplo, basta executar o script abaixo para criar a procedure "PR_BACKUP" e chamá-lo por um job. No final do script existe um exemplo de como executar a procedure para a realização de backups.
 
/*#### CRIA PROCEDURE DE BACKUP CUSTOMIZADO ####*/
 

use

master

go

if

exists(select 1 from sysobjects where name = ‘pr_backup’)

drop

procedure pr_backup

go

create

proc pr_backup

@database

varchar(50), — Informe o database para backup

@localbackup

varchar(500) = ‘C:’ , — Caminho default de backup, caso não seja declarado na chamada da procedure.

@tipoBackup

INT = 1, — Seleciona o tipo de backup. 1 = FULL , 2 = DIFERENCIAL , 3 = LOG

@retencao

int = 30

as

 

/* DECLARA VARIÁVEIS DA ROTINA */

 

create

table #backups (arquivos varchar(500))

declare

@data varchar(12)

declare

@cmd varchar(500) — Número de dias que serão ignorados os backups anteriores

declare

@tipoBackup_name varchar(15)

declare

@dataremocao varchar(12)

set

@dataremocao=CONVERT(varchar(12),dateadd(day,- @retencao,getdate()),112)

if

@tipoBackup = 1

begin

SET @tipoBackup_name = ‘Full’

END

if

@tipoBackup = 2

begin

SET @tipoBackup_name = ‘Diferencial’

END

if

@tipoBackup = 3

begin

SET @tipoBackup_name = ‘Log’

END

 
 

— ROTINA REMOVE BACKUP MAIOR QUE X DIAS

 

set

@cmd

= ‘exec xp_cmdshell ‘+ ””+‘dir ‘+@localbackup+@database+‘-‘ +@tipoBackup_name+‘*.bkp’+””

insert
into

#backups

exec

(

@cmd)

declare

@arq

varchar(100)

declare

arq

cursor for

select

substring(substring(arquivos,charindex(@database,arquivos)+LEN(@database)+LEN(@tipoBackup_name)+1,(LEN(arquivos)-(charindex(@database,arquivos)+LEN(@tipoBackup_name)))),1,12) as arquivos from #backups

where

(

charindex(@database,arquivos)<>0 and charindex(@database,arquivos) is not null)

and

convert

(datetime,substring(substring(arquivos,charindex(@database,arquivos)+LEN(@database)+LEN(@tipoBackup_name)+1,(LEN(arquivos)-(charindex(@database,arquivos)+LEN(@tipoBackup_name)))),1,8)) <= @dataremocao

open

arq

fetch

next from arq into @arq

while

@@FETCH_STATUS = 0

begin
set

@cmd=‘xp_cmdshell ”del ‘+@localbackup+ @database+‘-‘+@tipoBackup_name+@arq+‘.bkp”’

EXEC

(@cmd)

fetch

next from arq into @arq

end
close

arq

deallocate

arq
 
 

/* ROTINA REALIZA BACKUP */

 
 

if

@tipoBackup = 1

begin

SET @tipoBackup_name = ‘Full’

set @data=CONVERT(varchar(14),GETDATE(),112)+REPLACE(CONVERT(VARCHAR(5),GETDATE(),108),‘:’,)

set @cmd = ‘Backup database ‘+@database + ‘ to disk = ‘+””+@localbackup++@database+‘-‘+ @tipoBackup_name +@data+‘.bkp’+””

exec (@cmd)

print

print

print ‘Backup do database ‘+ @database + ‘Realizado em ‘+@localbackup++@database+‘-‘+@tipoBackup_name +@data+‘.bkp’

 

END

if

@tipoBackup = 2

begin

SET @tipoBackup_name = ‘Diferencial’

set @data=CONVERT(varchar(14),GETDATE(),112)+REPLACE(CONVERT(VARCHAR(5),GETDATE(),108),‘:’,)

set @cmd = ‘Backup database ‘+@database + ‘ to disk = ‘+””+@localbackup + +@database+‘-‘+ @tipoBackup_name +@data+‘.bkp’+”’ WITH DIFFERENTIAL’

exec (@cmd)

print

print

print ‘Backup do database ‘+ @database + ‘Realizado em ‘+@localbackup+ +@database+‘-‘+@tipoBackup_name +@data+‘.bkp’

END

if

@tipoBackup = 3

begin

SET @tipoBackup_name = ‘Log’

set @data=CONVERT(varchar(14),GETDATE(),112)+REPLACE(CONVERT(VARCHAR(5),GETDATE(),108),‘:’,)

set @cmd = ‘Backup LOG ‘+@database + ‘ to disk = ‘+””+@localbackup+ +@database+‘-‘+ @tipoBackup_name +@data+‘.bkp’+””

exec (@cmd)

print

print

print ‘Backup do LOG do database ‘+ @database + ‘ Realizado em ‘+@localbackup+ +@database+‘-‘+@tipoBackup_name +@data+‘.bkp’

END

go

 
 
 
 
/*#### EXEMPLO DE COMO EXECUTAR PROCEDURE DE BACKUP ####*/

 
/* 

exec

pr_backup

@database = ‘teste’,   — Nome do database
@localbackup = ‘D:’,  –Caso não seja declarado, o local do backup será em "C:"
@tipoBackup = 1,        — 1 = Full, 2 = Diferencial, 3 = Log
@retencao = 7          –Dias de retenção de backups, caso esse parâmetro seja omitido o padrão é 30 dias.
*/
 
 
Observações:
Para backups diferenciais e de log, é necessário configurar o recovery model do banco para bulk-logged ou full.
 
 
Até a próxima….
 
Emanuel Peixoto.

DOCUMENTAÇÃO DE PROCEDURES E TRIGGERS

Deparei-me com o seguinte desafio no Fórum do MSDN: backup da base de dados SQL Server em um Web Hosting.
Como a única opção fornecida pelo fornecedor do serviço era uma exportação de tabelas, os objetos como procedures, triggers, functions views, dentre outros, não poderiam ser exportados, então sugeri a criação de uma tabela de documentação desses objetos.
 
Existe também o "Database Publishing Wizard", que pode gerar um script de todo o schema do banco e dar suporte a web hosting.
 
Segue script.
 
/* ##### CRIA TABELA DE CONTROLE (DOCUMENTAÇÃO) #####*/
if not exists(select 1 from sysobjects where name = ‘documentacao’ )
create table documentacao
(objetoid int primary key,
nome_objecto varchar(250),
Objeto_referencia varchar(250),
tipo char(1),
dt_criacao datetime,
script text)
go
/* ##### CRIA PROCEDURE COM INSTRUÇÕES PARA DOCUMENTAÇAO #####*/
create proc pr_documentacao
as
set nocount on
declare @cmd varchar(1000),@objeto varchar(250),@objeto_referencia varchar(250),@criacao datetime,@id int,@tipo char(1),@script varchar(8000)
declare obj cursor for
select o.id,o.name,object_name(o.parent_obj),o.crdate,o.xtype,c.text from sysobjects o, syscomments c where c.id=o.id
open obj
fetch next from obj into @id,@objeto,@objeto_referencia,@criacao,@tipo,@script
while @@fetch_status=0
begin
insert into documentacao(objetoid,nome_objecto,Objeto_referencia,dt_criacao,tipo,script)
select @id,@objeto,@objeto_referencia,@criacao,@tipo,@script where @id not in(select objetoid from documentacao )
fetch next from obj into @id,@objeto,@objeto_referencia,@criacao,@tipo,@script
end
close obj
deallocate obj
go
/* ##### Executa procedure para documentar objetos #####*/
exec pr_documentacao
 
Até mais,
Emanuel Peixoto.

TRIGGER DE AUTO-INCREMENTO

 

create table tel_pac
(cod_pac int not null,
tel int not null)
go
alter
table tel_pac add constraint pk_codpac primary key(cod_pac)
go
create
trigger auto_num
on tel_pac
instead of insert
as
declare
@cod int,@tel int
select
@cod = case when max(cod_pac) is null then 1 else max(cod_pac)+1 end from tel_pac(xlock)
select @tel=i.tel from inserted i
insert into tel_pac(cod_pac,tel) values (@cod,@tel)

VISUALISAR SALDO

Bom, estou postando aqui, um exemplo de como realizar uma consulta de saldo.
Existem diversas formas de se realizar essa consulta, mas segue aqui um pequeno exemplo.
 
 

create

table mov_data

(

codigo int identity,

codigo_conta

int not null,

tipo

char(1),

data

datetime,

valor

float)

go
insert

into mov_data(codigo_conta,tipo,data,valor) values(1,‘C’,’10/10/2008′,100)

insert

into mov_data(codigo_conta,tipo,data,valor) values(1,‘C’,’10/10/2008′,100)

insert

into mov_data(codigo_conta,tipo,data,valor) values(1,‘D’,’11/10/2008′,50)

insert

into mov_data(codigo_conta,tipo,data,valor) values(1,‘D’,’12/10/2008′,100)

insert

into mov_data(codigo_conta,tipo,data,valor) values(2,‘D’,’12/10/2008′,100)

insert

into mov_data(codigo_conta,tipo,data,valor) values(2,‘D’,’12/10/2008′,100)

insert

into mov_data(codigo_conta,tipo,data,valor) values(2,‘D’,’12/10/2008′,100)

insert

into mov_data(codigo_conta,tipo,data,valor) values(2,‘C’,’12/10/2008′,300)

go

/* ##### Visualiza saldo #####*/

select

a.codigo,a.codigo_conta,a.tipo,a.data,a.valor,

(select sum(b.valor)

from (select codigo,codigo_conta,case when tipo = ‘C’ then valor else valor end valor from mov_data) b

where a.codigo>=b.codigo and a.codigo_conta=b.codigo_conta) as Saldo

from

mov_data a

order

by a.codigo

 
 
Resultados do select:
Cod   CC   Tipo Data     Valor   Saldo
1     1    C    20081010 100     100
2     1    C    20081010 100     200
3     1    D    20081110 50      150
4     1    D    20081210 100     50
5     2    D    20081210 100     -100
6     2    D    20081210 100     -200
7     2    D    20081210 100     -300
8     2    C    20081210 300      0
 

REMOVER ACENTUAÇÃO USANDO REPLACE

 
 
 
— Cria Tabela de Exemplo

Create

Table #Textos (Indice int identity(1,1), Texto VarChar(1000))

–insere caracteres com acentos

Insert

Into #Textos (Texto) Values (‘teste teste é, teste teste á’)

Insert

Into #Textos (Texto) Values (‘teste teste é, ãõâêî teste á’)

Insert

Into #Textos (Texto) Values (‘teste ãõâêî é, teste teste á’)

Insert

Into #Textos (Texto) Values (‘teste teste á, ãõâêî’)

Insert

Into #Textos (Texto) Values (‘teste teste’)

— Inicio da Rotina

create

table #TabelaAcentos (Indice Int Identity(1,1), Acento char(01), SemAcento Char(01))

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘á’,‘a’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘é’,‘e’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘í’,‘i’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ó’,‘o’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ú’,‘u’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘à’,‘a’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘è’,‘e’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ì’,‘i’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ò’,‘o’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ù’,‘u’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ã’,‘a’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘õ’,‘o’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘â’,‘a’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ê’,‘e’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘î’,‘i’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ô’,‘o’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘û’,‘u’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ç’,‘c’)

declare

@id int,@id_acento int,@old_char char(1), @new_char char(1),@texto varchar(1000)

set

@id=1

while

@id <= (select COUNT(1) from #Textos)

begin
select

@texto=Texto from #Textos where indice=@id

set @id_acento=1

while @id_acento <= (select count(1) from #TabelaAcentos)

begin

select @old_char=Acento, @new_char=SemAcento from #TabelaAcentos where Indice=@id_acento

set @texto=REPLACE(@Texto,@old_char,@new_char)

set @id_acento=@id_acento+1

end

select @texto

set

@id=@id+1

end

ÍNDICES E ESTATÍSTICAS


Hoje, me deparei com uma dúvida de um dos participantes do fórum "FÓRUM DA RM" e essa dúvida era sobre como funcionavam os índices e estatísticas.

 

Vou tentar, explicar como funcionam as estatísticas e os índices. Primeiramente, vou me abster a contextualizar sobre os diferentes tipos de índices (clustered, nonclustered e unique), prometo que meu próximo assunto abordado dará ênfase aos tipos de estatísticas e como criá-los.

Os índices de uma tabela são como os índices de um livro, eles indicam onde encontrar o que você está procurando.

O SQL permite criar vários índices em uma mesma tabela, de forma que você pode organizar os dados na ordem que quiser, exemplo:
se você cria um índice que organiza os dados por data, no momento que realiza consultas por data, o resultado será o melhor possível, pois o SQL irá utilizar o índice que você criou por data.

Se criar outro índice que organiza a tabela por código do movimento, quando realizar consultas que pesquisem por ordem de código de movimento, a consulta irá ser rápida, pois os índices irão apontar para as informações de forma correta.

Mas, como o SQL sabe qual índice utilizar quando você está realizando uma consulta em uma tabela que possui mais de um índice?

Resposta: ESTATÍSTICAS!!!

As estatísticas são contadores que informam para o SQL quais são os melhores índices ou os índices mais rápidos. O SQL sabe quais são os melhores índices baseado num contador sobre os campos que compõem os índices, os índices que possuem campos com maior seletividade são escolhidos e a consulta executa feito uma BALA!!!
Para isso, você deve criar estatísticas para os índices, não adianta ter índices sem uma boa estatísticas.

Realizando uma verificação do plano de execução de qualquer consulta, você pode verificar a inexistência de índice e criá-los. Outra forma é o Index Tuning wizard(SQL2000) ou o DTA(SQL2005), que sugere ou cria índices e estatísticas automaticamente.

O FullScan comentado pelo colega Mafra, só é realizado quando não existem índices para os dados que estão sendo pesquisados, quando as estatísticas não existem, são ineficientes ou estão desatualizadas. Pra ver como a falta de estatísticas é crucial, imagine a seguinte situação:

Você chega em uma biblioteca e vai procurar o assunto "Atualização de estatísticas" pesquisando nos índices de todos os livros de informática relacionados com banco de dados, até encontrar um livro que tenha o assunto no índice pode demorar bastante. Apesar de todos os livros possuírem índices isso não quer dizer que você irá encontrar rapidamente o que quer.

Agora, se a biblioteca possui uma estatística, onde você pode realizar a seguinte consulta: quais os livros que possuem o assunto "Atualização de estatísticas" no índice? Se existir essa estatística, você irá direto para os livros certos, sem perder muito tempo.

Se, por acaso, não existirem as estatísticas, você até pode encontrar os livros que contém o assunto, porém, se não existir nem estatística e nem índices, você estará perdido! Terá que verificar todas as folhas de todos os livros para encontrar o assunto "Atualização de estatísticas”, rsrsrsrs.

Então, tão importante quanto criar índices e estatísticas é a manutenção delas. Realize, diariamente, reindexação dos índices de todas as tabelas e atualize as estatísticas também para todos os objetos.

 

Espero ter lhe ajudado,

 

Emanuel Peixoto.

 

 

 

Rumo à EsFCEx