Understanding the SQL Server Proportional fill algorithm

When creating a database, SQL Server maps this database with minimum two operating system files; the database data file MDF and the database log file LDF. Logically, the database data files are created under a collection set of files that simplifies the database administration, this logical file container is called the Filegroup. Database data files can come in two types, the Primary data files that contains metadata information for the database and pointers to the other database files in addition to the data, where each database should have only one Primary data file and the optional Secondary files to store data only. The basic storage unit in SQL Server is the Page, with each page size equal to 8KB. And each 8 pages with 64KB size called Extent.

The SQL Server component that is responsible for managing the data storage within the database files, called the SQL Server Storage Engine. It uses a fill mechanism that writes data to the database files depending on the amount of free space in each data file rather than writing in each file until it is full then moving to the second one sequentially. This data filling algorithm is called Proportional Fill Algorithm. In other words, the SQL Server Storage Engine will write more frequently to the files with more free space. For example, if the first data file has 10 MB free space and the second one has 20 MB, the storage engine will fill one extent to the first file and two extents to the second one. If auto-growth is enabled to the database files and the database files become full, the SQL Server Database Engine will expand the database files one at a time and write to that file, once the expanded file becomes full, SQL Server Database Engine will expand the second database file and so on.

ranking integer number to specify how many times this file will be skipped from the writing process to the next file depending on the free space of that file, this number is called the Skip Target where the minimum value equal to 1 means that a write process will be performed on that file. The Skip Target can be measured by dividing the number of free extents in the file with the largest free space amount by the number of free extents in the current file, as integer value. The larger the free space in the database data file, the smaller Skip Target value. To have one file to write on each loop, there should be minimum one data file with Skip Target value equal to 1. Each time a new database data file is added or removed, or 8192 extents is filled in the database filegroup, the Skip Target value will be calculated again. In this way, all database data files will become full approximately at the same time. The Skip Target calculation can be monitored by enabling the Trace Flag 1165.

https://www.sqlshack.com/understanding-sql-server-proportional-fill-algorithm/

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.

Service Tier Options

PaaS comes in several different service tiers. Each tier has varying capabilities, which provide a wide range of options when choosing this platform.

The DTU model is available in three different service tiers:

• Basic

• Standard

• Premium

You can purchase vCore databases in three different service tiers as well:

• General Purpose – This tier is for general purpose workloads. It is backed by Azure premium storage. It will have higher latency than Business Critical.

• Business Critical – This tier is for high performing workloads offering the lowest latency of either service tier. This tier is backed by local SSDs instead of Azure blob storage. It also offers the highest resilience to failure as well as providing a built-in read-only database replica that can be used to off-load reporting workloads.

• Hyperscale – Hyperscale databases can scale far beyond the 4 TB limit the other Azure SQL Database offerings and have a unique architecture that supports databases of up to 100 TB.

https://docs.microsoft.com/en-us/azure/azure-sql/database/service-tiers-dtu

You'll see a number entry after the T-SQL statements, representing each line of the T-SQL entry.

For example, the assume the preceding command ends with '26'. Select ENTER after this. You will know the command has finished when you see 1 >, which indicates that sqlcmd is ready for the first line of your next T-SQL entry.

https://docs.microsoft.com/en-us/learn/modules/azure-sql-deploy-configure/8-exercise-load-data

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

Understanding recovery time and recovery point objectives are crucial to your high availability and disaster recovery (HADR) plan as they are the foundation for any availability solution.

Recovery Time Objective

Recovery Time Objective (RTO) is the maximum amount of time available to bring resources online after an outage or problem. If that process takes longer than the RTO, there could be consequences such as financial penalties, work not able to be done, and so on. RTO can be specified for the whole solution, which includes all resources, as well as for individual components such as SQL Server instances and databases.

Recovery Point Objective

Recovery Point Objective (RPO) is the point in time to which a database should be recovered and equates to the maximum amount of data loss that the business is willing to accept. For example, suppose an IaaS VM containing SQL Server experiences an outage at 10:00 AM and the databases within the SQL Server instance have an RPO of 15 minutes. No matter what feature or technology is used to bring back that instance and its databases, the expectation is that there will be at most 15 minutes worth of data lost. That means the database can be restored to 9:45 AM or later to ensure minimal to no data loss meeting that stated RPO. There may be factors that determine if that RPO is achievable.

Defining Recovery Time and Recovery Point Objectives

RTOs and RPOs are driven by business requirements but are also based on various technological and other factors, such as the skills and abilities of the administrators (not just DBAs). While the business may want no downtime or zero data loss, that may not be realistic or possible for a variety of reasons. Determining your solution’s RTO and RPO should be an open and honest discussion between all parties involved.

One of the aspects crucial for both RTO and RPO is knowing the cost of downtime. If you define that number and the overall effect being down or unavailable has to the business, it is easier to define solutions. For example, if the business can lose $10,000 per hour or could be fined by a government agency if something could not be processed, that is a measurable way to help define RTO and RPO. Spending on the solution should be proportional to the amount, or the cost, of downtime. If your HADR solution costs $X, but you wind up only being affected for a few seconds instead of hours or days when a problem occurs, it has paid for itself.

From a non-business standpoint, RTO should be defined at a component level (for example, SQL Server) as well as for the entire application architecture. The ability to recover from an outage is only as good as its weakest link. For example, if SQL Server and its databases can be brought online in five minutes but it takes application servers 20 minutes to do the same, the overall RTO would be 20 minutes, not five. The SQL Server environment could still have an RTO of five minutes; it still will not change the overall time to recover.

RPO deals specifically with data and directly influences the design of any HADR solution as well as administrative policies and procedures. The features used must support both the RTO and RPOs that are defined. For example, if transaction log backups are scheduled every 30 minutes but there is a 15-minute RPO, a database could only be recovered to the last transaction log backup available which in a best case would be 30 minutes ago. This timing assumes no other issues and the backups have been tested and are known to be good. While it is hard to test every backup generated for each database in your environment, backups are just files on a file system. Without doing at least periodic restores, there is no guarantee they are good. Running checks during the backup process can give you some degree of confidence.

The specific features used, such as an Always On Availability Group (AG) or an Always On Failover Cluster Instance (FCI) will also affect your RTOs and RPOs. Depending on how the features are configured, IaaS or PaaS solutions may or may not automatically failover to another location, which could result in longer downtime. By defining RTO and RPO, the technical solution that supports that requirement can be designed knowing the allowances for time and data loss. If those wind-up being unrealistic, RTOs and RPOs must be adjusted accordingly. For example, if there is a desired RTO of two hours but a backup will take three hours to copy to the destination server for restoring, the RTO is already missed. These types of factors must be accounted for when determining your RTOs and RPOs.

There should be RTOs and RPOs defined for both HA and DR. HA is considered a more localized event that can be recovered from more easily. One example of high availability would be an AG automatically failing over from one replica to another within an Azure region. That may take seconds, and at that point, you would need to ensure that the application can connect after the failover. SQL Server’s downtime would be minimal. A local RTO or RPO may potentially be measured in minutes depending on the critical nature of the solution or system.

DR would be akin to bringing up a whole new data centre. There are lots of pieces to the puzzle; SQL Server is just one component. Getting everything online may take hours or longer. This is why the RTOs and RPOs are separate. Even if many the technologies and features used for HA and DR are the same, the level of effort and time involved may not be.

All RTOs and RPOs should be formally documented and revised periodically or as needed. Once they are documented, you can then consider what technologies and features you may use for the architecture.

https://docs.microsoft.com/en-us/azure/architecture/framework/resiliency/backup-and-recovery

The Resource Provider will configure your Storage Pool.

https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/storage-configuration

When you use the Azure portal, a SQL Server VM comes already registered with the SQL Server resource provider. Using the resource provider includes the following benefits:

• Automated patching and backups

• Configurable SQL authentication modes

• Configurable data, log, and temp file paths

• User-defined storage workload types

The biggest issue about figuring out which method to use for configuration is determining how flexible you want to be for configuration options. With Azure PowerShell®, there are not many meaningful settings exposed for post configuration except for setting the SQL management type. You can find more information about the management mode here Azure PowerShell. Next in line is the Azure Command Line Interface (CLI). This CLI enables you to set backups, SQL authentication types, and patching schedules as described here. The most powerful option is configuring within an Azure Resource Manager (ARM) template. The ARM template includes all the settings from the CLI as well as configuring SQL storage settings as shown in Template JSON.

https://docs.rackspace.com/blog/azure-sql-vm-resource-provider/

SSL Options for MySQL and MariaDB

Azure Database for MySQL and MariaDB both support the use of SSL to encrypt network traffic between the database and the client application. In order to implement this encryption, you must both change the setting Enforce SSL Connection for your database, as shown in the image below.

You also need to download the public root certificate and include the path to the certificate in your client connection. After completing both of these tasks your network traffic will be encrypted.

https://docs.microsoft.com/en-us/azure/mariadb/howto-configure-ssl

tempdb is an important shared resource used by applications. Ensuring the right configuration of tempdb can affect your ability to deliver consistent performance. You use tempdb in the same way for both SQL Server and Azure SQL. Note, however, that your ability to configure tempdb is different, including the placement of files, the number and size of files, and tempdb configuration options.

tempdb files are always automatically stored on local SSD drives, so I/O performance shouldn't be an issue.

SQL Server professionals often use more than one database file to partition allocations for tempdb tables. For Azure SQL Database, the number of files are scaled with the number of vCores (for example, 2 vCores = 4 files) with a maximum of 16. The number of files isn't configurable through T-SQL against tempdb, but you can do so by changing the deployment option. The maximum size of tempdb is scaled per number of vCores. You get 12 files with SQL Managed Instance, independent of vCores.

The database option MIXED_PAGE_ALLOCATION is set to OFF, and AUTOGROW_ALL_FILES is set to ON. This can't be configured, but, as with SQL Server, they are the recommended defaults.

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

Azure Key Vault allows for the storage of both Always Encrypted and TDE keys.

https://docs.microsoft.com/en-us/azure/key-vault/general/basic-concepts

Azure SQL Managed Instance and Azure SQL Database, are platform as a service (PaaS) deployments. These PaaS Azure SQL deployment options contain a fully managed database engine that automates most of the database management functions, like upgrading, patching, backups, and monitoring.

Here are some key features of SQL Managed Instance and SQL Database:

• Business continuity enables your business to continue operating in the face of disruption.

• High availability guarantees your databases are up and running 99.99% of the time. No need to worry about maintenance or downtimes.

• Automated backups are created and use Azure read-access geo-redundant storage (RA-GRS) to provide geo-redundancy.

• Long-term backup retention enables you to store specific full databases for up to 10 years.

• Geo-replication creates readable replicas of your database in the same datacentre (region) or a different one.

• Scalability. You can easily add more resources (CPU, memory, storage) without long provisioning.

• Network security features protect your data over the network. These features include firewalls to restrict connectivity, Azure Private Link to ensure your data isn't exposed to the internet, and integration with virtual networks for connectivity to on-premises environments.

• Advanced security detects threats and vulnerabilities in your databases and enables you to secure your data.

• Automatic tuning analyzes your workload. It provides recommendations that can optimize performance of your applications by adding indexes, removing unused indexes, and automatically fixing query plan problems.

• Built-in monitoring capabilities enable you to get insights into the performance of your databases and workload and troubleshoot performance problems.

• Built-in intelligence automatically identifies potential problems in your workload and provides recommendations that can help you to fix those problems.

Azure SQL Managed Instance: https://azure.microsoft.com/en-us/services/azure-sql/sql-managed-instance/

Azure SQL Database: https://docs.microsoft.com/en-us/azure/azure-sql/database/sql-database-paas-overview