This topic describes the stored procedures supported by ApsaraDB RDS instances that run SQL Server 2012, SQL Server 2016, SQL Server 2017, or 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 database and the destination database are created on the same RDS instance.

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

Usage:

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

Set a database to the online mode

T-SQL command:

sp_rds_set_db_online

Instance configuration:

  • RDS High-availability Edition
  • RDS Basic Edition

Description

After you set a database to the offline mode, you cannot execute the ALTER DATABASE statement to set the database to the online mode. In this case, you can use this stored procedure to set the database to the online mode.

Usage:

USE db
GO
EXEC sp_rds_set_db_online 'db'
GO

The parameter specifies the name of the database that you want to set to the online mode.

Grant the permissions on some or all databases of an RDS instance to a user

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 the permissions on some or all databases of an RDS instance to a user.
Note The permissions of the user on the specified databases 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 one or more database names. If you enter more than one database name, you must separate the database names with commas (,). If you do not configure this parameter, all databases are specified.

Delete a database

T-SQL command:

sp_rds_drop_database

Instance configuration:

RDS High-availability Edition
Note RDS Basic Edition does not support this stored procedure. If you want to delete a database from an RDS instance that runs 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 RDS High-availability Edition, ApsaraDB RDS also deletes the associated images and closes the connections to the database.

Usage:

USE db
GO
EXEC sp_rds_drop_database 'db'
GO

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:

USE db
GO
EXEC sp_rds_change_tracking 'db',1
GO
  • 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 you enable the Always On Availability Groups feature, 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
EXEC sp_rds_cdc_enable_db
GO

Disable change data capture

T-SQL command:

sp_rds_cdc_disable_db

Instance configuration:

RDS High-availability Edition
Note If you enable the Always On Availability Groups feature, 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
EXEC sp_rds_cdc_disable_db
GO

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 SE, SQL Server 2016 SE, SQL Server 2017 SE, and SQL Server 2019 SE Standard on RDS High-availability Edition (general-purpose instance family or dedicated instance family)
  • SQL Server 2012 SE and SQL Server 2016 EE on RDS High-availability Edition (general-purpose instance family or dedicated instance family)
  • SQL Server 2017 EE and SQL Server 2019 EE on RDS Cluster Edition (general-purpose instance family or dedicated instance family)
Note The shared instance family does not support this stored procedure. For more information, see Primary ApsaraDB RDS 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 way, you do not 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.

Rename 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 rename a database. After you rename a database on an RDS instance that runs RDS High-availability Edition or RDS 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:

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

Assign server roles

T-SQL command:

sp_rds_set_server_role

Instance configuration:

RDS Basic Edition

Description

This stored procedure is used to assign server roles to logon users. The roles include setupadmin and processadmin. For more information about account permissions and how to create an account, see Create a system admin account for an ApsaraDB RDS for SQL Server instance and Account permissions in an ApsaraDB RDS for SQL Server instance.

Usage:

EXEC sp_rds_set_server_role @login_name='test_login',@server_role='setupadmin'
  • The first parameter specifies the account name.
  • The second parameter specifies the role name. The available roles are setupadmin and processadmin.

FAQ

Why is an error message reported when I run the sp_rds_copy_database T-SQL command?

When you run the command, the error message is returned due to insufficient permissions. We recommend that you use a privileged account to run the command.