SQL Server no Linux


Todos já devem estar sabendo dessa “novidade”

O link para o blog: https://blogs.microsoft.com/blog/2016/03/07/announcing-sql-server-on-linux

Se você quiser se cadastrar para “testar” o preview o link é: https://www.microsoft.com/en-us/server-cloud/sql-server-on-linux.aspx

Honestamente não sei se isso vai ser bom ou ruim,,, vejo muitas aplicações interessantes, mas também vejo muita dor de cabeça pela frente…

pode ser que eles usem isso para acabar com a versão Enterprise, forçando as empresas a usarem a nuvem caso queiram HA ou features avançadas? ou é uma forma de baratear o produto já que “em teoria” existem versões gratuitas de Linux? ainda mais pensando que ele vai ser distribuído por Docker,,,

Bom,,, de qualquer forma, mais uma coisa para relembrar,,, como faz tempo que não trabalho com linux… 😦

SQL Saturday #469 – Brasilia


E aí pessoal !!!

O conteúdo da palesta sobre backup parcial e restauração parcial e online está disponível.

O Slide está disponível para download ou visualização abaixo

O link para o download dos scripts e da apresentação está aqui.

Quero, novamente, agradecer pela oportunidade de ter palestrado no evento e agradecer a todos que tiveram paciência de assistir minha palestra.

O link para o acesso a agenda do evento é:

https://www.sqlsaturday.com/469/schedule.aspx

A ideia é a evolução contínua, gostaria que vocês avaliassem como foi a apresentação:

Event Notification para kill


i-see-dead-people-you-must-be-new-hereAlgum tempo atras fiz um post sobre Event Notification para monitorar DeadLock o que funciona muito bem,,,

Com um pouco de modificação o mesmo código pode ser utilizado para muitas outras coisas,,, por exemplo: limitar acesso a uma instância bloqueando a conexão do usuário…

Aí você pensa: “posso fazer isso por trigger, por que fazer diferente?”

Eu respondo,,, se você trabalha em ambientes com alto volume de conexões, ou ambientes que as vezes sofrem com falta de recursos como cpu ou memória sabe que a trigger pode ser uma pedra no sapato,,, ao invés de respeitar o filtro e impedir apenas algumas conexões ela bloqueia tudo e todos…

Já com o Event Notification é o contrário,,, no pior cenário ele só para de funcionar…

Os dois pontos que vejo como mais complicados são:

  • Não é possível personalizar uma mensagem para o usuário que sofreu o kill
  • Dependendo do que você fizer, ele pode começar a encher o errorlog com mensagens de kill

com o código abaixo consegui chegar a 20.000 tentativas de conexão simultâneas e nenhuma conseguiu conectar,,,


--apenas para constatar quais tipos de eventos podemos tratar... o que queremos eh o audit_login
select * from sys.event_notification_event_types with (nolock)
where type_name like '%login%'
use master
go

alter database BASE_DE_ADMINISTRACAO_DO_DBA set enable_broker with rollback immediate
go

alter database BASE_DE_ADMINISTRACAO_DO_DBA set TRUSTWORTHY on
go

use BASE_DE_ADMINISTRACAO_DO_DBA
go

CREATE QUEUE [Login_Killer_Queue]
GO

CREATE SERVICE [Login_Killer_Service]
AUTHORIZATION [dbo]
ON QUEUE [dbo].[Login_Killer_QUEUE]
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
CREATE ROUTE Login_Killer_Route
WITH SERVICE_NAME = 'Login_Killer_Service',
ADDRESS = 'LOCAL';
GO
DECLARE @AuditServiceBrokerGuid [uniqueidentifier]
,@SQL [varchar](max);

-- Pega o service broker guid da base de dados
SELECT @AuditServiceBrokerGuid = [service_broker_guid]
FROM [master].[sys].[databases]
WHERE [name] = 'ADM_BDADOS'

-- Cria e executa o SQL dinamico para criar o objeto do evento de notificacao
SET @SQL = 'IF EXISTS (SELECT * FROM sys.server_event_notifications
WHERE name = ''Login_Killer_Notification'')

DROP EVENT NOTIFICATION Login_Killer_EventNotification ON SERVER

CREATE EVENT NOTIFICATION Login_Killer_EventNotification
ON SERVER
WITH fan_in
FOR AUDIT_LOGIN
TO SERVICE ''Login_Killer_Service'', '''
+ CAST(@AuditServiceBrokerGuid AS [varchar](50)) + ''';'
EXEC (@SQL)
GO
SELECT * FROM [sys].[server_event_notifications]

select * from sys.server_event_session_actions with (nolock)
CREATE TABLE [dbo].[DBA_FailedConnectionTracker](
[host_name] [varchar](128) NOT NULL,
[login_name] [varchar](128) NOT NULL,
[spidu] int,
[FailedLoginData] XML
) ;

CREATE PROCEDURE dbo.spc_DBA_FailedConnectionTracker
AS
BEGIN
SET NOCOUNT ON;
-- looping infinito
WHILE (1 = 1)
BEGIN
DECLARE @messageBody VARBINARY(MAX);
DECLARE @messageTypeName NVARCHAR(256);
WAITFOR (
RECEIVE TOP(1)
@messageTypeName = message_type_name,
@messageBody = message_body
FROM [Login_Killer_Queue]
), TIMEOUT 500
-- se nao houver mensagens saia
IF @@ROWCOUNT = 0
BEGIN
BREAK ;
END ;
-- se o tipo da mensagem for um EventNotification para a fila atual
IF (@messageTypeName = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification')
BEGIN
DECLARE @XML XML,
@host_name varchar(128) ,
@login_name varchar(128) ,
@SPID varchar(5);

SELECT @XML=CONVERT(XML,@messageBody)
,@host_name = ''
,@login_name = ''
,@SPID ='';

-- Pega o SPID e as informacoes de login
SELECT @SPID = @XML.value('(/EVENT_INSTANCE/SPID)[1]', 'varchar(5)')
, @host_name = @XML.value('(/EVENT_INSTANCE/HostName)[1]', 'NVARCHAR(128)')
, @login_name = @XML.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(128)')
;

DECLARE @kill varchar(8000) = '';
--Caso o hostname e o login entrem no criterio abaixo
if ((@host_name like 'SPO%') and (@login_name like '%_user'))
SELECT @kill = @kill + 'kill ' + @SPID + ';'
EXEC(@kill);

--/* esta parte pode ser comentada para não gerar "log" do kill
if ((@host_name like 'SPO%') and (@login_name like '%_user'))
INSERT INTO [dbo].[DBA_FailedConnectionTracker]
([host_name], [login_name], FailedLoginData,spidu)
values ( @host_name, @login_name,@XML,@SPID);
--*/
END;
END;
END;
--inicia a matanca

ALTER QUEUE [dbo].[Login_Killer_Queue]
WITH STATUS = on
,ACTIVATION (PROCEDURE_NAME = [spc_DBA_FailedConnectionTracker]
,STATUS = ON
,MAX_QUEUE_READERS = 1
,EXECUTE AS OWNER)
GO

MSDB e os Pacotes de SSIS


Esse código mostra o tamanho ocupado por cada pacote de SSIS carregado dentro do MSDB.

Algumas vezes o MSDB começa a ganhar proporções ocupadas que apenas a limpeza de rotina não dá conta e o pessoal esquece que os pacotes também ocupam espaço…


with ChildFolders
as
(
select PARENT.parentfolderid, PARENT.folderid, PARENT.foldername,
cast('' as sysname) as RootFolder,
cast(PARENT.foldername as varchar(max)) as FullPath,
0 as Lvl
from msdb.dbo.sysssispackagefolders PARENT
where PARENT.parentfolderid is null
UNION ALL
select CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,
case ChildFolders.Lvl
when 0 then CHILD.foldername
else ChildFolders.RootFolder
end as RootFolder,
cast(ChildFolders.FullPath + '/' + CHILD.foldername as varchar(max))
as FullPath,
ChildFolders.Lvl + 1 as Lvl
from msdb.dbo.sysssispackagefolders CHILD
inner join ChildFolders on ChildFolders.folderid = CHILD.parentfolderid
)
select F.RootFolder, F.FullPath, P.name as PackageName,
P.description as PackageDescription, P.packageformat, P.packagetype,
P.vermajor, P.verminor, P.verbuild, P.vercomments,
cast(cast(P.packagedata as varbinary(max)) as xml) as PackageData,datalength(P.packagedata)/1024 AS SpaeceusedKB
from ChildFolders F
inner join msdb.dbo.sysssispackages P on P.folderid = F.folderid
order by F.FullPath asc, P.name asc;

Event Notification para monitorar DeadLock


A algumas semanas tivemos a necessidade de substituir a forma que usávamos para monitorar Dead Lock por alguma coisa mais usual…

Antes o Dead Lock era gravado no errorlog e depois encaminhado por email para o time,,, isso não é usual já que fica colocando muita informação desnecessária no errorlog e depois de alguns restarts perde-se algum histórico…

Com isso, cheguei a conclusão,,, vamos usar o event notification….

A vantagem é ser muito menos intrusivo que uma trigger e bem mais fácil de trabalhar que o System_Health…

Qual a ideia:

  1. O Event Notification vai enviar um email para cada vez que o evento de DeadLock acontecer, neste email vai conter informação da base que sofreu o DeadLock, horário e o DeadLock Graphic.
  2. Vai gravar em uma tabela na base de administração do DBA informações como: data e hora, base, XML com o evento.

use master
go

/*você já deve ter uma base de administração, caso não tenha, crie uma e troque o nome*/

alter database BASE_DE_ADMINISTRACAO_DO_DBA set enable_broker with rollback immediate
go

alter database BASE_DE_ADMINISTRACAO_DO_DBA set TRUSTWORTHY on
go

use BASE_DE_ADMINISTRACAO_DO_DBA
go

/**criando a tabela de controle/

create TABLE [dbo].[SQLDeadlockEvents](
[EventRowID] [bigint] primary key IDENTITY(1,1) NOT NULL,
[EventType] [nvarchar](128) NOT NULL,
[AlertTime] [datetime] NULL,
[DeadlockGraph] [xml] NULL,
[DatabaseName] [sysname] null,
[AuditDate] [smalldatetime]
) ON [FG_Data_01]
with (data_compression = PAGE) --neste caso tenho EE se você usar SE comente esta linha
GO

/**Criando a fila do Broker/

CREATE QUEUE [DeadlockGraphQueue]
GO

/*Criando o serviço para a fila do Broker*/

CREATE SERVICE [DeadlockEventsService]
AUTHORIZATION [dbo]
ON QUEUE [dbo].[DeadLockGraphQueue]
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

/*criando rota*/

CREATE ROUTE DeadlockGraphRoute
WITH SERVICE_NAME = 'DeadlockEventsService',
ADDRESS = 'LOCAL';
GO

/* Para ficar mais fácil ele vai criar o event notification com os dados dinamicamente, se não, eu teria que explicar que o guid altera, onde tem que pegar essa informação,,, era mais SQL para uma coisa que pode ser feita de forma dinâmica */

DECLARE @AuditServiceBrokerGuid [uniqueidentifier]
,@SQL [varchar](max);

-- broker guid do CaptureDeadlockGraph
SELECT @AuditServiceBrokerGuid = [service_broker_guid]
FROM [master].[sys].[databases]
WHERE [name] = 'BASE_DE_ADMINISTRACAO_DO_DBA'

SET @SQL = 'IF EXISTS (SELECT * FROM sys.server_event_notifications
WHERE name = ''DeadlockGraphEventNotification'')

DROP EVENT NOTIFICATION DeadlockGraphEventNotification ON SERVER

CREATE EVENT NOTIFICATION DeadlockGraphEventNotification
ON SERVER --aqui é o detalhe legal... servidor e não database
WITH fan_in
FOR DEADLOCK_GRAPH
TO SERVICE ''DeadlockEventsService'', '''
+ CAST(@AuditServiceBrokerGuid AS [varchar](50)) + ''';'
EXEC (@SQL)
GO

/*Para confirmar que foi criado,,, lembra que é nível servidor,,, por isso a consulta no [sys].[server_event_notifications] */

SELECT * FROM [sys].[server_event_notifications]
WHERE [name] = 'DeadlockGraphEventNotification';
GO

/* onde a mágica acontece.... */
create PROC [dbo].[usp_sCaptureDeadlockGraph]
AS BEGIN

SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON

DECLARE @SQLProcedureName [varchar](64)
SET @SQLProcedureName = 'usp_sCaptureDeadlockGraph'

BEGIN TRY

DECLARE @message_body [xml]
, @EventTime [datetime]
, @EventType [varchar](128)
, @message_type_name [nvarchar](256)
, @dialog [uniqueidentifier]

-- Endless loop
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION ;

-- Receive the next available message
WAITFOR (RECEIVE TOP(1)
@message_type_name = [message_type_name],
@message_body = [message_body],
@dialog = [conversation_handle]
FROM [dbo].[DeadlockGraphQueue]), TIMEOUT 2000

-- Rollback and exit if no messages were found
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END;

-- End conversation of end dialog message
IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
PRINT 'End Dialog received for dialog # ' + CAST(@dialog as [nvarchar](40));
END CONVERSATION @dialog;
END;

ELSE
BEGIN

SET @EventTime = CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS [nvarchar](max)) AS [datetime])
SET @EventType = CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS [nvarchar](128))

INSERT INTO [dbo].[SQLDeadlockEvents] ([EventType], [AlertTime], [DeadlockGraph])
VALUES (@EventType, @EventTime, @message_body)
END

COMMIT TRANSACTION

DECLARE @MailSubject [nvarchar](256)
,@MailBody [nvarchar](max)
,@SQLQuery [nvarchar](max)
,@AttachedFileName [nvarchar](256)
,@EventDateTime [datetime]
,@EventID [int]
,@Counter [int]
,@dbname [sysname]

IF EXISTS (SELECT * FROM [tempdb].[dbo].[sysobjects]
WHERE name = '##ListDeadlocks' AND type in (N'U'))
DROP TABLE ##ListDeadlocks

CREATE TABLE ##ListDeadlocks
([EventRowID] [int]
,[AlertTime] [datetime]
,[DeadlockGraph] [xml]
,[DeadlockXml] [xml])

INSERT INTO ##ListDeadlocks
([EventRowID]
,[AlertTime]
,[DeadlockGraph]
,[DeadlockXml])
SELECT [EventRowID]
,[AlertTime]
,[DeadlockGraph].query('./EVENT_INSTANCE/TextData/deadlock-list')
,DeadlockGraph FROM [dbo].[SQLDeadlockEvents]
WHERE [EventRowID] = @@IDENTITY

select @dbname = db_name(DeadlockGraph.value( '(/deadlock-list//@dbid)[1]', 'int' ))
--db_name(DeadlockGraph.value( '(/EVENT_INSTANCE/TextData/deadlock-list//@dbid)[1]', 'int' ) )
FROM ##ListDeadlocks
--where [EventRowID] = SCOPE_IDENTITY()

update [dbo].[SQLDeadlockEvents]
set [DatabaseName] = @dbname
where [EventRowID] = (select [EventRowID] FROM ##ListDeadlocks )

SELECT @Counter = MIN(EventRowID)
FROM ##ListDeadlocks

WHILE @Counter IS NOT NULL
BEGIN

SELECT @EventDateTime = [AlertTime]
,@EventID = [EventRowID]
FROM ##ListDeadlocks
WHERE [EventRowID] = @Counter

SELECT @MailSubject = 'Um deadlock ocorreu em ' + cast(@dbname as [varchar](256)) --+' servidor: '
--+ CONVERT([varchar](128), SERVERPROPERTY('ServerName'))
,@MailBody = 'Deadlock EventRowID: ' + CAST(@EventID AS [varchar](12))
+ char(13) + char(13) + 'O deadlock ocorreu as '
+ CONVERT([varchar](50), @EventDateTime, 120)
+ ' no Servidor: ' + CONVERT([varchar](128), SERVERPROPERTY('ServerName'))+'.'+char(13) + char(13) + 'Verifique o anexo xdl-file para mais detalhes.'
+ char(13) + char(13) +'Para mais informações ou eventos anteriores consulte: SELECT * FROM BASE_DE_ADMINISTRACAO_DO_DBA.dbo.SQLDeadlockEvents WITH (NOLOCK)'
,@SQLQuery = 'SET NOCOUNT ON; SELECT [DeadlockGraph] FROM [##ListDeadlocks] WITH (READUNCOMMITTED) WHERE EventRowID = '
+ CAST(@EventID AS [varchar](10))
,@AttachedFileName = CONVERT([varchar](128), SERVERPROPERTY('ServerName')) + '_' + CAST(@EventID AS [varchar](12)) + '.xdl'

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'email@dominio.com.br' -- troque o email aqui
,@subject = @MailSubject
,@body = @MailBody
,@query = @SQLQuery
,@attach_query_result_as_file = 1 ,@query_attachment_filename = @AttachedFileName -- http://support.microsoft.com/kb/924345
,@query_result_header = 0
,@query_result_width = 32767
,@query_no_truncate = 1

DELETE FROM ##ListDeadlocks
WHERE EventRowID = @Counter

SELECT @Counter = MIN(EventRowID)
FROM ##ListDeadlocks
end
END --End of loop

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER()
,ERROR_SEVERITY()
,ERROR_STATE()
,ISNULL(ERROR_PROCEDURE(), @SQLProcedureName)
,ERROR_LINE()
,ERROR_MESSAGE()

END CATCH

END
GO

/*inicia a fila para começar a capturar os DeadLocks*/
ALTER QUEUE [dbo].[DeadlockGraphQueue]
WITH STATUS = ON
,ACTIVATION (PROCEDURE_NAME = [usp_sCaptureDeadlockGraph]
,STATUS = ON
,MAX_QUEUE_READERS = 1
,EXECUTE AS OWNER)
GO

SQL Saturday #325 – São Paulo


E aí pessoal !!!

O conteúdo da palesta sobre backup parcial e restauração parcial e online está disponível.

O Slide está disponível para download ou visualização abaixo

O link para o download dos scripts e da apresentação está aqui.

Quero, novamente, agradecer pela oportunidade de ter palestrado no evento e agradecer a todos que tiveram paciência de assistir minha palestra.

O link para o acesso a agenda do evento é:

https://www.sqlsaturday.com/325/schedule.aspx

T-SQL Tuesday #57 – SQL Family and community


Para este mês o anfitrião do T-SQL Tuesday #57 é o Sr. Jeffery Verheul  (B|T)TSQL2sDay

Quando começamos como DBA´s, começamos lendo alguns livros, vendo alguns materiais na internet, participando de alguns treinamentos, etc… No começo acaba sendo bem assim,,, simples,,,

Aí você se depara com algum problema um pouco mais complicado,,, ou precisa fazer uma feature mais específica funcionar,,, e começa a perceber que existem mais pessoas mundo a fora que também mexem com essa coisa de SQL…

Ai descobre os BLOG´s,  fóruns de discussão, a hash #SQLHelp e vê que existem não mais,,, mas muito mais pessoas interessadas nessa coisinha chamada SQL… que o mundo não é só você com seu banquinho, mas com uma infinidade de pessoas precisando de ajuda e muitas outras dispostas a ajudar…

Quando você achou que já sabia de tudo que podia existir no SQL sempre vem alguém com mais e mais informação ou melhor forma ou solução para coisas que talvez você nem tivesse ideia que poderia acontecer…

Quantas vezes você não passou a noite em claro tentando resolver problemas e depois de colocar a pergunta no Twitter com a hashtag #SQLHelp teve a resposta em menos de 5 min? vinda de um cara que está provavelmente na Nova Zelândia que uma vez já passou por esse problema e fez um post em um blog  qualquer com a solução?

Após um tempo você percebe que existem várias fomas de interação, Ex.:

  • SQL Saturday – palestras presenciais gratuitas,
  • 24h de SQL – 24 horas de palestras online gratuitas
  • WebCasts – 1h30 sobre um assunto específico
  • #SQLHelp – ajuda
  • SQLPASS – Principal evento presencial (pago) da comunidade, só tem feras…
  • Fórum – a forma mais básica de P/R
  • Sites e mais sites… – olha você aqui lendo no meu site… 😀

E o melhor de tudo, tirando o SQLPASS e o twitter, tudo em português! Não tem nem como reclamar…

Quer começar a fazer parte? acesse o SQLPass faça um cadastro e veja as comunidades que existem na sua região… é de graça…

Isso é comunidade, pessoas ajudando pessoas. Não para massagear egos, mas pelo grande prazer em criar um lugar melhor, fazer as coisas melhores.

PS: uma coisa muito importante: Seja educado e agradeça! Se alguém gastou tempo para te ajudar a resolver algum problema ou para te dar um rumo para alguma coisa o mínimo da sua parte é agradecer.