Copy a database in the instance

T-SQL

sp_rds_copy_database

Supported editions:

  • High-Availability Edition
  • Basic Edition

Description

Copies a database in an instance.

Note The remaining storage capacity of the instance must be at least 1.3 times the database size.

Method

EXEC sp_rds_copy_database 'testdb','testdb_copy'
  • The first parameter represents the source database.
  • The second parameter represents the target database.

Bring a database online

T-SQL

sp_rds_set_db_online

Supported editions:

  • High-availability Edition
  • Basic Edition

Description

After you bring a database offline, you cannot directly bring it online by running the ALTER DATABASE statement. Use this stored procedure to bring a database online.

Method

EXEC sp_rds_set_db_online 'db'

The parameter represents the database to be brought online.

Set global database privileges

T-SQL

sp_rds_set_all_db_privileges

Supported editions:

  • High-Availability Edition
  • Basic Edition

Description

Grants the privileges of all or multiple databases to a user.
Note Your current database privileges must be higher or equal to the privileges you want to grant.

Method

sp_rds_set_all_db_privileges 'user','db_owner','db1,db2...'
  • The first parameter represents the user that you want to grant privileges to.
  • The second parameter represents the database role to be granted to the user.
  • The third parameter represents the databases. You can specify one or more databases, and separate multiple database databases with commas (,). (If the parameter is left blank, it indicates all user databases.)

Delete a database

T-SQL

sp_rds_drop_database

Supported editions:

High-Availability Edition
Note The Basic Edition currently does not support this stored procedure. For the Basic Edition, you can delete a database by running DROP DATABASE db .

Description

Delete a database from the instance. Dependent objects will be deleted when a database is deleted. The High-Availability Edition automatically deletes the mirror and terminates the database connection.

Method

EXEC sp_rds_drop_database 'db'

The parameter represents the database to be deleted.

Set change tracking

T-SQL

sp_rds_change_tracking

Supported editions:

High-Availability Edition

Description

Sets change tracking for the database.

Method

EXEC sp_rds_change_tracking 'db',1
  • The first parameter represents the database name.
  • The second parameter indicates whether change tracking is enabled.
    • 1: Enable.
    • 0: Disable.

Enable change data capture (CDC)

T-SQL

sp_rds_cdc_enable_db

Supported editions:

High-Availability Edition
Note If mirroring exists, this stored procedure also removes the availability group. In this case, this stored procedure is not recommended.

Description

Enables change data capture.

Method


USE db
GO
sp_rds_cdc_disable_db

Disables change data capture

T-SQL

sp_rds_cdc_disable_db

Supported editions:

High-Availability Edition
Note If mirroring exists, this stored procedure also removes the availability group. In this case, this stored procedure is not recommended.

Description

Disables change data capture.

Method


USE db
GO
sp_rds_cdc_disable_db

Configure instance parameters

T-SQL

sp_rds_configure

Supported editions:

  • High-availability Edition
  • Basic Edition

Description

Sets instance parameters. If your instance has primary and secondary nodes, the configuration is automatically synchronized from the primary node to the secondary node.

Parameters currently supported:
  • fill factor (%)
  • maximum worker threads
  • cost threshold for parallelism
  • max degree of parallelism
  • min server memory (MB)
  • max server memory (MB)
  • blocked process threshold (s)

Method


EXEC sp_rds_configure 'max degree of parallelism',4
  • The first parameter represents the instance parameters to be set.
  • The second parameter represents the instance parameter value.

Add a linked server

T-SQL

sp_rds_add_linked_server

Supported editions:

  • SQL Server 2012/2016 Standard Edition High-Availability series
  • SQL Server 2012/2016 Enterprise Edition High-Availability series

Description

Adds a linked server to the instance. Supports distributed transactions. The linked server created for both the primary and secondary nodes. If a switchover occurs, you do not need to add the link server again.

Method


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

Set a trace flag

T-SQL statements

sp_rds_dbcc_trace

Supported editions:

  • High-availability Edition
  • Basic Edition

Description

Sets trace flags for the instance. Only partial trace flags are currently supported. If your instance has primary and secondary nodes, the trace flags are automatically synchronized from the primary node to the secondary node.

Method

EXEC sp_rds_dbcc_trace '1222',1/0
  • The first parameter represents the trace flag.
  • The second parameter indicates whether the trace flag is enabled or disabled.
    • 1: Enable.
    • 0: Disable.

Rename a database

T-SQL

sp_rds_rename_database

Supported editions:

Basic Edition

Description

Renames a database.
Note This stored proceudre does not rename the physical database file.

Method

EXEC sp_rds_rename_database 'db','new_db'
  • The first parameter represents the database to be renamed.
  • The second parameter represents the new name of the database.