Regarding High Availability for a 3TB Data environment

This is a huge topic, and there are a lot of potential solutions. So the quick answer first… A SQL Server database can not have multiple instances attached to the same .mdf file at the same time. With that being said, there are a few options you may want to consider…

Database Clustering - This is where there are 2 or more machines, only one machine at a time can "own" the database, however if that machine fails for whatever reason a second server takes over. There is a lot more to this, but the basics. So there is only one copy of the database that is on shared storage.

Database Mirroring - This is close to clustering, however rather than all the databases moving from one machine to another, it can be done a database at a time. This is traditionally done with 2 storage devices, and is initialized with a Backup/Restore process. There is an automated failover process with this.

T-Log Shipping - You can move backup files from one machine to another, there are some timing considerations with the restore, and how you move records after a failure.

Replication - this is a row by row type of a deal to move data around. Not always considered a "failover" technology, but some use it like this. There are options to move data back and forth between machines that are online.

Really there are some questions or requirements that need to be gathered, things like what is the amount of time they can be down, how much data are they willing to lose. Are the machines in the same location, what the budget is like, how many transactions they are talking about, what is the data pattern, just to name a few.

I know this doesn’t help a lot, but if you can answer some of the requirements, or if you want to take some of the keywords and look at information on them, I am sure there are a lot of people here that are more than willing to answer any questions you may have.

I agree with @Chris Shaw's hints on making sure the business/client says what is required. They need to be crystal clear on this so that you can choose the right solution.

Going by what you have said, I would suggest consolidation onto an server in DC1 and have DB-mirroring to a second server in DC2. This gives you safety across sites (DC1 is down, so use DC2). Added to this, DB-Mirroring can be setup so that the failover is transparent to the client software (involves a small change to the connection string of the application).

You can further improve availability by making the servers in each location active-passive clusters, but this raises your admin (although the passive nodes are essentially free of license costs for SQL Server).

As @Chris Shaw also stated, SQL 2012 will make this a little better/easier with the AlwaysOn. This rolls the HA features into one feature pack and allows you to specify what you want and be less concerned with how SQL Server achieves those goals.

Guys, I have a rather urgent question.

I setup a design with 2 datacenters where the witness server and the Mirror server are in site B, the principal server in site A. The layer above will be an application server that pushes data from the application server to the database.

Possible issues that he network architecture suggested was:

1) If the network line between the two sites is down ==> Principal disconnected from the witness and mirror. What will happen at that point with the principal as the witness will force the mirror to open and become Active?

=> Will the principal know in this case that the mirror server became the principal and execute a role switch on his own?

2) When the network line between the sites comes back up. Does the principal at that point become the mirror database as the mirror database was previously forced by the witness to become the principal?

The network architecture wants to be sure that both servers are not working as principals when the network line comes back up.

3) If for a weird reason, both the mirror & principal have transactions on each server that haven't been applied to the other side .. Will there be data loss at that point? or what?

Can anyone help?

For

"Will the principal know in this case that the mirror server became the principal and execute a role switch on his own?"

No. The principal would then work as the mirror, and its database becomes the new mirror database. (But if some users or some application is still connected then that is a seperate issue. Since WITNESS is disonnected, the automatic failover will happen if configured)

For "Does the principal at that point become the mirror database as the mirror database was previously forced by the witness to become the principal"

Same as above. But After a role switch, certain metadata must exist on both partners to ensure that all of the database users can access the new principal database. In addition, backup jobs must be created on the new principal server, to ensure that the database continues to be backed up on its regular schedule.

For "If for a weird reason, both the mirror & principal have transactions on each server that haven't been applied to the other side .. Will there be data loss at that point? or what?"

That would depend upon the situation, if there are users connected to the former principal, and some other users connected to the new principal servers, this may lead to a scenario, where data loss could be the result.

Sacred Jewel, thanks for the quick response !!