Art of the DBA Rotating Header Image

Azure SQL Database

Azure SQL Databases with PowerShell: Moving To The Cloud

cloud-computing-2It’s been a while, but I want to wrap up my series on Azure SQL Databases and Powershell. In my last post, I talked about how you can make point in time backups of Azure SQL Databases using the Start-AzureSqlDatabaseExport. This process allows you to make a backup of your Azure SQL Database and store it as an Azure blob. You can then restore these backups using Start-AzureSqlDatabaseImport. It’s all very handy to manage your data within the Azure environment.

Now let’s talk about what I alluded to at the end of that post: using this process to migrate data from your traditional SQL Servers to Azure SQL databases. As we have previously discussed, Azure SQL Database does not support traditional backups as we are used to with regular SQL Server. While there’s a debate on whether or not these should be supported, the reality is that we need another process to handle this. This is where the export/import process comes in.

BACPACing

SQL Server Data Tools(SSDT) have always had a process to extract your database. There are two types of extracts you can perform:

  • DACPAC – A binary file that contains the logical database schema and possibly the data. This file retains the platform version of the database (i.e. 2012, 2014, 2016).
  • BACPAC – A binary file that contains the logical database schema and the data as insert statements. This stores the platform version, but is not locked into it.

The DACPAC extract is useful for managing your database code. These objects can be source controlled and, using SSDT, can be used to compare to a current database state and deploy changes. I’m a big fan of using DACPACs to manage my database code and maintain code consistency. I don’t use the data extract portion, but it’s there if you need it (more on this in a later blog post).

BACPACs are a little more limited. They contain the schema and your data in a logical state, as insert VALUE statements to populate your database objects. The advantage is you get a flexible package that can deploy a database and its data to almost any SQL Server platform (with some caveats, which we will get to).  The disadvantages are that the BACPACs are not as efficient as native backups and are not transactionally consistent.

What this means is that BACPACs are not really suited for day to day backups of your database. They are intended to be a migration tool, especially if you are working with disparate versions of SQL Server. It is why they’re intended for moving databases to Azure SQL Database, because a BACPAC can move your database logically and you are not reliant on the underlying database architecture.

Which brings us to the caveat. While BACPACs allow us to move a database logically, it can not always take into account unsupported features. We can export a database from our SQL Server, but if we’re using something not supported in Azure SQL Database (such as CLR or Service Broker), then this migration will not work. If you are planning this sort of migration, you first need to review your databases for these issues.

Exporting Our Database

Enough about that, let’s get exporting. There are several ways to migrate your database, but we’re going to focus on Powershell and those cmdlets. We can use sqlpackage.exe to create the BACPAC, which is pretty easy (this is really one line, I’m separating it so it’s easier to read):

SqlPackage.exe 
/action:Export 
/SourceServerName:localhost 
/SourceDatabaseName:AdventureWorks2012 
/targetfile:'C:\temp\AW2012.bacpac'

Once we’ve got our .bacpac file, we need to get it up into Azure. For this we need a storage account, then it’s just a matter of copying the blob. For this, we will use the same storage container we used for the exports before. We will create a storage context for the storage account, then use Set-AzureStorageBlobContent to upload the .bacpac file.

$stctxt = (Get-AzureRmStorageAccount -ResourceGroupName test -Name msftest).Context
Set-AzureStorageBlobContent -File C:\TEMP\ADW2012.bacpac -Context $context -Container sqlexports

Liftoff to the Cloud

With our export loaded into Azure, we’re ready to import it into Azure SQL database. We need to first create a database to import the data into:

New-AzureRmSqlDatabase -ResourceGroupName 'IntroAzureSQL' -ServerName 'msf-sqldb' -Edition Basic -DatabaseName AWTEST

After this, it is just a matter of starting the import as we did previously. We need to create a connection context for our Azure SQL Database server, then start the import:

$cred = Get-Credential
$sqlctxt = New-AzureSqlDatabaseServerContext -ServerName msf-sqldb -Credential $cred
$request = Start-AzureSqlDatabaseImport -SqlConnectionContext $sqlctxt -StorageContext $context -StorageContainerName sqlexports -DatabaseName awtest -BlobName ADW2012.bacpac

Notice I save the output of  Start-AzureSqlDatabaseImport into a variable. This is an object requesting the import request, which I can use to check on the import. Remember that calling the cmdlet starts an asynchronous process that will import the schema and data of the .bacpac into your newly created database. We’ll want to check on the process to identify when it completes.

Double Check Your Work

The one last thing to do is to verify the migration, which requires two checks: the schema and the data. For the schema, we will go back to sqlpackage.exe to perform a schema comparison. This is a two step process:

  1. Extract the source schema as a .dacpac
  2. Generate a deploy report targeting the Azure SQL database. If no changes are shown, the schemas match.

Extracting the schema as a .dacpac is a similar operation to extracting the schema as a .bacpac:

SqlPackage.exe /action:Extract <code>
/SourceServerName:localhost </code>
/SourceDatabaseName:AdventureWorks2012 <code>
/p:ExtractAllTableData=True </code>
/targetFile:C:\Temp\AW2012.dacpac

Generating a deploy report is a similar command, though we have a couple gotchas. First, we’re connecting to our Azure SQL database, so we need our login and password passed to the command. Second, .dacpacs store the database platfom version and will halt if there’s a possible incompatibility. So we need to declare AllowIncompatiblePlatform=True in our sqlpackage call to ignore this. We’ll still get a flag that there could be incompatibilities, but it will at least run the comparison for us:

SqlPackage.exe /action:DeployReport <code>
/SourceFile:C:\Temp\AW2012.dacpac </code>
/targetServerName:msf-sqldb.database.windows.net <code>
/targetDatabaseName:AWTEST </code>
/targetUser:&quot;$($cred.UserName)&quot; <code>
/targetPassword:&quot;$($cred.GetNetworkCredential().Password)&quot; </code>
/p:AllowIncompatiblePlatform=True `
/OutputPath:C:\temp\AWMigration.xml

The resulting .xml file will tell us what changes the deployment would have to make to get everything lined up. Since the goal here is to verify that everything got deployed correctly, we want this report to not have any changes. The desired output would look a little like this:

2016-09-15_8-48-46

Since no changes are listed, there’s nothing to do and everything matches.

Checking your data can be a trickier proposition. For a basic “sniff test”, I just check the row counts of each table. For this, I have a query to check sys.partitions for heaps and clustered indexes (index_id is 0 or 1) and compares the source to the Azure SQL database to look for any differences:

$sql = "select
schema_name(schema_id) +'.'+ t.name as TableName
,p.Rows as Rows
from
sys.tables t
join (select * from sys.partitions where index_id in (0,1)) p on t.object_id = p.object_id
order by TableName"

$local = Invoke-Sqlcmd -ServerInstance localhost -Database AdventureWorks2012 -Query $sql
$azure = Invoke-Sqlcmd -ServerInstance msf-sqldb.database.windows.net -Database AWTEST -Query $sql -Username $cred.UserName -Password $cred.GetNetworkCredential().Password

$matches = @()
foreach($i in $local){
$matches += New-Object psobject -Property @{'TableName'=$i.TableName;'LocalRows'=$i.Rows;'AzureRows'=($azure | Where-Object {$_.TableName -eq $i.TableName}).Rows}
}
$matches | Where-Object {$_.LocalRows -ne $_.AzureRows}

Again, if everything worked correctly we shouldn’t get any output.

I should stress that these two checks do NOT replace thorough regression testing. These should give you a starting point to verify that your migration was successful, but you still will want to test your applications and processing.

The New Hotness

Now, while I would like for Microsoft to include some native cmdlets that accomplish these actions, it should be noted that sqlpackage.exe is a pretty solid tool. It can be used from the command line and wrapping PowerShell around it is still effective. The key is having something I can use from the command line so I can write repeatable scripts. This is important because Azure is driven by automation and, while we can use GUI tools and the portal, we won’t succeed long term in the cloud on these tools alone, so it’s important to understand all the options.

Creating Alerts for Azure SQL Database with Powershell

Last week, as I watched the Twitters, I saw an interesting blog post from Julie Koesmarno(@MsSQLGirl) about creating alerts for Azure SQL Database. These are cool because one of the many things I get asked about when talking about Azure SQL Database is “how do I monitor this darn thing?” With these alerts, you can monitor performance, capacity, and security settings, setting up either email notifications or webhook responses.

Now, me being me, I tweeted out that it would be cool to do this in PowerShell. I say this not just being a geek about the language, but also because if you want to live in the cloud you need to up your automation and scripting game. PowerShell is the way to manage your Azure resources, especially if you want to set up consistent alerts for all your Azure SQL Databases. Julie pointed me to some examples that, while not specific for Azure SQL Database, got me moving in the right direction. I’d like to share some of that learning here.

The cmdlets

To start, we need to know what cmdlets are at our disposal. You’re going to need the Azure and AzureRM modules first (I recommend using the Powershell gallery installation) and then we just need to use PowerShell’s command syntax to find what we’re looking for. Using Get-Command, just do a wildcard search around ‘Azure’ and ‘Alert’ as my search terms.

Get-Command *Azure*Alert*

2016-08-17_15-02-15

It’s not a long list, but we don’t need much to get our work done. It should be noted that these are generic and can be used across all Azure resources.

Because these cmdlets are generic, we have to identify the specific metrics we can monitor for Azure SQL Database. Fortunately, these are discoverable using the Get-AzureRmMetricDefinition, which is not listed but part of the AzureRM.Insights module (use Get-Command -Module AzureRM.Insights). To use it, we need to know our Azure resource ID, which we can derive from three pieces of information: the resource group, the server name, and the database name.

$ResourceGroup = 'IntroAzureSql'
$server = 'msf-sqldb'
$db = 'MSFADMIN'
$rid = (Get-AzureRmResource -ResourceGroupName $ResourceGroup -ResourceName "$server/$db").ResourceID
Get-AzureRmMetricDefinition -ResourceId $rid | Format-Table

2016-08-17_15-11-26

Most of these Name values match what Julie listed in her post, but the nice thing is if we ever get new ones, we can look them up with this method.

Creating an Alert

So let’s get down to brass tacks and actually create an alert. To do this, we need some info first:

  • The Resource Group we will create the alert in.
  • An Azure location where the alert will live.
  • An Azure SQL Database server and database we are creating the alert for.
  • What metric we will monitor and what is the threshold we will be checking.
  • (optional) An email to send an alert to.

With this, here’s the settings I’m going with:

Resource Group – IntroAzureSql
Location – West US
Server – msf-db
Database – MSFADMIN
Alert – dtu_consumption_percent greater than 90 (Flag if more than 90% DTU usage)

There are a couple other considerations for creating an alert. The first is a time window the alert will check against. This window can be anywhere from five minutes to a full day. The second is what aggregation of the metric we will check (i.e. total, average, maximum). You’ll want to use values and aggregations that make sense, but for the example alert, we’ll check the maximum value over a 5 minute window.

Once we have everything in place, we will call the New-AzureRmAlertRuleEmail cmdlet to create our email notification and Add-AzureRmMetricAlertRule cmdlet to create our alert:

$ResourceGroup = 'IntroAzureSql'
$location = 'West US'
$server = 'msf-sqldb'
$db = 'MSFADMIN'
$rid = (Get-AzureRmResource -ResourceGroupName $ResourceGroup -ResourceName &quot;$server/$db&quot;).ResourceID
$email = New-AzureRmAlertRuleEmail -CustomEmails 'mfal@dummy.com' -SendToServiceOwners
Add-AzureRmMetricAlertRule -Name 'DTU90Check' <code>
-Location $location </code>
-ResourceGroup $ResourceGroup <code>
-TargetResourceId $rid </code>
-MetricName 'dtu_consumption_percent' <code>
-Operator GreaterThanOrEqual </code>
-Threshold 90 <code>
-WindowSize '00:05:00' </code>
-TimeAggregationOperator Maximum `
-Actions $email

2016-08-17_15-33-45

And now we can look at our shiny new alert using either the portal or Get-AzureRmAlertRule:

2016-08-17_15-37-18

2016-08-17_15-39-54

To get a rid of an alert is easy, too, just call Remove-AzureRmAlertRule:

Remove-AzureRmAlertRule -ResourceGroup $ResourceGroup -Name 'DTU90Check'

2016-08-17_15-42-50

Where To Now?

As you can see managing alerts in PowerShell isn’t all that difficult. The question is “why”? Hopefully you can already see the value, especially if you want to standardize your Azure SQL Database environment. By having all your alerts scripted out, you can apply them in a consistent fashion. This can be part of a larger automation process that helps scale out your environment as necessary. Regardless of how you manage it, using PowerShell can significantly decrease your management time for your Azure environment.

Thanks again to Julie for giving me the inspiration to figure this out!