AWS dms documentation change
Summary
Restructured documentation for SQL Server to PostgreSQL conversion settings, including reorganization into metadata and SQL code sections, added API/CLI parameter details, default values, and configuration examples.
Security assessment
The changes focus on improving documentation clarity, adding configuration examples, and detailing settings behavior. No security vulnerabilities, weaknesses, or incidents are mentioned. Settings like case sensitivity handling and CITEXT usage relate to functional compatibility, not security controls.
Diff
diff --git a/dms/latest/userguide/schema-conversion-sql-server-postgresql.md b/dms/latest/userguide/schema-conversion-sql-server-postgresql.md index 86ff6ca89..609dbe8f2 100644 --- a//dms/latest/userguide/schema-conversion-sql-server-postgresql.md +++ b//dms/latest/userguide/schema-conversion-sql-server-postgresql.md @@ -7 +7 @@ -# Understanding SQL Server to PostgreSQL conversion settings +Example: configure SQL Server to PostgreSQL settings @@ -9 +9 @@ -SQL Server to PostgreSQL conversion settings in DMS Schema Conversion include the following: +# SQL Server to PostgreSQL conversion settings @@ -11 +11 @@ SQL Server to PostgreSQL conversion settings in DMS Schema Conversion include th - * To convert applicable database objects using generative AI enable the **Generative AI** setting. Objects successfully converted using generative AI will be clearly identified with **Action Item 7744** , which states: "This conversion uses machine learning models that generate predictions based on patterns in data." For more information, see [Converting database objects with generative AI](./schema-conversion-convert.databaseobjects.html). +The following settings apply when the source is Microsoft SQL Server and the target is Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL. You can configure these settings using the AWS Management Console or the [`ModifyConversionConfiguration`](https://docs.aws.amazon.com/dms/latest/APIReference/API_ModifyConversionConfiguration.html) API operation. @@ -13 +13 @@ SQL Server to PostgreSQL conversion settings in DMS Schema Conversion include th - * In SQL Server, you can use indexes with the same name in different tables. However, in PostgreSQL, all index names that you use in the schema must be unique. To make sure that DMS Schema Conversion generates unique names for all your indexes, select **Generate unique names for indexes**. +This topic covers settings specific to the SQL Server to PostgreSQL conversion path. In addition to these settings, DMS Schema Conversion provides settings that apply to all source and target pairs, such as the severity level for action-item comments in converted SQL and the option to use generative AI for conversion. For those settings, see [Common conversion settings](./schema-conversion-settings.html#schema-conversion-settings-common). @@ -15 +15 @@ SQL Server to PostgreSQL conversion settings in DMS Schema Conversion include th - * PostgreSQL versions 10 and earlier don't support procedures. If you aren't familiar with using procedures in PostgreSQL, AWS DMS can convert SQL Server procedures to PostgreSQL functions. To do so, select **Convert procedures to functions**. +When you use the API or AWS CLI, specify conversion path settings under the section names `MSSQL_TO_POSTGRESQL`, `MSSQL_TO_POSTGRESQL_14`, or `MSSQL_TO_POSTGRESQL_15`. All three versioned sections accept the same keys. To find which section names your project uses, call [`DescribeConversionConfiguration`](https://docs.aws.amazon.com/dms/latest/APIReference/API_DescribeConversionConfiguration.html) first and update only the sections present in the response. @@ -17 +17 @@ SQL Server to PostgreSQL conversion settings in DMS Schema Conversion include th - * Your source SQL Server database can store the output of `EXEC` in a table. DMS Schema Conversion creates temporary tables and an additional procedure to emulate this feature. To use this emulation, select **Create additional routines to handle open datasets**. +Each setting shows the AWS Management Console label followed by the API and AWS CLI parameter name in parentheses. Use the parameter name when configuring settings with the API or AWS CLI. @@ -19 +19 @@ SQL Server to PostgreSQL conversion settings in DMS Schema Conversion include th - * You can define the template to use for the schema names in the converted code. For **Schema names** , choose one of the following options: +###### Metadata settings @@ -21 +21 @@ SQL Server to PostgreSQL conversion settings in DMS Schema Conversion include th - * **DB** – Uses the SQL Server database name as a schema name in PostgreSQL. +Settings that control how source SQL Server metadata — object names, schema structure, and index definitions — are handled during conversion. @@ -23 +23 @@ SQL Server to PostgreSQL conversion settings in DMS Schema Conversion include th - * **SCHEMA** – Uses the SQL Server schema name as a schema name in PostgreSQL. +**Treat source database object names as case sensitive** (`CaseSensitivityNames`) @@ -25 +24,0 @@ SQL Server to PostgreSQL conversion settings in DMS Schema Conversion include th - * **DB_SCHEMA** – Uses a combination of the SQL Server database and schema names as a schema name in PostgreSQL. @@ -27 +26 @@ SQL Server to PostgreSQL conversion settings in DMS Schema Conversion include th - * You can keep the letter case in the names of source operands. To avoid conversion of operand names to lowercase, select **Avoid casting to lowercase for case-sensitive operations**. This option is applicable only if the case-sensitivity feature is enabled in the source database. +Controls how object names (tables, columns, procedures, and so on) are handled during conversion. @@ -29 +28 @@ SQL Server to PostgreSQL conversion settings in DMS Schema Conversion include th - * You can keep the parameter names from your source database. DMS Schema Conversion can add double quotation marks to the names of parameters in the converted code. To do so, select **Keep original parameter names**. + * `true` — Object names are preserved in their original case and wrapped in double-quote identifiers (for example, `"MyTable"`). Use this when your application references objects using mixed-case names. @@ -31 +30 @@ SQL Server to PostgreSQL conversion settings in DMS Schema Conversion include th - * You can keep a length of routine parameters from your source database. DMS Schema Conversion creates domains and uses them to specify a length of routine parameters. To do so, select **Preserve parameter lengths**. + * `false` — Object names are folded to lower case without quoting (for example, `mytable`). This matches the default PostgreSQL case-folding behaviour. @@ -33 +31,0 @@ SQL Server to PostgreSQL conversion settings in DMS Schema Conversion include th - * To convert unsupported built-in objects to stub objects, enable the **Convert unsupported built-in objects to stub objects** setting: @@ -35 +32,0 @@ SQL Server to PostgreSQL conversion settings in DMS Schema Conversion include th - * When enabled, DMS SC replaces unsupported built-in objects with corresponding stub objects in the target database. This feature converts code sections that would normally be enclosed by migration issue 7811 or 7904. It creates stub objects based on the type of the source built-in objects `PROCEDURE` for procedures, `VIEW` for views or tables. @@ -37 +33,0 @@ SQL Server to PostgreSQL conversion settings in DMS Schema Conversion include th -Conversion of a source database object with a call of an unsupported object results in a call of a stub object and migration issue 7822. @@ -39 +35 @@ Conversion of a source database object with a call of an unsupported object resu -You can choose to create stub objects in a separate schema by enabling the **Create stub objects in a separate schema** option. When selected, stub objects are created in a special schema named `aws_sqlserver_stub` in the target database. If not selected, they are created in the same schema as the calling objects. +**Type:** Boolean (`true` | `false`) @@ -41 +37 @@ You can choose to create stub objects in a separate schema by enabling the **Cre - * Stub routines are named based on the fully qualified name of the original built-in. For stub views, the naming convention includes the system schema name `system_schema_name$builtin_view_name`. +**Default:** `false` @@ -43 +39 @@ You can choose to create stub objects in a separate schema by enabling the **Cre -During re-conversion, DMS SC checks for existing stub routines in the target database. If a routine with the same name and input parameters already exists, it is not overwritten. +**Generate unique names for indexes** (`UniqueIndexGeneration`) @@ -45 +40,0 @@ During re-conversion, DMS SC checks for existing stub routines in the target dat -After conversion, review and implement custom code for stub routines as needed. @@ -47 +42 @@ After conversion, review and implement custom code for stub routines as needed. - * `CITEXT` datatype for all string datatypes setting in DMS Schema Conversion include the following: +Controls whether DMS Schema Conversion generates unique index names across the target PostgreSQL schema. In SQL Server, indexes can share the same name across different tables. In PostgreSQL, all index names within a schema must be unique. @@ -49 +44 @@ After conversion, review and implement custom code for stub routines as needed. - * To use the `CITEXT` datatype for case-insensitive string operations when converting from SQL Server to PostgreSQL, enable the **Use CITEXT for all string datatypes** setting. This option helps maintain consistent behavior when migrating from a case-insensitive SQL Server to a case-sensitive PostgreSQL environment. + * `true` — DMS Schema Conversion generates unique names for all indexes, appending a suffix where necessary to avoid collisions. Use this when your source schema has indexes with the same name on different tables. @@ -51 +46 @@ After conversion, review and implement custom code for stub routines as needed. - * When enabled, DMS SC converts all relevant string datatypes from the source SQL Server database to `CITEXT` in PostgreSQL. This eliminates the need for explicit LOWER () function calls in conditions and automatically casts string expressions in conditional operations to `CITEXT`. + * `false` — Index names are preserved as-is from the source. If the same index name exists on multiple tables, deployment to PostgreSQL may fail with a duplicate name error. @@ -53 +47,0 @@ After conversion, review and implement custom code for stub routines as needed. - * To determine if your SQL Server instance is case-sensitive, run the following query: @@ -55 +48,0 @@ After conversion, review and implement custom code for stub routines as needed. - SELECT SERVERPROPERTY('COLLATION'); @@ -57 +49,0 @@ After conversion, review and implement custom code for stub routines as needed. -A result containing 'CI' indicates case-insensitive, while 'CS' indicates case-sensitive. @@ -59 +51 @@ A result containing 'CI' indicates case-insensitive, while 'CS' indicates case-s - * The `CITEXT` conversion may not apply in scenarios where explicit case-sensitive collate settings are used at the server, database, or column level. +**Type:** Boolean (`true` | `false`) @@ -61 +53 @@ A result containing 'CI' indicates case-insensitive, while 'CS' indicates case-s -To use this feature, ensure that the `CITEXT` module is installed and available in your target PostgreSQL database. +**Default:** `true` @@ -63 +55 @@ To use this feature, ensure that the `CITEXT` module is installed and available - * When using the `CITEXT` datatype conversion, consider the following best practices: +**Convert procedures to functions** (`ConvertProceduresToFunction`) @@ -65 +56,0 @@ To use this feature, ensure that the `CITEXT` module is installed and available - * Enable this feature when migrating from a case-insensitive SQL Server to maintain consistent behavior in PostgreSQL. @@ -67 +58 @@ To use this feature, ensure that the `CITEXT` module is installed and available - * Review your application code to ensure it doesn't rely on case-sensitive string operations. +Controls whether stored procedures are converted to PostgreSQL functions or procedures. @@ -69 +60 @@ To use this feature, ensure that the `CITEXT` module is installed and available - * Thoroughly test your application after migration to verify that case-insensitive behavior is maintained as expected. + * `false` — Procedures are converted to PostgreSQL `PROCEDURE` objects (requires PostgreSQL 11 or later). @@ -70,0 +62 @@ To use this feature, ensure that the `CITEXT` module is installed and available + * `true` — Procedures that return a result set are converted to PostgreSQL `FUNCTION` objects. Use this when your application reads a result set from a procedure call, or when targeting PostgreSQL 10 or earlier. @@ -73,0 +66,157 @@ To use this feature, ensure that the `CITEXT` module is installed and available + +**Type:** Boolean (`true` | `false`) + +**Default:** `false` + +**Create additional routines to handle open datasets** (`OpenDatasetsProcedures`) + + +Controls how procedures that store the output of `EXEC` into a table are converted. + + * `false` — These patterns are left as action items. + + * `true` — DMS Schema Conversion creates temporary tables and an additional procedure to emulate this pattern on the target. + + + + +**Type:** Boolean (`true` | `false`) + +**Default:** `false` + +**Schema names** (`SchemaNameTemplate`) + + +Controls how the target PostgreSQL schema name is generated from the SQL Server two-part naming convention (database + schema). + +Console label | API/CLI value | Generated target schema name +---|---|--- +**DB_SCHEMA** | `DB_SCHEMA` | `database`_`schema` (for example, `AdventureWorks_dbo`). Use when migrating multiple databases to the same PostgreSQL instance to avoid name collisions. +**DB** | `DB` | `database` (for example, `AdventureWorks`). +**SCHEMA** | `SCHEMA` | `schema` (for example, `dbo`). Use when migrating a single database and you want clean schema names without a database prefix. + +**Type:** String (enum) + +**Default:** `DB_SCHEMA` + +###### SQL code settings + +Settings that control how SQL Server code — identifiers, parameters, data types, and unsupported functions — is handled during conversion. + +**Keep object names in the same case** (`AvoidCastingToLowerCase`) + + +Controls whether DMS Schema Conversion adds automatic `LOWER()` casts around identifiers in converted code. + + * `false` — DMS Schema Conversion adds `LOWER()` casts where needed to normalise case comparisons. + + * `true` — No automatic `LOWER()` casts are added. Use this when your PostgreSQL database uses the `citext` extension or a case-insensitive collation to handle comparisons natively. + + + + +**Type:** Boolean (`true` | `false`) + +**Default:** `false` + +###### Note + +This setting has no effect when `UseCitextForAllStringDatatypes` is enabled. When all string types are mapped to `CITEXT`, case-insensitive comparison is handled natively by the type itself, so no `LOWER()` casts are generated regardless of this setting. + +**Keep original parameter names** (`KeepOriginalParameterManes`) + + +Controls whether original parameter names from T-SQL routines are preserved in the converted PostgreSQL routine signature. + + * `false` — Parameters may be renamed to avoid conflicts with PostgreSQL reserved words or naming conventions. + + * `true` — Original parameter names are kept verbatim. Use this when your application calls routines using named parameter syntax (for example, `my_proc(param_name => value)`). + + + + +**Type:** Boolean (`true` | `false`) + +**Default:** `false` + +###### Note + +The API parameter name is `KeepOriginalParameterManes` (ending in _Manes_ , not _Names_). Use this exact spelling when calling the API or AWS CLI. Using `KeepOriginalParameterNames` will not work. + +**Use CITEXT for all string datatypes** (`UseCitextForAllStringDatatypes`) + + +Controls the target data type for all character columns and parameters. + + * `false` — Character types (`CHAR`, `VARCHAR`, `NCHAR`, `NVARCHAR`) are mapped to the corresponding PostgreSQL types. + + * `true` — All character types are mapped to `CITEXT` (case-insensitive text). Use this when migrating from a case-insensitive SQL Server collation and you want to preserve that behaviour in PostgreSQL without modifying queries. + + + + +**Type:** Boolean (`true` | `false`) + +**Default:** `false` + +###### Note + +When this setting is enabled, DMS Schema Conversion automatically adds `CREATE EXTENSION IF NOT EXISTS citext;` to the converted schema scripts (both when applying to the target and when saving to a file). However, the `citext` module must be available in your target PostgreSQL database before you apply the converted schema. + +When `UseCitextForAllStringDatatypes` is enabled, the **Keep object names in the same case** (`AvoidCastingToLowerCase`) setting has no effect, because `CITEXT` handles case-insensitive comparison natively. + +**Convert unsupported built-in objects to stub objects** (`ConvUnsupportedBuiltinsToStubs`) + + +Controls what happens when DMS Schema Conversion encounters a T-SQL built-in function that has no direct PostgreSQL equivalent.