TempDB on Amazon Ephemeral drives

  • UncleBoris

    SSCrazy

    Points: 2017

    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

  • EdVassie

    SSC Guru

    Points: 60266

    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

  • lsalih

    SSCertifiable

    Points: 6141

    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'

  • bsherwood

    Grasshopper

    Points: 11

    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)

    • This reply was modified 2 months, 3 weeks ago by  bsherwood.
    • This reply was modified 2 months, 3 weeks ago by  bsherwood.
  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715411

    We run SSC on ephemeral drives (NVMe) but with AGs because if it dies, we need to fail over .

  • lsalih

    SSCertifiable

    Points: 6141

    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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply