How to Use ASP to Redirect Failed ADO Connections to a Backup SQL Server (225082)

MORE INFORMATION

For this "fail over" routine to work correctly, there must be consistency between the default SQL Server database and the "fail over" SQL Server database. Commerce database replication does not work properly on SQL Server 6.5 due to the way that SQL Server 6.5 replicates BLOBs (such as the marshalled_receipt and marshalled_basket). Commerce database replication does work when you use SQL Server 7.0.

Add at least one additional valid ODBC connection to the connection map for the store that points to a duplicate of the default database on another SQL Server computer. This can be done in the Commerce Host Administrator in the MMC by performing the following steps:

1. Right-click on the Store and select Properties.
2. Click the Database Connection Strings tab.
3. Clicking Connection Map, and then click Add.

This following example modifies the Volcano Coffee sample store by changing the include file Shop.asp (included in all of the VC30 Commerce pages by default).

Change the following code from:

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open MSCSSite.DefaultConnectionString
Set cmdTemp = Server.CreateObject("ADODB.Command")
cmdTemp.CommandType = adCmdText
Set cmdTemp.ActiveConnection = conn
				

to:

on error resume next
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open MSCSSite.DefaultConnectionString
Set cmdTemp = Server.CreateObject("ADODB.Command")
cmdTemp.CommandType = adCmdText
Set cmdTemp.ActiveConnection = conn

' If the above ADO connection fails, then we check
' the error number, if it is not equal to 0, then we change
' the MSCSSite.DefaultConnectionString to one of the
' connection strings in the mscssite.connectionstringmap.
' For purposes of this example, the default database is 
' SSCommerce and the backup is SSCommerce2

If Err.Number <> 0 Then
	err.clear 
	mscssite.defaultconnectionstring = mcssite.connectionstringmap("SSCommerce2")
	Set conn = Server.CreateObject("ADODB.Connection")
	conn.Open MSCSSite.DefaultConnectionString
	Set cmdTemp = Server.CreateObject("ADODB.Command")
	cmdTemp.CommandType = adCmdText
	Set cmdTemp.ActiveConnection = conn
End if