asked
Regarding High Availability for a 3TB Data environment
Hi guys, 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 [Active\Passive] 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.
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.
After I re-read your question I see you are looking for HA not really DR. With that being said I would look at T-Log Shipping, and Replication. There are some new really cool features coming with SQL Server 2012 and always on, that you might want to look at as well.
Thanks Chris, I asked the customer for more information so I could get a better idea of what they want. But I thought, let me ask the question here, perhaps others have already setup something like it. Amount of time down - As minimal as possible. location of servers - we have 2 datacenters so we could integrate that in our solution Budget - Customer insisted on a solid solution regardless of the cost, meaning that they won't decline if the cost is more or less 5000 dollar more than what they have\had in mind. Transactions - He wasn't sure but he thought that the current application inserted a total of 1 million records every hour. There would be about 300 end users who would consult the database with read\write actions.
I think your best best on the HA side is to take a strong look at replication, there are some other considerations, you may want to look at.
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?
You should have posted this as a new question to get a much faster response.
Hmmm. Moderator I may be, but I don't seem to be able turn an answer into a question. It'll have to be reinput. Sorry.
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.
@Freddy Question in another question...do not know how it will be dealt with. You should post it as another question. Some people would not even look into it as there are already 4 answers against it.
O no....Now since my answer is voted up, the question is now standing above the question. What a bad...
Sacred Jewel, thanks for the quick response !!
You would have got a much quicker response if this has been a new question. Question was with full details, nice image, but of little use here. BTW, I cannot see your application A connect to failover if the NETWORK lines goes down? So in this case, the application would still be connected to the principal?
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.
4 People are following this question.
