Interesting one today:
On one of our production machines, we recently added a new LUN to a SQL cluster. A task like this is a team effort. Sysadmins perform some steps and DBA carry out the remaining. In this article, the main focus is on covering the steps after the LUN is added to the OS & Sql Cluster by the sysadmins. For context, we’ll start with high level steps before going into details.
Sysadmins steps
- Add new storage to the machine/OS as an available storage
- Format the available drive with appropriate settings (cluster size) and add it as a new drive
- Make drive available to the Cluster using “Add Disk” screen in FailOver Cluster Management tool.
DBAs steps
- Add available storage to Sql Cluster
- Configure dependency (and check the report before & after)
- Add data file on the new cluster storage drive
Here we’ll cover the DBA steps in detail:
Some of these steps were covered under a different recent article as part of dealing with an error message, but here we’ll cover it as a task by itself (which it is).
Add New Storage
Once sysadmins have made the new storage as an ‘available storage’ to OS Cluster, it needs to be added as a new storage location to the SQL Cluster.
In FailOver cluster manager, go to Sql Server Resource Group for this SQL Cluster and right click for detailed options and choose “Add Storage” (see image below)
Once successful, go to Storage\Disks under in FailOver Cluster Manager to confirm the availability. See image below:
Configure Dependency
Adding the storage is an important step, and equally important step is adding the new drive to Sql Cluster Dependency Chain. Dependency Chain informs Sql Sever “how to act”, when any resource in the Cluster becomes unavailable. Some resources automatically trigger cluster failover to other node; some resources do not. This decision is made based on the configurations in Dependency Chain.
Example:
Critical: Data drive/LUN that has database files is critical for optimal availability of the Sql Cluster. So, if it becomes unavailable, failing over to other available nodes is imperative to keep the cluster available.
Non-Critical: In some scenarios, Sql Server Agent is not considered as Critical. So if it stops for some reason, Cluster will make multiple attempts to start it on the same node, but may not necessarily cause failover.
This is a business decision. All these “response actions” will be configured in Cluster settings.
Now, check the dependency report (before); We can see that new drive exists in Cluster, but is not yet added to the Dependency Chain.
To Configure Dependency Chain, go to the Sql Server Resource Group under Roles in FailOver Cluster Manager. See the image below for clarity:
Then go to the bottom section for this Resource Group, where all the individual resources that are part of this Resource Group are displayed.
Under “Other Resources“, right click on Sql Server Resource and choose properties.
do As show
In the “Sql Server Properties” window, we can see the existing resources already added to dependency chain logic.
Now, go to the end of the logic list and choose “AND” for condition and pick the new Cluster Storage to be included. See image below for clarity:
After saving the settings, regenerate the Dependency Chain report. Now, we’ll see the new drive as part of the logic.
Add Database Data File to New Cluster Storage
Now, that the new drive is ready, we could easily add a new data file to the new location.
-- -- Add data file to new storage location -- USE [master] GO ALTER DATABASE [SampleDB] ADD FILE ( NAME = N'SampleDB_Data3' , FILENAME = N'U:\MSSQL\Data\SampleDB_Data3.NDF' , SIZE = 3500 GB , FILEGROWTH = 100 GB , MAXSIZE = 3900 GB ) TO FILEGROUP [PRIMARY] GO
_Sqltimes