Conversion workspaces

Conversion workspaces help you convert the schema and objects from your source database into the SQL syntax that is compatible with your destination database. This page provides an overview of Database Migration Service conversion workspaces:

Deterministic code and schema conversion

When you create a conversion workspace, Database Migration Service immediately performs the initial schema conversion using a set of deterministic conversion rules where specific SQL Server data types and objects are mapped to specific PostgreSQL data types and objects. This process supports a very specific subset of available SQL Server database objects.

Unsupported objects

The automatic syntax conversion comes with certain limitations. If your source database includes objects that aren't supported by the automatic schema conversion, you can convert them by:

The following data types aren't supported, and aren't replicated to the destination:

  • SQL_VARIANT
  • HIERARCHYID
  • GEOMETRY
  • GEOGRAPHY

Additionally, Database Migration Service replicates user-defined data types, but only stores the base data type from which you derive your user-defined types. For example, if you define a USERNAME data type based on the VARCHAR(50) data type, the data is stored in the destination as VARCHAR(50).

Supported objects

Deterministic code and conversion provides support for the following SQL Server database objects:

Supported SQL Server schema elements

  • Constraints
  • Indexes (only indexes which are created in the same schema as their table)
  • Sequences
  • User Defined Types
  • Synonyms
  • Tables
  • Schemas
  • Views
  • Indexed Views

Supported SQL Server code elements

  • Triggers (table level only)
  • Procedures
  • Functions

Interactive SQL editor

The interactive SQL editor lets you modify converted PostgreSQL syntax directly in Database Migration Service. You can use it to fix conversion issues or adjust the schema to better fit your needs. Some objects can't be modified in the built-in editor.

Editable SQL Server objects

After you convert source database code and schema, you can use the interactive editor to modify the generated SQL for certain types of objects. The following SQL Server objects are supported by the editor:

  • Table triggers (requires permission)
  • Functions
  • Procedures
  • Synonyms
  • Views
  • Indexed Views
  • Constraints
  • Indexes
  • Sequences

Additionally, some objects are converted but not available for editing directly inside Database Migration Service. To modify such objects, you need to perform the updates directly on the destination database after you apply the converted schema and code.

Objects that aren't supported for editing:

  • User-defined object types
  • Tables
  • Schemas

Accelerate code and schema conversion with Gemini

Database Migration Service integrates Gemini for Google Cloud into the conversion workspaces to help you speed up and improve the conversion process in the following areas:

  • Enhance the deterministic conversion results with Gemini-powered auto-conversion to use the power of AI to significantly reduce the number of manual adjustments needed in your PostgreSQL code.

  • Provide code explainability features with the conversion assistant: a set of dedicated prompts that can help you better understand the conversion logic, propose fixes for conversion issues, or optimize converted code.

  • Expedite applying fixes for conversion issues with Gemini code conversion suggestions: a mechanism where the Gemini model can learn as you fix conversion issues and suggest changes to other faulty objects in the workspace.

For more information about Gemini-powered conversion, see the following pages:

Conversion mapping files

You can customize the conversion logic with a conversion mapping file. The conversion mapping file is a text file contains precise instructions (referred to as conversion directives) for how your SQL Server objects should be converted into PostgreSQL objects.

Supported conversion directives

Database Migration Service supports the following conversion directives for conversion mapping files:

EXPORT_SCHEMA

EXPORT_SCHEMA is a mandatory directive for all conversion mapping files. Database Migration Service requires this instruction to ensure that your source schemas are converted to the correct destination schemas. Make sure your conversion mapping files include this line: 

EXPORT_SCHEMA 1

SCHEMA

Database Migration Service must be able to determine which schema contains the objects that should be modified with your conversion directives. The SCHEMA directive causes the customizations in this conversion mapping file to be applied only to objects in this particular schema.

Use the following format:

SCHEMA SCHEMA_NAME

Where SCHEMA_NAME is the name of your schema in the source database.

  • If you include this directive in the conversion mapping file, all customizations are applied only to objects contained in this specific schema. If you want to customize objects in other schemas, you can create multiple conversion mapping files and upload them to the conversion workspace.
  • If you skip this directive, you must provide explicit schema names for objects modified by other conversion directives. For example, instead of using SOURCE_TABLE_NAME for the REPLACE_TABLES directive, you would need to use "SCHEMA_NAME.SOURCE_TABLE_NAME".

DATA_TYPE

You can use this directive to explicitly map any data type between SQL Server and PostgreSQL syntax. This directive expects a list of mappings separated by commas. The whole definition must be provided on a single line. Use the following format: 

DATA_TYPE SQLSERVER_DATA_TYPE1:PGSQL_DATA_TYPE1,SQLSERVER_DATA_TYPE2:PGSQL_DATA_TYPE2...

Where SQLSERVER_DATA_TYPE and PGSQL_DATA_TYPE are data types supported by their respective SQL Server and PostgreSQL versions you use in your migration. For information on supported versions, see Scenario overview.

Example:

DATA_TYPE REAL:double precision,SMALLINT:integer

For more information on SQL Server and PostgreSQL data types, see:

MODIFY_TYPE

The MODIFY_TYPE directive lets you control to what data type Database Migration Service converts a specific column in your source table. This directive expects a list of mappings separated by commas. Use the following format: 

MODIFY_TYPE SOURCE_TABLE_NAME1:COLUMN_NAME:EXPECTED_END_RESULT_DATA_TYPE,SOURCE_TABLE_NAME2:COLUMN_NAME:EXPECTED_END_RESULT_DATA_TYPE...

Where:

  • SOURCE_TABLE_NAME is the name of the table that contains the column where you want to change the data type.
  • COLUMN_NAME is the name of the column for which you want the converted data type to be different than the source data type.
  • EXPECTED_END_RESULT_DATA_TYPE is the PostgreSQL data type that you want the converted column to use.

Example:

MODIFY_TYPE events:dates_and_times:DATETIME,users:pseudonym:TEXT

PG_INTEGER_TYPE

By default, Database Migration Service attempts to convert your source SQL Server numeric types to their portable ANSI standard equivalents (INTEGER, SMALLINT, DECIMAL). Use the PG_INTEGER_TYPE directive to instruct Database Migration Service to convert your source SQL Server numeric types to PostgreSQL-specific data types. Use the following format: 

PG_INTEGER_TYPE 1

DEFAULT_NUMERIC

If you use the PG_INTEGER_TYPE directive, Database Migration Service converts DECIMAL without specified precision points into the PostgreSQL BIGINT type. To change this behavior, use the DEFAULT_NUMERIC directive and specify what data type should be used for DECIMAL types without specified precision points. Use the following format: 

DEFAULT_NUMERIC POSTGRESQL_NUMERIC_DATA_TYPE

Where POSTGRESQL_NUMERIC_DATA_TYPE is one of the following: integer, smallint, bigint.

Example:

DEFAULT_NUMERIC integer

REPLACE_COLS

You can use the REPLACE_COLS directive to rename columns in your converted schema. This directive expects a list of mappings separated by commas. Use the following format: 

REPLACE_COLS SOURCE_TABLE_NAME1(SOURCE1_TABLE1_COLUMN_NAME1:DESTINATION_TABLE1_COLUMN_NAME1,SOURCE_TABLE1_COLUMN_NAME2:DESTINATION_TABLE1_COLUMN_NAME2),SOURCE_TABLE_NAME2(SOURCE_TABLE2_COLUMN_NAME1:DESTINATION_TABLE2_COLUMN_NAME1,SOURCE_TABLE2_COLUMN_NAME2:DESTINATION_TABLE2_COLUMN_NAME2)...

Where:

  • SOURCE_TABLE_NAME is the name of the table that contains the column whose name you want to change.
  • SOURCE_COLUMN_NAME is the name of the column in your source whose name you want to change.
  • DESTINATION_COLUMN_NAME is the new name you for the column you want to use in the converted schema.

Example:

REPLACE_COLS events(dates_and_times:event_dates),users(pseudonym:nickname)

REPLACE_TABLES

You can use the REPLACE_TABLES directive to rename tables in your converted schema. This directive expects a list of mappings separated by spaces. Use the following format: 

REPLACE_TABLES SOURCE_TABLE_NAME1:DESTINATION_TABLE_NAME1 SOURCE_TABLE_NAME2:DESTINATION_TABLE_NAME2

Where:

  • SOURCE_TABLE_NAME is the name of the source table you want to rename in the converted schema.
  • DESTINATION_TABLE_NAME is the new name for the table you want to use in the converted schema.

Example:

REPLACE_TABLES "events:login_events" "users:platform_users"

You can also use this directive to move tables between schemas by adding the schema prefix to the new table name. This mechanism can be used regardless of how you use the SCHEMA directive for the whole conversion file. For example: 

REPLACE_TABLES "events:NEW_SCHEMA_NAME.login_events"

Sample conversion mapping file

See the following code for an example a conversion mapping file that uses all supported conversion directives:

EXPORT_SCHEMA 1
SCHEMA root
PG_INTEGER_TYPE 1
DEFAULT_NUMERIC integer
DATA_TYPE REAL:double precision,SMALLINT:integer
MODIFY_TYPE events:dates_and_times:DATETIME
REPLACE_COLS events(dates_and_times:event_dates),users(pseudonym:nickname)
REPLACE_TABLES events:login_events users:platform_users

The results of using this file are as follows:

  • EXPORT_SCHEMA 1 is a required directive.
  • SCHEMA root causes all other directives to apply only to, columns, and data types defined in the root schema.
  • PG_INTEGER_TYPE 1 makes Database Migration Service convert all SQL Server numeric data types found in tables in the root schema to PostgreSQL-specific types instead of ANSI portable numeric types.
  • DEFAULT_NUMERIC causes Database Migration Service to convert DECIMAL values that don't have a specified precision point into PostgreSQL INTEGER type. This only applies to DECIMAL values found in tables in the root schema.
  • DATA_TYPE REAL:double precision,SMALLINT:integer causes Database Migration Service to convert specific numeric types in tables in the root schema in the following manner:
    • REAL values are converted into PostgreSQL DOUBLE PRECISION.
    • SMALLINT values are converted into PostgreSQL INTEGER.
  • MODIFY_TYPE directive causes Database Migration Service to convert the data in the dates_and_times column in the events source table specifically to the PostgreSQL DATETIME type, regardless of the actual source column format.
  • REPLACE_COLS events(dates_and_times:event_dates),users(pseudonym:nickname) makes Database Migration Service rename the following columns in the converted schema:
    • dates_and_times column in the source events table is renamed to event_dates in the same table in converted schema.
    • pseudonym column in the source users table is renamed to nickname in the same table in converted schema.
    The renaming operation is only applied to the events and users tables in the root schema.
  • REPLACE_TABLES events:login_events users:platform_users renames the following tables in the converted schema:
    • The events table is renamed to login_events.
    • The users table is renamed to platform_users.
    The renaming operation is only applied to the events and users tables in the root schema.

What's next