# Migration tools and services overview
This topic provides conceptual content about migrating from Microsoft SQL Server 2019 to Amazon Aurora PostgreSQL. It introduces you to the AWS Schema Conversion Tool (AWS SCT) and AWS Database Migration Service (AWS DMS), explaining how these tools can streamline your database migration process. The content covers various aspects of the migration, including schema conversion, data transfer, and handling different database features. It also touches on related services like Amazon RDS on Outposts, Amazon RDS Proxy, and Amazon Aurora Serverless v1, providing a comprehensive overview of the AWS database ecosystem. By understanding these concepts, you can better plan and execute your database migration strategy, anticipate potential challenges, and leverage AWS tools and services to ensure a smooth transition to Aurora PostgreSQL.
**Topics**
+ [
# AWS Schema Conversion Tool overview
](chap-sql-server-aurora-pg.tools.awssct.md)
+ [
# AWS SCT Action Code Index overview
](chap-sql-server-aurora-pg.tools.actioncode.md)
+ [
# AWS Database Migration Service overview
](chap-sql-server-aurora-pg.tools.awsdms.md)
+ [
# Amazon RDS on Outposts overview
](chap-sql-server-aurora-pg.tools.rdsoutposts.md)
+ [
# Amazon RDS Proxy overview
](chap-sql-server-aurora-pg.tools.rdsproxy.md)
+ [
# Amazon Aurora Serverless v1 overview
](chap-sql-server-aurora-pg.tools.auroraserverless.md)
# AWS Schema Conversion Tool overview
You can use the AWS Schema Conversion Tool (AWS SCT) to streamline the migration of your Microsoft SQL Server 2019 database to Amazon Aurora PostgreSQL. This powerful Java utility automates the conversion of most database objects, significantly reducing manual effort in the migration process. By following the step-by-step instructions provided, you can connect to your source and target databases, analyze the schema objects, and generate a comprehensive migration assessment report. The tool offers valuable insights into potential conversion challenges and provides detailed recommendations for addressing them.
The AWS Schema Conversion Tool (AWS SCT) is a Java utility that connects to source and target databases, scans the source database schema objects (tables, views, indexes, procedures, and so on), and converts them to target database objects.
This section provides a step-by-step process for using AWS SCT to migrate an SQL Server database to an Aurora PostgreSQL database cluster. Since AWS SCT can automatically migrate most of the database objects, it greatly reduces manual effort.
We recommend to start every migration with the process outlined in this section and then use the rest of the Playbook to further explore manual solutions for objects that couldn’t be migrated automatically. For more information, see [Schema Conversion Tool user guide](http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/Welcome.html).
**Note**
This walkthrough uses the AWS DMS Sample Database. You can download it from [GitHub](https://github.com/aws-samples/aws-database-migration-samples).
## Download the Software and Drivers
Download and install AWS SCT from the [Schema Conversion Tool user guide](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Installing.html).
Download the [Microsoft SQL Server](https://docs.microsoft.com/en-us/sql/connect/jdbc/release-notes-for-the-jdbc-driver?view=sql-server-ver15#72) and [PostgreSQL](https://jdbc.postgresql.org/download) drivers.
Find other supported drivers in the [Schema Conversion Tool user guide](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Installing.html#CHAP_Installing.JDBCDrivers).
## Configure AWS SCT
1. Start AWS Schema Conversion Tool (AWS SCT).
1. Choose **Settings** and then choose **Global settings**.
1. On the left navigation bar, choose **Drivers**.
1. Enter the paths for the SQL Server and PostgreSQL drivers downloaded in the first step.
![\[Enter the paths for the Microsoft and PostgreSQL drivers\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-sql-server-aurora-pg-configure-aws-sct.png)
1. Choose **Apply** and then **OK**.
## Create a New Migration Project
1. Choose **File**, and then choose **New project wizard**. Alternatively, use the keyboard shortcut **Ctrl\$1W**.
1. Enter a project name and select a location for the project files. For **Source engine**, choose **Microsoft SQL Server**, and then choose **Next**.
1. Enter connection details for the source SQL Server database and choose **Test connection** to verify. Choose **Next**.
1. Select the schema or database to migrate and choose **Next**.
The progress bar displays the objects that AWS SCT analyzes. When AWS SCT completes the analysis, the application displays the database migration assessment report. Read the Executive summary and other sections. Note that the information on the screen is only partial. To read the full report, including details of the individual issues, choose **Save to PDF** at the top right and open the PDF document.
![\[Assessment report\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-sql-server-aurora-pg-aws-sct-assessment-report.png)
Scroll down to the **Database objects with conversion actions for Amazon Aurora (PostgreSQL compatible)** section.
![\[Assessment report conversion statistics\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-sql-server-aurora-pg-aws-sct-assessment-report-conversion-statistics.png)
Scroll further down to the **Detailed recommendations for Amazon Aurora (PostgreSQL compatible) migrations** section.
![\[Assessment report detailed recommendations\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-sql-server-aurora-pg-aws-sct-assessment-report-detailed-recommendations.png)
Return to AWS SCT and choose **Next**. Enter the connection details for the target Aurora PostgreSQL database and choose **Finish**.
When the connection is complete, AWS SCT displays the main window. In this interface, you can explore the individual issues and recommendations discovered by AWS SCT.
Choose the schema, open the context (right-click) menu, and then choose **Create report** to create a report tailored for the target database type. You can view this report in AWS SCT.
The progress bar updates while the report is generated.
AWS SCT displays the executive summary page of the database migration assessment report.
Choose **Action items**. In this window, you can investigate each issue in detail and view the suggested course of action. For each issue, drill down to view all instances of that issue.
Choose the database name, open the context (right-click) menu, and choose **Convert schema**. Make sure that you uncheck the `sys` and `information_schema` system schemas. Aurora PostgreSQL already has an `information_schema` schema.
This step doesn’t make any changes to the target database.
On the right pane, AWS SCT displays the new virtual schema as if it exists in the target database. Drilling down into individual objects displays the actual syntax generated by AWS SCT to migrate the objects.
Choose the database on the right pane, open the context (right-click) menu, and choose either **Apply to database** to automatically run the conversion script against the target database, or choose **Save as SQL** to save to an SQL file.
![\[Apply to database\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-sql-server-aurora-pg-aws-sct-apply-to-database.png)
We recommend saving to an SQL file because you can verify and QA the converted code. Also, you can make the adjustments needed for objects that couldn’t be automatically converted.
For more information, see the [Schema Conversion Tool user guide](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html).
# AWS SCT Action Code Index overview
This topic provides reference information for the automation levels and action codes used by AWS Schema Conversion Tool (AWS SCT) when migrating from Microsoft SQL Server 2019 to Amazon Aurora PostgreSQL. You can use this information to understand the degree of automation available for various database objects and features during the migration process.
The following table shows the icons we use to describe the automation levels of AWS Schema Conversion Tool (AWS SCT) and AWS Database Migration Service (AWS DMS).
| Automation level icon | Description |
| --- | --- |
| ![\[Five star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-5.png) | **Full automation**. AWS SCT performs fully automatic conversion, no manual conversion needed. |
| ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png) | **High automation**. Minor, simple manual conversions may be needed. |
| ![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png) | **Medium automation**. Low-medium complexity manual conversions may be needed. |
| ![\[Two star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-2.png) | **Low automation**. Medium-high complexity manual conversions may be needed. |
| ![\[One star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-1.png) | **Very low automation**. High risk or complex manual conversions may be needed. |
| ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png) | **No automation**. Not currently supported by AWS SCT, manual conversion is required for this feature. |
The following sections list the AWS Schema Conversion Tool Action codes for topics that are covered in this playbook.
**Note**
The links in the table point to the Microsoft SQL Server topic pages, which are immediately followed by the PostgreSQL pages for the same topics.
## Creating Tables
![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)
AWS SCT automatically converts the most commonly used constructs of the `CREATE TABLE` statement as both SQL Server and Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) support the entry level American National Standards Institute (ANSI) compliance. These items include table names, containing security schema or database, column names, basic column data types, column and table constraints, column default values, primary, `UNIQUE`, and foreign keys. Some changes may be required for computed columns and global temporary tables.
For more information, see [Creating Tables](chap-sql-server-aurora-pg.sql.tables.md).
| Action code | Action message |
| --- | --- |
| 7659 | If you use recursion, make sure that table variables in your source database and temporary tables in your target database have the same scope. |
| 7665 | PostgreSQL doesn’t support `FILESTREAM` clauses. AWS SCT skips `FILESTREAM` clauses in the converted code. |
| 7678 | AWS SCT replaced computed columns with regular columns in the converted code. |
| 7679 | AWS SCT replaced computed columns with triggers in the converted code. |
| 7680 | PostgreSQL doesn’t support global temporary tables. |
| 7812 | Make sure that you remove the temporary table before the end of the function. |
| 7835 | PostgreSQL doesn’t support `CREATE TABLE` statements with the `AS FileTable` option. |
## Data Types
![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)
Data type syntax and rules are very similar between SQL Server and Aurora PostgreSQL and most are converted automatically by AWS SCT. Note that date and time handling paradigms are different for SQL Server and Aurora PostgreSQL and require manual verification or conversion. Also note that due to differences in data type behavior between SQL Server and Aurora PostgreSQL, manual verification and strict testing are highly recommended.
For more information, see [Data Types](chap-sql-server-aurora-pg.sql.datatypes.md).
| Action code | Action message |
| --- | --- |
| 7657 | PostgreSQL doesn’t support the `hierarchyid` data type. |
| 7658 | PostgreSQL doesn’t support the `sql_variant` data type. |
| 7662 | PostgreSQL doesn’t support the `geography` data type. |
| 7664 | PostgreSQL doesn’t support the `geometry` data type. |
| 7690 | PostgreSQL doesn’t support table types. |
| 7706 | AWS SCT can’t convert the declaration of a variable of the unsupported %s data type. |
| 7707 | AWS SCT can’t convert the usage of a variable of the unsupported %s data type. |
| 7708 | AWS SCT can’t convert the usage of the unsupported %s data type. |
| 7773 | AWS SCT can’t convert arithmetic operations with dates. |
| 7775 | Converted code might lose accuracy compared to the source code. |
## Collations
![\[No automation\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png)
The collation paradigms of SQL Server and Aurora PostgreSQL are significantly different. AWS SCT can’t migrate collations automatically to PostgreSQL.
For more information, see [SQL Server Collations and PostgreSQL Encoding](chap-sql-server-aurora-pg.tsql.collations.md).
| Action code | Action message |
| --- | --- |
| 7646 | AWS SCT can’t convert collations. |
## PIVOT and UNPIVOT
![\[No automation\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png)
Aurora PostgreSQL version 10 doesn’t support `PIVOT` and `UNPIVOT` clauses. AWS SCT can’t automatically convert `PIVOT` and `UNPIVOT` clauses.
For more information, see [PIVOT and UNPIVOT](chap-sql-server-aurora-pg.tsql.pivot.md).
| Action code | Action message |
| --- | --- |
| 7905 | PostgreSQL doesn’t support `PIVOT` clauses for `SELECT` statements. |
| 7906 | PostgreSQL doesn’t support `UNPIVOT` clauses for `SELECT` statements. |
## TOP and FETCH
![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)
Aurora PostgreSQL supports the non-ANSI compliant but popular with other engines `LIMIT… OFFSET` operator for paging results sets. AWS SCT can’t automatically convert some options such as `WITH TIES`. These options require manual conversion.
For more information, see [SQL Server TOP and FETCH and PostgreSQL LIMIT and OFFSET](chap-sql-server-aurora-pg.tsql.topfetch.md).
| Action code | Action message |
| --- | --- |
| 7605 | PostgreSQL doesn’t support the `WITH TIES` argument in `TOP` clauses. |
| 7796 | PostgreSQL doesn’t support `TOP` clauses in `UPDATE` statements. |
| 7798 | PostgreSQL doesn’t support `TOP` clauses in `DELETE` statements. |
| 7799 | PostgreSQL doesn’t support `TOP` clauses in `INSERT` operators. |
## Cursors
![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)
PostgreSQL has PL/pgSQL cursors that enable you to iterate business logic on rows read from the database. They can encapsulate the query and read the query results a few rows at a time. All access to cursors in PL/pgSQL is performed through cursor variables, which are always of the `refcursor` data type. There are specific options which aren’t supported for automatic conversion by AWS SCT.
For more information, see [Cursors](chap-sql-server-aurora-pg.tsql.cursors.md).
| Action code | Action message |
| --- | --- |
| 7637 | PostgreSQL doesn’t support global cursors. |
| 7639 | PostgreSQL doesn’t support dynamic cursors. |
| 7700 | AWS SCT can’t convert the `KEYSET` option because PostgreSQL doesn’t support changing the membership and order of rows for cursors. |
| 7701 | AWS SCT doesn’t convert the `FAST_FORWARD` option because this is a default option for cursors in PostgreSQL. |
| 7702 | AWS SCT doesn’t convert the `READ_ONLY` option because this is a default option for cursors in PostgreSQL. |
| 7704 | PostgreSQL doesn’t support the `OPTIMISTIC` option for cursors. |
| 7705 | PostgreSQL doesn’t support the `TYPE_WARNING` option for cursors. |
| 7803 | PostgreSQL doesn’t support the `FOR UPDATE` option. |
## Flow Control
![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)
Although the flow control syntax of SQL Server differs from Aurora PostgreSQL, AWS SCT can convert most constructs automatically including loops, command blocks, and delays. Aurora PostgreSQL doesn’t support the `GOTO` and `WAITFOR TIME` commands, which require manual conversion.
For more information, see [SQL Server Flow Control and PostgreSQL Control Structures](chap-sql-server-aurora-pg.tsql.flowcontrol.md).
| Action code | Action message |
| --- | --- |
| 7628 | PostgreSQL doesn’t support `GOTO` statements. |
| 7691 | PostgreSQL doesn’t support the `WAITFOR TIME` feature. |
| 7801 | Make sure that your table isn’t locked by an open cursor. |
| 7802 | Make sure that you delete the table that you created within the procedure before the end of the procedure. |
| 7810 | PostgreSQL doesn’t support `SET NOCOUNT OFF` statements. |
| 7821 | AWS SCT can’t convert the `WAITFOR` operator with a variable. |
| 7826 | AWS SCT can’t convert the default value of the `DateTime` variable. |
| 7827 | AWS SCT can’t convert default values. |
## Transaction Isolation
![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)
Aurora PostgreSQL supports the four transaction isolation levels specified in the SQL:92 standard: `READ UNCOMMITTED`, `READ COMMITTED`, `REPEATABLE READ`, and `SERIALIZABLE`, all of which are automatically converted by AWS SCT. Also, AWS SCT converts `BEGIN / COMMIT` and `ROLLBACK` commands that use slightly different syntax. Manual conversion is required for named, marked, and delayed durability transactions that aren’t supported by Aurora PostgreSQL.
For more information, see [Transactions](chap-sql-server-aurora-pg.tsql.transactions.md).
| Action code | Action message |
| --- | --- |
| 7807 | AWS SCT can’t convert the transaction management command. PostgreSQL doesn’t support explicit transaction management commands such as `BEGIN TRAN`, `SAVE TRAN` in functions. |
## Stored Procedures
![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)
Aurora PostgreSQL stored procedures provide very similar functionality to SQL Server stored procedures. You can automatically convert them with AWS SCT. Manual conversion is required for procedures that use `RETURN` values and some less common `EXECUTE` options such as the `RECOMPILE` and `RESULTS SETS`.
For more information, see [Stored Procedures](chap-sql-server-aurora-pg.tsql.storedprocedures.md).
| Action code | Action message |
| --- | --- |
| 7640 | PostgreSQL doesn’t support `EXECUTE` statements with the `WITH RECOMPILE` option. |
| 7641 | PostgreSQL doesn’t support `EXECUTE` statements with the `RESULT SETS UNDEFINED` option. |
| 7642 | PostgreSQL doesn’t support `EXECUTE` statements with the `RESULT SETS NONE` option. |
| 7643 | PostgreSQL doesn’t support `EXECUTE` statements with the `RESULT SETS DEFINITION` option. |
| 7672 | PostgreSQL doesn’t support `EXECUTE` statements that run a character string. |
| 7695 | PostgreSQL doesn’t support support the call of a procedure as a variable. |
| 7800 | PostgreSQL doesn’t support result sets in the SQL Server style. |
| 7830 | AWS SCT can’t convert arithmetic operations with the `CASE` operand. |
| 7838 | AWS SCT can’t convert `EXECUTE` statements with `LOGIN` or `USER` options. |
| 7839 | Converted code might not work correctly because of parameter names. |
## Triggers
![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)
Aurora PostgreSQL supports `BEFORE` and `AFTER` triggers for `INSERT`, `UPDATE`, and `DELETE`. However, Aurora PostgreSQL triggers differ substantially from SQL Server’s triggers. You can migrate the most common use cases with minimal code changes.
For more information, see [Triggers](chap-sql-server-aurora-pg.tsql.triggers.md).
| Action code | Action message |
| --- | --- |
| 7809 | PostgreSQL doesn’t support `INSTEAD OF` triggers on tables. |
| 7832 | AWS SCT can’t convert `INSTEAD OF` triggers on views. |
| 7909 | AWS SCT can’t convert `UPDATE(column)` or `COLUMNS_UPDATED` statements. |
## MERGE
![\[No automation\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png)
Aurora PostgreSQL version 10 doesn’t support `MERGE` statements. AWS SCT can’t automatically convert these statements. Manual conversion is straightforward in most cases.
For more information, see [MERGE](chap-sql-server-aurora-pg.tsql.merge.md).
| Action code | Action message |
| --- | --- |
| 7915 | Converted code might produce different results compared to the source code. Make sure that the constraint includes the %s column. |
| 7916 | AWS SCT can’t emulate the `MERGE` statement using the `INSERT ON CONFLICT` statement. |
## Query Hints
![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)
You can use AWS SCT to convert basic query hints such as index hints, except for data manipulation language (DML) statements. Note that specific optimizations used for SQL Server may be completely inapplicable to a new query optimizer. AWS recommends to start migration testing with all hints removed. Then, selectively apply hints as a last resort if other means such as schema, index, and query optimizations have failed. Plan guides aren’t supported by Aurora PostgreSQL.
For more information, see [SQL Server Query Hints and Plan Guides and PostgreSQL DB Query Planning](chap-sql-server-aurora-pg.tuning.queryplanning.md).
| Action code | Action message |
| --- | --- |
| 7823 | PostgreSQL doesn’t support table hints in DML statements. |
## Full-Text Search
![\[No automation\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png)
Migrating full-text indexes from SQL Server to Aurora PostgreSQL requires a full rewrite of the code that deals with both creating, managing, and querying full-text indexes. AWS SCT can’t automatically convert these statements.
For more information, see [Full-Text Search](chap-sql-server-aurora-pg.tsql.fulltextsearch.md).
| Action code | Action message |
| --- | --- |
| 7688 | PostgreSQL doesn’t support `FREETEXT` predicates. |
## Indexes
![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)
Basic non-clustered indexes, which are the most commonly used type of indexes are automatically migrated by AWS SCT. In addition, filtered indexes, indexes with included columns, and some SQL Server specific index options can’t be migrated automatically and require manual conversion.
For more information, see [Indexes](chap-sql-server-aurora-pg.indexes.md).
| Action code | Action message |
| --- | --- |
| 7675 | PostgreSQL doesn’t support `ASC` and `DESC` sorting options for constraints. |
| 7681 | PostgreSQL doesn’t support clustered indexes. |
| 7682 | PostgreSQL doesn’t support the `INCLUDE` option in indexes. |
| 7781 | PostgreSQL doesn’t support the `PAD_INDEX` option in indexes. |
| 7782 | PostgreSQL doesn’t support the `SORT_IN_TEMPDB` option in indexes. |
| 7783 | PostgreSQL doesn’t support the `IGNORE_DUP_KEY` option in indexes. |
| 7784 | PostgreSQL doesn’t support the `STATISTICS_NORECOMPUTE` option in indexes. |
| 7785 | PostgreSQL doesn’t support the `STATISTICS_INCREMENTAL` option in indexes. |
| 7786 | PostgreSQL doesn’t support the `DROP_EXISTING` option in indexes. |
| 7787 | PostgreSQL doesn’t support the `ONLINE` option in indexes. |
| 7788 | PostgreSQL doesn’t support the `ALLOW_ROW_LOCKS` option in indexes. |
| 7789 | PostgreSQL doesn’t support the `ALLOW_PAGE_LOCKS` option in indexes. |
| 7790 | PostgreSQL doesn’t support the `MAXDOP` option in indexes. |
| 7791 | PostgreSQL doesn’t support the `DATA_COMPRESSION` option in indexes. |
## Partitioning
![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)
Aurora PostgreSQL uses table inheritance, some of the physical aspects of partitioning in SQL Server don’t apply to Aurora PostgreSQL. For example, the concept of file groups and assigning partitions to file groups. Aurora PostgreSQL supports a much richer framework for table partitioning than SQL Server, with many additional options such as hash partitioning, and sub partitioning.
For more information, see [SQL Server Partitioning and PostgreSQL Partitions or Table Inheritance](chap-sql-server-aurora-pg.storage.partitioning.md).
| Action code | Action message |
| --- | --- |
| 7910 | PostgreSQL doesn’t support `NULL` columns for partitioning. |
| 7911 | PostgreSQL doesn’t support foreign keys referencing partitioned tables. |
| 7912 | PostgreSQL doesn’t support foreign key references from partitioned tables to other tables. |
| 7913 | PostgreSQL doesn’t support `LEFT` partitioning. |
| 7914 | Converted code might produce different results compared to the source code. |
Starting from version 11, PostgreSQL supports `NULL` columns for partitioning. In this case, you can ignore the action item with the 7910 code and use `NULL` columns for partitioning in your target tables.
## Backup
![\[No automation\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png)
Migrating from a self-managed backup policy to a Platform as a Service (PaaS) environment such as Aurora PostgreSQL is a complete paradigm shift. You don’t need to worry about transaction logs, file groups, disks running out of space, and purging old backups. Amazon Relational Database Service (Amazon RDS) provides guaranteed continuous backup with point in time restore up to 35 days. Therefore, AWS SCT doesn’t automatically convert backups.
For more information, see [Backup and Restore](chap-sql-server-aurora-pg.hadr.backup.md).
| Action code | Action message |
| --- | --- |
| 7903 | PostgreSQL doesn’t support functionality similar to SQL Server Backup. |
## SQL Server Mail
![\[No automation\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png)
Aurora PostgreSQL doesn’t provide native support for sending emails from the database.
For more information, see [Database Mail](chap-sql-server-aurora-pg.management.databasemail.md).
| Action code | Action message |
| --- | --- |
| 7900 | PostgreSQL doesn’t support functionality similar to SQL Server Database Mail. |
## Graph
![\[No automation\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png)
AWS SCT doesn’t convert graph database capabilities.
For more information and potential workarounds, see [SQL Server Graph and PostgreSQL Apache AGE Extension](chap-sql-server-aurora-pg.tsql.graph.md).
| Action code | Action message |
| --- | --- |
| 7931 | AWS SCT can’t convert SQL Graph tables. |
| 7932 | AWS SCT can’t convert DML constructs of SQL Graph databases. |
## SQL Server Agent
![\[No automation\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png)
Aurora PostgreSQL doesn’t provide functionality similar to SQL Server Agent as an external, cross-instance scheduler. However, Aurora PostgreSQL provides a native, in-database scheduler. It is limited to the cluster scope and can’t be used to manage multiple clusters. Therefore, AWS SCT can’t automatically convert Agent jobs and alerts.
For more information, see [SQL Server Agent and PostgreSQL Scheduled Lambda](chap-sql-server-aurora-pg.management.scheduledlambda.md).
| Action code | Action message |
| --- | --- |
| 7902 | PostgreSQL doesn’t support functionality similar to SQL Server Agent. |
## Service Broker
![\[No automation\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png)
Aurora PostgreSQL doesn’t provide a compatible solution to the SQL Server Service Broker. However, you can use DB Links and AWS Lambda to achieve similar functionality.
For more information, see [SQL Server Service Broker Essentials](chap-sql-server-aurora-pg.tsql.servicebroker.md).
| Action code | Action message |
| --- | --- |
| 7901 | PostgreSQL doesn’t support functionality similar to SQL Server Service Broker. |
## XML
![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)
The XML options and features in Aurora PostgreSQL are similar or almost identical to SQL Server `XPATH` and `XQUERY` functions. PostgreSQL doesn’t support `FOR` XML clause, the walkaround for that is using `string_agg` instead. In some cases, it might be more efficient to use JSON instead of XML.
For more information, see [JSON and XML](chap-sql-server-aurora-pg.tsql.json.md).
| Action code | Action message |
| --- | --- |
| 7816 | PostgreSQL doesn’t support methods for the XML data type. |
| 7817 | PostgreSQL doesn’t support the `FOR XML PATH` option in SQL queries. |
| 7920 | PostgreSQL doesn’t support `EXPLICIT` mode with `FOR XML`. |
| 7924 | PostgreSQL doesn’t support XPath queries that return multiple elements. |
## Constraints
![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)
Constraints feature is almost fully automated and compatible between SQL Server and Aurora PostgreSQL. The differences are: missing `SET DEFAULT` and check constraint with sub-query.
For more information, see [SQL Server Constraints and PostgreSQL Table Constraints](chap-sql-server-aurora-pg.sql.constraints.md).
| Action code | Action message |
| --- | --- |
| 7606 | PostgreSQL doesn’t support foreign keys that reference partitioned tables. |
| 7675 | PostgreSQL doesn’t support `ASC` and `DESC` sorting options for constraints. |
| 7825 | AWS SCT removed the default value of the `DateTime` column. |
| 7915 | Converted code might produce different results compared to the source code. Make sure that the constraint includes the %s column. |
## Linked Servers
![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)
Aurora PostgreSQL supports remote data access from the database. Connectivity between schemas is trivial, but connectivity to other instances require an extension installation.
For more information, see [SQL Server Linked Servers and PostgreSQL DBLink and FDWrapper](chap-sql-server-aurora-pg.management.linkedservers.md).
| Action code | Action message |
| --- | --- |
| 7645 | PostgreSQL doesn’t support running pass-through commands on linked servers. |
## Synonyms
![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)
Aurora PostgreSQL supports synonyms. If synonyms refer to tables, views, or functions, you can replace them with views or functions to wrap those. It becomes more challenging when synonyms refer to other objects.
For more information, see [SQL Server Synonyms and PostgreSQL Views, Types, and Functions](chap-sql-server-aurora-pg.tsql.synonyms.md).
| Action code | Action message |
| --- | --- |
| 7792 | PostgreSQL doesn’t support synonyms. |
# AWS Database Migration Service overview
This topic provides conceptual information about AWS Database Migration Service (AWS DMS). It introduces you to the capabilities and benefits of AWS DMS for migrating databases to AWS quickly and securely
The AWS Database Migration Service (AWS DMS) helps you migrate databases to AWS quickly and securely. The source database remains fully operational during the migration, minimizing downtime to applications that rely on the database. The AWS Database Migration Service can migrate your data to and from most widely-used commercial and open-source databases.
The service supports homogenous migrations such as Oracle to Oracle as well as heterogeneous migrations between different database platforms such as Oracle to Amazon Aurora or Microsoft SQL Server to MySQL. You can also use AWS DMS to stream data to Amazon Redshift, Amazon DynamoDB, and Amazon S3 from any of the supported sources, which are Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle Database, SAP ASE, SQL Server, IBM DB2 LUW, and MongoDB, enabling consolidation and easy analysis of data in a petabyte-scale data warehouse. The AWS Database Migration Service can also be used for continuous data replication with high availability.
For AWS DMS pricing, see [Database Migration Service pricing](https://aws.amazon.com/dms/pricing).
For all supported sources for AWS DMS, see [Sources for data migration](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.html).
For all supported targets for AWS DMS, see [Targets for data migration](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.html).
## Migration Tasks Performed by AWS DMS
In a traditional solution, you need to perform capacity analysis, procure hardware and software, install and administer systems, and test and debug the installation. AWS DMS automatically manages the deployment, management, and monitoring of all hardware and software needed for your migration. You can start your migration within minutes of starting the AWS DMS configuration process.
With AWS DMS, you can scale up (or scale down) your migration resources as needed to match your actual workload. For example, if you determine that you need additional storage, you can easily increase your allocated storage and restart your migration, usually within minutes. On the other hand, if you discover that you aren’t using all of the resource capacity you configured, you can easily downsize to meet your actual workload.
AWS DMS uses a pay-as-you-go model. You only pay for AWS DMS resources while you use them as opposed to traditional licensing models with up-front purchase costs and ongoing maintenance charges.
AWS DMS automatically manages all of the infrastructure that supports your migration server including hardware and software, software patching, and error reporting.
AWS DMS provides automatic failover. If your primary replication server fails for any reason, a backup replication server can take over with little or no interruption of service.
AWS DMS can help you switch to a modern, perhaps more cost-effective database engine than the one you are running now. For example, AWS DMS can help you take advantage of the managed database services provided by Amazon RDS or Amazon Aurora. Or, it can help you move to the managed data warehouse service provided by Amazon Redshift, NoSQL platforms like Amazon DynamoDB, or low-cost storage platforms like Amazon S3. Conversely, if you want to migrate away from old infrastructure but continue to use the same database engine, AWS DMS also supports that process.
AWS DMS supports nearly all of today’s most popular DBMS engines as data sources, including Oracle, Microsoft SQL Server, MySQL, MariaDB, PostgreSQL, Db2 LUW, SAP, MongoDB, and Amazon Aurora.
AWS DMS provides a broad coverage of available target engines including Oracle, Microsoft SQL Server, PostgreSQL, MySQL, Amazon Redshift, SAP ASE, Amazon S3, and Amazon DynamoDB.
You can migrate from any of the supported data sources to any of the supported data targets. AWS DMS supports fully heterogeneous data migrations between the supported engines.
AWS DMS ensures that your data migration is secure. Data at rest is encrypted with AWS Key Management Service (AWS KMS) encryption. During migration, you can use Secure Socket Layers (SSL) to encrypt your in-flight data as it travels from source to target.
## How AWS DMS Works
At its most basic level, AWS DMS is a server in the AWS Cloud that runs replication software. You create a source and target connection to tell AWS DMS where to extract from and load to. Then, you schedule a task that runs on this server to move your data. AWS DMS creates the tables and associated primary keys if they don’t exist on the target. You can pre-create the target tables manually if you prefer. Or you can use AWS SCT to create some or all of the target tables, indexes, views, triggers, and so on.
The following diagram illustrates the AWS DMS process.
![\[How Database Migration Service works\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-how-aws-dms-works.png)
## Latest Updates
AWS DMS is continuously evolving and supporting more and more options, find some of the latest updates following:
+ Support for full-load with change data capture (CDC) and CDC-only tasks running against Oracle source tables created using the `CREATE TABLE AS` statement.
+ New MySQL version AWS DMS now supports MySQL version 8.0 as a source except when the transaction payload is compressed.
+ Support for AWS Secrets Manager integration. You can store the database connection details (user credentials) for supported endpoints securely in AWS Secrets Manager. You can then submit the corresponding secret instead of plain-text credentials to AWS DMS when you create or modify an endpoint. AWS DMS then connects to the endpoint databases using the secret. For more information, see [Using secrets to access Database Migration Service endpoints](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Security.html#security_iam_secretsmanager).
+ Support for Oracle extended data types for source and target.
+ Support for TLS 1.2 for MySQL endpoints.
+ Support for TLS 1.2 for SQL Server endpoints.
For a complete guide with a step-by-step walkthrough including all the latest notes for migrating SQL Server to Aurora MySQL with AWS DMS, see [Migrating a SQL Server Database to Amazon Aurora MySQL](https://docs.aws.amazon.com/dms/latest/sbs/chap-sqlserver2aurora.html).
For more information about AWS DMS, see [What is Database Migration Service?](https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html) and [Best practices for Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html).
# Amazon RDS on Outposts overview
This topic provides conceptual information about Amazon RDS on Outposts, a service that extends Amazon RDS capabilities to on-premises environments. You can learn about how this service enables you to run fully managed databases in your own data centers or co-location facilities, offering low-latency access to local systems and data processing capabilities.
**Note**
This topic is related to Amazon Relational Database Service (Amazon RDS) and isn’t supported with Amazon Aurora.
Amazon RDS on Outposts is a fully managed service that offers the same AWS infrastructure, AWS services, APIs, and tools to virtually any data center, co-location space, or on-premises facility for a truly consistent hybrid experience. Amazon RDS on Outposts is ideal for workloads that require low latency access to on-premises systems, local data processing, data residency, and migration of applications with local system inter-dependencies.
When you deploy Amazon RDS on Outposts, you can run Amazon RDS on premises for low latency workloads that need to be run in close proximity to your on-premises data and applications. Amazon RDS on Outposts also enables automatic backup to an AWS Region. You can manage Amazon RDS databases both in the cloud and on premises using the same AWS Management Console, APIs, and CLI. Amazon RDS on Outposts supports Microsoft SQL Server, MySQL, and PostgreSQL database engines, with support for additional database engines coming soon.
## How It Works
Amazon RDS on Outposts lets you run Amazon RDS in your on-premises or co-location site. You can deploy and scale an Amazon RDS database instance in Outposts just as you do in the cloud, using the AWS console, APIs, or CLI. Amazon RDS databases in Outposts are encrypted at rest using AWS KMS keys. Amazon RDS automatically stores all automatic backups and manual snapshots in the AWS Region.
![\[How RDS on Outposts works\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-rds-outposts-how-it-works.png)
This option is helpful when you need to run Amazon RDS on premises for low latency workloads that need to be run in close proximity to your on-premises data and applications.
For more information, see [AWS Outposts Family](https://aws.amazon.com/outposts), [Amazon RDS on Outposts](https://aws.amazon.com/rds/outposts), and [Create Amazon RDS DB Instances on Outposts](https://aws.amazon.com/blogs/aws/new-create-amazon-rds-db-instances-on-aws-outposts).
# Amazon RDS Proxy overview
This topic provides conceptual topic about Amazon RDS Proxy, a fully managed database proxy service for Amazon RDS. It introduces the key benefits and functionality of RDS Proxy, explaining how it improves application scalability, resilience, and security.understand the purpose and advantages of using Amazon RDS Proxy in their database architecture.
Amazon RDS Proxy is a fully managed, highly available database proxy for Amazon Relational Database Service (RDS) that makes applications more scalable, more resilient to database failures, and more secure.
Many applications, including those built on modern server-less architectures, can have many open connections to the database server, and may open and close database connections at a high rate, exhausting database memory and compute resources. Amazon RDS Proxy allows applications to pool and share connections established with the database, improving database efficiency and application scalability. With Amazon RDS Proxy, fail-over times for Aurora and Amazon RDS databases are reduced by up to 66%. You can manage database credentials, authentication, and access through integration with AWS Secrets Manager and AWS Identity and Access Management (IAM).
You can turn on Amazon RDS Proxy for most applications with no code changes. You don’t need to provision or manage any additional infrastructure. Pricing is simple and predictable: you pay for each vCPU of the database instance for which the proxy is enabled. Amazon RDS Proxy is now generally available for Aurora MySQL, Aurora PostgreSQL, Amazon RDS for MySQL, and Amazon RDS for PostgreSQL.
## Amazon RDS Proxy Benefits
+ **Improved application performance**. Amazon RDS proxy manages a connection pooling which helps with reducing the stress on database compute and memory resources that typically occurs when new connections are established and it is useful to efficiently support a large number and frequency of application connections.
+ **Increase application availability**. By automatically connecting to a new database instance while preserving application connections Amazon RDS Proxy can reduce fail-over time by 66%.
+ **Manage application security**. Amazon RDS Proxy also enables you to centrally manage database credentials using AWS Secrets Manager.
+ **Fully managed**. Amazon RDS Proxy gives you the benefits of a database proxy without requiring additional burden of patching and managing your own proxy server.
+ **Fully compatible with your database**. Amazon RDS Proxy is fully compatible with the protocols of supported database engines, so you can deploy Amazon RDS Proxy for your application without making changes to your application code.
+ **Available and durable**. Amazon RDS Proxy is highly available and deployed over multiple Availability Zones (AZs) to protect you from infrastructure failure.
## How Amazon RDS Proxy Works
![\[How Amazon RDS Proxy Works\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-how-rds-proxy-works.png)
For more information, see [Amazon RDS Proxy for Scalable Serverless Applications](https://aws.amazon.com/blogs/aws/amazon-rds-proxy-now-generally-available) and [Amazon RDS Proxy](https://aws.amazon.com/rds/proxy).
# Amazon Aurora Serverless v1 overview
This topic provides conceptual information about Amazon Aurora Serverless. It introduces Aurora Serverless as an on-demand autoscaling configuration for Amazon Aurora, explaining how it automatically adjusts compute capacity based on application needs.
Amazon Aurora Serverless version 1 (v1) is an on-demand autoscaling configuration for Amazon Aurora. An Aurora Serverless DB cluster is a DB cluster that scales compute capacity up and down based on your application’s needs. This contrasts with Aurora provisioned DB clusters, for which you manually manage capacity. Aurora Serverless v1 provides a relatively simple, cost-effective option for infrequent, intermittent, or unpredictable workloads. It is cost-effective because it automatically starts up, scales compute capacity to match your application’s usage, and shuts down when it’s not in use.
To learn more about pricing, see Serverless Pricing under MySQL-Compatible Edition or PostgreSQL-Compatible Edition on the [Amazon Aurora pricing page](https://aws.amazon.com/rds/aurora/pricing).
Aurora Serverless v1 clusters have the same kind of high-capacity, distributed, and highly available storage volume that is used by provisioned DB clusters. The cluster volume for an Aurora Serverless v1 cluster is always encrypted. You can choose the encryption key, but you can’t disable encryption. That means that you can perform the same operations on an Aurora Serverless v1 that you can on encrypted snapshots. For more information, see Aurora Serverless v1 and snapshots.
Aurora Serverless v1 provides the following advantages:
+ **Simpler than provisioned**. Aurora Serverless v1 removes much of the complexity of managing DB instances and capacity.
+ **Scalable**. Aurora Serverless v1 seamlessly scales compute and memory capacity as needed, with no disruption to client connections.
+ **Cost-effective**. When you use Aurora Serverless v1, you pay only for the database resources that you consume, on a per-second basis.
+ **Highly available storage**. Aurora Serverless v1 uses the same fault-tolerant, distributed storage system with six-way replication as Aurora to protect against data loss.
Aurora Serverless v1 is designed for the following use cases:
+ **Infrequently used applications**. You have an application that is only used for a few minutes several times for each day or week, such as a low-volume blog site. With Aurora Serverless v1, you pay for only the database resources that you consume on a per-second basis.
+ **New applications**. You’re deploying a new application and you’re unsure about the instance size you need. By using Aurora Serverless v1, you can create a database endpoint and have the database automatically scale to the capacity requirements of your application.
+ **Variable workloads**. You’re running a lightly used application, with peaks of 30 minutes to several hours a few times each day, or several times for each year. Examples are applications for human resources, budgeting, and operational reporting applications. With Aurora Serverless v1, you no longer need to provision for peak or average capacity.
+ **Unpredictable workloads**. You’re running daily workloads that have sudden and unpredictable increases in activity. An example is a traffic site that sees a surge of activity when it starts raining. With Aurora Serverless v1, your database automatically scales capacity to meet the needs of the application’s peak load and scales back down when the surge of activity is over.
+ **Development and test databases**. Your developers use databases during work hours but don’t need them on nights or weekends. With Aurora Serverless v1, your database automatically shuts down when it’s not in use.
+ **Multi-tenant applications**. With Aurora Serverless v1, you don’t have to individually manage database capacity for each application in your fleet. Aurora Serverless v1 manages individual database capacity for you.
This process takes almost no time and since the storage is shared between nodes Aurora can scale up or down in seconds for most workloads. The service currently has autoscaling thresholds of 1.5 minutes to scale up and 5 minutes to scale down. That means metrics must exceed the limits for 1.5 minutes to trigger a scale up or fall below the limits for 5 minutes to trigger a scale down. The cool-down period between scaling activities is 5 minutes to scale up and 15 minutes to scale down. Before scaling can happen the service has to find a “scaling point” which may take longer than anticipated if you have long-running transactions. Scaling operations are transparent to the connected clients and applications since existing connections and session state are transferred to the new nodes. The only difference with pausing and resuming is a higher latency for the first connection, typically around 25 seconds. You can find more details in the documentation.
![\[How Aurora Serverless Works\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-aurora-serverless.png)
## How to Provision
Log in to your [Management Console](https://eu-central-1.console.aws.amazon.com/rds/home?#databases:), choose ** Amazon RDS **, and then choose **Create database**.
On **Engine options**, for **Engine versions**, choose **Show versions that support Serverless v2**.
![\[Provision Serverless\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-aurora-serverless-provision.png)
Choose the capacity settings for your use case.
![\[Serverless Capacity Settings\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-aurora-serverless-capacity.png)
For more information, see [Amazon Aurora Serverless](https://aws.amazon.com/rds/aurora/serverless), [Aurora Serverless MySQL Generally Available](https://aws.amazon.com/blogs/aws/aurora-serverless-ga/), and [Amazon Aurora PostgreSQL Serverless Now Generally Available](https://aws.amazon.com/blogs/aws/amazon-aurora-postgresql-serverless-now-generally-available).