Determining an appropriate performance tier

As an SQL Server DBA, when migrating to Azure SQL Database, you will need to have an initial estimate of DTUs so as to assign an appropriate service tier to Azure SQL Database. An appropriate service tier will ensure that you meet most of your application performance goals. Estimating a lower or a higher service tier will result in decreased performance or increased cost, respectively.

This section teaches you how to use DMA to make an appropriate initial estimate of the service tier. You can, at any time, change your service tier by monitoring SQL Database's performance once it's up and running.

DMA SKU recommendation

DMA is a free tool from Microsoft to facilitate migration from SQL Server (on-premises or IaaS) to SQL Database. It can assess the source database to list out the compatibility issues between SQL Server and SQL Database. Once you fix the compatibility issues, you can use it to migrate the schema and data to SQL Database.

It also helps with recommendations to select a starting service tier and SKU. To get recommendations, we first need to run a PowerShell script to collect the required performance counters. It's advised to run the script for at least two hours at different times and ensure we collect counters at peak business hours.

The activity requires DMA to be installed on your machine. You can download it here: https://www.microsoft.com/download/details.aspx?id=53595.

To get recommendations using DMA for the toystore database, perform the following steps:

1. Open ~/Chapter02/DMA/RunWorkload.bat in Notepad. You should see the following code:

   CD "C:\Program Files\Microsoft Corporation\RMLUtils"
   ostress -SXENA\sql2016 -E -dtoystore -Q"Execute usp_Workload" -n10 -r100000 -q
   @echo off
   Pause

   Modify the RMLUtils directory location if required. Change the ostress parameter to point to the toystore database in your local environment.

   Save and close the file.

   Double-click on the file to run the workload.

2. Open ~/Chapter02/DMA/RunSKURecommendation.bat. You should see the following code:

   cd "C:\Program Files\Microsoft Data Migration Assistant\"
   powershell.exe -File .\SkuRecommendationDataCollectionScript.ps1 -ComputerName XENA -OutputFilePath "C:\Professional-Azure-SQL-Database-Administration-Third-Edition\Chapter02\DMA\Counter.csv" -CollectionTimeInSeconds 7200 -DbConnectionString "Server=XENA\SQL2016;Initial Catalog=master;Integrated Security=SSPI;"

   The preceding command runs the DMA SkuRecommendationDataColletionScript.ps1 PowerShell script to collect the required counters. The script is available at the DMA installation location.

   Modify the parameter values to point the script to your SQL Server environment.

   Save and close the file.

   Double-click RunSKURecommendation.batch to run the sku counter collection script.

   The script will run for the time specified by the CollectionTimeInSeconds parameter and will write the counter values to the file specified by the OutputFilePath parameter.

   To get more appropriate recommendations, it's advised you collect counters for at least two hours. You can also collect counters at different times of the day and generate recommendations to get the best results.

   When the sku collection script completes successfully, a file named counter.csv is generated at the ~/chapter02/DMA location.

3. Open ~/Chapter02/DMA/GetSKURecommendation.batch. You should see the following code:

   cd "C:\Program Files\Microsoft Data Migration Assistant"
   .\DmaCmd.exe /Action=SkuRecommendation /SkuRecommendationInputDataFilePath="C:\Professional-Azure-SQL-Database-Administration-Third-Edition\Chapter02\DMA\Counter.csv" /SkuRecommendationOutputResultsFilePath="C:\Professional-Azure-SQL-Database-Administration-Third-Edition\Chapter02\DMA\SKURecommedation.html" /SkuRecommendationPreventPriceRefresh=true /SkuRecommendationTsvOutputResultsFilePath=C:\Professional-Azure-SQL-Database-Administration-Third-Edition\Chapter02\DMA\SKURecommedation.tsv" 
   @echo off
   Pause

   The preceding command uses the DMA CLI command to generate recommendations. Provide the path to counter.csv in step 2 to the SKURecommendationInputDataFilePath parameter.

   Copy and save the results.

   When run, the command will generate an html and tsv recommendation output file.

   Double-click the GetSKURecommendation.batch file to generate the recommendations.

   The recommendation script will generate skurecommendation_SQL_DB html and tsv files with recommendations for Azure SQL Database. It also generates similar files for SQL Managed Instance.

   Figure 2.10 is a snapshot of the skurecommendation_sql_db.html file:

   

   Figure 2.10: DMA SKU recommendations for SQL Managed Instance

   Observe that it recommends using the General Purpose pricing tier with 8 vCores. You can select the pricing tier from the Pricing Tier drop-down menu.

4. The tsv file contains the reasons for considering or not considering a particular performance tier:
   

Figure 2.11: DMA SKU recommendation—tsv file

The DMA makes it easy to choose a starting service tier when migrating an existing on-premises SQL Server workload to an SQL managed database. Once we migrate the database to the selected service tier, we need to further test the application performance against the service tier and scale up or scale down as per the required performance.

Azure SQL Database compute tiers

There are two compute tiers, provisioned and serverless.

In the provisioned compute tier, the resources (vCores) are pre-allocated and can be changed by manually scaling to a different service tier as and when required. The provisioned compute tier cost is calculated per hour based on the number of vCores configured. The provisioned compute tier is suitable for scenarios with consistent and regular workloads.

In the serverless compute tier, compute resources for databases are automatically scaled based on workload demand and are billed based on the amount of compute used per second. The serverless compute tier also provides an option to automatically pause the database during inactive usage periods, when only storage is billed, and then automatically resume databases when activity returns. The serverless compute tier is price performance-optimized for single databases with intermittent, unpredictable usage patterns that can afford some delay in compute warm-up after low or idle usage periods.

Scaling up the Azure SQL Database service tier

In this section, we'll learn how to scale up the SQL Database service tier for better performance. Let's go back to our example of Mike, who observes that there is an increase in the load on the SQL database. To overcome this problem, he plans to change the service tier for the database so that it can handle the overload. This can be achieved via the following steps:

1. Open a new PowerShell console. In the PowerShell console, execute the following command to create a new SQL database from a bacpac file:

   C:\Code\Chapter02\ImportAzureSQLDB.ps1

2. Provide the SQL server name, SQL database name, SQL Server administrator user and password, bacpac file path, and sqlpackage.exe path, as shown in Figure 2.12:
   

   Figure 2.12: The Windows PowerShell window

   The script will use sqlpackage.exe to import the bacpac file as a new SQL database on the given SQL server. The database is created in the Basic service tier, as specified in the PowerShell script.

   It may take 10 to 15 minutes to import the SQL database.

3. Open C:\Code\Chapter02\ExecuteQuery.bat in Notepad. It contains the following commands:

   ostress -Sazuresqlservername.database.windows.net -Uuser
   -Ppassword -dazuresqldatabase -Q"SELECT * FROM Warehouse.StockItems si join Warehouse.StockItemholdings sh on si.StockItemId=sh.StockItemID join Sales.OrderLines ol on ol.StockItemID = si.StockItemID" –n25 –r20 -1

4. Replace azuresqlservername, user, password, and azuresqldatabase with the appropriate values. For example, if you are running the preceding command against SQL Database with toystore hosted on the toyfactory SQL server with the username sqladmin and the password Packt@pub2, then the command will be as follows:

   ostress -Stoyfactory.database.windows.net -Usqladmin -PPackt@pub2
   -dtoystore -Q"SELECT * FROM Warehouse.StockItems si join Warehouse. StockItemholdings sh on si.StockItemId=sh.StockItemID join Sales.
   OrderLines ol on ol.StockItemID = si.StockItemID" -n25
   -r20 -q

   The command will run 25 (specified by the -n25 parameter) concurrent sessions, and each session will execute the query (specified by the -Q parameter) 20 times.

5. Open the RML command prompt, enter the following command, and press Enter:

   C:\Code\Chapter02\ExecuteQuery.bat

   This will run the OSTRESS command. Wait for the command to finish executing. Record the execution time:

   

   Figure 2.13: RML command prompt

   As you can see, it took around 1 minute and 52 seconds to run 25 concurrent connections against the Basic service tier.

6. The next step is to scale up the service tier from Basic to Standard S3. In the PowerShell console, execute the following command:

   C:\Code\Chapter02\ScaleUpAzureSQLDB.ps1

   Provide the parameters as shown in Figure 2.14:

   

   Figure 2.14: Scaling up the service tier

   Observe that the database edition has been changed to standard.

7. Open a new RML command prompt and run the same OSTRESS command as in step 5. You should see a faster query execution time in the Standard S3 tier than in the Basic tier.

   Here's the output from the ExecuteQuery.bat command:

   

Figure 2.15: Output from the ExecuteQuery.bat command

It took around 42 seconds to run 25 concurrent connections against the Standard S3 service tier. This is almost 60% faster than the Basic tier. You get the performance improvement just by scaling up the service tier, without any query or database optimization.

Changing a service tier

You can scale up or scale down SQL Database at any point in time. This gives the flexibility to save money by scaling down to a lower service tier in off-peak hours and scaling up to a higher service tier for better performance in peak hours.

You can change a service tier either manually or automatically. Service tier change is performed by creating a replica of the original database at the new service tier performance level. The time taken to change the service tier depends on the size as well as the service tier of the database before and after the change.

Once the replica is ready, the connections are switched over to the replica. This ensures that the original database is available for applications during the service tier change. This also causes all in-flight transactions to be rolled back during the brief period when the switch to the replica is made. The average switchover time is four seconds, and it may increase if there are a large number of in-flight transactions.

You may have to add retry logic in the application to manage connection disconnect issues when changing a service tier.