SQL

Configuring Storage Spaces for Azure SQL IaaS


One way to use SQL in Azure is to deploy SQL IaaS by installing SQL on an Azure VM. This post goes over provisioning disk system in Azure optimized for SQL. In this demo we’re using a DS14v2 size VM, but any VM size that supports 16 data disks is OK. To see the VM SKU’s that support 16 or more disks in a given Azure location we can use the PowerShell cmdlet:

Get-AzureRmVMSize -Location 'eastus' | 
    where MaxDataDiskCount -GE 16 | sort MaxDataDiskCount

and we’ll see a list like

We deployed a VM using the Microsoft Gallery image of the latest 2016 server version, using an unmanaged OS disk in a storage account configured as:

  • Premium
  • LRS
  • GPv2
  • Hot

Attach 16 disks:

Next we provision and attach 16 one TB unmanaged SSD disks (page blobs) using the following PowerShell code:

$VMName = 'myVMName'
$RGName = 'myResourceGroupName'
0..15 | foreach {
    $VM = Get-AzureRmVM -ResourceGroupName $RGName -Name $VMName
    $DataDiskName = "$VMName-DataDisk-$_"
    $OSDiskUri    = $VM.StorageProfile.OsDisk.Vhd.Uri
    $DataDiskUri  = "$($OSDiskUri | Split-Path)\$DataDiskName.vhd".Replace('\','/')
    $ParameterSet = @{
        VM           = $VM 
        Name         = $DataDiskName 
        Caching      = 'ReadWrite' 
        DiskSizeInGB = 1023 
        Lun          = $_ 
        VhdUri       = $DataDiskUri 
        CreateOption = 'Empty'
    }
    $VM = Add-AzureRmVMDataDisk @ParameterSet
    Update-AzureRmVM -ResourceGroupName $RGName -VM $VM
}

Create Storage Pool:

Next we RDP to the VM and provision a storage pool. In Server Manager under File and Storage Service/Volumes/Storage Pools we should see the 16 disks under the default ‘Primordial’ pool. We can right click on that and create new pool. The important thing here is to select ‘Manual’ disk allocation for each of the 16 disks. This is important since this is an all-SSD pool. The default setting will cause the system to reserve all 16 disks for Journal and we won’t be able to create any virtual disks.

The same task can be performed via PowerShell as follows:

$PoolName = 'SQLPool'
$ParameterSet = @{
    FriendlyName = $PoolName 
    StorageSubSystemFriendlyName = (Get-StorageSubSystem).FriendlyName 
    PhysicalDisks = Get-PhysicalDisk –CanPool $True
}
New-StoragePool @ParameterSet
Get-PhysicalDisk -FriendlyName 'Msft Virtual Disk' | 
    Set-PhysicalDisk -Usage ManualSelect # otherwise all SSD disks will be reserved for journal

Create Virtual disks and volumes:

Finally we create virtual disks and volumes as follows:

This particular configuration uses 8 out of the 16 TB in the pool leaving 8 TB for future virtual disk growth. A Virtual disk can be expanded in Server Manager, to be followed by volume expansion in Disk Management tool.

The virtual disks in this configuration can survive a single disk failure being in a 2-way mirror. Although this is almost not a practical concern given that the 16 disks are triple redundant (each block of each disk is synchronously written to 3 physical underlying disks)

2 way-mirrored virtual disks also enhance read performance since read operations occur against one of the 2 disks in a mirrored space.

In the data/log/temp virtual disks, the Interleave size has been dropped to 64KB down from the default 256KB since SQL writes are 8-32KB. With 8 columns, this makes the data stripe size (64 * 8) 512KB


Powershell script to backup all user databases on current SQL server


This script will backup all user databases on the current SQL server. The Input region accepts values for 2 variables:

$Destination ==>This is the folder where backup files will be stored
$Retension ==> Number of days to keep backup files. Backup files older than this will be deleted. If you don’t want any files deleted, make this number high like 99999

The script creates a folder for each database under the $Destination folder. Backup file name contains the database name and date/time of backup. For example, for database ‘INM’, backup file would be ‘H:\SQL-Backup\INM\INM-20160605_081237AM.bak’
Script returns an object collection. Each object has the following properties:

  • ServerName
  • DatabaseName
  • RecoveryModel
  • BackupDateTime(yyyyMMdd_hhmmsstt)
  • BackupDuration(hh:mm:ss)
  • BackupFileSize(MB)
  • BackupFileSize

Sample script output:

SQL02


Also see script to list SQL Recovery Model of all databases on current server, script to modify and list SQL Recovery Model of all databases, and this related post.


SQL backup options and feature details


Simple Recovery Model

Overview:

  • Transaction log is mainly used for crash recovery (no log backup)
  • Transaction log keeps all records after last backup (full or differential)
  • Supports Full and Differential backups only
  • Changes since last backup will be lost
  • Example: Full on Saturdays and differentials on weekdays.

Advantages:

  • Automatically reclaims log space

Dis-advantages:

  • The following features are not supported under the Simple Recovery Model:
    • Log shipping
    • AlwaysOn Database mirroring
    • Point-in-time restores
  • Changes since the most recent backup cannot be recovered

To view a database Recovery Model:
TSQL:

SELECT recovery_model_desc FROM sys.databases WHERE name = ‘MyDBName’

Powershell:

Using the SBSQL module:

Get-SQLRecoveryModel -DBName MyDBName -ServerName MySQLServerName | FT -a

To view Recovery Model of all databases on current SQL server, use the Get-SQLRecoveryModel powershell script.

Sample script output:

SQL01

To change a database Recovery Model:
TSQL:

USE master; ALTER DATABASE MyDBName SET RECOVERY SIMPLE

This will change the Recovery Model for database ‘MyDBName’ to Simple. Valid options are Simple, Full and Bulk_Logged

Powershell:

Using the SBSQL module:

Set-SQLRecoveryModel -DBName MyDBName -RecoveryModel Simple -ServerName MySQLServerName

To modify Recovery Model of all user databases on current SQL server use the Set-SQLRecoveryModel script.


Setting up certificate based communication between SQL server endpoints for SQL mirroring


SQL mirroring is a popular DR option for SQL databases. It provides a warm standby server where a database can be recovered quickly. Although SQL mirroring is being deprecated by Microsoft in SQL 2016 in lieu of Availability Groups, they share several elements of the underlying technologies.

SQL mirroring popularity is often attributed to

  • It does not require shared storage like clustering
  • It does not require a common file share like log-shipping
  • It can be configured for automatic failover (synchronous mode only) with the configuration of a SQL Witness (3rd server). This option requires that the application/client be mirror-aware (include ‘failover partner=xxxx’ in connection string)
  • It can be configured in safety/synchronous mode (default), where a transaction is written to both servers before a commit is returned to the client. This requires low latency between the 2 servers.
  • It can be configured in performance/asynchronous mode, where the primary server sends commit back to client as soon as the transaction is written to the send queue. This may lose data if the primary fails before the transaction makes it to the secondary server redo queue.
  • It can be configured across distant geographical locations (recommend asynchronous mode and certificate based authentication in this scenario)
  • It can be configured between 2 servers that belong to different AD domains using certificate authentication.

This Powershell script automates the last scenario of setting up certificate based communication for a pair SQL 2014 servers in Azure. The script can be slightly modified to work on on-premises SQL servers as well. This script has been tested on SQL 2014 SP1 but should work on SQL 2008 R2 and above.

Region ‘Initialize’ output may look like:

SQL-Mirroring-02

Region ‘configure outbound connections’ output may look like:

SQL-Mirroring-03

Region ‘configure inbound connections’ output may look like:

SQL-Mirroring-04

Finally, region ‘Final Cleanup’ deletes the certificate files and closes open sessions to the 2 SQL servers:

SQL-Mirroring-05

To undo this setup, you can run this TSQL script on the Principal server:

DROP CERTIFICATE Vertitech1SQL2_cert
DROP user Vertitech1SQL2_user
DROP login Vertitech1SQL2_login
DROP endpoint Endpoint_mirroring
DROP CERTIFICATE Vertitech1SQL1_cert

SELECT * FROM sys.sysusers where name = ‘Vertitech1SQL2_user’
SELECT * FROM sys.server_principals where name = ‘Vertitech1SQL2_login’
SELECT * FROM sys.certificates
SELECT name,port FROM sys.tcp_endpoints

and this TSQL script on the Secondary server:

DROP CERTIFICATE Vertitech1SQL1_cert
DROP user Vertitech1SQL1_user
DROP login Vertitech1SQL1_login
DROP endpoint Endpoint_mirroring
DROP CERTIFICATE Vertitech1SQL2_cert

SELECT * FROM sys.sysusers where name = ‘Vertitech1SQL1_user’
SELECT * FROM sys.server_principals where name = ‘Vertitech1SQL1_login’
SELECT * FROM sys.certificates
SELECT name,port FROM sys.tcp_endpoints


Truncating SQL Log files using Powershell


SQLIn active SQL databases, log files may grow to very large sizes and cause performance degradation. The following script truncates SQL log files for all databases on the current SQL server.

The script can be downloaded from the Microsoft Script Center Repository.

This script needs SQLPS module. This is installed by default with SQL 2012 and higher versions. If you’re using an older SQL version, you need to download and install the following 3 components in order:

  1. Microsoft® System CLR Types for Microsoft® SQL Server® 2012 (SQLSysClrTypes.msi)
  2. Microsoft® SQL Server® 2012 Shared Management Objects (SharedManagementObjects.msi)
  3. Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® 2012 (PowerShellTools.msi)

I’ve linked to the x64 versions of these files, but you need to get the version that matches your OS.

You also need to remember to run the command:

Import-Module SQLPS

then close ISE and reopen it again. After that you can run the script.


To run this script as a scheduled task:

# To schedule a Powershell script:

$ScriptPath = ‘C:\Scripts\Truncate-SQL.ps1’
$TaskName = ‘TruncateSQL’
$TaskRun = “powershell.exe -NoLogo -NonInteractive -WindowStyle Hidden -Command “”$ScriptPath”””

# Example: Weekly on Sundays at 2 AM
SCHTASKS.EXE /Create /S $Env:COMPUTERNAME /RU SYSTEM /SC WEEKLY /D SUN /TN $TaskName /TR $TaskRun /ST 02:00 /RL HIGHEST /F
break

# Example: Daily at 7 AM
SCHTASKS.EXE /Create /S $Env:COMPUTERNAME /RU SYSTEM /SC DAILY /TN $TaskName /TR $TaskRun /ST 07:00 /RL HIGHEST /F
break

# Run now:
SCHTASKS /Run /TN “\$TaskName”
break

<#
Use
SchTasks.exe /Create /?
To see switch details
#>


Moving SQL Temp database and log files using Powershell


If you need to move the SQL Temp database and log files to a new drive/folder, you need to run a few SQL queries and restart SQL service. The following script automates this task. This can be particularly useful if you need to do this task for many SQL servers.

This script needs SQLPS module. This is installed by default with SQL 2012 and higher versions. If you’re using an older SQL version, you need to download and install the following 3 components in order:

  1. Microsoft® System CLR Types for Microsoft® SQL Server® 2012 (SQLSysClrTypes.msi)
  2. Microsoft® SQL Server® 2012 Shared Management Objects (SharedManagementObjects.msi)
  3. Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® 2012 (PowerShellTools.msi)

I’ve linked to the x64 versions of these files, but you need to get the version that matches your OS.

You also need to remember to run the command:

Import-Module SQLPS

then close ISE and reopen it again. After that you can run the script.

You need to edit lines 6 and 7 to enter the new locations of where you’d like to move the Temp DB and Log files. Make sure the necessary folder exists before you run the script (h:\SQL-Temp folder in this example).

Move-SQL-Temp

# Script to move temp DB and its log from one disk location to another
# Sam Boutros
# 6/5/2014
# Move-Temp.ps1
#
$NewTempDBLoc = “h:\SQL-Temp\tempdb.mdf”
$NewTemplogLoc = ‘h:\SQL-Temp\templog.ldf’
# End Data entry section
#
$TempDB = “N'” + $NewTempDBLoc + “‘” # This format is needed for Invoke-SQLCMD
$TempLog = “N'” + $NewTempLogLoc + “‘”
$Loc = Get-Location
$Date = Get-Date -format yyyyMMdd_hhmmsstt
$logfile = $Loc.path + “\Move-Temp_” + $Date + “.txt”
#
function log($string, $color)
{
if ($Color -eq $null) {$color = “white”}
write-host $string -foregroundcolor $color
$temp = “: ” + $string
$string = Get-Date -format “yyyy.MM.dd hh:mm:ss tt”
$string += $temp
$string | out-file -Filepath $logfile -append
}
#
# Import-Module SQLPS # See notes..
log “Modifying Temp DB and Log location..” green
Invoke-SQLCMD -Query “USE [master]”
Invoke-SQLCMD -Query “ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = $TempDB ) ”
Invoke-SQLCMD -Query “ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = $TempLog) ”
Set-Location -Path ($logfile.Split(“:”)[0] + “:”)
log “Restaring SQL Service..” green
Restart-Service MSSQLSERVER -Force