Hey there Stephen, Thanks for the input. Yes, one of the things the system mentioned was that something could occur During the time one is already connected to the Database - and things may need to be switched over. He presented the situation much like you did below.
I will pass on your suggestion to the rest of my team. Although - what you are saying sounds like it would require a LOT of code changes to do. It's probably the Safe thing to do - but, not sure if we will actually do it... Just sayin'... Thanks again, -K- -----Original Message----- From: ProfoxTech [mailto:[email protected]] On Behalf Of Stephen Russell Sent: Friday, July 24, 2015 11:20 AM To: [email protected] Subject: Re: Connecting to SQL via VFP w/Failover... > From: ProfoxTech [mailto:[email protected]] On Behalf Of > Kurt Wendt > Sent: Friday, July 24, 2015 10:10 AM > To: [email protected] > Subject: Connecting to SQL via VFP w/Failover... > > Hello Fellow ProFoxers, > > So, it recently came to my attention here at work that they have this > Disaster Recovery location in Albany. And, the Server here in > Manhattan gets Mirrored to a server in Albany. > > For this one system - which I am now in charge of supporting - there > is code to allow it to switch over (like the Failover in .Net) to this > Albany server. But, when one of the systems guys recently tested the > App on a Sat > - by "shutting" down our Manhattan server - it didn't switch over to > the Albany server. > > More specifically - this is what happened. When the App tried to > connect to the NYC server - it popped up a nasty error message on the > screen(Connection failed: SQL State...). Then, if you hit OK - you get > this "SQL Server Login" pop-up window. > > The trick is - when you get to that "SQL Server Login" pop-up window - > if you just hit Cancel - then the app will proceed to connect to the > Alternate server in Albany. However, the systems guy - he wants to > Avoid getting Error window and the "SQL Server Login" pop-up window. > He is asking me to somehow trap for that situation - then handle it > behind the scenes to then connect to the alternate server automatically. > > Well - I tried doing something like that. I used the PING command - by > stealing code from another one of our Apps - and implemented it in > this App I need to support. So - I just pinged to see if the Server > was up. I tested by F-ing up the Server name I was trying to connect > to. So - the Ping worked - and after finding the failed Ping - It > simply switched to connecting to the other Server. > > However, the systems guy said that isn't sufficient. He told me that > the Server could be Up - but, the Database could be down. So - I can't > just Ping the Server and assume that would be sufficient. HE told me > that a Database has Two States: Principal & Mirror. As such, I need to > have code in place that would handle the situation if the Database is > Down completely. Or, it must switch over to the Alternate server in > Albany if the Database that's being connected to in NYC has a Status > of Mirror instead of Principal. > > Finally - my question to you all. > > In the App it's using the SQLStringConnect command from within VFP. > But, I know that if this command has a failure - it displays the error > as previously mentioned - and then shows "SQL Server Login" pop-up > window. I'm also assuming that the control of this SQLStringConnect > command is outside the scope of VFP - and that I can't control the > display of that error message. Or CAN I??? Also - is there any way to > detect the status of the Database - of Principal vs. Mirror? > > ---------------------------- You have two things happening here that need checking. 1. Failover of backend data from one system to another. 2. Clients attached have to determine if they are still attached. Good chance that SQL's high Availability is doing what it should. Won't go into what happens inside the API to make that switch but you can google the heck out of it if interested. In the end the API is presenting itself as it was before and a new command to it will work just fine. Your problem is that you were is some phase of interaction with the data and the backend just swapped you out. You cannot just submit an update statement to the server without getting a new connection to it first. Thus you have to validate the connection is valid before every data request. -- Stephen Russell Sr. Analyst Ring Container Technology Oakland TN 901.246-0159 cell --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/80838f1ca795b14ea1af48659f35166f1ce...@drexch02.corp.globetax.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
