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:

Conversion overviews provide a cross-section of your schema conversion progress.
Objects supported by the deterministic code and schema conversion lists SQL Server objects supported for deterministic schema conversion.
Interactive SQL editor describes what objects you can modify directly in the conversion workspace editor.
Gemini-powered conversion features looks at how you can integrate generative AI support to expedite the schema conversion process.
Conversion mapping files section provides an overview of customization directives you can use to override the rules of deterministic schema conversion.

Conversion progress overviews

Conversion workspaces robust overview information, where you can gain insights into the total number of outstanding or solved conversion issues, Gemini-assisted augmentations, and the general health of your conversion process.

Conversion workspace screen with the Conversion Overview tab where
you can see the number of converted objects, conversion issues,
and Gemini-assisted conversion enhancements. — **Figure 1.** Conversion workspace overview screen, where you can monitor the progress of your conversion, view issues and inspect the resulting PostgreSQL code. (click to enlarge)

You can use this view to filter objects in your schema by type, issue severity, actions needed, or conversion status.

Conversion workspace screen that shows how you can filter converted
objects by type or status. — **Figure 2.** Converted objects filtering by status and object type. (click to enlarge)

For more information about using conversion overviews to inspect conversion results, see Work with 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 deterministic SQL conversion comes with certain limitations. If your source database includes objects that aren't supported by the deterministic schem conversion rules, you can convert them by:

Writing SQL statements in the interactive editor.
Using Gemini-powered conversion support.

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:

SQL Server data types in the Microsoft SQL Server documentation.
PostgreSQL data types in the PostgreSQL documentation.

`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.
Important: If you don't use the SCHEMA directive, make sure you use the fully qualified table name: SCHEMA_NAME.SOURCE_TABLE_NAME
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.
Important: If you don't use the SCHEMA directive, make sure you use the fully qualified table name: SCHEMA_NAME.SOURCE_TABLE_NAME
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

To learn about using conversion workspaces, see:
- Create a conversion workspace
- Work with conversion workspaces
To get a complete, step-by-step migration walkthrough, see SQL Server to Cloud SQL for PostgreSQL migration guide.