Archive for SQL Compare

Adventures Into Azure Databases For The First Time (Part 2 of 2)

AdobeStock_52521593_thumb[2]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.

11-29-2015 5-37-20 PM_thumb[1]


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.

11-28-2015 5-00-57 PM_thumb[1]

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:

  1. On the Portal, I clicked on the server name.
  2. I clicked on Configure in the menu bar under the server name.
  3. 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”
  4. Finally, I clicked save at the bottom.

11-29-2015 8-05-39 PM_thumb[1]


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.

Adventures Into Azure Databases For The First Time (Part 1 of 2)

New Construction Home High Ceiling Wood Stud Framing

Whether we want to accept it or not, cloud computing is here to stay. Microsoft has made a big push in that direction for a few years now. When I first started using Office 365, I wasn’t sure I would like it. Now I love it. I love being able to access Word, Excel, and Outlook from wherever I am.

Now I want to do the same with my databases. To do that, I need to take the plunge and learn how to work with Azure Databases. If I don’t, I’ll fall behind, which is something I don’t want to do.

So let’s get started with my first Azure Database.

Ready. Get Set. Go!

Whenever you start a new project, you should set goals, and my experiments in learning about Azure Databases is no exception.

In Part 1 of this series I have the following goals:

  • Activate my free credits through my MSDN license.
  • Create my first empty database.
  • Determine how to connect to the database.

In Part 2 of this series:

  • 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.
1.  Setup Azure Account

The first thing you need to do is activate your Azure Account through your MSDN License. This only took minutes. I entered my contact information, then Microsoft sent me a text with an activation number, and I was set.

There were lessons that I learned though.

  • The Azure account uses the same login as Office 365, so I ended up setting up my Azure account for work the first time. Oops.
  • There is a difference between the Free Azure Credits for the Professional MSDN license and the Enterprise MSDN license.
  • The Professional MSDN license provides $100 per month free credit, while the Enterprise MSDN license provides $150 per month free credit.
  • You can pay for more credit, but I didn’t need to.
  • You can find the details here for all MSDN and Visual Studio licenses. Note: These credits are for development and testing only, not for production use.
  • The free credits can’t be used to work with third party products like Oracle. (I’m good with that.)

If you do have multiple Azure Accounts, you can verify which one you are currently logged into in the top right hand corner of the portal. Now that I’m paranoid as to which account I’m in, I’ll be watching that corner for my private email or my work email.


2. Setup My First Empty Database

Setting up my first database was as easy as setting up my Azure Account.

At the bottom of the portal, there is a New link and a Delete link. These are for creating and deleting databases.

After clicking the New link, I went through a series of screens to create my database.

The first screen asked me for the name of my database and what size database I wanted to create. This is an important step, since it will affect my monthly charges. Remember, I only have $150 in free credits each month. You can go here to see the pricing for the various service tiers and the performance levels. I chose to create the smallest database I could (2 GB, and 5 DTUs). I also created this database on a new SQL Database Server (I kind of have to, since it is the first database).

11-28-2015 4-24-28 PM

The second screen I was shown was for the login credentials of my Azure Server. I created a login name, a strong password, and where I wanted my server to be stored. Microsoft has locations all over the world that house the Azure servers. While I can’t pick a particular location, I can pick a particular region.

11-28-2015 4-27-29 PM

Now I have an Azure Server and Database. By clicking the Database Link or the Server link, I can see my Databases and Servers.

11-29-2015 4-48-49 PM

Figure A

There is another portal that you can look at as well. You can find it by clicking on your login name and selecting Azure Preview Portal. (This is in preview at the time of writing this article.)

There are a couple of different ways you can look at your servers and databases. One is a tile layout and the other is a traditional list layout.

11-28-2015 4-47-48 PM

Figure B

11-29-2015 4-55-29 PM

Lessons learned

  • I don’t get to pick the name of my server, but I’m ok with that. It will provide better security.
  • If I’m moving a database to Azure, I should probably provide the same name as my current database. This means I get to create two new databases, AWMonkey and Utility, which will be the two databases I’ll be working with. I’ll also delete the AzureMonkey database since I don’t need it.
3. Find the Azure Server and ODBC strings I’ll need to use to access from my applications on my laptop

This was very easy. I remember the 90s where I would bang my head on the table trying to figure out how to put together an ODBC string the first few times. (This was the stone age where Google didn’t exist. I know, horrifying.)

  • If you are on the current portal page (Figure A above), then you double click on the database name. Towards the bottom you’ll see the string that will be used to connect to your Azure Server.
  • By clicking on the link for the connection strings, you’ll be given four different connection strings. You just need to provide your user account credentials.

11-29-2015 5-06-58 PM

If you are in the preview portal (Figure B above), you can click on the name of the database, which will bring you to a property page, which has the string for the Azure server. Then you can click on the link that will provide the connection strings.

11-29-2015 5-19-15 PM

Until We Meet Again

Even though I’ve only created an empty database so far, I feel like I’ve accomplished a lot. I remember trying to get SQL Server 7.0 setup. It took two of us half the day and a fifteen step cheat sheet to get it installed. (Yup. Still no Google.)

Note: Setting up an Azure Account and an empty database took less than 10 minutes. In fact, it took less time than it took me to write this post. See you soon for the next post.

%d bloggers like this: