Azure SQL and Site Reliability

Azure SQL Database is a relational database-as-a-service (DBaaS) based on the latest stable version of Microsoft SQL Server. Rather than install your SQL Server on a Virtual Machine (VM), remove your operational overhead with this DBaaS service.

We'll look at this service in this blog post along with snippets from the Microsoft Docs and Learn website. This blog will also provide you with instructions on how to create a new Azure SQL instance.

Why a DBaaS?

As a developer, you may need instant resources without understanding all the difficult work. Some of the manual or homebrewed tasks that a Database Administrator or Ops team member may have to do on a daily basis include:

• System Configuration, OS Tuning/Hardening
• Patching of OS and Microsoft SQL
• High Availability/Replication
• Backups - if we lose our data, we could lose our business
• Integration into Active Directory
• Database tuning
• Security of networks associated with the database
• Encryption

These tasks provide reliability, security and dependability for SQL database and server. By utilizing a platform that manages and automates all of these tasks, we can create more reliable applications and processes. Azure SQL Database provides teams with ways to streamline database provisioning by allowing administrators to create self-service or automated workflows using Azure Resource Manager templates.

Example use case - Tailwind Traders

Tailwind Traders has an E-Commerce website that provides users with products for every day. After a recent major outage, it's been determined by Tailwind Trader's Board and Executive Team that changes must be made to improve uptime and ensure orders are always possible. Without this as a motivating factor for the team at Tailwind Traders, there's a major potential for the business to lose money.

The technology team sat and evaluated their last few post-mortems. The constant seemed to be the SQL database with inventory seems to

For years the database server has been managed by a small team that provides regular maintenance. One of the critical issues for Tailwind Traders over the years has been this database service becoming unavailable. When the database goes offline, so does the entire ability to create new orders.

When websites and applications are offline or do not respond as expected by users, a business has a potential threat to its reputation. How can Tailwind Traders expand as a company and provide a comfortable working environment for their team without putting effort into improving the database?

The costs of failure

Beyond the cost financially is the damage done to the team who respond when applications and their supporting dependencies fail. Alerts coming at all times of the day and night do not provide a healthy working environment for team members on-call.

The Google SRE Book speaks specifically on creating a culture of safety for engineers who are on-call:

..SRE teams support Google’s most critical systems. Being an SRE on-call typically means assuming responsibility for user-facing, revenue-critical systems or for the infrastructure required to keep these systems up and running. SRE methodology for thinking about and tackling problems is vital for the appropriate operation of services.

-- Andrea Spadaccini

The Ops and DBA team currently lack this safety when working on-call. Due to this, they are constantly running with a high rate of team burnout. This burnout eventually will force the management at Tailwind Traders to come to grips with how this will impact talent acquisition and retention. The business has to take into how they plan the costs of losing a valued employee and having to train someone new if this cycle continues.

Decisions and management

The Database team along with the CIO of Tailwind Traders believe in order for the business to ensure the greatest possible reliability of the orders process must be improved. Every transaction will require some form of access to let us know when new items are available or have been purchased. Without this database available

Tailwind Traders currently hosts this service on a SQL server on a Virtual Machine. There is currently no management configured for this system. The database server lacks the default firewall, replication, and automated backup services. Due to this technical debt, the organization is currently running the production database in a less than an ideal manner. Without these services, Tailwind Traders could risk an attacker intrusion to the database or even possibly lose data due to missing backups. By placing management around the database systems required for Tailwind Traders, the business can increase reliability and help reduce failure points that could hurt the company.

A snip from the code in DatabaseInitializer.cs from the repo:

        private Task CreateBrandsTable(SqlConnection conn)
        {
            logger.LogInformation("Creating Brands table...");
            var command = new SqlCommand(@"
                CREATE TABLE Brands (
                    Id INT NOT NULL PRIMARY KEY,
                    Name NVARCHAR(255)
                )", conn);
            return command.ExecuteNonQueryAsync();
        }

        private Task CreateTypesTable(SqlConnection conn)
        {
            logger.LogInformation("Creating Types table...");
            var command = new SqlCommand(@"
                CREATE TABLE Types (
                    Id INT NOT NULL PRIMARY KEY,
                    Code NVARCHAR(255),
                    Name NVARCHAR(255)
                )", conn);
            return command.ExecuteNonQueryAsync();
        }

The team has decided to take the plunge and make the migration to Azure SQL Database. The reasons behind the decisions were clear once all the features seemed to resolve many of the problems the company seemed to be running into like scalability, network security, and automatic tuning.

Azure SQL

Azure SQL Database is a fully managed Platform as a Service (PaaS) Database Engine that handles most of the database management functions such as upgrading, patching, backups, and monitoring without user involvement. Azure SQL Database is always running on the latest stable version of SQL Server Database Engine and patched OS with 99.99% availability. PaaS capabilities that are built into Azure SQL database enables you to focus on the domain-specific database administration and optimization activities that are critical for your business.

With Azure SQL Database, you can create a highly available and high-performance data storage layer for the applications and solutions in Azure. SQL Database can be the right choice for a variety of modern cloud applications because it enables you to process both relational data and non-relational structures, such as graphs, JSON, spatial, and XML.

Deployment models

Azure SQL Database provides the following deployment options for an Azure SQL database:

• Single database represents a fully managed, isolated database. You might use this option if you have modern cloud applications and microservices that need a single reliable data source. A single database is similar to a contained database in Microsoft SQL Server Database Engine.
• Managed instance is a fully managed instance of the Microsoft SQL Server Database Engine. It contains a set of databases that can be used together. Use this option for easy migration of on-premises SQL Server databases to the Azure cloud, and for applications that need to use the database features that SQL Server Database Engine provides.
• Elastic pool is a collection of single databases with a shared set of resources, such as CPU or memory. Single databases can be moved into and out of an elastic pool.

Tailwind Trader is going to be using the Managed Instance to get the most out of their experience with SQL server.

Create a new SQL DB

Creating a new Azure SQL DB takes you only a few steps.

Create a managed instance

The following steps show you how to create a managed instance:

1. Select Azure SQL on the left menu of Azure portal. If Azure SQL is not in the list, select All services, and then enter Azure SQL in the search box.
2. Select +Add to open the Select SQL deployment option page. You can view additional information about an Azure SQL Database managed instance by selecting Show details on the Managed instances tile.
3. Select Create.
4. Use the tabs on the Create Azure SQL Database Managed Instance provisioning form to add required and optional information. The following sections describe these tabs

Use the table below as a reference for the information required at this tab.

Setting	Suggested value	Description
Subscription	Your subscription.	A subscription that gives you permission to create new resources.
Resource group	A new or existing resource group.	For valid resource group names, see Naming rules and restrictions.
Managed instance name	Any valid name.	For valid names, see Naming rules and restrictions.
Region	The region in which you want to create the managed instance.	For information about regions, see Azure regions.
Managed instance admin login	Any valid username.	For valid names, see Naming rules and restrictions. Don't use "serveradmin" because that's a reserved server-level role.
Password	Any valid password.	The password must be at least 16 characters long and meet the defined complexity requirements.

Select Configure Managed Instance to size compute and storage resources and to review the pricing tiers. Use the sliders or text boxes to specify the amount of storage and the number of virtual cores. When you're finished, select Apply to save your selection.

Fill out optional information on the Networking tab. If you omit this information, the portal will apply default settings. Here you can select to configure your Azure Virtual Nework, the connection type, whether or not a public endpoint is available and to permit access to it.

Finally click review and create to being the datbase creation.

Now Tailwind Traders can begin using Database Migration Service from their older, unreliable database to their Managed Instance ]on Azure.

More Information

For more information on how you and your team can start using Azure SQL Database check out the Microsoft Learn Module. It's free and can get you the skills you need to begin building more reliable database systems with Azure.

• Microsoft SQL documentation
• Learning Path - Migrate SQL workloads to Azure
• Secure your Azure SQL Database
• Getting started with single databases in Azure SQL Database
• Introduction to Site Reliability Engineering (SRE)