In my last post, I started a two part series to learn how to create and migrate a database from SQL Server 2014 to a new Azure Database.
Recap of My Goals
In Part 1 of this series, I completed the following goals:
- Activated my free credits through my MSDN license.
- Created my first empty database.
- Determined how to connect to the database.
In this post (part 2), my goals are:
- Poke around the Azure GUI to learn what is available with my free credits.
- Use my Red Gate tools to create a new schema and load it with data.
- Learn if I can take a backup from SQL Server 2014 and restore it in my Azure account.
4. Poking Around the Portal
When I was poking around, I found some great features. I discovered these screens by clicking on the database name on the Portal screen (figure A), where I found a series of links at the top that you can click on.
There is a monitoring screen, which also has some helpful metrics at the bottom, like dead locks. This is an important screen. You don’t want to max out your resources or you’ll be considered a “noisy neighbor” and Microsoft will do something about it.
I also found the Scale Out page. This page will allow you to increase or decrease the size of your database. This can be very helpful if your database slowly changes size throughout the month. By changing the service level or the DTU’s to only what you need, you can save money.
5. Upload A Database
I have an existing Database on my SQL Server 2014 server that I want to migrate to my Azure account. I want to first move the schema, then the data. I have always trusted my Red Gate tools to create reliable, deployable scripts. The two tools I’ll be using are SQL Compare and SQL Data Compare.
SQL Compare will compare my existing SQL Server 2014 database schema with my new Azure database schema, which is currently nonexistent. The reason why I like this tool is that it creates serializable transactions for deploying all the individual scripts as one script. If the script fails at any point, then the whole script is rolled back. The second tool, SQL Data Compare, will be used to move the data. I don’t recommend doing this on a gigabyte of data, but for my small database it will work great. It is also a good tool for moving all the values of lookup tables, or for resetting development data to a previous state.
When you open up SQL Compare, you are presented with a screen to set up the two databases. The left hand screen has the database with the SQL Objects you want to deploy and the right hand side has the database you want to deploy to. After you enter the server and login credentials, SQL Compare connects to the Server to obtain the list of databases.
At this point, I learnt another lesson. When I went to get the list of databases from my Azure Server, I was given an error message. It stated that my IP address was not allowed access to the Azure Server. Back to the Portal, I went and I followed these steps:
- On the Portal, I clicked on the server name.
- I clicked on Configure in the menu bar under the server name.
- I was presented with a very helpful page. It told me which IP address was currently accessing the portal. At this point, I clicked on the link “Add to the allowed IP addresses”
- Finally, I clicked save at the bottom.
I went back to SQL Compare, and I was able to retrieve the list of databases from my Azure server. Win!
When I clicked the Compare Now button, SQL Compare will compare the two databases and provide a list of deltas. The deltas are broken up into differing groups. The schemas exist in both databases, but differ. One group consists of the schemas that only exist in the database that will be deployed, and the other consists of the schemas that only exist in the database that will be receiving the deployment. You are able to select only the changes that you want to deploy.
In this database, there are only two tables and one function that are relevant for my Azure database, so I selected them and then I clicked on the Deployment Wizard button at the top. The next screen asked me if I want to have SQL Compare perform the deployment or to create a deployment script. I chose to create a deployment script. I’m then directed to the screen below.
It showed me the script that I can use to do the deployment. When I clicked the “Open Script in Editor” button, a marvelous thing happened. SSMS opened, a tab opened with the script I will be deploying, AND I had been connected to my Azure database with the same credentials I used to create the script. It also opened the correct database so that I was not left in the Master database.
After I executed the script, I had a database schema in the new Azure Utility database.
Now to get some data.
The SQL Data Compare works exactly like SQL Compare, but it is comparing all the data between two identical (or mostly identical) tables. Note: If the tables don’t have primary keys set, then you can select the field that should be the primary key.
Here you can see that I have two tables that I can compare. The ExpandedDate table has +44K records and my Tally table has 1 Billion records to move. The following steps are just like SQL Compare and I can choose whether SQL Data Compare deploys the script or creates a script to be manually deployed.
Since this was my first time migrating data from my local database to my Azure database, I learnt a few things. One, a deployment script with over 1 billion inserts, does not work. (I’m not surprised.)
Two, it took the SQL Data Compare tool a long time to migrate the data. I’m sure my home internet connection (with teenagers using the bandwidth) didn’t help.
6. Restore a local database backup to my Azure Database
While I waited…and waited for my Utility data to be inserted into my Azure database, I did some poking around to see if it was even possible to restore a local SQL Server 2014 backup to an Azure database. Guess what, I found something (And there was much rejoicing).
On CodePlex, I found a SQL Database Migration Wizard that can be used to restore the database. They even had Migration Wizards for 2008R2, 2012, and 2014. SQL Database Migration Wizard v3.15.6, v4.15.6 and v5.15.6
In the post, they also listed under their Reference Materials a “Migration Cookbook” that you can download and use. Migration cookbook now available for the latest Azure SQL Database Update (V12)
When I first started this two part post, I had only taken classes on Azure topics, but I had never gone through the steps of using the Azure portal. Now I’ve gone on a journey of completing a list of goals, which started with the creation of an Azure account and ended with loading a schema and data to a new database.
I hope you enjoyed this journey and will take a journey of your own exploring what Azure has to offer.