Skip to main content

Posts

Showing posts from 2018

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 of l…

SQL Server Mirroring and Replication - Replace the Primary

This is a follow on from my previous post, about how I replaced the mirror in a SQL Mirror set, all the while keeping replication flowing.

In this episode, we'll explore how to move the primary server in the mirror, which is also the publisher of a bunch of publications, onto new hardware.

Unlike the mirror, because the primary is the publisher, its replacement must use the same NetBIOS name as the machine being replaced. Note, it doesn't need the same IP - just the same NetBIOS/hostname. This can be understandably difficult if both machines need go on the same domain. In this instance, don't join the new hardware to the domain just yet and uncheck the boxes about automatically registering itself in DNS, under the network hardware settings.

The first steps are to backup both the service master key and the database master keys. Remember to snag the master databases master key as well as the published/mirrored databases master key.

You must also make sure the disk layout on …

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 -Publi…