This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Since Microsoft SQL Server 2008, database mirroring has been included as standard from Microsoft. This seems a good solution for adding entry level high availability for some of my customers who do not want to pay for a SQL cluster. Whilst I understand that this functionality is implemented at the database server / connection level, I was wondering if anyone had used lightspeed with SQL Server mirroring and experienced any problems? I have searched this site and the product documentation but mirroring / high availability does not seem to be mentioned.
|
|
|
|
|
Hi Mark, SQL Server mirroring will operate behind the scenes, so there isnt any impact on LightSpeed directly. You may of course want to be taking this into account as part of your application design if you were needing to manage failover etc. How are you looking to have this operate? Happy to share our thoughts if they would be useful :)
Jeremy |
|
|
|
|
My plan was to configure SQL Server 2008 R2 with Mirroring in high safety mode, where there are two servers and a witness. Thus providing auto-switchover on failure. By my understanding, from an application point of view I need only change the connection string to mention the two servers. With this configuration I believe a switch over is within seconds of a failure, hence nothing more needs to be changed in the application itself. All critical db actions are within .Net Transactions, so they would fail on mass, and need to be resubmitted. Is this how you understand it? We would of course run some testing to ensure a smooth failover.
|
|
|
|
|
Hi Mark, Yes, having the application fail over by switching the connection string will be all that is required provided the database mirroring is up to date. You may want to also add some basic checks to validate the expected state on the new datbase server or to flush any cached objects if you are holding any after failing over.
Jeremy |
|
|
|
|
Hi Jeremy, Thanks for the feedback, but must admit to being a little confused. As the failover should, in theory, take only a few seconds, and is handled at a database / server level, how will my application know it is a failover? and not just a temporary glitch in the PC / Network? I am guessing Lightspeed will not really know either so would be unable to raise an event? Best regards |
|
|
|
|
The application is not going to know by itself - as you are correctly assuming LightSpeed does not know about this type of event occuring and its likely that when this happens it will just look like a timeout or network connection related error. You will need an external process which is observing the principal and the mirror to determine what has happened and send a message to the application to trigger failover. This is pretty much the role the witness is fulfilling. You may be able to handle this by monitoring for event changes on the witness server, have a look at http://msdn.microsoft.com/en-us/library/ms191502(v=SQL.105).aspx which gives details about the event which will be raised around mirroring changes and then set this up as an alert which triggers a script. Failing that you could have an external process which is monitoring sys.database_mirroring (see http://msdn.microsoft.com/en-us/library/ms178655.aspx) and checking for when a server switches to being the principal and then fires off an alert that way.
Jeremy |
|
|
|
|
Never really done much on SQL Serve Events before, so will need to take a look. Thanks for the advice.
|
|
|
|
