This document introduces the usage specifications and recommendations for TencentDB for SQL Server.
Purpose
Standardize the management and maintenance of TencentDB for SQL Server to avoid impacts such as unavailability caused by improper operations.
This guide helps database developers write SQL properly to achieve optimal performance for TencentDB for SQL Server.
Instance Specification Recommendations
Avoid using 1-core instances in production environments. 1-core instances can be used for trial testing.
A minimum of 2-core or higher specifications is recommended for production environments. Because SQL Server runs on Windows, both the engine and the operating system require substantial resources. A 1-core specification is not suitable for production workloads and may lead to system low memory and lagging issues after prolonged operation.
Instance Selection Recommendations
Primary/replica instances of the multi-node or two-node editions (formerly HA/Cluster Edition) are recommended. Compared with instances of the single-node edition (formerly Basic Edition), primary/replica instances can greatly improve the business availability and reliability.
If your business has few write requests but massive read requests and you need to add read-only instances, it is recommended that you use SQL Server 2017 Enterprise or a later version of the two-node edition for more efficient and stable data synchronization.
Multi-AZ deployment is recommended for instances of the two-node (formerly HA/Cluster Edition) and multi-node editions to implement AZ-level disaster recovery.
Database Connection Recommendations
When connecting to TencentDB for SQL Server, use the "ip,port" format. Note that a comma separates the IP address and port in the connection string.
Using server names for connection is not recommended. Applications should implement a retry mechanism when connecting to the database. This allows applications to promptly restore connections through retries in case of database failover or disconnection.
Permission Management Specifications
To ensure the stability and security of TencentDB for SQL Server, permissions such as sysadmin and shutdown are restricted. When you execute certain statements on TencentDB for SQL Server, you may encounter the following related errors:
User does not have permission to perform this action.
You do not have permission to run the RECONFIGURE statement.
Solution: Operations such as parameter modification, database management, user management, and backup/recovery are recommended to be performed on the console.
Grant permissions on demand. Typically, applications only need read-write permissions for the specified database.
Follow the principle of least privilege. Typically, general application access users are granted permissions at the database level.
When granting user access, only allow access from specific IP addresses or IP address ranges. You can configure security groups on the console to implement this restriction. Always configure security groups strictly according to the standards provided in the console.
Separate management accounts, development accounts, and application accounts. Avoid using admin accounts for development or as business accounts.
Daily Operation Specifications and Usage Suggestions
For performance considerations, having too many databases can degrade instance performance, consume resources such as Worker threads, and exceed the database creation quantity limit, which may easily cause primary/secondary synchronization exceptions. It is recommended that the number of databases created for a single instance does not exceed its maximum limit. The number of databases a single instance can handle is related to the instance's CPU core quantity. For the detailed formula to calculate the maximum number of databases for each version, see Database Quantity. The database name length must not exceed 64 characters. Currently, only digits, uppercase and lowercase letters, and the underscore _ are supported for database names. Other special characters are not supported.
Do not use weak passwords to enhance the security of your database instance. Unless necessary, perform account and database management operations on the console.
For private network connection and login, ensure that the client's CVM and TencentDB for SQL Server are machines under the same account, in the same region, and within the same VPC.
Applications must not rely on sysadmin permissions. Accounts with the sysadmin role have super administrator privileges. Improper use of these privileges can threaten database security and stability. By default, cloud databases do not grant super administrator permissions. Applications that use databases must not depend on such permissions.
Monitor database size and shrink databases promptly. After prolonged use, some physical space in a database may not be released in a timely manner. You need to perform a database shrink operation to release this physical space. Monitor the size of log files and physical files. If you find that files are expanding rapidly, you can shrink the database during off-peak hours.
It is recommended that long-running instances can be restarted during off-peak hours. After prolonged operation, instances may experience performance degradation. It is recommended to restart instances once every three months during off-peak hours.
Do not create tables in system databases. For user data, create user-defined databases for storage. Do not create any tables in system databases to write data. Although permissions to use system databases are granted, any data stored in system databases is insecure.
Do not set the database to Single User mode. Single User mode allows only one Session to access the database, which prevents other sessions from accessing it and causes Ops issues for the cloud database. If you set the database to Single User mode, restore it to Multi User mode promptly.
Slow query log collection is implemented using Extended Events, which is a lightweight tracing method. It has minimal impact on the instance.
Rebuild indexes periodically. After prolonged use, a database may accumulate a significant amount of index fragmentation, which can degrade database access performance. Therefore, periodic index rebuilding is required. You can schedule index rebuilding by creating a SQL Agent Job. It is recommended to rebuild indexes once a month.
Update statistics periodically. Regularly updating database statistics is beneficial for performance. It is recommended to update statistics once a week by creating a SQL Agent Job.
Set the maximum degree of parallelism. The maximum degree of parallelism parameter affects the CPU utilization of your business.
Perform backup/recovery operations through the management console or by calling APIs. Do not perform these operations directly through SSMS or by executing SQL. For migrating to the cloud using backup/recovery methods, see Cold Backup Migration. Do not set the database recovery model to simple. It is recommended to use the full recovery model.
Setting the recovery model to simple will result in no incremental backups being performed for the database. Consequently, the database cannot be restored to a specific point in time.
For two-node (formerly HA Edition/Cluster Edition) instances, setting the database recovery model to simple prevents the database from establishing a replication relationship. Consequently, you cannot perform a switch between primary and secondary or modify the instance specifications.
Therefore, use the simple recovery model with caution.
Avoid performing ddl operations during peak business hours whenever possible.
Avoid performing batch data operations during peak business hours whenever possible. It is best to perform such operations in batches during off-peak hours. If you need to delete an entire table, it is recommended to use truncate or drop during off-peak hours.
Avoid running multiple services on a single instance whenever possible. High coupling introduces the risk of services interfering with each other.
Avoid using auto-commit for transactions. For online operations, develop the habit of starting with `begin tran;` to reduce the risk of data loss caused by misoperations. In case of a misoperation, you can also use the rollback feature of TencentDB for SQL Server. After a transaction is started, commit it promptly to avoid instance blocking.
Perform database operations in the console. Do not directly operate on databases in the SSMS client.
If your business has promotional activities or similar events, estimate resource requirements in advance and optimize your instances accordingly. If the demand is significant, contact the corresponding business representative promptly.
DTS Database Migration Recommendations
Before migrating to the cloud, perform the following checks:
Check the version numbers of the source and target databases. The version number of the target database must be greater than or equal to that of the source database. For example, if the source database is the 2016 version, the target database for migration can only be the 2016, 2017, or 2019 version.
Check the architecture versions of the source and target databases. When the source instance is a self-built instance in a local IDC, a self-built instance on a Tencent Cloud CVM, a self-built instance on another cloud provider's cloud server, or a SQL Server instance from another cloud provider's cloud database, it can be migrated to a TencentDB for SQL Server instance of any architecture version, including single-node (formerly Basic Edition) or dual-node (formerly HA Edition/Cluster Edition). When the source instance is a Tencent Cloud dual-node (formerly HA Edition/Cluster Edition) instance, it cannot be migrated to a Tencent Cloud single-node (formerly Basic Edition) instance via DTS. When the source instance is a Tencent Cloud single-node (formerly Basic Edition) instance, it can be migrated to a Tencent Cloud dual-node (formerly HA Edition/Cluster Edition) instance via DTS.
Check the network connectivity between the source and target databases. The network between the source and target databases must be able to connect. Additionally, the server hosting the source database must have sufficient outbound bandwidth; otherwise, migration efficiency will be affected.
Check the naming of the source and target databases. Databases with duplicate names cannot exist between the source and target.
Check the permissions of the source database instance account. The SQL service startup on the migration source must be changed to local. The database account used for migration on the source side has no restrictions but must have sysadmin permissions.
Check the permissions of the target database instance account. On the target side, use an account with administrator permissions for the migration.
Check the port openings on the source database. Port 1433 must be opened on the source database. Additionally, due to Windows Server sharing issues, file sharing port 445 must be opened on the server hosting the source database.
Check the recovery mode of the source database. The source database must be set to the "full recovery mode". It is also recommended that users perform a full backup before migration.
Check the local disk space of the source database. The local disk space where the source database resides must be sufficiently large, and the remaining free space must be able to accommodate the size of the database to be migrated.
Check the disk space of the target database. The disk space where the target database resides must be larger than the source database size, ideally 1.5 times the size of the source database.
Check the status of the target database. The target side must not be accessed or have any workload running, otherwise the migration will fail.
During the migration to the cloud, note the following operational restrictions:
Only one migration task can be initiated for the same source instance at the same time.
Only database-level migration is supported. This means that during migration, all objects within a database must be migrated together. Migration of individual tables is not supported.
Migration of instance-level logins, jobs, triggers, and db links (link servers) is not supported.
During migration, do not modify or delete user information (including usernames, passwords, and permissions) and port numbers in the source and target databases. Doing so will cause the migration task to fail.
Do not perform transaction log backups during incremental synchronization. Doing so will truncate the transaction logs, breaking their continuity.
If you perform only a full data migration, do not write new data to the source instance during the migration. Otherwise, the source and target data will become inconsistent. For scenarios where data writing occurs, to maintain data consistency in real time, it is recommended to choose full + incremental data migration.
For full + incremental data migration, do not write new data to the source database when you click Complete and the task status is Completing. It is recommended to stop writing for two minutes after you click Complete. Otherwise, data inconsistency between the source and target databases may occur.
After the migration to the cloud is completed, perform the following checks:
Check permission integrity. Permissions affect the database plan. The migration only completes data recovery. Database users are restored, but other service-level permissions such as logins need to be recreated and associated with database accounts.
Rebuild the indexes. After migration, the physical environment of the data files changes, causing database indexes to become invalid. You must rebuild the indexes. Otherwise, database performance may degrade significantly.
Check instance-level objects, such as logins, jobs, triggers, and db links (link servers). After migration, users must rebuild these objects themselves.
Database and Table Design Specifications
Must-Knows
TencentDB for SQL Server versions earlier than 2014 do not support MemoryTable. If you require MemoryTable-like functionality, it is recommended to use TencentDB for Redis or Memcached.
When creating new tables, follow the database third normal form. It is recommended that each table have a primary key. Even if no suitable column can be selected as the primary key, add a column to serve as the primary key.
Define fields as NOT NULL with a default value whenever possible. NULL values can cause many issues in SQL development, such as preventing index usage. When performing calculations on NULL, you can only use IS NULL and IS NOT NULL for evaluation.
Recommendations
Plan database resource usage appropriately based on business scenario analysis and estimates of data access (including database read/write QPS, TPS, storage space, and so on). You can also configure various monitoring items for your TencentDB for SQL Server instance on the TCOP console page.
The principle for creating databases is to place tables of the same business type in one database. Avoid sharing the same database for tables of different businesses. Also, avoid performing cross-database join operations in your programs, as such operations can impact subsequent fast rollback.
It is recommended to consistently use a single character set to avoid data garbling between different character sets.
For decimal fields, it is recommended to use the DECIMAL data type. The float and double types lack sufficient precision, especially for business scenarios involving money.
Avoid storing large text, binary data, images, files, and similar content in databases using TEXT/blob data types. Instead, save this data as local disk files and store only their index information in the database.
Avoid using foreign keys whenever possible. It is recommended to implement foreign key logic at the application layer. Foreign keys and cascade updates are not suitable for high-concurrency scenarios, as they degrade insertion performance and can easily cause deadlocks under heavy concurrency.
Decouple business logic from data storage. The primary role of the database is to store data. Implement business logic at the application layer whenever possible. Minimize the use of instance-level advanced features such as triggers, linked servers, and jobs, as these features have poor portability and scalability. If such objects exist in the instance, you need to manually migrate them to the new instance after the migration.
If your business is not expected to reach a significant scale in the short term, it is recommended to avoid using partitioned tables. Partitioned tables are primarily used for archival management. When most queries in your business do not use the partition key, partitioned tables provide little performance benefit.
For business scenarios with high read pressure and low consistency requirements (accepting data latency at the second level), it is recommended to purchase read-only instances to implement a read/write separation policy.
Index Design Specifications
Must-Knows
Avoid creating indexes on columns that are updated very frequently and have low cardinality. Record updates modify the B+ tree structure, and creating indexes on frequently updated fields can significantly degrade database performance.
When creating a composite index, place the column with the highest cardinality on the leftmost side of the index. For example, for a query like `select xxx WHERE a = x AND b = x;`, if you create a composite index on both a and b and column a has higher cardinality, create the index as `idx_ab(a,b)`. In scenarios where business logic involves a large number of mixed conditions with both non-equality and equality operators, it is recommended to prioritize columns with equality conditions. For instance, for a condition like `WHERE a xxx AND b = xxx`, even if column a has higher cardinality, it is advised to place column b at the forefront of the index because the query cannot utilize index a.
Recommendations
It is recommended that the number of indexes per table not exceed five, and the number of fields in a single index not exceed five. Too many indexes fail to provide effective filtering, occupy storage space, and consume resources for management.
Create indexes on columns that are most frequently used for filtering in business SQL queries and have relatively few duplicate values. Creating indexes on columns not used by business SQL is meaningless. The higher the uniqueness of a field, the better the filtering performance of the index.
Avoid redundant indexes. If both indexes (a,b) and (a) exist, then (a) is a redundant index. When the query filter condition is on column a, the (a,b) index is sufficient, and there is no need to create a separate (a) index.
Properly utilize Include indexes to reduce I/O overhead. Place frequently used columns at the front. Columns that are not used as query conditions can be placed in the Include clause.
SQL Writing Specifications
Must-Knows
For UPDATE and DELETE operations, use WHERE clauses for precise matching. When you need to delete a large amount of data, process it in batches during off-peak hours.
When using INSERT INTO t_xxx VALUES(xxx), explicitly specify the column attributes to be inserted. This practice helps prevent data errors caused by table structure changes.
Note the most common scenarios in SQL statements that cause index invalidation:
Implicit type conversion occurs, for example, when column a is of type varchar, but the SQL statement is written as where a = 1, causing the varchar to be converted to int.
Avoid performing mathematical calculations or applying functions on indexed columns. For example, do not use functions to format date columns.
An issue of inconsistent multi-column sort order may occur. For example, the index is defined as (a,b), but the SQL statement is written as order by a b desc.
Strive to make where conditions match exactly to avoid fuzzy matching and batch matching using in or not in.
Recommendations
Fetch only the data you need, avoid using select *, and steer clear of the following issues:
Index coverage cannot be achieved, leading to a table lookup operation and increased I/O.
This operation imposes additional memory overhead, loads a large amount of cold data into the cache, and reduces the query hit rate.
Additional network transmission overhead.
Avoid using large transactions whenever possible. It is recommended to break down large transactions into smaller ones to prevent instance blocking or master-slave replication delays caused by large transactions.
Commit transactions promptly in your business code to avoid unnecessary lock waits.
Minimize the use of multi-table joins and large table joins. When joining two tables, try to let the smaller table be the driving table. It is recommended that the join columns have consistent data types and are both indexed.
Note
It is difficult to completely avoid the aforementioned situations. The recommended approach is to not use such conditions as the primary filter criteria. Placing them after the primary filter criteria that utilize indexes is generally acceptable.
If you notice a high volume of full table scans in your monitoring, you can download and analyze the slow query log files from the console.
Perform necessary SQL reviews before service deployment. For routine Ops, regularly download the slow query log for targeted optimization.