Compute

When you provision an IBM Cloud virtual servers for VPC, you select an Instance Profiles from one of three families of profiles: Balanced, Compute, and Memory.

• Balanced - Balanced profiles have a ratio of 4 GiB of memory for every 1 vCPU of compute.
• Compute - Compute profiles have a ratio of 2 GiB of memory for every 1 vCPU of compute.
• Memory - Memory profiles are best for memory intensive workloads, such as database applications, and have a ratio 8 GiB of memory for every 1 vCPU of compute.

Online Transactional Processing (OLTP) typically involves inserting, updating, deleting small amounts of data in a database by large numbers of transactions by a large number of users. A recommended minimum for a production MS SQL OLTP environment is 4 vCore with 32 GB of memory from the Memory profile family, mx2-4x32. For larger requirements, the Memory profile extends up to the mx2-128x1024 which has 128vCPU, 1024 GiB RAM and a 80 Gbps network bandwidth cap.

SQL Server data warehouse environments often require greater memory to vCPU requirements than 8:1, therefore, you may have to over provision on vCPU to satisfy the memory requirements.

The Memory profile family includes profiles that are provisioned with instance storage. Instance storage provides solid state drives directly attached to the virtual server instance when the instance is provisioned. Instance storage disk provides fast, temporary storage to improve performance of many workloads including transactional processing.

Storage

When planning your SQL Server on IBM Cloud VPC, there are three storage components to consider; boot volumes, data volumes and instance storage.

• Boot volumes - When virtual server is created, a 100 GB, 3 IOPS/GB boot volume is created from block storage and attached to the instance. By default, boot volumes are encrypted by IBM-managed encryption, however, customer-managed encryption is an option. Boot volumes can not be detached, deleted or increased or reduced in size. Boot volumes are always deleted when the virtual server is deleted. Boot volumes contain the operating system files.

• Data volumes - Data volumes leverage block storage for VPC and provides hypervisor-mounted, high-performance data storage that is stored redundantly across multiple physical disks in an Availability Zone (AZ) to prevent data loss due to failure of any single component. Data volumes range from 10 GB to 2000 GB and maximum IOPS varies based on volume size and the IOPS tier profile selected. For example, the max IOPS for a 5 IOPS/GB volume of 2000 GB is 10,000 IOPS. You are able to select a volume profile that best meets your requirements as volume profiles are available as three predefined IOPS tiers or as a custom IOPS profile:

  • 3 IOPS/GB - A general-purpose tier profile provides IOPS/GB performance suitable for a virtual server instance Balanced profile.
  • 5 IOPS/GB - This profile provides IOPS/GB performance suitable for a virtual server instance Compute profile.
  • 10 IOPS/GB - Typically used for a virtual server instance Memory profile.

  For more information, see IOPs tiers. The number of volumes that can be attached to a virtual server depends on how many vCPUs the virtual server contains. For more information, see Volume attachment limits. Data volumes can be detached and attached to virtual servers as required. Data volumes are encrypted by default with IBM-managed encryption. You can also encrypt data volumes using your own root keys. Refer to Block storage capacity and performance advice on choosing the optimal block storage volume size and performance level.

• Instance Storage - Optionally, the virtual server can include Instance storage which provides solid state drives directly attached to the virtual server instance when the instance is provisioned. Instance storage disk provides fast, temporary storage to improve performance of many workloads including transactional processing. The data stored on instance storage is ephemeral, meaning it is tied directly to the lifecycle of the instance. The instance storage disk is automatically created and destroyed with the instance. Instance storage data is not lost, however, when an instance is rebooted. If performance is a concern then MS SQL Server tempdb can be placed on instance storage

For more information, see About Block Storage for VPC.

Under rare maintenance operations, a live migration of the virtual server to a new host may necessary. The virtual server will experience a brief pause of around 10 seconds, and in some cases up to 30 seconds. The virtual server instance is not rebooted as part of this process. However, for virtual servers with Instance Storage, then the server will be restarted. Refer to Understanding Cloud Maintenance Operations for further information.

Configure storage spaces

From the IBM Cloud console, capture the storage volumes information for the SQL server. For example:

sqldb01-data: 0787-ff88b86a-1e29-4f0d-8a69-67b4deda3d5c-lpcn2
sqldb01-log: 0787-1d41b85e-4e8a-499e-b889-13b96db5251c-2w2n2

The following PowerShell command is used to capture the Windows OS view of the SerialNumber for use in subsequent PowerShell commands; Get-StoragePool -IsPrimordial $true | Get-PhysicalDisk -CanPool $True. As can be seen from the following example, the SerialNumbers can be captured:

Number FriendlyName       SerialNumber                         MediaType   CanPool OperationalStatus HealthStatus Usage           Size
------ ------------       ------------                         ---------   ------- ----------------- ------------ -----           ----
1      QEMU QEMU HARDDISK cloud-init-0787_1c6e0975-a584-43ca-b Unspecified True    OK                Healthy      Auto-Select   378 KB
5      Red Hat VirtIO     cloud-init-                          Unspecified True    OK                Healthy      Auto-Select    44 KB
3      Red Hat VirtIO     0787-ff88b86a-1e29-4                 Unspecified True    OK                Healthy      Auto-Select     1 TB
4      Red Hat VirtIO     0787-1d41b85e-4e8a-4                 Unspecified True    OK                Healthy      Auto-Select     1 TB
2      Red Hat VirtIO     70ab84c0-0e12-4fc2-a                 Unspecified True    OK                Healthy      Auto-Select 139.7 GB

Create sqldatapool storage pool

The following PowerShell command can be used to configure the sqldatapool storage pool, replace <SerialNumber> with the serial number for the sqldb01-data volume. This command achieves the following:

• Creates a storage pool called sqldatapool.
• Creates a virtual disk in this pool called sqldata for striping (-ResiliencySettingName simple).
• The virtual disk is initialized with a GPT partition and assigned a drive letter of D.
• The virtual disk is formatted with the NTFS filesystem with a block size of 64KB and assigned a label of SQLDATA.

$dataserial = "<SerialNumber>"
New-StoragePool -FriendlyName "sqldatapool" -StorageSubsystemFriendlyName "Windows Storage*" -PhysicalDisks (Get-PhysicalDisk -SerialNumber $dataserial) | New-VirtualDisk -FriendlyName "sqldata" -Interleave 65536 -NumberOfColumns 1 -ResiliencySettingName simple –UseMaximumSize | Initialize-Disk -PartitionStyle GPT -PassThru | New-Partition -DriveLetter "D" -UseMaximumSize | Format-Volume -FileSystem NTFS -NewFileSystemLabel "SQLDATA" -AllocationUnitSize 65536 -Confirm:$false -UseLargeFRS

If you are using multiple data volumes for increased performance, then this PowerShell command must be modified. An example for 2 disks:

New-StoragePool -FriendlyName "sqldatapool" -StorageSubsystemFriendlyName "Windows Storage*" -PhysicalDisks (Get-PhysicalDisk | where {($_.SerialNumber -eq "<Disk1_SerialNumber>") -or ($_.SerialNumber -eq "<Disk2_SerialNumber>")}) | New-VirtualDisk -FriendlyName "sqldata" -Interleave 65536 -NumberOfColumns 2 -ResiliencySettingName simple –UseMaximumSize | Initialize-Disk -PartitionStyle GPT -PassThru | New-Partition -DriveLetter "D" -UseMaximumSize | Format-Volume -FileSystem NTFS -NewFileSystemLabel "SQLDATA" -AllocationUnitSize 65536 -Confirm:$false -UseLargeFRS

The -NumberOfColumns matches the number of disks to stripe data across.

Create the sqllogpool storage pool

The following PowerShell command can be used to configure the sqllogpool storage pool, replace <SerialNumber> with the serial number for the sqldb01-log volume. This command achieves the following:

• Creates a storage pool called sqllogpool.
• Creates a virtual disk in this pool called sqllog for striping (-ResiliencySettingName simple).
• The virtual disk is initialized with a GPT partition and assigned a drive letter of E.
• The virtual disk is formatted with the NTFS filesystem with a block size of 64KB and assigned a label of SQLLOG.

$logserial = "<SerialNumber>"
New-StoragePool -FriendlyName "sqllogpool" -StorageSubsystemFriendlyName "Windows Storage*" -PhysicalDisks (Get-PhysicalDisk -SerialNumber $logserial) | New-VirtualDisk -FriendlyName "sqllog" -Interleave 65536 -NumberOfColumns 1 -ResiliencySettingName simple –UseMaximumSize | Initialize-Disk -PartitionStyle GPT -PassThru | New-Partition -DriveLetter "E" -UseMaximumSize | Format-Volume -FileSystem NTFS -NewFileSystemLabel "SQLLOG" -AllocationUnitSize 65536 -Confirm:$false -UseLargeFRS

Initialize instance storage for tempdb

The drive for tempdb does not use Storage Spaces as instance storage only consists of a single volume. The following PowerShell command can be used to configure the volume, replace <SerialNumber> with the serial number for the instance storage volume. This command achieves the following:

• Creates a drive initialized with a GPT partition and assigned a drive letter of F.
• The drive is formatted with the NTFS filesystem with a block size of 64KB and assigned a label of TEMPDB.

$tempdbserial = "<SerialNumber>"
Get-Disk | Where SerialNumber -eq $tempdbserial | Initialize-Disk -PartitionStyle GPT -PassThru | New-Partition -DriveLetter "F" -UseMaximumSize | Format-Volume -FileSystem NTFS -NewFileSystemLabel "TEMPDB" -AllocationUnitSize 65536 -Confirm:$false -UseLargeFRS