Microsoft Azure provides Azure SQL Database, PostgreSQL, and MySQL as Database-as-a-Service offerings. We can create an instance of these databases without worrying about the installation, administration, infrastructure, and upgrades.
Needless to say that we can install any of the available Relational Database Management System (RDBMS) databases, such as Oracle or DB2, on an Azure virtual machine (VM).
In a data pipeline, we can use any of the RDBMS databases as either a source or a destination. In this chapter, we'll learn how to provision, connect to, manage, maintain, and secure these databases.
We'll cover the following recipes in this chapter:
In this recipe, we'll learn how to create and connect to an Azure SQL Database instance. Azure SQL Database comes in three failovers: standalone Azure SQL Database, Azure SQL Database elastic pools, and managed instances. In this recipe, we'll create a standalone Azure SQL database.
In a new PowerShell window, execute the Connect-AzAccount
command to log in to your Microsoft Azure account.
Let's begin by provisioning Azure SQL Database.
The steps are as follows:
New-AzResourceGroup -Name packtade -Location "central us"
#create credential object for the Azure SQL Server admin credential $sqladminpassword = ConvertTo-SecureString 'Sql@Server@1234' -AsPlainText -Force $sqladmincredential = New-Object System.Management.Automation.PSCredential ('sqladmin', $sqladminpassword) # create the azure sql server New-AzSqlServer -ServerName azadesqlserver -SqlAdministratorCredentials $sqladmincredential -Location "central us" -ResourceGroupName packtade
You should get a similar output as shown in the following screenshot:
New-AzSqlDatabase -DatabaseName azadesqldb -Edition basic -ServerName azadesqlserver -ResourceGroupName packtade
You should get an output as shown in the following screenshot:
To connect to an Azure SQL database, let's first whitelist the IP in the Azure SQL Server firewall:
$clientip = (Invoke-RestMethod -Uri https://ipinfo.io/json).ip New-AzSqlServerFirewallRule -FirewallRuleName "home" -StartIpAddress $clientip -EndIpAddress $clientip -ServerName azadesqlserver -ResourceGroupName packtade
You will get an output similar to the one shown in the following screenshot:
sqlcmd -S "azadesqlserver.database.windows.net" -U sqladmin -P "Sql@Server@1234" -d azadesqldb
We first execute the New-AzSQLServer
command to provision a new Azure SQL server. The command accepts the server name, location, resource group, and login credentials. An Azure SQL server, unlike an on-premises SQL server, is not a physical machine or VM that is accessible to customers.
We then execute the New-AzSQLDatabase
command to create an Azure SQL database. This command accepts the database name, the Azure SQL server name, the resource group, and the edition. There are multiple SQL database editions to choose from based on the application workload. However, for the sake of this demo, we will create a basic edition.
To connect to an Azure SQL database, we first need to whitelist the machine's IP in the Azure SQL Server firewall. Only whitelisted IPs are allowed to connect to the database. To whitelist the client's public IP, we use the New-AzSQLServerfirewallrule
command. This command accepts the server name, resource group, and start and end IPs. We can either whitelist a single IP or a range of IPs.
We can connect to an Azure SQL database from SQL Server Management Studio, SQLCMD, or a programming language using the appropriate SQL Server drivers. When connecting to an Azure SQL database, we need to specify the server name as azuresqlservername.database.windows.net
, and then specify the Azure SQL database to connect to.
Azure Database for PostgreSQL is a Database-as-a-Service offering for the PostgreSQL database. In this recipe, we'll learn how to provision an Azure database for PostgreSQL and connect to it.
We'll be using the Azure CLI for this recipe. Open a new Command Prompt or PowerShell window, and run az login
to log in to the Azure CLI.
Let's begin with provisioning a new Azure PostgreSQL server.
az group create --name rgpgressql --location eastus
az postgres server create --resource-group rgpgressql --name adepgresqlserver --location eastus --admin-user pgadmin --admin-password postgre@SQL@1234 --sku-name B_Gen5_1
Note
It may take 10–15 minutes for the server to be created.
$clientip = (Invoke-RestMethod -Uri https://ipinfo.io/json).ip az postgres server firewall-rule create --resource-group rgpgressql --server adepgresqlserver --name hostip --start-ip-address $clientip --end-ip-address $clientip
We can connect to an Azure PostgreSQL server using psql
or pgadmin
(a GUI tool for PostgreSQL management), or from any programming language using a relevant driver.
To connect from psql
, execute the following command in a Command Prompt or PowerShell window:
PS C:\Program Files\PostgreSQL\12\bin> .\psql.exe --host=adepgresqlserver.postgres.database.azure.com --port=5432 --username=pgadmin@adepgresqlserver --dbname=postgres
Provide the password and you'll be connected. You should get an output similar to the one shown in the following screenshot:
To provision a new Azure PostgreSQL server, execute the following Azure CLI command – az postgres server create
. We need to specify the server name, resource group, administrator username and password, location, and SKU name parameters. As of now, there are three different SKUs:
B_Gen5_1
is the basic and smallest SKU, up to 2 vCores.GP_Gen5_32
is the general-purpose SKU, up to 64 vCores.MO_Gen5_2
is the memory-optimized SKU, with 32 memory-optimized vCores.Note
For more information on the pricing tiers, visit https://docs.microsoft.com/en-us/azure/postgresql/concepts-pricing-tiers.
To connect to the PostgreSQL server, we first need to whitelist the IP in the server firewall. To do that, we run the az postgres server firewall-rule create
Azure CLI command.
We need to provide the firewall rule name, server name, resource group, and start and end IP.
Once the firewall rule is created, the PostgreSQL server can be accessed by any of the utilities, such as psql
or pgadmin
, or from a programming language. To connect to the server, provide the host or server name as <postgresql server name>.postgres.database.azure.com
and the port as 5432
. We also need to provide the username and password. If you are connecting for the first time, provide the database name as postgres
.
Azure Database for MySQL is a Database-as-a-Service offering for the MySQL database. In this recipe, we'll learn how to provision an Azure database for MySQL and connect to it.
We'll be using the Azure CLI for this recipe. Open a new Command Prompt or PowerShell window, and run az login
to log in to the Azure CLI.
Let's see how to provision the Azure MySQL server.
az group create --name rgmysql --location eastus
az mysql server create --resource-group rgmysql --name ademysqlserver --location eastus --admin-user dbadmin --admin-password mySQL@1234 --sku-name B_Gen5_1
You should get an output as shown in the following screenshot:
$clientip = (Invoke-RestMethod -Uri https://ipinfo.io/json).ip az mysql server firewall-rule create --resource-group rgmysql --server ademysqlserver --name clientIP --start-ip-address $clientip --end-ip-address $clientip
You should get an output as shown in the following screenshot:
.\mysqlsh.exe -h ademysqlserver.mysql.database.azure.com -u dbadmin@ademysqlserver -p
Here's the output:
To provision a new Azure MySQL server, execute the following Azure CLI command – az mysql server create
. We need to specify the server name, resource group, administrator username and password, location, and SKU name parameters. As of now, there are three different SKUs:
B_Gen5_1
is the basic and smallest SKU, up to 2 vCores.GP_Gen5_32
is the general-purpose SKU, up to 64 vCores.MO_Gen5_2
is the memory-optimized SKU, with 32 memory-optimized vCores.To connect to the MySQL server, we first need to whitelist the IP in the server firewall. To do that, we run the az mysql server firewall-rule create
Azure CLI command.
We need to provide the firewall rule name, server name, resource group, and start and end IPs.
Once the firewall rule is created, the MySQL server can be accessed by any of the utilities, such as the MySQL command line or the MySQL workbench, or from a programming language. To connect to the server, provide the host or server name as <mysql server name>.mysql.database.azure.com
. We also need to provide the username and password.
The active geo-replication feature allows you to create up to four readable secondaries of a primary Azure SQL database. Active geo-replication uses SQL Server AlwaysOn to asynchronously replicate transactions to the secondary databases. The secondary database can be in the same or a different region than the primary database.
Active geo-replication can be used for the following cases:
In this recipe, we'll configure active geo-replication for an Azure SQL database and perform a manual failover.
In a new PowerShell window, execute the Connect-AzAccount
command and follow the steps to log in to your Azure account.
You need an existing Azure SQL database for this recipe. If you don't have one, create an Azure SQL database by following the steps mentioned in the Provisioning and connecting to an Azure SQL database using PowerShell recipe.
First, let's create a readable secondary.
#create credential object for the Azure SQL Server admin credential $sqladminpassword = ConvertTo-SecureString 'Sql@Server@1234' -AsPlainText -Force $sqladmincredential = New-Object System.Management.Automation.PSCredential ('sqladmin', $sqladminpassword) New-AzSQLServer -ServerName azadesqlsecondary -SqlAdministratorCredentials $sqladmincredential -Location westus -ResourceGroupName packtade
$primarydb = Get-AzSqlDatabase -DatabaseName azadesqldb -ServerName azadesqlserver -ResourceGroupName packtade $primarydb | New-AzSqlDatabaseSecondary -PartnerResourceGroupName packtade -PartnerServerName azadesqlsecondary -AllowConnections "All"
You should get an output as shown in the following screenshot:
Moreover, we can also check this on the Azure portal, as shown in the following screenshot:
$secondarydb = Get-AzSqlDatabase -DatabaseName azadesqldb -ServerName azadesqlsecondary -ResourceGroupName packtade $secondarydb | Set-AzSqlDatabaseSecondary -PartnerResourceGroupName packtade -Failover
The preceding command performs a planned failover without data loss. To perform a manual failover with data loss, use the Allowdataloss
switch.
If we check the Azure portal, we'll see that azadesqlsecondary/azadesqldb
in West US is the primary database:
Get-AzSqlDatabaseReplicationLink -DatabaseName azadesqldb -PartnerResourceGroupName packtade -PartnerServerName azadesqlsecondary -ServerName azadesqlserver -ResourceGroupName packtade
You should get an output as shown in the following screenshot:
Execute the following command to remove the active geo-replication link between the primary and the secondary databases:
$primarydb = Get-AzSqlDatabase -DatabaseName azadesqldb -ServerName azadesqlserver -ResourceGroupName packtade $primarydb | Remove-AzSqlDatabaseSecondary -PartnerResourceGroupName packtade -PartnerServerName azadesqlsecondary
You should get an output as shown in the following screenshot:
To configure active geo-replication, we use the New-AzSqlDatabaseSecondary
command. This command expects the primary database name, server name, and resource group name, and the secondary resource group name, server name, and the Allow connections
parameter. If we want a readable secondary, then we set Allow connections
to All
; otherwise, we set it to No
.
The active geo-replication provides manual failover with and without data loss. To perform a manual failover, we use the Set-AzSqlDatabaseSecondary
command. This command expects the secondary server name, database name, resource group name, a failover switch, and the Allowdataloss
switch in case of failover with data loss.
To remove active geo-replication, we use the Remove-AzSqlDatabaseSecondary
command. This command expects the secondary server name, secondary database name, and resource name to remove the replication link between the primary and the secondary database.
Removing active geo-replication doesn't remove the secondary database.
An auto-failover group allows a group of databases to fail to a secondary server in another region in case the SQL database service in the primary region fails. Unlike active geo-replication, the secondary server should be in a different region than the primary. The secondary databases can be used to offload read workloads.
The failover can be manual or automatic.
In this recipe, we'll create an auto-failover group, add databases to the auto-failover group, and perform a manual failover to the secondary server.
In a new PowerShell window, execute the Connect-AzAccount
command and follow the steps to log in to your Azure account.
You will need an existing Azure SQL database for this recipe. If you don't have one, create an Azure SQL database by following the steps mentioned in the Provisioning and connecting to an Azure SQL database using PowerShell recipe.
First, let's create an auto-failover group.
$sqladminpassword = ConvertTo-SecureString 'Sql@Server@1234' -AsPlainText -Force $sqladmincredential = New-Object System.Management.Automation.PSCredential ('sqladmin', $sqladminpassword) New-AzSQLServer -ServerName azadesqlsecondary -SqlAdministratorCredentials $sqladmincredential -Location westus -ResourceGroupName packtade
New-AzSqlDatabaseFailoverGroup -ServerName azadesqlserver -FailoverGroupName adefg -PartnerResourceGroupName packtade -PartnerServerName azadesqlsecondary -FailoverPolicy Automatic -ResourceGroupName packtade
You should get an output as shown in the following screenshot:
$db = Get-AzSqlDatabase -DatabaseName azadesqldb -ServerName azadesqlserver -ResourceGroupName packtade $db | Add-AzSqlDatabaseToFailoverGroup -FailoverGroupName adefg
$db = New-AzSqlDatabase -DatabaseName azadesqldb2 -Edition basic -ServerName azadesqlserver -ResourceGroupName packtade $db | Add-AzSqlDatabaseToFailoverGroup -FailoverGroupName adefg
Get-AzSqlDatabaseFailoverGroup -ServerName azadesqlserver -FailoverGroupName adefg -ResourceGroupName packtade
You should get an output as shown in the following screenshot:
The endpoint used to connect to the primary server of an auto-failover group is of the form <auto-failover group name>.database.windows.net
. In our case, this will be adefg.database.windows.net
.
To connect to a readable secondary in an auto-failover group, the endpoint used is of the form <auto-failover group name>.secondary.database.windows.net
. In our case, the endpoint will be adefg.secondary.database.windows.net
. In addition to this, we need to specify ApplicationIntent
as readonly
in the connection string when connecting to the readable secondary.
$secondarysqlserver = Get-AzSqlServer -ResourceGroupName packtade -ServerName azadesqlsecondary $secondarysqlserver | Switch-AzSqlDatabaseFailoverGroup -FailoverGroupName adefg
If we check in the Azure portal, the primary server is now azadesqlsecondary
and the secondary server is azadesqlserver
, as shown in the following screenshot:
Remove-AzSqlDatabaseFailoverGroup -ServerName azadesqlsecondary -FailoverGroupName adefg -ResourceGroupName packtade
You should get an output as shown in the following screenshot:
The New-AzSqlDatabaseFailoverGroup
command is used to create an auto-failover group. We need to specify the auto-failover group name, the primary and secondary server names, the resource group name, and the failover policy (automatic/manual). In addition to this, we can also specify GracePeriodWithDataLossHours
. As the replication between the primary and secondary is synchronous, the failover may result in data loss. The GracePeriodwithDataLossHours
value specifies how many hours the system should wait before initiating the automatic failover. This can, therefore, limit the data loss that can happen because of a failover.
After the auto-failover group creation, we can add the databases to the auto-failover group by using the Add-AzSqlDatabaseToFailoverGroup
command. The database to be added should exist on the primary server and not on the secondary server.
We can perform a manual failover by executing the Switch-AzSqlDatabaseFailoverGroup
command. We need to provide the primary server name, the auto-failover group name, and the primary server resource group name.
To remove an auto-failover group, execute the Remove-AzSqlDatabaseFailoverGroup
command by specifying the primary server name and resource group and the auto-failover group name.
An Azure SQL database has multiple purchase model and service tiers for different workloads. There are two purchasing models: DTU-based and vCore-based. There are multiple service tiers within the purchasing models.
Having multiple service tiers gives the flexibility to scale up or scale down based on the workload or activity in an Azure SQL database.
In this recipe, we'll learn how to automatically scale up an Azure SQL database whenever the CPU percentage is above 40%.
In a new PowerShell window, execute the Connect-AzAccount
command and follow the steps to log in to your Azure account.
You will need an existing Azure SQL database for this recipe. If you don't have one, create an Azure SQL database by following the steps mentioned in the Provisioning and connecting to an Azure SQL database using PowerShell recipe.
The steps for this recipe are as follows:
#Create an Azure automation account $automation = New-AzAutomationAccount -ResourceGroupName packtade -Name adeautomate -Location centralus -Plan Basic
#Create a new automation runbook of type PowerShell workflow $runbook = New-AzAutomationRunbook -Name rnscalesql -Description "Scale up sql azure when CPU is 40%" -Type PowerShellWorkflow -ResourceGroupName packtade -AutomationAccountName $automation.AutomationAccountName
#Create automation credentials. $sqladminpassword = ConvertTo-SecureString 'Sql@Server@1234' -AsPlainText -Force $sqladmincredential = New-Object System.Management.Automation.PSCredential ('sqladmin', $sqladminpassword) $creds = New-AzAutomationCredential -Name sqlcred -Description "sql azure creds" -ResourceGroupName packtade -AutomationAccountName $automation.AutomationAccountName -Value $sqladmincredential
adeautomate
automation account:rnscalesql
runbook to open the runbook page. On the runbook page, select Edit:workflow rnscalesql { param ( # Name of the Azure SQL Database server (Ex: bzb98er9bp) [parameter(Mandatory=$true)] [string] $SqlServerName, # Target Azure SQL Database name [parameter(Mandatory=$true)] [string] $DatabaseName, # When using in the Azure Automation UI, please enter the name of the credential asset for the "Credential" parameter [parameter(Mandatory=$true)] [PSCredential] $Credential ) inlinescript { $ServerName = $Using:SqlServerName + ".database.windows.net" $db = $Using:DatabaseName $UserId = $Using:Credential.UserName $Password = ($Using:Credential).GetNetworkCredential().Password $ServerName $db $UserId $Password $MasterDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection $MasterDatabaseConnection.ConnectionString = "Server = $ServerName; Database = Master; User ID = $UserId; Password = $Password;" $MasterDatabaseConnection.Open(); $MasterDatabaseCommand = New-Object System.Data.SqlClient.SqlCommand $MasterDatabaseCommand.Connection = $MasterDatabaseConnection $MasterDatabaseCommand.CommandText = " ALTER DATABASE $db MODIFY (EDITION = 'Standard', SERVICE_OBJECTIVE = 'S0'); " $MasterDbResult = $MasterDatabaseCommand.ExecuteNonQuery(); } }
The preceding code modifies the service tier of the given Azure SQL database to Standard S0.
# define the runbook parameters $Params = @{"SQLSERVERNAME"="azadesqlserver";"DATABASENAME"="azadesqldb";"CREDENTIAL"="sqlcred"} # Create a webhook $expiry = (Get-Date).AddDays(1) New-AzAutomationWebhook -Name whscaleazure -RunbookName $runbook.Name -Parameters $Params -ResourceGroupName packtade -AutomationAccountName $automation.AutomationAccountName -IsEnabled $true -ExpiryTime $expiry
Note
When defining $Params
, you may want to change the default values mentioned here if you have a different Azure SQL server, database, and cred values.
You should get an output as shown in the following screenshot:
#Create action group reciever $whr = New-AzActionGroupReceiver -Name agrscalesql -WebhookReceiver -ServiceUri "https://s25events.azure-automation.net/webhooks?token=NfL30nj%2fkuSo8TTT7CqDwRI WEdeXR1lklkK%2fzgELCiY%3d"
Note
Replace the value of the ServiceUri
parameter with your webhook URI from the previous step.
#Create a new action group. $ag = Set-AzActionGroup -ResourceGroupName packtade -Name ScaleSQLAzure -ShortName scaleazure -Receiver $whr
#define the alert trigger condition $condition = New-AzMetricAlertRuleV2Criteria -MetricName "cpu_percent" -TimeAggregation maximum -Operator greaterthan -Threshold 40 -MetricNamespace "Microsoft.Sql/servers/databases"
The condition defines that the alert should trigger when the metric CPU percentage is greater than 40%.
#Create the alert with the condition and action defined in previous steps. $rid = (Get-AzSqlDatabase -ServerName azadesqlserver -ResourceGroupName packtade -DatabaseName azadesqldb).Resourceid Add-AzMetricAlertRuleV2 -Name monitorcpu -ResourceGroupName packtade -WindowSize 00:01:00 -Frequency 00:01:00 -TargetResourceId $rid -Condition $condition -Severity 1 -ActionGroupId $ag.id
You should get an output as shown in the following screenshot:
The preceding command creates an Azure SQL database alert. The alert is triggered when the cpu_percent
metric is greater than 40% for more than 1 minute. When the alert is triggered, as defined in the action group, the webhook is called. The webhook in turn runs the runbook. The runbook modifies the service tier of the database to Standard S0.
--query to simulate high CPU usage While(1=1) Begin Select cast(a.object_id as nvarchar(max)) from sys.objects a, sys.objects b,sys.objects c, sys.objects d End
As soon as the alert condition is triggered, the webhook is called and the database service tier is modified to Standard S0.
To configure automatic scaling for an Azure SQL database, we create an Azure Automation runbook. The runbook specifies the PowerShell code to modify the service tier of an Azure SQL database.
We create a webhook to trigger the runbook. We create an Azure SQL database alert and define the alert condition to trigger when the cpu_percent
metric is greater than 40% for at least 1 minute. In the alert action, we call the webhook defined earlier.
When the alert condition is reached, the webhook is called, which in turn executes the runbook, resulting in the Azure SQL database service tier change.
An elastic pool is a cost-effective mechanism to group single Azure SQL databases of varying peak usage times. For example, consider 20 different SQL databases with varying usage patterns, each Standard S3 requiring 100 database throughput units (DTUs) to run. We need to pay for 100 DTUs separately. However, we can group all of them in an elastic pool of Standard S3. In this case, we only need to pay for elastic pool pricing and not for each individual SQL database.
In this recipe, we'll create an elastic pool of multiple single Azure databases.
In a new PowerShell window, execute the Connect-AzAccount
command and follow the steps to log in to your Azure account.
The steps for this recipe are as follows:
#create credential object for the Azure SQL Server admin credential $sqladminpassword = ConvertTo-SecureString 'Sql@Server@1234' -AsPlainText -Force $sqladmincredential = New-Object System.Management.Automation.PSCredential ('sqladmin', $sqladminpassword) # create the azure sql server New-AzSqlServer -ServerName azadesqlserver -SqlAdministratorCredentials $sqladmincredential -Location "central us" -ResourceGroupName packtade Execute the following query to create an elastic pool. #Create an elastic pool New-AzSqlElasticPool -ElasticPoolName adepool -ServerName azadesqlserver -Edition standard -Dtu 100 -DatabaseDtuMin 20 -DatabaseDtuMax 100 -ResourceGroupName packtade
You should get an output as shown in the following screenshot:
#Create a new database in elastic pool New-AzSqlDatabase -DatabaseName azadedb1 -ElasticPoolName adepool -ServerName azadesqlserver -ResourceGroupName packtade
You should get an output as shown in the following screenshot:
#Create a new database outside of an elastic pool New-AzSqlDatabase -DatabaseName azadedb2 -Edition Standard -RequestedServiceObjectiveName S3 -ServerName azadesqlserver -ResourceGroupName packtade
You should get an output as shown in the following screenshot:
adesqldb2
database created in the preceding step to the elastic pool:#Add an existing database to the elastic pool $db = Get-AzSqlDatabase -DatabaseName azadedb2 -ServerName azadesqlserver -ResourceGroupName packtade $db | Set-AzSqlDatabase -ElasticPoolName adepool
You should get an output as shown in the following screenshot:
#remove a database from an elastic pool $db = Get-AzSqlDatabase -DatabaseName azadesqldb2 -ServerName azadesqlserver -ResourceGroupName packtade $db | Set-AzSqlDatabase -Edition Standard -RequestedServiceObjectiveName S3
You should get an output as shown in the following screenshot:
# get elastic pool object $epool = Get-AzSqlElasticPool -ElasticPoolName adepool -ServerName azadesqlserver -ResourceGroupName packtade # get all databases in an elastic pool $epdbs = $epool | Get-AzSqlElasticPoolDatabase # change the edition of all databases in an elastic pool to standard S3 foreach($db in $epdbs) { $db | Set-AzSqlDatabase -Edition Standard -RequestedServiceObjectiveName S3 } # Remove an elastic pool $epool | Remove-AzSqlElasticPool
Note
The command sets the edition of the SQL databases to Standard. This is for demo purposes only. If this is to be done on production, modify the edition and service objective accordingly.
We create an elastic pool using the New-AzSqlElasticPool
command. In addition to the parameters, such as the server name, resource group name, compute model, compute generation, and edition, which are the same as when we create a new Azure SQL database, we can specify DatabaseMinDtu
and DatabaseMaxDtu
. DatabaseMinDtu
specifies the minimum DTU that all databases can have in an elastic pool. DatabaseMaxDtu
is the maximum DTU that a database can consume in an elastic pool.
Similarly, for the vCore-based purchasing model, we can specify DatabaseVCoreMin
and DatabaseVCoreMax
.
To add a new database to an elastic pool, specify the elastic pool name at the time of database creation using the New-AzSqlDatabase
command.
To add an existing database to an elastic pool, modify the database using Set-AzSqlDatabase
to specify the elastic pool name.
To remove a database from an elastic pool, modify the database using the Set-AzSqlDatabase
command to specify a database edition explicitly.
To remove an elastic pool, first empty it by moving out all of the databases from the elastic pool, and then remove it using the Remove-AzSqlElasticPool
command.
Azure SQL Database has built-in monitoring features, such as query performance insights, performance overview, and diagnostic logging. In this recipe, we'll learn how to use the monitoring capabilities using the Azure portal.
We'll use PowerShell to create an Azure SQL database, so open a PowerShell window and log in to your Azure account by executing the Connect-AzAccount
command.
We'll use the Azure portal to monitor the Azure SQL database. Open https://portal.azure.com and log in to your Azure account.
First, let's execute a sample workload.
AdventureWorksLT
sample database:# create the resource group New-AzResourceGroup -Name packtade -Location "central us" -force #create credential object for the Azure SQL Server admin credential $sqladminpassword = ConvertTo-SecureString 'Sql@Server@1234' -AsPlainText -Force $sqladmincredential = New-Object System.Management.Automation.PSCredential ('sqladmin', $sqladminpassword) # create the azure sql server New-AzSqlServer -ServerName azadesqlserver -SqlAdministratorCredentials $sqladmincredential -Location "central us" -ResourceGroupName packtade #Create the SQL Database New-AzSqlDatabase -DatabaseName adeawlt -Edition basic -ServerName azadesqlserver -ResourceGroupName packtade -SampleName AdventureWorksLT
$clientip = (Invoke-RestMethod -Uri https://ipinfo.io/json).ip New-AzSqlServerFirewallRule -FirewallRuleName "home" -StartIpAddress $clientip -EndIpAddress $clientip -ServerName azadesqlserver -ResourceGroupName packtade
sqlcmd -S azadesqlserver.database.windows.net -d adeawlt -U sqladmin -P Sql@Server@1234 -i "C:\ADECookbook\Chapter02\workload.sql" > "C:\ADECookbook\Chapter02\workload_output.txt"
It can take 4–5 minutes for the workload to complete. You can execute the preceding command multiple times; however, you should run it at least once.
The Metrics page allows you to monitor different available metrics over time.
We can select the metrics we are interested in monitoring and use the Pin to dashboard feature to pin the chart to the portal dashboard. We can also create an alert rule from the metrics page by clicking on New alert rule. We can select a time range to drill down to specific times or investigates spikes in the chart.
Query Performance Insight is an intelligent performance feature that allows us to find any resource-consuming and long-running queries. The steps are as follows:
Resource Consuming Queries lists out the top three queries by CPU consumption. We can also select the top three queries by Data IO and Log IO. The bottom of the page lists out the color-coded queries.
We can look at the query text and optimize it for better performance.
We can further look into the query text and other details by selecting the query ID.
In addition to metrics and query performance insight, we can also monitor an Azure SQL database by collecting diagnostic logs. The diagnostic logs can be sent to the Log Analytics workspace or Azure Storage, or can be streamed to Azure Event Hubs. The steps are as follows:
Note:
Diagnostic setting adds an additional cost to the Azure SQL database. It may take some time for the logs to be available after creating a new diagnostic setting.
Automatic tuning provides three features: force plan, create, and drop indexes. Automatic tuning can be enabled for an Azure SQL server, in which case it's applied to all of the databases in that Azure SQL server. Automatic tuning can be enabled for individual Azure SQL databases as well. The steps are as follows:
Note
It may take time for recommendations to show up.
The recommendations will show up in the performance recommendations under the Intelligent Performance section.
Where there is an eBook version of a title available, you can buy it from the book details for that title. Add either the standalone eBook or the eBook and print book bundle to your shopping cart. Your eBook will show in your cart as a product on its own. After completing checkout and payment in the normal way, you will receive your receipt on the screen containing a link to a personalised PDF download file. This link will remain active for 30 days. You can download backup copies of the file by logging in to your account at any time.
If you already have Adobe reader installed, then clicking on the link will download and open the PDF file directly. If you don't, then save the PDF file on your machine and download the Reader to view it.
Please Note: Packt eBooks are non-returnable and non-refundable.
Packt eBook and Licensing When you buy an eBook from Packt Publishing, completing your purchase means you accept the terms of our licence agreement. Please read the full text of the agreement. In it we have tried to balance the need for the ebook to be usable for you the reader with our needs to protect the rights of us as Publishers and of our authors. In summary, the agreement says:
If you want to purchase a video course, eBook or Bundle (Print+eBook) please follow below steps:
Our eBooks are currently available in a variety of formats such as PDF and ePubs. In the future, this may well change with trends and development in technology, but please note that our PDFs are not Adobe eBook Reader format, which has greater restrictions on security.
You will need to use Adobe Reader v9 or later in order to read Packt's PDF eBooks.
Packt eBooks are a complete electronic version of the print edition, available in PDF and ePub formats. Every piece of content down to the page numbering is the same. Because we save the costs of printing and shipping the book to you, we are able to offer eBooks at a lower cost than print editions.
When you have purchased an eBook, simply login to your account and click on the link in Your Download Area. We recommend you saving the file to your hard drive before opening it.
For optimal viewing of our eBooks, we recommend you download and install the free Adobe Reader version 9.