Deployment Manager and SQL Azure

Last week’s update included some awesome new functionality which allows you to deploy to SQL Azure database. In this post I’m going to show you how easy it is to deploy database changes to SQL Azure with Deployment Manager.

Okay, I’ll start this post with a caveat – I’m not a developer, I’m not a database developer, or even a DBA. Anything more than the most basic simple SQL flummoxes me. Two weeks ago, if someone had asked me to make some changes to a database, and then deploy my changes to SQL Azure, I’d have simply stared back at them blankly!

So, the fact that this morning I was able to package and deploy an on premise database to SQL Azure astonishes me. It really is simple.

In this guide I’ll show you how you can deploy database changes to SQL Azure using Deployment Manager.

What you’ll need:

  1. A Microsoft Azure account
  2. Deployment Manager Starter – it’s free and you can grab it here. (You’ll need to install the server and also an agent.)
  3. A SQL Azure database. (Instructions on how to create one.)
Step one – create your Azure Database:

If you’ve already created an Azure database, feel free to skip to step 2.

Once you have an Azure account set up, jump to the Azure Management Portal, select “SQL Databases” from the left hand menu and click New.

Specify azure database settings

In the specify database settings screen pick a name, size limit and language for your database. I opted for the smallest 1GB database size, but if you’re planning to develop something meatier, it’s worth selecting something larger as you’re only charged for what you actually use. On the next screen you can specify a login name, password and where you’d like the database to be hosted. One setting to be aware of is the “Allow Windows Azure services access to the server” tick-box at the bottom of the page. I’m a bit of a security freak, so I left this un-ticked. Either way you’ll have to set up a firewall rule to grant your machine or domain access.

Specify azure database settings

Once you’ve created your database, you will see something like this.

Windows Azure SQL Database details

We now need to take a note of the login details so that we can Point Deployment Manager to Azure. The easiest way to do this is to copy the server name (circled in red) and append it with “.database.windows.net”. So in my case, “ofq1ntsv0t” becomes “ofq1ntsv0t.database.windows.net”. You can then use this string to access the SQL Azure database through both Deployment Manager and SQL Server Management Studio (SSMS).

Step 2 – Configuring the Azure Deployment in Deployment Manager

I’m going to assume you’ve installed and set up Deployment Manager, if you haven’t it’s a pretty simple process.

Creating a database package:

The first thing you’ll need to do is create a database package. There are basically two ways to do this.

  1. You can have your build server automatically create and publish a package to Deployment Manager using the sqlCI.exe command line.
  2. Or you can use the Deployment Manager Add-in for SSMS.

I opted for the SSMS add-in for my demo as it’s incredibly fast to set up. If you already know how to publish database packages to Deployment Manager, feel free to jump straight to the next section.

Once you’ve downloaded and installed the add-in, launch SSMS and connect to your development database. It’s worth noting at this point that some SQL Server features aren’t supported in SQL Azure such as extended properties, so if the database that you package up uses one of those features, then you won’t be able to deploy to SQL Azure.

To launch the add-in, right click on your database and choose “Publish for deployment”.

Launch Deployment Manager SSMS Packager add-in.

Once you’ve launched the add-in, enter your DM server location and API key . You will then receive a green notification that the add-in has successfully connected to Deployment Manager.

Red Gate Deployment Manager SSMS Packager add-in

Once you’ve connected, proceed through the rest of the wizard. It’s all pretty self-explanatory, but you can find more info here if you get stuck. After you’ve filled out all of the necessary details the wizard, creates and publishes a database package to Deployment Manager.

Set up the Deployment in Deployment Manager:

When you’ve created a database package, head over to the Deployment Manager web interface and jump to the projects tab.

Deployment Manager Projects Tab

Create a new project by clicking the blue button and choose a name. In my case I’m calling it “SimpleTalk Azure Demo” as I’m deploying a version of the Simple Talk website.

Once you’ve picked a name, the next step is to add a “package step”. Think of a package step as a component of your deployment. For example you might have one for a database and one for an ASP.NET website. At this point pick your package from the package name drop down, and choose where you’d like to deploy too by selecting the machine and clicking ‘Include’. Finally click add to add the package step to you project.

Deployment Manager add package step

You’ll still need an agent…

In order to deploy to Azure, you’ll still need to install an agent.  In Deployment Manager all deployments initially go to one or more agents, but because of the variables below, the agent will perform the deployment to SQL Azure. For my example I’ve named the agent Azure to reflect what’s really going on. Here’s more information on configuring environments.

Deployment Manager Environments

Once you’ve created a package step for the database, the next step is to add some variables. Variables are essentially a way of specifying configuration variables for different environments. To deploy to SQL Azure you need to specify the following variables:

Variable Name Value
RedGateDatabaseAllowDynamicUpgrade true
RedGateDatabaseServer Your Azure database location
RedGateDatabaseUserName Your Azure database username
RedGateDatabasePassword Your Azure database password
RedGateDatabaseName The name of your Azure database
Red Gate Deployment Manager SQL Azure Variables

This is what my variables looked like.

Create a release:

The final step before you’re ready to deploy to Azure is to create a release. Jump to the releases page and click create release. Choose a version number and specify any release notes before clicking create.

Deployment Manager Create a Release

Now for the deployment!

After creating a release you’ll end up in the release overview page which tells you everything about the release you’ve just created. All you have to do now is click “deploy this release”, enter any comments and click deploy.

Congratulations, you’ve made your first database deployment to SQL Azure! If everything goes smoothly, you’ll see something like the below.

Deployment Manager Azure Successful Deployment

For interesting product updates, news and tips you should follow us on Twitter.

Tags: