SQL Server Clustering Management inside the command line

There are numerous commands you can run to manage a SQL Cluster from the command line. Below I’ll show you a few of these that include status checks and how to failover.

How to View SQL Clusters

This can be run from your workstation to view all SQL Clusters on the network you are connected to.

cluster /list

SQL Freelancer SQL Server Failover Cluster Command Prompt

View Status of Nodes

You can run this to view the status of all nodes in the cluster.

cluster node 
--or 
cluster node /status

SQL Freelancer SQL Server Failover Cluster Command Prompt

View Status of Cluster Groups

You can run this to view the status for all cluster resource groups.

cluster group 
--or 
cluster group /status

SQL Freelancer SQL Server Failover Cluster Command Prompt

View Status of Cluster Networks

You can run this to view the cluster networks status.

cluster network 
--or 
cluster network /status

SQL Freelancer SQL Server Failover Cluster Command Prompt
Click here to view the rest of this post.

Adding Storage to SQL Server 2008 Cluster

Adding a new storage device using Windows 2008 Failover Clustering has been simplified enormously since the Windows 2003 and older clustering technology. In this post, I’ll show you how to add an extra storage device to your configuration. First thing you will need to do is get your SAN administrator to present a new disk to the cluster. Once the new disk is presented, go to Disk Management, and you should see the new disk unallocated. If the disk displays “Offline” simply right click and choose Online.

SQL Freelancer SQL Server Cluster Storage
Next, right click on the disk and choose “New Simple Volume”. Clustering does not support any of the other volume options.

SQL Freelancer SQL Server Cluster Storage
After clicking “New Simple Volume” a wizard will appear.

SimpleVolumeWizard

Click Next and you will see the following page. Choose your volume size and click Next.

VolumeSize

Choose a drive letter, create a mount point on an existing disk or do not assign a drive letter. In this example, I’ll give the disk a drive letter of F. Click Next.

SQL Freelancer SQL Server Cluster Storage
On this screen, you will need to format the volume using NTFS and you can rename the volume to better identify it later on. I will also perform a quick format. Click Next and Finish to format.

SQL Freelancer SQL Server Cluster Storage
The disk should now be online and allocated.

SQL Freelancer SQL Server Cluster Storage
Click here to view the rest of this post.

Managing a Windows and SQL Server Cluster using the Failover Cluster Manager tool

Installing a cluster is just the beginning of a DBA’s administrative duties. In this post, I’ll show you a few management tasks that may help the novice. Most tasks will be carried out using Failover Cluster Management which is the management console built into Windows Server 2008. You can access the Failover Cluster Management in a couple of different ways:

  • Go to Server Manager, Features, Failover Cluster Manager

SQL Freelancer SQL Server Cluster Failover Cluster Manager

  • Go to Control Panel, Administrative Tools, Failover Cluster ManagerSQL Freelancer SQL Server Cluster Failover Cluster Manager

Once you open Failover Cluster Manager let’s see what can be done inside of this console.

View Status of Services and Applications

Inside of Failover Cluster Manager, if you click on Services and Applications you are presented with a wealth of information.

In this example, I have a multiple instance cluster that consists of four instances on a two node active/passive cluster. After clicking Services and applications, you can see in the right pane all the instances, status, type, current owner (or current node that they exist on) and if they are set to auto start. In the right pane, if you click on a specific instance you can see more information regarding this instance at the bottom.

SQL Freelancer SQL Server Cluster Failover Cluster Manager
Likewise, if you drilldown from Services and applications and click on an instance name you will see even more information regarding this instance that includes server name, IP address, disk information, and the services that are clustered.

SQL Freelancer SQL Server Cluster Failover Cluster Manager
Click here to view the rest of this post.

SQL Server 2008 Cluster Installation

Here’s a quick and dirty look at how to install a two node Active/Passive SQL 2008 cluster on Windows Server 2008.

Environment: Two servers (nodes) running Windows 2008 R2 Enterprise Edition and SQL Server 2008 R2 Enterprise named TSTPSSQLCL03 and TSTPSSQLCL04. I’m creating a named instance named TSTECSSQLv01\ECS. The Systems Administrator has already set up the Cluster Name (TSTPSSQLCLv03), storage, and given me the following IP address to configure my named instance: 10.101.1.69.

Step 1: On the first node (TSTPSSQLCL03) insert installation media and proceed to the SQL Server Installation Center, Installation, click on New SQL failover cluster installation.

SQL Freelancer SQL Server Cluster InstallationStep 2: A Setup Support Rules check will run to identify problems that might occur when you install SQL Server Support Rules. Click OK.
SQL Freelancer SQL Server Cluster InstallationStep 3: Install Setup Support Rules.
SQL Freelancer SQL Server Cluster InstallationStep 4: Once Setup Support Files install successfully, click Next.
SQL Freelancer SQL Server Cluster InstallationStep 5: Enter Product Key and click Next.
SQL Freelancer SQL Server Cluster InstallationStep 6: Accept the license terms and click Next.
SQL Freelancer SQL Server Cluster InstallationStep 7: On the Features Selection page, select the features to cluster and click Next.
SQL Freelancer SQL Server Cluster InstallationStep 8: On the Instance Configuration page, specify a SQL Server Network Name and choose whether this installation is a default instance or named instance. You can also change the Instance root directory. Click Next.
SQL Freelancer SQL Server Cluster InstallationStep 9: View Disk Space Requirements and click Next.
SQL Freelancer SQL Server Cluster InstallationStep 10: Choose a Cluster Resource Group and click Next.
SQL Freelancer SQL Server Cluster InstallationStep 11: Choose a Cluster Disk Selection and click Next. (This should’ve been setup by the System Administrator).
SQL Freelancer SQL Server Cluster InstallationStep 12: On the Cluster Network Configuration page, it’s better to remove DHCP and specify a static IP address. (The System Administrator should be able to give you the IP address) Once specified, click Next.
SQL Freelancer SQL Server Cluster InstallationStep 13: Choose a Cluster Security Policy and click Next.
SQL Freelancer SQL Server Cluster InstallationStep 14: Enter service accounts and password for the DB Engine and SQL Agent. You can also use the “Use the same accounts for all SQL Services” button if you want to specify the same account for both services. Click Next.
SQL Freelancer SQL Server Cluster InstallationStep 15: On the Database Engine Configuration page, select Mixed Mode and enter a secure ‘sa’ password. Under Specify SQL Server administrators, enter all users that will need sysadmin permissions. Click the Data Directories tab.
SQL Freelancer SQL Server Cluster InstallationStep 16: Under the Data Directories tab, change the directories to the following and click Next:
SQL Freelancer SQL Server Cluster InstallationStep 17: View Error Reporting Options and click Next.
SQL Freelancer SQL Server Cluster InstallationStep 18: A Cluster Installation Rules check will run to determine if the failover cluster installation will be blocked. Once successful, click Next.
SQL Freelancer SQL Server Cluster InstallationStep 19: Look over the “Summary” page to make sure everything looks ok and click Install.
SQL Freelancer SQL Server Cluster InstallationStep 20: Once the install is complete, click Close. To verify an instance has been created, open Failover Cluster Manager (Administrative Tools) and drilldown under Cluster name.
SQL Freelancer SQL Server Cluster InstallationStep 21: Halfway there.  On the second node (TSTPSSQLCL04) insert installation media and proceed to the SQL Server Installation Center, Installation, click on Add node to a SQL Server failover cluster.
SQL Freelancer SQL Server Cluster InstallationStep 22: A Setup Support Rules check will run to identify problems that might occur when you install SQL Server Support Rules. Click OK.
SQL Freelancer SQL Server Cluster InstallationStep 23: Install Setup Support Rules.
SQL Freelancer SQL Server Cluster InstallationStep 24: Once Setup Support Files install successfully, click Next.
SQL Freelancer SQL Server Cluster InstallationStep 25: Enter Product Key and click Next.
SQL Freelancer SQL Server Cluster InstallationStep 26: Accept the license terms and click Next.
SQL Freelancer SQL Server Cluster InstallationStep 27: Select SQL Server Instance name to add a node to. Click Next.
SQL Freelancer SQL Server Cluster InstallationStep 28: Specify Service Account passwords and click Next.
SQL Freelancer SQL Server Cluster InstallationStep 29: View Error Reporting Options and click Next.
SQL Freelancer SQL Server Cluster InstallationStep 30: An Add Node Rules check will run to determine if the add node process will be blocked. Once successful, click Next.
SQL Freelancer SQL Server Cluster InstallationStep 31: Look over the “Summary” page to make sure everything looks ok and click Install.
SQL Freelancer SQL Server Cluster InstallationStep 32: Once installation is complete, click Close. Repeat steps 21 – 31 for each node.


 

SQL Server High Availability Options

Finding the right high availability option can be tricky.  The decision really depends on these items:

  • Needs
  • Budget
  • Scope
  • SQL Server version
  • Level of automation
  • Team level support
  • etc.

At a high level, there are five main high availability options including a new feature set to be release with SQL Server 2012:

  • Replication
  • Mirroring
  • Log Shipping
  • Clustering
  • AlwaysON

SQL Server Replication Overview

At a high level, replication involves a publisher and subscriber, where the publisher is the primary server and the subscriber is the target server. Replication’s main purpose is to copy and distribute data from one database to another. There are four types of replication that we will outline:

  • Snapshot replication
  • Transactional replication
  • Merge replication
  • Peer to Peer replication

Snapshot: Snapshot replication occurs when a snapshot is taken of the entire database and that snapshot is copied over to the subscriber. This is best used for data that has minimal changes and is used as an initial data set in some circumstances to start subsequent replication processes.

Transactional: Transactional replication begins with a snapshot of the primary database that is applied to the subscriber. Once the snapshot is in place all transactions that occur on the publisher will be propagated to the subscriber. This option provides the lowest latency.

Merge: Merge replication begins with a snapshot of the primary database that is applied to the subscriber. Changes made at the publisher and subscriber are tracked while offline. Once the publisher and subscriber are back online simultaneously, the subscriber synchronizes with the publisher and vice versa. This option could be best for employees with laptops that leave the office and need to sync their data when they are back in the office.

Peer to Peer: Peer to Peer replication can help scale out an application.  This is because as transactions occur they are executed on all of the nodes involved in replication in order to keep the data in sync in near real time.

Pros and Cons for SQL Server Replication
Pros
Cons
Can replicate to multiple servers
Manual failover
Can access all databases being replicated
Snapshot can be time consuming if you have a VLDB
Replication can occur in both directions
Data can get out of sync and will need to re-sync

 


SQL Server Database Mirroring Overview

Database Mirroring involves a principal server that includes the principal database and a mirror server that includes the mirrored database. The mirror database is restored from the principal with no recovery leaving the database inaccessible to the end users. Once mirroring is enabled, all new transactions from the principal will be copied to the mirror. The use of a witness server is also an option when using the high safety with automatic failover option. The witness server will enable the mirror server to act as a hot standby server. Failover with this option usually only takes seconds to complete. If the principal server was to go down the mirror server would automatically become the principal.

Click here to view the rest of this post.