This is part 3 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 publisher

You’ll need to start by configuring the AG current primary as Publisher. (In this demo, this is SQL2014AG2.) Then, you’ll need to configure the distributor that we set up in step 2 to use the AG listener name, rather than the server name.

Log into the primary – SQL2014AG2.

Expand Replication, right-click Local Publications, select New Publication.

Click Next on Welcome.

Distributor – select Use the following server and click Add. Connect to the distributor you configured in the previous step. Click Next.

Administrative Password – enter the distributor admin password you created in the previous step. Click Next. (If you add multiple publications from one server, you only do this the first time.)

Publication database – choose your database. Click Next.

Publication type – because this is going to Azure SQL DB, it must be Transactional. Select your option. Click Next.

Articles – choose which database objects you want to replicate. Click Next.

Filter Table Rows – if you need to filter, click Add. Otherwise, click Next.

Snapshot Agent – choose to create a snapshot immediately. Click Next.

Agent Security – click Security Settings to choose what account the snapshot and log reader agents will run under. Click OK. Click Next.

(I could write an entire blog post about this, but that is better left to people more well-versed in replication than myself. I know that I don’t want to use the SQL Server Agent service account – I want to create an AD account that has been granted the permissions needed as described in  Replication Agent Security Model.)

Wizard Actions – choose both options. Click Next.

Script File Properties – choose a location to save the script. Review the other options. Click Next.

Complete the wizard – enter a Publication name. Click Finish.

All options should have green checkmarks.

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.)

Configure distribution on all other publishers

Because my database is in an AG, I need to make sure that the distributor is configured on all the replicas in the AG. Note: you only have to do this step for the first publication – not each subsequent publication.

In this example, I’ll need to configure SQL2014AG1, the other replica.

Connect to SQL2014AG1. Right-click Replication and select Configure Distribution.

Welcome – click Next.

Distributor – select Use the following server. Click Add. Connect to the previously-created distributor. Click Next.

Administrative password – enter the distribution admin password. Click Next.

Wizard Actions – check both boxes, click Next.

[

]17

Script File Properties – choose a file location and name. Review other options. Click Next.

Complete the wizard – click Finish. All options should have green checkmarks.

Create linked server from each replica to distribution server

This step also needs to be done only once, for the first publication.

Connect to each replica. Run this script.

USE master;
GO

EXEC sys.sp_addlinkedserver @server = 'DistributorServerName';
GO

After doing that, in Object Explorer, expand Server Objects > Linked Servers. Right-click the linked server and select Test Connection. Make sure that is successful.

Redirect the original publisher name to the listener name

This needs to be done for every publication you set up where the database is in an AG.

This step also needs to be done from the distributor server – you can’t do this from SSMS on another server or your workstation. You must RDP to the distributor and run these commands – in SSMS or even SQL CMD.

Connect to the distributor.

Run the following command.

USE distribution;
GO

EXEC sys.sp_redirect_publisher @original_publisher = 'SQL2014AG2', @publisher_db = 'AGTest', @redirected_publisher = 'JesTestAGListen';
GO

Expected output is “Commands completed successfully.”

Then, you want to confirm this worked. To do so, run the following command.

DECLARE @redirected_publisher sysname;

EXEC sys.sp_validate_replica_hosts_as_publishers @original_publisher = 'SQL2014AG2', @publisher_db = 'AGTest', @redirected_publisher = 'JesTestAGListen';
GO

Expected output is “Commands completed successfully.”

After following all of these steps, your publication is ready. You can now set up the subscriber(s).