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: 60154

    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: SQL Server FineBuild[/url] 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: 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: 6140

    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'

Viewing 3 posts - 1 through 3 (of 3 total)

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