A client of ours has about 70 MS SQL databases on a bunch of single servers running. The client wants to consolidate all those databases into 1 large database. Data wise, we are talking about 3 TB. The client insists High Availability as the data is critical.
I've setup a couple of clusters [ActivePassive] in the past, but I don't know if that's the right choice in this case.
The Client suggested to run the database on 2 or 3 instances. As I'm someone who is specialized in Oracle. I thought that was the main difference with Oracle, that it's not possible to run a database on several instances with MS SQL.
I'd love your intake on this one.
asked Jan 26 '12 at 11:43 AM in Default
i think, Log-shipping is not a better choice. as mirroring does better that Log-shipping. I would recommend you to set up cluster for database access availability. Mirroring, replication would cater for disaster recovery. for performance, you can use RAID 10. number of spindles, enough RAM, proper configuration of RAM, Indexing and stats, regular maintenance.
answered Feb 03 '12 at 03:05 PM
"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.
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?
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.
answered Jan 27 '12 at 01:45 AM