Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jgblack2
Starting Member
1 Post |
 Posted - 2012-01-13 : 10:12:20
|
| I have been asked with coming up with a solution to provide HA redundancy for our SQL 2008 r2 production. We are trying to create the following scenerio and I am researching to see if there is a Microsoft solution;1. We have a production SQL server running 2008 r2 enterprise and want to make it redundant. 2. This production server has it's own independet storage and we want to keep it independent.3. We want a syncronous replication with another server and we want that "other" server or "backup" server to have it's own indepenent storage, not shared storage like a cluster would apparently have.4. So basically there would be 2 SQL servers with databases that constantly have the same data in each database5. If the production server were to crash or have an issue, we want automatic failover, so the SQL clients do not miss a beat or, worst case, have to close their application and reopen it, without making any changes and no interaction from the system admins. 6. We want to make this happen with 2 servers, if possible, or maybe a third monitoring server.When I first had this explained to me, I thought we would be looking at mirroring, but I sooon found out that there was no automatic failover for the clients and it required admin intervention, to get all the clients pointed to the mirror, after it took over. So, is there a Microsoft solution that will give me the results I want in this scenerio? Please remember these servers will be VMs, created on the latest VMware software. We are using Win 2008r2 enterprise and SQL 2008 r2 enterprise. I was looking at clustering but that all seems to end up with shared storage between the production and backup server. We are hoping storage can be kept independant |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-13 : 10:31:08
|
| I don't know much about this, but I expect folk that do will be along shortly (and possibly before I have finished typing this!!).If you have shared-storage between two SQL Server machines then the switch over is straightforward. No need to implement replication.Replication means that each record that is updated on Server-A has to be replicated to Server-B before the transaction can be Committed. This takes time / adds complexity and MAY cause new issues in your application. Shared-storage avoids that.We tried shared-storage with a pair of VM machines and it was a disaster. I have no idea what we did wrong, and people with much heavier-iron than us do it routinely, so it was clearly us! I know that the empty-box for the shared-storage was a "fortune" (in my eyes) so it wasn't because we skimped on that.Our VM machines could only use 4 CPUs, we had 8 available. Performance was dire (possibly as a consequence, but from day one I had been reporting that "some things are sluggish" - although I couldn't put my finger on exactly what ... at that time no one but me and another DEV were using the machines.After going live and getting some real-world performance problems we broke the shared storage, made it local to the machine, and got rid of the VM, and since then performance is what I would "expect". However, redundancy is out-of-the-window. We've implemented Log Shipping to the original standby-server (client can tolerate some data loss, and some downtime; our log backups are 15 minute intervals, so we might lose 15 minutes data worst case, assuming when disaster happens we cannot get a final tail-backup of the log) |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-13 : 11:42:10
|
| With mirroring if your apps use SNAC or the newer .net drivers, there is automatic failover. Read up on Transparent Client Redirection--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
trying to find best solution to provide redundancy