DbSchema | SQL Server - How to Setup Failover Clustering?

SQL Server • 12-Jul-2023

by Malik Umair Sajjad

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

Introduction
Prerequisites
What is Failover Clustering?
Purpose and Usage of Failover Clustering
Advantages and Limitations
Setting Up Failover Clustering in SQL Server
Conclusion
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

Purpose:

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.

Usage:

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

Advantages:

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.

Limitations:

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:

Launch sqlcmd with administrator privileges.
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.

Create the cluster using the following commands:

CREATE AVAILABILITY GROUP group_name
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE database_name
REPLICA ON 'server_name\instance_name' WITH (ENDPOINT_URL = 'TCP://server_name.domain_name.com:port', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));

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:

Open DbSchema and connect to your SQL Server.
In the main menu, click DbSchema, then Connect to database.
In the connection dialog, select SQL Server from the list.
In the Failover Server field, enter the name of your failover server.
In the Failover Port field, enter the port of your failover server.
Click Test to verify the connection.
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

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