All Products
Search
Document Center

ApsaraDB RDS:Stored procedures

Last Updated:Mar 18, 2024

This topic describes the stored procedures supported by RDS instances that run SQL Server 2012 and later.

Replicate data between the databases of an RDS instance

T-SQL commands

sp_rds_copy_database

Supported RDS instances

  • RDS instances that run RDS High-availability Edition

  • RDS instances that run 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.

  • ApsaraDB MyBase for SQL Server clusters are not supported.

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 commands

sp_rds_set_db_online

Supported RDS instances

  • RDS instances that run RDS High-availability Edition

  • RDS instances that run 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 commands

sp_rds_set_all_db_privileges

Supported RDS instances

  • RDS instances that run RDS High-availability Edition

  • RDS instances that run 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 name of the database 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, the permissions of all databases are granted.

Delete a database from an instance

T-SQL commands

sp_rds_drop_database

Supported RDS instances

RDS instances that run RDS High-availability Edition

Note

Description

This stored procedure is used to delete a database from an RDS instance. During the deletion process, the system deletes all objects that are associated with the database. If the RDS instance runs RDS High-availability Edition, the system 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 commands

sp_rds_change_tracking

Supported RDS instances

RDS instances that run 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 commands

sp_rds_cdc_enable_db

Supported RDS instances

RDS instances that run RDS High-availability Edition

Note

When databases and Always On availability groups are running on your RDS instance, you can use change data capture (CDC) only after Always On availability groups are removed from the RDS instance. We recommend that you do not use CDC.

Description

This stored procedure is used to enable CDC for a database.

Usage

USE db
GO
-- Enable CDC at the database level.
EXEC sp_rds_cdc_enable_db
GO
-- Enable CDC for a specified table
EXEC sys.sp_cdc_enable_table
    @source_schema = '<Schema name>',
    @source_name = '<Table name>',
    @role_name = '<Role name of CDC>' 

Disable CDC

T-SQL commands

sp_rds_cdc_disable_db

Supported RDS instances

RDS instances that run RDS High-availability Edition

Note

If you enable the Always On Availability Groups feature, we recommend that you disable CDC.

Description

This stored procedure is used to disable CDC for a database.

Usage

USE db
GO
-- Disable CDC at the database level.
EXEC sp_rds_cdc_disable_db
GO
-- Disable CDC for a specified table.
EXEC sys.sp_cdc_disable_table
    @source_schema = '<Schema name>',
    @source_name = '<Table name>',
    @capture_instance = '<Name of the instance on which CDC is enabled>'
    
-- Method to obtain the name of the instance on which CDC is enabled for a specific table.
SELECT capture_instance
FROM cdc.change_tables
WHERE source_schema = '<Schema name>'
    AND source_name = '<Table name>' 

Configure a parameter for an RDS instance

T-SQL commands

sp_rds_configure

Supported RDS instances

  • RDS instances that run RDS High-availability Edition

  • RDS instances that run 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, the system synchronizes the new parameter setting to the secondary RDS instance. The following table describes the supported parameters. For more information, see Microsoft documentation.

Parameter

Description

Usage example

fill factor (%)

The percentage of the fill factor for the index page.

EXEC sp_rds_configure 'fill factor (%)', 90;

max worker threads

The maximum number of worker threads that execute queries and process requests in parallel.

EXEC sp_rds_configure 'max worker threads', 100;

cost threshold for parallelism

The overhead threshold for parallel execution.

EXEC sp_rds_configure 'cost threshold for parallelism', 30;

max degree of parallelism

The maximum degree of parallelism for a query.

EXEC sp_rds_configure 'max degree of parallelism', 4;

min server memory (MB)

The minimum amount of memory for the RDS instance.

EXEC sp_rds_configure 'min server memory (MB)', 1024;

max server memory (MB)

The maximum amount of memory for the RDS instance.

EXEC sp_rds_configure 'max server memory (MB)', 4096;

blocked process threshold (s)

The threshold for blocked processes.

EXEC sp_rds_configure 'blocked process threshold (s)', 20;

nested triggers

Specifies whether to enable nested triggers. Valid values:

  • 0: disables nested triggers.

  • 1: enables nested triggers.

Note

This parameter is not supported for RDS instances that are created before May 2023.

EXEC sp_rds_configure 'nested triggers', 1;

Ad Hoc Distributed Queries

Specifies whether to enable ad hoc distributed queries. Valid values:

  • 0: disables ad hoc distributed queries.

  • 1: enables ad hoc distributed queries.

Note

This parameter is not supported for RDS instances that are created before May 2023.

EXEC sp_rds_configure 'Ad Hoc Distributed Queries', 1;

clr enabled

Specifies whether to enable Common Language Runtime (CLR). Valid values:

  • 0: disables CLR.

  • 1: enables CLR.

Note

This parameter is not supported for RDS instances that are created before May 2023.

EXEC sp_rds_configure 'clr enabled', 1;

default full-text language

The default full-text search language. Valid values:

  • 0: uses the default language. The default language is determined by the locale of the operating system.

  • 1033: uses English.

Note

This parameter is not supported for RDS instances that are created before May 2023.

EXEC sp_rds_configure 'default full-text language', 0;

default language

The default language. Valid values:

  • 0: uses the default language. The default language is determined by the locale of the operating system.

  • 1033: uses English.

Note

This parameter is not supported for RDS instances that are created before May 2023.

EXEC sp_rds_configure 'default language', 1033;

max text repl size (B)

The maximum size of the text during replication.

Note

This parameter is not supported for RDS instances that are created before May 2023.

You can execute the following statement to set the maximum size to 100 MB:

EXEC sp_rds_configure 'max text repl size (B)', 104857600;

optimize for ad hoc workloads

Specifies whether to enable dynamic management views that are optimized for ad hoc workloads. Valid values:

  • 0: disables dynamic management views that are optimized for ad hoc workloads.

  • 1: enables dynamic management views that are optimized for ad hoc workloads.

Note

This parameter is not supported for RDS instances that are created before May 2023.

EXEC sp_rds_configure 'optimize for ad hoc workloads', 1;

query governor cost limit

The maximum execution time of the query. Unit: seconds. The value 0 indicates no limits are imposed.

Note

This parameter is not supported for RDS instances that are created before May 2023.

EXEC sp_rds_configure 'query governor cost limit', 10;

recovery interval (min)

The recovery interval.

Note

This parameter is not supported for RDS instances that are created before May 2023.

EXEC sp_rds_configure 'recovery interval (min)', 60;

remote login timeout (s)

The timeout period for the remote logon.

Note

This parameter is not supported for RDS instances that are created before May 2023.

EXEC sp_rds_configure 'remote login timeout (s)', 30;

remote query timeout (s)

The timeout period for the remote query.

Note

This parameter is not supported for RDS instances that are created before May 2023.

EXEC sp_rds_configure 'remote query timeout (s)', 60;

query wait (s)

The period of time a query waits for resources before it times out.

Note

This parameter is not supported for RDS instances that are created before May 2023.

EXEC sp_rds_configure 'query wait (s)', 5;

min memory per query (KB)

The minimum amount of memory consumed by each query.

Note

This parameter is not supported for RDS instances that are created before May 2023.

EXEC sp_rds_configure 'min memory per query (KB)', 1024;

Usage

EXEC sp_rds_configure '<Parameter>',<Parameter value>
  • 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 commands

sp_rds_add_linked_server

Supported RDS instances

  • RDS instances that run SQL Server 2012 SE, SQL Server 2016 SE, SQL Server 2017 SE, SQL Server 2019 SE, and SQL Server 2022 SE on RDS High-availability Edition and use general-purpose or dedicated instance types

  • RDS instances that run SQL Server 2012 EE and SQL Server 2016 EE on RDS High-availability Edition and use general-purpose or dedicated instance types

  • RDS instances that run SQL Server 2017 EE, SQL Server 2019 EE and SQL Server 2022 EE on RDS Cluster Edition and use general-purpose or dedicated instance types

Note

The shared instance types do 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, the system 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 commands

sp_rds_dbcc_trace

Supported RDS instances

  • RDS instances that run RDS High-availability Edition

  • RDS instances that run 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 commands

sp_rds_modify_db_name

Supported RDS instances

  • RDS instances that run RDS High-availability Edition

  • RDS instances that run RDS Cluster Edition

  • RDS instances that run 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, the system 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, make sure that the available storage 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 commands

sp_rds_set_server_role

Supported RDS instances

RDS instances that run 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 the Cannot use KILL to kill your own process. error reported when I execute the EXEC sp_rds_drop_database 'dbtest'; statement by using a standard account?

You must use a privileged account to execute the statement in the CLI of a database other than the required database. Make sure that the privileged account has the permissions to perform operations on the required database. For more information account the privileged account, see Account permissions in an ApsaraDB RDS for SQL Server instance. For more information, see Modify the permissions of a standard account on an ApsaraDB RDS for SQL Server instance.