Setup steps included in the wizard will be as follows:

1. Distributor: Acts as its own Distributor
2. Snapshot folder: default for this lab but share on network for true production environment
3. Distribution Database: leave defaults to your disk configurations
4. Publishers: leave distributor as publisher and add Server A as a publisher. XPS2008R2 in this test
5. Distributor password: Enter the service credentials. Needs access to Server A and B and share
6. Wizard Actions: Check both configure and generate scripts
7. Execute setup

Each step shown below ordered left to right

Move to the publisher now.

In the wizard:

1. Distributor: change the distributor to the remote distributor we setup prior
2. Administrative Password: enter the administrator password to gain access to the remote distributor
3. Publication Database: select AdventureWorks as the database
4. Publication Type: select Transactional publication for the publication type
5. Articles: select the articles (tables: Address, AddressType, Contact, ContactType and CountryRegion)
6. Filtered table rows: no filters
7. Snapshot Agent: check “Create a snapshot immediately and keep the snapshot available to initialize subscriptions”
8. Agent Security: select the security settings as the “Run under the SQL Server Agent service account”
9. Wizard Options: finish by selecting the Generate scripts option.

These steps are shown in order below ordered left to right

Recap: At this point we have a distributor role on R2Distributor, Server B; and a publication role on XPS2008R2, Server A.

To finish the replication setup, create a subscription to the publication on ONPNT_XPS, Server C. To do this, connect to the SQL Server in SSMS, right click the subscriptions and select New Subscriptions…

Select XPS2008R2 (your own server name replaced here) as Publisher

Through the wizard, make the following selections.

1. Distribution Agent Location: Run all agents at the Distributor
2. Subscribers: Subscriber (ONPNT_XPS), Subscription Database (AdventureWorks)
3. Distribution Agent Security: Impersonate process account
4. Synchronization Schedule: Run Continuously
5. Initialize Subscriptions: Immediately
6. Wizard Actions: Create subscriptions and Generate scripts

Each step shown below ordered left to right

We’ve successfully setup transactional replication at this point and can move to setting up the mirroring session. The mirror will exist between Server A (Replication Publisher) and Server B (Replication Distributor). To set mirroring up, refer to the following blog on setting mirroring up with Developer Edition.

Once the mirror setup is completed, replication must be configured further to ensure a failover in the mirror is also followed with a failover in the replication. To accomplish this, the agent profiles for all replication agents must have the –PublisherFailoverPartner set. This ensures that in the event of a failover, the agents know what the mirror is.

To set the agents, use the sp_add_agent_parameter procedure

exec sp_add_agent_parameter @profile_id = 1, @parameter_name = N'-PublisherFailoverPartner', @parameter_value = N'ONPNT_XPSR2Distributor'
exec sp_add_agent_parameter @profile_id = 2, @parameter_name = N'-PublisherFailoverPartner', @parameter_value = N'ONPNT_XPSR2Distributor'
exec sp_add_agent_parameter @profile_id = 3, @parameter_name = N'-PublisherFailoverPartner', @parameter_value = N'ONPNT_XPSR2Distributor'
exec sp_add_agent_parameter @profile_id = 9, @parameter_name = N'-PublisherFailoverPartner', @parameter_value = N'ONPNT_XPSR2Distributor'

The profile ID can be obtained by querying the MSAgent_Profiles table in the MSDB database. The agent types that will be contained in this table are:

1 = Snapshot Agent

2 = Log Reader Agent

3 = Distribution Agent

4 = Merge Agent

9 = Queue Reader Agent

Since profile IDs may be different on different installations, verify your profile ID by check this table. Once the parameters are set, use the MSagent_parameters table to verify by checking the parameter_name column for the value -PublisherFailoverPartner.

SELECT * FROM MSagent_parameters WHERE parameter_name = '-PublisherFailoverPartner'

If we were in a merge replication setup, profile 4 would also need to be set.

The procedure sets the mirror name as the failover parameter. Run this on the distributor (which is also acting as the mirror in our case).

Verification and failover test

Testing is everything when you have a more complex HA setup, such as mirroring with replication. Taking testing in stages will allow for problem areas to be quickly resolved, rather than testing the end result and backtracking through your configurations.

At this point mirroring and replication is functioning together. The principal and mirror are acting in a synchronized state and replication is acting based off the distributor. To test this setup, test mirroring by altering a record in AdventureWorks on the principal.

SELECT AddressLine1 FROM person.Address WHERE AddressID = 1

The statement above results in, “1970 Napa Ct”. Update the AddressLine1 value to, “1970 Napa Court” and ensure the changes flow to the subscriber of the publication.

UPDATE Person.Address SET AddressLine1 = '1970 Napa Court'
WHERE AddressID = 1

Check the subscriber by running the same select. You should see the changes made to the same record. In the replication monitor we can also see the commands sent

Our next step it to test a complete failover of the mirror and then make this same test to ensure that replication also fails over as it is detected.

Right click the AdventureWorks database on the Principal and select properties. Go to the mirroring page and click the Failover button

Click Yes to the warning on failover. The wizard screen will close when the failover is completed.

At this point replication is disconnected from the publisher on running commands. We can see this by looking at the replication monitor and seeing retry attempts

The default amount of time that is monitored for replication to failover is around 35 seconds. Once replication agents failover to the mirror set in the failover partner parameter, the agent status in the monitor will resume the running state.

To test the failover, run the same test as we performed earlier but in reverse order. Update the mirror that is now acting as the principal and follow the replicated commands through to the subscriber to ensure the changes were committed there.

In this case, update the same value back to the Ct over Court value.

UPDATE Person.Address SET AddressLine1 = '1970 Napa Ct'
WHERE AddressID = 1

Check on the subscriber now that the changes flowed as they should have

SELECT AddressLine1 FROM person.Address WHERE AddressID = 1

We see that the failover was successful and the value committed as, “1970 Napa Ct” and the distributor in the replication monitor shows the commands sent.

Once verified, fail the mirroring session back to the original state and test again.

Primary concerns

The primary concern with putting replication to work on a database that is mirrored will fall into latency. Replication latency will increase when certain mirroring operating modes are set or in the case a mirror is in a lengthy synchronizing state. This is more the case when High Performance mirroring is set.

In some cases when replication is heavily used, multiple distributors are also utilized to balance loads across physical SQL Servers. With mirroring and replication, the distributor must be the same for both the principal and the mirror. This is due to the state of the mirror and requiring it to mimic the principal at all times.

Finally, unsupported replication types include: Immediate update Subscribers, Oracle publishers, peer-to-peer publishers and republishing.

This and other important notes that should be considered when working with mirroring and replication on the same database can be found in this BOL document.

This entire solution can be scripted out both by the use of the Generate Scripts option and by altering the scripts to utilize parameters you pass in. It is recommended that you at least save the generated scripts from the configurations so the setup can be restored quickly. This includes mirroring scripts for the endpoints.

You can see a close example of scripting the replication setup on mirrored databases here on SQLServerCentral.com.

Thanks to Kendal Van Dyke (Twitter | Blog) (aka, the replication king) for the technical review of the article.