August 26, 2010 at 8:25 am
Ok, this is my first mirror setup. I have successfully set it up with witness, using TDE, and in a non domain environment.
Now my issue is that the web application is running on "LIV" and the database is mirrored to "MIR". If "LIV" were to go down, of course my database would be up but the webapp would be down. Is it as simple as putting the webapp on "MIR" and the connection will resolve itself or is there a few more steps I am missing. I have a feeling it is the latter.
Thanks in advance.
August 26, 2010 at 2:00 pm
To make automatic failover seamless you will need the "Failover Partner=" parameter in the connection string if it's supported. You need to be utilising either SQL native client or ado.net.
For more on ado.net check this link
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 26, 2010 at 2:05 pm
But if the webapp is on "LIV", the connection string is on that server. So if that server goes down, so does the whole app. Isn't that right?
My question is, do I need to install the webapp on MIR also? If so, how would the connection know to go to MIR if LIVE was down.
OR do i need to have the webapp on a different server than LIV and MIR?
OR is there another option?
August 26, 2010 at 2:09 pm
jody 26314 (8/26/2010)
OR do i need to have the webapp on a different server than LIV and MIR?
Yes!!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 26, 2010 at 2:14 pm
Ok. Maybe I can put the webapp on the witness. Sorry, I am fairly new to the mirror/clustering business.
So if I wanted to just use LIV and MIR, have the webapp just on them, and I had a iSCSI SAN, it would have been more ideal to originally use a cluster instead of a mirror or No?
August 26, 2010 at 2:56 pm
Depends on what you are trying to achieve. Thinking about it a little more you could have a copy of the web on the mirror and make the website live when the database fails over!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 26, 2010 at 5:15 pm
jody 26314 (8/26/2010)
OR do i need to have the webapp on a different server than LIV and MIR?
this would be the normal practice.
---------------------------------------------------------------------
August 26, 2010 at 7:34 pm
Jody,
I'm not sure if I understand what you are trying to achieve. Mirroring the DB with a witness will make the DB highly available but if you only have one instance of the web app then you still have a single point of failure. For the entire application to be HA then you need to have multiple instances of the web app and use a proxy, NLB, etc to ensure that your web app is always available.
Having the failover partner in the connection string will ensure the web app can connect to the DB in the event of failure on the primary DB.
Also, keep in mind that if you use connection pooling your connections will not immediately fail over to the failover partner. New connections will but existing connections will still look for the primary until they timeout.
Cheers
Andrew
August 26, 2010 at 8:22 pm
Andrew,
I actually think you do understand my situation.
I was looking at NLB as well as Application Request Routing unless those are one in the same. I need to do a little more research to so I can further understand them. Can I use the witness server as a control to control the failover for the web app? If so, can I then just have the web app on the principal and mirror so if the principal goes down, the witness will faillover both the DB and webapp to mirror?
August 26, 2010 at 8:42 pm
Jody,
I'm not sure of the specifics of your environment, but what I did in our dev environment while playing around with mirroring was to set up an event trigger on the witness to fire off a command line to kill all pooled connections on the web server.
Perhaps you could do something similar with a triggers to reconfigure where you app is running.
Cheers
Andrew
August 27, 2010 at 3:15 am
To make the failover automatic you need to be using the latest access components, i.e. as Perry said SNAC. check BOL and msdn for details on this.
You would also need to move the app off of the SQL server so the app does not go down as well when SQL does. I believe normally there would be two load balanced web servers to provide resilience, but of course it depends on your budget and downtime you can tolerate. As the mirroring witness can be the express edition of SQL and has a small footprint the web server could support this.
If you can tolerate some manual intervention the repointing is frequently done using a DNS alias, you just point this at the other server when a failover occurs, this propagates pretty quickly.
---------------------------------------------------------------------
August 27, 2010 at 7:24 am
I have no issue with the failover of the DB. It is just the app I am tying to figure out.
My setup to make understanding easier....switch, 3 servers, 1 iscsi san. Non domain. 1 primary, 1 mirror, and the witness.
Just curious though, if I wanted to originally just have the webapp on LIVE and MIR, would it have been better to go with a cluster setup?
I may just wind up moving the app to the witness but then I have no failover there if the app fails.
I was looking at this last night. Seems like a viable option: [/url]
August 27, 2010 at 2:14 pm
>> 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]
August 29, 2010 at 4:44 pm
Danny,
I think this is to do with connection pooling. Once your web app connects to the primary the connection is held in a pool. This pooled connection will remain pointed at the same SQL instance even if the DB has failed over. Any new connection will point to the failover partner. That's why recycling the app pool works, it clears the connection pool and makes a new connection.
To avoid having to recycle the app pool you can turn off connection pooling in your connection string or set a timeout for connection. Be warned, however, that there is always an overhead for creating new connections and this could impact performance.
Hope this helps.
Andrew
September 1, 2010 at 4:15 am
Hi Jody,
[highlight=#ffff11]Based on the mesage trail, you are running your web application on your SQL Server infrstructure and mirroring your database with witness based automatic fail-over? Thus, in the event your "LIV" database fails for any reason, you would want to re-point your application at the mirror, but currently have only a single installation of the application?
OK, firstly, as already indicated by other forum members, you will need to ensure your connection string has the partner server included and your application is clever enough to attempt re-connecting. In the event your database server is completely down, you will get the automatic fail-over at the database level, but the application will need to also reside on the "MIR" server and triggered to start.
This configuration is not an ideal setup for a web application with both your data and application tiers residing on the same physical hardware and you should consider removing the web application off of SQL Server and commission two additional dedicated web servers with a front-end NLB component to remove the single point of failure. On each web server, you would ensure the application has both mirror partners configured, thus in the event your database fails-over to mirror server, the application will attempt to connect to the next partner in the list.[/highlight]
Hope this helps.
Phillip Cox
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply