TempDB Database On a Local Disk for an FCI

, 2017-03-08

We may have performance issues with high tempdb usage even though we have the faster SSDs (Solid State Disks) in a Windows Server Failover Cluster with SQL Server Clustered Instance Installed. This article will be helpful in configuring TempDB on a local Disk instead of shared storage in SQL Server 2012/2014. This will reduce the I/O requests from the shared storage and overall it can improve performance.

Prerequsites

Before we start with the installation, lets assume we have the following things in place. First, we have two Windows Server 2012 Nodes: WINSRVNODE1 and WINSRVNODE2. These nodes are part of a Windows Cluster: WINSRVCLU01.

There is a SQL Server Network Instance Name for the cluster: SQLSRVCLU. To install the SQL Failover Clustered Instance, we are going to use a domain login part of Administrator Group on both the nodes.

Installation

On Node 1( WINSRVNODE1): Starting from the SQL Server Installation Center, the installation would remain same until the Database Engine Configuration (This is the stage where we use Local Disk as directory for tempdb instead of of Shared Storage.

     1. Once we are at the Database Engine Configuration Tab,we specify SAN Disk for Data & Log files but Local Disk for Tempdb Database.

        Note: It is always recommeded to use isolated disks for data file,log  files and tempdb files.

     2. I have my DataFiles on S Drive, Log Files on L Drive (S & L are from SAN) and my Tempdb database on F Drive(F is a Local Disk on Node 1)

Please ignore the warning. This is advice to ensure the same path exists on all the cluster Nodes. Click OK to continue until the installation is successfully completed on Node 1 ( WINSRVNODE1).

On Node 2 (WINSRVNODE2)

  1. Starting with Installation Center as we did on Node 1, we select 'Add Node to a SQL Server Failover Cluster' i.e. SQLSRVCLU and proceed with same steps involving Support Setup Files Installation, Cluster Node Configuration till adding Node is successfully completed.
  2. Now, please create the same folder structure for the tempdb database on the local disk of Node 2 (WINSRVNODE2). If you use a different path, the SQL Instance will fail to come online in case of a failover.

           In my case, it is F:\MSSQL11.SQL01\MSSQL\Data.

This concludes configuring tempdb database on Local Disk on SQL Server 2012/2014 Failover Clustered Instances.

Thank you.

Rate

4 (1)

Share

Share

Rate

4 (1)

Related content

Implementing SQL Server Failover Clustering in Azure

Deploying IaaS solutions in Microsoft Azure offers benefits that leverage agility, resiliency, and scalability built into the underlying platform. However, when dealing with business-critical workloads, customers typically want to also provide high-availability and disaster recovery capabilities in a manner that they can control. Trying to implement this approach in the cloud by following the procedures applicable in on-premises datacenters frequently presents challenges. This article focuses on these differences in the context of deployment of SQL Server Failover Clustering in Azure.

2017-09-29

3,141 reads

Temporary Tables in SQL Server

Temporary tables are used by every DB developer, but they're not likely to be too adventurous with their use, or exploit all their advantages. They can improve your code's performance and maintainability, but can be the source of grief to both developer and DBA if things go wrong and a process grinds away inexorably slowly. We asked Phil Factor for advice, thinking that it would be a simple explanation.

2011-09-22

6,511 reads