If you’re not familiar, T-SQL Tuesday is a blogging party hosted by a different

Transactional Replication for Azure Databases

person each month. It’s a creation of Adam Machanic (b|l|t), and it’s been going on for a long time (77 months to be exact). The host selects a topic, defines the rules (those are almost always the same), and then everyone else blogs about it. Once the deadline is reached, the host will summarize each of the submitted posts on their site/blog.

This month, Jens Vestergaard is the host and he is asking the following question.  What is your favorite SQL Server feature?  I am going to use this opportunity to talk about my favorite new Azure Database Feature that was added this past year.

TRANSACTIONAL REPLICATION TO AZURE DATABASES

That’s right, and this won’t surprise my #SQLFamily because they already know that I love some transactional replication. Yes, as a Database Administrator I love this feature more than Query Store. I also think this will be a game changer.  Here are three reasons why.

The Proof Of Concept Your Boss Will Approve

So you are interested in trying out Azure Database but your boss says, “Yea, that’s not going to happen! Implementation risk is too high.” Go ahead and insert basically any sentence excuse you want. I am sure you could compile your own list.  This is where you can tell your boss, “Do you remember the support calls we get when James in Finance  runs his ad-hoc queries (more on this in a bit) or the that John was using Access again against our database? Wouldn’t it be nice if we can offload that activity and use it as a use case to see if Azure Database can work for us at the same time?” Would it be nice to do this without buying new hardware?  Wouldn’t it be nice if this copy of data was kept in-sync so you didn’t have to refresh it? Wouldn’t it be nice if it worked using normal tools that are included with SQL Server?

That’s right, with transactional replication we can now get those users who we don’t want anywhere near our teir-1 OLTP critical databases in Azure. You can provide them their own data center and let the reporting users be your test case for working with Azure Databases. If it doesn’t work, you can easily bring them back on-prem.

Offloading Real-World Ad-Hoc Reporting

In the field, I see a lot of people using Availability Groups to have a near real-time replica for reporting.  I talked a little bit about this above.  What isn’t mentioned here is you have to maintain a Windows Failover Cluster, Quorum, Active Directory (Unless using Windows 2016 Preview) and more. This gets you a replica that is just a copy of the database. What does this mean? You cannot change database objects like security, indexes, etc. Also, what if you don’t need the whole database(s) for reporting? If not, you can replicate only the data you truly need.

So, let’s recap here.  You only have to replicate the data that you need.  You can have different security and indexes on your reporting subscriber database(s).  The reporting subscriber database can be scaled up or down as needed based on your needs.  The reporting database can now be an Azure Database. Folks, I call this a huge win!

Easy Migration to Azure Database

As databases get bigger they become harder to migrate.  I learned this first hand when I had to migrate and make an 80TB database highly available with Availability Groups.  The larger your database the more downtime is required to deploy your existing database to an Azure Database.  Well this isn’t true anymore if you have primary keys on every table. You could pre-sync the data migration process in advance.  This could greatly cut down your migration time and get you to no downtime during your migration.

In my next blog post, I will walk you through a step by step guide to creating a Transactional Replication Subscriber that uses an Azure Database.