Moving to Azure SQL

As part of one of my pet projects, I maintain a reasonably large set of data. Since it has exceeded the capabilities of SQL Server Express, I’ve had to move to a fee-based database server. Since this project doesn’t generate income, I need to keep expenses fairly low, and a dedicated SQL Server instance was simply too expensive.  I decided to move the server to an Azure SQL database.

Interesting things I found…

Choosing a DB Size

Choosing a database size in Azure can be a challenge. Azure will allow you to choose between DTUs and V-Cores. Since I needed something that could scale to 100+ GB, I had to choose at least a “Standard” instance. And, because I am collecting data and running calculations almost continuously, I went with 100 DTUs. As it turns out, I have almost 100 percent DTU usage consistently. The instance costs just under 150 USD/month. You can expect future blog posts on lowering DTU usage (and subsequently cost.)

Calculating expected DTU usage is beyond the scope of this article, but there are some estimation tools available here and here.

The Migration

Migration was fairly simple. Microsoft provides a Data Migration Tool that checks compatibility and helps migrate the data to SQL Azure.  You can find the latest version (as of this publishing) at https://www.microsoft.com/en-us/download/details.aspx?id=53595.

I followed the general process shown here: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-migrate-your-sql-server-database.   My 15GB database moved over as quickly as one would expect over the wire and without incident.

Of course, it’s never just that easy. There is a pretty long list of things that changed for Azure SQL or are no longer reasonable in the cloud server environment.  Most of these changes have to do with users or credentials, file systems, or server level tasks that are handled behind the scenes in Azure.  I did, however, run into a couple of things that could cause issues that are not immediately apparent….

Join Changes

I’m old school. At least, I like to add “school” to that. And, when I learned joins, I learned them the old fashioned way:

select a.column1, b.column2 from table1 a, table2 b where a.something=b.something

The newer versions of SQL server prefer the more definitive syntax:

select a.column1, b.column2
from table1 a
inner join table2 b
on a.something-=b.something

But, since I’m not really a DB guy, I thought I’d be perfectly fine using the old syntax for a few of the views in my project.  I was wrong. The Data Migration tool complained about them, but still moved them over to Azure SQL. They work, but I’m suspecting a performance impact. I’ll be reworking them during the coming weeks and will likely post the results here.

Date/Time

The default time zone for Azure SQL DB is UTC.  That doesn’t sound terribly surprising or problematic, but if you’re using GetDate() in any of your queries, defaults or comparisons and expecting a local result, you may run into some trouble.

One solution is to use the ‘AT TIME ZONE’ and datetimeoffset features available in Azure SQL.  You can find all of the available time zones by running this statement:

select * from sys.time_zone_info

And then use it like this:

select convert(datetimeoffset,GetUTCDate()) 
                      AT TIME ZONE 'Eastern Standard Time'

The Microsoft documentation on those features shows some good examples of returning data in different formats. I recommend taking a look.

Pleasant Surprises

Transparent Data Encryption (TDE) – This feature is usually only available on Enterprise Edition SQL Server databases. I was pleasantly surprised to see it, along with several other security options,  available on even basic Azure SQL DB instances. Be sure to look on the database features blade for TDE, Auditing, Advanced Threat Detection and Data Masking.

Automatic Backups – Finally! To me, this is worth the price of admission. Finally I can sleep at night without wondering if my backups worked. Log Backups happen every 5-10 minutes, with full backups every week and differential backups every 12 hours or so. Retention periods vary based on the service tier, but you can increase the retention by providing additional storage.

Automatic Tuning – The jury is out on this one – but they say they will tune the databases automatically.  Keep watch and I’ll let you know how it works out.

Summary

Microsoft has made the move to Azure SQL DB fairly easy, and, for smaller projects, it’s certainly more cost effective than running your own Standard instance of SQL Server.  And, on the plus side, you get some of the features that are typically reserved for Enterprise versions.  So, even if you’re not looking to make a move to a cloud DB, I’d take a look at Azure SQL DB just to see what you’re missing.

The Project

This work was part of my BitPacer project that you can find at https://www.bitpacer.com.

 

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s