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

 

Powershell, Excel e SQL uma combinação excelente


Uma coisa muito legal em automatizar tarefas é a dificuldade que temos em planejar o que vai ser executado, a forma que vai ser executado e manter da forma mais genérica possível para garantir que vai funcionar na maior parte dos ambientes sem precisar sofrer muita alteração…

Agora imagina uma rotina qualquer em que você precisa ficar pegando resultados colocando no excel para fazer alguma graça interessante para alguém…

O posh abaixo faz exatamente isso. Ele conecta em um servidor que você definir, pega o nome dos bancos e executa a mesma query em cada um dos bancos,,,

até aí nenhuma novidade, certo?

O legal é que o resultado já vai para o excel, formatado e com auto filtro… e cada guia é a resposta de um banco deste servidor.

Você pode alterar a query para executar o que quiser, fragmentação de índice? blz…. um único select de uma única base? sem problemas,,, ou verificar estimativa de compactação de tabelas e índices usando compactação PAGE? é para isso que estamos aqui….


$servers = "SERVIDOR"

#lista as bases de dados para entrar no looping
$databases =
@'
SELECT
name
FROM sys.sysdatabases
where dbid>4
'@

$resultsDB = (invoke-sqlcmd -ServerInstance $servers -Query $databases).name

#query para ser executada em cada base do looping
$query2 =
@'
declare @scanupd TABLE
(
table_name sysname NULL
,index_name sysname NULL
,partition int NULL
, index_id int NULL
,index_type nvarchar(12) NULL
,percent_scan bigint NULL
,percent_update bigint NULL
)

DECLARE @CompressionSavingsEstimate table
(
SchemaName sysname NOT NULL,
ObjectName sysname NOT NULL,
IndexName sysname NOT NULL,
IndexType nvarchar(60) NOT NULL,
PartitionNum int NOT NULL,
CompressionType nvarchar(10) NOT NULL,
[size_with_current_compression_setting (KB)] bigint NOT NULL,
[size_with_requested_compression_setting (KB)] bigint NOT NULL,
[sample_size_with_current_compression_setting (KB)] bigint NOT NULL,
[sample_size_with_requested_compression_setting (KB)] bigint NOT NULL,
percent_scan bigint NULL,
percent_update bigint NULL
PRIMARY KEY (SchemaName, ObjectName, IndexName, IndexType, PartitionNum, CompressionType)
);
DECLARE @ProcResult table
(
[object_name] sysname NOT NULL,
[schema_name] sysname NOT NULL,
[index_id] int NOT NULL,
[partition_number] int NOT NULL,
[size_with_current_compression_setting (KB)] bigint NOT NULL,
[size_with_requested_compression_setting (KB)] bigint NOT NULL,
[sample_size_with_current_compression_setting (KB)] bigint NOT NULL,
[sample_size_with_requested_compression_setting (KB)] bigint NOT NULL
);
DECLARE @SchemaName sysname;
DECLARE @ObjectName sysname;
DECLARE @IndexID int;
DECLARE @IndexName sysname;
DECLARE @IndexType nvarchar(60);
DECLARE @PartitionNum int;
DECLARE @CompTypeNum tinyint;
DECLARE @CompressionType nvarchar(60);

SET NOCOUNT ON;

DECLARE CompressedIndex INSENSITIVE CURSOR FOR
SELECT s.name AS SchemaName,
o.name AS ObjectName,
i.index_id AS IndexID,
COALESCE(i.name, '<HEAP>') AS IndexName,
i.type_desc AS IndexType,
p.partition_number AS PartitionNum
FROM sys.schemas AS s
INNER JOIN sys.objects AS o
ON s.schema_id = o.schema_id
INNER JOIN sys.indexes AS i
ON o.object_id = i.object_id
INNER JOIN sys.partitions AS p
ON o.object_id = p.object_id
AND
i.index_id = p.index_id
WHERE o.type_desc IN ('USER_TABLE','VIEW')
AND p.data_compression_desc NOT IN ('PAGE','ROW');

OPEN CompressedIndex;

WHILE 1 = 1
BEGIN
FETCH NEXT FROM CompressedIndex
INTO @SchemaName, @ObjectName, @IndexID, @IndexName, @IndexType, @PartitionNum;

IF @@FETCH_STATUS <> 0
BREAK;

SELECT @CompTypeNum = 2;
WHILE @CompTypeNum <= 2
BEGIN
SELECT @CompressionType = CASE @CompTypeNum
WHEN 0 THEN 'NONE'
WHEN 1 THEN 'ROW'
WHEN 2 THEN 'PAGE'
END;

DELETE FROM @ProcResult;

-- RAISERROR('Estimating compression savings using "%s" compression for object "%s.%s", index "%s", partition %d...', 10, 1, @CompressionType, @SchemaName, @ObjectName, @IndexName, @PartitionNum);

INSERT INTO @ProcResult
EXEC sp_estimate_data_compression_savings @schema_name = @SchemaName,
@object_name = @ObjectName,
@index_id = @IndexID,
@partition_number = @PartitionNum,
@data_compression = @CompressionType;

INSERT INTO @CompressionSavingsEstimate
(
SchemaName,
ObjectName,
IndexName,
IndexType,
PartitionNum,
CompressionType,
[size_with_current_compression_setting (KB)],
[size_with_requested_compression_setting (KB)],
[sample_size_with_current_compression_setting (KB)],
[sample_size_with_requested_compression_setting (KB)]
)
SELECT [schema_name],
[object_name],
@IndexName,
@IndexType,
[partition_number],
@CompressionType,
[size_with_current_compression_setting (KB)],
[size_with_requested_compression_setting (KB)],
[sample_size_with_current_compression_setting (KB)],
[sample_size_with_requested_compression_setting (KB)]
FROM @ProcResult;

SELECT @CompTypeNum += 1;
END;
END;

CLOSE CompressedIndex;
DEALLOCATE CompressedIndex;
insert into @scanupd(table_name, index_name, partition,index_id,index_type,percent_scan,percent_update)
SELECT o.NAME AS [Table_Name]
,x.NAME AS [Index_Name]
,i.partition_number AS [Partition]
,i.index_id AS [Index_ID]
,x.type_desc AS [Index_Type]
,i.range_scan_count * 100.0 / (i.range_scan_count + i.leaf_insert_count + i.leaf_delete_count + i.leaf_update_count + i.leaf_page_merge_count + i.singleton_lookup_count) AS [Percent_Scan]
,i.leaf_update_count * 100.0 / (i.range_scan_count + i.leaf_insert_count + i.leaf_delete_count + i.leaf_update_count + i.leaf_page_merge_count + i.singleton_lookup_count) AS [Percent_Update]
FROM sys.dm_db_index_operational_stats(db_id(), NULL, NULL, NULL) i
JOIN sys.objects o ON o.object_id = i.object_id
JOIN sys.indexes x ON x.object_id = i.object_id
AND x.index_id = i.index_id
WHERE (i.range_scan_count + i.leaf_insert_count + i.leaf_delete_count + leaf_update_count + i.leaf_page_merge_count + i.singleton_lookup_count) != 0
AND objectproperty(i.object_id, 'IsUserTable') = 1
ORDER BY [Percent_Scan] DESC
SELECT CompressionType,
IndexName,
IndexType,
ObjectName,
PartitionNum,
CASE WHEN t.[percent scan] >100 then 100 else [percent scan] END AS [percent scan],
CASE WHEN t.[percent update] >100 then 100 else [percent update] END AS [percent update],
SchemaName,
[size_with_current_compression_setting (KB)],
[size_with_requested_compression_setting (KB)],
([size_with_requested_compression_setting (KB)]/NULLIF([size_with_current_compression_setting (KB)],0)*100) AS [X percent compact],
CASE WHEN (([size_with_requested_compression_setting (KB)]/NULLIF([size_with_current_compression_setting (KB)],0)*100) <=80 OR ([size_with_requested_compression_setting (KB)]/NULLIF([size_with_current_compression_setting (KB)],0)*100) IS NULL) AND [t].[percent scan] >=t.[percent update] THEN 'Compactar' ELSE 'Provavelmente Nao' END as [Z result]
FROM (
SELECT CompressionType,
IndexName,
IndexType,
ObjectName,
PartitionNum,
sum(s.percent_scan) AS [percent scan],
sum(s.percent_update) AS [percent update],
SchemaName,
cast(AVG([size_with_current_compression_setting (KB)]) AS money) AS [size_with_current_compression_setting (KB)],
cast(AVG([size_with_requested_compression_setting (KB)]) AS money) AS [size_with_requested_compression_setting (KB)]
FROM @CompressionSavingsEstimate CSE
INNER JOIN @scanupd AS s
ON CSE.ObjectName = S.table_name
GROUP BY GROUPING SETS (
(CompressionType),
(SchemaName, ObjectName, IndexName, IndexType, PartitionNum, CompressionType)
)
)AS t
ORDER BY SchemaName, ObjectName, IndexName, IndexType, PartitionNum, CompressionType DESC;

SET NOCOUNT OFF;
'@

# abre o excel
$xl = new-object -comobject excel.application
$wb = $xl.Workbooks.Add()
#$ExcelWorkSheet = $wb.Worksheets.Add()
$xl.Visible = $true
#$rowCount = 2

foreach ($s in $resultsDB)
{
$s #pode ser removido
$ExcelWorkSheet = $wb.Worksheets.Add()
$ExcelWorkSheet.Name = $s #usa o nome do banco como nome da planilha
$rowCount = 2
$results = invoke-sqlcmd -ServerInstance $servers -Query $query2 -Database $s -querytimeout ([int]::MaxValue) #timeout nunca !!!

foreach ($r in $results)
{
$r #pode ser removido
$ColCount = 1
$ExcelWorkSheet.Cells.Item($rowCount,$ColCount).Value2 = $servers
$ColCount = 2
for ($i=1; $i -le $r.ItemArray.Count; $i ++)
{
$ExcelWorkSheet.Cells.Item($rowCount,$ColCount).Value2 = $r.ItemArray[$i-1]
$ColCount ++
}
$rowCount ++
}
# adiciona o cabeçalho das colunas
$headers = $results | Get-Member -Membertype property
$ExcelWorkSheet.Cells.Item(1,1).Value2 = 'server'
$h = 2

foreach ($header in $headers)
{
$ExcelWorkSheet.Cells.Item(1,$h).Value2 = $header.name
$h ++
}
## Formata a planilha do Excel
$listObject = $ExcelWorkSheet.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $ExcelWorkSheet.UsedRange, $null,[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes,$null)
$listObject.Name = "User Table"
$listObject.TableStyle = "TableStyleLight10"
## ajusta o tamanho das colunas
$ExcelWorkSheet.UsedRange.Columns.Autofit() | Out-Null

}

PS.: ainda estou tentando entender qual a birra do powershell em querer colocar o resultado das colunas em ordem alfabética,,, assim que resolver este detalhe atualizo o código,,,