Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
Solved
SQL 2005 database mirroring connection string
Posted on 2006-07-05
I have been doing a bit of research trying to find out how a connection string works for a SQL 2005 instance which is setup with database mirroring.
i am very familiar with microsoft clustering and there being a VIP.
in database mirroring there appears to be no VIP.
i have 3 nodes, a primary a mirror and a witness.
using microsoft sharepoint how would i modify the string and where to connect to a database mirror?
my guess is the web.config file but i cannot find a location.
any suggestions are much appreciated since i have not seen this question answered before it may be very usefull for future references, thanks team!
i am very familiar with microsoft clustering and there being a VIP.
in database mirroring there appears to be no VIP.
i have 3 nodes, a primary a mirror and a witness.
using microsoft sharepoint how would i modify the string and where to connect to a database mirror?
my guess is the web.config file but i cannot find a location.
any suggestions are much appreciated since i have not seen this question answered before it may be very usefull for future references, thanks team!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4
7 Comments
Active 5 days ago
Client-side Redirect
In SQL Server 2005, if you connect to a database that is being mirrored with ADO.NET or the SQL Native Client, your application can take advantage of the drivers' ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string, and optionally the failover partner server.
There are many ways to write the connection string, but here is one example, specifying server A as the principal, server B as the mirror, and AdventureWorks as the database name:
"Data Source=A;Failover Partner=B;Initial Catalog=AdventureWorks;Int
The failover partner in the connection string is used as an alternate server name if the connection to the initial principal server fails. If the connection to the initial principal server succeeds, then the failover partner name will not be used, but the driver will store the failover partner name that it retrieves from the principal server on the client-side cache.
Assume a client is successfully connected to the principal, and a database mirroring failover (automatic, manual, or forced) occurs. The next time the application attempts to use the connection, the ADO.NET or SQL Native Client driver will detect that the connection to the old principal has failed, and will automatically retry connecting to the new principal as specified in the failover partner name. If successful, and there is a new mirror server specified for the database mirroring session by the new principal, the driver will retrieve the new partner failover server name and place it in its client cache. If the client cannot connect to the alternate server, the driver will try each server alternately until the login timeout period is reached.
In SQL Server 2005, if you connect to a database that is being mirrored with ADO.NET or the SQL Native Client, your application can take advantage of the drivers' ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string, and optionally the failover partner server.
There are many ways to write the connection string, but here is one example, specifying server A as the principal, server B as the mirror, and AdventureWorks as the database name:
"Data Source=A;Failover Partner=B;Initial Catalog=AdventureWorks;Int
The failover partner in the connection string is used as an alternate server name if the connection to the initial principal server fails. If the connection to the initial principal server succeeds, then the failover partner name will not be used, but the driver will store the failover partner name that it retrieves from the principal server on the client-side cache.
Assume a client is successfully connected to the principal, and a database mirroring failover (automatic, manual, or forced) occurs. The next time the application attempts to use the connection, the ADO.NET or SQL Native Client driver will detect that the connection to the old principal has failed, and will automatically retry connecting to the new principal as specified in the failover partner name. If successful, and there is a new mirror server specified for the database mirroring session by the new principal, the driver will retrieve the new partner failover server name and place it in its client cache. If the client cannot connect to the alternate server, the driver will try each server alternately until the login timeout period is reached.
Active 5 days ago
also:
Connecting Clients to a Mirrored Database
http://msdn2.microsoft.com/en-us/library/ms175484.aspx
Connecting Clients to a Mirrored Database
http://msdn2.microsoft.com/en-us/library/ms175484.aspx
Active 5 days ago
2 more:
Where to store "Database Connection String" used in a web part
http://msd2d.com/Content/Tip_viewitem_03NoAuth.aspx?section=SharePoint&id=824943E6-6900-4045-8B2C-13D88E3777AC
also
the article
How to: Move from a Single-Server to a Dual-Server Deployment
where you can find example how and where to change connection string in the web.config file:
http://msdn2.microsoft.com/en-us/library/ms404854.aspx
Where to store "Database Connection String" used in a web part
http://msd2d.com/Content/Tip_viewitem_03NoAuth.aspx?section=SharePoint&id=824943E6-6900-4045-8B2C-13D88E3777AC
also
the article
How to: Move from a Single-Server to a Dual-Server Deployment
where you can find example how and where to change connection string in the web.config file:
http://msdn2.microsoft.com/en-us/library/ms404854.aspx
thank you very much for pointing me into the right direction.
right now i am using sharepoint services + sp2, the free version available for download from microsoft.com/sharepoint.
i was not sure which web.config file i should be modifying but i will be giving this a shot and reading through all of these articles now.
when i search the hard drive numerous web.config files show up. in order to assume which may be the correct file what i have done is gone to IIS, right click the default site with sharepoint installed, go to ASP.net properties and look at the path pointing to the web.config file. oddly enough though that file is much smaller in size then some of the other web.config files located in various other folders on the drive.
right now i am using sharepoint services + sp2, the free version available for download from microsoft.com/sharepoint.
i was not sure which web.config file i should be modifying but i will be giving this a shot and reading through all of these articles now.
when i search the hard drive numerous web.config files show up. in order to assume which may be the correct file what i have done is gone to IIS, right click the default site with sharepoint installed, go to ASP.net properties and look at the path pointing to the web.config file. oddly enough though that file is much smaller in size then some of the other web.config files located in various other folders on the drive.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses
Course of the Month8 days, left to enroll
610 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.