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

ID: Q225082

The information in this article applies to:

SUMMARY

You can use ASP code to redirect failed ADO connections to a backup SQL Server computer in the event that the default SQL Server computer used by Commerce Server goes offline. Using this code can provide an additional measure of fault tolerance above and beyond other fault tolerance solutions such as clustering. This code should not be used in place of a true fault tolerant solution.


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

Additional query words: 


Keywords          : 
Version           : winnt:3.0
Platform          : winnt 
Issue type        : kbinfo

Last Reviewed: April 6, 1999