Tuesday, February 14, 2012

DSNLess connection with Failover to a SQL Server 2005 cluster

Hi,

We have configured two SQL Server 2005 in a cluster (without Witness).

We have severals ASP (using ADO) that are connecting to SQL Server 2005 with SQL Native Client in a DSNLess connection String :

driver={SQL Native Client}; server=MAIN\MAIN; Failover Partner=BCKUP\BCKUP; uid=log;pwd=pwd;database=MyBase

When using this connection string, the connection to MAIN is alrigth, but when we simulate a unavailability situation on "MyBase" or on the entire MAIN instance, the new connection are joining the MAIN and not the BCKUP SQL Server.

We tryed several Connection Strings including Failover_Partner, Initliag Catalog instead of database... but no one succed in joining the BCKUP SQL Server.

I precise that the DNS on the PC that connect to SQL server is configured and both MAIN and BCKUP are resolved in command line or in explorer.

Is there an other driver that must be used, or is there an other parameter to set in the connection string ?

Regards.

When you configure Mirroring without a witness, failover is not automatic. You must manually failover the database - you do this via alter database or using the button provided in the database mirroring properties.

Matt Hollingsworth

Sr. Program Manager

Microsoft SQL Server

No comments:

Post a Comment