SQL replication with a Publisher Database in Always On Availability Groups
SQLShack
SQL Server training Español SQL replication with a Publisher Database in Always On Availability Groups
July 8, 2019 by Ranga Babu In this article, we will review how to setup SQL replication with publisher database in Always On availability groups so that replication continuous to sync even after failover to the secondary server in the availability group. The following are the steps involved in setting up SQL Server replication with publisher database in Always on Availability Groups. Configuring the remote distribution Configure publishers to use the remote distribution Add the publisher database to the Always on Availability Group Configuring the SQL Server replication Creating the linked servers to subscribers on the secondary servers Redirect the original publisher to the Always on Availability Group Listener Let’s go over these steps one by one. For the demo purposes, I am using the following servers to configure SQL replication. SQL01V– Primary server SQL02V– Secondary server SQL03V – Remote distribution server SQL03V – Subscriber Configuring the remote distribution
We need to configure the distribution database on a remote server and the remote distribution server must be available for both the nodes in the Always on availability group. For demo purposes, I used mssql3 for both distribution servers and as a subscriber server. Log in to the distribution server mssql3 using SQL Server Management Studio, navigate to the Replication folder, Right-click and click on Configure Distribution. Click Next on Configure Distribution Wizard window. Select the first option i.e. ‘SQL03V’ will act as its own Distributor; SQL Server will create a distribution database and log and click Next. In case if your SQL Server Agent service is not set to start automatically, you will see the below window that asks you to select the SQL Server Agent startup behavior. Select “Yes, configure the SQL Server Agent service to start automatically” and click Next. This is because the replication setup creates replication agent job’s which are run by SQL server agent that runs as per schedule or continuously to synchronize the data from the publisher to the subscriber or from the subscriber to the publisher. In case, if you select a manual option and your SQL server agent is not running, the data synchronization between publisher and subscriber stops. It is advised to select the “Yes, configure the SQL Server Agent service to start automatically” so that SQL replication runs unattended. Enter the snapshot folder location and click Next. The location you entered will be used to store the initial snapshot generated by the snapshot agent. Use the shared path which is accessible to the subscriber in case if your agent runs on the subscriber (pull subscription). if you want a custom name, Enter the name of the distribution database else set it to default. Enter the location of the data and log file of the distribution database and click Next. Click Next. Select configure distribution, Click Next, and Finish. Once the distribution database setup is complete, right-click on Replication folder and click on Distributor Propertie Click on Publishers, give the password of your choice. This password will be used when configuring the distribution on both nodes (SQL01V and SQL02V) in the Always on Availability group. Click on Add -> Add SQL Server Publisher. Enter SQL01V as a server name, enter the login credentials and add SQL01V as a publisher. Similarly, add SQL02V as a publisher. In this case, I have SQL01V and SQL02V as possible failover nodes. If you have more than two nodes participating in Always on Availability Group, Add all the nodes as publishers at the distribution server. Configure publishers to use the remote distribution
Make sure SQL replication is installed on all the replica nodes in the Always on Availability Group. Executing the following T-SQL script on each replica node. It should return 1. 12345 USE master; GO DECLARE @installed int; EXEC @installed = sys.sp_MS_replication_installed; SELECT @installed; Now we need to configure the publishers SQL01V and SQL02V to use remote distribution server SQL03V which was set up above. Log in to the primary node SQL01V using SQL Server Management Studio. Navigate to the Replication folder. Right-click on the Replication folder and click on Configure Distribution. Click Next. select “Use the following server as distributor”. Click on Add and add the remote distribution server SQL03V which was configured above. Click Next. Enter the administrative password you set above. Click Next -> Enable Configure Distribution -> Click Next and Finish. Similarly, configure the secondary node SQL02V to use the same remote distribution server. If you have more nodes in the Availability group, configure all the possible failover nodes to use the same remote distribution server. Add the publisher database to the availability group
After configuring the publishers to use the remote distribution server, we need to add the database to the availability group on which we are going to configure the SQL Server replication. i.e. Publisher database. This part can also be done after configuring the SQL replication. Please refer to the below links to configure Always on Availability group and add the database to the availability group. Configuring a SQL Server AlwaysOn High Availability Group Read Scale Availability Group in a clusterless availability group Configuring the SQL Server replication
Log in to primary node SQL01V using SQL Server Management Studio, Navigate to the Replication folder and then Local Publications. Right-click and click on New Publication. Click Next and select the database that you want to replicate. Click Next and select the type of replication. Click Next. Select 2008 or later and click Next. Select the tables you want to replicate and click Next. Enable Create Snapshot Immediately and Click Next. Configure Agent Security and click Next. Enable Create the Publication. Click Next, Enter the name of the publication in SQL Replication and click Finish. Once you click on Finish, the publication is created, and the snapshot agent is started which generates the snapshot. Once the snapshot is generated add the subscriber and apply the initial snapshot. To add the subscriber, log in to the publisher SQL01V and navigate to Replication -> Local Publications. Right-click on the publication you created above and click on New Subscriptions. Click Next. Select the publication and click Next. Select the Agent location and click Next. Click on Add SQL Server subscriber and add SQL03V as a subscriber. Configure agent security and click Next. Schedule the SQL replication agent to run as per your choice. Initialize the subscription Immediately to apply the snapshot immediately after creation. Select the subscription type as Server if you want to republish the subscriber else select the subscription type as Client and create the subscriber. Once the subscriber is created the initial snapshot is applied and all the incremental data changes are replicated to the subscriber from the publisher and vice versa. Creating linked servers to the subscribers on the secondary servers
We need to create a linked server for SQL03V (subscriber) on SQL02V (secondary node). Repeat this for all the SQL replication subscribers on all the possible failover nodes in the Always on Availability group. Please refer to the following sample script to add a linked server. 12 EXEC sp_addlinkedserver @server = 'SQL03V'; Redirect the original publisher to the Always on Availability Group Listener
Log in to the remote distribution server SQL03V using SQL Server management studio. Execute the following script on the distribution database to redirect the original publisher to Always on the listener. 123456 USE distribution; GO EXEC sp_redirect_publisher @original_publisher = 'SQL01V', @publisher_db = 'pub_AOAG', @redirected_publisher = 'MyAGListenerName'; At the distribution database, Execute the following script to validate the redirection. 1234567 USE distribution; GO DECLARE @redirected_publisher sysname; EXEC sys.sp_validate_replica_hosts_as_publishers @original_publisher = 'SQL01V', @publisher_db = 'pub_AOAG', @redirected_publisher = @redirected_publisher output; Once the validation is completed, manually failover the primary node to the secondary node and test the replication agent synchronization and verify the data on the SQL replication publishers and the subscribers. Conclusion
In this article, we explored how to create SQL replication with a publisher in Always on Availability Group. In case you have any questions, please feel free to ask in the comment section below. Author Recent Posts Ranga BabuSQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies Latest posts by Ranga Babu (see all) Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019 Overview of the Collate SQL command - October 22, 2019 Recover a lost SA password - September 20, 2019 Related posts
SQL Server Replication on a mirrored database SQL Server replication: Configuring Snapshot and Transactional Replication SQL Server replication configuration: Peer to Peer and Merge Replication SQL Server database migration to Azure SQL Database using SQL Server Transactional Replication SQL Server Transactional Replication Moving Distribution Database – step-by-step guide 27,675 Views Follow us
Popular
SQL Convert Date functions and formats SQL Variables: Basics and usage SQL PARTITION BY Clause overview Different ways to SQL delete duplicate rows from a SQL Table How to UPDATE from a SELECT statement in SQL Server SQL Server functions for converting a String to a Date SELECT INTO TEMP TABLE statement in SQL Server SQL WHILE loop with simple examples How to backup and restore MySQL databases using the mysqldump command CASE statement in SQL Overview of SQL RANK functions Understanding the SQL MERGE statement INSERT INTO SELECT statement overview and examples SQL multiple joins for beginners with examples Understanding the SQL Decimal data type DELETE CASCADE and UPDATE CASCADE in SQL Server foreign key SQL Not Equal Operator introduction and examples SQL CROSS JOIN with examples The Table Variable in SQL Server SQL Server table hints – WITH (NOLOCK) best practices Trending
SQL Server Transaction Log Backup, Truncate and Shrink Operations Six different methods to copy tables between databases in SQL Server How to implement error handling in SQL Server Working with the SQL Server command line (sqlcmd) Methods to avoid the SQL divide by zero error Query optimization techniques in SQL Server: tips and tricks How to create and configure a linked server in SQL Server Management Studio SQL replace: How to replace ASCII special characters in SQL Server How to identify slow running queries in SQL Server SQL varchar data type deep dive How to implement array-like functionality in SQL Server All about locking in SQL Server SQL Server stored procedures for beginners Database table partitioning in SQL Server How to drop temp tables in SQL Server How to determine free space and file size for SQL Server databases Using PowerShell to split a string into an array KILL SPID command in SQL Server How to install SQL Server Express edition SQL Union overview, usage and examples Solutions
Read a SQL Server transaction logSQL Server database auditing techniquesHow to recover SQL Server data from accidental UPDATE and DELETE operationsHow to quickly search for SQL database data and objectsSynchronize SQL Server databases in different remote sourcesRecover SQL data from a dropped table without backupsHow to restore specific table(s) from a SQL Server database backupRecover deleted SQL data from transaction logsHow to recover SQL Server data from accidental updates without backupsAutomatically compare and synchronize SQL Server dataOpen LDF file and view LDF file contentQuickly convert SQL code to language-specific client codeHow to recover a single table from a SQL Server database backupRecover data lost due to a TRUNCATE operation without backupsHow to recover SQL Server data from accidental DELETE, TRUNCATE and DROP operationsReverting your SQL Server database back to a specific point in timeHow to create SSIS package documentationMigrate a SQL Server database to a newer version of SQL ServerHow to restore a SQL Server database backup to an older version of SQL Server Categories and tips
►Auditing and compliance (50) Auditing (40) Data classification (1) Data masking (9) Azure (295) Azure Data Studio (46) Backup and restore (108) ►Business Intelligence (482) Analysis Services (SSAS) (47) Biml (10) Data Mining (14) Data Quality Services (4) Data Tools (SSDT) (13) Data Warehouse (16) Excel (20) General (39) Integration Services (SSIS) (125) Master Data Services (6) OLAP cube (15) PowerBI (95) Reporting Services (SSRS) (67) Data science (21) ►Database design (233) Clustering (16) Common Table Expressions (CTE) (11) Concurrency (1) Constraints (8) Data types (11) FILESTREAM (22) General database design (104) Partitioning (13) Relationships and dependencies (12) Temporal tables (12) Views (16) ►Database development (418) Comparison (4) Continuous delivery (CD) (5) Continuous integration (CI) (11) Development (146) Functions (106) Hyper-V (1) Search (10) Source Control (15) SQL unit testing (23) Stored procedures (34) String Concatenation (2) Synonyms (1) Team Explorer (2) Testing (35) Visual Studio (14) DBAtools (35) DevOps (23) DevSecOps (2) Documentation (22) ETL (76) ▼Features (213) Adaptive query processing (11) Bulk insert (16) Database mail (10) DBCC (7) Experimentation Assistant (DEA) (3) High Availability (36) Query store (10) Replication (40) Transaction log (59) Transparent Data Encryption (TDE) (21) Importing, exporting (51) Installation, setup and configuration (121) Jobs (42) ►Languages and coding (686) Cursors (9) DDL (9) DML (6) JSON (17) PowerShell (77) Python (37) R (16) SQL commands (196) SQLCMD (7) String functions (21) T-SQL (275) XML (15) Lists (12) Machine learning (37) Maintenance (99) Migration (50) Miscellaneous (1) ▼Performance tuning (869) Alerting (8) Always On Availability Groups (82) Buffer Pool Extension (BPE) (9) Columnstore index (9) Deadlocks (16) Execution plans (125) In-Memory OLTP (22) Indexes (79) Latches (5) Locking (10) Monitoring (100) Performance (196) Performance counters (28) Performance Testing (9) Query analysis (121) Reports (20) SSAS monitoring (3) SSIS monitoring (10) SSRS monitoring (4) Wait types (11) ►Professional development (68) Professional development (27) Project management (9) SQL interview questions (32) Recovery (33) Security (84) Server management (24) SQL Azure (271) SQL Server Management Studio (SSMS) (90) SQL Server on Linux (21) ►SQL Server versions (177) SQL Server 2012 (6) SQL Server 2016 (63) SQL Server 2017 (49) SQL Server 2019 (57) SQL Server 2022 (2) ►Technologies (334) AWS (45) AWS RDS (56) Azure Cosmos DB (28) Containers (12) Docker (9) Graph database (13) Kerberos (2) Kubernetes (1) Linux (44) LocalDB (2) MySQL (49) Oracle (10) PolyBase (10) PostgreSQL (36) SharePoint (4) Ubuntu (13) Uncategorized (4) Utilities (21) Helpers and best practices BI performance counters SQL code smells rules SQL Server wait types © 2022 Quest Software Inc. ALL RIGHTS RESERVED. GDPR Terms of Use Privacy