How to move Micrsoft SQL Server tempdb to instance/ephemeral disks on Amazon EC2

IFM supplies engineering services for $NZ200+GST per hour. If you require assistance with designing or engineering an Amazon solution - hire us!

Ephemeral or instance disks are temporary disks because they are local to the physical node your virtual machine is running on. If your virtual machine stops running and then starts on another node you loose access to the existing local disks, and hence what you could see before is gone. SQL Server re-creates tempdb on every boot, so this is not a problem. Because ephemeral disks are local to the physical host your virtual machine is running on they are fast. Also you don't get charged for any I/O generated on these disks.

So what is the down side? The EC2Config service preps the drives on "cold" boot and as a result the ephemeral storage may not be ready to run before SQL server starts. If SQL server can't create tempdb because the disk is not available it stops dead. The next problem is newly created drives don't have the necessary permissions for SQL server to be able to create tempdb, which will once again stop the server dead.

The solution? I have pieced toegther various scripts from Google and added some bits of my own. What the below script does is scan the system for all ephemeral drives, and then creates a striped disk array from them. This new array is formatted and called k: drive. Then everyone is granted full access, and finally SQL server is restarted. This script is then scheduled to run everytime the machine boots.

How do I set it up?

• Download stripe_disks_sql.ps1 and save it to a local directory. I put mine in a directory called c:\scripts.
• Schedule a basic task in task scheduler to run on system start. It should run the command powershell.exe and have the agruments -file c:\scripts\stripe_disks_sql.ps1. Make it runs as an Administrator with the highest privileges.
• Configure powershell to run unsigned scripts by starting powershell and running "Set-ExecutionPolicy unrestricted".
• You should be able to run the task now, and shortly afterwards you should see a k: drive appear.
• I set the services MSSQLSERVER and SQLSERVERAGENT to manual start. The script will start them once the drive has been created on boot.
• Execute this SQL server script to move the tempdb files.
  USE master;
  GO
  ALTER DATABASE tempdb
  MODIFY FILE (NAME = tempdev, FILENAME = 'K:\tempdb.mdf');
  GO
  ALTER DATABASE tempdb
  MODIFY FILE (NAME = templog, FILENAME = 'K:\templog.ldf');
  GO
  
• Start the EC2ConfigServiceSettings tool. On the "Storage" tab untick "Initialize and format all the unitialized disks ...". The script will be doing it from now on.
• You should now be able to restart the server, and on reboot you'll have a k: drive and tempdb files will be located on it.