Schemas and securables

SQL Server and Azure SQL Database have three scopes for securables. Securables are the resources within the database to which the authorization system manages access. For example, a table is a securable. To simplify access control, SQL Server contains securables in nested hierarchies called scopes.

The three securable scopes are:

• The server

• The database

• The schema

A schema is a collection of objects within your database, which allows objects to be grouped into separate namespaces.

Every user has a default schema. If a user tries to access an object without specifying a schema name, as in: SELECT name FROM customers, it is assumed the object is in the user’s default schema. If there is no such object in the default schema, SQL Server will check to see if the object is in the pre-defined dbo schema. If there is no object of the specified name in either the user’s default schema, or in the dbo schema, the user will receive an error message. It is considered best practice to always specify the schema name when accessing objects, so the previous select would be something like: ‎SELECT name FROM SalesSchema.customers. If a user has not been given a default schema, their default schema is set to dbo.

By default, if no schema is specified when a user creates an object, SQL Server will attempt to create it in the user’s default schema. If the user has not been granted permission to create objects in their default schema, the object cannot be created.

https://docs.microsoft.com/en-us/sql/relational-databases/security/securables?view=sql-server-ver15

Private Link provides a private IP address in an Azure vNet for an Azure SQL Database.

https://docs.microsoft.com/en-us/azure/private-link/private-link-overview

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.

Application roles

Application roles can be created within a SQL Server database or Azure SQL Database. Unlike database roles, users are not made members of an application role. An application role is activated by the user, by supplying the pre-configured password for the application role. Once the role is activated the permissions that are applied to the application role are applied to the user until that role is deactivated.

Built-in database roles

Microsoft SQL Server contains several fixed database roles within each database for which the permissions are predefined. Users can be added as members of one or more roles. These roles give their members a pre-defined set of permissions. These roles work the same within Azure SQL Database and SQL Server.

Users that need to create other users within the database can be granted membership in the role db_accessadmin. This role does not grant access to the schema of any of the tables, nor does it grant access to the data within the database.

Users that need to back up a database in a SQL Server or Managed Instance can be made members of the role db_backupoperator. The role db_backupoperator does not confer any permissions in an Azure SQL Database.

Users that need the ability to read from every table and view within the database can be made members of the role db_datareader.

Users that need the ability to INSERT, UPDATE, and DELETE data from every table and view within the database can be made members of the role db_datawriter.

Users who need the ability to create or modify objects within the database can be made members of the role db_ddladmin. Members of this role can change the definition of any object, of any type, but members of this role are not granted access to read or write any data within the databases.

The role db_denydatareader can be used for users who need to be prevented from reading data from any object in the database, when those users have been granted rights through other roles or directly.

The role db_denydatawriter can be used for users who need to be prevented from writing data to any object in the database, when those users have been granted rights through other roles or directly.

Users who need administrative access to the database can be made members of the role db_owner. Members of the db_owner role can perform any action within the database by default. However, unlike the actual database owner, who has the user name dbo, users in the db_owner role can be blocked from accessing data by placing them in other database roles, such as db_denydatareader, or by denying them access to objects. Membership in this database role should be limited to only trusted users.

Users who need to be able to grant access to other users within the database can be made members of the role db_securityadmin. Members of this role are not specifically granted access to the data within the database; however members of this role can grant themselves access to the tables within the database. Membership in this database role should be limited to only trusted users.

All users within a database are automatically members of the public role. By default, this role has no permissions granted to it. Permissions can be granted to the public role, but you should consider carefully whether that is really something you want to do. Granting permissions to the public role would grant these permissions to any user, including the guest account, if the guest account was enabled.

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

Azure provides multiple methods to monitor the performance of your resources and create a baseline. Each method can be tailored for a specific metric. The metrics that you can monitor will vary depending on the type of Azure resource you are monitoring. For example, Azure SQL Database and SQL Server on an Azure Virtual Machine will have different metrics available in the Azure Portal.

The following examples are focused on an Azure Virtual Machine. When you deploy an Azure Virtual Machine from the Azure Marketplace, an agent is installed in the virtual machine that provides a basic set of operating system metrics that are presented to you in the Azure portal. This agent supplies metrics to a service called Azure Monitor, which is a comprehensive platform monitoring solution that collects and displays a standard set of metrics from Azure resources. In the case of a virtual machine, the default metrics captured are CPU, network utilization, and disk read and write operations. You can capture additional metrics beyond what is captured in Azure monitor by enabling Monitoring Insights for your virtual machine as shown in the following image.

These metrics pertain to the operating system, not SQL Server. You’ll notice that the namespace for each metric is the virtual machine host, not SQL Server.

You are unable to view SQL Server-specific metrics from within the portal. For detailed SQL Server-specific metrics you will need to gather them from the virtual machine itself.

Azure Monitoring Insights allows you to collect additional data points like storage latency, available memory, and disk capacity. These Azure Monitor Insights can be one way of viewing a baseline of performance for your Azure Virtual Machine including I/O performance, memory, and CPU utilization. This data is stored in an Azure Log Analytics workspace. Azure Log Analytics is the primary tool in Azure for storing and querying log files of all kinds. Log Analytics is queried by a SQL-like language called Kusto Query Language (KQL).

If you create a virtual machine with one of the pre-configured SQL Server images in the Azure Marketplace, you will also get the SQL virtual machine resource provider as shown in the following image. You can launch this screen in the Azure portal by clicking on the SQL Server configuration option in the settings section of the main blade for an Azure Virtual Machine.

This dashboard allows you to see how much space your database files and transaction log file are consuming, and allows you to manage the features provided by the resource provider like automated patching and storage configuration. You can manually install the SQL Resource Provider for other installations of SQL Server on Azure Virtual Machine that were not defined as part of the virtual machine.

https://azure.microsoft.com/en-us/services/monitor/

Azure Data Studio provides a lightweight editor and other tools (including notebooks) for interacting with Azure Data Services (SQL Server on-premises, Azure SQL, Azure Database for PostgreSQL, and more).

https://docs.microsoft.com/en-us/sql/azure-data-studio/what-is-azure-data-studio?view=sql-server-ver15

When doing performance tuning in Azure SQL, understanding the importance of statistics is critical.

Statistics are stored in the user database as binary large objects (blobs). These blobs contain statistical information about the distribution of data values in one or more columns of a table or indexed view.

Statistics contain information about the distribution of data values within a column. The query optimizer uses column and index statistics in order to determine cardinality, which is the number of rows a query is expected to return. Cardinality estimates are then used by the query optimizer to generate the execution plan. Cardinality estimates also help the optimizer determine what type of operation (for example, index seek or scan) to use to retrieve the data requested.

https://docs.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server?view=sql-server-ver15

These methods are all configurable options for monitoring the availability of regions and datacentres. You can also view Azure Service Health in the Azure portal.

https://docs.microsoft.com/en-us/azure/availability-zones/az-overview

Platform as a Service (PaaS) provides a complete development and deployment environment in the cloud, which can be used for simple cloud-based applications as well as for advanced enterprise applications.

As shown in the image above, PaaS includes cloud provided operating system and management of your solution. For example, in Azure SQL Database and Azure SQL Managed Instance, the high availability, OS and SQL Server features, and backups are all provided by the Azure platform.

Azure SQL Database is available in three different deployment options:

• A single database – A single database that is billed and managed on a per database level

• Elastic Pools – A group of databases that are managed together and share a common set of resources

• Hyperscale – a single database offering that allows databases to scale much beyond the 4-TB limit of an Azure SQL Database

Even in the cloud all services are backed by physical hardware. The Azure SQL Database allows you to choose from two different purchasing models:

• Database Transaction Unit (DTU) – DTUs are calculated based on a formula combining compute, storage, and I/O resources.

• vCore – The vCore model allows you to purchase a specified number of vCores based on your given workloads. vCore is the default purchasing model when purchasing Azure SQL Database resources. vCore databases have a specific relationship between the number of cores and the amount of memory and storage provided to the database.

Azure SQL Database also includes:

• Automatic backups

• Automatic patching

• Built-in high availability

• SQL Server feature enhancements without the need to upgrade software

https://www.pass.org/PASS-Blog/choose-the-right-azure-sql-database-deployment-options-for-your-solution

Ultra Disk offers the lowest latency of the three storage types.

Azure ultra disks offer high throughput, high IOPS, and consistent low latency disk storage for Azure IaaS virtual machines (VMs). This new offering provides top of the line performance at the same availability levels as our existing disks offerings. One major benefit of ultra disks is the ability to dynamically change the performance of the SSD along with your workloads without the need to restart your VMs. Ultra disks are suited for data-intensive workloads such as SAP HANA, top tier databases, and transaction-heavy workloads.

https://docs.microsoft.com/en-us/azure/virtual-machines/disks-enable-ultra-ssd?tabs=azure-portal

Ultra HDD is not a valid option.

Azure SQL Database

When you're securing your network for Azure SQL Database, you have four main choices:

• Allow access to Azure services

• Use firewall rules

• Use virtual network rules

• Use Azure Private Link

In addition to these main choices, you have the opportunity to block all public access (only with Private Link) and the option to force a minimum Transport Layer Security (TLS) version. The least secure method, but the easiest to configure, is to allow access to Azure services. The most secure method is to use Private Link.

https://azure.microsoft.com/en-us/services/private-link/