Recommended practice to deal with TempDB , TempLog and Backup location LUN or local or share with database and Log location for instance?

Question

Hi Team,

I need some input with the recommended practice to place 'TempDB' and 'TempLog' while installing SQL instances ?

our plan to have LUN presented to SQL Server and have Windows failover cluster with shared storage.

While installing multiple SQL instances on cluster, what is the best practices to place 'TempDB' , TempLog' and Backup?

Instance Database and Log will be placed on separate LUNs while installing SQL Instance.

Is it possible to have all instance TempDB, TempLog and Backup on same location instead of shared LUN? Not sure if we give the same path for TempDb, TempLog and backup for each SQL instance, will that overwrite the previous installed Instance or will have its own subfolder and then store these files keeping other instance files intact?

Any inputs will be appreciated. Thanks

Regards,

Answer 1

Is it possible to have all instance TempDB, TempLog and Backup on same location instead of shared LUN? Not sure if we give the same path for TempDb, TempLog and backup for each SQL instance, will that overwrite the previous installed Instance or will have its own subfolder and then store these files keeping other instance files intact?

If you are installing multiple instance on cluster you would need separate storage for each cluster. For example if you have data, log and tempdb file for both instances and you want to put data file log file and tempdb on different drives then you would require 3 drives, added as shared storage for instance 1 and 3 drives added as shared storage for instance 2 so total of 6 drives.

Regarding best practice for placing files if you can place data , log and tempdb all on different physical drives then it would be great and above that if you can afford separate drive for backup it would be even great

---

Cheers,

Shashank

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My TechNet Wiki Articles
MVP

Answer 2

There are certain ways to do it, and if you work along with your storage team and using mount points then you can create a common folder for all teampdb data, tempdb log, and one common folder for backup for all instances like:

TempDB

 Data 

   Instance1TempDB

   Instance2TempDB

   Instance3TempDB

   .....

  Log

   Instance1TempDB

   Instance2TempDB

   Instance3TempDB

   .....

Backup

 Instance1

 Instance2

 Instance3

-----

When actually these data, log and backup would be on separate luns at backend so that either individual or cluster wouldn't fight with each other for their belongings..) and finally won't allow you to configure a common disk for them.

So, if you plan properly, you can get it done someway, I too like such planning and keeping centralize solution with look and feel while keeping things individually intact.

All the best!! 

Otherwise Shanky has put across correct pointers on data, log and backup folders are required separately for instances if cluster, and for individuals standalone instances, it will mix all the dbs in the same folder, so it will become tough to identify our databases for different instances, so that is way, we try to keep those dbs on different folders and also for performance better means on separate luns and keep good number of disks at backend to get better IOPS as per app designer requirement. 

After setting up tempdb, please setup DBCC traceon(-1, 1117) and DBCC traceon(-1, 1118) on each instances or be in better ways, setup these on startup too.

Also keeping tempdb data files as per processors we have.

Some good link to check:

https://support.microsoft.com/en-us/kb/2154845

https://www.brentozar.com/sql/tempdb-performance-and-configuration/

Some good discussions and please check with version you need help on too.

https://technet.microsoft.com/en-us/library/ms175527%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

However it can give you some idea.

---

Santosh Singh

Answer 3

I forgot to add that starting from SQL Server 2012 onward you can have Tempdb on local disk

---

Cheers,

Shashank

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My TechNet Wiki Articles
MVP

Answer 4

Hi Shashank\Santosh\Shanky (SSS :-)

Thanks guys for great input. If i understand it correctly having these TempDB, TempLog and Backup on different LUN is good . Since Tempdb, Templogs can be configured on local disk, would it be suggested to have it on local disk or still on separate LUNs when its come to Clustering? If these DBs are placed on local disk and not on shared disk, it will be an issue if there is a failover to other node.

Any suggestions.

Regards

Answer 5

Hi Shashank\Santosh\Shanky (SSS :-)

Thanks guys for great input. If i understand it correctly having these TempDB, TempLog and Backup on different LUN is good . Since Tempdb, Templogs can be configured on local disk, would it be suggested to have it on local disk or still on separate LUNs when its come to Clustering? If these DBs are placed on local disk and not on shared disk, it will be an issue if there is a failover to other node.

Any suggestions.

Regards

That depends on what constitutes local drive , if SSD or fusion I/O cards are used then yes it would be fast. The fact that it was introduced doesn't makes it bad choice all u need to do is teat and test

---

Cheers,

Shashank

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My TechNet Wiki Articles
MVP

Answer 6

Hi ,

SQL will be running on VMware as Guest VM. so local harddisk will be vdisk.

Answer 7

Hi MS Expert 2010,

The speed of disk in VMware also depends on the speed of the disk in the machine where you setup the VMware.

You can tell us the hardware of the machine where you setup VMware.

Regards,

Teige