Sign in

    Top 9 Tips for Setting Up Your SQL Server Cluster

    By: Spotlight Cloud

    System outages and failures are painful for DBAs, but even more so for customers. Today’s users expect nearly 100 percent availability, and anything less is cause for irritation if you’re lucky and loss of a customer if you’re not.

    One of the DBA’s primary goals is to help ensure SQL Server instances and databases stay online and functioning after a failure or outage. One method to shore up availability is setting up Windows Server Failover Clusters with SQL Server.

    A failover cluster is a group of servers that work together to maintain availability of your applications and services in the event of an outage or failure. Basically, the failover cluster takes all of the data housed on a SQL Server instance and installs it in a shared storage repository—usually on a SAN—that can be accessed from different servers. 

    To help get you started down the path to high availability, we’ve compiled the top nine dos and don’ts for setting up your SQL Server failover cluster so you can minimize database downtime.

    1. Don’t skip cluster validation.

    Before installing a cluster, it is imperative that you run validation to check the configuration. If this is a new cluster, you will want to run all of the tests. 

    Once the cluster is set up and you’ve completely installed and configured your SQL Server instances on the cluster, run validation whenever you make changes. It is important to make sure validation results are correct before pushing your SQL Server failover cluster to live so you won’t have to schedule downtime to fix missed issues.

    2. Do configure quorum well.

    If you want to keep your SQL Server online, be sure you have configured quorum properly in the failover cluster. This Microsoft documentation provides in-depth instructions on how to achieve this, but the highlight reel includes these best practices:

    • Re-evaluate quorum every time your cluster configuration changes
    • Assign a witness to get an odd number of votes
    • Remove votes when appropriate
    • Use the “Dynamic Quorum” feature to dynamically adjust node votes

    It is important to note that the most effective way to configure quorum will vary depending on Windows version, number of nodes, and how reliable network communication is between the nodes,

    3. Don’t select the wrong version of Windows or SQL Server.

    This one kind of sounds like a no-brainer, but it always bears repeating. Make sure you select the most recent version of Windows Server and make sure you’re using the Enterprise or Datacenter version. Also, stick with one version of SQL Server to keep things simple. Adhering to these two practices will make your cluster easier to manage and keep online.

    4. Do buy the correct hardware.

    Right-sizing your hardware for a SQL Server cluster can be tough. For example, you don’t want to waste money on too much memory, but having too little could affect performance.

    As you develop your plan for creating your SQL Server cluster, be sure to confirm your hardware needs are met for the right amount of memory, your network path is redundant, and you have accurately evaluated your SSD needs.

    5. Don’t put too many nodes in one cluster.

    You may be tempted to put all of your nodes in one cluster, but it’s better to stick with one to two nodes per cluster. Remember that any time you apply a patch or an update to a cluster, you’ll need to test that each instance still works on each node. The fewer nodes in a cluster, the less downtime for each instance when you fail it over each node. 

    6. Do plan your nodes and instances.

    Failover clusters are not one-size-fits-all, so you will need to evaluate your needs and plan accordingly. A great place to start is by answering these questions and tailoring your cluster as appropriate:

    • How many cluster nodes do we need?
    • How many SQL Server instances will we install?
    • How many Windows failover clusters fit our needs and budget?
    • What kind of storage will we use?
    • What does our staging environment look like?

    7. Don’t assume your applications will failover gracefully.

    Never trust that your SQL Server instance is running like it was before a failover occurred. Some applications may not automatically come back online afterward, and depending on the application, it could take a while for you to notice. 

    Make it a standard practice to include application testing with every migration to a failover cluster.

    8. Do re-evaluate your SQL Server configuration settings.

    As you start the planning phase of creating SQL Server failover clusters, it’s a good time to take another look at your configuration settings. For example, check that you are using the best settings for things such as memory allocation on multi-instance clusters. 

    9. Don’t slack on your naming convention.

    Take time now to name your cluster components carefully and save yourself a massive headache when you are trying to connect to the server later. Here are a few ideas to help establish an effective naming convention:

    • Make sure the name identifies the type of component you are labeling. Is it a cluster, physical server, SQL Server instance, or Distributed Transaction Coordinator?
    • Install BGINFO to display the server name on the desktop for every server in the cluster. This makes finding the correct databases a breeze. 
    • Be consistent when you add additional nodes or install another SQL Server instance onto the cluster. If you stick with your naming convention, it will not only simplify things for you now but also make finding servers easier for those who need them later.
    New DBA checklist
    June 16, 2020 8:15:00 AM PDT
    Spotlight Cloud

    Written by Spotlight Cloud

    Understand your SQL Server infrastructure health at a glance by monitoring SQL Server's relational engine, Analysis Services, Windows, VMware ESX and SQL Azure with Spotlight.