SQL in Azure - Azure SQL

Microsoft SQL Server is one of the most popular databases and is often the core of many popular applications. Thanks to Azure, we can skip the whole cluster setup, installation, and maintenance by using Azure SQL—a cloud version of SQL Server with the same features available. Thanks to flexible pricing, we can select whichever option we want when it comes to both performance and available features. We don't have to worry about geo-replication and storing backups either—all these functionalities can be easily configured and automated in the cloud.

The following topics will be covered in this chapter:

• Differences between Microsoft SQL Server and Azure SQL
• Working with Azure SQL in the Azure portal
• Security features of Azure SQL
• Scaling and monitor Azure SQL

Technical requirements

To perform the exercises in this chapter, you will need:

• An Azure subscription

Differences between Microsoft SQL Server and Azure SQL

Microsoft SQL Server is a well-known and widely used SQL database server that has gained much popularity and is considered a default choice for many projects ranging from very simple websites to enterprise-class services that handle high load and are considered critical for a business. As cloud technologies gain more and more popularity, the natural consequence of such a situation is the expectation that by moving an application to Azure, it is also possible to move its database. To meet such needs, Microsoft has developed Azure SQL Service—a PaaS version of Microsoft SQL Server that is managed and upgraded by their teams; the only things you are responsible for are configuration and data management. There is also one more offering from Azure called SQL Server VMs, which is one more option for using this database in...

Creating and configuring Azure SQL

After reading the beginning of this chapter, you should be able to sense how Azure SQL works and what it offers for you. While some theory is always a good thing, it is practice which creates the full picture and allows you to fully understand the topic. In this section, we will focus on creating and configuring Azure SQL in the portal and trying to identify all the afore mentioned features. You will also see how managing this PaaS service is different from the on-premise version, especially when it comes to using its features.

Creating an Azure SQL instance

In the Azure portal, when you search for Azure SQL, you will see plenty of different options such as SQL Database, SQL server (logical...

Security

When it comes to Azure SQL features, there are multiple different options you can use to make your solution secure. Things such as firewalls, full operation auditing, and data encryption are the common capabilities of this service and are available even for the Basic tier. In this section, we will focus on learning the afore mentioned capabilities, so your instance is secured and immune to most threats.

Firewall

When browsing your SQL Database, you probably noticed the Set server firewall button that is available on the Overview blade:

This is the easiest way to set a firewall rule that allows traffic to Azure SQL.

In Azure SQL, initially all traffic is rejected—you have to whitelist all IPs of computers...

Scaling Azure SQL

The required performance of your database may differ depending on the time and current state of your application. This is when scaling is all-important—you can adjust cost and available resources depending on the needs of your service. In Azure SQL, there are multiple different scenarios that you will consider: whether you use a single database or an elastic pool, whether you need to scale out reads, or whether you need all features available everywhere. In this short section, I will show you how to quickly proceed with your decision and where you can find scaling tools.

Single database

As we mentioned previously, with a single database scaling is really simple—you just need to go to the Configure...

Monitoring and tuning

The last item we cover in this chapter will be the monitoring and tuning of Azure SQL. Because databases are often the heart of many applications, it is crucial to have a quick way to diagnose any issues regarding performance or usage, and easily tweak things if needed. Azure SQL uses multiple different features that you can leverage to get insights from your instance.

Monitoring

To monitor your SQL Database, you can use alerts, which should be familiar to you (assuming you have read the previous chapter, Chapter 15, Using Application Insights to Monitor Your Applications). You can access this functionality by clicking on the Alerts blade:

Here you have two types of alert available:

• Add metric alert...

Summary

Azure SQL is a very complex and extended service that works in a similar way to its on-premise version, Microsoft SQL Server. While being a full PaaS Azure component, it still allows for many advanced operations such as sharding, multi-tenancy, AD integration or failover, and geo-replication. Besides being hosted within a cloud, you can still use it in the same way you would a standalone version of SQL Server. In the next chapter, we will cover the last PaaS service mentioned in this book, which is Azure Data Lake Storage.

Questions

1. What is different in terms of update policy between Azure SQL and Microsoft SQL Server?
2. What is sharding?
3. You created a new SQL Database in Azure SQL, but the server refuses to connect to it. What could be the issue here?
4. What are the two available purchasing models for Azure SQL?
5. What is an elastic pool?
6. What is the difference between DTU and eDTU?
7. How can you mask a particular field in Azure SQL?
8. What audit log destinations are available?

Further reading

• How to understand DTU: https://sqlperformance.com/2017/03/azure/what-the-heck-is-a-dtu
• Performance recommendations: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-advisor
• Adaptive query processing: https://docs.microsoft.com/pl-pl/sql/relational-databases/performance/adaptive-query-processing?view=sql-server-2017
• Securing SQL Database: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-security-overview
• Read scale-out: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-read-scale-out
• Sharding: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-scale-introduction