Databased Deployments

Well done! In the previous step, you have created an MSBuild based web deploy package. We are going to take what we have done a step further and talk about creating database packages for database deployments and bringing your databases into the same DevOps process.

The aim of creating database packages for database deployments is to bridge the gap that often exists between developers and database administrators. It is not uncommon to have silo teams taking care of web applications and database applications.

Unless these teams are merged, you will always have a slowdown in the deployment process where web components are ready and packages can be deployed but DBAs manually run scripts on receipt of service tickets.

Visual Studio natively supports database projects. This can be done by simply importing an existing database schema. When you deploy databases in a release pipeline, you have a unique set of issues that you can mitigate by using the various options for deployment.

Prominent among the deployment options are DACPAC and Script Deployments.

DACPAC

DACPAC is a data-tier application (DAC) package or DACPAC. A DAC is a self-contained unit of Microsoft SQL Server database deployment that enables developers and administrators to package SQL Server objects into a portable artefact called a DAC package, also known as a DACPAC.

The advantage of DACPAC is that the deployment tool assists in identifying and validating behaviour in different target and source databases. It also warns if upgrades might cause data loss and provide an upgrade plan.

For more information on DACPAC, see https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/data-tier-applications

For more on DACPAC deployment, see https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/deploy-a-data-tier-application

Script Deployments

A script deployment is a SQL Server script with data definition language (DDL), stored procedure, function and data scripts (pre- and post-scripts), which can be generated from a database build or schema compare, or manually.

Scripts provide the greatest flexibility for complex or non-standard behaviour, but foregoes any warning mechanisms, thereby making deployment riskier. Additionally, rollback behaviour might need to be customized as a separate script.

The risks in this approach, especially when assembled manually, can range from determining which script should be run and in which order, to determining whether the scripts safe to run multiple times or require rollback to rerun.

For more on SQL script deployment, see https://www.visualstudio.com/en-us/docs/build/apps/aspnet/cd/deploy-database-sqlscripts

DACPAC Deployment in Visual Studio Team Services

The process involves a few basic steps:

1. Add a build step to publish the DACPAC.

2. Add a release task to copy the DACPAC to the target database VM, and then add a variable for the admin login and password.

3. Add a release SQL Server DACPAC task.

Releasing a DACPAC using Windows PowerShell Script

Besides Visual Studio Team Servies, you can use a Windows PowerShell script to release a DACPAC, as in this example:

1. Set an SMO Server object to the default instance on the local computer.

CD SQLSERVER:SQLlocalhostDEFAULT 
$srv = get-item .

2. Open a Common.ServerConnection to the same instance.

$serverconnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($srv.ConnectionContext.SqlConnectionObject) 
$serverconnection.Connect() 
$dacstore = New-Object Microsoft.SqlServer.Management.Dac.DacStore($serverconnection)

3. Load the DAC package file.

$dacpacPath = "C:MyDACsMyApplication.dacpac" 
$fileStream = [System.IO.File]::Open($dacpacPath,[System.IO.FileMode]::OpenOrCreate) 
$dacType = [Microsoft.SqlServer.Management.Dac.DacType]::Load($fileStream)

4. Subscribe to the DAC deployment events.

$dacstore.add_DacActionStarted({Write-Host `n`nStarting at $(get-date) :: $_.Description}) 
$dacstore.add_DacActionFinished({Write-Host Completed at $(get-date) :: $_.Description})

5. Deploy the DAC and create the database.

$dacName = "MyApplication" 
$evaluateTSPolicy = $true 
$deployProperties = New-Object Microsoft.SqlServer.Management.Dac.DatabaseDeploymentProperties($serverconnection,$dacName) 
$dacstore.Install($dacType, $deployProperties, $evaluateTSPolicy) 
$fileStream.Close()

Firewall Connections and SQLCMD Utility

Script deployment in VSTS involves using PowerShell Tasks to enable firewall connectionss and drive the SQLCMD utility, which will deploy the database:

1. Add a release PowerShell task to add firewall rule to allow connection to the Azure SQL Database.

2. Add a release PowerShell task such as:

   • SQLCMD

   • Arguments: -S {database-server-name}.database.windows.net -U {username}@{database-server-name} -P {password} -d {database-name} -i {SQL file}.

   Note: {SQL file} refers to an artifact source created as a build step; for example $(System.DefaultWorkingDirectory)/devops200.3demo-repo/demonstrationdatabase.sql

3. Add a release PowerShell task to remove the firewall rule create in the first step. Removing the firewall rule restores the desired state of security for the VM.

For more information on DACPAC, see https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/data-tier-applications

For more on DACPAC deployment, see https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/deploy-a-data-tier-application>

For more on SQL Script deployment, see https://www.visualstudio.com/en-us/docs/build/apps/aspnet/cd/deploy-database-sqlscripts

The following are examples of scripts used to add and remove firewall rules:

Add Firewall Rule

[CmdletBinding(DefaultParameterSetName = 'None')] 
param 
( 
[String][Parameter(Mandatory = $true)] $ServerName, 
[String] $AzureFirewallName = "AzureWebAppFirewall" 
) 
 
$ErrorActionPreference = 'Stop' 
 
function New-AzureSQLServerFirewallRule { 
$agentIP = (New-Object net.webclient).downloadstring("http://checkip.dyndns.com") -replace "[^d.]" 
New-AzureSqlDatabaseServerFirewallRule -StartIPAddress $agentIp -EndIPAddress $agentIp -RuleName $AzureFirewallName -ServerName $ServerName 
} 
function Update-AzureSQLServerFirewallRule{ 
$agentIP= (New-Object net.webclient).downloadstring("http://checkip.dyndns.com") -replace "[^d.]" 
Set-AzureSqlDatabaseServerFirewallRule -StartIPAddress $agentIp -EndIPAddress $agentIp -RuleName $AzureFirewallName -ServerName $ServerName 
} 
 
If ((Get-AzureSqlDatabaseServerFirewallRule -ServerName $ServerName -RuleName $AzureFirewallName -ErrorAction SilentlyContinue) -eq $null) 
{ 
New-AzureSQLServerFirewallRule 
} 
else 
{ 
Update-AzureSQLServerFirewallRule 
}

Remove Firewall Rule

[CmdletBinding(DefaultParameterSetName = 'None')] 
param 
( 
[String][Parameter(Mandatory = $true)] $ServerName, 
[String] $AzureFirewallName = "AzureWebAppFirewall" 
) 
 
$ErrorActionPreference = 'Stop' 
 
If ((Get-AzureSqlDatabaseServerFirewallRule -ServerName $ServerName -RuleName $AzureFirewallName -ErrorAction SilentlyContinue)) 
{ 
Remove-AzureSqlDatabaseServerFirewallRule -RuleName $AzureFirewallName -ServerName $ServerName 
}