Can I set up Replication on a clustered Instance of 2 Nodes. If yes then is it mandatory to have a 3 separate Instances for Distributor, Publisher and lastly Subscriber . If there is a fail over then will the replication break ?
Sql-server – Replication on a clustered Instance
clusteringhigh-availabilitysql servertransactional-replication
Related Solutions
What is the best practice in order to have this scenario done?
It depends on how much data you are replicating i.e.
- Are you replicating selected columns/tables, entire database (all tables), etc ?
- Are both nodes located in the same domain or different domains ?
- Are you replicating in same region or cross region (US-UK, etc) ?
I have implemented T-Rep where I have used same server as publisher and distributor as the data that was needed to replicate was less and also, have implemented separate distribution database on separate server that does all the heavy lifting of publishing the data to subscribers where we had massive data to push down to subscribers.
You have to consider factors like -
- time taken to perform snapshot and applying that snapshot to subscribers
- Feasibility of re-initializing the articles when a major (e.g. schema) change occurs to the tables that are involved in replication.
should I create 2 distributors? You can use the same distribution database. Though, for ease of maintenance and better performance [reducing contention - both writing to and reading from the distribution database] I would highly recommend you use separate Distribution databases.
Remember that distribution database is the heart of replication. So it requires proper maintenance, backups, etc. Now if you have just 1 distribution database that supports multiple publishers and a DISASTER happened, then restoring it from a previous backup will impact ALL publishers.
From BOL :
In many cases, a single distribution database is sufficient. However, if multiple Publishers use a single Distributor, consider creating a distribution database for each Publisher. Doing so ensures that the data flowing through each distribution database is distinct.
Lastly some good references that will help you :
Deep Dive on Initialize from Backup for Transactional Replication
Replicating Non-Clustered Indexes Improves Subscriber Query Performance
Follow the Data in Transactional Replication - Whitepaper
I think you are absolutely right on here:
The installer creates the distributor on the publisher, which I assume will break replication after a failover and require generation of new snapshots and reinitializion of subscriptions.
a good way to relate the replication to the availability group is:
The primary is SP1 and the secondary replica is SS2. The distributor is on another server SD3. The replication is working fine. We redirected the Original Publisher to the AG Listener Name using this script:
USE distribution;
GO
EXEC sys.sp_redirect_publisher
@original_publisher = 'MyPublisher',
@publisher_db = 'MyPublishedDB',
@redirected_publisher = 'MyAGListenerName';
You can find more details on this link:
Replication log reader not updated to new primary after availability group failover
In an AlwaysOn availability group a secondary database cannot be a publisher.
The failover of a distributor on an availability database is not supported.
I would suggest you use another server as a distributor, as you are probably already doing.
I normally use another server as a distributor and several distributor databases depending on the size and how busy your publications are.
I am not entirely sure about the push subscriptions but for the other stuff you could have a look at this link:
Setting up Replication on a database that is part of an AlwaysOn Availability Group
SRV1: Original Publisher
SRV2: Publisher Replica
SRV3: Publisher Replica
SRV4: Distributor and Subscriber (You can choose a completely new server to be the distributor as well, however do not have a distributor on any of the publishers in this case as the failover of a distributor is not supported in this case).
maybe this will not apply on your case, but just for reference:
Configure Replication for Always On Availability Groups (SQL Server)
Am I on the right track in saying the most reliable method of "pausing" replication would be to use sp_MSstopdistribution_agent on the publisher (for PUSH) and on subscriber (for PULL) before failover to the (non-distributor) node, then do a sp_MSstartdistribution_agent after we fail back again?
I would say in most cases yes, I used to use those procedures as you can see (scripts included) on this link:
How to restart the distributor agent of transactional replication?
Now I prefer to use the following method:
-- on the publisher
-- this gives you the distributor server name and distributor database name
sp_helpdistributor
-- now connect to the distributor server and database
-- get the job name
-- get the distributor job name for the publication you want
-- in this example my publication is called 'DEOrder'
use distribution
go
my database and publication are called ATOrder
in the example below.
-- the snapshot agent
select job_name=name, publisher_db, publication from distribution.dbo.mssnapshot_agents
where publisher_db = N'ATOrder'
-- the distributor jon transactional replication
select job_name=name, publisher_db, publication from distribution.dbo.MSlogreader_agents
where publisher_db = N'ATOrder'
-- if it is merge replication
select job_name=name, publisher_db, publication from MSmerge_agents
where publisher_db = N'ATOrder'
-- who are my publishers and who are my subscribers
select * from MSsubscriber_info
-- just checking dbs
select * from MSpublisher_databases
-- get the distributor job (log reader) for a specific publication
select job_name=name, publisher_db, publication from distribution.dbo.MSlogreader_agents
where publisher_db = N'ATOrder'
-- take note of the job name
--my_publication_server_name_and_instance2-ATOrder-25
-- check that the job is running at the moment - it must be!!
sp_runningjobs 'my_publication_server_name_and_instance2-ATOrder-25'
--check I am targeting the right job
exec msdb.dbo.sp_help_job @job_name = 'my_publication_server_name_and_instance2-ATOrder-25'
--stop the job
exec msdb.dbo.sp_stop_job @job_name = 'my_publication_server_name_and_instance2-ATOrder-25'
--start the job
exec msdb.dbo.sp_start_job @job_name = 'my_publication_server_name_and_instance2-ATOrder-25'
for more information, as to see if everything is going on alright, or who and what is getting a bit slow, you can check this link:
Replication Monitor Information using T-SQL
We are using SQL 2014 on both publisher and subscriber.
Lucky you
Related Question
- SQL Server Replication – Convert Existing Subscriber Database to Publisher
- SQL Server – Set Production Database as Publisher and Distributor for Snapshot Replication
- Sql-server – Snapshot Folder Location for Replication with Clustered Instance
- SQL Server – Database Compatibility Requirement for Transactional Replication
- Subscriber Node Down in SQL Server Transactional Replication
Best Answer
Replication doesn't care about or interact with clustering.
Replication is set up from SQL instance to SQL instance, the physical host that the instance happens to be running on at that moment isn't relevant.
If a failover occurs, a momentary interruption in replication will be experienced as the instance goes offline, but will resume once SQL comes up on the other host.
What? No.
You can have a single SQL instance be the distributor, publisher, AND subscriber if you want to. (Not common, but possible to push changes from one database to another on the same host.)
A more typical configuration is to have two SQL instances (a publisher/distributor and a subscriber).
But all of those business decisions are completely independent of clustering.