First, sorry I have disappeared for a while. I seem to say sorry a lot. We are pushing hard over here to build an awesome next version of the product and to fix up a number of things you guys have given us feedback on. I’m also still working on my Tech-Ed presentation so, well, life is busy in BizTalk world. However, that is no reason for me to not try and help your life be less busy. Here is a question I often hear posed and I thought I would give a couple of pointers.
BizTalk itself has a number of databases. The base engine has a mananagement database, a tracking database, a messagebox database. It also uses the SSO database and then you have BAM and BAS and HWS to add into the mix. When things go bad, and one of those databases goes down due to an unrecoverable hardware failure, you want to be able to restore the database to a new machine and get up and rolling quickly. There is actually some pretty decent documentation out there at http://www.msdn.microsoft.com/library/default.asp?url=/library/en-us/operations/htm/ebiz_ops_backuprestore_inpu.asp. This should walk you through what we do and why. Basically, because at times we use distributed transactions across our databases, we require the use of transactional log marking of the logs across our databases. Luckily we provide a sql agent job which you can configure to do this marking and backups for you. Note, configuring this should not be considered an option, but a must. Not only will you have no recovery story without backups, but our dbs are marked as recovery model full, so without doing the log backups, the log will grow forever and you will fall over from out of disk space errors. In SP1, we also included a feature to automate the log shipping so that you can have a warm backup. Docs for this are on http://download.microsoft.com/download/0/7/c/07c3598d-0f27-4d7d-a471-fad9b7da4fbd/Readme.htm. I wanted, though, to share a couple of sample vbs files which I have written to automate the updating of biztalk after you have restored the databases to a new set of servers. One is for updating the databases. The other updates the registry. Both are driven by an xml file which will contain the information about which databases you moved and from where to where. If you did not move all of the database, but instead restored some in place and just rolledback to a point in the log, you don’t need to update them. The scripts are okay with you not putting all the databases in the .xml. However, make sure you get all databases into a consistent state. You don’t want to restore some and not others since we will then be out of sync. THESE ARE SAMPLES. You should try them out and make sure they work for you. I have actually only had a chance to try them out on the next release which we are working on, but since we have not changed the docs on how to restore, they’ll work for the 2004 too, but you need to test them and make sure. These are not “supported” but if you think you found an issue, let me know and I will update the file. The follow the steps outlined in the restoration process. Please read the docs and look at the scripts and test things out. In the next release, we will ship ones which look just like this as part of automating the restoration process, but I figured there is no point in not sharing them now since you have to do it now. Thx
So basically you would have a situtation where the hardware died and you needed to restore to a new machine, you would:
1)Make sure all bts related resources are turned off.
2) restore the databases to the new machines hopefully all you have to do is restore the last one if you were using our log shipping solution.
3) Fill in the xml script with the to and from values for all of your databases
4) Run the update registry script on each bts machine
5) Run the update database script on each machine
6) Turn everything back on and you are ready to go
Again, YOU SHOULD ALWAYS TRY THIS IN A TEST ENVIRONMENT. It would suck if you were in production and this issue hit you and you had never walked through what it took to restore and as such you realize that you had forgotten something which cost you. Test things so that you know how to do it if it ever occurs. Hope this stuff helps.
Thx
Lee
Each script takes one parameter which is the location of the .xml file which contains the update information.
UpdateDatabase.vbs
******************************************************************************************************
oldMgmtDBServer = “Not Specified”
oldMgmtDBName = “Not Specified”
newMgmtDBServer = “Not Specified”
newMgmtDBName = “Not Specified”
oldMasterMsgboxDBServer = “SubscriptionDBServerName”
oldMasterMsgboxDBName = “SubscriptionDBName”
newMasterMsgboxDBServer = “SubscriptionDBServerName”
newMasterMsgboxDBName = “SubscriptionDBName”
oldRuleEngineDBServer = “RuleEngineDBServerName”
oldRuleEngineDBName = “RuleEngineDBName”
newRuleEngineDBServer = “RuleEngineDBServerName”
newRuleEngineDBName = “RuleEngineDBName”
oldTrackingDBServer = “TrackingDBServerName”
oldTrackingDBName = “TrackingDBName”
newTrackingDBServer = “TrackingDBServerName”
newTrackingDBName = “TrackingDBName”
TrackingDatabaseMachineName = “TrackingDatabaseMachineName”
TrackingDatabaseName = “TrackingDatabaseName”
oldAnalysisDBServer = “TrackAnalysisServerName”
oldAnalysisDBName = “TrackAnalysisDBName”
newAnalysisDBServer = “TrackAnalysisServerName”
newAnalysisDBName = “TrackAnalysisDBName”
oldBamDBServer = “BamDBServerName”
oldBamDBName = “BamDBName”
newBamDBServer = “BamDBServerName”
newBamDBName = “BamDBName”
oldHWSAdminDBServer = “”
oldHWSAdminDBName = “”
newHWSAdminDBServer = “”
newHWSAdminDBName = “”
””””””’
Dim configObj, loaded, WshShell
Set configObj = CreateObject(“MSXML2.DOMDocument”)
Set WshShell = WScript.CreateObject(“WScript.Shell”)
configObj.async = false
loaded = configObj.load(WScript.Arguments(0))
if not loaded then
wscript.echo “Failed to load the document: ” & configObj.parserError.reason
‘return the error string
end if
dim node : set node = configObj.selectSingleNode(“/UpdateConfiguration/ManagementDB”)
if not (node Is Nothing) then
oldMgmtDBServer = node.getAttribute(“oldDBServer”)
oldMgmtDBName = node.getAttribute(“oldDBName”)
newMgmtDBServer = node.getAttribute(“newDBServer”)
newMgmtDBName = node.getAttribute(“newDBName”)
else
newMgmtDBServer = WshShell.RegRead(“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BizTalk Server\3.0\Administration\MgmtDBServer”)
newMgmtDBName = WshShell.RegRead(“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BizTalk Server\3.0\Administration\MgmtDBName”)
end if
set node = configObj.selectSingleNode(“/UpdateConfiguration/HWSAdminDB”)
if not (node Is Nothing) then
oldHWSAdminDBServer = node.getAttribute(“oldDBServer”)
oldHWSAdminDBName = node.getAttribute(“oldDBName”)
newHWSAdminDBServer = node.getAttribute(“newDBServer”)
newHWSAdminDBName = node.getAttribute(“newDBName”)
else
On Error Resume Next
newHWSAdminDBServer = WshShell.RegRead(“HKLM\SOFTWARE\Microsoft\BizTalk Server\3.0\Hws\WebService\AdminDBServer”)
if (err = 0) then
newHWSAdminDBName = WshShell.RegRead(“HKLM\SOFTWARE\Microsoft\BizTalk Server\3.0\Hws\WebService\AdminDBName”)
end if
newHWSAdminDBServer = WshShell.RegRead(“HKLM\SOFTWARE\Microsoft\BizTalk Server\3.0\Hws\WMI\AdminDBServer”)
if (err = 0) then
newHWSAdminDBName = WshShell.RegRead(“HKLM\SOFTWARE\Microsoft\BizTalk Server\3.0\Hws\WMI\AdminDBName”)
end if
On Error Goto 0
end if
set node = configObj.selectSingleNode(“/UpdateConfiguration/MessageBoxDB[@IsMaster=’1′]”)
if not (node Is Nothing) then
oldMasterMsgboxDBServer = “‘” & node.getAttribute(“oldDBServer”) & “‘”
oldMasterMsgboxDBName = “‘” & node.getAttribute(“oldDBName”) & “‘”
newMasterMsgboxDBServer = “‘” & node.getAttribute(“newDBServer”) & “‘”
newMasterMsgboxDBName = “‘” & node.getAttribute(“newDBName”) & “‘”
end if
set node = nothing
set node = configObj.selectSingleNode(“/UpdateConfiguration/TrackingDB”)
if not (node Is Nothing) then
oldTrackingDBServer = “‘” & node.getAttribute(“oldDBServer”) & “‘”
oldTrackingDBName = “‘” & node.getAttribute(“oldDBName”) & “‘”
newTrackingDBServer = “‘” & node.getAttribute(“newDBServer”) & “‘”
newTrackingDBName = “‘” & node.getAttribute(“newDBName”) & “‘”
TrackingDatabaseMachineName = newTrackingDBServer
TrackingDatabaseName = newTrackingDBName
end if
set node = nothing
set node = configObj.selectSingleNode(“/UpdateConfiguration/AnalysisDB”)
if not (node Is Nothing) then
oldAnalysisDBDBServer = “‘” & node.getAttribute(“oldDBServer”) & “‘”
oldAnalysisDBDBName = “‘” & node.getAttribute(“oldDBName”) & “‘”
newAnalysisDBDBServer = “‘” & node.getAttribute(“newDBServer”) & “‘”
newAnalysisDBDBName = “‘” & node.getAttribute(“newDBName”) & “‘”
end if
set node = nothing
set node = configObj.selectSingleNode(“/UpdateConfiguration/BamDB”)
if not (node Is Nothing) then
oldBamDBServer = “‘” & node.getAttribute(“oldDBServer”) & “‘”
oldBamDBName = “‘” & node.getAttribute(“oldDBName”) & “‘”
newBamDBServer = “‘” & node.getAttribute(“newDBServer”) & “‘”
newBamDBName = “‘” & node.getAttribute(“newDBName”) & “‘”
end if
set node = nothing
set node = configObj.selectSingleNode(“/UpdateConfiguration/RuleEngineDB”)
if not (node Is Nothing) then
oldRuleEngineDBServer = “‘” & node.getAttribute(“oldDBServer”) & “‘”
oldRuleEngineDBName = “‘” & node.getAttribute(“oldDBName”) & “‘”
newRuleEngineDBServer = “‘” & node.getAttribute(“newDBServer”) & “‘”
newRuleEngineDBName = “‘” & node.getAttribute(“newDBName”) & “‘”
end if
””””””’
wscript.echo “Attempting to connect to server “”” & newMgmtDBServer & “””, database “”” & newMgmtDBName & “””…”
dim cnString : cnString = “Driver={SQL Server}; Trusted_Connection=yes; Server=” & newMgmtDBServer & “; Initial Catalog=” & newMgmtDBName
dim MgmtDbConn : set MgmtDbConn = GetConnection(cnString)
dim MgmtDbCmd : set MgmtDbCmd = CreateObject(“ADODB.Command”)
set MgmtDbCmd.ActiveConnection = MgmtDbConn
wscript.echo “Updating the adm_Group table with new information”
MgmtDbCmd.CommandText = “Update adm_Group SET BamDBServerName = ” & newBamDBServer & “, BamDBName = ” & newBamDBName & “, TrackAnalysisServerName = ” & newAnalysisDBServer & “, TrackAnalysisDBName = ” & newAnalysisDBName & “, TrackingDBServerName = ” & newTrackingDBServer & “, TrackingDBName = ” & newTrackingDBName & “, SubscriptionDBServerName = ” & newMasterMsgboxDBServer & “, SubscriptionDBName = ” & newMasterMsgboxDBName & “, RuleEngineDBServerName = ” & newRuleEngineDBServer & “, RuleEngineDBName = ” & newRuleEngineDBName
wscript.echo MgmtDbCmd.CommandText
MgmtDbCmd.Execute
wscript.echo “Updating the list of messageboxes in the management database”
for each node in configObj.selectNodes(“/UpdateConfiguration/MessageBoxDB”)
oldDBName = node.getAttribute(“oldDBName”)
oldDBServer = node.getAttribute(“oldDBServer”)
newDBName = node.getAttribute(“newDBName”)
newDBServer = node.getAttribute(“newDBServer”)
‘ Perform the Update here
MgmtDbCmd.CommandText = “Update adm_MessageBox set DBName = ‘” & newDBName & “‘, DBServerName = ‘” & newDBServer & “‘ WHERE DBName = ‘” & oldDBName & “‘ AND DBServerName = ‘” & oldDBServer & “‘”
wscript.echo MgmtDbCmd.CommandText
MgmtDbCmd.Execute
MgmtDbCmd.CommandText = “Update TDDS_Sources set SourceName = ‘” & newDBServer & “_” & newDBName & “‘, ConnectionString = ‘Pooling=false;Current Language=us_english;Integrated Security=SSPI;Data Source=” & newDBServer & “;Initial Catalog=” & newDBName & “‘ WHERE SourceName = ‘” & oldDBServer & “_” & oldDBName & “‘”
wscript.echo MgmtDbCmd.CommandText
MgmtDbCmd.Execute
next
wscript.echo “updating the list TDDS Destinations with the new tracking database names”
set node = configObj.selectSingleNode(“/UpdateConfiguration/TrackingDB”)
if not (node Is Nothing) then
oldDBName = node.getAttribute(“oldDBName”)
oldDBServer = node.getAttribute(“oldDBServer”)
newDBName = node.getAttribute(“newDBName”)
newDBServer = node.getAttribute(“newDBServer”)
MgmtDbCmd.CommandText = “Update TDDS_Destinations set ConnectionString = ‘Pooling=false;Current Language=us_english;Integrated Security=SSPI;Data Source=” & newDBServer & “;Initial Catalog=” & newDBName & “‘ WHERE ConnectionString = ‘Pooling=false;Current Language=us_english;Integrated Security=SSPI;Data Source=” & oldDBServer & “;Initial Catalog=” & oldDBName & “‘”
wscript.echo MgmtDbCmd.CommandText
MgmtDbCmd.Execute
end if
‘update HWS_Core in HWS Admin database
if ( (newHWSAdminDBServer <> “”) AND (newHWSAdminDBName <> “”) ) then
wscript.echo “Attempting to connect to server “”” & newHWSAdminDBServer & “””, database “”” & newHWSAdminDBName & “””…”
cnString = “Driver={SQL Server}; Trusted_Connection=yes; Server=” & newHWSAdminDBServer & “; Initial Catalog=” & newHWSAdminDBName
dim HWSDbConn : set HWSDbConn = GetConnection(cnString)
dim HWSDbCmd : set HWSDbCmd = CreateObject(“ADODB.Command”)
set HWSDbCmd.ActiveConnection = HWSDbConn
HWSDbCmd.CommandText = “Update HWS_Core set BizTalkServerLocation = ‘” & newMgmtDBServer & “‘, BizTalkManagementDBName = ‘” & newMgmtDBName & “‘, TrackingDatabaseMachineName = ” & TrackingDatabaseMachineName & “, TrackingDatabaseName = ” & TrackingDatabaseName & “, ConstraintsDatabaseMachineName = ‘” & newHWSAdminDBServer & “‘, ConstraintsDatabaseName = ‘” & newHWSAdminDBName & “‘”
wscript.echo HWSDbCmd.CommandText
HwsDbCmd.Execute
end if
‘ ===============================================================
‘ GetConnection(cnString)
‘ This function accepts a connection string and opens
‘ a connection by using it. It returns the connection
‘ back
‘ ===============================================================
Function GetConnection(cnString)
dim conn : set conn = CreateObject(“ADODB.Connection”)
conn.ConnectionTimeout = 30
conn.Provider = “MSDASQL”
conn.Open cnString
Set GetConnection = conn
End Function
***************************************************************************************************
UpdateRegistry
***************************************************************************************************
Dim configObj, loaded, WshShell
Set configObj = CreateObject(“MSXML2.DOMDocument”)
Set WshShell = WScript.CreateObject(“WScript.Shell”)
configObj.async = false
loaded = configObj.load(WScript.Arguments(0))
if not loaded then
wscript.echo “Failed to load the document: ” & configObj.parserError.reason
‘return the error string
end if
On Error Resume Next
dim node : set node = configObj.selectSingleNode(“/UpdateConfiguration/ManagementDB”)
if not (node Is Nothing) then
newDBServer = node.getAttribute(“newDBServer”)
newDBName = node.getAttribute(“newDBName”)
bKey = WshShell.RegRead(“HKLM\SOFTWARE\Microsoft\BizTalk Server\3.0\Administration\MgmtDBServer”)
if (err = 0) then
WScript.Echo bKey
WshShell.RegWrite “HKLM\SOFTWARE\Microsoft\BizTalk Server\3.0\Administration\MgmtDBServer”, newDBServer, “REG_SZ”
WshShell.RegWrite “HKLM\SOFTWARE\Microsoft\BizTalk Server\3.0\Administration\MgmtDBName”, newDBName, “REG_SZ”
end if
else
wscript.echo “Management Database was not updated”
end if
set node = configObj.selectSingleNode(“/UpdateConfiguration/HWSAdminDB”)
if not (node Is Nothing) then
newDBServer = node.getAttribute(“newDBServer”)
newDBName = node.getAttribute(“newDBName”)
bKey = WshShell.RegRead(“HKLM\SOFTWARE\Microsoft\BizTalk Server\3.0\Hws\WebService\AdminDBServer”)
if (err = 0) then
WScript.Echo bKey
WshShell.RegWrite “HKLM\SOFTWARE\Microsoft\BizTalk Server\3.0\Hws\WebService\AdminDBServer”, newDBServer, “REG_SZ”
WshShell.RegWrite “HKLM\SOFTWARE\Microsoft\BizTalk Server\3.0\Hws\WebService\AdminDBName”, newDBName, “REG_SZ”
end if
bKey = WshShell.RegRead(“HKLM\SOFTWARE\Microsoft\BizTalk Server\3.0\Hws\WMI\AdminDBServer”)
if (err = 0) then
WScript.Echo bKey
WshShell.RegWrite “HKLM\SOFTWARE\Microsoft\BizTalk Server\3.0\Hws\WMI\AdminDBServer”, newDBServer, “REG_SZ”
WshShell.RegWrite “HKLM\SOFTWARE\Microsoft\BizTalk Server\3.0\Hws\WMI\AdminDBName”, newDBName, “REG_SZ”
end if
else
wscript.echo “HWS Admin Database was not updated”
end if
*****************************************************************************************************
Sample .xml for driving these scripts. You should be editting this obviously. I just put the names of the dbs in here.
*****************************************************************************************************
<UpdateConfiguration>
<MessageBoxDB oldDBName=”BizTalkMsgboxDb” oldDBServer=”asdf” newDBName=”BizTalkMsgboxDb” newDBServer=”fdsa” IsMaster=”1″/>
<TrackingDB oldDBName=”BizTalkDTADb” oldDBServer=”asdf” newDBName=”BizTalkDTADb” newDBServer=”fdsa”/>
<RuleEngineDB oldDBName=”BizTalkRuleEngineDb” oldDBServer=”asdf” newDBName=”BizTalkRuleEngineDb” newDBServer=”fdsa” IsMaster=”1″/>
<BAMDB oldDBName=”BizTalkDTADb” oldDBServer=”asdf” newDBName=”BizTalkDTADb” newDBServer=”fdsa”/>
<ManagementDB oldDBName=”BizTalkMsgboxDb” oldDBServer=”asdf” newDBName=”BizTalkMsgboxDb” newDBServer=”fdsa” IsMaster=”1″/>
<HWSAdminDB oldDBName=”BizTalkDTADb” oldDBServer=”asdf” newDBName=”BizTalkDTADb” newDBServer=”fdsa”/>
</UpdateConfiguration>