January 28, 2014 at 9:44 am
Hi guys,
I would like to have your opinion on the following situation:
I've got a customer with an ERP application that has a client to direct SQL Server model.
The database is about 200GB, but most of it is historical data. Maximum about 20 concurrent users. Requirement is to have minimal downtime in case of any hardware failure, because customers are always buying stuff over the counter.
At first I thought about an Active/Pasive SQL cluster. But what takes me down is the hardware expenses for just 20 database users. A/P cluster means I need two servers and a SAN. Also there is the case of SAN failure; so I would also have to buy a backup SAN and do some kind of mirroring. Summing up, this would at least cost about 25 - 30 thousand Euros. And I understand it's also more complicated to mantain.
So the next option I'm thinking of is database mirroring with a witness server. With this option I could buy 2 servers with 8 - 10 internal storage drives, and maybe a smaller third server for witness. So the cost would go down more than half, about 10 thousand euros.
- I would like any comments or thoughts about if you think I'm making the right choice here.
- Also i thought of putting the Witness server on the secondary SQL Server (the mirror), so I don't need to buy a third server just to act as a witness. This means if the mirror server fails, the witness fails, but I guess users still can work.
- Another option would be to virtualize the witness, but this would anyway mean that if the host server fails, the witness fails. Anyway, now days everybody is going all crazy about virtualization, but in this scenario when using Servers exclusively for SQL Server and doing mirroring, does virtualization bring something to the table?
Any advice is appreciated here. Thanks in advance.
Best regards.
January 28, 2014 at 9:57 am
With SQL Server 2012 Always-On AG you can have active/Readonly-Active cluster without a SAN. You just need two servers set up the same way.
The probability of survival is inversely proportional to the angle of arrival.
January 28, 2014 at 10:03 am
sturner (1/28/2014)
With SQL Server 2012 Always-On AG you can have active/Readonly-Active cluster without a SAN. You just need two servers set up the same way.
It's good to know! Thanks for the answer. However, the manufacturer of this client application doesn't support SQL 2012, so it's not an option for the moment. I'm afraid I have to stick to SQL 2008 for the moment. :doze:
January 28, 2014 at 10:34 am
I have several databases that are mirrored. A few have a witness server and a few don't. Initially I set up all of the databases with witnesses until one failed over without me knowing. The application was not set up to handle the fail over so I reconfigured the mirror removing the witness.
I learned my lesson here. If I wanted a "real time" mirror as a DR backup I will not configure the mirror with a witness. If I need HA and the application is smart enough to handle a fail over then I will set up a witness.
Setting my sights to 2012 with Always On, I plan on moving these databases that have mirrors to 2012. Some of them are small applications do the risk is low. I plan on moving into A-O before the end of Q1-2014. It makes a lot of sense to migrate into this direction when the resources are available.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
January 28, 2014 at 1:09 pm
If I need HA and the application is smart enough to handle a fail over then I will set up a witness.
I guess this means that the application needs to have some coding that says "if connect to sqlserver1 fails then connect to sqlserver2" or use the SQL native client.
It wont be automatic as in a A/P cluster where we have the application pointing to a virtual IP.
Nice point! Thanks for that. The thing is this ERP doesn't use the SQL Native Client nor has an editable code, so if I need High Availability I'm going to need a cluster configuration.
¿any ideas for low cost HA cluster? Fibre channel Storage is very expensive, and I don't understand why.
I think the manager will agree to buy 2 servers and 1 SAN. How can I deal with a SAN failure and still have minimal downtime?
January 29, 2014 at 5:30 am
"I learned my lesson here. If I wanted a "real time" mirror as a DR backup I will not configure the mirror with a witness."
Please remember that mirroring is not a DR backup solution. You delete all of your data and it goes on the mirror as well pretty instantly. Mirroring is for high availability only. You still need regular backups in case of data loss.
January 29, 2014 at 9:18 am
Just keep in mind that Always ON is Enterprises only feature where as Mirroring is available for standard version also and work very well for HA purpose.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply