All Products
Search

This Product

    ApsaraDB RDS:Operations related to a stored procedure

    Document Center

    ApsaraDB RDS:Operations related to a stored procedure

    Last Updated:May 15, 2023

    This topic describes the stored procedures that are supported by ApsaraDB RDS instances that run SQL Server 2019, SQL Server 2017, SQL Server 2016, or SQL Server 2012.

    Replicate data between the databases of an RDS instance

    T-SQL commands

    sp_rds_copy_database

    Supported RDS instances

    • RDS High-availability Edition

    • 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 High-availability Edition

    • 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 High-availability Edition

    • 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 names of the databases 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 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.

    Description

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

    Note

    If you enable the Always On Availability Groups feature, we recommend that you disable change data capture.

    Description

    This stored procedure is used to enable change data capture for a database.

    Usage

    USE db
GO
EXEC sp_rds_cdc_enable_db
GO

    Disable change data capture

    T-SQL commands

    sp_rds_cdc_disable_db

    Supported RDS instances

    RDS High-availability Edition

    Note

    If you enable the Always On Availability Groups feature, we recommend that you disable change data capture.

    Description

    This stored procedure is used to disable change data capture for a database.

    Usage

    USE db
GO
EXEC sp_rds_cdc_disable_db
GO

    Configure a parameter for an RDS instance

    T-SQL commands

    sp_rds_configure

    Supported RDS instances

    • RDS High-availability Edition

    • 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, ApsaraDB RDS synchronizes the new parameter setting to the secondary RDS instance.

    This stored procedure supports the following parameters:

    • fill factor (%)

    • max worker threads

    • cost threshold for parallelism

    • max degree of parallelism

    • min server memory (MB)

    • max server memory (MB)

    • blocked process threshold (s)

    Usage

    EXEC sp_rds_configure 'max degree of parallelism',4

    • 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

    • SQL Server 2012 SE, SQL Server 2016 SE, SQL Server 2017 SE, and SQL Server 2019 SE on RDS High-availability Edition (general-purpose instance family or dedicated instance family)

    • SQL Server 2012 EE and SQL Server 2016 EE on RDS High-availability Edition (general-purpose instance family or dedicated instance family)

    • SQL Server 2017 EE and SQL Server 2019 EE on RDS Cluster Edition (general-purpose instance family or dedicated instance family)

    Note

    The shared instance family does 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, ApsaraDB RDS 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 High-availability Edition

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

      • 0: disables trace flag.

    Rename a database

    T-SQL commands

    sp_rds_modify_db_name

    Supported RDS instances

    • RDS High-availability Edition

    • RDS Cluster Edition

    • 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, ApsaraDB RDS 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 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 an error message reported when I run the sp_rds_copy_database T-SQL command?

    When you run the command, the error message is returned due to insufficient permissions. We recommend that you use a privileged account to run the command.