Migrating a SQL Server database to Babelfish for Aurora PostgreSQL - Amazon Aurora
Overview of the migration processEvaluating and handling differences between SQL Server and BabelfishImport/export tools for migrating from SQL Server to Babelfish
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Migrating a SQL Server database to Babelfish for Aurora PostgreSQL

You can use Babelfish for Aurora PostgreSQL to migrate an SQL Server database to an Amazon Aurora PostgreSQL DB cluster. Before migrating, review Using Babelfish with a single database or multiple databases.

Overview of the migration process

The following summary lists the steps required to successfully migrate your SQL Server application and make it work with Babelfish. For information about the tools you can use for the export and import processes and for more detail, see Import/export tools for migrating from SQL Server to Babelfish.

  1. Create a new Aurora PostgreSQL DB cluster with Babelfish turned on. To learn how, see Creating a Babelfish for Aurora PostgreSQL DB cluster.

    To import the various SQL artifacts exported from your SQL Server database, connect to the Babelfish cluster using a SQL Server tool such as sqlcmd. For more information, see Using a SQL Server client to connect to your DB cluster.

  2. On the SQL Server database that you want to migrate, export the data definition language (DDL). The DDL is SQL code that describes database objects that contain user data (such as tables, indexes, and views) and user-written database code (such as stored procedures, user-defined functions, and triggers).

    For more information, see Using SQL Server Management Studio (SSMS) to migrate to Babelfish.

  3. Run an assessment tool to evaluate the scope of any changes that you might need to make so that Babelfish can effectively support the application running on SQL Server. For more information, see Evaluating and handling differences between SQL Server and Babelfish.

  4. To load the data, we recommend using the Amazon DMS with either Babelfish or Aurora PostgreSQL as a target endpoint based on your migration requirements. Make sure to modify the columns with the recommended Babelfish data types. To do so, see the Prerequisites to using Babelfish as a target for Amazon DMS.

  5. On your new Babelfish DB cluster, run the DDL within your specified T-SQL database to create only the schemas, user-defined data types, and tables with their primary key constraints.

  6. Use Amazon DMS to migrate your data from SQL Server to Babelfish tables. For continuous replication using SQL Server Change Data Capture or SQL Replication, use Aurora PostgreSQL instead of Babelfish as the endpoint. To do so, see the Using Babelfish for Aurora PostgreSQL as a target for Amazon Database Migration Service.

  7. When the data load completes, create all the remaining T-SQL objects that support the application on your Babelfish cluster.

  8. Reconfigure your client application to connect to the Babelfish endpoint instead of your SQL Server database. For more information, see Connecting to a Babelfish DB cluster.

  9. Modify your application as needed and retest. For more information, see Differences between Babelfish for Aurora PostgreSQL and SQL Server.

You still need to assess your client-side SQL queries. The schemas generated from your SQL Server instance convert only the server-side SQL code. We recommend that you take the following steps:

  • Capture client-side queries by using the SQL Server Profiler with the TSQL_Replay predefined template. This template captures T-SQL statement information that you can then replay for iterative tuning and testing. You can start the profiler within SQL Server Management Studio from the Tools menu. Choose SQL Server Profiler to open the profiler and choose the TSQL_Replay template.

    To use for your Babelfish migration, start a trace and then run your application using your functional tests. The profiler captures the T-SQL statements. When you finish testing, stop the trace. Save the result to an XML file with your client-side queries (File > Save as > Trace XML File for Replay).

    For more information, see SQL Server Profiler in the Microsoft documentation. For more information about the TSQL_Replay template, see SQL Server Profiler Templates.

  • For applications with complex client-side SQL queries, we recommend that you use Babelfish Compass to analyze these queries for Babelfish compatibility. If the analysis indicates that the client-side SQL statements contain unsupported SQL features, review the SQL aspects in the client application and modify as needed.

  • You can also capture the SQL queries as extended events (.xel format). To do so, use the SSMS XEvent Profiler. After generating the .xel file, extract the SQL statements into .xml files that Compass can then process. For more information, see Use the SSMS XEvent Profler in the Microsoft documentation.

When you're satisfied with all testing, analysis, and any modifications needed for your migrated application, you can start using your Babelfish database for production. To do so, stop the original database and redirect live client applications to use the Babelfish TDS port.

Evaluating and handling differences between SQL Server and Babelfish

For best results, we recommend that you evaluate the generated DDL/DML and the client query code before actually migrating your SQL Server database application to Babelfish. Depending on the version of Babelfish and the specific features of SQL Server that your application implements, you might need to refactor your application or use alternatives for functionality that aren't fully supported yet in Babelfish.

  • To assess your SQL Server application code, use Babelfish Compass on the generated DDL to determine how much T-SQL code is supported by Babelfish. Identify T-SQL code that might need modifications before running on Babelfish. For more information about this tool, see Babelfish Compass tool on GitHub.

    Note

    Babelfish Compass is an open-source tool. Report any issues with Babelfish Compass through GitHub instead of through Amazon Support.

You can use Generate Script Wizard with SQL Server Management Studio (SSMS) to generate the SQL file that is assessed by Babelfish Compass or Amazon Schema Conversion Tool CLI. We recommend the following steps to streamline the assessment.

  1. On the Choose Objects page, choose Script entire database and all database objects.

    Using SSMS Generate script wizard to choose objects.

  2. For the Set Scripting Options, choose Save as script file as a Single script file.

    Using SSMS Generate script wizard to set the scripting options.

  3. Choose Advanced to change the default scripting options to identify features that normally are set to false for a full assessment:

    • Script Change Tracking to True

    • Script Full-Text Indexes to True

    • Script Triggers to True

    • Script Logins to True

    • Script Owner to True

    • Script Object-Level Permissions to True

    • Script Collations to True

    Using SSMS Generate script wizard to set the advance scripting options.

  4. Perform the remaining steps in the wizard to generate the file.

Import/export tools for migrating from SQL Server to Babelfish

We recommend that you use Amazon DMS as the primary tool for migrating from SQL Server to Babelfish. However, Babelfish supports several other ways to migrate data using SQL Server tools that includes the following.

  • SQL Server Integration Services (SSIS) for all versions of Babelfish. For more information, see Migrate from SQL Server to Aurora PostgreSQL using SSIS and Babelfish.

  • Use the SSMS Import/Export Wizard for Babelfish versions 2.1.0 and later. This tool is available through the SSMS, but it's also available as a standalone tool. For more information, see Welcome to SQL Server Import and Export Wizard in the Microsoft documentation.

  • The Microsoft bulk data copy program (bcp) utility lets you copy data from a Microsoft SQL Server instance to a data file in the format you specify. For more information, see bcp Utility in the Microsoft documentation. Babelfish now supports the data migration using the BCP client and the bcp utility now supports -E flag (for identity columns) and -b flag (for batching inserts). Certain bcp options aren't supported, including -C, -T, -G, -K, -R, -V, and -h.

Using SQL Server Management Studio (SSMS) to migrate to Babelfish

We recommend generating separate files for each of the specific object types. You can use the Generate Scripts wizard in SSMS for each set of DDL statements first, and then modify the objects as a group to fix any issues found during the assessment.

Perform these steps to migrate the data using Amazon DMS or other data migration methods. Run these create script types first for a better and faster approach to load the data on the Babelfish tables in Aurora PostgreSQL.

  1. Run CREATE SCHEMA statements.

  2. Run CREATE TYPE statements to create user-defined data types.

  3. Run basic CREATE TABLE statements with the primary keys or unique constraints.

Perform the data load using the recommended import/export tool. Run the modified scripts for the following steps to add the remaining database objects. You need the create table statements to run these scripts for the constraints, triggers, and indexes. After the scripts generate, delete the create table statements.

  1. Run ALTER TABLE statements for the check constraints, foreign key constraints, default constraints.

  2. Run CREATE TRIGGER statements.

  3. Run CREATE INDEX statements.

  4. Run CREATE VIEW statements.

  5. Run CREATE STORED PROCEDURE statements.

To generate scripts for each object type

Use the following steps to create the basic create table statements using the Generate Scripts wizard in SSMS. Follow the same steps to generate scripts for the different object types.

  1. Connect to your existing SQL Server instance.

  2. Open the context (right-click) menu for a database name.

  3. Choose Tasks, and then choose Generate Scripts....

  4. On the Choose Objects pane, choose Select specific database objects. Choose Tables, select all tables. Choose Next to continue.

    Using SSMS Generate Scripts wizard to choose objects and tables.

  5. On the Set Scripting Options page, choose Advanced to open the Options settings. To generate the basic create table statements, change the following default values:

    • Script Defaults to False.

    • Script Extended Properties to False. Babelfish does not support extended properties.

    • Script Check Constraints to False. Script Foreign Keys to False.

    Using SSMS Generate script wizard to set the advance options.

  6. Choose OK.

  7. On the Set Scripting Options page, choose Save as script file and then choose the Single script file option. Enter your File name.

    Using SSMS Generate script wizard to set the default values of the script.

  8. Choose Next to view the Summary wizard page.

  9. Choose Next to start the script generation.

    You can continue to generate scripts for the other object types in the wizard. Instead of choosing Finish after the file is saved, choose the Previous button three times to go back to the Choose Objects page. Then repeat the steps in the wizard to generate scripts for the other object types.