Database Mirroring - Web App question

Question

Database Mirroring - Web App question

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Divine Flame SSCoach Points: 15943 More actions · Answer 1

Hi Jody,

Actuly Web App high availability & database high availability are totaly different senerios & independent of each other. So please don't mixup. To achive the high availability at both application & database level you need to perform following steps:

1. For web application high availability:

Ask you IT/Infra team to prepare the web servers in NLB (network load balancing). In this each application will be hosted on 2 (minimum) or more then 2 nodes. The NLB comes in 2 forms, one is hardware NLB & one is software NLB. remember after configuring the servers in NLB you need to put the same build of the application code on both servers, and on both nodes the application will be hosted in IIS with same virtual diarectory names.

Now, if one node of NLB server is down, the other node will still be working fine & it will be serving the purpose.

2. For database high availability:

For database high availability you are already using the mirroring which will take care of the things if the database server is down. Remember you have to put the value for "failover partner=<secondary server>" in the connection string of the application code. (same code will be hosted on the each node in NLB servers).

Hope this helps. 🙂

Sujeet Singh

thesubjectexpert Valued Member Points: 67 More actions · Answer 2

Sql Server Secret Diary - Know the unknown of SQL : http://www.thesubjectexpert.com/solutions

wbrianwhite SSC Eights! Points: 954 More actions · Answer 3

Danny Holcombe (8/27/2010)
>> Event trigger on the witness to fire off a command line to kill all pooled connections on the web server.
Andrew,
Do you know of another solution for classic ASP applications using ADODB.Connection to immediately use the "Failover Partner" from the connection string without having to Recycle the Application Pooling or "kill pooled connections"? It just seems like a bug or limitation in IIS 7.0 web server to not be fully aware of the "Failover Partner" portion of the ConnectingString and have these connections cached/pooled without doing a failover check.
Here's my Scenario:
1.) Classic ASP page running on IIS 7.0 (Windows 2008 Web Server)
2.) ASP test page using the "Failover Partner" parameter of the ConnectionString
Successful when Principal Server is online!
Set cn = Server.CreateObject("ADODB.Connection")
cn.ConnectionString = "Provider=SQLNCLI10; DATABASE=myDB; UID=dbuser; PWD=dbpass; SERVER=SQLSVR01; Failover Partner=SQLSVR02\MIRROR"
Response.Write "provider: " & cn.Provider & "<br>" & vbCrLf ' will display SQLNCLI10 in the browser
cn.Open ' Connects to SQLSVR01 (Principal Server) successfully
Set rs = cn.Execute("select @@servername AS ServerName")
Response.Write "server: " & rs("ServerName").Value ' will display SQLSVR01 in the browser
rs.Close
cn.Close
[Browser Output]
provider: SQLNCLI10
server: SQLSVR01
[End]
3.) After the above has been executed from a browser, I then use SQL Server Management Studio (SSMS) and open a new SQL Query on the Principal server and initiate the command "ALTER DATABASE myDB SET PARTNER FAILOVER"
4.) Using SSMS, I connect to the Mirror server and verify that the Mirrored database has taken over the role of Principal [+] MyDB (Principal, Synchronized)
5.) I execute the above ASP page again (F5) in the Browser window and get the following response.
[Browser Output]
provider: SQLNCLI10
Microsoft SQL Server Native Client 10.0 error '80004005'
Cannot open database "MyDB" requested by the login. The login failed.
/test.asp, line 6
[End]
6.) Next, I recycle the Application Pool or do an IISRESET and execute the ASP page again (F5) in the browser and then it works
[Browser Output]
provider: SQLNCLI10
server: SQLSVR02\MIRROR
[End]

I am having the exact same problem. And we have a web farm of like 30 servers. IISReset on all those servers would be time consuming.

Anyone have any good ideas? It really seems like Failover and ASP just don't work well together. Works fine in .net sql client.

This is the horrible idea i have that works. I set one row in our config db to be 'example db should be failed over' to 1 or 0. If it's 0, I strip out the failover info. If it's 1, I flip primary and failover, then strip out the failover info. That way they are 2 separate connections and don't get pooled. It is the most horrible hack. But i literally can't find anything else that will work.