# Migrating an Amazon RDS for SQL Server Database to an Amazon S3 Data Lake This walkthrough gets you started with the process of migrating from an Amazon Relational Database Service (Amazon RDS) for Microsoft SQL Server to Amazon Simple Storage Service (Amazon S3) cloud data lake using AWS Database Migration Service (AWS DMS). For most organizations, data is distributed across multiple systems and data stores to support varied business needs. On-premises data stores struggle to scale performance as data sizes and formats grow exponentially for analytics and reporting purposes. These limitations in data storage and management limit efficient and comprehensive analytics. Amazon S3 based data lakes provide reliable and scalable storage, where you can store structured, semi-structured and unstructured datasets for varying analytics needs. You can integrate Amazon S3 based data lakes with distributed processing frameworks such as Apache Spark, Apache Hive, and Presto to decouple compute and storage, so that both can scale independently. **Topics** + [ ## Why Amazon S3? ](#chap-rdssqlserver2s3datalake.whys3) + [ ## Why AWS DMS? ](#chap-rdssqlserver2s3datalake.whydms) + [ ## Solution overview ](#chap-rdssqlserver2s3datalake.overview) + [ # Prerequisties for migrating from an Amazon RDS for SQL Server database to an Amazon S3 data lake ](chap-rdssqlserver2s3datalake.prerequisites.md) + [ # Step-by-step Amazon RDS for SQL Server database to an Amazon S3 data lake migration walkthrough ](chap-rdssqlserver2s3datalake.steps.md) ## Why Amazon S3? Amazon S3 is an object storage service for structured, semi-structured, and unstructured data that offers industry-leading scalability, data availability, security, and performance. With a data lake built on Amazon S3, you can use native AWS services, optimize costs, organize data, and configure fine-tuned access controls to meet specific business, organizational, and compliance requirements. Amazon S3 is designed for 99.999999999% (11 9s) of data durability. The service automatically creates and stores copies of all uploaded S3 objects across multiple systems. This means your data is available when needed and protected against failures, errors, and threats. Amazon S3 is secure by design, scalable on demand, and durable against the failure of an entire AWS Availability Zone. You can use AWS native services and integrate with third-party service providers to run applications on your data lake. ## Why AWS DMS? Data lakes typically require building, configuring, and maintaining multiple data ingestion pipelines from cloud and on-premises data stores. Traditionally, databases can be loaded once with data ingestion tools such as import, export, bulk copy, and so on. Ongoing changes are either not possible or are implemented by bookmarking the initial state. Setting up a data lake using these methods can present challenges ranging from increased load on the source database to overheads while carrying schema changes. AWS DMS supports a one-time load and near-real-time ongoing replication making the data migration seamless, while supporting multiple source and target database platforms. One of the common use cases is the need to derive insights on data stored in several sources. For example, you may need to identify monthly sales for a specific year on sales data stored on different database instances. As a part of this walkthrough, we will configure AWS DMS to move data from an Amazon RDS for SQL Server database instance to Amazon S3 for a sales analytics use case. **Note** This introductory exercise doesn’t cover all use cases of migrating to Amazon S3 but provides an overview of the migration process using AWS DMS. This example covers commonly faced problems and describes best practices to follow when migrating to an Amazon S3 data lake. ## Solution overview The following diagram displays a high-level architecture of the solution, where we use AWS DMS to move data from Microsoft SQL Server databases hosted on Amazon Relational Database Service (Amazon RDS) to Amazon Simple Storage Service (Amazon S3). ![\[A high-level architecture diagram of the migration solution.\]](http://docs.aws.amazon.com/dms/latest/sbs/images/sbs-rdssqlserver2s3datalake-solution-overview.png) The following diagram shows the structure of the Amazon S3 bucket from the preceding diagram. ![\[bucket structure.\]](http://docs.aws.amazon.com/dms/latest/sbs/images/sbs-rdssqlserver2s3datalake-s3-bucket-structure.png) To replicate data, you need to create and configure the following artifacts in AWS DMS: + **Replication Instance** — An AWS managed instance that hosts the AWS DMS engine. You control the type or size of the instance based on the workload you plan to migrate. + **Source Endpoint** — An endpoint that provides connection details, data store type, and credentials to connect to a source database. For this use case, we will configure the source endpoint to point to the Amazon RDS for SQL Server database. + **Target Endpoint** — AWS DMS supports several target systems including Amazon RDS, Amazon Aurora, Amazon Redshift, Amazon Kinesis Data Streams, Amazon S3, and more. For the use case, we will configure Amazon S3 as the target endpoint. + **Replication Task** — A task that runs on the replication instance and connects to endpoints to replicate data from the source database to the target database For this walkthrough, we will use the `AdventureWorks` sample database on an Amazon RDS for SQL Server instance as the base data for the walkthrough. The `AdventureWorks` database holds sales, marketing, and order data. We will use AWS DMS to move sales data from the source database to Amazon S3 object store, which can be used as a data lake for downstream analytics needs. **Note** You can refer to [Migrating a SQL Server Always On Database to Amazon Web Services](chap-manageddatabases.sqlserveralwayson.md) for details on migrating from a Microsoft SQL Server Always On database instance. We will create an AWS DMS task, which will perform a one-time full load to migrate a point in time snapshot and will then stream incremental data to the target Amazon S3 bucket. This way, sales data in the S3 bucket will be kept in sync with the source database. # Prerequisties for migrating from an Amazon RDS for SQL Server database to an Amazon S3 data lake The following prerequisites are required to complete this walkthrough: + Understand Amazon Relational Database Service (Amazon RDS), the applicable database technologies, and SQL. + Create a user with AWS Identity and Access Management (IAM) credentials that allows you to launch Amazon RDS and AWS Database Migration Service (AWS DMS) instances in your AWS Region. For information about IAM credentials, see [Create an IAM user](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_GettingStarted.SettingUp.html#CHAP_SettingUp.IAM). + Understand the Amazon Virtual Private Cloud (Amazon VPC) service and security groups. For information about using Amazon VPC with Amazon RDS, see [Amazon Virtual Private Cloud (VPCs) and Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_VPC.html). For information about Amazon RDS security groups, see [Controlling access with security groups](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.RDSSecurityGroups.html). + Understand the supported features and limitations of AWS DMS. For information about AWS DMS, see [What is Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html). + Understand how to work with Microsoft SQL Server as a source and Amazon S3 data lake as a target. For information about working with SQL Server as a source, see [Using a Microsoft SQL Server database as a source](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html). For information about working with Amazon S3 as a target, see [Using Amazon S3 as a target](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.S3.html). + Understand the supported data type conversion options for SQL Server and Amazon S3. For information about data types for SQL Server as a source, see [Source data types for SQL Server](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html#CHAP_Source.SQLServer.DataTypes). For information about data types for Amazon S3 as a target (Parquet only), see [Target data types for S3 Parquet](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.S3.html#CHAP_Target.S3.DataTypes). + Audit your source SQL Server database. For each schema and all the objects under each schema, determine whether any of the objects are no longer being used. Deprecate these objects on the source SQL Server database, because there’s no need to migrate them if they aren’t being used. For more information about AWS DMS, see [Getting started with Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_GettingStarted.html). To estimate what it will cost to run this walkthrough on AWS, you can use the AWS Pricing Calculator. For more information, see [https://calculator.aws/](https://calculator.aws/). To avoid additional charges, delete all resources after you complete the walkthrough. # Step-by-step Amazon RDS for SQL Server database to an Amazon S3 data lake migration walkthrough The following steps provide instructions for migrating an Amazon RDS for SQL Server database to an Amazon S3 data lake. These steps assume that you have already prepared your source database as described in [Prerequisties for migrating from an Amazon RDS for SQL Server database to an Amazon S3 data lake](chap-rdssqlserver2s3datalake.prerequisites.md). **Topics** + [ # Step 1: Create an AWS DMS Replication Instance ](chap-rdssqlserver2s3datalake.steps.createreplicationinstance.md) + [ # Step 2: Configure a Source Amazon RDS for SQL Server Database ](chap-rdssqlserver2s3datalake.steps.configuresource.md) + [ # Step 3: Create an AWS DMS Source Endpoint ](chap-rdssqlserver2s3datalake.steps.sourceendpoint.md) + [ # Step 4: Configure a Target Amazon S3 Bucket ](chap-rdssqlserver2s3datalake.steps.targets3bucket.md) + [ # Step 5: Configure an AWS DMS Target Endpoint ](chap-rdssqlserver2s3datalake.steps.targetendpoint.md) + [ # Step 6: Create an AWS DMS Task ](chap-rdssqlserver2s3datalake.steps.createtask.md) + [ # Step 7: Run the AWS DMS Task ](chap-rdssqlserver2s3datalake.steps.runtask.md) # Step 1: Create an AWS DMS Replication Instance To create an AWS Database Migration Service (AWS DMS) replication instance, see [Creating a replication instance](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_ReplicationInstance.Creating.html). Usually, the full load phase is multi-threaded (depending on task configurations) and has a greater resource footprint than ongoing replication. Consequently, it’s advisable to start with a larger instance class and then scale down once the task is in the ongoing replication phase. Moreover, if you intend to migrate your workload using multiple tasks, monitor your replication instance metrics and re-size your instance accordingly. For this use case, we will migrate a subset (the Sales schema) of the `AdventureWorks` database, which is over 3 GB in size. Because we perform a heterogenous migration without many LOB columns, we can start with a compute optimized instance like c5.xlarge running the latest AWS DMS engine version. We can later scale up or down based on resource utilization during task execution. **Note** Scaling replication instance during full load and ongoing replication phases is usually based on CloudWatch metrics such as CPU, memory, I/O, and so on. Choosing the appropriate replication instance class and size depends on several factors such as number of tasks, table size, DML activity, size of transactions, Large Objects (LOB), and so on. This is out of scope for this walkthrough. To learn more about these topics, see [Choosing replication instance types](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_ReplicationInstance.Types.html) and [Sizing a replication instance](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.SizingReplicationInstance.html). To create an AWS DMS replication instance, do the following: 1. Sign in to the AWS Management Console, and open the [AWS DMS console](https://console.aws.amazon.com/dms/v2). 1. If you are signed in as an AWS Identity and Access Management (IAM) user, you must have the appropriate permissions to access AWS DMS. For more information about the permissions required, see [IAM permissions](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Security.html#CHAP_Security.IAMPermissions). 1. On the Welcome page, choose **Create replication instance** to start a database migration. 1. On the **Create replication instance** page, specify your replication instance information. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/chap-rdssqlserver2s3datalake.steps.createreplicationinstance.html) 1. Choose **Create**. # Step 2: Configure a Source Amazon RDS for SQL Server Database One of the primary considerations when setting up AWS DMS replication is the load that it induces on the source database. During full load, AWS DMS tasks initiate two or three connections for each table that is configured for parallel load. Because AWS DMS settings and data volumes vary across tasks, workloads, and even across different runs of the same task, providing an estimate of resource utilization that applies for all use cases is difficult. Ongoing replication is single-threaded and it usually consumes less resources than full load. Providing estimates for change data capture (CDC) resource utilization has the same challenges described before. That said, you can estimate the expected increase in load on your source Amazon RDS instance, by running test AWS DMS tasks on replicas of your source Amazon RDS for SQL Server instance and monitoring the CPU, memory, IO and throughput metrics. For our source database, we use an `m5.xlarge` Amazon RDS instance running Microsoft SQL Server 2019. While the steps for Amazon RDS for SQL Server creation are out of scope for this walkthrough (for more information, see [Prerequisties for migrating from an Amazon RDS for SQL Server database to an Amazon S3 data lake](chap-rdssqlserver2s3datalake.prerequisites.md)), make sure that your Amazon RDS instance has **Automatic Backups** turned on so that the recovery model for the database is set to **FULL**. This is a pre-requisite for ongoing replication with AWS DMS. You can turn on these settings when you create or modify an existing Amazon RDS instance. The following image displays the database settings required for ongoing replication with AWS DMS. ![\[Database backup settings required for ongoing replication.\]](http://docs.aws.amazon.com/dms/latest/sbs/images/sbs-rdssqlserver2s3datalake-backup-settings.png) To perform the full load phase, AWS DMS requires read privileges to the tables in scope for migration. For more information about required permissions, see [Permissions for full load only tasks](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html#CHAP_Source.SQLServer.Permissions). Connect to the Amazon RDS for SQL Server instance and run the following queries. Use a login with master user privileges for both full load and CDC. ``` USE AdventureWorks; CREATE LOGIN dms_user WITH PASSWORD = 'password' CREATE USER dms_user FOR LOGIN dms_user ALTER ROLE [db_datareader] ADD MEMBER dms_user ALTER ROLE [db_owner] ADD MEMBER dms_user GRANT VIEW DATABASE STATE to dms_user USE master; GRANT VIEW SERVER STATE TO dms_user ``` **Note** Here, we create a new user to perform the migration. You can skip this step if you plan to use existing logins and users that have the required privileges. Turn on MS-CDC for your Amazon RDS for SQL Server database instance at the database level. ``` exec msdb.dbo.rds_cdc_enable_db 'AdventureWorks' ``` Because we migrate all tables in the `Sales` schema of the `AdventureWorks` database, we need to identify the total number of tables. ``` SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM information_schema.tables WHERE TABLE_SCHEMA = 'Sales' ORDER BY TABLE_NAME ``` Then we need to divide tables in the following groups: + Tables with a primary key. + Tables with a unique index without primary key. + Tables without a primary key and unique index. We use the information\$1schema to identify tables that have a primary key or a unique index without a primary key. ``` SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.CONSTRAINT_TYPE, CONSTRAINT_NAME FROM information_schema.table_constraints a JOIN information_schema.tables b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA AND a.TABLE_NAME = b.TABLE_NAME WHERE b.TABLE_TYPE = 'BASE TABLE' AND a.TABLE_SCHEMA = 'Sales' AND a.CONSTRAINT_TYPE in ('UNIQUE','PRIMARY KEY') ORDER BY a.TABLE_SCHEMA, a.TABLE_NAME ``` The query results show that the task has 19 tables and all of them have primary keys. For all these tables, run the following query to turn on MS-CDC at the table level. ``` exec sys.sp_cdc_enable_table @source_schema = N'Sales', @source_name = N'table_name', @role_name = NULL, @supports_net_changes = 1 ``` Now, set the retention period for changes to be available on the source using the following commands. Set the `pollinginterval` value to 86399 seconds to increase the retention of changes on the Amazon RDS for SQL Server instance. ``` EXEC sys.sp_cdc_change_job @job_type = 'capture', @pollinginterval = 86399 exec sys.sp_cdc_stop_job @job_type = 'capture' exec sys.sp_cdc_start_job @job_type = 'capture' exec sys.sp_cdc_help_jobs ``` Set the polling interval on your secondary database to 86399 seconds too. For most use cases these settings should be enough. For databases that have a large number of transactions, you need to make additional configuration changes to make sure that the transaction log has optimal retention. For more information, see [Optional settings when using Amazon RDS for SQL Server as a source](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html#CHAP_Source.SQLServer.OptionalSettings). For more information about ongoing replication, see [Setting up ongoing replication on a Cloud SQL Server DB instance](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html#CHAP_Source.SQLServer.Configuration). **Note** AWS DMS does not support replicating ongoing changes from views. For more information, see [Selection rules and actions](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Selections.html). In this walkthrough, we focus on migrating the tables and do not include views in the migration scope. You should also look at estimating the number of records in the tables you are going to migrate as this is a useful consideration while configuring AWS DMS tasks. # Step 3: Create an AWS DMS Source Endpoint After you configured the AWS Database Migration Service (AWS DMS) replication instance and the source Amazon RDS for SQL Server instance, ensure connectivity between these two instances. To ensure that the replication instance can access the server and the port for the database, make changes to the relevant security groups and network access control lists. For more information about your network configuration, see [Setting up a network for a replication instance](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_ReplicationInstance.VPC.html). After you completed the network configurations, you can create a source endpoint. To create a source endpoint, do the following: 1. Open the AWS DMS console at https://console.aws.amazon.com/dms/v2/. 1. Choose **Endpoints**. 1. Choose **Create endpoint**. 1. On the **Create endpoint** page, enter the following information. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/chap-rdssqlserver2s3datalake.steps.sourceendpoint.html) 1. Choose **Create endpoint**. **Note** To migrate a Microsoft SQL Server Always On database, you need to use different configurations. For more information, see [Migrating a SQL Server Always On Database to Amazon Web Services](chap-manageddatabases.sqlserveralwayson.md). # Step 4: Configure a Target Amazon S3 Bucket You can integrate Amazon S3 with other AWS and third-party services to take advantage of the following: + Data analysis using Amazon Athena query engine. This service helps reduce cost as you do not pay for dedicated resources and instead pay based on the amount data being scanned. + Perform extract, transform, and load (ETL) operations using distributed processing frameworks such as Spark with Amazon EMR or AWS Glue. + Implement machine learning use cases, because Amazon S3 can store granular time series data spanning years in raw form, in conjunction with Amazon SageMaker. Because in this use case we migrate the `Sales` schema to Amazon S3, we need to account for future use cases of the migrated data before we set up Amazon S3 bucket and AWS DMS endpoints. To create the Amazon S3 bucket, do the following: 1. Open the Amazon S3 console at https://s3.console.aws.amazon.com/s3/home. 1. Choose **Create bucket**. 1. For **Bucket name**, enter **adventure-works-datalake**. 1. For ** AWS Region **, choose the region that hosts your AWS DMS replication instance. 1. Leave the default values in the other fields and choose **Create bucket**. # Step 5: Configure an AWS DMS Target Endpoint To use Amazon S3 as an AWS Database Migration Service (AWS DMS) target endpoint, create an IAM role with write and delete access to the S3 bucket. Then add DMS (dms.amazonaws.com) as *trusted entity* in this IAM role. For more information, see [Prerequisites for using Amazon S3 as a target](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.S3.html#CHAP_Target.S3.Prerequisites). When using AWS DMS to migrate data to an Amazon Simple Storage Service (Amazon S3) data lake, you can change the default task behavior, such as file formats, partitioning, file sizing, and so on. This leads to minimizing post-migration processing and helps downstream applications consume data efficiently. You can customize task behavior using endpoint settings and extra connection attributes (ECA). Most of the Amazon S3 endpoint settings and ECA settings overlap, except for a few parameters. In this walkthrough, we will configure Amazon S3 endpoint settings. ## Choose File Format AWS DMS supports data replication through comma-separated values (CSV) or Apache Parquet file formats. Each file format has its own benefits. Choose the right file format depending on your consumption pattern. Apache Parquet is a columnar format, which is built to support efficient compression and encoding schemes providing storage space savings and performance benefits. With Parquet, you can specify compression schemes for each column to improve query performance when using avg(), max(), or other column level aggregation operations. That is why Parquet is popular for data lake and analytics use cases. CSV files are helpful when you plan to keep data in human readable format, share or transfer Amazon S3 files into other downstream systems for further processing. For this walkthrough, we will use the Parquet file format. Specify the following endpoint settings. ``` DataFormat=parquet ParquetVersion=PARQUET_2_0 ``` ## Determine File Size By default, during ongoing replication AWS DMS tasks writes to Amazon S3 are triggered either if the file size reaches 32 KB or if the previous file write was more than 60 seconds ago. These settings ensure that the data capture latency is less than a minute. However, this approach creates a large number of small files in target Amazon S3 bucket. Because we migrate our source `Sales` database schema for an analytics use case, some latency is acceptable. However, we need to optimize this schema for cost and performance. When you use distributed processing frameworks such as Amazon Athena, AWS Glue or Amazon EMR, it is recommended to avoid too many small files (less than 64 MB). Small files create management overhead for the driver node of the distributed processing framework. Because we plan to use Amazon Athena to query data from our Amazon S3 bucket, we need to make sure our target file size is at least 64 MB. Specify the following endpoint settings: `CdcMaxBatchInterval=3600` and `CdcMinFileSize=64000`. These settings ensure that AWS DMS writes the file until its size reaches 64 MB or if the last file write was more than an hour ago. **Note** Parquet files created by AWS DMS are usually smaller than the specified `CdcMinFileSize` setting because Parquet data compression ratio varies depending on the source data set. The size of CSV files created by AWS DMS is equal to the value specified in `CdcMinFileSize`. ## Turn on S3 Partitioning Partitioning in Amazon S3 structures your data by folders and subfolders that help efficiently query data. For example, if you receive sales record data daily from different regions and you query data for a specific region and find stats for a few months, then it is recommended to partition data by region, year, and month. In Amazon S3, the path for our use case looks as following: ``` s3://////// s3://adventure-works-datalake - s3://adventure-works-datalake/US-WEST-DATA - s3://adventure-works-datalake/US-WEST-DATA/Sales - s3://adventure-works-datalake/US-WEST-DATA/Sales/CreditCard/ - s3://adventure-works-datalake/US-WEST-DATA/Sales/CreditCard/LOAD00000001.parquet - s3://adventure-works-datalake/US-WEST-DATA/Sales/SalesPerson - s3://adventure-works-datalake/US-WEST-DATA/Sales/SalesPerson/LOAD00000001.parquet - s3://adventure-works-datalake/US-WEST-DATA/Sales/SalesPerson/2021/11/23/ - s3://adventure-works-datalake/US-WEST-DATA/Sales/SalesPerson/2021/11/23/20211123-013830913.parquet - s3://adventure-works-datalake/US-WEST-DATA/Sales/SalesPerson/2021/11/27/20211127-175902985.parquet ``` Partitioning provides performance benefits because data scanning will be limited to the amount of data in the specific partition based on the filter condition in your queries. For our sales data example, your queries might look as follows: ``` SELECT FROM WHERE = AND = ``` If you use Amazon Athena to query data, partitioning helps reduce cost as Athena pricing is based on the amount of data that you scan when running queries. To turn on partitioning for ongoing changes in the preceding format, use the following queries. ``` bucketFolder=US-WEST-DATA DatePartitionedEnabled=true DatePartitionSequence=YYYYMMDD DatePartitionDelimiter=SLASH ``` ## Other Considerations The preceding settings help optimize performance and cost. We also need to configure additional settings because: + Our use case does not have a fixed end-date. + We need to minimize issues arising from misconfigurations or retroactive changes. + We want to minimize recovery time in case of unforeseen issues. ### Serialize ongoing replication events A common challenge when using Amazon S3 as a target involves identifying the ongoing replication event sequence when multiple records are updated at the same time on the source database. AWS DMS provides two options to help serialize such events for Amazon S3. You can use the TimeStampColumnName endpoint setting or use transformation rules to include LSN column. Here, we will discuss the first option. For more information about the second option, see [Step 6: Create an AWS DMS Task](chap-rdssqlserver2s3datalake.steps.createtask.md). **Use the TimeStampColumnName endpoint setting** The `TimeStampColumnName` setting adds an additional `STRING` column to the target Parquet file created by AWS DMS. During the ongoing replication, the column value represents the commit timestamp of the event in SQL Server. For the full load phase, the columns values represent the timestamp of data transfer to Amazon S3. The default format is `yyyy-MM-dd HH:mm:ss.SSSSSS`. This format provides a microsecond precision but depends on the source database transaction log timestamp precision. The following image shows the seven microseconds difference between two operations in the `sourceRecordTime` field. ![\[Time difference between two operations in the sourceRecordTime field.\]](http://docs.aws.amazon.com/dms/latest/sbs/images/sbs-rdssqlserver2s3datalake-sourcerecordtime.png) **Note** Because `TimeStampColumnName` is an endpoint setting, all tasks that use this endpoint, will include this column for all tables. **Include full load operation field** All files created during the ongoing replication, have the first column marked with `I`, `U`, or `D`. These symbols represent the DML operation on the source and stand for **Insert**, **Update**, or **Delete** operations. For full load files, you can add this column by configuring the endpoint setting. ``` includeOpForFullLoad=true ``` This ensures that all full load files are marked with an `I` operation. When you use this approach, new subscribers can consume the entire data set or prepare a fresh copy in case of any downstream processing issues. ## Create a Target Endpoint After you completed all settings configurations, you can create a target endpoint. To create a target endpoint, do the following: 1. Open the AWS DMS console at https://console.aws.amazon.com/dms/v2/. 1. Choose **Endpoints**, and then choose **Create endpoint**. 1. On the **Create endpoint** page, enter the following information. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/chap-rdssqlserver2s3datalake.steps.targetendpoint.html) 1. Expand the **Endpoint settings** section, choose **Wizard**, and then choose **Add new setting** to add the settings as shown on the following image. ![\[Target endpoint settings.\]](http://docs.aws.amazon.com/dms/latest/sbs/images/sbs-rdssqlserver2s3datalake-target-endpoint-settings.png) 1. Choose **Create endpoint**. # Step 6: Create an AWS DMS Task After you configured the replication instance and endpoints, you need to analyze your source database. A good understanding of the workload helps plan an effective migration approach and minimize configuration issues. Find some of the important considerations following and learn how they apply to our walkthrough. **Size and number of records** The volume of migrated records affects the full load completion time. It is difficult to predict the full load time upfront, but testing with a replica of a production instance should provide a baseline. Use this estimate to decide whether you should parallelize full load by using multiple tasks or by using the parallel load option. The `Sales` schema includes 19 tables. The `CreditCard` table is the largest table containing 100,000 records. We can increase the number of tables loaded in parallel to 19 if the full load is slow. The default value for the number of tables loaded in parallel is eight. **Transactions per second** While full load is affected by the number of records, the ongoing replication performance relies on the number of transactions on the source Amazon RDS for SQL Server database. Performance issues during change data capture (CDC) generally stem from resource constraints on the source database, replication instance, target database, and network bandwidth or throughput. Knowing average and peak TPS on the source and recording CDC throughput and latency metrics help baseline (AWS DMS) performance and identify an optimal task configuration. For more information, see [Replication task metrics](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Monitoring.html#CHAP_Monitoring.Metrics.Task). In this walkthrough, we will track the CDC latency and throughput values after the task moves into the ongoing replication phase to baseline AWS DMS performance. **LOB columns** AWS DMS handles large binary objects (LOBs) columns differently compared to other data types. For more information, see [Migrating large binary objects (LOBs)](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html#CHAP_BestPractices.LOBS). Because AWS DMS does not support **Full LOB mode** for Amazon S3 endpoints, we need to identify a suitable **LOB Max Size**. A detailed explanation of LOB handling by AWS DMS is out of scope for this walkthrough. However, remember that increasing the **LOB Max Size** increases the tasks memory utilization. Because of that, it is recommended not to set **LOB Max Size** to a large value. For more information about LOB settings, see [Task Configuration](#chap-rdssqlserver2s3datalake.steps.createtask.configuration). **Unsupported data types** Identify data types used in tables and check that AWS DMS supports these data types. For more information, see [Source data types for SQL Server](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html#CHAP_Source.SQLServer.DataTypes). Validate that the target Amazon S3 has the corresponding data types. For more information, see [Target data types for S3 Parquet](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.S3.html#CHAP_Target.S3.DataTypes). After running the initial load test, validate that AWS DMS converted data as you expected. You can also initiate a pre-migration assessment to identify any unsupported data types in the migration scope. For more information, see [Specifying individual assessments](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.AssessmentReport1.html#CHAP_Tasks.AssessmentReport1.Individual). **Note** The preceding list is not complete. For more information, see [Best practices](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html). Combining the considerations from the preceding list, we start with a single task that migrates all 19 tables. Based on the full load run time and resource utilization metrics on the source SQL Server database instance and replication instance, we can evaluate if we should parallelize the load further to improve performance. ## Task Configuration In an AWS DMS task, you can specify the schema or table to migrate, the type of migration, and the configurations for the migration. You can choose one of the following options for your task. + **Full Load only** — migrate existing data. + **Full Load \$1 CDC** — migrate existing data and replicate ongoing changes. + **CDC only** — replicate ongoing changes. For more information about the task creation steps and available configuration options, see [Creating a task](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.Creating.html). In this walkthrough, we will focus on the following settings. **Table mappings** Use selection rules to define the schemas and tables that the AWS DMS task will migrate. For more information, see [Selection rules and actions](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Selections.html). Because we need to identify monthly sales for a specific year, one possible approach can restrict the migration to `SalesOrder%` tables in the `Sales` schema and keep adding new tables to the task when additional reporting is required. This approach saves cost and minimizes the load, but increases operational overhead by requiring repeated configurations, performance baselining, and so on. For the walkthrough, we will migrate all tables (`%`) from the `Sales` schema. **Using transformation rules to include LSN column** In the previous section we discussed using the `TimestampColumnName` endpoint setting to serialize ongoing replication events. For more information about using the `TimestampColumnName` endpoint setting, see [Serialize ongoing replication events](chap-rdssqlserver2s3datalake.steps.targetendpoint.md#chap-rdssqlserver2s3datalake.steps.targetendpoint.considerations.serialize). Because the source database transaction log precision is limited to milliseconds, multiple events can have the same timestamp. To address this issue, you can use task level transformation rules to include source table headers to the Amazon S3 target files as described in the task creation section. Source table headers add an additional column that contains the log sequence number (LSN) value of the operation from the source SQL Server database instance. You can use this information in our Amazon S3 data lake scenario for downstream serialization. For more information about source table headers, see [Replicating source table headers using expressions](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions.html#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions-Headers). To include headers, add the following transformation rule in the JSON editor in table mapping. This rule adds a new `transact-id` column with the LSN to all tables that the task migrates. For more information, see [Specifying table selection and transformations rules using JSON](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.html). ``` { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-target": "column", "object-locator": { "schema-name": "%", "table-name": "%" }, "rule-action": "add-column", "value": "transact_id", "expression": "$AR_H_STREAM_POSITION", "data-type": { "type": "string", "length": 50 } } ``` **Note** Mapping rules are applied at the task level. You need to add a mapping rule to each task that replicates data to your data lake. **LOB settings** Use the **sys** schema to identify the LOB columns in the tables of the `Sales` schema. ``` SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName, y.name AS DataType FROM sys.tables AS t INNER JOIN sys.schemas AS s ON s.schema_id = t.schema_id INNER JOIN sys.columns AS c ON t.object_id = c.object_id INNER JOIN sys.types AS y ON y.user_type_id = c.user_type_id WHERE (c.user_type_id in (34,35,99,129,130,241,256) OR (c.user_type_id in (165,167,231) AND c.max_length = -1)) AND s.name = 'Sales' ORDER BY t.name; ``` The Sales.Store table includes one LOB column. Use the following query to identify the size of the largest LOB in the migrated tables. ``` select max(datalength(Demographics)) as "Size in Bytes" from Sales.Store ``` The size of the largest LOB is 1,000 bytes. Because of that, we will leave the default value for `LOB Max Size`, which is 32 KB. If the size of the largest LOB is more than 32 KB, it is recommended to factor in LOB growth over time, include some buffer, and set that as the `LOB Max Size` value. **Other task settings** Choose **Enable CloudWatch Logs** to upload the AWS DMS task execution log to Amazon CloudWatch. You can use these logs to troubleshoot issues because they include error and warning messages, start and end times of the run, configuration issues, and so on. Changes to the task logging setting, such as enabling debug or trace can also be helpful to diagnose performance issues. **Note** CloudWatch log usage is charged at standard rates. For more information, see [Amazon CloudWatch pricing](https://aws.amazon.com/cloudwatch/pricing/). For **Target table preparation mode**, choose one of the following options: `Do nothing`, `truncate`, and `Drop`. Use `Truncate` in data pipelines where the downstream systems rely on a fresh dump of clean data and do not rely on historical data. In this walkthrough, we choose **Do nothing** because we want to control the retention of files from previous runs. For **Maximum number of tables to load in parallel**, enter the number of parallel threads that AWS DMS initiates during full load. You can increase this value to improve the full load performance and minimize the load time when you have numerous tables. **Note** Increasing this parameter induces additional load on the source database, replication instance, and target database. ## Create an AWS DMS Task After you completed all settings configurations, you can create an AWS DMS database migration task. To create a database migration task, do the following: 1. Open the AWS DMS console at https://console.aws.amazon.com/dms/v2/. 1. Choose **Database migration tasks**, and then choose **Create task**. 1. On the **Create database migration task** page, enter the following information. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/chap-rdssqlserver2s3datalake.steps.createtask.html) 1. Leave the default values in the other fields and choose **Create task**. 1. The task begins immediately. The **Database migration tasks** section shows you the status of the migration task. # Step 7: Run the AWS DMS Task After you created your AWS Database Migration Service (AWS DMS) task, run the task a few times to identify the full load run time and ongoing replication performance. You can validate that initial configurations work as expected. You can do this by monitoring and documenting resource utilization on the source database, replication instance, and target database. These details make up the initial baseline and help determine if you need further optimizations. After you started the task, the full load operation starts loading tables. You can see the table load completion status in the **Table Statistics** section and the corresponding target files in the Amazon S3 bucket. Because in our case the overall number of records is less than 200,000, the full load operation finishes in less than a minute. We can increase the value of **Maximum number of tables to load in parallel**, but it will not provide any meaningful gain in this scenario. After the AWS DMS task completes full load, the status changes to the **Load complete, replication ongoing** phase. The following image shows the updated status of the task. ![\[Load complete\]](http://docs.aws.amazon.com/dms/latest/sbs/images/sbs-rdssqlserver2s3datalake-load-complete-replication-ongoing.png) During this phase, AWS DMS partitions data by the year, month, and day of generation. The following image shows the structure of folders. ![\[the structure of folders after partitioning.\]](http://docs.aws.amazon.com/dms/latest/sbs/images/sbs-rdssqlserver2s3datalake-partitioning.png) Following, find some of the common errors and unexpected results you might see while following this walkthrough. **Files are not written to the Amazon S3 target even though changes are visible in the table statistics section of the c console** + This happens due to the target endpoint configuration. After you set `CdcMaxBatchInterval=3600` and `CdcMinFileSize=64000`, AWS DMS waits for an hour or for the file size to reach 64 MB before writing data to Amazon S3. + To write the output to Amazon S3 sooner, reduce `CdcMaxBatchInterval` to a smaller value. Alternatively, you can stop and resume the task. This will force Amazon S3 to flush events to Amazon S3 disregarding the extra connection attributes settings. Using these options means that the size of CDC files will be much smaller than the expected 64 MB. **Parquet file sizes are less than 64 MB despite setting `CdcMinFileSize=64000` ** AWS DMS creates 64 MB files in memory. When this data is encoded as Parquet the resulting file size is smaller. The file sizes vary based on the level of compression possible. ** AWS DMS captures only inserts and deletes and does not migrate update records to the target** You can see the following warning in the task logs: ``` 00008570: 2021-12-07T19:52:52 [SOURCE_CAPTURE ]W: MS-REPLICATION is not enabled for table '[Sales].[SalesPerson]'. Therefore, UPDATE changes to it will not be captured. If you want UPDATE changes to be captured, either define a Primary Key for the table (if missing) or enable Microsoft CDC instead. (sqlserver_log_utils.c:1292) ``` This log message indicates MS-Replication. However, for Amazon RDS for SQL Server you can use MS-CDC. This error occurs when you have not turned on MS-CDC for the table. For more information, see [Step 2: Configure a Source Amazon RDS for SQL Server Database](chap-rdssqlserver2s3datalake.steps.configuresource.md). In this walkthrough, we covered most prerequisites that help avoid configuration related errors. If you observe issues when running the task, see [Troubleshooting migration tasks](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Troubleshooting.html), [Best practices](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html), or reach out to AWS Support for further assistance. After you completed the migration, validate that your data migrated successfully and delete the cloud resources that you created.