Understand the concept of RDS

This is also the first and most important one. Before using a product or service, you should first understand the functions and limitations of the product or service. Just like when you buy a refrigerator or washing machine, you can only use its functions and precautions after reading the manual. The same is true for RDS for SQL Server.

The main difference between RDS for SQL Server and traditional self-built computer room to provide SQL Server is the problem of how many or few modules users need to be responsible for the database. Figure 1 shows the part that self-built SQL Server and Ali Cloud RDS for SQL Server need to be responsible for:

Of course, the cost of convenience is not only cost, but also limitation. This is similar to using some basic tools can do various kinds of work, but special tools can efficiently complete specific tasks. RDS is more similar to special tools.

The advantages of limitation

The purpose of the restriction is that alibaba Cloud uniformly does this part of the work, and the database is provided to users as a service, so users do not have the rights to the following parts of the database (operating system, storage, etc.). The database provided as a service has the following advantages:

simple

The biggest advantage of nature is simple, no longer pay attention to the INSTALLATION, configuration, monitoring, alarm, backup, high availability and other work of SQL Server, these work is often more professional people for a long time to establish a complete system, because the database is often the core part of the whole system, These work will often bring serious consequences if the level of operation and maintenance personnel problems or misoperation. Under Aliyun, there is a whole cloud system that does all this work and presents this information in a simple and intuitive interface. See Figure 2 and Figure 3.

Standardization & best practices

Ali Cloud is responsible for the operation and maintenance of basic database configuration, which can make the database operation and maintenance standardized. Usually, after SQL Server is installed, some operations such as: Maximum parallelism, file growth, number of TEMPdB, operating system permissions, disk I/O distribution, and many other parameters can affect the performance and availability of SQL Server in many cases. Many enterprises do not have DBAs or professional database operation and maintenance personnel, and therefore cannot correctly configure these parameters. Ali Cloud can standardize these parameters to be configured when the instance is generated. These configurations are in line with 99% of the best practices of the database after a lot of practice, so it can avoid a lot of database problems caused by improper configuration.

High availability

So far, from the perspective of high availability, Ali Cloud RDS For SQL Server is divided into two versions, one is dual-machine version, which uses SQL Server Mirror to achieve high availability at the bottom, so it can achieve high availability, even if there is a hardware failure, can also achieve second level switch. For stand-alone VERSIONS of SQL Server, high availability technology is more of a virtualization layer drift. When a failure occurs, SQL Server is moved from one host machine to another, so in this case it is on a minute scale. You can choose based on the importance of the business type and the budget of the cost. However, it is recommended to use the dual-phone version as much as possible.

Use the tools provided by Aliyun

One advantage of RDS is that it already encapsulates common operations that would normally be laborious and error-prone if done manually. For example, when the database has a problem, restore the database to a certain point in time. If the manual operation of the DBA requires the existence of a complete backup system, restore the database according to the backup sequence, but in the Ali Cloud is one-click operation. The use of common procedures can be seen in the second half of this article.

Make use of elastic extension

A big advantage of cloud database is elastic expansion, which means that it can be expanded on demand. Generally, it is appropriate to consider elastic functions in the following situations:

  1. Service growth: Dynamically expand the database capacity as services grow
  2. Business peak: for example, the double 11 will promote the temporary upgrade of server specifications, and then downgrade the server specifications during the business peak period
  3. Temporary use: For example, to apply for a server for testing, this process uses the cloud database not only avoids the troublesome cost of database installation and deployment, but also saves costs by shutting down the server after testing is completed.

Understanding the authority system

As mentioned above, as RDS is a service of PAAS layer, some permissions need to be transferred. However, most common permissions can still be used. You can use the command fn_my_permission function to know the permissions of the current login account, as shown in the figure:

Set a maintainable period

In fact, there are some operation and maintenance operations at the bottom of RDS, and there may be intermittent interruption during operation and maintenance. Now you can choose the accepted operation and maintenance period, which can be based on business, and the default is 2:00am-6: 00 am, this is suitable for most scenarios, but some scenarios, such as overseas business, or game business, may be peak in the evening, so it should be appropriate for the business.

Leveraging temporary instances

One very nice feature of RDS is temporary instances, as shown in the console:

Figure. Create temporary instance function



Figure. Specifies that a temporary instance is created from a point in time.

This feature allows you to clone an instance completely from a point-in-time backup, which you can use to do the following:

  1. Testing: A copy of the data can be completely cloned and used for testing without affecting the data and stress of the online production environment
  2. Data retrieval: If the operation is wrong, a copy of data can be cloned using the online production environment, and then manually retrieved data

  3. Development environment: Temporary instances can be used as a software development environment, eliminating the need to manually create test data and making it easier to find bugs in a timely manner

    The best part is that this feature is completely free.

Perform some undocument operations using built-in system stored procedures

RDS can perform some “non-standard” operations in addition to restricted privileges through a set of internal stored procedures that reside in the Master database. The following are the stored procedures supported in the current version

  • [dbO].[sp_rds_add_linked_server] (Add link server)
  • [dbo].[sp_rds_configure] (configure server level parameters)
  • [dbO].[sp_rds_copy_database]
  • [dbo].[sp_rds_dbcc_trace] (Enable trace markup)
  • [dbO].[sp_rds_read_error_logs] (Read SQL Server error logs)

  • [dbo].[sp_rds_set_db_online]

    For example, we could change the server’s parallel overhead threshold to 50 by using the following command.

   [dbo].[sp_rds_configure] 'cost threshold for parallelism','50'
Copy the code

Consider the impact of the RDS underlying architecture

Because RDS for Server two-node version uses SQL Server image at the bottom and is in high security mode, which means that all data updates (insert, delete, update) need to be synchronized to the mirror end. Therefore, if there are a large number of data update operations in a short time, consider reducing the size of each transaction. For example, SQL that deletes 100W pieces of data at a time can be written as a circular delete, with a waiting time added to each loop.

DELETE FROM table where date <'2015-1-1'Copy the code

Can be replaced with:

DECLARE @r INT; SET @r=1; WHILE @r>0 BEGIN DELETE TOP(5000) FROM table WHERE date <'2015-1-1' SET @r= @@rowcount; wait for dealy '00:00:00:01'Copy the code

summary

When using Aliyun RDS for SQL Server, we should consider the structure and limitations of PAAS layer. While RDS provides a lot of functions, we also need to transfer some permissions. It is the best practice to understand the underlying principles and maximize the functions provided by RDS and avoid restrictions.