All Products
Search

This Product

    ApsaraDB RDS:Stored procedures

    Document Center

    ApsaraDB RDS:Stored procedures

    Last Updated:Nov 11, 2024

    This topic describes the stored procedures supported by ApsaraDB 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 master
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

    • 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.

    • You must execute the statement by using a privileged account 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 about the privileged account, see Account permissions in an ApsaraDB RDS for SQL Server instance. For more information, see Modify the permissions of an account.

    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 and RDS Cluster Edition

    Description

    This stored procedure is used to enable 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 = '<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 and RDS Cluster Edition

    Description

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

    Usage

    USE db
GO
-- Disable CDC for a database.
EXEC sp_rds_cdc_disable_db
GO
-- Disable CDC for a 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>'
    
-- Obtain the name of the instance on which CDC is enabled for a 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

    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;

    in-doubt xact resolution

    The way that the system processes uncertain distributed transactions. Valid values:

    • 0: disables automatic processing. This is the default value for RDS instances that do not run RDS Cluster Edition. The system does not automatically process uncertain distributed transactions, and manual processing is required.

    • 1: assumes that the uncertain distributed transactions are automatically committed. If the system does not have sufficient information to process uncertain transactions, the transactions are automatically committed.

    • 2: assumes that the uncertain distributed transactions are automatically rolled back. This is the default value for RDS instances that run RDS Cluster Edition. If uncertain distributed transactions are committed to the system, the uncertain distributed transactions are automatically rolled back.

    EXEC sp_configure 'in-doubt xact resolution', 2;

    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 editions and databased engine versions:

      • SQL Server SE on RDS High-availability Edition: SQL Server 2012, SQL Server 2016, SQL Server 2017, SQL Server 2019, and SQL Server 2022

      • SQL Server EE on RDS High-availability Edition: SQL Server 2012 and SQL Server 2016

      • SQL Server EE (Always On) on RDS Cluster Edition: SQL Server 2017, SQL Server 2019, and SQL Server 2022

    • Instance types: The RDS instance belongs to the general-purpose or dedicated instance family. The shared instance family is not supported.

    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', --The name of the linked server.
@data_source sysname = N'****.sqlserver.rds.aliyuncs.com,3888', --The IP address and port of the destination RDS instance. Format: IP address,Port.
@user_name sysname = N'ay15' , --The logon username of the destination RDS instance.
@password nvarchar(128) = N'******', --The logon password of the destination RDS instance.
@source_user_name sysname = N'test', --The logon username for creating a linked server on the current RDS instance.
@source_password nvarchar(128) = N'******', --The logon password for creating a linked server on the current RDS instance.
--The configuration items that are required to connect the RDS instance to the server. The configuration items are specified in the XML format. In this example, the configuration items include 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 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 an account.