April 21, 2013 at 5:23 pm
Has anybody used the EC2 Ephemeral drives as a location for their TEMPDB databases?
Normally I use a standard volume but these drives are supposedly faster for TEMPDB -- but are volatile of course which should not be an issue for the TEMPDB as long as it is put in the root of the drive and not a folder (as the folder will not get re-created when the machine restarts).
thanks
April 22, 2013 at 4:24 am
I am interested to hear what your experience is if you do this.
We have decided to always use EC2 volumes, but have not experimented with using the instance storage for tempdb.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 4, 2019 at 6:22 pm
I know this is an old post, but I wanted to share my experience... I highly recommend not to place tempdb files on instance storage in a production environment because you will be relying on a startup script to start the SQL services. Additionally, I noticed that SQL agent service wont start because SQL Agent XPs becomes disabled (at least in my environment). I had to enable it back in my PowerShell script to correct it. I followed AWS documentation and wrote below script to initiate the drive, add tempdb folders, alter tempdb, and start SQL services. Anyone who is a Powershell expert, please feel free to correct my script. Below is the full script I used:
### Powershell Script to store Tempdb on instance storage steps:
Import-Module SQLPS
#################################################
# Detect the Ephemeral drives and stripe them
#################################################
# Be sure to choose a drive letter that will not already be assigned
$DriveLetterToAssign = "Z:"
#################################################
# Given a device (e.g. xvda), strip off
# "xvd" and convert the remainder to the
# appropriate SCSI ID
#################################################
function GetSCSI {
Param([string]$device)
# remove xvd prefix
$deviceSuffix = $device.substring(3)
if ($deviceSuffix.length -eq 1) {
$scsi = (([int][char] $deviceSuffix[0]) - 97)
}
else {
$scsi = (([int][char] $deviceSuffix[0]) - 96) * 26
+ (([int][char] $deviceSuffix[1]) - 97)
}
return $scsi
}
#################################################
# Main
#################################################
# From metadata read the device list and grab only
# the ephemeral volumes
$alldrives = (Invoke-WebRequest -Uri http://169.254.169.254/latest/meta-data/block-device-mapping/).Content
$ephemerals = $alldrives.Split(10) | where-object {$_ -like 'ephemeral*'}
# Build a list of scsi ID's for the ephemeral volumes
$scsiarray = @()
foreach ($ephemeral in $ephemerals) {
$device = (Invoke-WebRequest -Uri http://169.254.169.254/latest/meta-data/block-device-mapping/$ephemeral).Content
$scsi = GetSCSI $device
$scsiarray = $scsiarray + $scsi
}
# Convert the scsi ID's to OS drive numbers and set them up with diskpart
$diskarray = @()
foreach ($scsiid in $scsiarray) {
$disknumber = (Get-WmiObject -Class Win32_DiskDrive | where-object {$_.SCSITargetId -eq $scsiid}).Index
if ($disknumber -ne $null)
{
$diskarray += $disknumber
$dpcommand = "select disk $disknumber
select partition 1
delete partition
convert dynamic
exit"
$dpcommand | diskpart
}
}
# Build the stripe from the diskarray
$diskseries = $diskarray -join ','
if ($diskarray.count -gt 0)
{
if ($diskarray.count -eq 1) {
$type = "simple"
}
else {
$type = "stripe"
}
$dpcommand = "create volume $type disk=$diskseries
format fs=ntfs quick
assign letter=$DriveLetterToAssign
exit"
$dpcommand | diskpart
}
#################################################
# SQL STEPS
#################################################
#STEP1: Create TempDB folders
New-Item -Path Z:\TempDB -ItemType directory -Force
New-Item -Path Z:\TempLOG -ItemType directory -Force
#Step2: Grant SQL Server Service account Full access to the TempDB folders
$path = 'Z:\TempDB', 'Z:\TempLOG'
$user = "AD\SQLServiceAccount"
$Rights = "FullControl"
$InheritSettings = "Containerinherit, ObjectInherit"
$PropogationSettings = "None"
$RuleType = "Allow"
$acl = Get-Acl $path
$perm = $user, $Rights, $InheritSettings, $PropogationSettings, $RuleType
$rule = New-Object -TypeName System.Security.AccessControl.FileSystemAccessRule -ArgumentList $perm
$acl.SetAccessRule($rule)
$acl | Set-Acl -Path $path
#Step3: Start SQL database engine without TempDB files
cd C:\SQLDATA\MSSQL13.AWSSQLTEST\MSSQL\Binn
net start 'MSSQL$AWSTEST' /f
#Step4: Alter TempDB files to point to the new path
Invoke-SQLCMD -ServerInstance AWSDBSQLTST\AWSSQLTEST -Query "USE [master]"
Invoke-SQLCMD -ServerInstance AWSDBSQLTST\AWSSQLTEST -Query "ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'Z:\TempDB\tempdb.mdf');"
Invoke-SQLCMD -ServerInstance AWSDBSQLTST\AWSSQLTEST -Query "ALTER DATABASE tempdb MODIFY FILE (NAME = tempdb_mssql_2, FILENAME = 'Z:\TempDB\tempdb_mssql_2.ndf')"
Invoke-SQLCMD -ServerInstance AWSDBSQLTST\AWSSQLTEST -Query "ALTER DATABASE tempdb MODIFY FILE (NAME = tempdb_mssql_3, FILENAME = 'Z:\TempDB\tempdb_mssql_3.ndf')"
Invoke-SQLCMD -ServerInstance AWSDBSQLTST\AWSSQLTEST -Query "ALTER DATABASE tempdb MODIFY FILE (NAME = tempdb_mssql_4, FILENAME = 'Z:\TempDB\tempdb_mssql_4.ndf')"
Invoke-SQLCMD -ServerInstance AWSDBSQLTST\AWSSQLTEST -Query "ALTER DATABASE tempdb MODIFY FILE (NAME = tempdb_mssql_5, FILENAME = 'Z:\TempDB\tempdb_mssql_5.ndf')"
Invoke-SQLCMD -ServerInstance AWSDBSQLTST\AWSSQLTEST -Query "ALTER DATABASE tempdb MODIFY FILE (NAME = tempdb_mssql_6, FILENAME = 'Z:\TempDB\tempdb_mssql_6.ndf')"
Invoke-SQLCMD -ServerInstance AWSDBSQLTST\AWSSQLTEST -Query "ALTER DATABASE tempdb MODIFY FILE (NAME = tempdb_mssql_7, FILENAME = 'Z:\TempDB\tempdb_mssql_7.ndf')"
Invoke-SQLCMD -ServerInstance AWSDBSQLTST\AWSSQLTEST -Query "ALTER DATABASE tempdb MODIFY FILE (NAME = tempdb_mssql_8, FILENAME = 'Z:\TempDB\tempdb_mssql_8.ndf')"
Invoke-SQLCMD -ServerInstance AWSDBSQLTST\AWSSQLTEST -Query "ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'Z:\TempLOG\templog.ldf');"
#Step5: Restart SQL Database engine and service
net stop 'MSSQL$AWSTEST'
net start 'MSSQL$AWSTEST'
$EnableXPs = "
sp_configure 'show advanced options', 1
reconfigure
GO
sp_configure 'Agent XPs', 1
reconfigure
"
Invoke-SqlCmd -Query $EnableXPs -ServerInstance AWSDBSQLTST\AWSSQLTEST -Database master
net start 'SQLAgent$AWSTEST'
June 28, 2019 at 8:29 pm
Since this conversation is being 'pulled along' / updated....
Here is my experience:
All of my production (customer facing, not engineering dev servers) run on I2 / I3 series instances. They all use the ephemeral drive provided as their TempDB drive. Performance is WAY faster than EBS storage and is free!
Yes, you have to control your startup, and prep the drive via a startup script (controlled in Windows Task Scheduler to run at startup) that makes sure the drive is on the right drive letter you desire (I always use Z:) and if you use a sub folder, then create it for you.
However.. I am also using replication software. So I want it running first (file level block replication) before SQL Server starts. So I am already in control of when SQL Server starts up. I also control when the replication software starts up because I construct a cache layer folder on the ephemeral drive for it.
In the end, the the ephemeral drive is perfect for my TempDB drive needs. I need very high performance, and it must be big. One application using my data can jack that thing to over a terabyte of space used, so I end up on I3.2xlarge instances to keep up.
That is what I do, and it works for me. Don't be afraid of automating your startup sequence. It may feel too complex, but in the end the payoff is of value.
** and.. uh.. no special SQL Server modifications or other post startup changes required. (SQL 2008 & SQL 2016 used this way successfully)
July 1, 2019 at 6:56 pm
We run SSC on ephemeral drives (NVMe) but with AGs because if it dies, we need to fail over .
July 1, 2019 at 8:05 pm
Steve,
Thank you for the update, we ended up not using the instance storage. Our environment is an AG, saved myself some headache 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply