Skip to main content

Posts

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
Recent posts

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, it s 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

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 -

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_

An ETW in .NET update

So ages ago I wrote about using ETW as the proper way to log in .NET apps that run on Windows. This is still true. But now the tooling is even better. A shortfall always was that the auto generated manifest couldn't be installed into the event log and use standard event log collection tools to gather logs. There is a package on Nuget that fills in the gaps -  EventSource beta . It's a beta package but seems to be OK. So go forth, and do structured logging, for the sake of yourselves and your operators.

AOP is for tracing not logging

Every now and then I go and have a bit of search around for the recommended patterns and practices around incorporating logging in your application code. It seems that this particular subject is one of those ones where someone said something a long time ago which has then been propagated to every nook and cranny of the internet until there is no room for argument or any contrarian views to be expressed. I am of course talking about the canonical answers given for .NET logging questions which are - Just use log4net  and Use Aspect Oriented Programming for logging Well, I must offer a contrarian view on both of these points. I'll start with log4net and simply say that I agree wholeheartedly with this Stack Overflow post  which effectively says that from .NET 2.0, TraceSource is built in and is a very effective mechanism to log whatever you need logged. It is also extremely extensible and you would really need some unique edge case to require something else. I have used the T

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