CODEWITHSUNDEEP
azure-sql-databasetempdb
Andrea
Andrea

Reputation: 1

Sql server Tempdb on temporary disk

I read this article https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-sql-performance/

that explain best practices to apply for improve performance on SQL server in Azure, I've a DS_v2 virtual machine with temporary disk SSD (then all data is wipe every reboot) , someone can says me if could be any problem storing tempdb on this temporary partition? some pros and cons of this practice?

thanks! Andre

Upvotes: 0

Views: 1701

Answers (3)

Skaue
Skaue

Reputation: 783

Please refer to this article where it explains how you can move the TempDB to the Temp Disk AND also make sure SQL is not stopped by permission issues or other errors:

https://blogs.technet.microsoft.com/dataplatforminsider/2014/09/25/using-ssds-in-azure-vms-to-store-sql-server-tempdb-and-buffer-pool-extensions/

Upvotes: 1

Andrea
Andrea

Reputation: 1

I've tried to move tempdb on temporary disk but I could not did it because give me permission errors, I cannot create a folder or change permission on partition because this partition will reset every time. So how is possibile use this partition without having any problem on SQL?

Upvotes: 0

alroc
alroc

Reputation: 28194

From the link you provided:

For D-series, Dv2-series, and G-series VMs, the temporary drive on these VMs is SSD-based. If your workload makes heavy use of TempDB (e.g. for temporary objects or complex joins), storing TempDB on the D drive could result in higher TempDB throughput and lower TempDB latency.

If Microsoft is suggesting this configuration in a "best practices" document, it's a safe bet that you will be OK in doing it. As always though, try it on a test instance first.

Upvotes: 3

Related Questions