SQL Connection String for Mirrored Database

Try this example:

Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;

From: http://connectionstrings.com/sql-server-2005

To make failover automatic use a witness server. For more reading refer to:  http://technet.microsoft.com/library/Cc917680

Regards Marten

I have setup a witness server as well, so should I put the witness server's IP address in the string? TIA.

No, the witness is configured in the partner servers, the client has no business there whatsoever.

//Marten

I am not a programmer so please bear with me... To use ODBC don't you need to have a 'System DSN' setup on each system that's connecting to the SQL Server? We do not have that setup on each setup so I am not sure why that is being used.

I should mention that we using Access 2003 to connect to a SQL Server 2008 instance.

I did try what you suggested and add the IP address of the failover server but that did not work. I was able to test the failover by clicking the [failover] button on the properties of the database in SQL Server. However if I take the primary SQL instance offline it does not automatically failover.

Now were getting somewhere. Was the db accessible after the manual failover? If so were halfway there.

The next part is to configure the witness from the participsting servers.

I quote myself:
To make failover automatic use a witness server. For more reading refer to:  http://technet.microsoft.com/library/Cc917680

Look at the part:
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (ROLE = WITNESS, ENCRYPTION = ENABLED);

Regards Marten

If I am logged into the database and I select the manual failover option I get the error message "ODBC Called Failed." However when I close out of the database and log back in it works properly and is connecting to the database that I failed over to.

The link that you posted is for SQL Server 2005, I am using SQL Server 2008, don't know if it's the same or not. Regardless I have already setup the witness server and established the endpoints.

Do you use snac ver 10 (i e sql 2008) [On the client where ODBC resides that is]
http://www.mssqltips.com/sqlservertip/2198/determine-which-version-of-sql-server-data-access-driver-is-used-by-an-application/

Install from SQL2008 media, for comparision look at:
http://blogs.msdn.com/b/sqlnativeclient/archive/2008/02/27/microsoft-sql-server-native-client-and-microsoft-sql-server-2008-native-client.aspx

//Marten

Kpuchases:
Is the question solved to your satisfaction?
If not please post in this thread!

Regards Marten

Marten, I am sorry other projects were preventing me from working on this, however this is now # 1. I will answer your questions and respond ASAP. Thanks and sorry for the delay.

Do you use snac ver 10 (i e sql 2008) [On the client where ODBC resides that is?

Marten, I ran the query on the SQL Server and determined that the driver version is SQL Server 2008. Do I need to determine which version the client is trying to use to connect to the SQL Server?

How do I do that?

From the code I can see the following lines:

"ODBC;Driver={SQL Server};Server="
Global Const gStrDriverPath = "c:\WINDOWS\System32\sqlsrv32.dll"

Does this help?

I am now able to connect to the SQL backend using the connection string:
"ODBC;Driver={SQL Server Native Client 10.0};Server=<SQLSERVER_IPADDRESS>;Failover_Partner=<FAILOVER_IPADDRESS>;Database=>DEFAULT_DATABASE>; Trusted_Connection=yes;"

It appears that failover is working but only when I log out and then back into the frontend. What happens is when I am connected to the SQL server and I choose failover I get the following message:
"Run-time error '3146':
ODBC--call failed.
[Microsoft][SQL Server Native Client 10.0] TCP Provider: An existing connection was forcibly closed by the remote host.
(#10054) [Microsoft][SQL Server Native Client 10.0] Communication link failure (#10054)

If I close the application and go back in it connects to the failover SQL Server. How can I get this to automatically failover without disconnecting me first?

Well this is as far as you'll go. If you dont trap connection errors and reconnect silently within your application
For reference see: http://msdn.microsoft.com/en-us/library/ms366199.aspx
Please remember that a failover is when something breaks, or when you patch servers. But then you get to choose the time and date so that implications for users are as small as possible.

Regards Marten

:kpurchase
Please close the question if its answered.

If you have any more questions please ask them.

Regards Marten