Replication and Database Mirroring
Database mirroring can be used to improve availability of certain replication databases. Support for combining transactional replication with database mirroring depends on which replication database is being considered. Peer-to-Peer replication is not supported in combination with database mirroring. For more information about database mirroring, see Database Mirroring Administration.
The following table lists the replication databases and corresponding level of support for mirroring.
Replication database |
Supported for use with database mirroring |
|---|---|
Publication |
Yes. Supported for both merge and transactional replication with automatic failover. The replication agents that connect to the publication database can automatically fail over to the mirrored publication database. In the event of a failure the agents that connect to the publication database will automatically reconnect to the new principal database. |
Distribution |
No. Mirroring the distribution database is not supported. The distribution database is where the replication configuration is stored, and configuration metadata is tightly coupled with the server name where the Distributor is configured. The server name will change during failover to the mirrored database, but configuration data stored in the distribution database will point to the old distribution database server. |
Subscription |
Yes. Supported starting in SQL Server 2008 for transactional replication with manual failover and configuration. The replication agents that connect to the subscription database are not mirror-aware. If the principal subscription database fails, failover to the secondary database requires that you perform several manual steps to restore the replication stream. For more information, see SQL Server Replication: Providing High Availability Using Database Mirroring (SQL Server Technical Article). |
For information about recovering a distribution database or subscription database without any need to reconfigure replication, see Backing Up and Restoring Replicated Databases.
Note
After a failover, the mirror becomes the principal. In this topic, "principal" and "mirror" always refer to the original principal and mirror.
Requirements and Considerations for Using Replication with Database Mirroring
Be aware of the following requirements and considerations when using replication with database mirroring:
The principal and mirror must share a Distributor. We recommend that this be a remote Distributor, which provides greater fault tolerance if the Publisher has an unplanned failover.
The Publisher and Distributor must be Microsoft SQL Server 2005 or a later version. Subscribers can be any version, but merge replication pull subscriptions from a version prior to SQL Server 2005 do not support failover; the agent in this case runs at the Subscriber and previous versions of the agent are not mirror aware. Replication to such Subscribers resumes if the database fails back from the mirror to the principal.
Replication supports mirroring the publication database for merge replication and for transactional replication with read-only Subscribers or queued updating Subscribers. Immediate updating Subscribers, Oracle Publishers, Publishers in a peer-to-peer topology, and republishers are not supported.
Metadata and objects that exist outside the database are not copied to the mirror, including logins, jobs, linked servers, and so on. If you require the metadata and objects at the mirror, you must copy them manually. For more information, see Managing Logins and Jobs After Role Switching (SQL Server).
Configuring Replication with Database Mirroring
Configuring replication and database mirroring involves five steps. Each step is described in more detail in the following section.
Configure the Publisher.
Configure database mirroring.
Configure the mirror to use the same Distributor as the principal.
Configure replication agents for failover.
Add the principal and mirror to Replication Monitor.
Steps 1 and 2 can also be performed in the opposite order.
To configure database mirroring for a publication database
Configure the Publisher:
We recommend using a remote Distributor. For more information about configuring distribution, see Configuring Distribution.
You can enable a database for snapshot and transactional publications and/or merge publications. For mirrored databases that will contain more than one type of publication, you must enable the database for both types at the same node using sp_replicationdboption. For example, you could execute the following stored procedure calls at the principal:
exec sp_replicationdboption @dbname='<PublicationDatabase>', @optname='publish', @value=true exec sp_replicationdboption @dbname='<PublicationDatabase>', @optname='mergepublish', @value=trueFor more information about creating publications, see Publishing Data and Database Objects.
Configure database mirroring. For more information, see How to: Configure a Database Mirroring Session (SQL Server Management Studio) and Setting Up Database Mirroring.
Configure distribution for the mirror. Specify the mirror name as the Publisher, and specify the same Distributor and snapshot folder that the principal uses. For example, if you are configuring replication with stored procedures, execute sp_adddistpublisher at the Distributor; and then execute sp_adddistributor at the mirror. For sp_adddistpublisher:
Set the value of the @publisher parameter to the network name of the mirror.
Set the value of the @working_directory parameter to the snapshot folder used by the principal.
Specify the mirror name for the –PublisherFailoverPartner agent parameter. Agent This parameter is required for the following agents to identify the mirror after failover:
Snapshot Agent (for all publications)
Log Reader Agent (for all transactional publications)
Queue Reader Agent (for transactional publications that support queued updating subscriptions)
Merge Agent (for merge subscriptions)
SQL Server replication listener (replisapi.dll: for merge subscriptions synchronized using Web synchronization)
SQL Merge ActiveX Control (for merge subscriptions synchronized with the control)
The Distribution Agent and Distribution ActiveX Control do not have this parameter because they do not connect to the Publisher.
Agent parameter changes take effect the next time the agent is started. If the agent runs continuously, you must stop and restart the agent. Parameters can be specified in agent profiles and from the command prompt. For more information, see:
How to: Work with Replication Agent Profiles (SQL Server Management Studio)
How to: View and Modify Replication Agent Command Prompt Parameters (SQL Server Management Studio)
How to: Work with Replication Agent Profiles (Replication Transact-SQL Programming)
We recommend adding the –PublisherFailoverPartner to an agent profile, and then specifying the mirror name in the profile. For example, if you are configuring replication with stored procedures:
-- Execute sp_help_agent_profile in the context of the distribution database to get the list of profiles. -- Select the profile id of the profile that needs to be updated from the result set. -- In the agent_type column returned by sp_help_agent_profile: -- 1 = Snapshot Agent; 2 = Log Reader Agent; 3 = Distribution Agent; 4 = Merge Agent; 9 = Queue Reader Agent. exec sp_help_agent_profile -- Setting the -PublisherFailoverPartner parameter in the default Snapshot Agent profile (profile 1). -- Execute sp_add_agent_parameter in the context of the distribution database. exec sp_add_agent_parameter @profile_id = 1, @parameter_name = N'-PublisherFailoverPartner', @parameter_value = N'<Failover Partner Name>' -- Setting the -PublisherFailoverPartner parameter in the default Merge Agent profile (profile 6). -- Execute sp_add_agent_parameter in the context of the distribution database. exec sp_add_agent_parameter @profile_id = 6, @parameter_name = N'-PublisherFailoverPartner', @parameter_value = N'<Failover Partner Name>'Add the principal and mirror to Replication Monitor. For more information, see How to: Add and Remove Publishers from Replication Monitor (Replication Monitor).
Maintaining a Mirrored Publication Database
Maintaining a mirrored publication database is essentially the same as maintaining a non-mirrored database, with the following considerations:
Administration and monitoring must occur at the active server. In SQL Server Management Studio, publications appear under the Local Publications folder only for the active server. For example, if you failover to the mirror, the publications are displayed at the mirror and are no longer displayed at the principal. If the database fails over to the mirror, you might need to manually refresh Management Studio and Replication Monitor for the change to be reflected.
Replication Monitor displays Publisher nodes in the object tree for both the principal and the mirror. If the principal is the active server, publication information is displayed only under the principal node in Replication Monitor.
If the mirror is the active server:
If an agent has an error, the error is indicated only on the principal node, not on the mirror node.
If the principal is unavailable, the principal and mirror nodes display identical lists of publications. Monitoring should be performed on the publications under the mirror node.
When using stored procedures or Replication Management Objects (RMO) to administer replication at the mirror, for cases in which you specify the Publisher name, you must specify the name of the instance on which the database was enabled for replication. To determine the appropriate name, use the function publishingservername.
When a publication database is mirrored, the replication metadata stored in the mirrored database is identical to the metadata stored in the principal database. Consequently, for publication databases enabled for replication at the principal, the Publisher instance name stored in system tables at the mirror is the name of the principal, not the mirror. This affects replication configuration and maintenance if the publication database fails over to the mirror. For example, if you are configuring replication with stored procedures on the mirror after a failover, and you want to add a pull subscription to a publication database that was enabled at the principal, you must specify the principal name rather than the mirror name for the @publisher parameter of sp_addpullsubscription or sp_addmergepullsubscription.
If you enable a publication database at the mirror after failover to the mirror, the Publisher instance name stored in system tables is the name of the mirror; in this case, you would use the name of the mirror for the @publisher parameter.
Note
In some cases, such as sp_addpublication, the @publisher parameter is supported only for non-SQL Server Publishers; in these cases, it is not relevant for SQL Server database mirroring.
To synchronize a subscription in Management Studio after a failover: synchronize pull subscriptions from the Subscriber; and synchronize push subscriptions from the active Publisher.
Replication Behavior if Mirroring is Removed
Keep the following issues in mind if database mirroring is removed from a published database:
If the publication database at the principal is no longer mirrored, replication continues to work unchanged against the original principal.
If the publication database fails over from the principal to the mirror and the mirroring relationship is subsequently disabled or removed, replication agents will not function against the mirror. If the principal is permanently lost, disable and then reconfigure replication with the mirror specified as the Publisher.
If database mirroring is removed completely, the mirror database is in a recovery state and must be restored in order to become functional. The behavior of the recovered database with respect to replication depends on whether the KEEP_REPLICATION option is specified. This option forces the restore operation to preserve replication settings when restoring a published database to a server other than that on which the backup was created. Use the KEEP_REPLICATION option only when the other publication database is unavailable. The option is not supported if the other publication database is still intact and replicating. For more information about KEEP_REPLICATION, see RESTORE (Transact-SQL).
Log Reader Agent Behavior
The following table describes Log Reader Agent behavior for the various operating modes of database mirroring. For more information about operating modes, see Transact-SQL Settings and Database Mirroring Operating Modes.
Operating mode |
Log Reader Agent behavior if the mirror is unavailable |
|---|---|
High-safety mode with automatic failover |
If the mirror is unavailable, the Log Reader Agent propagates commands to the distribution database. The principal cannot failover to the mirror until the mirror is back online and has all transactions from the principal. |
High-performance mode |
If the mirror is unavailable, the principal database is running exposed (that is, unmirrored). However, the Log Reader Agent only replicates those transactions that are hardened on the mirror. If service is forced and the mirror server assumes the role of the principal, the Log Reader Agent will work against the mirror and start picking up the new transactions. For more information, see Forced Service (with Possible Data Loss). Be aware that replication latency will increase if the mirror falls behind the principal. |
High-safety mode without automatic failover |
All committed transactions are guaranteed to be hardened to disk on the mirror. The Log Reader Agent replicates only those transactions that are hardened on the mirror. If the mirror is unavailable, the principal disallows further activity in the database; therefore the Log Reader Agent has no transactions to replicate. |
See Also