SQL on Azure VM BPCheck


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.

Advertisements

Azure SQL Database Export


In one of my previous posts I had talked about creating or rather restoring a deleted database backup. There is another option in preview called the automated export which allows you to create an automated export copy of your Azure SQL Database. The Azure SQL Database Import/Export Service is a REST-based web service that runs in every Microsoft Azure data center. The service provides a free request queuing service and a free Compute service to perform imports and exports from a Microsoft Azure SQL database to Microsoft Azure binary large object (BLOB) storage. The import and export operations are not a traditional physical database backup but a logical backup of the database that uses a special BACPAC format. This logical BACPAC format lets you avoid having to use a physical format that might vary between versions of SQL Server and SQL Database. Therefore, you can use it to safely restore the database to an SQL database and also to a SQL Server database.

As you can see from the screenshot, the Configure tab in the Azure Management portal for my Azure SQL Database, megatron, has an Export Status set to None.

 

image

As usual, this is a PREVIEW feature and the above is true as of today when I am writing this post. This functionality could change in the future.

Read on to find out what happens when I change the setting to Automatic… Is is as easy as 1-2-3?

Continue reading

Geo Replication meets Azure SQL Database


Active Geo-Replication enables continuous copy of your Premium database across geographic regions worldwide or within the same region. This is akin to setting up an availability group for SQL Server 2012 and above. For this you will need the following:

  • At least two Azure SQL Database Servers will available Premium database quota. The quota available is visible under the SERVERS tab in the Azure Management Portal for SQL Databases. You can alternatively retrieve the available quota by querying the server_quotas view (also in PREVIEW).
  • One or more Azure SQL Database configured on the Premium tier.

When downgrading from a Premium service tier, you must first terminate all Active-Geo Replication relationships. You can follow the steps described in the Terminate a Continuous Copy Relationship topic to stop the replication process between the primary and the active secondary databases.

The performance level of the secondary replica should be the same or higher than the primary replica.

Setting it up

In screenshot 1, you will see that the GEO-REPLICATION tab shown for the database optimus (Yes, another transformer character!) This will be available only if you have signed up for the PREVIEW feature for the new database tiers offered for Azure SQL Database. And if you have a Premium database configured, then you can setup a replica using the ADD REPLICA button which will be available at the bottom of the page.

image

On clicking the ADD REPLICA button, you will be provided with an ADD ACTIVE GEO-REPLICA screen. This allows you to pick another target server which will host the replica copy of your Azure SQL Database. See Screenshot 2.

image

Select the target server where you want your active geo-replica created. Only servers enabled for geo-replication in the same subscription as the source with available Premium database quota are shown. Additionally, only servers enabled for the current reservation size of the source database are shown. Replicas are created with the same database name, edition, maximum size, and reservation size as the source.

In the above screenshot, you can see that I have asked Azure to create a replica of optimus in the North Central US region and I have the primary copy hosted in the South Central US region. Once you proceed with your selection, you will be asked to accept terms and conditions as Premium database hosting attracts a cost!

Once the replication is configured, the Geo Replication tab will show you the status and the configured replicas as shown in Screenshot 3.

image

Reference:

SQL Database Pricing Details
http://azure.microsoft.com/en-us/pricing/details/sql-database/#service-premium

Changing Database Service Tiers and Performance Levels
http://msdn.microsoft.com/library/azure/dn369872.aspx

Active Geo-Replication for Azure SQL Database
http://msdn.microsoft.com/en-us/library/azure/dn741339.aspx