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

Ativando um Windows trial


As vezes é legal voltar a trabalhar um pouco com outras coisas além de SQL Server,,,

Essa semana fiz uma consultoria em Active Directory,,,, Fui resolver alguns problemas de replicação e configurar um novo domain controler para replicação,,,

Até aí nenhum grande problema,,, o cliente já havia instalado o SO (Windows Server 2012R2),,, atualizei o SO, alteramos o nome, adicionando ao domínio,,, até aí tudo tranquilo,,,

O problema: o cliente havia instalado o SO na versão trial de 180 dias,,, quando tentamos registrar o Windows ele informava que o número de série não era compatível com a versão instalada,,,

Em contato com o time de suporte da MS eles informaram que a única opção era reinstalar o SO na versão correta,,, o que na teoria não estava errado,,, mas sempre tem um jeito,,,,

Caso você tenha instalado um SO trial e queira registra-lo como full faça o seguinte:

  1. Abra o prompt em modo elevado e digite  dism /online /get-targeteditions
  2. Ele vai te mostrar para quais versões você pode elevar a instalação atual,,,
  3. Tenha em mãos com o serial da versão que você quer usar
  4. Agora digite:  DISM /online /Set-Edition:ServerStandard /ProductKey:XXXXX-XXXXX-XXXXX-XXXXX-XXXXX /AcceptEula Onde neste caso a edição é para qual queremos registrar este SO e o xxxx é o número de série,,,
  5. Se você fez certo será apresentada uma mensagem de erro,,,, Error 87
  6. Você não tem como forçar um upgrade de versão usando um serial que nao seja de KMS,,, haha pegadinha do malandro,,,
  7. Entre nesse site https://technet.microsoft.com/en-us/library/jj612867.aspx e copie o serial correto para sua versão,,,
  8. Substitua o xxxxx por esse serial e agora sim vai funcionar,,, só que ele não avisa que vai reiniciar,,, provavelmente ele deve reiniciar umas 2x
  9. Após isso, você deve conseguir ativar seu Windows com onseu serial sem problemas,,,

Caso não funcione sempre tem a opção do backup, reinstalar certo dessa vez e restore,,,

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

Coisas que só o Powershell ISE faz por você


Tenho alguns clusters que estão em Multi-site, usando um Quorum em File Share para melhorar a disponibilidade.

Como o “Cluster Core” conta como voto na contagem do cluster, tenho preferencia na localização do recurso, já que todos os nós estão votando ele trabalha como voto de desempate.

Eu precisava monitorar um evento bem particular do cluster, quando o “Cluster Core Resource” alterasse de site ele deveria alarmar pelo SCOM, até ai tranquilo,,, faço um script no POSH o pessoal coloca para monitorar e pronto….


$API = new-object -comObject "MOM.ScriptAPI"
$bag = $api.CreatePropertyBag()
$resultado = (@(Get-ClusterGroup "Cluster Group" | Where-Object {$_.OwnerNode -like 'SERVIDOR*'}).count -eq 1)

if ($resultado -eq $true)
{
$bag.AddValue("State","Good")}
else
{
$bag.AddValue("State","Bad")}

$API.Return($bag)

Enquanto testava o script percebi que o Powershell ISE estava retornando mensagem de erro, fiquei revendo argumento, variável, comObject, mas não encontrei o erro…

Powershell ISE SCOM Script Error

Powershell ISE SCOM Script Error

Exception calling “Return” with “1” argument(s): “The handle is invalid. (Exception from HRESULT: 0x80070006 (E_HANDLE))”
At line:15 char:1
+ $API.Return($bag)
+ ~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ComMethodTargetInvocation

A solução é bem simples:

  • Salve o arquivo em script e execute ele… o ISE não consegue interpretar o retorno do comObject.

O retorno é alguma coisa como:

<DataItem type=”System.PropertyBagData” time=”2015-06-24T12:18:40.1270337-03:00″ sourceHealthServiceId=”68A1F050-F975-9EE7-E0F3-C2CDE3445FD3″><Property Name=”State” VariantType=”8″>Bad</Property></DataItem>

Gerar DACPAC usando Powershell v2


Para quem leu meu post anterior de Gerar DACPAC usando Powershell percebeu que é um código muito simples, passível de erros como:

  • digitar o nome do banco errado,
  • símbolos nos nomes dos bancos,
  • não escolher um local ou o nome do servidor ele gera erro no script…

coisas bem primárias…

Dessa vez fiz algumas melhorias,,, como:

  • Tela mais simpática
  • conecta no servidor de banco e lista as bases
  • multi seleção de bases
  • não deixa você continuar se não selecionar um destino e colocar o nome do servidor
  • Não fecha sozinho
  • deixa na tela do posh o código para, caso precise, você copie e execute novamente
  • coloca o mouse como ampulheta
  • abre o diretório de destino quando acaba a tarefa
  • e localiza o executável do gerador de dacpac,,, desde que esteja no C:\

Uma coisa muito importante, o código é livre para modificação, sinta-se a vontade de fazer qualquer modificação que queira,, se achar que sua versão ficou melhor, ou achou algum problema e corrigiu, mande o código, vamos melhorar,,,,

Existe um detalhe para rodar esta versão sem problemas,,, você precisa ter o Windows atualizado além do Windows Management Framework 4.0 e do .Net Frame Work 4.5.2.

gerador de dacpac v2


cls
$data = get-date -format "_yyyyMMdd"
$WindowTitle = "Gerador de DACPAC"
$LabelPath = "Caminho:"
$LabelServer = "Servidor:"

$aplicacao = (Get-ChildItem -Path "C:\Program Files (x86)\Microsoft SQL Server" -Include "SqlPackage.exe" -Recurse | % { $_.FullName } | Select-Object -First 1)

$x = @()

[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")

$objForm = New-Object System.Windows.Forms.Form
$objForm.Text = $WindowTitle
$objForm.Size = New-Object System.Drawing.Size(290,270)
$objForm.StartPosition = "CenterScreen"
$objForm.FormBorderStyle = 'FixedSingle'

$objForm.KeyPreview = $True
$objForm.Add_KeyDown({if ($_.KeyCode -eq "Escape")
{$objForm.Close()}})

$OKButton = New-Object System.Windows.Forms.Button
$OKButton.Location = New-Object System.Drawing.Size(75,215)
$OKButton.Size = New-Object System.Drawing.Size(75,23)
$OKButton.Text = "OK"

$OKButton.Add_Click(
{$i=0
if ( $textbox2.Text -eq '' )
{

}

else
{
foreach ($objItem in $objListbox.SelectedItems)
{
#[System.Windows.Forms.Application]::UseWaitCursor=$true
$objForm.Cursor=[System.Windows.Forms.Cursors]::WaitCursor
$local = $textbox2.Text
$servidor = $textBoxServerName.Text
Write-Progress -Activity "Gerando DACPAC" -status "Gerando DACPAC para $objItem" -percentComplete ($i++ / ($objListbox.SelectedItems).Count*100)
$exec = ("'$aplicacao'" + " /a:Extract /ssn:"+$servidor +" /sdn:"+$objItem +" /tf:"+"'$local'"+"\"+$objItem+$data+".dacpac")
#$exec = (C:\"Program Files (x86)"\"Microsoft SQL Server"\110\DAC\bin\SqlPackage.exe /a:Extract /ssn:$servidor /sdn:$objItem /tf:$local\$objItem$data+.dacpac)
write-host $exec
Invoke-expression "& $exec"
#[System.Windows.Forms.Application]::UseWaitCursor=$false
$objForm.Cursor=[System.Windows.Forms.Cursors]::Default
}
#Invoke-Item "$local"
}
if ( $checkbox1.Checked -eq $true -and $textbox2.Text -ne '')
{
Invoke-Item "$local"
}

else
{

}

#$objForm.Close()
})

$objForm.Controls.Add($OKButton)

$CancelButton = New-Object System.Windows.Forms.Button
$CancelButton.Location = New-Object System.Drawing.Size(150,215)
$CancelButton.Size = New-Object System.Drawing.Size(75,23)
$CancelButton.Text = "Fechar"
$CancelButton.Add_Click({$objForm.Close()})
$objForm.Controls.Add($CancelButton)

# create your checkbox
$checkbox1 = new-object System.Windows.Forms.checkbox
$checkbox1.Location = new-object System.Drawing.Size(260,66)
$checkbox1.Size = new-object System.Drawing.Size(250,50)
$checkbox1.Checked = $true
$objForm.Controls.Add($checkbox1)

$objLabel = New-Object System.Windows.Forms.Label
$objLabel.Location = New-Object System.Drawing.Size(10,120)
$objLabel.Size = New-Object System.Drawing.Size(280,20)
$objLabel.Text = "Selecione a base que deseja extrair o DACPAC:"
$objForm.Controls.Add($objLabel)

$objListbox = New-Object System.Windows.Forms.Listbox
$objListbox.Location = New-Object System.Drawing.Size(10,140)
$objListbox.Size = New-Object System.Drawing.Size(260,20)
$objLabel2 = New-Object System.Windows.Forms.Label
$objLabel2.Location = New-Object System.Drawing.Size(10,10)
$objLabel2.Size = New-Object System.Drawing.Size(280,20)
$objLabel2.Text = "Servidor:"
$objForm.Controls.Add($objLabel2)

# Create textbox for the Server name
$textBoxServerName = New-Object System.Windows.Forms.TextBox
$textBoxServerName.DataBindings.DefaultDataSourceUpdateMode = 0
$textBoxServerName.Location = New-Object System.Drawing.Size(10,30)
$textBoxServerName.Name = "ServerName"
$textBoxServerName.Size = New-Object System.Drawing.Size(100,10)
$textBoxServerName.TabIndex = 0
$objForm.Controls.Add($textBoxServerName)
$objLabel2 = New-Object System.Windows.Forms.Label
$objLabel2.Location = New-Object System.Drawing.Size(10,60)
$objLabel2.Size = New-Object System.Drawing.Size(280,20)
$objLabel2.Text = "Destino:"
$objForm.Controls.Add($objLabel2)

# Create textbox used to file destination
$textBox2 = New-Object System.Windows.Forms.TextBox
$textBox2.DataBindings.DefaultDataSourceUpdateMode = 0
$textBox2.Location = New-Object System.Drawing.Size(10,80)
$textBox2.Name = "textBox2"
$textBox2.Size = New-Object System.Drawing.Size(150,10)
$textBox2.TabIndex = 0
$objForm.Controls.Add($textBox2)

$app = New-Object -ComObject Shell.Application
$destino_OnClick=
{
#TODO: Place custom script here

try {
$browseForFolderOptions = 0
if ($NoNewFolderButton) { $browseForFolderOptions += 512 }

$folder = $app.BrowseForFolder(0, $Message, $browseForFolderOptions, $InitialDirectory)
#if ($folder) { $selectedDirectory = $folder.Self.Path } else { $selectedDirectory = '' }
#[System.Runtime.Interopservices.Marshal]::ReleaseComObject($app) > $null
$textbox2.Text = $folder.Self.Path #$selectedDirectory #.FileName.ToString()

}
catch {
[System.Windows.Forms.MessageBox]::Show(
$_.Exception.Message,
"Error",
[System.Windows.Forms.MessageBoxButtons]::OK,
[System.Windows.Forms.MessageBoxIcon]::Error
)
}

}
$conectar_OnClick=
{
#TODO: Place custom script here

if ( $textBoxServerName.Text -eq '' )
{

}

else
{
$objForm.Cursor=[System.Windows.Forms.Cursors]::WaitCursor
$dataSource = $textBoxServerName.Text

$database = "master"
$connectionString = "Server=$dataSource;Database=$database;Integrated Security=True;"

$query = "select name from sys.databases where database_id > 4"

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$table = new-object “System.Data.DataTable”
$table.Load($result)
#$table
$objListbox.datasource=$table.Item(0)
$connection.Close()
$objForm.Cursor=[System.Windows.Forms.Cursors]::Default

}

}

$conectar = New-Object System.Windows.Forms.Button
$conectar.Location = New-Object System.Drawing.Size(200,30)
$conectar.Size = New-Object System.Drawing.Size(60,20)
#$button2.TabIndex = 1
$conectar.Text = "Conectar"
$conectar.UseVisualStyleBackColor = $True
$conectar.add_Click($conectar_OnClick)
$objForm.Controls.Add($conectar)
$destino = New-Object System.Windows.Forms.Button
$destino.Location = New-Object System.Drawing.Size(200,80)
$destino.Size = New-Object System.Drawing.Size(50,20)
#$button2.TabIndex = 1
$destino.Text = "..."
$destino.UseVisualStyleBackColor = $True
$destino.add_Click($destino_OnClick)
$objForm.Controls.Add($destino)

$objListbox.SelectionMode = "MultiExtended"

$objListbox.Height = 70
$objForm.Controls.Add($objListbox)
$objForm.Topmost = $True

$objForm.Add_Shown({$objForm.Activate()})
[void] $objForm.ShowDialog()

$x

Gerar DACPAC usando PowerShell


O ciclo de vida da maioria dos bancos envolve desenvolvedores e DBA´s compartilhando scripts para atualização de objetos e atividades de manutenção.

Existem algumas formas de fazer isso:

  • gerar scripts de objetos
  • Proporcionar acesso as partes interessadas (piada… eu sei…) para gerar os scripts
  • DACPAC´s
  • Backup/Restore
  • etc..

Um pouco de contexto antes,,,

Um DAC (aplicativo da camada de dados) é uma entidade lógica de gerenciamento de banco de dados que define todos os objetos do SQL Server, como tabelas, exibições e objetos de instância, incluindo logons, associados a um banco de dados de usuário.

Todos tem seus prós e contras… não é a intenção deste post tratar isso…

Para quem gosta de gerar scripts dos objetos lembra que tem um outro post que faz isso…

A vantagem do DACPAC é que você pode usa-lo para comparação entre um projeto do TFS e esse pacote extraído da produção ou, caso você seja o DBA, você pode comparar esse pacote contra a sua base de produção ou uma base em homologação. Gerar os scripts para igualar os ambientes ou apenas conseguir ter ideia das diferenças.


Add-Type -AssemblyName System.Drawing
Add-Type -AssemblyName System.Windows.Forms

$data = get-date -format "_yyyyMMdd"
$WindowTitle = "Gerador de DACPAC"
$LabelPath = "Caminho:"
$LabelServer = "Servidor:"

# Create the Label.
$label = New-Object System.Windows.Forms.Label
$label.Location = New-Object System.Drawing.Size(10,20)
$label.Size = New-Object System.Drawing.Size(280,20)
$label.AutoSize = $true
$label.Text = $LabelPath

# Create the TextBox used to capture the user's text.
$textBox = New-Object System.Windows.Forms.TextBox
$textBox.Location = New-Object System.Drawing.Size(10,40)
$textBox.Size = New-Object System.Drawing.Size(575,200)
$textBox.AcceptsReturn = $true
$textBox.AcceptsTab = $false
$textBox.Multiline = $true
$textBox.ScrollBars = 'Both'
$textBox.Text = $DefaultText

# Create textbox used to file destination
$textBox2 = New-Object System.Windows.Forms.TextBox
$textBox2.DataBindings.DefaultDataSourceUpdateMode = 0
$textBox2.Location = New-Object System.Drawing.Size(65,20)
$textBox2.Name = "textBox2"
$textBox2.Size = New-Object System.Drawing.Size(150,10)
$textBox2.TabIndex = 0
# Create the Label.
$label2 = New-Object System.Windows.Forms.Label
$label2.Location = New-Object System.Drawing.Size(420,20)
$label2.AutoSize = $true
$label2.Text = $LabelServer

# Create textbox for the Server name
$textBoxServerName = New-Object System.Windows.Forms.TextBox
$textBoxServerName.DataBindings.DefaultDataSourceUpdateMode = 0
$textBoxServerName.Location = New-Object System.Drawing.Size(480,20)
$textBoxServerName.Name = "textBox2"
$textBoxServerName.Size = New-Object System.Drawing.Size(100,10)
$textBoxServerName.TabIndex = 0

# Create the OK button.
$okButton = New-Object System.Windows.Forms.Button
$okButton.Location = New-Object System.Drawing.Size(415,250)
$okButton.Size = New-Object System.Drawing.Size(75,25)
$okButton.Text = "OK"
$okButton.Add_Click({ $form.Tag = $textBox.Text; $form.Close() })

# Create the Cancel button.
$cancelButton = New-Object System.Windows.Forms.Button
$cancelButton.Location = New-Object System.Drawing.Size(510,250)
$cancelButton.Size = New-Object System.Drawing.Size(75,25)
$cancelButton.Text = "Cancel"
$cancelButton.Add_Click({ $form.Close() })

$app = New-Object -ComObject Shell.Application
$button2_OnClick=
{
try {

$browseForFolderOptions = 0
if ($NoNewFolderButton) { $browseForFolderOptions += 512 }
$folder = $app.BrowseForFolder(0, $Message, $browseForFolderOptions, $InitialDirectory)
$textbox2.Text = $folder.Self.Path #$selectedDirectory #.FileName.ToString()
}
catch {
[System.Windows.Forms.MessageBox]::Show(
$_.Exception.Message,
"Error",
[System.Windows.Forms.MessageBoxButtons]::OK,
[System.Windows.Forms.MessageBoxIcon]::Error
)
}

}
$button2 = New-Object System.Windows.Forms.Button
$button2.Location = New-Object System.Drawing.Size(215,20)
$button2.Size = New-Object System.Drawing.Size(50,20)
$button2.Text = "..."
$button2.UseVisualStyleBackColor = $True
$button2.add_Click($button2_OnClick)
# Create the form.
$form = New-Object System.Windows.Forms.Form
$form.Text = $WindowTitle
$form.Size = New-Object System.Drawing.Size(610,320)
$form.FormBorderStyle = 'FixedSingle'
$form.StartPosition = "CenterScreen"
$form.AutoSizeMode = 'GrowAndShrink'
$form.Topmost = $True
$form.AcceptButton = $okButton
$form.CancelButton = $cancelButton
$form.ShowInTaskbar = $true

# Add all of the controls to the form.
$form.Controls.Add($label)
$form.Controls.Add($label2)
$form.Controls.Add($textBox)
$form.Controls.Add($okButton)
$form.Controls.Add($cancelButton)
$form.Controls.Add($textBox2)
$form.Controls.Add($textBoxServerName)
$form.Controls.Add($button2)

# Initialize and show the form.
$form.Add_Shown({$form.Activate()})
$form.ShowDialog() > $null

$srv = $textBoxServerName.Text
$local = $textbox2.Text
$bases = $textBox.Text.Split("`n")|%{$_.trim()}
$i=0
foreach ($s in $bases)
{
Write-Progress -Activity "Gerando DACPAC" -status "Gerando DACPAC para $s" -percentComplete ($i++ / $bases.count*100)
$GoBigSon= (C:\"Program Files (x86)"\"Microsoft SQL Server"\110\DAC\bin\SqlPackage.exe /a:Extract /ssn:$srv /sdn:$s /tf:$local\$s$data.dacpac )
}

Como parece de praxe,,, tem uns pequenos bugs:

  • Quando clicar no “…” para mapear o caminho, se não aparecer uma janela pop-up, movimenta a janela do app um pouco para o lado, ela ficou atrás da janela inicial,,, isso é um bug que acontece de vez em quando,,,
  • se vc clicar diversas vezes no “…” ele vai ficar abrindo diversas vezes…
  • ainda não estou tratando outros tipos de entrada no campo texto,,,, você deve colocar um banco abaixo do outro,,,
  • e sim,,, ele fecha a janela quando você clica em OK,,,

Ele ficou até que bem simpático…

gerador dacpac p1

 

Com os campos preenchidos

gerador dacpac p2

 

E tem até barra de status… olha que chique…

gerador dacpac p3