Pythian Blog: Technical Track

How to Configure Transactional Replication-Mirroring Failover

by Ashish Sharma on Feb 3, 2014 12:00:00 AM

Configure SQL Server Transactional Replication with Mirroring – this was one of the recent interesting and unusual projects that came our way. At first it looked really odd and intriguing at the same time. The question was, can it really be done? Well, as it happens, Microsoft SQL Server has a pretty straight forward method to set it up – at least the Publishers-failover part. The Subscribers-failover is not a straight forward method, but is definitely possible through workarounds. In this blog post, we'll discuss how to configure a simple Transactional Replication-Mirroring failover configuration, particularly configuring failover of publications. This may be of some help if you do not want to reset publications and subscribers in case the published (and mirrored) database fails over to its mirror. While setting up the actual configuration, we'll also discuss a couple of tweaks involved with the process. Here’s the Scenario to begin with:
  1. Current setup contains 2 servers.
    • The OLTP Server (S_OLTP1) contains databases which support main portal application, and are highly volatile.
    • The application middle tier server (S_Apps) hosts SSIS and SSRS instances, besides basic configuration and metadata databases, like, SSISDB (for SSIS) and ReportServer (for SSRS).
    • Both the servers have SQL Server 2008 installed.
  2. Following requirements must be met for the project:
    • Provisioning a DR solution for main portal databases, by adding a new mirror server to the setup. So, Database Mirroring is the selected DR strategy.
    • Offloading the reporting requirements from S_OLTP1 server, by adding a new reporting server. This new server should be configured to subscribe data from S_OLTP1 and make it available for reporting purposes, by means of transactional replication.
    • A “complementary” requirement that comes up is that the transactional replication should not break in case any of the mirrored databases failover.
Note: Although, all the servers used in our project are SQL Server 2008’s, this blog post is valid for all implementations of SQL Server versions 2005 and higher. Based on above requirements, we now have the following servers in our configuration:
  • S_OLTP1: The primary OLTP server, hosting all application databases. Transactional replication publisher. Mirror member server.
  • S_OLTP2: The new OLTP DR server. Mirror member server, with S_OLTP1 as Principal. Transactional replication publisher (obviously, as we want the replication to be working after failover).
  • S_Apps: The original middle tier server. (This will also be used as replication Distributor. Optionally, this server could be used as a Witness server for the mirrored pair of S_OLTP1 and S_OLTP2.)
  • S_Rept1: The new reporting server. (This server will host all databases required for reporting. These databases will be subscribers of publication(s) from S_OLTP1/2.)
Note: It is a must to have a remote Distributor if you want replication to failover. It is a MUST to configure a common (ideally, remote) distributor for publishers to failover. Diagrammatic representation: Replication_Mirroring The Setup will be done as discussed below. First step we will take in this document is mirroring on all the required databases. Since there is no special configuration required for “Replication-failover” to be done at mirroring end, we will exclude details of mirroring setup in this blog post. Note: Database Mirroring can also be done after setting up publication. So, this step can be performed after publishing steps discussed later in this blog post. Next, we will configure replication. As per the required setup, we will have to perform following actions:
  1. Setup Distributor on central remote distributor server (S_Apps1).
  2. Add S_OLTP1 and S_OLTP2 as publishers on Distributor.
  3. Add new Publications on the Publisher server.
  4. Configure the agent profiles or jobs to failover.
  5. Add subscription to S_Rept1.
  6. Repeat steps 3 & 4 for all the databases that require publishing.
Now that we know the process, let’s discuss these steps in detail starting with setting up the distributor.
  1. To setup a common remote distributor, we will connect to S_Apps1 server, using SSMS. Once connected, expand the server and right click on Replication and select “Configure Distribution…” from the context action menu. See figure 2 below. Figure 2: ConfigDistributor_Step1
  2. In the first page of Configure Distribution Wizard, press Next, as shown below in Figure 3.Figure 3: ConfigDistributor_Step2.1
  3. On the next page, keep the default option (Server will act as its own distributor) on, and press Next button. Refer Figure 4 below.Figure 4: ConfigDistributor_Step3
  4. As shown in Figure 5 below, select path for generating snapshots, and click Next to proceed.Figure 5: ConfigDistributor_Step4
  5. On the next screen, specify the details for distribution database, as shown in Figure 6.Figure 6: ConfigDistributor_Step5
  6. Next, enable this server to act as Distributor for the mirrored pair - S_OLTP1 and S_OLTP2. By default, you will find the server itself is listed as Publisher (refer Figure 7). a) Click on Add, to open “Connect to Server” dialog box, as shown in Figure 8. b) Specify the name for Primary server (S_OLTP1). c) Click Connect. This adds a new server to list of Publishers.Figure 7:
    1. ConfigDistributor_Step6Figure 8: ConfigDistributor_Step7
    d) Click Add again from main screen, and connect to Secondary server (S_OLTP2). e) Finally, you will see both servers are listed as Publishers, along with the distributor. Your screen should look like the snapshot shown in Figure 9 below.
    1. Figure 9: ConfigDistributor_Step8Note: I always use SQL Server Agent account, which is a domain account, for such connections -- this saves a lot on connectivity and permissions issues.
  7. On the next screen, we will need to enter a custom password, which will be used by the Publishers to connect to the Distributor. This password is not related to SQL Server or Agent service. Make a note of the password you enter and keep it safe, because this is the one which you will need to provide when configuring the publishers to use remote distributor. A snapshot of this screen is shown in Figure 10 below.Figure 10: ConfigDistributor_Step9.0
  8. As in Figure 11, we are now done with configuration. Click Next.Figure 11: ConfigDistributor_Step9.1
  9. On the next screen, review the summary and press Finish button.
  10. The “Configuring…” screen will appear, and at the end of a successful completion, the final screen will look something like Figure 12 below.Figure 12: ConfigDistributor_Step9.3
Now that Distributor is successfully set up, let’s proceed to creating publications step -- I won't get into the details of creating a publication, to keep myself from deviating from the topic. Instead, let’s discuss which actions are differently performed when creating a publication for failover scenario. Here is how it goes:
  1. In the “New Publication Wizard”, when selecting distributor, choose to use remote distributor. Click Add and connect to the distributor configured in the steps above. The “Distributor” screen of “New Publication Wizard” will look something like Figure 13 below.Figure 13: NewPublicationRemoteDistributor_Step1
  2. On the next screen, we will specify the password that we provided while configuring the distributor for “Distributor Password” – refer Step 7, in configuring Distributor steps above. The wizard screen to specify the password will look like Figure 14 below.Figure 14: NewPublicationRemoteDistributor_Step2
  3. We will continue with rest of the wizard normally, as we would while creating a usual transactional publication.
Once we have the publication in place, we are done with setting up Distributor, Publishers and the Publication. Next step up will be to configure the agents to failover. Let’s discuss what needs to be done to make the replication agents to recognize the failover partner. There are a few ways we can do it: a) Modify agent profile to change the “PublisherFailoverPartner” property, using Distributor properties dialog box. b) Modify agent profile to change the “PublisherFailoverPartner” property, using TSQL. c) Modify each agent’s job individually, to add the parameter “-PublisherFailoverPartner”. The difference between changing agent profiles and agent job properties is that by changing profile, we can alter the behaviour of all publisher side agents. By changing the job properties, we only affect those agents whose job is modified. We will look at options A and C in this blog post. To modify agent profile using Distributor properties, we will go through the following steps:
  1. Connect to the distributor. Right click “Replication” and select “Distributor Properties” from the context action menu. See Figure 15 below.Figure 15: AgentProfile_Step1
  2. This will open up the Distributor Properties dialog box for your distributor, as show below in Figure 16.Figure 16: AgentProfile_Step2
  3. Click on “Profile Defaults…” button to open up “Agent Profiles” dialog box. Choose profiles for log reader agents as show in Figure 17.Figure 17: AgentProfile_Step4
  4. Click on the ellipsis button “…” in the “Type” column for Default agent profile, to open up “Default agent profile Properties” dialog box. See Figure 18 below.Figure 18: AgentProfile_Step5
  5. By default, the dialog box shows only the properties that are configured. To see the complete list, including the property we are interested in, uncheck the Check box “Show only parameters used in this profile”. Figure 19 shows what the screen looks like. Profile property “PublisherFailoverPartner” is highlighted in the snapshot below.Figure 19: AgentProfile_Step6
  6. We will not be able to change values here, as this is a System type profile. We will need to create a new agent profile and enable it for Log Reader agents to use. To do so, close the current properties and click the “New” button to open “New Agent Profile” dialog, shown below in Figure 20.Figure 20: AgentProfile_Step7
  7. Click “OK”, and open the “New Agent Profile” dialog box. Specify the Secondary Mirror server (S_OLTP2 in our case) as value of parameter “PublisherFailoverPartner”, as shown in Figure 21 below, and click “OK”.Figure 21: AgentProfile_Step8
  8. Now, the new profile is shown in the Agent profiles list for Log Reader Agents. Click the check box next to “NewProfile” profile. This makes a default for any new log reader agents that will be created. Figure 22 shows how the screen will look.Note: If you want to change all existing agents to use the newly created settings, click the “Change Existing Agents” button given below in the box.Figure 22: AgentProfile_Step9
Click OK, and voila! The log reader agents for this project are all set for automatic failover. Alternatively, we can also customize automatic failover for specific agents by just modifying Agent job parameters. To achieve this, simply open the properties and modify the job step “Run agent”. Figure 23 shows how it should be put in. We will simply add the parameter “-PublisherFailoverPartner” to the command. A typical command parameter-set will look like this: “ -Publisher [S_OLTP1] -PublisherDB [DB1] -Distributor [S_Apps1] -DistributorSecurityMode 1 -Continuous -PublisherFailoverPartner [S_OLTP2]” The underlined portion is what we need to add to the job. This is how it will look once complete: Figure 23: AgentJobProperties That concludes the failover configuration for publishers. Now we can proceed with adding subscribers normally. Since, there is no configuration required at subscriber end, to configure publisher-failover scenario, we can proceed with adding subscribers normally. And, there we are, with the project successfully implemented! Food for thought: Although, we have wonderfully configured publisher failover, this is not a fully established topic and needs some more exploration and discussion. Clearly, not all types of replication setups support publisher-failover. It would be interesting to note which ones. Moreover, we are talking about two major techniques combined! It increases possibilities and options, but it also introduces some challenges to be dealt with. Replication is highly robust, but what if mirroring breaks, or simply, the principal server is unavailable for some time? We sometimes rely on Replication Monitor, and would also love to think that it provides a complete picture of Replication state – even when mirrored!
Topics: Oracle DBA Lounge Technical Track Articles Oracle Exadata Exadata Listener Sdp Infiniband

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.