Amazonrds 简明教程

Amazon RDS - MS SQL DBA Tasks

作为RDS服务，MSSQL DB有许多DBA任务可作为托管服务使用。你无法通过外壳访问数据库，但可以通过客户端软件中的控制台或命令执行各种DBA活动。以下是Amazon RDS Ms SQL Server中执行的最常见和最常用的DBA任务。

As a RDS service the MSSQL DB has many DBA tasks available as managed service. You do not have the shell access to the DB, but through the console or through the commands in the client software you can execute various DBA activities. Below are the most common and frequently used DBA tasks performed in Amazon RDS Ms SQL server.

Change Data Capture

CDC获取对表中的数据所做的更改。对用户表所做的更改被捕获在相应的更改表中。这些更改表提供了过去一段时间内更改的历史视图。SQL Server提供的变更数据捕获功能使能够轻松且系统地使用变更数据。

CDC captures changes that are made to the data in the tables. The changes that were made to user tables are captured in corresponding change tables. These change tables provide an historical view of the changes over time. The change data capture functions that SQL Server provides enable the change data to be consumed easily and systematically.

在连接到RDS MSSQL服务器的SSMS中使用以下命令来启用和禁用CDC。

Use the below commands in SSMS connected to RDS MSSQL server to enable and disable CDC.

#Enable CDC for RDS DB Instance
exec msdb.dbo.rds_cdc_enable_db ''

#Disable CDC for RDS DB Instance
exec msdb.dbo.rds_cdc_disable_db ''

接下来，使用stored procedure sp_cdc_enable_table和以下命令来跟踪特定表中的更改。

Next to track the changes in a specific table we use the stored procedure sp_cdc_enable_table with the below command.

#Begin tracking a table
exec sys.sp_cdc_enable_table
   @source_schema           = N''
,  @source_name             = N''
,  @role_name               = N''
, @captured_column_list   = ''
;

Modifying tempdb Database Options

tempdb系统数据库是一个全局资源，它可供连接到SQL Server实例的所有用户使用，并用于保存以下内容

The tempdb system database is a global resource which is available to all users connected to the instance of SQL Server and is used to hold the following

  1. Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.

  2. Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.

  3. Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.

以下是有关如何修改RDS MSSQL tempdb来进行各种DBA活动的一些示例。

Following are examples of How you modify the RDS MSSQL tempdb for various DBA activities.

# setting the size to 100 GB and file growth to 10 percent.
alter database[tempdb] modify file (NAME = N'templog', SIZE=100GB, FILEGROWTH = 10%)

# set the MAXSIZE property to prevent tempdb database from using all available disk space.
alter database [tempdb] modify file (NAME = N'templog', MAXSIZE = 2048MB)

# Shrinking the tempdb Database file size and requests a new size
exec msdb.dbo.rds_shrink_tempdbfile @temp_filename = N'test_file', @target_size = 10;

OFFLINE to ONLINE Transition

你可以使用以下命令将Amazon RDS DB实例上的Microsoft SQL Server数据库从OFFLINE转换为ONLINE。

You can transition your Microsoft SQL Server database on an Amazon RDS DB instance from OFFLINE to ONLINE using the following command.

EXEC rdsadmin.dbo.rds_set_database_online name

Non-English Character Set

在创建RDS MSSQL实例时，标记为DB的默认排序规则是英语。但可以通过应用COLLATE子句和排序规则名称将其更改为另一种非英语语言。以下示例对此进行了说明。

During the creation of RDS MSSQL instance, the default collation marked for the DB is English. But it can be changed to another non-English language by applying the COLLATE clause along with the name of the collation. The below example illustrates that.

CREATE TABLE [dbo].[Account]
(
    [AccountID] [nvarchar](10) NOT NULL,
    [AccountName] [nvarchar](100) COLLATE Japanese_CI_AS NOT NULL
) ON [PRIMARY];