Yes it’s possible, even fairly simple (once you’ve read this blogpost). But why would you do it?

I’ve written before about an Umbraco development teams database setup, and if you read the blogpost, you’ll notice that I originally arrived at a setup, where every developer has her/his own database.

Since then Azure happened, and if you have access to an Azure account one of the impediments to using a shared database sort of disappeared: Creating a database you can share between developers, doesn’t require you setup a local database server, living on your local network. So if you want to explore the advantages of using a central shared database for Umbraco development, you can read on here to see how to move your Umbraco database into Azure.

Requirements

There’s a lot of different ways to go about moving or creating a database on Azure, this being one of them, but anyway: You’re going to need a Windows computer, a SQL Server installation (preferably v2012), and an installation of the Microsoft SQL Server Data Tools - October 2013 or later.

We’re going to create the database on Azure, following this recipe:

1. Create a database server for the database on Azure, using the web interface.
2. Create a Data Tier Application for your database with SqlPackage.exe, which sounds much more involved than it actually is.
3. Import your Data Tier Application into Azure with SqlPackage.exe

Create a database server in Azure

This is where you’ll need your Azure Account login credentials ready. Login, click the Database icon in the toolbar to the left, click servers in content-menu, and select: New » Data Services » SQL Database » Custom Create.

Next step is filling in the following rather self explanatory form. If this is your first SQL database in Azure you’ll need to create a server for the DB, by setting the Server to the New SQL database server option.

When setting up your DB server you’ll be asked to provide a username and a password for the server login, these will be used in the connection string for the database. Also choose a datacenter location for your database server.

Your new Azure SQL Database isn’t immediately available to everyone with an internet connection, there’s a firewall in front of it. So in order to be able to use it when developing you have to add your IP-address to the allowed IP-addresses list. You can do this from the Dashboard view for the database. (Click DB-icon in the left side - then your newly created database). You find the link to manage allowed IP addresses in the right side of the view below quick glance

Click the link - the interface will detect your current IP, so it’s just a matter of clicking a button to add your address to the list of allowed addresses.

After adding your IP address, you’re almost set to go. Last remaining step is grabbing the connection string, and use that when setting up your Umbraco project. The connection string is also available in the quick glance menu, just click Show connection strings copy the ADO.NET connection string in the dialog, and paste it into your web.config file.

When this is done, you should be able to run your local Umbraco project and setup the initial database using the Umbraco install wizard.

Move an existing database

So what if I want to do this on a project I’ve already started? - you say. Don’t worry I’ve got your back. You can move an existing database on to an Azure SQL database using a simple command line tool called sqlpackage.exe. The recipe for doing this looks like this:

1. Create an empty database in Azure using the instructions above
2. Export your existing database to a bacpac-file using sqlpackage.exe
3. Import your database into the Azure database using sqlpackage.exe.

Yep - it’s that easy, shouldn’t take you much longer than 10 minutes once you know how to do it.

The SQL Server Data Tools comes as a standalone download from Microsoft (find the link in the sidebar). Thay also come with the “Microsoft Azure SDK for .NET 2.2” download you can find using Microsofts Web Platform Installer, and I recommend you go ahead and install that, since you’re already getting started with Azure.

What we’re going to use here is a little command line tool called sqlpackage.exe which is usually placed here c:\Program Files (X86)\Microsoft SQL Server\110\DAC\bin. But it exists in diferrent versions, so your might want to check if it your version does what you need. To do that Open a command prompt, cd into the directory where your version of sqlpackage.exe is placed and try typing sqlpackage and hit enter. If the line specifying actions contains Export|Import at the end, you should be good to go. I recommend adding the location of your exe file to your path environment variable, you’re probably going to use it more than once.

A bacpac file is basically a full copy of the schema and data in a sql-database. You can produce it with sqlpackage.exe, and you can put the schema and data in the file into an empty sql database with sqlpackage.exe.

Export the database to a bacpac file

To do this run the following command line:

sqlpackage /a:export /ssn:dbserver /sdn:dbname /su:username /sp:password /tf:filename.bacpac

Once it’s done you have a bacpac-file which is a snapshot of your database as it looks right now. And if you’ve previously moved databases by generating sql-scripts using “Generate scripts …” in SQL Server Management Studio, you’ll be happy when you notice that the generated backpac-file is significantly smaller than the equivalent sql script. (The data for the database is stored in JSON format inside the file)

Import the database

As stated above you can import this file into an empty database, using sqlpackage.exe. To do this run the following command:

sqlpackage /a:import /tsn:azuredbserver /tdn:azuredbname /tu:azuredbusername /tp:azuredbpassword /sf:yourfile.bacpac

After this command has finished your database has been moved into Azure, all you need to do to use it in an application is copy the connection string, paste it in your application, make sure the ip-address of the machine running the application has been added to the database firewall, and you’re ready.

Notice that copying the database from Azure to your local machine is simply a question of reversing this process, first create a bacpac file from the Azure database using sqlpackage /a:export, then import it into your own database using sqlpackage /a:import. Also grabbing a backup snapshot of your database is a question of exporting your Azure database into a bacpac file on your local computer. I usually create a small bat file, that does this to spare myself some keystrokes.

Happy database hosting!