PhishThis!

Well, finally a post after the chaos that was the holidays. I was on vacation for 12 days and accomplished…nothing. Except for a lot of Counterstrike. And Team Fortress 2.

If you’re running SCOM 2007 and BizTalk 2006 (and have the SQL agent jobs properly configured) you may notice a warning in SCOM that says there are long running jobs. Upon inspection, you’ll discover that a single job (ManageRefCountLog) has been running since the SQL Agent last started (or the minute immediately following). I ignored this for quite some time, leaving the alert ACK’d the whole time because I couldn’t figure out why the job was running forever…that, and everything was working fine. I finally got fed up with it today and did a little digging. The error looks like this:

There are long running jobs on SQL instance MSSQLSERVER on computer SERVER.FOO.BAR.COM. This may indicate an issue with one or more jobs.

The ManageRefCountLog job has only one step that runs a stored proc on BizTalkMsgBoxDB called bts_ManageMessageRefCountLog. If you look at the store procedure itself, you’ll see:


WHILE (1 = 1)
BEGIN


So, the job is supposed to run forever…or until 1 != 1. Whichever comes first.I never would have noticed this job running forever, if not for SCOM2007 + the SQL 2005 management pack. Now I just need to add an exception. I’ll do that tomorrow…

So, for whatever reason you need to move your BizTalk databases from one SQL server to another. In my case, I was moving from a hostname to a CNAME, for DR purposes (failover server is in another DC, and not clustered).

In the event of a disaster, we could just fail over to the other database server, change the CNAME to point to the “failover” server, and we should be back up and running. The problem, I found, was changing from DC1BTSQL1 to the CNAME (we’ll call it BTSQL1). After searching for a while (maybe I’m an idiot, but all of these things were classified as “backup and restore methods” not “I’m changing server name” methods or anything like that) I found two scripts and an xml file.

Under %systemdrive%\Program Files\Microsoft Biztalk Server 2006\Schema\Restore are two VBS scripts. UpdateDatabase.vbs and UpdateRegistry.vbs. There’s also an XML file called “SampleUpdateInfo.xml.” Below is the procedure for changing the SQL server name. You can even change the names of the DBs. This procudure assumes you’ve backed up and restored, or attached, the DBs to the “new” server already.

More »