This topic describes the stored procedures that are supported by ApsaraDB RDS instances that run SQL Server 2012, SQL Server 2016, SQL Server 2017, and SQL Server 2019.

Replicate data between the databases of an RDS instance

T-SQL command:

sp_rds_copy_database

Instance configuration:

  • RDS High-availability Edition
  • RDS Basic Edition

Description:

This stored procedure is used to replicate the data of a source database to a specified destination database. The source and destination databases are created on the same RDS instance.

Note The remaining storage space of the RDS instance must be at least 1.3 times the size of the source database.

Usage:

EXEC sp_rds_copy_database 'db','db_copy'
  • The first parameter specifies the name of the source database.
  • The second parameter specifies the name of the destination database.

Set the status of a database to online.

T-SQL command:

sp_rds_set_db_online

Instance configuration:

  • RDS High-availability Edition
  • RDS Basic Edition

Description:

If a database is offline, you cannot execute the ALTER DATABASE statement to set the status of the database to online. If the database is offline, you can use this stored procedure to set the status of the database to online.

Usage:

EXEC sp_rds_set_db_online 'db'

The parameter specifies the name of the database whose status you want to set to online.

Grant a user the permissions on some or all databases

T-SQL command:

sp_rds_set_all_db_privileges

Instance configuration:

  • RDS High-availability Edition
  • RDS Basic Edition

Description:

This stored procedure is used to grant a user the permissions on some or all databases.
Note The permissions that the user is allowed must be higher than or equal to the permissions that you want to grant to the user.

Usage:

sp_rds_set_all_db_privileges 'user','db_owner','db1,db2...'
  • The first parameter specifies the name of the user to whom you want to grant permissions.
  • The second parameter specifies the database role that you want to grant to the user.
  • The third parameter specifies the names of the databases whose permissions you want to grant to the user. You can enter multiple database names. If you enter multiple database names, you must separate the database names with commas (,). If you do not specify this parameter, the permissions on all databases are granted to the user.

Delete a database

T-SQL command:

sp_rds_drop_database

Instance configuration:

RDS High-availability Edition
Note The RDS Basic Edition does not support this stored procedure. If you want to delete a database from an RDS instance that runs the RDS Basic Edition, you can execute the DROP DATABASE db statement.

Description:

This stored procedure is used to delete a database from an RDS instance. During the deletion process, ApsaraDB RDS deletes all objects that are associated with the database. If the RDS instance runs the RDS High-Availability Edition, ApsaraDB RDS also deletes the images that are associated with the database and closes the connections to the database.

Usage:

EXEC sp_rds_drop_database 'db'

The parameter specifies the name of the database that you want to delete.

Configure change tracking for a database

T-SQL command:

sp_rds_change_tracking

Instance configuration:

RDS High-availability Edition

Description:

This stored procedure is used to configure change tracking for a database.

Usage:

EXEC sp_rds_change_tracking 'db',1
  • The first parameter specifies the name of the database for which you want to configure change tracking.
  • The second parameter specifies whether to enable change tracking. Valid values:
    • 1: enables change tracking.
    • 0: disables change tracking.

Enable change data capture

T-SQL command:

sp_rds_cdc_enable_db

Instance configuration:

RDS High-availability Edition
Note If the Always On Availability Groups feature is enabled, we recommend that you disable change data capture.

Description:

This stored procedure is used to enable change data capture for a database.

Usage:

USE db
GO
sp_rds_cdc_enable_db

Disable change data capture

T-SQL command:

sp_rds_cdc_disable_db

Instance configuration:

RDS High-availability Edition
Note If the Always On Availability Groups feature is enabled, we recommend that you disable change data capture.

Description:

This stored procedure is used to disable change data capture for a database.

Usage:

USE db
GO
sp_rds_cdc_disable_db

Configure a parameter for an RDS instance

T-SQL command:

sp_rds_configure

Instance configuration:

  • RDS High-availability Edition
  • RDS Basic Edition

Description:

This stored procedure is used to configure a parameter for an RDS instance. If the RDS instance is provided with a secondary RDS instance as a standby, ApsaraDB RDS synchronizes the new parameter setting to the secondary RDS instance.

This stored procedure supports the following parameters:
  • fill factor (%)
  • max worker threads
  • cost threshold for parallelism
  • max degree of parallelism
  • min server memory (MB)
  • max server memory (MB)
  • blocked process threshold (s)

Usage:

EXEC sp_rds_configure 'max degree of parallelism',4
  • The first parameter specifies the name of the parameter that you want to configure.
  • The second parameter specifies the value of the parameter.

Add a linked server to an RDS instance

T-SQL command:

sp_rds_add_linked_server

Instance configuration:

  • SQL Server 2012 or 2016 SE on RDS High-availability Edition
  • SQL Server 2012 or 2016 EE on RDS High-availability Edition
Note The shared instance family does not support this stored procedure. For more information, see Primary instance types.

Description:

This stored procedure is used to add a linked server to an RDS instance. This stored procedure supports distributed transactions. After you add a linked server to the RDS instance, ApsaraDB RDS replicates the configuration of the linked server to the associated secondary RDS instance. This eliminates the need to add the linked server after a primary/secondary switchover.

Usage:

DECLARE
@linked_server_name sysname = N'yangzhao_slb',
@data_source sysname = N'****.sqlserver.rds.aliyuncs.com,3888', --style: 10.1.10.1,1433
@user_name sysname = N'ay15' ,
@password nvarchar(128) = N'******',
@source_user_name sysname = N'test',
@source_password nvarchar(128) = N'******',
@link_server_options xml
= N'
            <rds_linked_server>
                <config option="data access">true</config>
                <config option="rpc">true</config>
                <config option="rpc out">true</config>
            </rds_linked_server>
'

EXEC sp_rds_add_linked_server
@linked_server_name,
@data_source,
@user_name,
@password,
@source_user_name,
@source_password,
@link_server_options

Configure a trace flag for an RDS instance

T-SQL command:

sp_rds_dbcc_trace

Instance configuration:

  • RDS High-availability Edition
  • RDS Basic Edition

Description:

This stored procedure is used to configure a trace flag for an RDS instance. This stored procedure supports only some trace flags. The trace flag that you have configured on the RDS instance is automatically replicated to the associated secondary RDS instance.

Usage:

EXEC sp_rds_dbcc_trace '1222',1/0
  • The first parameter specifies the trace flag that you want to configure for the RDS instance.
  • The second parameter specifies whether to enable the trace flag. Valid values:
    • 1: enables the trace flag.
    • 0: disables the trace flag.

Change the name of a database

T-SQL command:

sp_rds_modify_db_name

Instance configuration:

  • RDS High-availability Edition
  • RDS Cluster Edition
  • RDS Basic Edition

Description:

This stored procedure is used to change the name of a database. After you change the name of a database on an RDS instance that runs the RDS High-availability Edition or Cluster Edition, ApsaraDB RDS automatically rebuilds the replication configuration between the RDS instance and its secondary instance. During the rebuild process, the data of the RDS instance is backed up and restored. If the database occupies a large amount of storage space, make sure that the available storage space of the RDS instance is sufficient.

Usage:

EXEC sp_rds_modify_db_name 'db','new_db'
  • The first parameter specifies the original name of the database.
  • The second parameter specifies the new name of the database.