# Database (DB) import to AMS RDS for Microsoft SQL Server
<a name="db-to-sql-rds"></a>

**Note**  
The AMS API/CLI (amscm and amsskms) endpoints are in the AWS N. Virginia Region, `us-east-1`. Depending on how your authentication is set, and what AWS Region your account and resources are in, you may need to add `--region us-east-1` when issuing commands. You may also need to add `--profile saml`, if that is your authentication method.

The DB import to AMS RDS for SQL Server, process relies on AMS change types (CTs) submitted as requests for change (RFCs), and uses the Amazon RDS API parameters as input. MicroSoft SQL Server is a relational database management system (RDBMS). To learn more, see also: [Amazon Relational Database Service (Amazon RDS)](https://aws.amazon.com/rds/) and [rds](https://docs.aws.amazon.com/cli/latest/reference/rds/index.html) or [Amazon RDS API reference](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/ProgrammingGuide.html).

**Note**  
Make sure each RFC completes successfully before moving on to the next step.

High level import steps:

1. Back up your source on-premises MS SQL database into a .bak (backup) file

1. Copy the .bak file into the transit (encrypted) Amazon Simple Storage Service (S3) bucket

1. Import the .bak into a new DB on your target Amazon RDS MS SQL instance

Requirements:
+ MS SQL RDS stack in AMS
+ RDS stack with restore option (`SQLSERVER_BACKUP_RESTORE`)
+ Transit S3 bucket
+ IAM role with bucket access allowing Amazon RDS to assume the role
+ An EC2 instance with MS SQL Management Studio installed to manage the RDS (can be a workstation on-premises)

# Setting up
<a name="db-to-sql-rds-setup"></a>

Complete these tasks to begin the import process.

1. Submit an RFC to create an RDS stack using Deployment \$1 Advanced stack components \$1 RDS database stack \$1 Create (ct-2z60dyvto9g6c). *Do not use the target DB name* (`RDSDBName` parameter) in the creation request, the target DB will be created during the import. Make sure to allow enough space (`RDSAllocatedStorage` parameter). For details on doing this, see the AMS Change Management Guide [RDS DB Stack \$1 Create](https://docs.aws.amazon.com/managedservices/latest/ctref/deployment-advanced-rds-database-stack-create.html).

1. Submit an RFC to create the transit S3 bucket (if does not exist already) using Deployment \$1 Advanced stack components \$1 S3 storage \$1 Create (ct-1a68ck03fn98r). For details on doing this, see the AMS Change Management Guide [S3 Storage \$1 Create](https://docs.aws.amazon.com/managedservices/latest/ctref/deployment-advanced-s3-storage-create.html).

1. Submit a Management \$1 Other \$1 Other \$1 Update (ct-1e1xtak34nx76) RFC to implement the `customer_rds_s3_role` with these details:

   In the console:
   + Subject: "To support MS SQL Server Database Import, implement `customer_rds_s3_role` on this account.
   + Transit S3 bucket name: *BUCKET\$1NAME*.
   + Contact information: *EMAIL*.

   With an ImportDbParams.json file for the CLI:

   ```
   {
          "Comment": "{"Transit S3 bucket name":"BUCKET_NAME"}",
          "Priority": "High"
        }
   }
   ```

1. Submit a Management \$1 Other \$1 Other \$1 Update RFC requesting AMS to set the `SQLSERVER_BACKUP_RESTORE` option to the RDS created in step 1 (use the stack ID from the step 1 output, and the `customer_rds_s3_role` IAM role in this request, in this request).

1. Submit an RFC to create an EC2 instance (you can use any existing EC2 or on-premise workstation/instances), and install Microsoft SQL Management Studio on the instance.

# Importing the database
<a name="db-to-sql-rds-import-db"></a>

To import the database (DB), follow these steps.

1. Back up your source on-premises database using MS SQL Native backup and restore (see [ Support for native backup and restore in SQL Server](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.Options.BackupRestore.html)). As the result of running that operation, you should have a .bak (backup) file.

1. Upload the .bak file to and existing transit S3 bucket using the AWS S3 CLI or AWS S3 console. For information on transit S3 buckets, see [Protecting data using encryption](https://docs.aws.amazon.com/AmazonS3/latest/userguide/UsingEncryption.html).

1. Import the .bak file into a new DB on your target RDS for SQL Server MS SQL instance (for details on types, see [Amazon RDS for MySQL instance types](https://aws.amazon.com/rds/mysql/instance-types/)):

   1. Log into the EC2 instance (on-premises workstation) and open MS SQL Management Studio

   1. Connect to the target RDS instance created as prerequisite in step \$11. Follow this procedure to connect: [ Connecting to a DB Instance Running the Microsoft SQL Server Database Engine](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToMicrosoftSQLServerInstance.html)

   1. Start the import (restore) job with a new Structured Query Language (SQL) query (for details on SQL queries, see [Introduction to SQL](https://www.w3schools.com/sql/sql_intro.asp)). The target database name must be new (do not use the same name as the database that you previously created). Example without encryption:

      ```
      exec msdb.dbo.rds_restore_database 
              @restore_db_name=TARGET_DB_NAME, 
              
              @s3_arn_to_restore_from='arn:aws:s3:::BUCKET_NAME/FILENAME.bak';
      ```

   1. Periodically check the status of the import job by running this query in a separate window:

      ```
      exec msdb.dbo.rds_task_status;
      ```

      If the status changes to Failed, look for the failure details in the message.

# Cleanup
<a name="db-to-sql-rds-cleanup"></a>

Once you have imported the database, you might want to remove unnecessary resources, follow these steps.

1. Delete the backup file (.bak) from the S3 bucket. You can use the S3 console to do this. For the CLI command to delete an object from an S3 bucket, see [rm](https://docs.aws.amazon.com/cli/latest/reference/s3/rm.html) in the AWS CLI Command Reference.

1. Delete the S3 bucket if you’re not planning to use it. For steps on doing that, see [Delete Stack](https://docs.aws.amazon.com/managedservices/latest/ctref/ex-stack-delete-col.html).

1. If you’re not planning to do MS SQL imports, submit a Management \$1 Other \$1 Other \$1 Update (ct-0xdawir96cy7k) RFC and request that AMS delete the IAM role `customer_rds_s3_role`.