ApsaraDB RDS:Stored procedures

Usage notes

The examples in this topic are written for SQL Server Management Studio (SSMS), which uses GO as a batch separator. If you run stored procedure commands in Data Management Service (DMS), remove the GO keyword — otherwise an error occurs.

Available stored procedures

Update database statistics

T-SQL command: sp_rds_update_db_stats

Updates statistics for a database with configurable sampling percentage, degree of parallelism (DOP), timeout period, and modification threshold.

If you pass only @db_name, or if the SQL Server version is 2008, the system runs sp_updatestats by default. For more information, see the Microsoft documentation on UPDATE STATISTICS.

Syntax

EXEC sp_rds_update_db_stats
    @db_name = 'test_db',          -- Database name (required)
    @sample_percent = 50,          -- Sampling percentage (optional)
    @max_dop = 4,                  -- Degree of parallelism (optional)
    @timeout_seconds = 7200,       -- Timeout in seconds (optional)
    @modification_threshold = 3;   -- Modification threshold (optional)

Parameters

`@modification_threshold` policy

When set to 0 (default), the following Alibaba Cloud-recommended thresholds apply:

To set a custom threshold, calculate: Value = (modified rows × 100) / total rows. A statistics update is triggered when the calculation result is less than or equal to the specified value.

Examples

Set only the database name to follow the sp_updatestats logic:

EXEC sp_rds_update_db_stats @db_name = 'test_db';

Set the sampling percentage to 30%:

EXEC sp_rds_update_db_stats
    @db_name = 'test_db',
    @sample_percent = 30;

Set the maximum degree of parallelism to 4:

EXEC sp_rds_update_db_stats
    @db_name = 'test_db',
    @max_dop = 4;

Set the timeout to 7,200 seconds (2 hours):

EXEC sp_rds_update_db_stats
    @db_name = 'test_db',
    @timeout_seconds = 7200;

Set a custom modification threshold: if a table has 10,000 rows and you want to trigger a statistics update after 100 rows change, set the threshold to 1 (100 × 100 / 10,000 = 1):

EXEC sp_rds_update_db_stats
    @db_name = 'test_db',
    @modification_threshold = 1;

Copy a database within an instance

T-SQL command: sp_rds_copy_database

Supported editions: Basic Edition, High-availability Edition

Copies a database to a new database within the same instance.

The instance must have at least 1.3 times the current database size in available storage.

Not supported for MyBase dedicated clusters for SQL Server.

Syntax

USE db
GO
EXEC sp_rds_copy_database 'db', 'db_copy'
GO

The first parameter specifies the source database. The second parameter specifies the destination database name.

Set a database online

T-SQL command: sp_rds_set_db_online

Supported editions: Basic Edition, High-availability Edition

After a database is set to OFFLINE, ALTER DATABASE cannot restore it to ONLINE. Use this stored procedure instead.

Syntax

USE master
GO
EXEC sp_rds_set_db_online 'db'
GO

The parameter specifies the database to set to ONLINE.

Grant global database permissions

T-SQL command: sp_rds_set_all_db_privileges

Supported editions: Basic Edition, High-availability Edition

Grants a user permissions on all or selected user databases.

The account performing the grant must have permissions equal to or greater than the permissions being granted.

Syntax

sp_rds_set_all_db_privileges 'user', 'db_owner', 'db1,db2...'

• Parameter 1: The user to grant permissions to.

• Parameter 2: The database role to assign.

• Parameter 3: (Optional) A comma-separated list of databases. If omitted, permissions are granted on all user databases.

Delete a database

T-SQL command: sp_rds_drop_database

Supported editions: High-availability Edition

Basic Edition instances do not support this stored procedure. Use DROP DATABASE db instead.

Run this command with a privileged account in a command window that is not connected to the target database. Make sure the account has the required permissions. See Modify the permissions of an account for details.

Deletes a database from an instance. All associated objects are removed. For High-availability Edition instances, the database image is automatically removed and all connections to the database are terminated.

Syntax

USE db
GO
EXEC sp_rds_drop_database 'db'
GO

The parameter specifies the database to delete.

Set change tracking

T-SQL command: sp_rds_change_tracking

Supported editions: High-availability Edition

Sets the change tracking flag for a database.

Syntax

USE db
GO
EXEC sp_rds_change_tracking 'db', 1
GO

• Parameter 1: The database name.

• Parameter 2: 1 to enable, 0 to disable.

Enable change data capture for a database

T-SQL command: sp_rds_cdc_enable_db

Supported editions: High-availability Edition, Cluster Edition

Enables change data capture (CDC) for a database.

Syntax

Enable CDC at the database level:

USE db
GO
EXEC sp_rds_cdc_enable_db
GO

Enable CDC for a specific table:

EXEC sys.sp_cdc_enable_table
    @source_schema = '<schema_name>',
    @source_name = '<table_name>',
    @role_name = '<CDC_role_name>'

Disable change data capture for a database

T-SQL command: sp_rds_cdc_disable_db

Supported editions: High-availability Edition, Cluster Edition

Disables CDC for a database.

Syntax

Disable CDC at the database level:

USE db
GO
EXEC sp_rds_cdc_disable_db
GO

Disable CDC for a specific table:

EXEC sys.sp_cdc_disable_table
    @source_schema = '<schema_name>',
    @source_name = '<table_name>',
    @capture_instance = '<CDC_capture_instance_name>'

To get the CDC capture instance name for a table:

SELECT capture_instance
FROM cdc.change_tables
WHERE source_schema = '<schema_name>'
    AND source_name = '<table_name>'

Configure instance parameters

T-SQL command: sp_rds_configure

Supported instance families: Basic Edition, High-availability Edition

Sets SQL Server instance parameters. For instances with primary and secondary nodes, parameters are automatically synchronized to both nodes. For parameter descriptions, see the Microsoft documentation on server configuration options.

Syntax

EXEC sp_rds_configure '<parameter>', <value>

Supported parameters

Full list of `default full-text language` values

<details> <summary>Click to expand</summary>

</details>

Full list of `default language` values

<details> <summary>Click to expand</summary>

</details>

Add a linked server

T-SQL command: sp_rds_add_linked_server

Supported instances:

• Editions: Cluster Edition, High-availability Edition. Basic Edition is not supported.

• Instance types: General-purpose and Dedicated. Shared instance types are not supported.

• Billing methods: Subscription and Pay-as-you-go. Serverless instances are not supported.

Adds a linked server to an instance. Distributed transactions are supported. A linked server created on an instance is automatically synchronized between primary and secondary nodes, so no reconfiguration is needed after a high-availability (HA) switchover. However, subsequent changes to existing linked servers on the primary node are not synchronized to the secondary node. For more information, see Automatic or manual primary/secondary failover.

Syntax

DECLARE
@linked_server_name sysname = N'yangzhao_slb',          -- Linked server name
@data_source sysname = N'****.sqlserver.rds.aliyuncs.com,3888', -- Destination SQL Server address in IP,Port format
@user_name sysname = N'ay15',                           -- Login name for the destination SQL Server
@password nvarchar(128) = N'******',                    -- Password for the destination SQL Server login
@source_user_name sysname = N'test',                    -- Login name used to create the linked server on the current instance
@source_password nvarchar(128) = N'******',             -- Password for the current instance login
-- Linked server settings in XML format (data access, RPC, and RPC out permissions)
@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

Set a trace flag

T-SQL command: sp_rds_dbcc_trace

Supported editions: Basic Edition, High-availability Edition

Sets a trace flag for the instance. Only specific trace flags are supported. For instances with primary and secondary nodes, trace flags are automatically synchronized.

Syntax

EXEC sp_rds_dbcc_trace '1222', 1

• Parameter 1: The trace flag.

• Parameter 2: 1 to enable, 0 to disable.

Change the name of a database

T-SQL command: sp_rds_modify_db_name

Supported editions: Basic Edition, High-availability Edition, Cluster Edition

Renames a database. Before running, make sure the account has the required permissions on the target database and that the database is online.

For High-availability Edition and Cluster Edition instances, renaming triggers an automatic rebuild of the primary/secondary relationship through a backup and restoration process. If the database is large, check available storage space before proceeding. If storage is insufficient, scale out the instance first.

Syntax

USE master
GO
EXEC sp_rds_modify_db_name 'db', 'new_db'
GO

• Parameter 1: The original database name.

• Parameter 2: The new database name.

Grant server-level roles

T-SQL command: sp_rds_set_server_role

Supported editions: Basic Edition

Grants server-level roles to a login. The supported roles are setupadmin and processadmin. To create other permissions or learn more about account permissions, see Create an account with SA permissions and Account permission list.

Syntax

EXEC sp_rds_set_server_role @login_name = 'test_login', @server_role = 'setupadmin'

• @login_name: The login name.

• @server_role: The role to assign. Valid values: setupadmin, processadmin.

FAQ

Why do I get `Cannot use KILL to kill your own process` when I run `EXEC sp_rds_drop_database 'dbtest'` with a standard account?

Run this command with a privileged account in a command window that is not connected to the target database. Make sure the account has the required permissions. See Modify the permissions of an account for details.