DbSchema Database Designer

DbSchema | SQL Server - How to Setup Failover Clustering?

Publish on DbSchema Blog >>>

SQL Server: How to Set Up Failover Clustering in sqlcmd and DbSchema

SQL Server alt >

Table of Contents

  1. Introduction
  2. Prerequisites
  3. What is Failover Clustering?
  4. Purpose and Usage of Failover Clustering
  5. Advantages and Limitations
  6. Setting Up Failover Clustering in SQL Server
    1. Permissions and Restrictions
    2. Setting Up in sqlcmd
    3. Setting Up in DbSchema
  7. Conclusion
  8. References

1. Introduction

Failover clustering is an important aspect of maintaining a reliable, high-availability database infrastructure. With this technology, SQL Server allows for near-seamless transitions during unplanned outages or planned maintenance. This article provides a comprehensive guide to understanding and setting up failover clustering in SQL Server using both sqlcmd and DbSchema.

2. Prerequisites

Before setting up failover clustering, the following requirements must be met:

  • Hardware: A minimum of two servers (nodes) with the same or compatible versions of Windows Server.
  • Software: SQL Server (Standard or Enterprise edition) installed on each of the nodes.
  • Shared Storage: All nodes in the cluster should have access to shared storage, such as a SAN.
  • Network: Each node must be connected to the network, with a dedicated network for internal cluster communications recommended.

For installation and establishing connection you can read our article SQL Server-How to create a database?

3. What is Failover Clustering?

Failover clustering is a strategy employed in computing environments to ensure continuous uptime and maintain high availability. It involves the use of multiple servers, or nodes, arranged in such a way that if one node fails, workload will automatically transfer to another node. In essence, failover clustering prevents a single point of failure, improving service availability and reliability.

4. Purpose and Usage of Failover Clustering


The primary purpose of failover clustering is to maintain service availability and prevent data loss. In the event of a node failure, the services and applications will failover to another node in the cluster, ensuring minimal downtime.


Failover clustering is commonly used in high-availability environments, such as:

  • Large enterprise databases where downtime could mean significant financial loss
  • Critical applications that need to be constantly available to users
  • Systems with high transactional volumes, where even a small amount of downtime could lead to significant data loss

5. Advantages and Limitations


Failover clustering provides many benefits, including:

  • High Availability: The primary benefit is high availability of services. In the event of a failure, services are immediately moved to another node.
  • Scalability: Clusters can be easily expanded by adding more nodes.
  • Redundancy: Clusters provide redundancy of services, which helps to reduce the risk of unplanned downtime.


However, it also has some limitations:

  • Complexity: Setting up and managing a failover cluster can be complex and requires skilled IT personnel.
  • Cost: The cost of implementing and maintaining a failover cluster can be high due to the need for multiple servers and a shared storage solution.
  • Shared Storage Failure: If the shared storage fails, it can impact all nodes in the cluster.

6. Setting Up Failover Clustering in SQL Server

6.1 Permissions and Restrictions

To set up failover clustering, administrative privileges on all nodes are necessary. The account used for installation should have the Create Computer Objects and Read All Properties Active Directory permissions. Note that there are some restrictions:

  • The SQL Server failover cluster installation is not supported where cluster nodes are domain controllers.
  • SQL Server failover cluster instances (FCIs) cannot be installed to a Read-Only Domain Controller (RODC).

6.2 Setting Up in sqlcmd

Here is a step-by-step guide on setting up failover clustering in sqlcmd:

  1. Launch sqlcmd with administrator privileges.
  2. Connect to the SQL Server instance where you want to create the new failover cluster with the following command:
sqlcmd -S server_name\instance_name -U login_id -P password

Replace <server_name>, <login_id>, and <password> with your specific server details and login credentials.

  1. Create the cluster using the following commands:
FOR DATABASE database_name

Replace ‘group_name’, ‘database_name’, ‘server_name’, ‘instance_name’, ‘domain_name.com’, ‘port’ with your specific details.

This SQL command creates an Availability Group in SQL Server for a specific database, defining the backup preferences, replica settings, failover mode, and availability mode. The settings control how and where backups occur, how failovers are managed, and how connections to the secondary replica are handled.

6.3 Setting Up in DbSchema

DbSchema is a visual database design and management tool that does not directly support setting up failover clustering. However, DbSchema can be used to connect to databases that are part of a failover cluster.

Once the failover cluster is set up and operational, you can use DbSchema to connect to the cluster by specifying the appropriate connection details, such as the virtual IP address or network name associated with the cluster.

Setting up failover clustering in DbSchema involves these steps:

  1. Open DbSchema and connect to your SQL Server.
  2. In the main menu, click DbSchema, then Connect to database.
  3. In the connection dialog, select SQL Server from the list.
  4. In the Failover Server field, enter the name of your failover server.
  5. In the Failover Port field, enter the port of your failover server.
  6. Click Test to verify the connection.
  7. If the test is successful, click Connect.

7. Conclusion

In conclusion, setting up a failover cluster in SQL Server using sqlcmd or DbSchema requires careful planning and configuration. Despite some limitations, it is an essential step for maintaining high availability and preventing data loss. With this guide, you should be able to implement failover clustering effectively in your SQL Server environment.

8. References

  1. Microsoft Failover Clustering. link
  2. Microsoft Always On availability groups: a high-availability and disaster-recovery solution. link
  3. DbSchema Connect to SQL Server Database. link

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.

Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.

Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.

Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.

SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.

Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.

Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.

Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.

DbSchema can be downloaded for free. No registration is required.