Scaling and development features for SQL Database
This is the second part of the cycle about how SQL Database is organized. The first part dealt with the architecture of SQL Database, in the second part we continue this review with a focus on scaling and some features of development for SQL Database.
One of the most significant benefits of hosting databases in a SQL Database environment is the built-in scalability features. If necessary, you can add additional databases. Two components of the SQL Database provide scalability by constantly monitoring the workload on each of the nodes. The first component is Engine Throttling (kernel load controller), which protects the server from overload. The second component is the Load Balancer (load balancer), which ensures that the server does not operate continuously in high performance mode.
Each of the SQL Servers in the data center is used by several clients at the same time. There is a possibility that the subscriber application will overload the server and disrupt the entire SQL Server instance. For example, in the full recovery mode, the operation of inserting a large number of rows, especially those containing large objects, is able to fill the transaction log and the entire disk that hosts the log. In addition, each instance of SQL Server shares a computer with other critical system processes that cannot be deprived of resources. The most important of these is the structure support process, which monitors the state of the system and provides access to the SQL Database environment.
')
To protect data center resources from unnecessary workloads that affect the performance of computers, the load on each computer is monitored by the Engine Throttling component (kernel load balancer). In addition, each database replica is tracked. This allows you to control statistical indicators, the values ​​of which must be within specified limits: log size, duration of write operations, processor utilization level, actual limits on the size of the physical database, and the size of the user database in the SQL Database. If the limits are exceeded, the SQL Database may reject read and write requests for 10 seconds in a row. Exceeding resource usage limits may cause the SQL Database to reject all read and write requests (depending on the type of resource).
Currently, the performance of the SQL Database environment cannot be guaranteed, despite the guarantee of its availability. One of the reasons is the problem of multi-user access: multiple clients with their own SQL Database share the same instance of SQL Server and the same computer. It is impossible to predict the level of workload that will be required by each of the subscribers connections. Nevertheless, ensuring high performance is a critical aspect that was taken into account when developing the SQL Database infrastructure. The SQL Database Load Balancing services evaluate the load on each of the computers in the data center.
When adding a new SQL Database to a cluster, the load balancer determines the locations of the new replicas (primary and secondary), given the current level of workload on the computers.
If one of the computers is overloaded, the load balancer can move the main replica to a less loaded computer. The simplest way is to switch the primary and secondary replicas of the SQL Database, the performance of which is reduced. This switch can instantly improve performance, since all read and write operations are performed on the main replica.
Federation allows the database layer to be scaled in the same way as the middle-tier or front-end scaling. If it is simple, then the federation breaks one big base into several smaller ones by a certain principle. Usually this principle is chosen in such a way as to make small bases independent of each other (minimize cross-references). The actual federation allows the following:
The technical details of federation and sharding are beyond the scope of this article (since this is a separate large topic), but for those who want to join the beautiful (federation) at the end of the article I cite references to Russian-language posts on the subject of federation architecture in SQL Database.
Most of the security issues of SQL Database databases are solved at the level. As with access to a local database, a user must have an account and password to connect to a SQL Database. SQL Database supports only standard security tools. Therefore, each account must be created explicitly.
On each SQL Database server, you can configure the firewall so that it only allows traffic from specified IP addresses. By default, the list of allowed IP addresses is empty. This reduces the risk of denial-of-service (DoS) attacks. All data transferred between clients and the SQL Database must be encrypted using SSL. Clients should use the Encrypt = True parameter for the connection, which eliminates the threat of a malicious attacker in the middle. To further reduce the risk of DoS attacks, you can use the DoSGuard service, which monitors login errors from the same IP addresses for a certain period of time and blocks access to all resources from these IP addresses.
Although developing SQL Server applications for a SQL Database environment is very similar to developing applications for a local instance of SQL Server, there are a few key differences.
When using a cloud database, including SQL Database, you need to be prepared to handle unexpected connection breaks, including handling similar situations in program code. The best way to handle breaking a connection is to reestablish a connection and execute commands or requests that failed , which is the principle of retry logic .
If a connection breakdown is caused by a network problem, the SQL Database environment cannot return a meaningful error message to the application before terminating the session. When you try to reuse this connection (similar to creating a pool of connections to SQL Server), you will receive a transport-level protocol error.
Like other databases, the SQL Database breaks connections from time to time due to errors, lack of system resources and other temporary problems. In these situations, the SQL Database environment always tries to return a specific error message if an active query is sent from the client connection side. However, it is not always possible to inform the client application about an error if there are no pending requests from its side. For example, if you did not send a single active query within 30 minutes when working with a database using SQL Server Management Studio, the session will time out. Since there are no active queries, the SQL Database environment cannot return an error message.
Unlike SQL Server, each SQL Database table must have a clustered index . When declaring the primary key of the table, a cluster index of the primary key column (or several columns depending on the type of key) is created by default. A primary key clustered index is not the only way to create a clustered index. Sometimes this is not the best solution. Clustered indexes sort and store rows of data in a table based on their key values. For each table there can be only one clustered index, since the data rows themselves can be sorted in only one order. It is necessary to control the presence of a clustered index for each table.
SQL Database does not support the creation of HEAP tables. A HEAP table is any table that does not have a clustered index. This rule is valid only for SQL Database databases. Temporary tables are located in the tempdb database, which is part of the serving SQL Server instances in the data center. These tables can be HEAP tables.
When creating additional nonclustered indexes for tables that have clustered indexes, these additional indexes reference table data using a cluster key. In SQL Server, the non-clustered HEAP data index uses their physical addresses to refer to this data. Multiple SQL Database databases can be stored in a single SQL Server database. Therefore, using references to physical data storage addresses would noticeably reduce the flexibility of the SQL Database system.
All indexes in SQL Server and SQL Database are stored as balanced trees. The underlying technologies for high availability and replication in the SQL Database environment are based on replication of balanced tree sequences. This structure allows the maintenance of computers independently of each other. The system takes advantage of optimized I / O that cannot be achieved when working with HEAP tables.
The SQL Database environment supports local transactions using normal Transact-SQL commands that are fully consistent with those of SQL Server.
SQL Database database settings provide snapshot isolation. READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION are ON. The default isolation level for SQL Server and SQL Database is READ COMMITTED. Since the READ_COMMITTED_SNAPSHOT database parameter is also used, transactions in the SQL Database environment are performed in optimistic concurrency mode. Cannot change SQL Database parameters. However, you can control the isolation level by issuing the SET TRANSACTION ISOLATION LEVEL command before starting a transaction. The user cannot change the default value (READ COMMITTED) when using optimistic parallelism and set the SQL Server default value (READ COMMITTED) when using pessimistic parallelism. The only way to implement the mechanism of operation that is used in SQL Server by default is to use the special WITH blocking technique (READCOMMITTEDLOCK) when working with each table in each of the transactions.
The user cannot control the physical allocation of resources or the configuration of the computers and database files used. Therefore, the system has minimal requirements for troubleshooting. You may need to troubleshoot when you detect poor query processing performance or parallel processing problems (for example, blocking). SQL Database Database Isolation Level is set to READ COMMITTED SNAPSHOT by default. Therefore, the user does not have to solve serious blocking problems.
The troubleshooting techniques for locking or suboptimal execution plans are almost the same in SQL Database and SQL Server. As with SQL Server, some of the basic troubleshooting tools are available as dynamic management view (DMV) views. However, SQL Database supports only some of the dynamic management views provided by SQL Server, and some of the available dynamic management views have a different mechanism. For example, in SQL Server, users need VIEW SERVER STATE permission to view the contents of many views, and in SQL Database, users need VIEW DATABASE STATE permission. In SQL Server, the sys.dm_tran_locks, sys.dm_exec_requests, and sys.dm_exec_query_stats views provide detailed information about the procedures and queries of the entire server instance. In a SQL Database environment, these views return only information about the SQL Database. For more information about supported views of dynamic management, see the “Metadata” section.
So, we see that SQL Database is a specially designed cloud relational database designed for scaling, fault tolerance and one-click use (or very close to one click). From the point of view of comparing SQL Database and SQL Server, SQL Database is not an exact copy of SQL Server, but gradually the most interesting features of SQL Server become available in SQL Database (for example, cloud SQL Reporting ).
Ps. Sharding and Federation Resources:
Providing scalability in SQL Database
One of the most significant benefits of hosting databases in a SQL Database environment is the built-in scalability features. If necessary, you can add additional databases. Two components of the SQL Database provide scalability by constantly monitoring the workload on each of the nodes. The first component is Engine Throttling (kernel load controller), which protects the server from overload. The second component is the Load Balancer (load balancer), which ensures that the server does not operate continuously in high performance mode.
Performance regulation
Each of the SQL Servers in the data center is used by several clients at the same time. There is a possibility that the subscriber application will overload the server and disrupt the entire SQL Server instance. For example, in the full recovery mode, the operation of inserting a large number of rows, especially those containing large objects, is able to fill the transaction log and the entire disk that hosts the log. In addition, each instance of SQL Server shares a computer with other critical system processes that cannot be deprived of resources. The most important of these is the structure support process, which monitors the state of the system and provides access to the SQL Database environment.
')
To protect data center resources from unnecessary workloads that affect the performance of computers, the load on each computer is monitored by the Engine Throttling component (kernel load balancer). In addition, each database replica is tracked. This allows you to control statistical indicators, the values ​​of which must be within specified limits: log size, duration of write operations, processor utilization level, actual limits on the size of the physical database, and the size of the user database in the SQL Database. If the limits are exceeded, the SQL Database may reject read and write requests for 10 seconds in a row. Exceeding resource usage limits may cause the SQL Database to reject all read and write requests (depending on the type of resource).
Load balancing
Currently, the performance of the SQL Database environment cannot be guaranteed, despite the guarantee of its availability. One of the reasons is the problem of multi-user access: multiple clients with their own SQL Database share the same instance of SQL Server and the same computer. It is impossible to predict the level of workload that will be required by each of the subscribers connections. Nevertheless, ensuring high performance is a critical aspect that was taken into account when developing the SQL Database infrastructure. The SQL Database Load Balancing services evaluate the load on each of the computers in the data center.
When adding a new SQL Database to a cluster, the load balancer determines the locations of the new replicas (primary and secondary), given the current level of workload on the computers.
If one of the computers is overloaded, the load balancer can move the main replica to a less loaded computer. The simplest way is to switch the primary and secondary replicas of the SQL Database, the performance of which is reduced. This switch can instantly improve performance, since all read and write operations are performed on the main replica.
Database Federation
Federation allows the database layer to be scaled in the same way as the middle-tier or front-end scaling. If it is simple, then the federation breaks one big base into several smaller ones by a certain principle. Usually this principle is chosen in such a way as to make small bases independent of each other (minimize cross-references). The actual federation allows the following:
- Overcome the 150 GB limit on the size of a single database in SQL Database.
- Increase overall system performance due to the fact that each member of the federation is usually located on different physical servers.
- Implement multi- tenancy ( Multi-tenant architecture for SaaS applications ).
The technical details of federation and sharding are beyond the scope of this article (since this is a separate large topic), but for those who want to join the beautiful (federation) at the end of the article I cite references to Russian-language posts on the subject of federation architecture in SQL Database.
Security
Most of the security issues of SQL Database databases are solved at the level. As with access to a local database, a user must have an account and password to connect to a SQL Database. SQL Database supports only standard security tools. Therefore, each account must be created explicitly.
On each SQL Database server, you can configure the firewall so that it only allows traffic from specified IP addresses. By default, the list of allowed IP addresses is empty. This reduces the risk of denial-of-service (DoS) attacks. All data transferred between clients and the SQL Database must be encrypted using SSL. Clients should use the Encrypt = True parameter for the connection, which eliminates the threat of a malicious attacker in the middle. To further reduce the risk of DoS attacks, you can use the DoSGuard service, which monitors login errors from the same IP addresses for a certain period of time and blocks access to all resources from these IP addresses.
Recommendations for developers in SQL Database
Although developing SQL Server applications for a SQL Database environment is very similar to developing applications for a local instance of SQL Server, there are a few key differences.
Connect and disconnect
When using a cloud database, including SQL Database, you need to be prepared to handle unexpected connection breaks, including handling similar situations in program code. The best way to handle breaking a connection is to reestablish a connection and execute commands or requests that failed , which is the principle of retry logic .
If a connection breakdown is caused by a network problem, the SQL Database environment cannot return a meaningful error message to the application before terminating the session. When you try to reuse this connection (similar to creating a pool of connections to SQL Server), you will receive a transport-level protocol error.
Like other databases, the SQL Database breaks connections from time to time due to errors, lack of system resources and other temporary problems. In these situations, the SQL Database environment always tries to return a specific error message if an active query is sent from the client connection side. However, it is not always possible to inform the client application about an error if there are no pending requests from its side. For example, if you did not send a single active query within 30 minutes when working with a database using SQL Server Management Studio, the session will time out. Since there are no active queries, the SQL Database environment cannot return an error message.
Clustered Index Requirement
Unlike SQL Server, each SQL Database table must have a clustered index . When declaring the primary key of the table, a cluster index of the primary key column (or several columns depending on the type of key) is created by default. A primary key clustered index is not the only way to create a clustered index. Sometimes this is not the best solution. Clustered indexes sort and store rows of data in a table based on their key values. For each table there can be only one clustered index, since the data rows themselves can be sorted in only one order. It is necessary to control the presence of a clustered index for each table.
SQL Database does not support the creation of HEAP tables. A HEAP table is any table that does not have a clustered index. This rule is valid only for SQL Database databases. Temporary tables are located in the tempdb database, which is part of the serving SQL Server instances in the data center. These tables can be HEAP tables.
When creating additional nonclustered indexes for tables that have clustered indexes, these additional indexes reference table data using a cluster key. In SQL Server, the non-clustered HEAP data index uses their physical addresses to refer to this data. Multiple SQL Database databases can be stored in a single SQL Server database. Therefore, using references to physical data storage addresses would noticeably reduce the flexibility of the SQL Database system.
All indexes in SQL Server and SQL Database are stored as balanced trees. The underlying technologies for high availability and replication in the SQL Database environment are based on replication of balanced tree sequences. This structure allows the maintenance of computers independently of each other. The system takes advantage of optimized I / O that cannot be achieved when working with HEAP tables.
Transaction management
The SQL Database environment supports local transactions using normal Transact-SQL commands that are fully consistent with those of SQL Server.
SQL Database database settings provide snapshot isolation. READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION are ON. The default isolation level for SQL Server and SQL Database is READ COMMITTED. Since the READ_COMMITTED_SNAPSHOT database parameter is also used, transactions in the SQL Database environment are performed in optimistic concurrency mode. Cannot change SQL Database parameters. However, you can control the isolation level by issuing the SET TRANSACTION ISOLATION LEVEL command before starting a transaction. The user cannot change the default value (READ COMMITTED) when using optimistic parallelism and set the SQL Server default value (READ COMMITTED) when using pessimistic parallelism. The only way to implement the mechanism of operation that is used in SQL Server by default is to use the special WITH blocking technique (READCOMMITTEDLOCK) when working with each table in each of the transactions.
Troubleshooting
The user cannot control the physical allocation of resources or the configuration of the computers and database files used. Therefore, the system has minimal requirements for troubleshooting. You may need to troubleshoot when you detect poor query processing performance or parallel processing problems (for example, blocking). SQL Database Database Isolation Level is set to READ COMMITTED SNAPSHOT by default. Therefore, the user does not have to solve serious blocking problems.
The troubleshooting techniques for locking or suboptimal execution plans are almost the same in SQL Database and SQL Server. As with SQL Server, some of the basic troubleshooting tools are available as dynamic management view (DMV) views. However, SQL Database supports only some of the dynamic management views provided by SQL Server, and some of the available dynamic management views have a different mechanism. For example, in SQL Server, users need VIEW SERVER STATE permission to view the contents of many views, and in SQL Database, users need VIEW DATABASE STATE permission. In SQL Server, the sys.dm_tran_locks, sys.dm_exec_requests, and sys.dm_exec_query_stats views provide detailed information about the procedures and queries of the entire server instance. In a SQL Database environment, these views return only information about the SQL Database. For more information about supported views of dynamic management, see the “Metadata” section.
Total
So, we see that SQL Database is a specially designed cloud relational database designed for scaling, fault tolerance and one-click use (or very close to one click). From the point of view of comparing SQL Database and SQL Server, SQL Database is not an exact copy of SQL Server, but gradually the most interesting features of SQL Server become available in SQL Database (for example, cloud SQL Reporting ).
Ps. Sharding and Federation Resources:
- Sharding with SQL Database - this document is a guide to building applications that have a horizontal partition of data in their architecture called sharding. The data in it is spread across several physical databases, which ensures the scalability of the application or service as it grows and the amount of data increases.
- The first acquaintance with the federations of SQL Database - this article gives an answer to the question what is federation in SQL Database databases and why is federation necessary.
- Federation in SQL Database - a detailed description of the federation implementation in SQL Database.
Source: https://habr.com/ru/post/149551/
All Articles