Now let’s talk about Availability Group replication and network latency. Availability Groups replicate data over your network using Database Mirroring Endpoints which are TCP sockets used to move data between the primary and it’s replicas. When designing Availability Groups, we often think about things in terms of bandwidth…how much data do I need to move between my replicas. But there’s another design factor your need to consider, network latency. Why? Hint, it’s not going to have anything to do with synchronous availability mode and HADR_SYNC_COMMIT waits. Let’s talk about some fundamentals of TCP for a second.
Click through for some discussion of TCP fundamentals.
Congratulations, you’ve configured a remote distributor, configured all of your AG replicas as publishers, and configured your SQL Database as a subscriber! Now you want to ensure that transactions are replicating to the database, and that they continue to do so if there is a failover in the AG.
Read on for the two testing scenarios.
This subscription is going to use an Azure SQL Database.
Go to the AG primary replica. (In this demo, this is SQL2014AG2.)
Expand Replication. Expand Local Publications. Right-click the publication and select New Subscription.
It turns out that this is a basic push subscription. Jes’s post is full of screenshots, making it even easier to follow.
After initializing, check the Snapshot Agent and Log Reader Agent for success. (To do so, go to Replication, right-click the publication name, and select Snapshot Agent Status and Log Reader Agent Status.) I ran into problems with the Snapshot account not having high enough permissions in the databases (it needs db_owner), and then not having enough permissions on the snapshot folder (it needs Full). (This forum post, answered by Hilary Cotter, helped: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/899857db-e38e-4026-a34c-2a8c2628c6fc/access-denied-to-sql-replication-snapshot-folder?forum=sqlreplication.)
Except for the final section, it’s pretty much the same as dealing with on-prem SQL Server sans Availability Groups.
Jes Borland has a five-part series on replicating a series of databases in an Availability Group to Azure SQL Database. Part 1 involves planning:
There are tasks you’ll need to take care of in SQL Server, the AG, and the SQL DB before you can begin.
This blog series assumes you already have an AG set up – it won’t go through the setup of that. It also assumes you have an Azure SQL server and a SQL Database created – it won’t go through that setup either.
Ideally, the publishers, distributor, and subscribers will all be the same version and edition of SQL Server. If not, you have to configure from the highest-version server, or you will get errors.
The first step in this process is to set up the remote distributor. As I mentioned in the first blog, you do not want your distribution database on one of the AG replicas. You need to set this up on a server that is not part of the AG.
Start by logging on to the distributor server – in this demo, SQL2014demo.
Stay tuned for the remainder of the series.
From the above screenshots, you can clearly see that the disks are not aligned.
So, what’s a big deal about this? When disks for primary and secondary are not aligned, then the AG synchronization process can run slow. This is not something which you would like to see in a Production server.
Read the whole thing.
You’ll need a linked server from your secondary replica to the primary replica. This linked server should feature the absolute bare-minimum of permissions, preferably with only the “be made using the login’s current security context” selected.
The account running the procedure (or the mapped login in the linked server) will need SELECT access to the following DMVs on the remote (primary) server:
The code itself is a download from Daniel’s website; go check it out.
Some of the problems that we have noticed are querying tables that have big datatypes like time(3-7), timestamp, and a few others. Casting or converting the datatypes doesn’t help. If we pull the table into a view without the big datatype columns, we are able to query the view from another server, but never the base table. It has been a bit frustrating, but we are still hopeful that we can find a solution or that Microsoft with fix ODBC connections. If there is a better way to do this, please reach out to me. We have things we need to solve and could use some help.
This is a nice walkthrough of how she set it up, but that sounds like a rather frustrating error.
ASYNCHRONOUS DATABASE MIRRORING
For asynchronous Database Mirroring, all we need is two servers: the principal at the primary site and the secondary at the DR site. Setup async mirroring between the two, and you’re done. If the secondary server goes down, production still continues. The transaction log isn’t clearing when the log backups occur because the principal still needs to send those log records to the secondary. As long as you have enough disk space where the transaction log resides to support this until the secondary server comes back online, production still continues. Of course if you run out of disk space, users will start receiving errors. But that can take some time and often times is enough time to get the secondary back online.
Availability Groups are more difficult to implement than database mirroring, but the flipside is that they’re much more powerful.
I panicked. In this situation I would normally pull the database out of the AG and then re-add it. I didn’t have that option because it is a HUGE database and didn’t have that much time and space to move it around. I knew a large transaction had kicked off (thank you alert email that I created to warn me about such things) but thought that surely the rollback would have cleared quickly. That lead me to looking for rolling back transactions.
Fortunately, the issue was on a secondary not under heavy use, so she was able to recover in time.