Microsoft KB Archive/225082

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

Article ID: 225082

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Site Server 3.0 Commerce Edition

-



This article was previously published under Q225082



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

Keywords: kbinfo KB225082

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.