January 4, 2012 at 11:09 am
I have a sql database mirror in place, I tried stopping the primary SQL Server and the Mirror SQL becomes the primary, I have a witness server, and the switch is made automatically. My question is: how do i have to configure my application in order that if the primary database goes down, the application uses my mirror database?
January 4, 2012 at 11:19 am
Usually, the application needs to have that built into the data access layer (DAL). It either checks a heartbeat for the primary and, failing a response on that, changes the connection to the mirror, or if it encounters a connectivity error on a query, the error handling changes to the mirror.
The complexity of doing that depends on the application. A clever solution I saw once was an application-level variable for the connection string, set in a cookie or session variable in the browser. Something like that, but at the web server, could handle it nicely. (The workstation-level version allowed devs to connect to a dev copy of the database from the production application, simply by switching a value in a cookie. Pretty slick, in my opinion.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 4, 2012 at 11:30 am
So I have the mirror set but i have no clue if it will work? it all depends on the application? I was told that using transaction log shipping, it might work.
Any suggestion on having this enviorement working, the idea is if one database server fails, we can use the other server
January 5, 2012 at 4:51 am
As GSquared has stated, you'll have to tell (or automate via cookies etc) the application to point at the new (Mirror) server via connection strings or similar.
Depending on how the connection string is set, you could possibly have a TSQL/Powershell process that determines whether the mirror is now the principal and update the web.config (as an example) to use the new servername.
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
January 5, 2012 at 6:34 am
angel.fernandezj (1/4/2012)
So I have the mirror set but i have no clue if it will work? it all depends on the application? I was told that using transaction log shipping, it might work.Any suggestion on having this enviorement working, the idea is if one database server fails, we can use the other server
You can certainly test if it's working or not, regardless of the application. How you test it will depend on what kind of testing you want to do, and on what edition of SQL Server you're using. If it's Enterprise, you can create a snapshot of a mirrored database, and query the snapshot. That's one test. Bing/Google/whatever "sql server mirroring" and you'll find a ton of material on how to do it and how to administer various aspects of it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 5, 2012 at 7:11 am
If you're using SQL Native Access Client as a data access library, it includes transparent client redirection, picking up that the principal and mirror have switched. The only thing you need there is the mirror server name adding to the connection string and the app to have reconnect logic so that it will try to reconnect if a connection is unexpectedly dropped
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply