Hi Friends,
Here by in this blog I am going to discuss a new feature of SQL Server 2012 cluster with regard to TempDB.
Lot of time we have seen that on a busy SQL Server Instance the Tempdb database possess high IO load. The IO load can be much high than other database if we are using row versioning. As we know TempDB is a shared resource and used by all toher databases so this put more stress on it.
Now in clustered instance of SQL this is requirement that all database files should reside in Shared Storage, this holds true for TempDB as well. Now on a busy SQL environment high IO on a TempDB can lead to slow response due to high burden on System's HBA and storage arrays. So a question arises....
Why should I keep TempDB on a Shared Disk, why not on a Local disk of the node which is part of cluster?
It will be easy as its a local drive and requesnsts need not to traverse via HBA, Storage arrays etc. etc.
Well SQL team has listen to this requst of users and comeup with a solution for that. Starting from SQL 2012, on a cluster instance we can keep TempDB on the local disk of the node.
How to do that?
First below is my disk layout on a Virtual Cluster and location of TempDB files for a SQL 2012 instance.
So I have 4 cluster drives ( E:, F:, X:, Y:). My TempDB files are on Y: drive (above screenshot).
Now lets move the files to local disk of specific cluster node.
In order to move the location of TempDB we will be using the same ALTER DATABASE commands.
Alter database tempdb modify file (name='tempdev',filename='C:\SQLData\tempdb.mdf')
Go
Alter database tempdb modify file (name='templog',filename='C:\SQLData\templog.ldf')
Go
After running above command in the message section we have below message written:
Local directory 'c:\sqldata\tempdb.mdf' is used for tempdb in a clustered server. This directory must exist on each cluster node and SQL Server Service has read/write permission on it.
The file "tempdev" has been modified in the system catalog. The new patch will be used the net time the database is started.
Local directory 'c:\sqldata\templog.ldf' is used for tempdb in a clustered server. This directory must exist on each cluster node and SQL Server Service has read/write permission on it.
The file "templog" has been modified in the system catalog. The new patch will be used the net time the database is started.
Regards
Gurpreet Singh Sethi
Here by in this blog I am going to discuss a new feature of SQL Server 2012 cluster with regard to TempDB.
Lot of time we have seen that on a busy SQL Server Instance the Tempdb database possess high IO load. The IO load can be much high than other database if we are using row versioning. As we know TempDB is a shared resource and used by all toher databases so this put more stress on it.
Now in clustered instance of SQL this is requirement that all database files should reside in Shared Storage, this holds true for TempDB as well. Now on a busy SQL environment high IO on a TempDB can lead to slow response due to high burden on System's HBA and storage arrays. So a question arises....
Why should I keep TempDB on a Shared Disk, why not on a Local disk of the node which is part of cluster?
It will be easy as its a local drive and requesnsts need not to traverse via HBA, Storage arrays etc. etc.
Well SQL team has listen to this requst of users and comeup with a solution for that. Starting from SQL 2012, on a cluster instance we can keep TempDB on the local disk of the node.
How to do that?
First below is my disk layout on a Virtual Cluster and location of TempDB files for a SQL 2012 instance.
So I have 4 cluster drives ( E:, F:, X:, Y:). My TempDB files are on Y: drive (above screenshot).
Now lets move the files to local disk of specific cluster node.
In order to move the location of TempDB we will be using the same ALTER DATABASE commands.
Alter database tempdb modify file (name='tempdev',filename='C:\SQLData\tempdb.mdf')
Go
Alter database tempdb modify file (name='templog',filename='C:\SQLData\templog.ldf')
Go
Local directory 'c:\sqldata\tempdb.mdf' is used for tempdb in a clustered server. This directory must exist on each cluster node and SQL Server Service has read/write permission on it.
The file "tempdev" has been modified in the system catalog. The new patch will be used the net time the database is started.
Local directory 'c:\sqldata\templog.ldf' is used for tempdb in a clustered server. This directory must exist on each cluster node and SQL Server Service has read/write permission on it.
The file "templog" has been modified in the system catalog. The new patch will be used the net time the database is started.
Regards
Gurpreet Singh Sethi
2 comments:
Awesome post
This is such a great resource that you are providing and you give it away for free. I love seeing websites that understand the value of providing a quality resource for free. It?s the old what goes around comes around routine. Did you acquired lots of links and I see lots of trackbacks??
http://www.sqlservermasters.com/
Nice post, very helpfull. Thanks a lot.
Post a Comment