This page includes configuration steps on setting up transactional replications on a Microsoft SQL Server database that is part of an Always On Availability Group. It uses the environment described below as an example.
In a Microsoft SQL Server environment, an availability group can provide a fail-over environment for a set of user databases by supporting a set of primary databases and one to eight sets of secondary databases. Each set of availability databases is hosted by an availability replica. A single primary replica hosts the primary databases, and one to eight secondary replicas, which host a set of secondary databases that serve as potential fail-over targets for the availability group. The use of Always On Availability Groups requires the establishment of a Windows Server Fail-over Clustering (WSFC) cluster. Each availability replica within an availability group resides on a different node of the same WSFC cluster. For more information on Availability Groups and the Always On concept, refer to Microsoft SQL Server documentation, for example, Overview of Always On Availability Groups (SQL Server).
Environment
The below example describes how to set up and use an Always On Availability Group with Syniti Replicate.
The initial Always On SQL Server environment:
VH4-206: Synchronous Replica “ Current Primary
VH4-207: Synchronous Replica
Availability Group: TEST1_AG
AG database: AGTest1
AG Listener: TEST1_AG_Listen (192.168.1.81, port 1433)
Cluster name: AGTestcluster
The environment setup for replication with Syniti Replicate:
VH4-206: Original Publisher
VH4-207: Publisher Replica
VH4-205: Distributor
Note
Do not set a distributor on any of the publishers in this case as the fail-over of a distributor is not supported.
Configure the Replication Environment
Configure a Remote Distributor
Connect to VH4-205 from Microsoft SQL Server Management Studio.
From the Object Explorer panel, right-click Replication, and then select Configure Distribution. The Configure Distribution Wizard is displayed.
Click Next and select VH4-205 as Distributor, and then click Next.
Select the Yes, configure the SQL Server Agent service to start automatically option, and then click Next.
Enter the Snapshot Folder location and then click Next.
Enter DBRS_distribution in the Distribution Database Name field and specify the folder path for distribution database file and database log file, and then click Next. The VH4-205 distributor is displayed as a publisher.
Click Add > Add SQL Server Publisher and add the VH4-206 and VH4-207 distributors to connect to each server that will act as publisher. Note that VH4-205 already exists in the list.
Click Next.
Enter the password that the publishers will use to connect to the distributor, and then click Next.
Note
This password is used internally between publishers and distributor. Make a note of it as you will need to use it again when configuring a new publisher.
Select Configure Distribution, and then click Next.
Click Finish to complete the wizard.
The distributor is now set up successfully.
Configure Distribution at the Primary Replica
Connect to the VH4-206 primary replica.
From the Object Explorer panel, right-click Replication, and then select Configure Distribution. The Configure Distribution Wizard is displayed.
Click Next and select the Use the following server as the Distributor option to connect to another server as the remote distributor, and then click Add.
Select VH4-205 and click Next.
Enter the same password that you have used earlier to configure the distributor and click Next.
Select Configure Distribution, and then click Next.
Click Finish to complete the wizard.
The remote distributor is now set up successfully.
Configure Distribution for the Secondary Replica
To connect to the secondary replica, VH4-207, repeat the same steps as mentioned in Configure Distribution at the Primary Replica.
Note
If the distributor is being used by multiple publishers, steps mentioned in Set Log Reader Agent PublisherFailoverPartner Property can be skipped, please perform step in section 8 first and then continue with steps 5 onwards.
Set Log Reader Agent PublisherFailoverPartner Property
The SQL Server Log Reader Agent is set up to monitor the transaction log on the primary publisher, VH4-206. In case of fail-over to VH4-207, the agent job cannot be switched to pick up changes from the secondary (now primary) replica, unless the PublisherFailoverPartner property is set to VH4-207. This is the fail-over partner instance participating in a database mirroring session with the publication database. On fail-over, the publisher of the secondary replica will start to replicate to the remote distributor.
Complete the following steps to set Log Reader Agent PublisherFailoverPartner property:
Connect to the VH4-205 distributor.
Right-click Replication, and then select Distributor Properties. The Distribution Properties dialog box is displayed.
Select Profile Defaults, and then select the Log Reader Agents page from the list. Ensure that the Default agent profile (default for new agents) check box is selected.
Complete the following steps to make a copy of this profile and apply a custom setting:
Click New to open the New Agent Profile dialog box.
Enter a name for the new profile.
Deselect the Show only parameters used in this profile check box.
Scroll down to access the PublisherFailoverPartner property and set the name of the secondary replica as VH4-207.
Click OK to save the profile.
In the Agent Profile dialog box, select the new agent profile as default instead of the Default agent profile and click OK.
Click OK to exit the Distributor Properties dialog box.
Note
The above steps is for setting the agent profile work for a single secondary replica. For multiple secondary replicas. it may be possible to define multiple agent profiles, each one using a different PublisherFailoverPartner value, and set it properly as default to each publisher. However, the details are beyond the scope of this document. Please contact Syniti Software support at support.syniti.com for additional information.
Add Linked Servers to Secondary Replicas
In the event that a secondary replica transitions to the primary role, it must be configured so that the secondary can take over after a fail over. All possible publishers will connect to the subscriber using a linked server.
Complete the following steps to create a linked server to the subscriber, VH4-205:
Open a connection to the secondary replicas and create the linked server to it.
Connect to the secondary replica, VH4-207.
Execute the following query:
EXEC sys.sp_addlinkedserver @server = 'VH4-205'Configure the SQL Server Connection in Syniti Replicate
Configure the source connection in Syniti Replicate that points to the SQL Server Availability Group (AG), such that the application can connect to the AG and switch dynamically among the cluster replicas.
Complete the following steps to configure the SQL Server Connection in Syniti Replicate:
In the Management Center, right-click and select Add New Connection from the Metadata Explorer Sources list. The Add Source Connection Wizard is displayed.
On the Select Provider page, enter a name and select Microsoft SQL Server as the data provider, and then click Next.
On the Set Connection String page, specify the connection parameters
In the Setup Info screen, select Log Reader.
Set the IP address that you use to connect to SQL Server from Syniti Replicate to the Availability Group listener IP address. The listener IP allows Syniti Replicate to automatically switch from one replica to another once a failover has occurred.
Specify the user name and password for the connection, and then click Verify to check for an existing distributor. Syniti Replicate determines a that remote distributor has been established but a User ID is required to be able to connect to it.
The Change Distributor dialog box automatically shows the remote distributor name as VH4-205, and prompts you to specify a user name and password.
Enter the user name and password and click OK. An alert message displays that the distributor is set up correctly. Click Next.
On the Select Tables page, select a few test tables from the AG database, for example, AGTest1.
Click Next and click Finish to create the connection.
Add Replications and Create the Publication Database
Add a target connection and create replications in the usual way.
Redirect the Publisher to the AG Listener Name
In the SQL Server Management Studio, connect to the VH4-205 distributor.
For each replica (VH4-206 and VH4-207), execute the sp_redirect_publisher stored procedure to associate the original publisher and the published DB with the AG listener name.
USE DBRS_distribution;
GO
EXEC sys.sp_redirect_publisher
@original_publisher = 'VH4-206',
@publisher_db = 'AGTest1',
@redirected_publisher = 'TEST1_AG_Listen';Note
Execute this stored procedure again, replacing VH4-206 with VH4-207.
In the distribution database, for each replica (VH4-206 and VH4-207), execute the sp_validate_replica_hosts_as_publishers stored procedure to verify that the replica host is now configured to serve as publisher for the published database.
USE DBRS_distribution;
GO
DECLARE @redirected_publisher sysname;
EXEC sys.sp_validate_replica_hosts_as_publishers
@original_publisher = 'VH4-206',
@publisher_db = 'AGTest1',
@redirected_publisher = 'TEST1_AG_Listen';Note
Execute this stored procedure again, replacing VH4-206 with VH4-207. This procedure is necessary whenever a new database is added to a replication; the publisher database will have to be specified in the queries.
Validate the Configuration
Let the replications start and run the initial refresh.
Execute some transactions from the active node (VH4-206) and verify that they are correctly replicated to the target.
Execute a manual fail over to the secondary replica using the Fail over wizard in SQL Server. Wait until you see the secondary node become the primary replica.
Execute some transactions from the active node (VH4-207) and verify that they are correctly replicated to the target.
Note
The SQL Server Reader Agent has to be running on the Distributor machine only.
When creating the distributor, the following error may be generated:
Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider "SQLNCLI11" for linked server "repl_distributor" returned message "Login timeout expired".
OLE DB provider "SQLNCLI11" for linked server "repl_distributor" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible.
Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Solution:
Check if the instance name is correct, by running ˜select @@servername™. This name must match the server name you are using in the distributor wizards and in the SQL statements you have run so far, for instance ˜VH4-207™. If this is not the case, you must reconfigure the name of the local instance by executing the following commands:
sp_dropserver ‘oldname’ (the name found from the select @servername)
sp_addserver ‘newname’ (for instance, VH4-207)Restart the SQL Server services
When running the fail over to the VH4-207, ensure that the Log Server Agent is running and has no errors.
Complete the following steps to check eventual errors:
In the SQL Server Console, click SQL Server Agent.
Expand and double click Job Activity Monitor. Check the job named to match the database (in the example above 'VH4-206-AGTest1-1').
In the SQL Server Console, click Replication.
Select Launch Replication Monitor. Check if all publishers are functioning or show any error icon. In case of errors, expand to find the error items.
For example, the following error could occur:
The process could not execute 'sp_replcmds' on VH4-207
Status: 0, code: 15517, text: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'.
Solution:
The error states that the current owner of the job is not a DBO on the Publication Database. Hence, the next logical step is to make the job owner the DBO of the database as follows.
Connect to VH4-207.
Change the owner of the database using the following command:
USE AGTest1;
sp_changedbowner 'sa'Further Resources
For a complete up-to-date list of supported sources and targets, and information on where to obtain a .NET provider, check the Supported .NET Providers article in the Help Center.
The Help Center also provides database-specific setup notes in PDF format.