ApsaraDB RDS:Stored procedures

Usage notes

The commands in this topic are intended for execution in SQL Server Management Studio (SSMS). These commands include GO as a batch separator. If you plan to run stored procedure commands in Data Management Service (DMS), do not include the GO keyword. Otherwise, an error occurs.

• Update database statistics

• Copy a database within an instance

• Set a database online

• Grant global database permissions

• Delete a database

• Set change tracking

• Enable change data capture for a database

• Disable change data capture for a database

• Configure instance parameters

• Add a linked server

• Set a trace flag

• Change the name of a database

• Grant server-level roles

• Manage db_owner permissions for the tempdb database

Update database statistics

T-SQL command

sp_rds_update_db_stats

Description

Updates database statistics information in a flexible and efficient manner. You can configure multiple dimensions, such as sample rate, degree of parallelism, timeout period, and threshold percentage.

Usage

-- The following example shows how to use multiple parameters.
-- Update the statistics information for the test_db database. Set the sample rate to 50%, the degree of parallelism to 4, the timeout period to 7200 seconds, and the modification threshold to 3.
EXEC sp_rds_update_db_stats        
    @db_name = 'test_db',          -- Database name (required)
    @sample_percent = 50,          -- Sample rate (optional)
    @max_dop = 4,                  -- Degree of parallelism (optional, not supported for SQL Server 2012 and earlier)
    @timeout_seconds = 7200,       -- Timeout period in seconds (optional)
    @modification_threshold = 3;   -- Modification threshold (optional)

-- Set only the database name to follow the statistics information update logic of Microsoft sp_updatestats.
EXEC sp_rds_update_db_stats @db_name = 'test_db';

-- Set the sample 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 period to 7200 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 instance editions

Basic Edition, High-availability Edition

Description

Copies a database within an instance.

Usage

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

• The first parameter specifies the database to copy.

• The second parameter specifies the destination database.

Set a database online

T-SQL command

sp_rds_set_db_online

Supported instance editions

Basic Edition, High-availability Edition

Description

After you set a database to OFFLINE, you cannot use ALTER DATABASE to set it back to ONLINE. You can use this stored procedure to set the database to ONLINE.

Usage

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 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 to whom you want to grant permissions.

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

• The third parameter specifies the databases. You can specify one or more databases separated by commas. This parameter is optional. If you do not specify this parameter, permissions are granted on all user databases.

Delete a database

T-SQL command

sp_rds_drop_database

Supported instance editions

High-availability Edition

Description

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

Usage

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 instance editions

High-availability Edition

Description

Sets the change tracking flag for a database.

Usage

USE db
GO
EXEC sp_rds_change_tracking 'db',1
GO

• The first parameter specifies the database name.

• The second parameter specifies whether to enable the feature.

  • 1: Enable.

  • 0: Disable.

Enable change data capture for a database

T-SQL command

sp_rds_cdc_enable_db

Supported instance editions

High-availability Edition, Cluster Edition

Description

Enables change data capture (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 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 instance editions

High-availability Edition, Cluster Edition

Description

Disables 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 specific table.
EXEC sys.sp_cdc_disable_table
    @source_schema = '<schema_name>',
    @source_name = '<table_name>',
    @capture_instance = '<CDC_capture_instance_name>'
    
-- Method to get the CDC capture instance name for a specific 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 editions

Basic Edition, High-availability Edition

Description

Configures instance parameters. If the instance has a primary and a secondary node, the parameters are automatically synchronized. The following parameters are supported. For more information about how to use these parameters, see the official Microsoft documentation.

Usage

EXEC sp_rds_configure '<parameter>',<value>

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

• The second parameter specifies the value of the instance parameter.

Add a linked server

T-SQL command

sp_rds_add_linked_server

Supported instances

• Instance editions: Cluster Edition and 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.

Description

Adds a linked server to an instance. Distributed transactions are supported. The linked server is automatically created on the primary and secondary nodes of the instance. You do not need to reconfigure it after a high availability (HA) switchover. For more information, see Automatic or manual primary/secondary failover.

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 in the IP,Port format.
@user_name sysname = N'ay15' , -- The logon username for the destination SQL Server.
@password nvarchar(128) = N'******', -- The password for the logon username of the destination SQL Server.
@source_user_name sysname = N'test', -- The logon username used to create the linked server on the current SQL Server instance.
@source_password nvarchar(128) = N'******', -- The password for the logon username used to create the linked server on the current SQL Server instance.
-- Settings for the linked server, passed in XML format. This example shows the settings for 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 instance editions

Basic Edition, High-availability Edition

Description

Sets a trace flag for the instance. Only specific trace flags are supported. If the instance has a primary and a secondary node, the trace flags are automatically synchronized.

Usage

EXEC sp_rds_dbcc_trace '1222',1/0

Change the name of a database

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

Grant server-level roles

T-SQL command

sp_rds_set_server_role

Supported instance editions

Basic Edition

Description

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

Usage

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

• The first parameter specifies the username.

• The second parameter specifies the role name. The supported roles are setupadmin and processadmin.

Manage db_owner permissions for the tempdb database

T-SQL command

sp_rds_manage_tempdb_user

Supported instance editions

Basic Edition, High-availability Edition, Cluster Edition

Description

Grants or revokes the db_owner permission on the tempdb database for a specified account.

Usage

EXEC sp_rds_manage_tempdb_user 
    @login_name = '<login_name>', 
    @action = '<action>';

• The first parameter specifies the username.

• The second parameter specifies the action type. Supported values are create (grant permission) and drop (revoke permission).

FAQ

Q: Why do I receive the Cannot use KILL to kill your own process. error after I run the EXEC sp_rds_drop_database 'dbtest'; command using an account with standard permissions?

A: You can run this command using a privileged account in a command window that is not connected to the destination database. Make sure that the account has the required permissions on the destination database. For more information, see Modify the permissions of an account.