ApsaraDB RDS:Stored procedures

Usage

The commands in this topic are for SSMS and use GO as the batch command separator. If you run stored procedure commands in DMS, do not include the GO keyword. Otherwise, an error will occur.

• Update database statistics

• Copy a database within an instance

• Set a database online

• Grant global database permissions

• Delete a database

• Configure change tracking

• Enable change data capture

• Disable change data capture

• Configure instance parameters

• Add a linked server

• Set trace flags

• Rename a database

• Grant server roles

Update database statistics

T-SQL command

sp_rds_update_db_stats

Description

Updates database statistics flexibly and efficiently. You can configure parameters such as sampling rate, degree of parallelism, timeout, and modification threshold.

Usage

-- This is a comprehensive example that includes multiple parameters.
-- Update database statistics for the test_db database. Set the sampling rate to 50%, the degree of parallelism to 4, the timeout to 7,200 seconds, and the modification threshold to 3.
EXEC sp_rds_update_db_stats        
    @db_name = 'test_db',          -- Database name (required)
    @sample_percent = 50,          -- Sampling rate (optional)
    @max_dop = 4,                  -- Degree of parallelism (optional, not supported in SQL Server 2012 and earlier)
    @timeout_seconds = 7200,       -- Timeout in seconds (optional)
    @modification_threshold = 3;   -- Modification threshold (optional)

-- Specify only the database name. The update follows the logic of Microsoft's sp_updatestats.
EXEC sp_rds_update_db_stats @db_name = 'test_db';

-- Set the sampling rate 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;

Copy a database within an instance

T-SQL command

sp_rds_copy_database

Supported editions

Basic Edition and High-availability Edition

Description

Creates a copy of a database within the same instance.

Usage

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

• The first parameter is the name of the source database.

• The second parameter is the name of the destination database.

Bring a database online

T-SQL command

sp_rds_set_db_online

Supported editions

RDS Basic Edition and RDS High-availability Edition

Description

After you set a database to OFFLINE, you cannot use the ALTER DATABASE statement to bring it back online. Use this stored procedure instead.

Usage

USE master
GO
EXEC sp_rds_set_db_online 'db'
GO

The name of the database to bring online.

Global database permissions

T-SQL command

sp_rds_set_all_db_privileges

Supported instance editions

Basic Edition, High-availability Edition

Description

Usage

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

• The first parameter specifies the user who receives the permissions.

• The second parameter specifies the database role to grant to the user.

• The third parameter specifies one or more target databases, separated by commas. This parameter is optional. Omitting it grants permissions on all user databases.

Delete a database

T-SQL command

sp_rds_drop_database

Supported editions

High-availability Edition

Description

Deletes a database from an instance. This operation removes all associated objects. For High-availability Edition instances, this operation also removes the database mirror and terminates all connections to the database.

Usage

USE master
GO
EXEC sp_rds_drop_database 'db'
GO

The parameter specifies the name of the database to delete.

Configure change tracking

T-SQL command

sp_rds_change_tracking

Supported instance series

High-availability Edition

Description

Enables or disables change tracking for a database.

Usage

USE db
GO
EXEC sp_rds_change_tracking 'db',1
GO

• The first parameter is the database name.

• The second parameter specifies whether to enable or disable change tracking. Valid values:

  • 1: Enables change tracking.

  • 0: Disables change tracking.

Enable change data capture

T-SQL command

sp_rds_cdc_enable_db

Supported instance series

High-availability Edition and Cluster Edition

Description

Enables change data capture (CDC) for a database.

Usage

USE db
GO
-- Enable CDC for a database.
EXEC sp_rds_cdc_enable_db
GO
-- Enable CDC for a table.
EXEC sys.sp_cdc_enable_table
    @source_schema = '<schema name>',
    @source_name = '<table name>',
    @role_name = '<CDC role name>'

Disable change data capture

T-SQL command

sp_rds_cdc_disable_db

Supported instance series

High-availability Edition, Cluster Edition

Description

Disables change data capture (CDC) for a database.

Usage

USE db
GO
-- Disable change data capture (CDC) at the database level.
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 = '<capture_instance_name>'
    
-- Get the capture instance name for a specific table.
SELECT capture_instance
FROM cdc.change_tables
WHERE source_schema = '<schema_name>'
    AND source_name = '<table_name>'

RDS instance parameters

T-SQL command

sp_rds_configure

Supported instance families

Basic Edition and High-availability Edition

Description

Configure the following instance parameters. For primary/standby instances, settings are synchronized automatically. For more details, see Microsoft documentation.

Usage

EXEC sp_rds_configure '<parameter>',<parameter value>

• The first parameter specifies which instance configuration parameter to set.

• The second parameter is its value.

Add a linked server

T-SQL command

sp_rds_add_linked_server

Supported instances

• Instance edition: Cluster Edition and High-availability Edition. Basic Edition is not supported.

• Instance type: general-purpose and dedicated. shared instances are not supported.

• Billing method: subscription and pay-as-you-go. serverless instances are not supported.

Description

This command adds a linked server to an instance and supports distributed transactions. A linked server created on the primary instance automatically synchronizes to the secondary instance. You do not need to reconfigure the linked server after a primary/secondary switchover. However, modifications to existing linked servers on the primary instance are not synchronized to the secondary instance. For more information, see Automatic or manual primary/secondary switchover.

Usage

DECLARE
@linked_server_name sysname = N'yangzhao_slb', --The name of the linked server.
@data_source sysname = N'****.sqlserver.rds.aliyuncs.com,3888', --The IP address and port number of the destination SQL Server instance. Format: IP,Port
@user_name sysname = N'ay15' , --The username for the destination SQL Server instance.
@password nvarchar(128) = N'******', --The password for the destination user.
@source_user_name sysname = N'test', --The username on the source instance.
@source_password nvarchar(128) = N'******', --The password for the source user.
--Server options for the linked server in XML format. This example sets permissions for data access, rpc, and rpc out.
@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

T-SQL command

sp_rds_dbcc_trace

Supported editions

Basic Edition, High-availability Edition

Description

This stored procedure sets a trace flag for an RDS instance. It currently supports only a subset of trace flags. For primary/secondary instances, the platform automatically synchronizes this setting.

Usage

EXEC sp_rds_dbcc_trace '1222',1/0

Rename a database

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

Grant server roles

T-SQL command

sp_rds_set_server_role

Supported editions

Basic Edition

Description

Grants a server role to a login. The available roles are setupadmin and processadmin. To create accounts with other permissions or to learn more about account permissions, see Create an account with SA permissions and Account permissions.

Usage

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

• Specifies the login name.

• Specifies the role name. Valid values are setupadmin and processadmin.

FAQ

Q: Why do I get the Cannot use KILL to kill your own process. error when running the EXEC sp_rds_drop_database 'dbtest'; command with a standard account?

A: Run the command using a privileged account from a command window connected to a different database. Ensure that the account has the required permissions on the target database. For more information, see Modify account permissions.