Throughout the last few years, I’ve worked with SQL Server Availability Groups, Transactional Replication, and Azure SQL Databases. Recently, I had the challenge and opportunity to work on a project that involved all three at the same time. The goal was to take six databases that were in a SQL Server 2012 Availability Group and replicate them to Azure SQL Databases.
Both replication of a database in an AG and replication from SQL Server to SQL Database come with several caveats, limitations, and challenges. In this five-post blog series, I hope to share with you the lessons I’ve learned so you can do this correctly from the beginning.
- Part 1 – Planning
- Part 2 – The Distributor
- Part 3 – The Publisher
- Part 4 – The Subscriber
- Part 5 – Testing
Getting Started
Determine what you need to accomplish and if there is a simpler way to do it. This is a complicated solution with a lot of moving parts. Many things can go wrong or break. In this situation, we needed to have on-premises data in Azure to be consumed by other Azure services and for analytics. It also had to stay on-premises for transactional applications. The other option for moving the data on a regular basis was Azure SQL Data Sync, which has been in preview for five (long) years – I didn’t want to use a preview technology, especially one that’s been in preview mode for so long.
You should have some familiarity with each of these features before combining them. Write up an architecture document ahead of time that will include all the relevant information you need as you’re setting this up – replica names, publication properties, distributor properties, subscriber information, SQL DB information. Download my Replication Setup Checklist to be prepared.
Read through this series, and these other blog posts, to be prepared. I’ve referenced these blogs many time:
- Configure Replication for Always On Availability Groups (MSDN)
- Setting up Replication on a database that is part of an AlwaysOn Availability Group (AlwaysOn Support Team)
- Replication to SQL Database (MSDN)
- Azure SQL Database Live Migrations (John Sterrett)
Lastly, reach out for help if needed. The SQL Server community helped me many, many times while I set this up. Special thanks to Kendal Van Dyke (blog | Twitter), Drew Furgiuele (blog | Twitter), and Andy Mallon (blog | Twitter) for their help.
Prep Work
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.
There are minimum version and service pack requirements for replicating to a SQL DB, as noted in the article Replication to SQL Database. Make sure all SQL Servers are patched to the correct version.
Make sure replication components are installed on all SQL Server instances before beginning. You can verify by running this query. The desired result is 1.
USE master; GO
DECLARE @installed int; EXEC @installed = sys.sp_MS_replication_installed; SELECT @installed;
In the AG, all the replicas must be readable. If they aren’t, the distributor can’t read them, and the setup won’t work.
The distribution database should not be on a replica server. If that server is lost and the HA of the AG kicks in, you’ve lost a huge part of your replication strategy. Your distribution database needs to reside on a SQL Server that is outside of your AG.
The service accounts for the engine and Agent on all publishers, distributor, and subscribers must be Windows accounts. Don’t do this with NT Service accounts. Also, make sure the accounts have minimum permissions or you may get SSPI errors. (Described at https://cmatskas.com/fixing-error-cannot-generate-sspi-context-after-changing-sql-service-account/.)
The service account of the Log Reader Agent must be a db_owner in the publication database. As a matter of fact, there are a whole lot of rules about service accounts and permissions. Read Replication Agent Security Model and apply all these rules. Do not just automatically make accounts sysadmin and local admins. It will be hard and frustrating – but your setup needs to be secure.
On SQL Servers, make sure port 1433 is open on firewalls.
When replicating to an Azure SQL Database:
You must have a user set up in that database that is member of the db_owner role.
Make sure the IP address of every publisher and distributor is allowed through the firewall.
Using SSMS, verify you can connect from each publisher and distributor to the SQL DB. Resolve any errors before continuing.
Use the “Deploy Database to Microsoft Azure SQL Database” wizard to find any incompatibilities within the database. If there are stored procedures that cross-reference another database, for example, that isn’t supported in SQL Database and those objects can’t be replicated.
With all those pieces in place, let’s get started!
Scenario
Publishers: servers SQL2014AG1 and SQL2014AG2, database AGTest
Distributor: stand-alone server, SQL2014demo
Subscriber: Azure SQL Database, server jessqldb2, database ReplicationTest
Jes Borland is a Premier Field Engineer - SQL Server for Microsoft, where she is a trusted technical adviser, deep technical support, and teacher for her customers. Her experience as a DBA, consultant, and 5-time Data Platform MVP allow her to help the business and the IT teams reach their goals. She has worked with SQL Server as a developer, report writer, DBA, and consultant. Her favorite topics are administration, automation, and performance. She frequently presents at user groups, SQL Saturdays, and other community events. She is also an avid runner and chef.
