Copying an on-premise Database to an Azure SQL Database4
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:
- Select New
- Type SQL Database Server in the search box.
- Select SQL Server (logical server) from the search results.
- Select Create. (You can spend some time reading the description if you really want to. I don’t mind.)
- 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.
- And finally hit Create again.
- Wait some more.
- Get some coffee.
- Wait a bit more.
- Sigh with relief that your server has been created.
Next we’ll need to know how to connect to the server. Go to the server, hit properties and it’s under Server Name.
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.
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.
Confirm your settings and hit finish!
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.
And finally finished!
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.
Category: Backups, Cloud, Microsoft SQL Server, SQL Database, SQLServerPedia Syndication
4 thoughts on “Copying an on-premise Database to an Azure SQL Database”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
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/
I’ll take a look. Thanks! I have plans to write some posts in the future about the same processes using a BacPac and PoSh as well.
[…] 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…. […]
[…] lets you create DACPACs and BACPACs. A little while back created a BACPAC and then used it to copy the on-premises DB into Azure. This time I’m going to use a DACPAC as a schema only […]