October 8, 2007 at 11:25 am
Hi
I am more a programmer that any thing, buy I guess I am right now the person that knows more about SQL Server here, that is why I have to decide which version of SQL Server 2005 we will buy.
We have a database that manage the credit and debit card process and an internal coupon card that work like an debit card. The expert that come to see our process recoment us to change to SQL Server 2005 and use mirror to have another available data base in case of contingency.
Which version is best for us Standar or Enterprise?, I already read
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
But how I told you I am just programmer, I don't have any skills on Administration, ( well I took I microsoft course on Administration like when sql server 2000 appear, but at this time whe don't use SQL Server in any project until like a year ago)
Thanks for your help ( I hope you urderstand my English)
October 8, 2007 at 12:10 pm
The big question is how available does your SQL Server need to be? How much down time is acceptable? These questions go beyond database mirroring. Standard Edition with a witness should support automatic failover, if I remember right. This might help:
Comparison Between SQL Server 2005 Standard and Enterprise Editions
K. Brian Kelley
@kbriankelley
October 8, 2007 at 12:33 pm
We are a chain of supermarket that sale groceries in Mexico, and each day the number of trasaction with credit or debit card is increase, so if we don't have the server we lost money.
If you ask me I will said high disponibility but that cost money and the people that decide how much money spend is a committee.
October 8, 2007 at 12:59 pm
Well, to be honest, it does sound like you do need some consulting on high-availability (is that why the consultants were in house?), if you don't already the expertise in house. Mirroring works, but requires updated clients that are configured to reconnect immediately. It also requires 3 SQL Servers (though one can be an Express version) to get automatic failover. Depending on what your actual business needs are, fail-over clustering, mirroring, or log shipping (or some combination) may be necessary.
K. Brian Kelley
@kbriankelley
October 8, 2007 at 1:29 pm
If your data grows as fast you seem to be implying it will be (supermarkets tend to generate a LOT of transactions), then I'd tend to lean towards Enterprise version. The big improvements have to do with dealing with very large data sets, and the uptime requirements for those. For example - Enterprise edition sports the ability to update indexes without locking whole tables, allows for only restores of single tables (so it doesn't lock the entire database), allows for mostly automatic table partitions (very large tables broken into pieces so as to leverage faster processing on the set), all of which become big factors on very large databases.
As to mirroring vs partitioning - they're really very different animals, and different solutions require different things. Let's run down a few:
- Database mirroring is essentially an advanced version of log shipping. In essence - an activity happens on the primary server, and the transaction log of the changes being made are sent to be independently processed by the secondary server.
- Database mirroring is set up at the database level. Because of that - the two servers have to be licensed separately.
- because the server are completely "independent", there is a lot more leeway in terms of what hardware may be used. Of course, if you wish to use the automatic failover part of DB mirroring, the mirror needs to be able to keep up with the principal (the principal will send one transaction, wait for confirmation that it was hardened, then send the next, etc...) or it will introduce processing delays.
- Database mirroring requires processing to happen TWICE for the two nodes to be in synch. That means that each server needs to have its own disk space, its own controllers, and all of the activity happens twice (once on the main server, once on the mirror). That means that they shouldn't share disk resources (like - they shouldn't be on the same SAN). It also starts to get cumbersome when dealing with multiple databases.
- In order to achieve automatic failover - there needs to be a THIRD machine which acts as a Witness/Quorum server, also with its own version of SQL Server. Its sole mission in life is to keep an eye on the other two and "decide" which one should be the primary and which should be the mirror.
- Your client code would need to be using either ADO.NET or SQL Native Client to connect, AND those connections strings need to be specifically written to allow for redirection on failure.
- There are a few circumstances where you might lose data (such as setting it up to be asynch communications), or where a partial network outage could prevent the DB from being served up (if the mirror stays online, but both the principal and the witness "drop off" - no one will serve the DB).
- Clustering - usually requires top of the line equipment, so hardware tends to be EXPENSIVE.
- most Cluster configs these days seem to be active/passive (or at least start that way). The two servers (per microsoft) "need" to be the same hardware (it's not true, but you'll catch hell from MS support if you don't keep them reasonably close).
- You only need to license SQL once per active/passive combination (or once per active node in a n/m config).
- you will more likely than not need to invest in a SAN, since the cluster will need to share the drive resources.
- in a cluster - transactions happen once (since the disks are shared).
- clustering happens at the instance level, so it has no issues with multiple DB's.
- in the case of a failure, the SQL server will take some time on startup to double-check the consistency of the databases, and possibly undo/redo anything not committed yet. As a result - it will take longer for a typical cluster instance to fail over than it does for a mirror to recover. It usually will not lose data however.
Finally - DB mirroring is "brand new" (it's only supported in production environments since SP1), while clustering has been around since 2000. Choose wisely - your mileage may vary.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 8, 2007 at 2:03 pm
One advantage mirroring has over clustering is it does not require a 3rd party solution to do a geographically dispersed high availability solution (having the data in more than one physical place with immediate fail-over).
K. Brian Kelley
@kbriankelley
October 8, 2007 at 3:05 pm
Correct - missed that one. Since the disks are local in the case of mirroring, you wouldn't run into latency issues accessing SQL data on shared disks over remote tunnels. However you might still have some issues since the witness has to live on one side or the other.
Of course - I'm not so sure I'd run that in Synchronous mode (given the distance and possible responsiveness issues on shipping the logs).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 8, 2007 at 5:24 pm
Be sure that you consider your application. Automatic failover with mirroring requires you to use the new SQL Native Client, not older OLEDB/ADO technologies. This can be an issue.
If you really need HA, I'd recommend a cluster locally and then mirroring or log shipping offsite to protect you.
Enterprise is likely what you want, but you need to get all the requirements down to decide.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 10, 2007 at 5:28 pm
Thanks for all your help that gave me a clue, about what I need to considerate.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply