Aug 28, 2018 by Andrei Buse
SQL Server in the Cloud
A Comprehensive Overview of the SQL Server Databases in the Cloud
I will start at the beginning—what is “cloud computing?” In layman’s terms, cloud computing is the delivery of computing services—servers, storage, databases, networking, software, analytics and more—over the Internet (“the cloud”). Companies offering these computing services are called cloud providers and typically charge for cloud computing services based on usage, similar to how you are billed for water or electricity at home.
Today many corporations perform extensive research in regards to moving from a dedicated server (in-house) environment to running SQL Server databases in the cloud. This trend has largely been based around concerns about deteriorating performance, giving away administrative control of their core systems and applications, and maintaining optimal security for critical business data. Until recently, it was not uncommon for organizations testing database applications in the cloud to see significant levels of performance degradation. However, advancements in SQL Server and cloud infrastructures, combined with a hosting service provider’s knowledge and expertise managing databases in the cloud, now address many of the security and performance issues that have prevented companies from deploying more high-volume, data-intensive applications to the cloud beyond testing and development.
CLOUD CONCERNS & SQL SERVER SOLUTIONS
Here are some of the primary concerns businesses have had with running more robust solutions and applications in the cloud, and how SQL Server and hosting providers work together to address them:
- Performance: In the days when hardware had no virtual bits and the virtual layer had to simulate more because it did not have direct access to the CPU, disk I/O performance was so slow that it simply was not worth running an application on a virtualization platform. Those days are over. Running SQL Server in the cloud drives speed and allows companies to move up-stack without experiencing any downtime. As a result, performance degradation drops significantly, and in some instances, has proven to run faster than in a dedicated environment.
- Manageability: When it comes to managing critical business data and applications in the cloud, companies are concerned about how they can access and manage their information. SQL Server’s built-in tools allow organizations to more efficiently scale and manage servers, instances, database applications, and resource utilization through a single user console.
- Security: SQL Server has built-in security and compliance capabilities that allow organizations to manage how people access data, as well as audit information to help with their regulatory compliance needs (e.g., HIPAA, PCI Data Security Standard Compliance). For businesses that need to secure sensitive data because they collect credit card or social security information, SQL Server has database encryption so people cannot query, steal or recover it somewhere else.
- Expertise: Moving SQL Server databases to the cloud requires higher levels of expertise to deploy and manage their applications, something many organizations do not have in-house. Due to a lack of experience and confidence, businesses are often reluctant to virtualize applications that are at the heart of their business operations where there is no tolerance for performance degradation or downtime. A hosting service provider offers virtualization expertise that gives businesses the confidence they need for managing SQL Server databases in the cloud.
- Cost Effectiveness: This enables businesses to reduce monthly recurring charges by paying only for the resources they use. Companies that take advantage of the subscription model increase their return on investment because they do not have to purchase on-premise hardware, software and support personnel.
It’s important to take into consideration some of the features that can be obtained by using SQL Server in the cloud:
- Simplified administration, leaving the management of the database platform, operating system, and hardware in the hands of others;
- Scalable resources, allowing you to quickly add or remove resources as your needs change;
- Integration with ODBC, PHP, and ADO.NET data access;
- Management through SQL Server Management Studio (using the version shipped with SQL Server 2008 R2 or later);
- Most of the basic functionality of Microsoft SQL Server.
Aside from all the benefits, there are also some downsides regarding Azure SQL Database. These are the features present on premise that are still to be implemented in Cloud and will arrive soon in the Managed Instance:
- SQL Server Agent
- Database Mail
- Native database backup and restore
- Linked Servers
- Cross-database transactions
- SQL CLR modules
- Transparent Data Encryption (TDE)
- Row-Level Security
- Always Encrypted
- SQL Audit
Finally, there is another possible downside— the price. For some, the cost may be considered too high. Depending on the configuration that you need, the hourly rate can be quite costly, including license.
INSTALLING SQL SERVER DATABASE ON AZURE
Taking as an example one of the most known Cloud services, Azure, let’s dive into Installing SQL Server Database on Azure and later on Performance analysis.
Follow these steps to create an empty SQL database.
- Click Create a resource in the upper left-hand corner of the Azure portal
- Select Databases from the New page, and select Create under SQL Database on the New page
- Fill out the SQL Database fields marked in red with the necessary details
- Under Server, click Configure required settings and fill out the SQL server (logical server) form with the necessary information
- When you have completed the form, click Select
- Click Pricing tier to specify the service tier, the number of DTUs (database transaction units), and the amount of storage. Explore the options for the amount of DTUs and storage that is available to you for each service tier
- For this quick start tutorial, select the Standard service tier and then use the slider to select 10 DTUs (S0) and 1 GB of storage
- Accept the preview terms to use the Add-on Storage option
- After selecting the server tier, the number of DTUs, and the amount of storage, click Apply
- Now that you have completed the SQL Database form, click Create to provision the database Provisioning takes a few minutes
- On the toolbar, click Notifications to monitor the deployment process
PERFORMANCE TROUBLESHOOTING FOR AZURE SQL DATABASE
I consider this part a very important step, as nowadays managing and tuning the performance of relational databases is a challenging task that requires significant expertise and time investment. Query Performance Insight allows you to spend less time troubleshooting database performance by providing the following:
- Deeper insight into your databases resource (DTU) consumption
- The top queries by CPU/Duration/Execution count, which can potentially be tuned for improved performance
- The ability to drill down into the details of a query, view its text and history of resource utilization
- Performance tuning annotations that show actions performed by SQL Azure Database Advisor
DATABASE TRANSACTION UNIT
Now you will probably ask yourself, what does DTU mean? Let’s have a quick look:
Query Performance Insight requires that Query Store is active on your database. If Query Store is not running, the portal prompts you to turn it on.
The following role-based access control permissions are required to use Query Performance Insight:
Reader, Owner, Contributor, SQL DB Contributor, or SQL Server Contributor permissions are required to view the top resource consuming queries and charts.
Owner, Contributor, SQL DB Contributor, or SQL Server Contributor permissions are required to view query text.
USING QUERY PERFORMANCE INSIGHT
- Query Performance Insight is easy to use. Open Azure portal and find database that you want to examine.
- From the left-hand side menu, under support and troubleshooting, select “Query Performance Insight”
- On the first tab, review the list of top resource-consuming queries
- Select an individual query to view its details
- Open SQL Azure Database Advisor and check if any recommendations are available
- Use sliders or zoom icons to change observed interval
REVIEW TOP CPU CONSUMING QUERIES
In the portal do the following:
- Browse to a SQL database and click All settings > Support + Troubleshooting > Query performance insight.
The top queries view opens and the top CPU consuming queries are listed.
2. Click around the chart for details. The top line shows overall DTU% for the database, while the bars show CPU% consumed by the selected queries during the selected interval (for example, if Past week is selected each bar . represents one day).
The bottom grid represents aggregated information for the visible queries.
- Query ID – unique identifier of query inside database
- CPU per query during observable interval (depends on aggregation function)
- Duration per query (depends on aggregation function)
- Total number of executions for a particular query. You can select or clear individual queries to include or exclude them from the chart using checkboxes.
3. If your data becomes stale, click the Refresh button
4. You can use sliders and zoom buttons to change observation interval and investigate spikes
5. Optionally, if you want a different view, you can select Custom tab and set:
- Metric (CPU, duration, execution count)
- Time interval (Last 24 hours, Past week, Past month)
- Number of queries
- Aggregation function
Viewing individual query details
To view query details:
- Click any query in the list of top queries.
- The details view opens and the queries CPU consumption/Duration/Execution count is broken down over time
- Click around the chart for details
- Top chart shows line with overall database DTU%, and the bars are CPU% consumed by the selected query
- Second chart shows total duration by the selected query
- Bottom chart shows total number of executions by the selected query
4.Optionally, use sliders, zoom buttons or click Settings to customize how query data is displayed, or to pick a different time period
REVIEW TOP QUERIES PER DURATION
In the recent update of Query Performance Insight, Microsoft introduced two new metrics that can help the user identify potential bottlenecks: duration and execution count.
Long-running queries have the greatest potential for locking resources longer, blocking other users, and limiting scalability. They are also the best candidates for optimization.
To identify long running queries:
- Open Custom tab in Query Performance Insight for selected database
- Change metrics to be duration
- Select number of queries and observation interval
- Select aggregation function
- Sum adds up all query execution time during whole observation interval
- Max finds queries which execution time was maximum at whole observation interval
- Avg finds average execution time of all query executions and show you the top out of these averages
REVIEW TOP QUERIES PER EXECUTION COUNT
High number of executions might not be affecting database itself and resources usage can be low, but overall application might get slow.
In some cases, very high execution count may lead to increase of network round trips. Round trips significantly affect performance. They are subject to network latency and to downstream server latency.
For example, many data-driven Web sites heavily access the database for every user request. While connection pooling helps, the increased network traffic and processing load on the database server can adversely affect performance. General advice is to keep round trips to an absolute minimum.
To identify frequently executed queries (“chatty”) queries:
- Open Custom tab in Query Performance Insight for selected database
- Change metrics to be execution count
- Select number of queries and observation interval
UNDERSTANDING PERFORMANCE TUNING ANNOTATIONS
While exploring your workload in Query Performance Insight, you might notice icons with vertical line on top of the chart.
These icons are annotations; they represent performance affecting actions performed by SQL Azure Database Advisor. By hovering annotation, you get basic information about the action:
If you want to know more or apply advisor recommendation, click the icon. It will open details of action. If it’s an active recommendation you can apply it straight away using command.
Query Performance Insight helps you understand the impact of your query workload and how it relates to database resource consumption. With this feature, you will learn about the top consuming queries, and easily identify the ones to fix before they become a problem.
I believe that SQL Server administrators will no longer have to worry about filling up their database servers. The current SQL Server edition and the next editions will provide a way to extend databases into the cloud, allowing customers to retain information they otherwise might need to erase due to a lack of local storage space.
Organizations can leverage teams that are experienced in the cloud to assess their needs and recommend things they may not have been thinking about to increase performance, decrease costs, and develop solutions at possibly the same cost of doing it in a dedicated environment.
This is why companies considering running SQL Server-anything should consider placing it into a cloud environment. Today, the cloud environment has the same or better performance than the local infrastructure and may save time in administering the databases through the tools that Cloud can offer.