SQL Server TempDB, Azure Virtual Machines, and the D: Drive

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) DATALOSS_WARNING_README.TXT:

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?

Very

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 attempt

First, and most obvious thing to try: change the line

SQLTEMPDIR="D:\MSSQL\TempDB"

to

SQLTEMPDIR="D:\"

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

Next attempt

Let’s try without the quotes. Change the SQLTEMPDIR line to:

SQLTEMPDIR=D:\

Results:

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

Third attempt

Try getting a bit cocky here, with the “.” notation:

SQLTEMPDIR=D:\.

Results:

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…

Hang on. I missed something. Didn’t the result of attempt two, where I had

SQLTEMPDIR=D:\

have something else in the error message? “Folder Path=D:” That’s not what I put in. Let’s try one more:

SQLTempDIR="D:\\"

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.
Advertisements
This entry was posted in SQLServerPedia Syndication and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s