Files and Filegroups

SQL Server professionals often use files and filegroups to improve I/O performance through physical file placement. Azure SQL doesn't allow users to place files on specific disk systems. However, Azure SQL has resource commitments for I/O performance with regard to rates, IOPS, and latencies. In this way, abstracting the user from physical file placement can be a benefit.

Azure SQL Database only has one database file (Hyperscale typically has several), and the maximum size is configured through Azure interfaces. There's no functionality to create additional files.

Azure SQL Managed Instance supports adding database files and configuring sizes but not physical placement of files. The number of files and file sizes for SQL Managed Instance can be used to improve I/O performance. In addition, user-defined filegroups are supported for SQL Managed Instance for manageability purposes.

https://docs.microsoft.com/en-ca/learn/modules/azure-sql-performance/3-configure-maintain-performance

Azure SQL SLA

Azure SQL maintains a service-level agreement (SLA) that provides financial backing to the commitment to achieve and maintain service levels. If your service level isn't achieved and maintained as described in the SLA, you might be eligible for a credit toward a portion of your monthly service fees.

Currently, you can achieve the highest availability (99.995%) from an Azure SQL Database Business Critical deployment that has Availability Zones configured. The Business Critical tier is the only option in the industry that provides RPO and RTO SLAs of 5 seconds and 30 seconds, respectively. RPO stands for recovery point object. It represents the amount of data you're potentially willing to lose in a worst case scenario. RTO stands for recovery time objective. It represents how long it takes to be back up and running again if a disaster occurs.

For General Purpose or single-zone Business Critical deployments of Azure SQL Database or Azure SQL Managed Instance, the SLA is 99.99%.

The Hyperscale tier's SLA depends on the number of replicas. Remember that you choose how many replicas you have in Hyperscale. If you don't have any, the failover behaviour is more like that of General Purpose. If you have replicas, the failover behaviour is more like that of Business Critical.

Here are the SLAs, based on the number of replicas:

• 0 replicas: 99.5%

• 1 replica: 99.9%

• 2 or more replicas: 99.99%

https://azure.microsoft.com/en-us/support/legal/sla/sql-database/v1_5/

Another method to increase availability for Azure SQL Database is to use active geo-replication. Active geo-replication creates a replica of the database in another region that is asynchronously kept up to date. That replica is also readable, similar to an AG in IaaS. Underneath the surface, Azure is using the same methods as an AG, which is why some of the terminology and functionality is similar(primary and secondary logical servers, read-only databases, etc.).

https://docs.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-overview

One of the key features of ATP is SQL Injection inspection.

Azure offers built in advanced threat detection functionality through services such as Azure Active Directory (Azure AD), Azure Monitor logs, and Azure Security Centre. This collection of security services and capabilities provides a simple and fast way to understand what is happening within your Azure deployments.

Azure provides a wide array of options to configure and customize security to meet the requirements of your app deployments. This article discusses how to meet these requirements.

https://docs.microsoft.com/en-us/azure/security/fundamentals/threat-detection

You can apply tags to groups of Azure resources to organize billing data.

https://docs.microsoft.com/en-us/azure/devops/organizations/billing/billing-faq

Database Roles

Database security can get complicated for applications with many users. In order to make it easier for both administrators and auditors, most database applications use role-based security.

Roles are effectively security groups that share a common set of permissions. Combining permissions into a role allows a set of roles to be created for a given application.

Some examples of roles would be administrators who had full access to all of the databases and servers, reporting users who only read the database, and an application account that had access to write data into the database.

The roles can be defined when the application is designed, and then users can be assigned to those roles as they need access to the database. Role-based access control or RBAC is a common architecture across computer systems and is how authorization is managed in Azure Resource Manager.

SQL Server and Azure SQL Database both include built-in roles that are defined by Microsoft, and also provide the option to create custom roles.

Custom roles can be created at the server or database level. However, server roles cannot be granted access to objects within a database directly. Server roles are only available in SQL Server and Azure SQL Managed Instance, not in Azure SQL Database.

Within a database, permissions can be granted to the users that exist within the database. If multiple users all need the same permissions, you can create a database role within the database and grant the needed permissions to this role. Users can be added as members of the database role. The members of the database role will inherit the permissions of the database role.

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15

Typical activities that you can schedule for regular SQL Server maintenance include:

• Database and transaction log backups

• Database Consistency Checks

• Index maintenance

• Statistics updates

You should be aware of the importance of backups, as well as index and statistics maintenance, for all your databases. Database consistency checks, also known as CHECKDB (for the command DBCC CHECKDB) are of equal importance, as it is the only way to check an entire database for corruption. Depending on the size of your databases and your uptime requirements, you may perform all of these activities nightly. More commonly in production systems, the maintenance operations are spread out over the course of week, as both index maintenance and consistency checks are very I/O intensive operations and they are typically done during weekend hours. Similarly, many DBAs stagger backups of large databases, and only do one full backup a week. Differential and transaction log backups can then be used to manage recovery to a specific point in time. SQL Server offers a built-in way to manage all of these tasks using Maintenance Plans. Maintenance plans create a workflow of the tasks to support your databases. Maintenance plans are created as Integration Services packages, which allow you to schedule your maintenance activities. Many DBAs also use open-source scripts to perform database maintenance, to allow for more flexibility and control of maintenance activities.

Best practices for maintenance plans

In addition to allowing you to performance database maintenance, maintenance plans provide options to allow you to prune data from the msdb database, which acts as the data store for the SQL Server Agent. Maintenance plans also allow you to specify that older database backups should be removed from disk. Removing old backup files helps your SQL Server by reducing the size of your backup volume and helps manage the size of the msdb database. Ensure that your backup retention period is longer than your consistency check window. This means if you run a consistency check weekly, you should retain eight days of backups. (Note: The backup operation will not detect corruption in a database, so it is possible to have corruption within a backup file). Maintenance plan activities are scheduled as SQL Server Agent jobs for execution.

https://docs.microsoft.com/en-us/azure/azure-sql/database/planned-maintenance

Disk storage is designed for VMs and is the best storage option.

Disk Storage - Azure managed disks offer block storage that is presented to a virtual machine. These disks are managed just like a physical disk in an on-premises server, except that they are virtualized. There are several performance tiers within managed disks depending on your workload. This type of storage is the most commonly used type for SQL Server data and transaction log files.

https://docs.microsoft.com/en-us/azure/storage/common/storage-introduction

Command-line interfaces

Popular command-line interfaces like sqlcmd and bcp are supported with Azure SQL services.

Azure CLI and Azure PowerShell cmdlets are supported for specific Azure SQL service scenarios. All these CLIs are supported across Windows, macOS, and Linux clients. Tools like sqlcmd and az are pre-installed in Azure Cloud Shell.

https://www.microsoft.com/en-ie/download/details.aspx?id=53591

SQL Server has always had configuration options that were set at the database level. For example, the recovery model has always been a database setting, but as more complex features have been introduced to the database, more options have been added. Many of these options are tied to the compatibility level of the database, which is itself a database level configuration option. Database configuration options break down into two groups, with a minor difference:

• Options configured by the ALTER DATABASE SCOPED CONFIGURATION syntax in T-SQL

• Options configured by the ALTER DATABASE syntax in T-SQL

There is no significance to the different ways to set these options. Options that are set using ALTER DATABASE include:

• Database Recovery Model - Whether the database is in full or simple recovery model

• Automatic Tuning Option - Whether to enable the force last good plan

• Auto Create and Update Statistics - Allows the database to create and update statistics and allows for the option of asynchronous statistics updates

• Query Store Options - The Query Store options are configured here

• Snapshot Isolation - You can configure snapshot isolation and read committed snapshot isolation

The above settings are a subset of the configurable options.

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql?view=sql-server-ver15&tabs=sqlpool

Database scoped options were introduced in SQL Server 2016, and allow configuration of several options that were formerly configured at the server level. Some of the options include:

• Maximum Degree of Parallelism - This setting allows for a database to configure its own MaxDOP setting and override the server’s setting.

• Legacy Cardinality Estimation - This setting allows for the database to use the older cardinality estimator. Some queries may have degraded performance under the newer cardinality estimator, introduced in SQL Server 2014, and may benefit from this setting. You should note that if you use this option in conjunction with a newer compatibility level, you can still get the benefits of Intelligent Query Processing in compatibility level 140 or 150.

• Last Query Plan Stats - This allows you to capture the values of the last actual execution plan for a query. This feature is only active in compatibility level 150.

• Optimize for Ad Hoc Workloads - This option uses the optimizer to store a stub query plan in the plan cache. This can help reduce the size of the plan cache for workloads that have a lot of single use queries.

https://social.technet.microsoft.com/wiki/contents/articles/34718.new-sql-server-2016-scoped-configuration.aspx

Database compatibility level

Each database has its own compatibility level, which controls the behaviour of the query optimizer for that database. You can manage this setting when upgrading SQL Server to ensure that your queries have similar execution plans to the older version. Microsoft will support running on an older compatibility level for an extended period. You should attempt to move towards newer compatibility levels, as many of the new performance features in Intelligent Query Processing are only available under compatibility level 140 or 150.