Sql-server – Replication for Mirrored server

sql serversql-server-2012

I have 3 SQL servers running SQL Server 2012 Standard Edition, called A , B and C. Server B is a mirror of server A. There is transactional replication between server A and Server C.

How can I ensure replication continues to C regardless of which server is the primary server in the mirrored pair?

Best Answer

The -PublisherFailoverPartner option should help you out in your case. I am assuming you want to have replication running irrespective of database running on principal/mirror server.

Below links have your issue covered and has all the details on how to configure it.

http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/replication-and-mirroring-setup/

https://www.pythian.com/blog/how-to-configure-transactional-replication-mirroring-failover/

https://msdn.microsoft.com/en-us/library/ms151799.aspx

Test it out in your test environment before implementing it on PROD.

Related Solutions

Sql-server – Transaction log growing on a mirrored principal

Mirror, Synchronized / Restoring... is the typical state for the mirror instance.

You can determine why the log file is growing by issuing the following command in a SQL Server Management Studio query window:

SELECT name, LOG_REUSE_WAIT_DESC 
FROM sys.databases 
WHERE name='your_database_here';

This will show several possible choices, such as:

NOTHING
CHECKPOINT
LOG_BACKUP
ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
OTHER_TRANSIENT

You may need to perform at least one transaction log backup, among other actions depending on the LOG_REUSE_WAIT_DESCRIPTION shown in the query above. For instance, there may be a long-running transaction that is forcing the log files to not be re-used.

Sql-server – SQL Server replication for off site copy

Based on what you have described Log shipping is going to be the way to go. Log shipping is old school, tried and true.

Replication is pretty fragile, provides massive amounts of room for failure especially when it comes to adding new tables and getting those tables into the replication topology.

As for 2012 upgrade costs they may not be as bad as you think. If you have software assurance and an enterprise agreement that'll save you a bunch of money on licensing.

Best Answer

Related Solutions

Sql-server – Transaction log growing on a mirrored principal

Sql-server – SQL Server replication for off site copy

Related Question