Mounting a Volume as a Folder on a Cluster

July 8, 2009 · Posted in SQL Server · Comments 

I have recently had an opportunity to learn some new stuff from a customer so I thought I would share. The scenario is implementing an eight instance SQL Server 2008\Windows 2008 cluster with seven drives per instance. If you are sharp, I bet you are saying, “Wait a minute, there are only 20-something drive letters.”. This is true but did you use your toes to count on? ;) You have to use mount points. See below.

image

So let’s continue with one instance of our example. The seven drives would be provisioned something like this:

  • d:\ – 3GB – Program files
  • d:\system – 10GB – System databases and system object filegroup files for every user DB
  • d:\data – 500GB – all user database filegroups files
  • d:\indexes – 300GB – all user index filegroups files
  • d:\bindata – 2TB – all user blob filegroups files
  • d:\TempDBData – 100GB TempDB data files
  • d:\backups – 3TB

Here are the basic steps you go through:

  • Present the LUNs to the OS
  • Create sector aligned partitions
  • Create the root letter formatted with x64 NT allocation units
  • Format the mount points with x64 NT allocation units
  • Create empty folders named accordingly in the root drive. d: in this case
  • Create the mount points just as you would assign a drive letter. See the image above
  • Install your clustered instance to the root drive with system databases going to d:\system in our case.
  • After the installation, make the SQL Service cluster resource dependant on the mount points.(The mounted folders will automatically be added to the cluster group if you install to the root.)

Now before I end, setting it up like this is really dictated by what is important. Unless you just have uddles of disks, this design put aversion to data loss(assuming there are separate disk groups.) ahead of performance. However, that is another conversation.

Tags: , , , , , , ,

High Level View of a Clustered SQL Server 2008 Setup

July 7, 2009 · Posted in SQL Server · Comments 

In SQL Server 2008, a setup of a clustered instance has changed greatly over SQL Server 2005. In my opinion, it is more complicated and it took me a minute to grasp this so I figured I would blog about it from a high level. They did this to allow for the cluster to be more available during patches and service packs so it is a good thing. This called a rolling upgrade.

For history, this is what happens on SQL Server 2005.

  1. You run setup on one node.
  2. setup spawns another process on each node to copy files and make configurations.
  3. setup creates the clustered instance.

There are several ways to install a SQL cluster. You can create a single node cluster and then add as many nodes as you have. You can also do “Advanced Cluster Preparation” on each node and “Advance Cluster Completion” once to create the clustered instance. See below:

image

Regardless what you do, these actions must be done.

  • Setup must run on each node to install files and make configurations like services and security.
  • Setup must run to create the failover configuration.

Tags: , , ,

Mirroring and Clustering on SQL Server

July 6, 2009 · Posted in SQL Server · Comments 

In the first post, I talked about mirroring vs. clustering for HA purposes. In this post we will talk about combining the two into a monstrosity know as a FrankenCluster. So combining clustering and mirroring fits one specific purpose as far I can imagine. It does it really well though. The scenario is when your application cannot handle the write overhead of high safety(synchronous) mirroring but you want the fast failover that mirroring provides for planned outages like service packs, hardware upgrade and security patches. Another benefit is fact that you have a second copy of the data to project against disk failure or some types of corruption.

Here is the high level setup:

  1. Configure the clustered SQL Server instance.
  2. Configure the mirror instance
  3. Backup the database(s) on the cluster
  4. Backup the log on the cluster
  5. Restore the database with NORECOVERY on the mirror instance
  6. Restore the log with NORECOVERY on the mirror instance
  7. Establish the asynchronous mirroring session
  8. Throw up hi5’s all around like the SQL Server superstar that you are.

This is a basic drawing of what this looks like:

image

Clustering will handle unplanned outages like BSOD’s, hardware failures and service failures. Here is how you handle planned outages:

  1. Set the mirroring session to high safety mode
  2. Verify that the mirror is synchronized
  3. Failover the mirror
  4. Do maintenance
  5. Fail the mirror back
  6. Set the mirroring session back to high performance mode

Note: High performance(asynchronous) mirroring requires Enterprise Edition.

Here are the tsql commands:

–Turn on synchronous mirroring

ALTER DATABASE AdventureWorks SET PARTNER SAFETY FULL;
GO

–Failing over a database

ALTER DATABASE AdventureWorks SET PARTNER FAILOVER;
GO
--Turn on asynchronous
ALTER DATABASE AdventureWorks SET PARTNER SAFETY OFF;
GO

Tags: , , , , , ,

Clustering on Windows Server 2008

February 23, 2008 · Posted in SQL Server · Comments 

There are alot of changes in clustering on Windows 2008. As a matter of fact, you no longer have to have a quorum drive. While there are a lot of changes and improvements, the focus of this release is ease of use. I am going to start here. Let me know if you run across some good resources.

Tags: ,