A quick history lesson: through SQL Server 2016, we have three main variants of AGs:
- “Regular” AGs (i.e. the ones deployed using an underlying Windows Server failover cluster [WSFC] requiring Active Directory [AD]; SQL Server 2012+)
- AGs that can be deployed without AD, but using a WSFC and certificates (SQL Server 2016+ with Windows Server 2016+)
- Distributed AGs (SQL Server 2016+)
SQL Server v.Next (download the bits here) adds another variant which is, to a degree, a side effect of how things can be deployed in Linux: AGs with no underlying cluster. In the case of a Windows Server-based install, this means that there could be no WSFC, and for Linux, currently no Pacemaker.
Read on for more details, including limitations and expectations.
There’s a gray bar across the top that says, “This site is currently in read-only mode; we’ll return with full functionality soon.”
That’s not a hidden feature of Always On Availability Groups. Rather, it’s a hidden feature of really dedicated developers whose application:
Tries to connect to the SQL Server, and if that fails, shows a total-site-down page
If it’s not writeable, shows a polite banner across the top of the site, and still gracefully runs readable queries only
This is where a bit of foresight and hard work can really pay off. Read the whole thing.
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.