How To Resize an AWS Volume Using The AWS Console or PowerShell

Problem

I'm running my SQL Server on an Elastic Compute Cloud (EC2) instance, but my databases are growing bigger than anticipated. The Elastic Block Storage (EBS) volumes attached to the server are nearly full, how can I expand them to get more space for my data?

Solution

Unlike most on-premises storage systems, an Amazon EBS volume's size is fixed when it is created. Despite our best efforts at capacity planning sometimes we run out of space. In AWS there are two ways to address this problem. The first is to simply add more EBS volumes to the server and create new data files on the fresh volumes. This is relatively simple, and has the advantage that you do not have to reboot your server to gain access to the new space. The disadvantage is that the new layout may conflict with existing server standards or cause problems with backups. The second is a bit more complicated, but allows us to keep the mount points or drive letters unchanged. It is also the only way to get more space for the root volume (C: drive) when it runs out of space. It is this method that will be covered in this tip.

"Resizing" a volume consists of several steps that can be accomplished both through the AWS console and via PowerShell. In practice, a mixed method will probably be easiest. If you find yourself doing this frequently you may want to try to fully automate the process, but a closer look at your capacity planning is probably warranted! The steps are listed here and we will cover each one in detail both from the console and using PowerShell. This tip is specifically for SQL Server running on EC2, if you are using Amazon RDS for SQL Server the process will be very different.

  1. Determine the instance id, volume id, and device name of disk to be resized.
  2. Shut down the associated EC2 instance.
  3. Create a snapshot of the disk to be resized.
  4. Create new, larger volume from the snapshot.
  5. Detach the old volume from the EC2 instance.
  6. Attach the new volume to the EC2 instance.
  7. Start up the EC2 instance.
  8. Expand the disk partition to utilize the additional space in the volume.

In this tip I am assuming the reader is familiar with the AWS Console and running Powershell in the AWS environment. The Amazon PowerShell commandlets require authentication for each invokation. Your credentials can be set up in your environment as I described in a previous article Backing up your SQL Server to Amazon's Cloud in Three Easy Steps in step two about installing and configuring the Amazon PowerShell Module. I've added the code to set up my credentials in my profile so they are automatically available when I start a new session.

Step 1 - Identify the Volume

From the console we can identify the volume and device name from the EC2 page on the description tab. The device names are "unixy" due to the underlying virtualization. As a rule we find that /dev/sda1 is the C: drive and the /dev/xvd* devices are mounted to the drive letter corresponding to the last letter of the device name. i.e. /dev/xvdd is drive D: and /dev/xvde is drive E: and so on. By clicking on the device name we see the EBS volume id in the tool tip. Make sure to write down the instance id, device name and the EBS volume id, they are needed in the next steps. The ids are in the format of a prefix identifying the object type, then an eight-digit hex value i.e. i-1234abcd for an EC2 instance or vol-abcd1234 for an EBS volume.

Fig 1a

 

Fig 1b

 

A PowerShell script that maps disk drive letters to EBS volume ids and device names for an instance can be found on the Amazon support web site. It's a clever piece of code that burrows into the WMI disk information and matches it with the EBS information. I've adapted it a bit for this tip, but credit goes to Amazon for the original. This code should be run from the instance that has the EBS volume attached to it, otherwise we need to get the instance id and availability zone from another source (like the console) and replace the first two lines with simple variable assignments.

# List the Windows disks
# From the Amazon AWS Documentation Website circa 9/3/2015
#   - correction to $Map hash table creation
#
# http://docs.aws.amazon.com/AWSEC2/latest/WindowsGuide/ec2-windows-volumes.html
#

# Create a hash table that maps each device to a SCSI target
$Map = @{"0" = '/dev/sda1'} 
for($x = 1; $x -le 25; $x++) {$Map.add($x.ToString(), [String]::Format("/dev/xvd{0}",[char](97 + $x)))}
for($x = 78; $x -le 102; $x++) {$Map.add($x.ToString(), [String]::Format("/dev/xvdc{0}",[char](19 + $x)))}

Try {
    # Use the metadata service to discover which instance the script is running on
    $InstanceId = (Invoke-WebRequest '169.254.169.254/latest/meta-data/instance-id').Content
    $AvailabilityZone = (Invoke-WebRequest '169.254.169.254/latest/meta-data/placement/availability-zone').Content
    $Region = $AvailabilityZone.Substring(0, $AvailabilityZone.Length -1)

    # Get the list of volumes attached to this instance
    $BlockDeviceMappings = (Get-EC2Instance -Region $Region -Instance $InstanceId).Instances.BlockDeviceMappings
}
Catch
{
    Write-Host "Could not access the AWS API, are your credentials loaded?"  -ForegroundColor Yellow
}

Get-WmiObject -Class Win32_DiskDrive | %{
    $Drive = $_
    # Find the partitions for this drive
    Get-WmiObject -Class Win32_DiskDriveToDiskPartition |  Where-Object {$_.Antecedent -eq $Drive.Path.Path} | %{
        $D2P = $_
        # Get details about each partition
        $Partition = Get-WmiObject -Class Win32_DiskPartition |  Where-Object {$_.Path.Path -eq $D2P.Dependent}
        # Find the drive that this partition is linked to
        $Disk = Get-WmiObject -Class Win32_LogicalDiskToPartition | Where-Object {$_.Antecedent -in $D2P.Dependent} | %{ 
            $L2P = $_
            # Get the drive letter for this partition, if there is one
            Get-WmiObject -Class Win32_LogicalDisk | Where-Object {$_.Path.Path -in $L2P.Dependent}
        }

        $BlockDeviceMapping = $BlockDeviceMappings | Where-Object { $_.DeviceName -eq $Map[$Drive.SCSITargetId.ToString()] } 

        # Display the information in a table
        New-Object PSObject -Property @{
            Device = $Map[$Drive.SCSITargetId.ToString()];
            Disk = [Int]::Parse($Partition.Name.Split(",")[0].Replace("Disk #",""));
            Boot = $Partition.BootPartition;
            Partition = [Int]::Parse($Partition.Name.Split(",")[1].Replace(" Partition #",""));
            SCSITarget = $Drive.SCSITargetId;
            DriveLetter = If($Disk -eq $NULL) {"NA"} else {$Disk.DeviceID};
            VolumeName = If($Disk -eq $NULL) {"NA"} else {$Disk.VolumeName};
            VolumeId = If($BlockDeviceMapping -eq $NULL) {"NA"} else {$BlockDeviceMapping.Ebs.VolumeId}
        }
    }
} | Sort-Object Disk, Partition | `
Format-Table -AutoSize -Property Disk, Partition, SCSITarget, DriveLetter, Boot, VolumeId, Device, VolumeName


Disk Partition SCSITarget DriveLetter  Boot VolumeId     Device     VolumeName
---- --------- ---------- -----------  ---- --------     ------     ----------
   0         0          0 NA           True vol-1234abcd /dev/sda1  NA
   0         1          0 C:          False vol-1234abcd /dev/sda1
   1         0          3 D:          False vol-1235abcd /dev/xvdd
   2         0          4 E:          False vol-1236abcd /dev/xvde
   3         0          5 F:          False vol-1237abcd /dev/xvdf
   4         0         78 Z:          False NA           /dev/xvdca Temporary Storage 1

 

Step 2 - Stop the EC2 Instance

To ensure that the data on the volume is consistent when we create a snapshot the instance should be shut down. We can either log on to the instance to shut it down, stop it from the console, or issue a single PowerShell command (from another machine) to stop it. When this is done from the console be sure to choose "Stop" and not "Terminate" from the Actions menu or you will get a chance to practice your server recovery procedure!

Fig 2a

The PowerShell commandlet needed to stop the instance is Stop-EC2Instance and requires the instance id to be stopped. We need to run this on a different machine than the one whose disk is being resized. I recommend that you have the AWS PowerShell module installed on your desktop or jumpbox and run the next few steps from there.

# From the output of the PowerShell in step one set these variables in a session on this machine.

$InstanceId = "i-abcd1234"
$AvailabilityZone = "us-east-1b"
$OldVolumeId = "vol-1235abcd" # Choose the volume id from the output of the first script
$DeviceName = "/dev/xvdd"     # /dev/xvdd is (usually) the D: drive, the C: drive is usually /dev/sda1
$NewVolumeSize = 100          # The size of the new volume in GB

Initialize-AWSDefaults -ProfileName default

Stop-EC2Instance -Instance $InstanceId

# Wait for the instance  to stop.
Write-Host -NoNewLine "Checking $InstanceId "
while ((Get-EC2Instance -Instance $InstanceId).Instances.State.Name -ne "stopped") {
  Write-Host -NoNewline "."
  sleep 5
}

Write-Host "."
Write-Host Instance: $InstanceId is stopped

Step 3 - Snapshot the Old Volume

With the instance stopped and the volume information at hand we can resize the volume. Resizing is really a misnomer, what we will do is backup the volume and restore it to a larger one that will replace the original. To back up the volume we will create a snapshot. From the console EC2 page click the volumes link on the left side. If your environment is like ours there are hundreds of volumes listed. To find the one we want type its id (or the value of one of its tags) in the filter area. Then select the volume and from the Actions menu select Create a Snapshot. Be sure to write down the snapshot id of the snapshot that is created or just click the link provided to go to the Snapshot list. Depending on the size of the volume this may take a while.

Fig 3a

 

Fig 3b

 

Fig 3b

 

The PowerShell commandlet used to create a snapshot is New-EC2Snapshot, it requires the volume id of the old volume that will be backed up.

$SnapShot = (New-EC2Snapshot -VolumeId $OldVolumeId -Description "Snapshot for MSSQLTips.com")

# Wait for the snapshot to complete, this may take a while for large volumes
Write-Host -NoNewLine Checking $SnapShot.SnapshotId 
while ((Get-EC2Snapshot -SnapshotId $SnapShot.SnapshotId).State -ne "completed") {
  Write-Host -NoNewline "."
  sleep 5
}
Write-Host "."
Write-Host Snapshot: $SnapShot.SnapshotId is ready

Step 4 - Create a New and Larger Volume

Once the snapshot is complete we need to create a new, larger volume based on the snapshot. From the Snapshot List on the EC2 page of the console select the newly created snapshot and from the Actions menu select Create Volume then fill in the size of the new volume. Note the volume id or click the link in the dialog presented when the volume is created to switch to the volume list and wait for it to become available.

Fig 4a

 

Fig 4b

 

Fig 4b

 

The PowerShell commandlet to create a volume is New-EC2Volume which will need the snapshot id, size, availability zone, and volume type. You need to be sure the volume is created in the same AZ as the target EC2 instance or you will not be able to attach it!

$Volume = ( New-EC2Volume -SnapshotId $SnapShot.SnapshotId `
                          -Size $NewVolumeSize `
                          -AvailabilityZone $AvailabilityZone `
                          -VolumeType "gp2" )

# Wait for the creation to complete, this may take a while for large volumes
Write-Host -NoNewLine Checking $Volume.VolumeId 
while ((Get-EC2Volume -VolumeId $Volume.VolumeId).State -ne "available") {
  write-host -NoNewline "."
  sleep 5
}
Write-host "."
write-host Volume: $Volume.VolumeId is ready

Step 5 - Detach the Old Volume

The new, larger volume is now ready to replace the older, smaller volume. First the old volume needs to be detached from the EC2 instance. Select the old volume on the Volume List in the console and select Detach Volume from the Action menu, then confirm you really want to detach it.

Fig 5a

 

Fig 5b

 

The PowerShell commandlet to detach a volume is Dismount-EC2Volume and requires the volume id. Adding the instance id and device name helps ensure we are detaching the right volume by making the request more specific.

Dismount-EC2Volume -VolumeId $OldVolumeId -InstanceId $InstanceId -Device $DeviceName

# Wait for the dismount to complete
Write-Host -NoNewLine Checking $OldVolumeId 
while ((Get-EC2Volume -VolumeId $OldVolumeId).State -ne "available") {
  write-host -NoNewline "."
  sleep 5
}
Write-host "."
write-host Volume: $OldVolumeId is dismounted

Step 6 - Attach the New Volume

With the old volume detached from the instance the new, larger volume can be attached in its place. Find the new volume in the volume list, search by the new volume id or by the snapshot id that was used to create the volume, then select Attach Volume from the Action Menu. Fill in the instance id and device name then click Attach.

Fig 6a

 

Fig 6b

 

The PowerShell commandlet to attach a volume is Add-EC2Volume and requires the instance id, the volume id to be attached, and the device name where the volume will be attached.

Add-EC2Volume -InstanceId $InstanceId -VolumeId $Volume.VolumeId -Device $DeviceName

# Wait for the attachment to complete
Write-Host -NoNewLine Checking $Volume.VolumeId
while ((Get-EC2Volume -VolumeId $Volume.VolumeId).State -ne "in-use") {
  write-host -NoNewline "."
  sleep 5
}
Write-host "."
write-host Volume: $Volume.VolumeId is attached

# Optionally delete the old volume to avoid paying for the storage.
# Remove-EC2Volume -VolumeId $OldVolumeId

Step 7 - Start the EC2 Instance

The surgery is over and the volume has been replaced with a newer larger one. All that's left to do is spin up the EC2 instance and let Windows know about the new space. On the console Instance List select the instance and click Start from the Action menu

Fig 7a

 

From PowerShell the commandlet to start an EC2 instance is Start-EC2Instance, it just needs the instance id.

Start-EC2Instance -InstanceId $InstanceId

# Wait for the instance  to start.
write-Host -NoNewLine "Checking $InstanceId "
while ((Get-EC2Instance -Instance $InstanceId).Instances.State.Name -ne "running") {
  write-host -NoNewline "."
  sleep 5
}

Write-host "."
write-host Instance: $InstanceId is started

Step 8 - Expand the Partition to use the New Disk Space

The final step enables Windows to recognize the new space added to the volume (or device from its point of view). Log on to the EC2 instance whose volume is being resized. On Windows Server 2012, on the taskbar, right-click the Windows logo, and then select Disk Management. On Windows Server 2008, click Start, point to Administrative Tools, select Computer Management, and then select Disk Management. Look at the lower pane, there will be a disk with unallocated storage. Right-click on the disk and select Extend Volume. This pops up a wizard that will expand the disk to encompass the entire new volume.

drive partitions

Be aware that the session must be run "as Administrator" for these commands to work. First, get a list of the partitions available to resize. The commandlet Get-Partition with no arguments will provide the list.

Get-Partition
   Disk Number: 0

PartitionNumber  DriveLetter Offset                                        Size Type
---------------  ----------- ------                                        ---- ----
1                            1048576                                     350 MB IFS
2                C           368050176                                 59.66 GB IFS


   Disk Number: 1

PartitionNumber  DriveLetter Offset                                        Size Type
---------------  ----------- ------                                        ---- ----
1                D           1048576                                      50 GB IFS

In this case we are working on the "D:" drive which shows up here as Disk Number 1 and Partition Number 1. We want to use all the newly available space for the drive so the following PowerShell will extend the partition to the end of the volume. We can then run Get-Partition to verify the new size. In this example the size has increased from 50 GB to 100 GB

Resize-Partition -DiskNumber 1 `
                 –PartitionNumber 1 `
                 -Size (Get-PartitionSupportedSize –DiskNumber 1 –PartitionNumber 1).SizeMax

Get-Partition -DiskNumber 1 -PartitionNumber 1

   Disk Number: 1

PartitionNumber  DriveLetter Offset                                        Size Type
---------------  ----------- ------                                        ---- ----
1                D           1048576                                     100 GB IFS

Next Steps

There you have it, a process for "resizing" an EBS volume on your AWS hosted SQL Server. I'm looking forward to the new Elastic File System currently in beta to see if it can solve this problem more elegantly but it is not currently available in our home region. I'm learning AWS and PowerShell as I go so if you see a way this process can be improved, please share your thoughts with the MSSQLTips.com community in the comments below

Top