In its simplest single machine using default configuration BizTalk Server 2010 comes with a sample of how to use sysprep, that resides in the C:\Program Files (x86)\Microsoft BizTalk Server 2010\SDK\Samples\Admin\Sysprep folder. It uses an unattended installation answer file that among other things tells windows to run a number of scripts that updates the database, SSO, BAM etc, where references to the machine name exists. Full description of that “sample” is described here.

Windows Azure on the other hand does not (as far as I can figure out) support running sysprep and supplying an unattend file. The way to properly sysprep a Windows Server VHD running on Windows Azure Virtual Machines is described here. If developing a machine locally the same general steps apply.

The problem with this is that they clash, they mix like cream and lemon – not at all. (I am sure there are ways to combine these, but its not openly apparent how, but if someone knows feel free to Comment on that).

To make a BizTalk Server image created on Windows Azure fully usable we can run the scripts and commands that the unattended configuration would have. When running these scripts you need to know the name the computer had when sysprep was used and the image captured.

There are three steps to this:

  1. Update SQL Server User Groups
  2. Update SQL Server metadata
  3. Update BizTalk Server (including SSO and other components)

Update SQL Server User Groups

Simply rename the groups.

Updating SQL Server Metadata

The problem when logging on to SQL Server after the name change is that you no longer have access with the administrative account, Administrator, since this is really oldmachine\administrator. So you need another administrative account, ie SYSTEM to fix your login for you.

One way to run as system and do the things described for step 1 is to add a Scheduled Task. Set it to run as SYSTEM, since that account will be sysadmin in the database and set it to run a script that fixes the things necessary, ie:

REM UpdateSqlLogins.bat

SET oldcomputername=JEHBTS5
pushd %programfiles(x86)%\Microsoft BizTalk Server 2010\SDK\Samples\Admin\Sysprep\scripts

SqlCmd -s . -d master -A -Q "sp_dropserver %oldcomputername%" -o UpdateSQLLogins.log
SqlCmd -s . -d master -A -Q "sp_addserver %computername%, local" -o UpdateSQLLogins.log
SqlCmd -s . -d master -A -Q "drop login [%oldcomputername%\Administrator]" -o UpdateSQLLogins.log
SqlCmd -s . -d master -A -Q "create login [%computername%\Administrator] from windows" -o UpdateSQLLogins.log
REM SqlCmd -s . -d master -A -Q "EXEC sp_changedbowner [%computername%\Administrator]" -o UpdateSQLLogins.log
SqlCmd -s . -A -Q "EXEC sp_addsrvrolemember @loginame = N'%computername%\Administrator', @rolename = N'sysadmin'" -o UpdateSQLLogins.log


Update BizTalk Server

As the start of the article mentions, Microsoft has briefly covered applying sysprep to a BizTalk Server machine, but since that procedure does not map to this we need to take a somewhat different approach.

  1. Update the file UpdateInfo.xml. In particular remove the <DeploymentUnit Name="Alert"> section, since I am not using BAM Alerts.
  2. Create a file called Replace.vbs and insert the following code.

    .csharpcode, .csharpcode pre
    font-size: small;
    color: black;
    font-family: consolas, "Courier New", courier, monospace;
    background-color: #ffffff;
    /*white-space: pre;*/
    .csharpcode pre { margin: 0em; }
    .csharpcode .rem { color: #008000; }
    .csharpcode .kwrd { color: #0000ff; }
    .csharpcode .str { color: #006080; }
    .csharpcode .op { color: #0000c0; }
    .csharpcode .preproc { color: #cc6633; }
    .csharpcode .asp { background-color: #ffff00; }
    .csharpcode .html { color: #800000; }
    .csharpcode .attr { color: #ff0000; }
    .csharpcode .alt
    background-color: #f4f4f4;
    width: 100%;
    margin: 0em;
    .csharpcode .lnum { color: #606060; }

    'Usage: Replace.vbs <text file to open> <string to be replaced> <string to replace it with>
    Dim sOutput, reader, readerStream, writer, writerStream, Wshell
    set WshShell = WScript.CreateObject("WScript.Shell")
    Set reader = CreateObject("Scripting.FileSystemObject")
    set readerStream = reader.OpenTextFile(WScript.Arguments(0), 1, , -2)
    sOutput = Replace(readerStream.ReadAll, WScript.Arguments(1), WScript.Arguments(2))
    sOutput = Replace(sOutput, UCase(WScript.Arguments(1)), WScript.Arguments(2))
    sOutput = Replace(sOutput, LCase(WScript.Arguments(1)), WScript.Arguments(2))
    Set writer = CreateObject("Scripting.FileSystemObject")
    Set writerStream = writer.CreateTextFile(WScript.Arguments(0), true, False) ''Write the file in ASCII
  3. Create a file called BizTalkSysPrepRestore.bat and place the following code in it.

    REM BizTalkSysPrepRestore.bat
    REM SET /P oldcomputername=<test.txt
    SET oldcomputername=JEHBTS5
    pushd %programfiles(x86)%\Microsoft BizTalk Server 2010\SDK\Samples\Admin\Sysprep\scripts
    REM First run UpdateSQLLogins.bat. Once. To provision the account as Admin to be allowed to do the below.
    net stop BTSSvc$BizTalkServerApplication
    net stop RuleEngineUpdateService
    net stop ENTSSO
    cscript.exe "Replace.vbs" "UpdateInfo.xml" $(NEWCOMPUTERNAME) %computername%
    cscript.exe "Replace.vbs" "UpdateInfo.xml" $(OLDCOMPUTERNAME) %oldcomputername%
    cscript.exe "Replace.vbs" "UpdateInfo.xml" $(OLDCOMPUTERENAME) %oldcomputername%
    cscript.exe "UpdateRegistry.vbs" "UpdateInfo.xml" > "UpdateRegistry.log"
    cscript.exe "UpdateDatabase.vbs" "UpdateInfo.xml" > "UpdateSqlServerDatabase.log"
    cscript.exe "UpdateBAMDb.vbs" "UpdateInfo.xml" > "UpdateBAMDb.log"
    "UpdateSSO.cmd" > "SSO.log"
    REM Update path to SSOXXXX.bak or place in local folder with this name
    "%CommonProgramFiles%\Enterprise Single Sign-On\ssoconfig.exe -restoreSecret SSO.bak
    net stop SQLAgent$SQLEXPRESS
    net stop sqlserveragent
    net stop mssqlserver
    cscript.exe "Replace.vbs" "%programfiles(x86)%\Microsoft BizTalk Server 2010\Tracking\bm.exe.config" %oldcomputername% %computername%
    net start mssqlserver
    net start MSSQL$SQLEXPRESS
    net start sqlserveragent
    net start SQLAgent$SQLEXPRESS
    net start RuleEngineUpdateService
    net start BTSSvc$BizTalkServerApplication
  4. Run the BizTalkSysPrepRestore.bat as Administrator.
  5. Open SQL Server Management Studio and runt the following SQL script to update Agent jobs.

    USE [msdb]
    EXEC msdb.dbo.sp_update_job @job_name=N'Backup BizTalk Server (BizTalkMgmtDb)', @owner_login_name=N'sa'
    EXEC msdb.dbo.sp_update_job @job_name=N'CleanupBTFExpiredEntriesJob_BizTalkMgmtDb', @owner_login_name=N'sa'
    EXEC msdb.dbo.sp_update_job @job_name=N'DTA Purge and Archive (BizTalkDTADb)', @owner_login_name=N'sa'
    EXEC msdb.dbo.sp_update_job @job_name=N'MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb', @owner_login_name=N'sa'
    EXEC msdb.dbo.sp_update_job @job_name=N'MessageBox_Message_Cleanup_BizTalkMsgBoxDb', @owner_login_name=N'sa'
    EXEC msdb.dbo.sp_update_job @job_name=N'MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb', @owner_login_name=N'sa'
    EXEC msdb.dbo.sp_update_job @job_name=N'MessageBox_Parts_Cleanup_BizTalkMsgBoxDb', @owner_login_name=N'sa'
    EXEC msdb.dbo.sp_update_job @job_name=N'MessageBox_UpdateStats_BizTalkMsgBoxDb', @owner_login_name=N'sa'
    EXEC msdb.dbo.sp_update_job @job_name=N'Monitor BizTalk Server (BizTalkMgmtDb)', @owner_login_name=N'sa'
    EXEC msdb.dbo.sp_update_job @job_name=N'Operations_OperateOnInstances_OnMaster_BizTalkMsgBoxDb', @owner_login_name=N'sa'
    EXEC msdb.dbo.sp_update_job @job_name=N'PurgeSubscriptionsJob_BizTalkMsgBoxDb', @owner_login_name=N'sa'
    EXEC msdb.dbo.sp_update_job @job_name=N'Rules_Database_Cleanup_BizTalkRuleEngineDb', @owner_login_name=N'sa'
    EXEC msdb.dbo.sp_update_job @job_name=N'TrackedMessages_Copy_BizTalkMsgBoxDb', @owner_login_name=N'sa'
  6. You should now be set to run BizTalk.

In Summary

I am sure there are better ways to do some of these things. This was a PoC of exactly this, and nothing else. I know there are ways to simplify and automate further. I am sure this is not the best possible solution, but it is one possible solution. It is also a work in progress. I do not have time to take it further right now, but I still wanted to release this post to perhaps help someone else along.

I have hardcoded the old computer name in these scripts, you need to replace that with whatever your original machines name was when you created the image.

There are a couple of things here where I have taken the easy road. All services are run by Administrator, which of course is an administrator. The same is a a member of the groups SSO Administrator, SSO Affiliate Administrator, BizTalk Server Application Users and BizTalk Server Administrator, as well as being assigned sysadmin in the scripts above. So not the ideal best practice security there, and it was outside my scope to make it that.

For the moment, you can also create an image from a virtual machine in Windows Azure without using sysprep. This will work as well, but it’s a quirk, not really what we want here for several reasons.

Since the sysprep support for BizTalk Server is a SAMPLE, I am not sure how “supported” using sysprep on BizTalk Server really is at the moment. The team will have to solve this on their way to offering BizTalk Server as stock images on Windows Azure Virtual Machines, but we are not there yet.

SQL Server does not officially support sysprep in this manner. Instead another procedure is detailed, which includes not fully installing SQL at all before you sysprep. This does not seem to have changed with SQL Server 2012. It will be interesting to see how the team works around this limitation for BizTalk Server 2010 R2. I am guessing that is what the “provisioning” stage that virtual machines go through is for – finalizing installations.

Perhaps not really installing SQL, and following that products offical way to do it, as well as just installing but not configuring BizTalk Server, is the easiest way to do it at the moment. You be the judge.

Blog Post by: Johan Hedberg