SQL Server diagnostic support scripts
Following, you can find a description of the diagnostic support scripts available to analyze
an on-premises or Amazon RDS for SQL Server database in your Amazon DMS migration configuration. These
scripts work with either a source or target endpoint. For an on-premises database, run these
scripts in the sqlcmd command-line utility. For more information on using this
utility, see sqlcmd - Use the utility
For an Amazon RDS database, you can't connect using the sqlcmd command-line utility. Instead, run these scripts using any client tool that connects to Amazon RDS SQL Server.
Before running the script, ensure that the user account that you use has the necessary
permissions to access your SQL Server database. For both an on-premises and an Amazon RDS database,
you can use the same permissions you use to access your SQL Server database without the
SysAdmin
role.
Topics
Setting up minimum permissions for an on-premises SQL Server database
To set up the minimum permissions to run for an on-premises SQL Server database
-
Create a new SQL Server account with password authentication using SQL Server Management Studio (SSMS), for example
.on-prem-user
-
In the User Mappings section of SSMS, choose the MSDB and MASTER databases (which gives public permission), and assign the
DB_OWNER
role to the database where you want to run the script. -
Open the context (right-click) menu for the new account, and choose Security to explicitly grant the
Connect SQL
privilege. -
Run the grant commands following.
GRANT VIEW SERVER STATE TO
on-prem-user
; USE MSDB; GRANT SELECT ON MSDB.DBO.BACKUPSET TOon-prem-user
; GRANT SELECT ON MSDB.DBO.BACKUPMEDIAFAMILY TOon-prem-user
; GRANT SELECT ON MSDB.DBO.BACKUPFILE TOon-prem-user
;
Setting up minimum permissions for an Amazon RDS SQL Server database
To run with the minimum permissions for an Amazon RDS SQL Server database
-
Create a new SQL Server account with password authentication using SQL Server Management Studio (SSMS), for example
.rds-user
-
In the User Mappings section of SSMS, choose the MSDB database (which gives public permission), and assign the
DB_OWNER
role to the database where you want to run the script. -
Open the context (right-click) menu for the new account, and choose Security to explicitly grant the
Connect SQL
privilege. -
Run the grant commands following.
GRANT VIEW SERVER STATE TO
rds-user
; USE MSDB; GRANT SELECT ON MSDB.DBO.BACKUPSET TOrds-user
; GRANT SELECT ON MSDB.DBO.BACKUPMEDIAFAMILY TOrds-user
; GRANT SELECT ON MSDB.DBO.BACKUPFILE TOrds-user
;
SQL Server Support Scripts
The following topics describe how to download, review, and run each support script available for SQL Server. They also describe how to review and upload the script output to your Amazon Support case.
awsdms_support_collector_sql_server.sql script
Download the awsdms_support_collector_sql_server.sql
Note
Run this SQL Server diagnostic support script on SQL Server 2014 and higher versions only.
This script collects information about your SQL Server database configuration. Remember to verify the checksum on the script, and if the checksum verifies, review the SQL code in the script to comment out any of the code that you are uncomfortable running. After you are satisfied with the integrity and content of the script, you can run it.
To run the script for an on-premises SQL Server database
-
Run the script using the following sqlcmd command line.
sqlcmd -U
on-prem-user
-Ppassword
-SDMS-SQL17AG-N1 -y 0 -iC:\Users\admin\awsdms_support_collector_sql_server.sql -oC:\Users\admin\DMS_Support_Report_SQLServer.html -dsqlserverdb01The specified sqlcmd command parameters include the following:
-
-U
– Database user name. -
-P
– Database user password. -
-S
– SQL Server database server name. -
-y
– Maximum width of columns output from the sqlcmd utility. A value of 0 specifies columns of unlimited width. -
-i
– Path of the support script to run, in this caseawsdms_support_collector_sql_server.sql
. -
-o
– Path of the output HTML file, with a file name that you specify, containing the collected database configuration information. -
-d
– SQL Server database name.
-
-
After the script completes, review the output HTML file and remove any information that you are uncomfortable sharing. When the HTML is acceptable for you to share, upload the file to your Amazon Support case. For more information on uploading this file, see Working with diagnostic support scripts in Amazon DMS.
With Amazon RDS for SQL Server, you can't connect using the sqlcmd command line utility, so use the following procedure.
To run the script for an RDS SQL Server database
-
Run the script using any client tool that allows you to connect to RDS SQL Server as the
Master
user and save the output as an HTML file. -
Review the output HTML file and remove any information that you are uncomfortable sharing. When the HTML is acceptable for you to share, upload the file to your Amazon Support case. For more information on uploading this file, see Working with diagnostic support scripts in Amazon DMS.