Long-winded thoughts on Migrating TempDB

One of the problems of having a developer background is that I tend to think of things in terms of distinct operations. Most of the time that can be an advantage as it allows me to easily break up bigger tasks into smaller ‘bite-size’ units of work that I can then work on executing. At other times it means that I can needlessly fear perceived complexity that just isn’t there.

Migrating TempDB – Worrying about the Permissions

I also get a ‘double-whammy’ in the sense that my DBA background tends to make me hyper-cautious and defensive when undertaking any kind of operation that might have the potential to end badly.

Migrating tempdb

Consequently, when I recently helped a client migrate tempdb on one of their servers, I started thinking about the discrete operation that need to take place in order to move the file locations of the files that make up tempdb.

To migrate tempdb, you start by merely instructing SQL Server to change file locations for any files you wish to move. Syntax for this is pretty simple and looks similar to the following:

USE masterGOALTER DATABASE tempdb MODIFY FILE (NAME = tempdev,FILENAME = N'D:SQLTemptempdb.mdf')GOALTER DATABASE tempdb MODIFY FILE (NAME = templog,FILENAME = N'D:SQLTemptempdblog.ldf')GO

Under the covers, this merely tells SQL Server that the path for these files has changed – meaning that the next time that SQL Server starts it will go ahead and recreate the tempdb at the locations specified. (Which is what it does EVERY time SQL Server starts; it takes a copy of the model database, and ‘spams’ it into the file locations specified – which, in turn, are stored as data in the master database.)

Consequently, the only real issue to migrating tempdb is that it won’t happen until SQL Server is stopped and restarted – AND it means that if you want to clean up the old files that were used previously, you’ll have to keep tabs on where they were and run in and delete them AFTER SQL Server restarts and uses the new paths.

Otherwise, the process is pretty straightforward.

Imagining a Worst-Case Scenario

Only, even though I’ve done this a number of times in the past, this last time I did it my mind conspired against me.

First my discrete-operation-focused developer-mind made me work through the process of what’s going on behind the scenes – at which point my overly-cautious dba-mind wondered: “what if the new path for these files was to a folder where the SQL Server service-account didn’t have permissions? Would SQL Server try to start up and then bump into ACL problems when trying to create tempdb only to fail miserably during the startup process?”

For my developer mind, this sure seemed feasible. (Everything you do when it comes to development feels like you’re constantly fighting permissions and security.) And for my hyper-cautious DBA-mind, this also seemed like it would or could be possible – because it would certainly be a nightmare scenario (and being a DBA is all about trying to avoid potential nightmare scenarios by thinking things through before pulling the trigger).

Putting My Fears to Rest

So, during the process of helping my client migrate tempdb I went ahead and made sure to explicitly grant the SQL Server service-account access to the new locations where tempdb would be spun-up on some new disk. But I made a mental note to go in and test out what would happen if I didn’t do that.

And, today in my lab I took a few minutes to snapshot my VMs and run a few tests.

And it turns out that all of that paranoia and worry about potential complexity was completely unwarranted, because when I went ahead and used T-SQL to run some ALTER DATABASE scripts against each of the files in my tempdb, then restarted SQL Server, it didn’t have any problems starting up at all – because SQL Server 2008 R2 was smart enough to go ahead and inject the following into the mix:

So, long story short: SQL Server (at least SQL Server 2008 R2) has been well designed to avoid a ‘perfect storm’ scenario where you might go and change file locations WITHOUT explicitly granting permissions.

That said, if you go ahead and grant explicit permissions, you can grant MODIFY instead of FULL (which is overkill in my mind) and have a tiny degree of increased security if you go with the more explicit route.