Here's my situation: We are planning to move our production databases from a single SQL Server 2000 setup to a new mirrored SQL Server 2005 setup with automatic failover. I am currently testing failover on our Development server. On the database side of things, the automatic failover is working well. However, I'd ideally want our WebFOCUS reports to automatically point to a new server after a failover also.

I've had success setting a "Mirror Server" in the SQL Native Client's ODBC driver. However, in testing I realized that each of our roughly 50 master files would likely need their SUFFIX=SQLMSS changed to SUFFIX=SQLODB (an somewhat annoying chore). Also, in testing this on a mock master file, I've encountered a roadblock where the dates are strangely are all winding up as 1920/MM/YY when converting to the YYMD format all our reports are written to use... but that can be a topic for another thread if I wind up going the ODBC route.

I was wondering if there is any way to set up a SQLMSS data adapter to have a mirror/failover partner server that will automatically be used if the principal cannot be reached. While there is no field for it in the "Configure Adapter" form, but I was wondering if perhaps there is some way to add it to the edeasprof file.

If all else fails, I think I can do some combo of SQL Agent Alert that will detect the failover, that in turn will run a VB script Job that will replace the old DB server name with the new DB server name in the edasprof file... but if there's a built-in way to do this I'd definitely prefer that to this homemade approach.

Would you please clarify, is your auto failover another SQL Server 2005 server?

Then surely the only change should be the adapter properties in edasprof.prf

I am failing over to another SQL Server 2005 server, so you are correct that all that is needed is a change to the edasprof file from OLD_SERVER_NAME to NEW_SERVER_NAME in the line:

"ENGINE SQLMSS SET CONNECTION_ATTRIBUTES MY_CONNECTION OLD_SERVER_NAME\INSTANCE[...]".

I was just wondering if there was some "official" method to declare the 2nd server right in the edasprof file, so that if the 1st server is down, it would automatically try the 2nd without any human interaction necessary (like the SQL Native Client does). If no such method exists, I guess we can consider this question closed.

In case others using mirroing might be interested, as mentioned in my 4th paragraph, I did try making an Alert/Job on my mirror server that would run the following VBScript after a failover where it becomes the principal. It does accomplish the automated "find and replace" of the edasprof.prf file that I was hoping for (you'd want to change the filePath, OLD_SERVER_NAME, NEW_SERVER_NAME to match your setup):

filePath = "\\MY_WFSERVER\C$\ibi\srv76\wfs\etc\edasprof.prf"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(filePath, 1) 'Open For Reading
strText = objFile.ReadAll
objFile.Close
strNewText = Replace(strText, "OLD_SERVER_NAME", "NEW_SERVER_NAME")
Set objFile = objFSO.OpenTextFile(filePath, 2) 'Open For Writing
objFile.WriteLine strNewText
objFile.Close
Set objFSO = Nothing

Hi Eric,

I passed this one to our iWay experts and here is the response: This is not a function of our software and should be transparent to us. We do not provide failover within the adapters. I believe if they configure a clustered SQL Server environment and point the adapter to it then SQL Server will handle it. More research may be required here. If so please have a case opened.

Cheers,

Kerry

Thanks for the follow-up Kerry. That answers my question (I suspected this was not a built-in iWay function, but figured I'd check to make sure before going to the plan B I described above).

-Eric