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.

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

After clicking “New Simple Volume” a wizard will appear.

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

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.

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.

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

Go to Control Panel, Administrative Tools, 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.

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.

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.

Step 2: A Setup Support Rules check will run to identify problems that might occur when you install SQL Server Support Rules. Click OK.Step 3: Install Setup Support Rules.Step 4: Once Setup Support Files install successfully, click Next.Step 5: Enter Product Key and click Next.Step 6: Accept the license terms and click Next.Step 7: On the Features Selection page, select the features to cluster and click Next.Step 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.Step 9: View Disk Space Requirements and click Next.Step 10: Choose a Cluster Resource Group and click Next.Step 11: Choose a Cluster Disk Selection and click Next. (This should’ve been setup by the System Administrator).Step 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.Step 13: Choose a Cluster Security Policy and click Next.Step 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.Step 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.Step 16: Under the Data Directories tab, change the directories to the following and click Next:Step 17: View Error Reporting Options and click Next.Step 18: A Cluster Installation Rules check will run to determine if the failover cluster installation will be blocked. Once successful, click Next.Step 19: Look over the “Summary” page to make sure everything looks ok and click Install.Step 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.Step 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.Step 22: A Setup Support Rules check will run to identify problems that might occur when you install SQL Server Support Rules. Click OK.Step 23: Install Setup Support Rules.Step 24: Once Setup Support Files install successfully, click Next.Step 25: Enter Product Key and click Next.Step 26: Accept the license terms and click Next.Step 27: Select SQL Server Instance name to add a node to. Click Next.Step 28: Specify Service Account passwords and click Next.Step 29: View Error Reporting Options and click Next.Step 30: An Add Node Rules check will run to determine if the add node process will be blocked. Once successful, click Next.Step 31: Look over the “Summary” page to make sure everything looks ok and click Install.Step 32: Once installation is complete, click Close. Repeat steps 21 – 31 for each node.

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.