How to setup a SQL cluster using MS SQL?

rwheeler23
rwheeler23 used Ask the Experts™
on
I have been asked to design and setup a SQL cluster. Before I commit to this project is this something that is extremely difficult and I should bring in a team that is familiar with this process? I have not done this before but have been working with MS SQL since version 6.0. I have setup SQL replication before but I spend the vast majority of my time writing T-SQL scripts and stored procedures. Any tips beyond what I am reading in the MS notes would be appreciated. What versions of MS SQL support clustering?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Paul MacDonaldDirector, Information Systems

Commented:
"...is this something that is extremely difficult..."
No.

"...I should bring in a team that is familiar with this process?"
This depends on your comfort level, but the process isn't difficult:
Build the machines.
Add Microsoft Failover Cluster Services
Install SQL Server
Optionally establish SQL AlwaysOn High Availability

"Any tips beyond what I am reading"
Maybe read up on Failover Clustering and AlwaysOn HA

"What versions of MS SQL support clustering?"
All the modern ones.  The last several versions of SQL Server are "cluster aware" <- maybe read up on what that means.
rwheeler23President

Author

Commented:
I would assume you would need at least two SQL servers to do this? What does clustering get you that SQL replication does not?
Paul MacDonaldDirector, Information Systems

Commented:
"I would assume you would need at least two SQL servers to do this? "
Yes, at least two physical or virtual servers.

"What does clustering get you that SQL replication does not?"
A Failover Cluster can fail over when one node becomes unavailable.   In other words, you're not just duplicating the databases, but creating one, large cluster of servers.  You only ever talk to one real server at a time, but you don't know (or care, usually) which one.  If that server goes down for some reason, Failover Clustering simply grabs another SQL server and puts it in charge.  Downtime is usually minimal and the transition may go completely unnoticed.
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Fractional CTO
Distinguished Expert 2018
Commented:
1) I have been asked to design and setup a SQL cluster.

Key consideration here is design.

Most... wow... how to say this nicely... Most non-technical people (think management in Dilbert cartoons) read something about replicated clusters being better than sliced bread... then think to themselves... wow... that sounds impressive... I can do many presentations to my management about this + maybe fool someone into giving me a raise.

So... big first question is should you even begin this project... relates to required data throughput speed...

There are many considerations. The first of which will be moving from a single instance to a cluster will slow down data access to a crawl, for many reasons.

2) Before I commit to this project is this something that is extremely difficult and I should bring in a team that is familiar with this process?

You're asking the right question.

Tip: Here's how you know you have the right people for the design step. If they treat clustering as a last resort, to solve very specific problems, you have the right person. If clustering is their first solution, you likely have someone who's either incompetent (wow... I'm wound tight today...) or just looking for an infinite billing project or most likely a little of both.

Tip: Clustering is always best for the person billing to maintain the cluster. Almost always a travesty for the site/application owner.

Aside: So far every cluster I've inherited, has moved to single instance + worked far better + is far cheaper to maintain.

3) I have not done this before but have been working with MS SQL since version 6.0. I have setup SQL replication before but I spend the vast majority of my time writing T-SQL scripts and stored procedures. Any tips beyond what I am reading in the MS notes would be appreciated. What versions of MS SQL support clustering?

All MSSQL versions support some form of clustering. Refer to docs for your specific version for correct answers about your version of clustering/replication.

4) I would assume you would need at least two SQL servers to do this?

Correct.

5) What does clustering get you that SQL replication does not?

One way to think about this...

a) Clustering - Multiple instances of a site/application each accessing some database.

b) Replication - All databases being accessed are kept in sync, so all reads return same data (master + slave replication) or all instances can process writes/updates/deletes (master + master replication).

6) Failover (if required), which Paul mentions above, is an entirely different matter, producing an entirely different set of considerations.

7) Big Tip: Once you go down the rabbit hole of clustering or more likely replication (which is what most people really mean when they say clustering)... best have a large budget to maintain your project.
rwheeler23President

Author

Commented:
In this case, we are talking about security for an airport to keep the planes flying and commerce moving. You both have given excellent advice and it is very much appreciated. I will add that the request for clustering came from their IT consultant who is not a database person. I will speak directly with the client next week and find out their true plans.
Paul MacDonaldDirector, Information Systems

Commented:
"to keep the planes flying and commerce moving"
If the SQL Server is critical to this mission, you're completely justified in failover clustering it, depending on your threshold of pain.  If you can afford some downtime and/or lost data, just having reliable backups you can reliably restore from may be an adequate solution.
rwheeler23President

Author

Commented:
Thanks for your tips.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial