SQL Connection String for Mirrored Database

We setup a new mirrored SQL Server instance. I am able to connect to the principle server, but when the principle server is unavailable it does not automatically failover to the mirror.
This is our current connection string:
strGlobalPath = "DRIVER=SQL Server;SERVER=<IP_ADDRESS_SERVER_A>;UID=<USERNAME>;APP=Microsoft Office 2003;WSID=<SYSTEMNAME>;DATABASE=" &  <DATABASE_NAME> & ";Trusted_Connection=Yes"
Below I added the ‘Failover_Partner:’
strGlobalPath = "DRIVER=SQL Server;SERVER=<IP_ADDRESS_SERVER_A>;UID=<USERNAME>;APP=Microsoft Office 2003;WSID=<SYSTEMNAME>;DATABASE=" &  <DATABASE_NAME> & ";Trusted_Connection=Yes"";Failover_Partner=<IP_ADDRESS_SERVER_B"

I also tried this as well:
strGlobalPath = "<IP_ADDRESS_SERVER_A>; Failover_Partner=<IP_ADDRESS_SERVER_B>; Database=<DATABASENAME>; Network=dbmssocn"
kpurchaseAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
0
kpurchaseAuthor Commented:
I have setup a witness server as well, so should I put the witness server's IP address in the string? TIA.
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
No, the witness is configured in the partner servers, the client has no business there whatsoever.

//Marten
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

kpurchaseAuthor Commented:
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.
0
kpurchaseAuthor Commented:
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.
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
0
kpurchaseAuthor Commented:
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.
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Kpuchases:
Is the question solved to your satisfaction?
If not please post in this thread!

Regards Marten
0
kpurchaseAuthor Commented:
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.
0
kpurchaseAuthor Commented:
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?
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Yes you need to make suri the client is using Snac ver 10

Regards Marten
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kpurchaseAuthor Commented:
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?
0
kpurchaseAuthor Commented:
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?
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
:kpurchase
Please close the question if its answered.

If you have any more questions please ask them.

Regards Marten
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.