Copying an on-premise Database to an Azure SQL Database

3

August 24, 2016 by Kenneth Fisher

Continuing my exploration of Azure the next logical step seemed to be copying an on-premise database up to the cloud. This is also the next in the ideas I got while watching Jes Borland’s (b/t) excellent session on Azure and SQL Server on the Pass Data Architecture Virtual Chapter.

It turns out it’s pretty easy (even if it takes some time). So where to start? Well the first thing we need is a place to put our database. An Azure SQL Database Server. If you don’t already have one creating a new one is fairly easy.

First start at portal.azure.com. Log in and follow these steps:

  1. Select New
  2. Type SQL Database Server in the search box.
  3. Select SQL Server (logical server) from the search results.
  4. Select Create. (You can spend some time reading the description if you really want to. I don’t mind.)
  5. Fill in the required fields. Server name, admin name & password, subscription, resource group (create a new one if needed), and lastly the location of the server. Make sure it’s close by. The first few times I did this I managed to put all of my work in Europe.
  6. And finally hit Create again.
  7. Wait.
  8. Wait some more.
  9. Get some coffee.
  10. Wait a bit more.
  11. Sigh with relief that your server has been created.

NewSQLDatabaseServer

Next we’ll need to know how to connect to the server. Go to the server, hit properties and it’s under Server Name.

CopyToAzureDB1

Almost done! Now go into SSMS and connect to the server with the database you want to copy. Right click on the database and select Tasks -> Deploy Database to Microsoft Azure SQL Database.

CopyToAzureDB2

This is where we will need that server name from before. Don’t forget that in order to connect we need a firewall rule in place. Also pay special attention to the Edition (Basic, Standard, Premium, and Data Warehouse), Size (dependent on edition), and Service Objective (also dependent on edition). These are going to dramatically affect your cost. When ready hit Next.

CopyToAzureDB3

Confirm your settings and hit finish!

CopyToAzureDB4

Your database will now be exported and then imported. This can take some time even with a relatively small database. You thought you were waiting before? If you have a slow network you might as well go have some dinner, take a nap, whatever.

CopyToAzureDB5

And finally finished!

CopyToAzureDB6

There are lots of ways to perform any task and it’s a good idea to know at least a few of each. So in the not to distant future I’m going to try the same thing only using a BACPAC. Then we work on going the other direction.

Note: I found out after I posted this that my friend Randolph West (b/t) posted on the same subject recently. (Not that it matters. Remember how I’ve said it’s not a big deal to post on the same subject as someone else?) I enjoyed reading his version and thought I would share it with ya’ll as well.

Moving a database to Azure SQL Database

3 thoughts on “Copying an on-premise Database to an Azure SQL Database

  1. Neal Ganslaw says:

    There is also a great tool called
    SQL Database Migration Wizard (SQLAzureMW) which automates this process. I have been using it for years and absolutely love it. https://sqlazuremw.codeplex.com/

  2. […] Well there are two reasons. First because I’m studying how to move databases from SQL Server to Azure SQL Database and back. My first blog on the subject was using the Deploy Database to Microsoft Azure SQL Database option to move a SQL Server database to Az…. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,134 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: