Why put the TempDB on the D: drive?
In the wonderful world of Azure Virtual Machines, for certain VM types, the D: drive is hosted on SSDs – great for performance. However, in this world, the D: drive is “non-persistent storage”, meaning that there’s no guarantee that what you put there will still be there after you reboot the server. Heck, MS even leaves a little note on D:\ to that effect in the file called (another hint)
WARNING : THIS IS A TEMPORARY DISK.
Any data stored on this drive is SUBJECT TO LOSS and THERE IS NO WAY TO RECOVER IT.
Please do not use this disk for storing any personal or application data.
For additional details please refer to the MSDN documentation at : http://msdn.microsoft.com/en-us/library/windowsazure/jj672979.aspx
Sounds ideal for SQL Server’s TempDB. Indeed, there’s a recommendation to do this in the Azure documentation.
However, this non-persistent nature of the Azure Virtual Machine’s D: drive causes a problem for SQL Server, as for some reason, it can’t cope with building the path to the TempDB files on boot. It’s quite happy rebuilding the TempDB files themselves, but it relies on the folder structure being built already.
Obvious question: why not put the TempDB (and the SSAS equivalent) in the root of D:? In the immortal words of the ancient philosopher Clarksonius, how hard can it be?
We’re using a scripted SQL server installation, so it’s easily and quickly repeatable, and we have control over the configuration script being used to build SQL Server without having to go through the individual steps.
In these attempts, where I suggest a change to the “SQLTEMPDIR” setting, you might also want to change the “ASTEMPDIR” setting, if you’re also doing an SSAS installation.
First, and most obvious thing to try: change the line
This bombs out quite quickly with this message in the summary report:
Overall summary: Final result: Failed: see details below Exit code (Decimal): -2061893624 Exit facility code: 1306 Exit error code: 8 Exit message: The input setting "SQLTEMPDBDIR" has invalid character " " ". Retry setup with valid value for "SQLTEMPDBDIR". Start time: 2015-07-31 08:15:44 End time: 2015-07-31 08:18:30 Requested action: Install Exception help link: http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=12.0.4100.1&EvtType=0x5225C3D0%400x4BDAF9BA%401306%408&EvtType=0x5225C3D0%400x4BDAF9BA%401306%408
Let’s try without the quotes. Change the SQLTEMPDIR line to:
Overall summary: Final result: Failed: see details below Exit code (Decimal): -2068578304 Exit facility code: 1204 Exit error code: 0 Exit message: The folder path specified is invalid. Please enter a valid folder path.. Folder Path=D:" Start time: 2015-07-31 08:23:40 End time: 2015-07-31 08:24:37 Requested action: Install
Try getting a bit cocky here, with the “.” notation:
Overall summary: Final result: Failed: see details below Exit code (Decimal): -2068578304 Exit facility code: 1204 Exit error code: 0 Exit message: The folder path specified is invalid. Please enter a valid folder path.. Folder Path=D:\. Start time: 2015-07-31 08:31:04 End time: 2015-07-31 08:31:54 Requested action: Install
Hang on. I missed something. Didn’t the result of attempt two, where I had
have something else in the error message? “Folder Path=D:” That’s not what I put in. Let’s try one more:
Oh, this looks hopeful. It’s not bombing out so quickly. Time for a coffee?
Some time later… Fail. Multiple occurrences of the following set of error messages:
Feature: Database Engine Services Status: Failed: see logs for details Reason for failure: An error occurred during the setup process of the feature. Next Step: Use the following information to resolve the error, uninstall this feature, and then run the setup process again. Component name: SQL Server Database Engine Services Instance Features Component error code: 0x84CF0004 Error description: While updating permission setting for folder 'D:\' the permission setting update failed for file 'D:\pagefile.sys'. The folder permission setting were supposed to be set to 'D:P(A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)(A;OICI;FA;;;S-1-5-80-3880718306-3832830129-1677859214-2598158968-1052248003)'. Error help link: http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=12.0.4100.1&EvtType=0xB11EAE11%400xE9BC3D64&EvtType=0xB11EAE11%400xE9BC3D64
For the following features:
- Feature: Reporting Services – Native
- Feature: Database Engine Services
- Feature: Data Quality Services
- Feature: Full-Text and Semantic Extractions for Search
- Feature: SQL Server Replication
To put it another way, the SQL Server process tries to take control of the folder that contains TempDB, and, in this case, fails miserably.
And in the end
What I ended up doing was, as recommended in this post, creating a Scheduled Task to run a PowerShell script at boot time that rebuilds the folder structure I wanted for TempDB, SSASTempDB, etc, and then starts the SQL Server services.
What have we learned?
- SQL Server really should be clever enough to rebuild the path to TempDB – after all, that’s probably the easiest part of rebuilding TempDB
- TempDB probably can’t be in the root directory
- We need another plan
- There’s only so many build failures I can take in a morning
- I really needed to be a bit cannier about when to Google, as I found that Scheduled Task recommendation about an hour after starting to do that.