Compute

It is recommended (based on testing done by Microsoft) that you use a DS3 machine for the Enterprise Edition and DS2 machine for the Standard Edition. One of the main reasons for this is due to the availability of the local SSD, which can be utilized for tempdb and also because Premium IO disks can be attached to these machines. Azure Storage and its different tiers were highlighted in Chapter 3. If such a check had to be automated against multiple machines, then this could be done using the weapon of choice of an automation ninja; i.e., PowerShell. Listing 7-1 shows how to check if you are running a DS- or G-series virtual machine hosting a SQL Server instance.

Listing 7-1. PowerShell Code to Check if an Azure VM Hosting a SQL Server Instance Is Running the Recommended Tier

$RGName = "<Resource Group Name>"
$VMName = "<ARM deployed virtual machine name>"
# Get the details of the virtual machine
$VM = Get-AzureRmVM -ResourceGroupName $RGName -Name $VMName
# Check if the recommended machine size is being followed
if ($VM.HardwareProfile.VirtualMachineSize -like ("*_DS*") -or $VM.HardwareProfile.VirtualMachineSize -like "*_G*")
{
    Write-Host "[INFO] Virtual machine size: " $VM.HardwareProfile.VirtualMachineSize -ForegroundColor Green
}
else
{
    Write-Host "[WARN] Virtual machine size: " $VM.HardwareProfile.VirtualMachineSize -ForegroundColor Yellow
    Write-Host "It is recommended to use DS2 or higher machines for SQL Server Standard Edition" -ForegroundColor Yellow
    Write-Host "It is recommended to use DS3 or higher machines for SQL Server Enterprise Edition" -ForegroundColor Yellow
}

Another consideration with respect to choosing a larger virtual machine is the network bandwidth. The larger the machine, the more network bandwidth you have. It is important to note that network communications generated by accessing data disks and the operating system disk attached to the virtual machine are not considered part of the bandwidth limits.

Network

One of the fundamental differences in Azure related to networking is that the latency can be higher as compared to your traditional on-premises environment due to multiple layers that are introduced, such as load balancers, firewalls, virtualization, etc. One of the golden rules of optimizing network performance for your applications talking to SQL Server is to reduce the number of network round trips. If you are unable to do so, it is recommended that you consolidate the application layers into a single virtual machine of an appropriate tier. This ensures that the network calls do not have to leave the virtual machine.

If you have applications or systems that make the upward journey to the cloud from your on-premises environment, it’s best to determine the network profile of the application calls to the database along with the chattiness quotient. In scenarios where none of the changes required to control the chattiness and network round trips can be changed as desired, you might consider using Azure ExpressRoute, which can create private connections between Azure data centers and on-premises infrastructure or in a co-location environment. You can reach bandwidths as high as 10Gbps depending on the tier that you choose and your geographical location.

Ctrl+C and Ctrl+V are truly awesome features adored by IT professionals across the world and this at times can become the bane of your performance. Moving any sort of data to and from your virtual machine and an on-premises environment using copy and paste functionality or any other means counts toward your bandwidth consumption. So it is time to cringe when you see that developer running a SELECT * query on a table with a million rows against a Management Studio running on the on-premises machine while connecting to a SQL Server instance hosted on an Azure VM! It is always advisable to set up a client workstation(s) in Azure that the developer(s) can use for development/testing efforts. If there is a need to move data in and out of the Azure region or on-premises, it’s better to compress the data before moving it for performance and cost reasons.

Storage

The next aspect of performance is the configuration of the storage layout. The Azure Storage account and compute for your virtual machine should be in the same data center to reduce transfer delays. If you fail to do this, performance is negatively impacted exponentially. This is akin to having your bed in one room and the mattress in the other. The functionality of the mattress might be served but the true value of both the components put together will never be realized unless they are bundled together!

It is recommended to use SQL Server features for high availability and disaster recovery for the instance and databases hosted on the instance, as consistent write order across multiple disks is not guaranteed. This is primarily the reason why the Locally Redundant Storage (LRS) optionis recommended for data disks hosting SQL Server database files. Another aspect to keep in mind is that LRS storage has twice the ingress/egress bandwidths of its peer categories like Geo-Redundant Storage/Zone Redundant Storage.

The first automation check is to use the virtual machine properties and compare the location of the compute and storage and ensure that it is co-located. The way to automate this is to retrieve the storage profile of the data disks attached to the virtual machine and determine if replication is enabled for them. The PowerShell script in Listing 7-2 assumes that the storage accounts in the resource group are associated with the SQL Server instance deployed in the same resource group. Its checks to see if storage replication is enabled and if storage and compute are co-located.

Listing 7-2. PowerShell Script for Storage Best Practices

$Accounts = @() #Empty list to store the account names

# Get the details about the virtual machine
$VM = Get-AzureRmVM -ResourceGroupName $RGName -Name $VMName

# Retrieve the URI from the virtual hard disks attached to the VM
$VHDs = $VM.StorageProfile.DataDisks.VirtualHardDisk.Uri

# Retrieve the storage account from each URI
foreach ($vhd in $VHDs)
{
   $Accounts = $Accounts + ((($vhd -split "//")[1]) -split ".blob")[0]
}
# Get the unique storage accounts
$Accounts = $Accounts | select -Unique

# Get the best practices for the account
foreach ($Account in $Accounts)
{
    $StorageAccount = Get-AzureRmStorageAccount -ResourceGroupName $RGName -Name $Account
    Write-Host "***** Storage account check for " $StorageAccount.StorageAccountName
    if ($StorageAccount.AccountType.ToString().Contains("LRS"))
    {
        Write-Host "[INFO] Replication is not enabled for the storage account" -ForegroundColor Green
    }
    else
    {
        Write-Host "[ERR] Account Type: " $StorageAccount.AccountType -ForegroundColor Red
        Write-Host "[ERR] It is recommended to disable any form of replication for your storage account" -ForegroundColor Red
    }

    if ($VM.Location -eq $StorageAccount.Location.Replace(" ",""))
    {
        Write-Host "[INFO] Storage and Compute are co-located" -ForegroundColor Green
    }
    else
    {
        Write-Host "[ERR] Storage and Compute are co-located" -ForegroundColor Red
    }
}                                                                               

Data Disks

When you think about SQL Server database files and operating system disks, a few best practices do jump out irrespective of the platform and environment. It is never a good idea to mix data and log files in the same volume and, in the case of Azure VMs, on the same data disks. SQL Server data files thrive on random IO whereas transaction logs perform sequential IO. Due to the different IO profiles, the storage medium cannot perform optimally when both data and logs are performing large amounts of operations in short periods.

With Azure disks, there is a warm-up effect that can result in a reduced rate of throughput and bandwidth for a short period of time. In situations where a data disk is not accessed for a period of time (approximately 20 minutes), adaptive partitioning and load balancing mechanisms kick in. If the disk is accessed while these algorithms are active, you may notice some degradation in throughput and bandwidth for a short period of time (approximately 10 minutes), after which they return to their normal levels. This warm-up effect happens because of the adaptive partitioning and load balancing mechanism of Azure, which dynamically adjusts to workload changes in a multi-tenant storage environment. When deciding on the placement of your files on data disks, it is important to keep this information in mind.

The operating system disk is locally attached disk and not in the same performance class as required for hosting high performance, low latency database files. It is advisable not to store anything on the operating system files other than, as the name suggests, operating system files. All Azure Virtual Machines deployed from the gallery have C: as the operating system drive. Listing 7-3 shows a code sample used to check if database files are hosted on the operating System drive.

Listing 7-3. PowerShell Script To Test If Any Database Files Are Hosted on the Operating System [C:] Drive

$RulePass = 1
$sqlquery = "select distinct substring(physical_name,1,2) as drive from sys.master_files where substring(physical_name,2,1) = ':'"
$sqlDrives = Invoke-Sqlcmd -ServerInstance $sqlserver -Query $sqlquery

# Find out the OS Drive
$Name = Get-WmiObject -Class Win32_DiskDrive -Filter "InterfaceType = `"IDE`" and SCSITargetId = 0" | Select-Object PAth
$Dependent = Get-WmiObject -Class Win32_DiskDriveToDiskPartition | Where-Object {$_.Antecedent -contains $Name.Path} | Select-Object Dependent
$OSDrive = (Get-WmiObject -Class Win32_LogicalDiskToPartition | Where-Object {$_.Antecedent -eq $Dependent.Dependent} | Select-Object Dependent).Dependent.Split("`"")[1]


foreach ($drive in $sqlDrives)
{
    if ($drive.drive -eq $OSDrive)
    {
        Write-Host "[ERR] Database files found on OS drive" -ForegroundColor Red
        $RulePass = 0
    }
}

if ($RulePass -eq 1)
{
    Write-Host "[INFO] No database files found on OS drive" -ForegroundColor Green
}

The teams at Microsoft have tested various workloads on SQL Server running on Azure Virtual Machines and the verdict on the disk configuration is as follows (assuming that you are using Premium IO) :

• Enable read caching on the data disks that host data files and tempdb. This allows reads and writes to be cached for future reads. The writes are persisted directly to Azure Storage to prevent data loss or data corruption while still enabling read cache. For transactional workloads, this is very beneficial.

• Disable read caching for data disks that will host transaction log files. This would bypass the cache completely. All disk transfers are completed against Azure Storage. This cache setting prevents the physical host local disks from becoming a bottleneck. This is a common problem with local disks, which manifests itself as WRITELOG or other IO-related waits when transaction log flushes are high. This configuration setting is very performant in scenarios where the rates of log flushes due to transaction commits are very high.

• Temporary disk (typically D: drive) for certain tiers have locally attached SSDs. Such disks can be used to host the tempdb or buffer pool extensions. Buffer pool extension was a feature added in SQL Server 2014, which targets non-volatile storage as an extension of the SQL Server buffer pool. See Listing 7-5 for a code sample that checks if the temporary drive is hosting database files other than tempdb.

• Formatting the data disks with recommended allocation sizes (64KB). This is especially beneficial for OLTP workload performance. (See Listing 7-4.)

A number of deployments have raised support incidents with Microsoft due to performance issues that have been attributed to these best practices not being followed.

Listing 7-4. PowerShell Code for Checking if the Allocation Unit Size Is Set to 64KB

function global:Split-Result()
{
param
(
[parameter(ValueFromPipeline=$true,
Mandatory=$true)]
[Array]$MATCHRESULT
)

process
{
$ReturnData=NEW-OBJECT PSOBJECT –property @{Title=”;Value=”}
$DATA=$Matchresult[0].tostring().split(":")
$ReturnData.Title=$Data[0].trim()
$ReturnData.Value=$Data[1].trim()
Return $ReturnData
}
}

$LogicalDisks = Get-WmiObject -Query "select * from Win32_LogicalDisk Where MediaType = 12" | Select Name, MediaType, FileSystem, Size

foreach ($disk in $LogicalDisks)
{
    $Drive = $disk.Name + ""
    $RESULTS=(fsutil fsinfo ntfsinfo $Drive)
    $AllocSize = $Results | Split-Result | Select-Object Title,Value | Where-Object {$_.Title -eq "Bytes Per Cluster"}
    if ($AllocSize.Value -eq 65536)
    {
        Write-Host "Allocation size for " $Drive " = " $AllocSize.Value " bytes" -ForegroundColor Green
    }
    else
    {
        Write-Host "Allocation size for " $Drive " = " $AllocSize.Value " bytes (Recommendation is 64K)" -ForegroundColor Red
    }
}                                                                                                    

If you are not using Premium IO (Standard IO), caching should be disabled on all data disks. For production scenarios, it is recommended that you use Premium IO, as it is built for handling parallel, high queue depth IO workloads. Applications with highly concurrent and IO intensive workloads will see consistently high performance throughput .

Remember that testing is only as good as the environment. Benchmarking on low capability hardware gives low benchmarks and similarly benchmarking on low tier Azure VMs will provide inaccurate bottlenecks and benchmark numbers. The beauty of Azure is that you can turn off your test environment as soon as the test is completed and this lets you keep an eye out on the monthly bill without optimizing test quality. At the same time, you can maintain copies of the production environment at a fraction of the cost of what it would have been on-premises.

Listing 7-5. PowerShell Code to Check if Files Are Hosted on the Temporary Drive

$RulePass = 1
$sqlquery = "select distinct substring(physical_name,1,2) as drive,db_name(database_id) as dbname, name, physical_name from sys.master_files where substring(physical_name,2,1) = ':'"
$sqlDrives = Invoke-Sqlcmd -ServerInstance $sqlserver -Query $sqlquery
$Files = $sqlDrives | Where-Object {$_.drive -eq $TempDrive -and $_.dbname -ne "tempdb"}

foreach ($file in $Files)
{
    Write-Host "[ERR] Database file" $file.name "(physical file:" $file.physical_name ") for database" $file.dbname "is hosted on the temporary drive" -ForegroundColor Red
    $RulePass = 0
}


if ($RulePass -eq 1)
{
    Write-Host "[INFO] No files found on the temporary drive" $TempDrive -ForegroundColor Green
}
else
{
    Write-Host "[ERR] Any data stored on" $TempDrive "drive is SUBJECT TO LOSS and THERE IS NO WAY TO RECOVER IT." -ForegroundColor Red
    Write-Host "[ERR] Please do not use this disk for storing any personal or application data." -ForegroundColor Red
}

Storage Spaces

Storage spaces are common configuration patterns used during the VM provisioning. If more than one disk is required for your specified settings, the latest gallery image templates create one Windows storage space (virtual drive) across all disks. A thought might have crossed your mind about the necessity to complicate the configuration. A number of databases hosted on-premises have a single database file that cannot be split evenly into multiple files at the drop of a hat. This might be due to the lack of partitioned data, which would could possibly make a single file the hotspot.

If you are using Windows Server 2012 or above, you can use storage spaces to group two or more drives together in a storage pool and then use capacity from that pool to create virtual drives called storage spaces. This feature of Windows allows you to absolve yourself from the need to split your database into multiple files. However, there are a few quirks that you need to be aware of.

The first quirk to remember is to configuring the number of columns correctly for the storage pool. Increasing the number of columns can significantly improve performance for sequential workloads. Random workloads do not experience as significant a performance increase, exhibiting more uniform performance across different column counts. Another factor that ties in directly with the column count of the space is the amount of outstanding IOs or data that is to be read from or written to the storage space. A large number of columns benefits applications that generate enough load to saturated multiple disks, but introduce unnecessary limitations on capacity expansion for less demanding applications. If there are more than eight data disks, you need to use PowerShell scripts to adjust the number of columns appropriately, as shown in Listing 7-6.

The second quirk to remember is the Resiliency setting. Storage spaces offer three resiliency settings: simple, parity, and mirror. If you select mirror, your data disk will end up performing two IO operations for a single IO generated by the SQL Server instance. Mirroring is recommended for resiliency, which can eat up your IO bandwidth on Azure. If you are only concerned about protecting the virtual machine from disk failures, this is already done under the hood by Azure Storage (details in Chapter 3), where redundant copies of the blobs hosting the data files are created by default.

Listing 7-6. PowerShell Script Example to Create a Virtual Disk Storage Pool with a Column Count of 2 and Simple Resiliency Setting

New-VirtualDisk -StoragePoolFriendlyName CompanyData -FriendlyName BusinessCritical -ResiliencySettingName simple -Size 1TB -ProvisioningType Thin -NumberOfColumns 2 -Interleave 65536                                          

Tempdb

SQL Server’s temporary database has been the victim of poorly optimized queries that spill over operations to the tempdb. The performance problems that plague tempdb are not a unique issue isolated to Azure VM. Tempdb performance is a source of constant headache if configured incorrectly. To ensure that your tempdb stays happy, enable trace flag 1118 (-T1118) to prevent resource allocation contention during the creation of temp objects by concurrent threads.

It is a good idea to increase the number of data files in tempdb to maximize disk bandwidth and to reduce contention in allocation structures. As a general rule, if the number of logical processors is less than or equal to eight, use the same number of data files as logical processors. If the number of logical processors is greater than eight, use eight data files. If contention continues, increase the number of data files by multiples of four (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code. If your virtual machine's temporary drive is a SSD, hosting the tempdb on the temporary disk is recommend to increase performance throughput.

Figure 7-1 shows clearly that when the tempdb is hosted on standard disks, the performance is not consistent and the latency for certain IOs could be very high. This would lead to erratic performance, which would be unpalatable for mission- or business-critical workloads. When hosting the tempdb on SSD or Premium IO disks, the performance is consistent and the deviations are negligible.

Figure 7-1. Tempdb performance comparison of SSD, Premium IO, and standard disks

In summary, if your application likes consistent tempdb performance, picking the SSD or Premium IO is the best bet.

Database Settings

The earlier sections talked in detail about how knowing your IO patterns and the IO bandwidth required helps you deploy the Azure VM with the correct configuration. This in turn allows you to get the best bang for your buck! There are also a few database level settings that help optimize for good performance throughput.

Database page compression helps reduce the IO, which is true for all environments but in Azure it definitely helps boost the performance for chatty systems.

If you are an avid reader of blogs, you would have seen multiple SQL Server professionals discouraging the use of Auto Shrink and Auto Close for a database; this is also true for databases hosted on SQL Server instances running on Azure VMs. Listing 7-7 shows how to determine if AUTO CLOSE and AUTO SHRINK are enabled for any database hosted on a SQL Server instance.

Listing 7-7. PowerShell Code to Determine if AUTO CLOSE and AUTO SHRINK Are Enabled on Any Database

$RulePass = 1
$sqlquery = "select name, is_auto_shrink_on, is_auto_close_on from sys.databases"

# Execute a query against the SQL Server instance
$dbs = Invoke-Sqlcmd -ServerInstance $sqlserver -Query $sqlquery

# Loop through the database properties
foreach ($db in $dbs)
{
    if ($db.is_auto_shrink_on -eq $true)
    {
        Write-Host "[ERR] Database" $db.name "has Auto Shrink turned on" -ForegroundColor Red
        $RulePass = 0
    }
    if ($db.is_auto_close_on -eq $true)
    {
        Write-Host "[ERR] Database" $db.name "has Auto Close turned on" -ForegroundColor Red
        $RulePass = 0
    }
}                                                                              

if ($RulePass -eq 1)
{
    Write-Host "[INFO] No databases found with Auto Close and Auto Shrink turned on" -ForegroundColor Green
}

A few housekeeping tasks that you may need to perform after SQL Server is installed on your virtual machine are:

1. Change the default location of the database data and log files to ensure that new database files are hosted on the data disks attached to the virtual machine that were configured for hosting SQL Server database files.

2. Move the system database files to the data disks.

3. Move your error log, trace file locations, and extended events file targets to the data disks.

Service Account Privileges

There has been an eternal debate on the web about enabling the “lock pages in memory” privilege for the SQL Server service account. Lock pages in memory is a Windows policy that determines which account can use a process to keep memory allocations pinned in physical memory. It prevents the system from paging the data to virtual memory on disk. When the SQL Server service account is granted this user right, buffer pool memory cannot be paged out by Windows. For SQL Server instances running on Azure VMs, it is recommended that you provide the lock pages in memory security privilege to the SQL Server service account. This prevents the working set of the SQL Server process to get paged to disk. The biggest downside to the obvious performance hit of getting paged is that the page file of the virtual machine by default resides on the locally attached disks. Listing 7-8 shows how to determine if the lock pages in memory security privilege is granted to the SQL Server service account .

Listing 7-8. PowerShell Script to Determine if Lock Pages in Memory Privilege Is Granted to the SQL Server Service Account

$RulePass = 1
$sqlquery = "SELECT locked_page_allocations_kb FROM sys.dm_os_process_memory"


# Execute a query against the SQL Server instance
$lpim = Invoke-Sqlcmd -ServerInstance $sqlserver -Query $sqlquery

if ($lpim.locked_page_allocations_kb -eq "0")
{
        Write-Host "[WARN] Lock Pages in Memory security privilege is not granted to the SQL Server service account" -ForegroundColor Red
        $RulePass = 0
}
if ($RulePass -eq 1)
{
    Write-Host "[INFO] Lock Pages in Memory Security Privilege is granted to the SQL Server service account" -ForegroundColor Green
}

Another easy performance win is to grant the SQL Server service account the “Performance Volume Maintenance Tasks” security privilege. When the SQL Server service account has this privilege, it allows the SQL Server to perform instant file initialization. This allows SQL Server to create data files without having to zero out the pages. If you do not have any security compliance requirement that prevents you from doing this, you should look at providing this security privilege to the SQL Server service account. Without this privilege, it can take as long as 10 times longer to create a database file! Figure 7-2 illustrates the difference between having and not having the privilege. Such an illustration is best left to this book and should not be determined in production environments. Listing 7-9 shows how to determine if the SQL Server service account has the privilege to perform instant file initialization for data files. It works for any instance of SQL Server.

Figure 7-2. Performance impact of instant file initialization

Listing 7-9. PowerShell Script to Determine if the SQL Service Account Has the Ability to Perform Instant File Initialization

# Find out the SQL Server services installed on the machine
$SqlService = Get-WmiObject -Query "SELECT * FROM Win32_Service WHERE PathName LIKE '%sqlservr%'"

# Export the secpol privileges on the machine to a file
$filename  = "secpol.inf"
$secpol = secedit /export /cfg $filename | Out-Null
$secpol = Get-Content $filename
# Search for the volumne maintenance task privilege in the output
$IFI = Select-String -Path $filename -Pattern "SeManageVolumePrivilege"
# Remove the file
#Remove-Item $filename


# Loop through each SQL Server service found on the machine
foreach ($servcice in $SqlService)
{
    # Find out the SID value of the service account
    $objUser = New-Object System.Security.Principal.NTAccount($servcice.StartName)
    $strSID = $objUser.Translate([System.Security.Principal.SecurityIdentifier])

    # Find out if the SQL Service account SID exists in the output
    if ($IFI.ToString().Contains($strSID.Value))
    {
        Write-Host "[INFO] SQL Server service account ["$servcice.StartName"] has 'Perform Volume Maintenance Task' security privilege" -ForegroundColor Green
    }
    else
    {
        Write-Host "[ERR] SQL Server service account ["$servcice.StartName"] has 'Perform Volume Maintenance Task' security privilege" -ForegroundColor Red
    }
}

To take advantage of instant file initialization, you grant the SQL Server service account SE_MANAGE_VOLUME_NAME and add it to the Perform Volume Maintenance Tasks security policy. After adding the SQL Server service account to the Perform Volume Maintenance Tasks security policy, restart the SQL Server service.

Backups

One of the most common DBA tasks is to ensure that the data is backed up and protected from any sort of disaster. Protecting the data is a sacrosanct job that no DBA can refute. However, the art of backing up databases on an Azure VM is slightly different.

When you do configure the database backups, it is best to use SQL Server’s Backup to Url feature (available in SQL Server 2012 Service Pack 1 Cumulative Update 2 or higher or SQL Server 2014 or SQL Server 2016), as it directly backs up to Azure blog storage. This is not something that you would have normally configured in data centers hosted on-premises, but is a no-brainer for your Azure VM. Another tip to keep in mind is to use the backup compression feature available in SQL Server. This leads to fewer numbers of network bytes transferred between your virtual machine and the blog store.

Figure 7-3 shows that there is a significant throughput and time difference between compressed backups to blob storage (denoted as the cloud) as opposed to uncompressed backups on the disk.

Figure 7-3. Performance comparison of backing up to disk and to blob storage

A glance at Figure 7-3 might make you wonder why there is a need to break the habit of backing up to a backup data disk if one is created for this purpose. On an Azure VM, the primary reason is that backing up to the disk consumes IOPs for your data disk and storage accounts, which have thresholds. You do not want to spend this commodity if it can be avoided, especially for high performance environments. If you concurrently back up multiple databases to a data disk, you could very quickly hit your disk throughput thresholds. And if you are using Premium IO disk for backups, this wouldn't be the best use of your resources and is counter-intuitive from a cost-benefit analysis! Listing 7-10 shows how to determine if backups of the databases are being stored on an Azure disk.

Listing 7-10. PowerShell Script to Check if Compressed Backups to Azure Blobs Are Being Performed on the SQL Server Instance

$sqlquery = "if exists (select TOP 1 physical_device_name from msdb.dbo.backupmediafamily where physical_device_name not like 'http%')
       select 'Disk' as Result
else
        select 'Blob' as Result"

# Execute a query against the SQL Server instance
$backups = Invoke-Sqlcmd -ServerInstance $sqlserver -Query $sqlquery
# Check if backups are being done to BLOBs directly
if ($backups.Result -eq "Disk")
{
        Write-Host "[WARN] Database backups found on local disks" -ForegroundColor Red
        $RulePass = 0
}
if ($RulePass -eq 1)
{
    Write-Host "[INFO] All database backups are being backed up to Azure Blobs" -ForegroundColor Green                                                
}

$sqlquery = "if exists (select top 1 backup_size from msdb.dbo.backupset where compressed_backup_size = backup_size)
        select 'Uncompressed' as Result
else
        select 'Compressed' as Result"

# Execute a query against the SQL Server instance
$backups = Invoke-Sqlcmd -ServerInstance $sqlserver -Query $sqlquery
# Check if backups are being compressed or not
if ($backups.Result -eq "Uncompressed")
{
        Write-Host "[WARN] Uncompressed backups are being performed on this instance" -ForegroundColor Red
        $RulePass = 0
}
if ($RulePass -eq 1)
{
    Write-Host "[INFO] All database backups are using backup compression" -ForegroundColor Green
}