September 25, 2009 at 7:03 am
Does anyone know how to get the Native Client to redirect requests to the Failover Partner, the databases failover correctly and can be accessed using Management Studio but using the following connection string in ASP fails to connect to the Failover Partner.
Provider=SQLNCLI10.0;Password=########;Persist Security Info=True;User Id=username;Server=192.168.###.1,1433;Failover Partner=192.168.###.2,1433;Database=mirroredDB;Timeout=2;
If I amend the connection string after the failover to
Provider=SQLNCLI10.0;Password=########;Persist Security Info=True;User Id=username;Server=192.168.###.2,1433;Failover Partner=192.168.###.1,1433;Database=mirroredDB;Timeout=2;
The site connects correctly.
August 27, 2010 at 4:53 pm
Andrew,
I'm having the same issue. In order for me to get my ASP page to failover to the mirrored database, I have to to stop the IIS worker process to release the database connection.
I was reading in the discussion about "Database Mirroring - Web App question" and somebody had suggested that the Witness server should launch a procedure that would "kill pooled connections" when a failover occurs.
Has anyone figured out a solution for classic ASP applications using ADODB.Connection to immediately use the "Failover Partner" from the connection string without having to stop the Application Pool Worker Process in IIS 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 & "
" & 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]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply