|« PreviousNext »|
|Did this page help you? Yes | No | Tell us about it...|
In order to deliver a managed service experience, Amazon RDS does not provide shell access to DB Instances, and it restricts access to certain system procedures and tables that require advanced privileges. This section describes the Amazon RDS-specific implementations of some common DBA tasks for DB Instances that are running the Microsoft SQL Server database engine.
When working with a SQL Server DB Instance, you can run scripts to modify a newly created database, but you cannot modify the [model] database, the database used as the model for new databases.
In RDS, the recovery model, retention period, and database status are linked. Changes to one can impact the other settings. For example:
Changing a database’s recovery model to “Simple” while backup retention is enabled will result in RDS setting the recovery model to “Full” about five mimutes after the setting was changed.
Setting the backup retention to “0” days results in RDS setting the recovery mode to “Simple.”
Changing a database’s recovery model from “Simple” to any other option while backup retention is set to “0” days results in RDS setting the recovery model back to “Simple.”
Setting a database to “offline” will cause the database to remain “offline.”
SQL Server method
Amazon RDS method
The Database Engine Tuning Advisor is a client application provided by Microsoft that analyzes database workload and recommends an optimal set of indexes for your SQL Server databases based on the kinds of queries you run. Like SQL Server Management Studio, you run Tuning Advisor from a client computer that connects to your RDS DB Instance that is running SQL Server. The client computer can be a local computer that you run on premises within your own network or it can be an Amazon EC2 Windows instance that is running in the same region as your RDS DB Instance.
This section shows how to capture a workload for Tuning Advisor to analyze. This is the preferred process for capturing a workload because RDS restricts host access to the SQL Server instance. The full documentation on Tuning Advisor can be found on MSDN.
To use Tuning Advisor, you must provide what is called a workload to the advisor. A workload is a set of Transact-SQL statements that execute against a database or databases that you want to tune. Database Engine Tuning Advisor uses trace files, trace tables, Transact-SQL scripts, or XML files as workload input when tuning databases. When working with RDS, a workload can be a file on a client computer or a database table on an RDS SQL Server DB accessible to your client computer. The file or the table must contain queries against the databases you want to tune in a format suitable for replay.
For Tuning Advisor to be most effective, a workload should be as realistic as possible. You can generate a workload file or table by performing a trace against your DB Instance. While a trace is running, you can either simulate a load on your DB Instance or run your applications with a normal load.
There are two types of traces: client-side and server-side. A client-side trace is easier to set up and you can watch trace events being captured in real-time in SQL Server Profiler. A server-side trace is more complex to set up and requires some Transact-SQL scripting. In addition, because the trace is written to a file on the RDS DB Instance, storage space is consumed by the trace. It is important to track of how much storage space a running server-side trace uses because the DB Instance could enter a storage-full state and would no longer be available if it runs out of storage space.
For a client-side trace, when a sufficient amount of trace data has been captured in the SQL Server Profiler, you can then generate the workload file by saving the trace to either a file on your local computer or in a database table on an DB Instance that is available to your client computer. The main disadvantage of using a client-side trace is that the trace may not capture all queries when under heavy loads. This could weaken the effectiveness of the analysis performed by the Database Engine Tuning Advisor. If you need to run a trace under heavy loads and you want to ensure that it captures every query during a trace session, you should use a server-side trace.
For a server-side trace, you must get the trace files on the DB Instance into a suitable workload file or you can save the trace to a table on the DB Instance after the trace completes. You can use the SQL Server Profiler to save the trace to a file on your local computer or have the Tuning Advisor read from the trace table on the DB Instance.
To run a client-side trace on a SQL Server DB Instance
Start SQL Server Profiler. It is installed in the Performance Tools folder of your SQL Server instance folder. You must load or define a trace definition template to start a client-side trace.
In the SQL Server Profiler File menu, click New Trace. In the Connect to Server dialog box, enter the DB Instance endpoint, port, master user name, and password of the database you would like to run a trace on.
In the Trace Properties dialog box, enter a trace name and choose a trace definition template. A default template, TSQL_Replay, ships with the application. You can edit this template to define your trace. Edit events and event information under the Events Selection tab of the Trace Properties dialog box. For more information about trace definition templates and using the SQL Server Profiler to specify a client-side trace see the documentation in MSDN.
Start the client-side trace and watch SQL queries in real-time as they execute against your DB Instance.
Select Stop Trace from the File menu when you have completed the trace. Save the results as a file or as a trace table on you DB Instance.
Writing scripts to create a server-side trace can be complex and is beyond the scope of this document. This section contains sample scripts that you can use as examples. As with a client-side trace, the goal is to create a workload file or trace table that you can open using the Database Engine Tuning Advisor.
The following is an abridged example script that starts a server-side trace and captures details to a workload file. The trace initially saves to the file RDSTrace.trc in the D:\RDSDBDATA\Log directory and rolls-over every 100 MB so subsequent trace files are named RDSTrace_1.trc, RDSTrace_2.trc, etc.
DECLARE @file_name NVARCHAR(245) = 'D:\RDSDBDATA\Log\RDSTrace'; DECLARE @max_file_size BIGINT = 100; DECLARE @on BIT = 1 DECLARE @rc INT DECLARE @traceid INT EXEC @rc = sp_trace_create @traceid OUTPUT, 2, @file_name, @max_file_size IF (@rc != 0) BEGIN EXEC sp_trace_setevent @traceid, 10, 1, @on EXEC sp_trace_setevent @traceid, 10, 2, @on EXEC sp_trace_setevent @traceid, 10, 3, @on … EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Profiler' EXEC sp_trace_setstatus @traceid, 1 END
The following example is a script that stops a trace. Note that a trace created by the previous script continues to run until you explicitly stop the trace or the process runs out of disk space.
DECLARE @traceid INT SELECT @traceid = traceid FROM ::fn_trace_getinfo(default) WHERE property = 5 AND value = 1 AND traceid <> 1 IF @traceid IS NOT NULL BEGIN EXEC sp_trace_setstatus @traceid, 0 EXEC sp_trace_setstatus @traceid, 2 END
You can save server-side trace results to a database table and use the database table as the workload for the Tuning Advisor by using the fn_trace_gettable function. The following commands load the results of all files named RDSTrace.trc in the D:\rdsdbdata\Log directory, including all rollover files like RDSTrace_1.trc, into a table named RDSTrace in the current database:
SELECT * INTO RDSTrace FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace.trc', default);
To save a specific rollover file to a table, for example the RDSTrace_1.trc file, specify the name of the rollover file and substitute 1 instead of default as the last parameter to fn_trace_gettable.
SELECT * INTO RDSTrace_1 FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace_1.trc', 1);
Once you create a trace, either as a local file or as a database table, you can then run Tuning Advisor against your RDS instance. Microsoft includes documentation on using the Database Engine Tuning Advisor in MSDN. Using Tuning Advisor with RDS is the same process as when working with a standalone, remote SQL Server instance. You can either use the Tuning Advisor UI on your client machine or use the dta.exe utility from the command line. In both cases, you must connect to the RDS DB Instance using the endpoint for the DB Instance and provide your master user name and master user password when using Tuning Advisor.
The following code example demonstrates using the dta.exe command line utility against an RDS DB Instance with an endpoint of
dta.cnazcmklsdei.us-east-1.rds.amazonaws.com. The example includes the master user
admin and the master user password
test, the example database to tune
RDSDTA and the input workload is a trace file on the local machine named
The example command line code also specifies a
trace session named
RDSTrace1 and specifies output files to the local machine named
for the SQL output script,
RDSTrace.txt for a
result file, and
RDSTrace.xml for an XML file of the analysis. There is also an error table specified on the RDSDTA database named
dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -if C:\RDSTrace.trc -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors
Here is the same example command line code except the input workload is a table on the remote RDS instance named
which is on the
dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -it RDSDTA.dbo.RDSTrace -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors
A full list of dta utility command-line parameters can be found in MSDN.
With Amazon RDS, you can use SQL Server Agent on a DB Instance running SQL Server Standard, Web Edition, or Enterprise Edition. SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, which are called jobs. You can use SQL Server Agent to run T-SQL jobs to rebuild indexes, run corruption checks, and aggregate data in a SQL Server DB Instance.
SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand. For more information, see SQL Server Agent in the SQL Server documentation. You should avoid scheduling jobs to run during the maintenance and backup windows for your DB Instance because these maintenance and backup processes that are launched by AWS could interrupt the job or cause it to be cancelled. Because Amazon RDS backs up your DB Instance, you cannot use SQL Server Agent to create backups.
Because SQL Server Agent is running on a managed host in a DB Instance, there are some actions that are not supported. Running replication jobs and running command-line scripts by using ActiveX, Windows command shell, or Windows PowerShell are not supported. In addition, you cannot manually start, stop, or restart SQL Server Agent because its operation is managed by the host. Email notifications through SQL Server Agent are not available from a DB Instance.
When you create a SQL Server DB Instance, the master user name is enrolled in the SQLAgentUserRole role. To allow an additional login/user to use SQL Server Agent, you must log in as the master user and do the following.
Create another server-level login by using the CREATE LOGIN command.
Create a user in msdb using CREATE USER command, and then link this user to the login that you created in the previous step.
Add the user to the SQLAgentUserRole using the
sp_addrolemember system stored procedure.
For example, suppose your master user name is
myawsmaster and you want to give access to
SQL Server Agent to a user named
theirname with a password
would log in using the master user name and run the following commands.
--Initially set context to master database USE [master]; GO --Create a server-level login named theirname with password theirpassword CREATE LOGIN [theirname] WITH PASSWORD = 'theirpassword'; GO --Set context to msdb database USE [msdb]; GO --Create a database user named theirname and link it to server-level login theirname CREATE USER [theirname] FOR LOGIN [theirname]; GO --Added database user theirname in msdb to SQLAgentUserRole in msdb EXEC sp_addrolemember [SQLAgentUserRole], [theirname];
You cannot use the UI in SQL Server Management Console to delete a SQL Server Agent job. To delete a SQL Server Agent job, run the following T-SQL statement.
EXEC msdb..sp_delete_job @job_name = '<job-name>';
To view the SQL Server Agent log, you can use the RDS console. For information on
viewing log files, see Viewing and Listing Database Log Files. You can also use the stored
rdsadmin.dbo.rds_read_error_log to view the agent log as
CREATE PROCEDURE [dbo].[rds_read_error_log] @index INT = 0, @type INT = 1, @search_str1 VARCHAR(255) = NULL, @search_str2 VARCHAR(255) = NULL, @start_time DATETIME = NULL, @end_time DATETIME = NULL, @sort_order NVARCHAR(4) = N'asc'
Two parameters are important when you call the
rdsadmin.dbo.rds_read_error_log stored procedure:
@index parameter indicates the log that Amazon RDS will read from. The default value of 0 indicates
the current log is used. A value of 1 indicates that the previously rotated log is used.
@type parameter indicates which type of log is read. The default value of 1 indicates that
the SQL Server Error Log is used. A value of 2 indicates that the SQL Server Agent Log is used.
All the other parameters are related to searching and sorting results and they can be kept at their default values.
For example, to read the current SQL Server Agent Log, you execute the following statement, where 0 indicates the current log and 2 indicates the SQL Server Agent Log.
EXEC rdsadmin..rds_read_error_log 0, 2;
You can specify the dbo schema name and list the parameters, but it is not necessary. The following three statements are equivalent to the statement in the previous example.
EXEC rdsadmin.dbo.rds_read_error_log 0, 2; EXEC rdsadmin..rds_read_error_log @index = 0, @type = 2; EXEC rdsadmin.dbo.rds_read_error_log @index = 0, @type = 2;
To read the last rotated log relative to the current SQL Server Agent Log, you execute the following statement, where 1 indicates the previous log and 2 indidates the SQL Server Agent Log.
EXEC rdsadmin..rds_read_error_log 1, 2;
If a rotated log does not exist, for example if the SQL Server Agent Log has never been rotated, then the statement returns the following error message.
Msg 22004, Level 16, State 1, Line 0 xp_readerrorlog() returned error 2, 'The system cannot find the file specified.'
To view the history of an individual SQL Server Agent job in the SQL Server Management Studio, you open Object Explorer, right-click the job, and then click View History.