SQL Server Performance Forum – Threads Archive
Redunant Solutions
I’ve been researching redundancy solutions for SQL server over the past week. The problem here is that i would like two geographically disperse servers (at different colocation facilities) to be sync’ed. Does anyone know a good solution either using windows clustering, sql replication, log shipping, or sql failover clustering.. it doesnt have to be limited to any of those, but i can seem to find a solid article specifying if this can actually happen. i know windows clustering requires a delay of <500ms, so i am considering that, but still i have no hard evidence any of this will work. Please HELP!In regards to synching, what kind of time lag is acceptable to you? Also, how will the second node be used (failover only, active use by others, etc)? —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
I would like to minimalize lag time greatly, but if it cant be avoided, its understandable. The second node would idealy be active, in a geographic load balanced situation, however it can be passive if the latter cannot be resonably configured in regards to price, time, etc…
oh an btw, that sql server performance audit article you wrote up helped me a ton!
For an active/active solution you are fairly limited. If your databases can be logically divided into sections, you can use merge replication; however this is very heavy on the maintenance and design time. You could use a third party replication tool or log shipping if you can divide the databases in half. You could then have a half active at each location with replication or log shipping to the other location. The Windows Clustering is not viable for a remote DR location. You have to share the resources, meaning the actual disks would be located in one location or the other. This doesn’t make sense from a DR perspective. Clustering provides server redundancy only, not disk redundancy.
Generally, it’s preferrable to either divide your applications and have DR in both locations or to use an active/passive scenario. For this, you can use log shipping, sql replication, or hardware replication such as EMC provides. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
What we are attempting to plan out is not a very complex database, however we would like to plan for future growth. We’re dealing with one primary user database and one application that will access this database. from my understanding log shipping will only occur at the times where you specify it, so that means the locations will be sync’d within a 15 minute period, and this may not be feasible. Replication seems to be the best situation to provide data consistancy, but im not fully versed in the other options to rule them out and commit to replication. Thats why im here! i think if i am correct, you can only have a even number of nodes in a clustering situation. so if we had an active/passive at the main location, and a passive at a remote, that may not be able to be done. From what you are saying, windows clustering will not mirror the drives? just the services and the data associated with those services? so wouldnt the sql data be replicated as well? and if i had a third party hardware replication tool, will that ensure that both the data and the services would be replicated? I am really looking for a next to completely automous solution that will provide consistancy over both sites and solid failover. i am not worried about the time setting it up, just as long as it’ll pay off by being nearly failsafe.
well after doing a considerable amount of research on this all, i think we’re going to opt for a third party solution from legato. our main problem was preforming automatic failover and replication of a wan link. legato’s replistore with the automatic availablity manager seems to handle the trick all inclusively. if you think this may be an option for anyone else, check out legato.com, and on the same note, if you’ve experienced any negative occurances with these applications please let me know!
]]>