After a long hiatus, I am back on the blogosphere and have decided to dedicate a series to running Best Practices checks using PowerShell on Azure virtual machines running in the new deployment model: ARM (Azure Resource Manager). If you have worked on the classic deployment model, then you will need to unlearn a few things and re-learn a little more. However, the learning curve ain’t that great. Think of Azure Resource Manager as a container for all the cloud components that make up your solution. If you had a SQL Server instance running on an Azure virtual machine, then you would be using compute, networking, storage components in Azure which would together be encapsulated in a Resource Manager group.
I had previously done a series of posts around running best practices checks on Azure Virtual Machines running SQL Server. Some of those checks are still valid today as they only dealt with the SQL Server instance. You will see components of those scripts reused. Some of the checks are way easier due to the way ARM deployments are managed in Azure and the endpoints that the Azure PowerShell 1.0 exposes.
The PowerShell scripts available on the GitHub repository are mentioned below. I will run a post on each of these scripts to explain what each of these do and what to expect as the output of each of these scripts.
- Get-AllocationUnitCheck.ps1 – Checks if the allocation unit size for the disks attached to the VM is 64K
- Get-DBProperties.ps1 – Checks if any database has AUTO CLOSE or AUTO SHRINK enabled
- Get-FilesOnTemp.ps1 – Checks to see if any database files are hosted on the temporary drive
- Get-IFI.ps1 – Checks to see if the SQL Server service account has instant file initialization security privileges
- Get-LPIM.ps1 – Checks to see if Lock Pages in Memory privilege is granted to the SQL Server service account
- Get-OSFilesDB.ps1 – Checks to see if database files are hosted on the OS drive
- Get-StorageAccountBP.ps1 – Checks to see if the storage account has replication enabled
- Get-VMSize.ps1 – Checks if the right virtual machine tier is being used
- Temporary Drive.ps1 – Finds out the temporary drive on the virtual machine
- Get-Backups.ps1 – Finds out if any backups are being taken to local disk
The PowerShell scripts are available on GitHub repository SqlOnAzureVM. Since these scripts are now on GitHub, please feel free to pull them and enhance them as per your needs.