Hi,
< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
We are looking to implement a SQL Server 2005 (64-bit) architecture incorporating diaster recovery. Please see the following diagram.
http://www.users.on.net/~karunathilake/misc/SQL%20Architecture%20(SQL%202005).JPG
Our goal is to utilise all the nodes available whilst maintaining a manageable environment so any feedback and comments are greatly appreciated.
Site1 will have local failover using Microsoft Clustering meaning that if one of the nodes go down, then instances running on that node will failover to the other node at Site1. The failover scenario will also be the same at Site2.
And now for the interesting part…….
We are looking at utilising SQL Server 2005 database mirroring for disaster recovery (i.e. in the event of one of the sites being unavailable). For example, on Node1 the SQL01 instance would be mirrored to Node3 at Site2 and similarly SQL02 instance at Node4 would be mirrored to Node2 at Site1. When I mean mirrored, I am refereeing to mirroring occurring at the database level so all the databases in that instance will be mirrored. On Node1 we will have over 20 databases that will be mirrored to Node3. The mode of mirroring will be Asynchronous and in the event of a outage at a site, we would manually failover the databases to the other site (i.e. make the mirrored database the new principal).
We acknowledge that since system tables cannot be mirrored, in the event of a failure, we would need to recreate jobs, login/users/permission and replication on the new node. DTS packages will be stored in a separate server so all we would need to do is point the connections to the new node and instance.
Due to the large number of databases we are looking to mirror as well as high volume of transactions in these databases we are not sure what the impact the mirroring will have on the systems, SANs or the network but we will testing this in our Preproduction environment to get an idea. I also believe that mirroring at the SAN level is also a possibility so any comments from users on this type of technology would a greatly appreciated.
Sorry for making it long.
Any comments are greatly appreciated.
Thanks,
Priyanga
Implementing SQL Server 2005 DR solution with database mirroring
Crazychil
comments:
1. since the safety is OFF, you are in the asynchronous mode and there is no automatic failover, so you do not need a witness.
2. 20 databases. with mirroring, for each database you need a couple of threads dedicated to run mirroring. hopefully the performance will be enough
3. with asynchronous, the problem occurs where the throughput of the network is too slow. as a rule of thumb, i would say that the network bandwidth should be at least 3x larger than the peak logging rate of all the databases.
4. now i have a generally bad feeling about the running active/active clusters. people seem to size them up so that if one node fails, the load after failover will be low enough for the node to handle it. then after running the system for a year or so, the load increases gradually as you do more and more with the system. there is a failure, and after the failover, the load is too much and the node slowly grinds to a halt, decreasing your availability.