[AccessD] OT - Using Connection Strings For Disaster Recovery

I don't believe it is possible.
You have to try to connect and catch the error, if there is one.  
Pinging the server is no good because the server may be up but the
db may be down.
The type of disaster recovery you are looking at is usually accomplished
with RAID, clustered servers, that type of thing and definitely not my
thing ;-)
Failover is what they call it IIRC.  If 1 server dies a mirror image
takes over.

cheers

Michael M


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Paul Hartland
(ISHARP)
Sent: Friday, 9 December 2005 12:08 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] OT - Using Connection Strings For Disaster Recovery

To all,

I am work on implementing a new section to the disaster recovery of my
own projects, one of the things I am currently looking into is having a
.ini file with connection string to my main server something like:

	Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;User ID=myusername;Initial Catalog=mydatabase;Data
Source=mysourcemain1

Each day I make my own backup of the main database, but if our main
server went down what I want to be able to detect if the connection
string is valid, if not use the next one in the sequence:

	Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;User ID=myusername;Initial Catalog=mydatabase;Data
Source=mysourcemain2

So that although my dataenvironment, ADO controls etc point to
mysourcemain1, if mysourcemain1 shuts down...I can go to the latest
backup that I have and restore on mysourcemain2 then let everyone back
in again....i.e. I want something like the following:

	Open the .ini file as text
	Check if the first connection string is ok
	if ok then set the datasources to it
	if not ok warn user and alert administraors of possibility of
server down
	get the next connection string and recheck 
	loop until found valid string

Is there a way of check if a connection string to a SQL Server would
work before trying to open the connection ?

If anyone could help me on this I would be very grateful, sample code
and/or guidance greatly appreciated.

Thanks in advance.

Paul Hartland

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com