This is part 4 of a 5-part series.
- Part 1 – Planning
- Part 2 – The Distributor
- Part 3 – The Publisher
- Part 4 – The Subscriber
- Part 5 – Testing
Scenario
Publishers: servers SQL2014AG1 and SQL2014AG2, database AGTest
Distributor: stand-alone server, SQL2014demo
Subscriber: Azure SQL Database – server jessqldb2, database ReplicationTest
Setting up the replication subscription
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.
Publication – select the publication and click Next.
Distribution Agent Location – select Run all agents at the Distributor (push subscriptions). Click Next.
Subscribers – here we will add the SQL DB. Click Add Subscriber > Add SQL Server Subscriber.
Enter the Azure SQL Server name, the login, and the password. Click Options. Go to Connection Properties. Enter the database. Click Connect.
Make sure there is a checkmark next to the subscriber name. Use the drop-down to select a Subscription Database. Click Next.
Distribution Agent Security – click the ellipses on the right side.
[
]7
Distribution Agent Security – three pieces. First select an account to run the distribution agent process to sync the sub. Ideally, this is a specific domain user.
Second, select the account to connect to the distributor.
Third, select an account to connect to the subscriber. If this is a SQL DB, this must be a SQL login that is a member of the db_owner role in the database.
Click OK.
Click Next.
Synchronization Schedule – choose Run continuously. Click Next.
Initialize Subscriptions – make sure Initialize is checked, choose Immediately. Click Next.
Wizard Actions – select both options. Click Next.
Script File Properties – give the file a name. Review the other options. Click Next.
Click Finish. All steps should have a green checkmark next to them.
To verify it’s working, expand Replication > Local Publications > Publication Name, and you should see your subscription. Right-click and select View Synchronization Status to confirm it’s applying the snapshot.
Your replication subscriber is now set up. The next step is to verify that all the pieces work by testing both replication and AG failover.
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.
