Skip to main content

SQL Server Mirroring and Replication - Replace The Mirror

So, as with most technical "support" available a Google search away, the literature about SQL Server Replication and Mirroring is mostly inaccurate and, worse yet, in some instances it will cause you significant data loss and heartache.

This small mini series uncovers the truth about SQL Server and how mirroring and replication interact. I understand that mirroring has been deprecated for AlwaysOn Availability Groups - I'm sure I'll have something to say when I do one of those migrations.

Anyway, I recently had cause to do a hardware refresh on a SQL mirror pair that was also configured as a replication publisher. In this instance, for reasons I don't fully remember, I provisioned a new mirror server, made sure the SQL version and patch level matched, broke the original mirror and re-established it to the "new" mirror hardware.

And this worked fine. All you need to remember to do is update the replication Log Reader Agent and Snapshot Agent's -PublisherFailoverPartner agent parameter to the new mirror's hostname and all is well.

Note however that if you do choose this method to replace the mirror partner, you absolutely MUST NOT do as the Microsoft Guide says in Step 3 of their guide, and that is -
execute sp_adddistributor at the mirror
If you do this, when there is a publication already up and running, you will DESTROY REPLICATION. The time to run sp_adddistributor at the mirror is in a new environment where you are just setting up mirroring and publications. The Log Reader Agent will still work without doing this, in a failover scenario. And practically, failing over to the mirror is a temporary thing - it's unlikely you have your SQL jobs etc set up to run on both. So you will never be on the mirror for an extended period of time and if the log reader is churning away, happy days.

And because replication is so reliant on the NetBIOS names of the publisher, distributor and subscribers, you can't practically perform much replication maintenance while on a mirror server either, as apart from the agents that support the failover partner parameter (because they are using the ADO driver for a connection) , most operations will be looking to the publisher do anything, and it will be offline or the database will be in recovery mode.

One caveat here - if you did setup your mirror and publications correctly in the first place, you could, instead of replacing your mirror with a new machine with a new hostname, simply do a "DR" style replacement of your mirror by restoring the master and msdb databases and copying the mirrored databases files over. This is the method I used to replace the mirror master and publisher of this setup and will be covered in my next article.
Labels:

Comments

Post a Comment

Popular posts from this blog

Log Reader Error On Adding Subscription

Ever tried to add a new subscription to an existing publication, using the "replication support only" or "initialize from backup" methods and go the following error? The process could not execute 'sp_MSadd_replcmds' on <'Distribution Server>'. (Source: MSSQLServer, Error number: 1007) Conversion failed when converting the nvarchar value '.' to data type int. (Source: MSSQLServer, Error number: 1007) Batches were not committed to the Distributor. (Source: MSSQL_REPL, Error number: MSSQL_REPL22020) The last step did not log any message! (Source: MSSQL_REPL, Error number: MSSQL_REPL22037) If so, you will know the internet is absolutely useless, filled with worthless workarounds or advice to just "rebuild replication" Well, if you want an actual solution, just read on. Between SQL 2012 RTM and SQL 2012 SP4 there was a change made to the Distributor proc sp_MSadd_replcmds. This change was to facilitate a change in the format
222 comments
Read more

Writing to the event log in .NET - the right way

As a DevOper, I spend a lot of time instrumenting code. This involves adding stuff like performance counters, tracing and logging. One thing that constantly irks me when I go looking for ways or means to improve how I do my logging, is the complete and utter misinformation and misuse of the Windows Event Log. The problem relates to the canonical implementation of logging in .NET, log4net. Look at this method signature: void Info(object message); Note the lack of structure, the lack of context. Note the ability for a developer to put absolutely anything into the message object (which will be outputted as a string). When these types of messages are hooked up to an Event Log sink, the result is a generic message, usually in the Application log with a bit of free form text. Fairly useless for anyone to be able to filter and report on. The above style of logging is really more suited towards tracing and debug logging than actual operational logging, Why you should write operatio
52 comments
Read more

SCCM 2012 - SMSPXE Error

I write this to help all you technical warriors out there who get shitty advice from the Microsoft Forums and other profound idiots about the 0x80092002 error in SCCM - usually to do with PXE booting but can manifest in other ways around DP -> MP communications. The tricky one about this error is that it happens even if you are only using HTTP communication and not HTTPS/TLS. So to get an error about certificates is somewhat confusing. What the uneducated masses of the internet will tell you is to re-install the PXE role over an over. This will do nothing. Then they will tell you to re-install your management point and effectively re-build your entire infrastructure. This will  work, but only because it is the nuclear option and will run the step that actually makes a difference as a matter of course. So, here it is folks. If you have these kinds of errors in your logs: PXE::MP_InitializeTransport failed; 0x80092002 SMSPXE 22/03/2017 2:45:58 PM 4144 (0x1030) PXE::MP_
42 comments
Read more