This is part 4 of a 5-part series.

 

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.

subscription 1

Distribution Agent Location – select Run all agents at the Distributor (push subscriptions). Click Next.

subscription 2

Subscribers – here we will add the SQL DB. Click Add Subscriber > Add SQL Server Subscriber.

subscription 3

Enter the Azure SQL Server name, the login, and the password. Click Options. Go to Connection Properties. Enter the database. Click Connect.

subscription 4

Make sure there is a checkmark next to the subscriber name. Use the drop-down to select a Subscription Database. Click Next.

subscription 5

Distribution Agent Security – click the ellipses on the right side.

subscription 6

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.

subscription 7

Click Next.

Synchronization Schedule – choose Run continuously. Click Next.

subscription 8

Initialize Subscriptions – make sure Initialize is checked, choose Immediately. Click Next.

subscription 9

Wizard Actions – select both options. Click Next.

subscription 10

Script File Properties – give the file a name. Review the other options. Click Next.

subscription 11

Click Finish. All steps should have a green checkmark next to them.

subscription 12

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.