SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

TempDB Database On a Local Disk for an FCI

By Sadan Kumar Budde,

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.

 
Total article views: 1128 | Views in the last 30 days: 4
 
Related Articles
FORUM

SQL 2005 Cluster Install

Failover cluster sql 2005 install

FORUM

How Many TempDB Files Can be added to SQL Server 2008 with 32 CPU's

Tempdb's for SQL Server 2008 Clustered Server

FORUM

Do you need two nodes when installing a cluster?

Cluster Installation

FORUM

ERROR: INSTALL SQL SERVER 2000 on Cluster...

ERROR: INSTALL SQL SERVER 2000 on Cluster...

FORUM

SQL server SP3 installation on Cluster nodes

SQL server SP3 installation on Cluster nodes

Tags
cluster    
failover    
localdisk    
tempdb    
 
Contribute