Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Is there a way to set a Mirror/Failover Partner for a SQLMSS data adapter?
Go
New
Search
Notify
Tools
Reply
  
Is there a way to set a Mirror/Failover Partner for a SQLMSS data adapter?
 Login/Join
 
Member
posted
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.


WebFOCUS 7.6.11
Windows2k3/SQL Server 2005
Output: HTML, Excel, PDF
 
Posts: 7 | Registered: June 11, 2008Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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


--------------------------------------------------------------------------------
prod: WF/AS 8.2.05; OmniGen;
In FOCUS since 1991
 
Posts: 105 | Location: United Kingdom | Registered: February 07, 2008Reply With QuoteReport This Post
Member
posted Hide Post
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


WebFOCUS 7.6.11
Windows2k3/SQL Server 2005
Output: HTML, Excel, PDF
 
Posts: 7 | Registered: June 11, 2008Reply With QuoteReport This Post
Expert
posted Hide Post
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


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1950 | Location: New York | Registered: November 16, 2004Reply With QuoteReport This Post
Member
posted Hide Post
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


WebFOCUS 7.6.11
Windows2k3/SQL Server 2005
Output: HTML, Excel, PDF
 
Posts: 7 | Registered: June 11, 2008Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Is there a way to set a Mirror/Failover Partner for a SQLMSS data adapter?

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.