Configure your source SQL Server database

This page describes how to configure the following source database instances for heterogeneous SQL Server migrations:

Configure a Cloud SQL for SQL Server instance

To configure a Cloud SQL for SQL Server source instance for your migration process, do the following:

  1. Create a dedicated migration user account in your instance. See Create a user in the Cloud SQL documentation.
  2. Connect to your Cloud SQL instance with a SQL client. You can use the following methods:
    • sqlcmd client. You can use this method to connect to your instance private IP, but it might require that you create a Compute Engine virtual machine.
    • gcloud sql connect command. This command works only for Cloud SQL instances that have a public IP address enabled.
  3. Run the following commands on your source instance:
    1. Assign the db_owner and db_denydatawriter roles to the migration user you created in step 1. 
      EXEC sp_addrolemember 'db_owner', 'USER_NAME';
EXEC sp_addrolemember 'db_denydatawriter', 'USER_NAME';

    2. For each database you want to migrate, enable change data capture (CDC) and snapshot isolation. A migration job can only migrate one database at a time. If you plan to create multiple migration jobs, repeat this command for every database you want to migrate. 

      EXEC msdb.dbo.gcloudsql_cdc_enable_db 'DATABASE_NAME';
ALTER DATABASE DATABASE_NAME SET ALLOW_SNAPSHOT_ISOLATION ON;
    3. For each database, enable CDC on all tables you want to migrate. Execute this command separately for each table. 
      USE [DATABASE_NAME]
EXEC sys.sp_cdc_enable_table
@source_schema = N'SCHEMA_NAME',
@source_name = N'TABLE_NAME',
@role_name = NULL
  4. At this stage, we also recommend that you check for the following entities in your source database: tables without primary keys and objects defined with EXECUTE AS DATABASE_USER statements.

    There are additional steps that you need to perform for each of these entities. Expand the following sections for more information.

    Tables that don't have primary keys or a unique index

    Database Migration Service migrates only tables that have primary keys. If your source database includes tables that don't have primary keys or a unique index, you need to manually create primary key constraints in the converted tables in the destination database at a later stage.

    There are no extra steps to perform at this moment, but we recommend you consider which approach to creating primary keys can work best for your scenario. For more information, see Migrate tables without primary keys.

    Objects defined with the EXECUTE AS statement

    Database Migration Service doesn't migrate user and server login data. If your source database contains objects defined with the EXECUTE AS DATABASE_USER clause, these objects might not function properly after the migration because the destination database won't have these users.

    You can check for such objects in your source database ahead of migration with the following SQL query: 

    SELECT s.name AS schema_name, o.name AS object_name, p.name AS user_name
FROM sys.sql_modules m  
INNER JOIN sys.objects o ON o.object_id = m.object_id
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
INNER JOIN sys.database_principals p ON p.principal_id = m.execute_as_principal_id

    If the query returns any results, you have to create the same user logins in the destination database when you Create and configure the destination Cloud SQL instance.

Configure a self-hosted database

To configure a self-hosted SQL Server source instance for your migration process, do the following:

  1. Connect to your source instance with a SQL client, for example the sqlcmd utility.
  2. At the SQL prompt, run the following commands:
    1. Create a dedicated migration user and grant it the db_owner and db_denydatawriter roles. 
      USE master;
CREATE LOGIN YOUR_LOGIN WITH PASSWORD = 'PASSWORD';
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
EXEC sp_addrolemember 'db_owner', 'USER_NAME';
EXEC sp_addrolemember 'db_denydatawriter', 'USER_NAME';

    2. For each database you want to migrate, enable change data capture (CDC) and snapshot isolation. A migration job can only migrate one database at a time. If you plan to create multiple migration jobs, repeat this command for every database you want to migrate. 

      USE [DATABASE_NAME]
GO
EXEC sys.sp_cdc_enable_db
GO
    3. For each database, enable CDC on all tables you want to migrate. Execute this command separately for each table. 
      USE [DATABASE_NAME]
EXEC sys.sp_cdc_enable_table
@source_schema = N'SCHEMA_NAME',
@source_name = N'TABLE_NAME',
@role_name = NULL
GO
  3. Start SQL Server Agent and make sure it's running at all times. See Start, stop, or restart an instance of SQL Server Agent in Microsoft documentation.
  4. At this stage, we also recommend that you check for the following entities in your source database: tables without primary keys and objects defined with EXECUTE AS DATABASE_USER statements.

    There are additional steps that you need to perform for each of these entities. Expand the following sections for more information.

    Tables that don't have primary keys or a unique index

    Database Migration Service migrates only tables that have primary keys. If your source database includes tables that don't have primary keys or a unique index, you need to manually create primary key constraints in the converted tables in the destination database at a later stage.

    There are no extra steps to perform at this moment, but we recommend you consider which approach to creating primary keys can work best for your scenario. For more information, see Migrate tables without primary keys.

    Objects defined with the EXECUTE AS statement

    Database Migration Service doesn't migrate user and server login data. If your source database contains objects defined with the EXECUTE AS DATABASE_USER clause, these objects might not function properly after the migration because the destination database won't have these users.

    You can check for such objects in your source database ahead of migration with the following SQL query: 

    SELECT s.name AS schema_name, o.name AS object_name, p.name AS user_name
FROM sys.sql_modules m  
INNER JOIN sys.objects o ON o.object_id = m.object_id
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
INNER JOIN sys.database_principals p ON p.principal_id = m.execute_as_principal_id

    If the query returns any results, you have to create the same user logins in the destination database when you Create and configure the destination Cloud SQL instance.

Configure an Amazon RDS for SQL Server database

To configure an Amazon RDS for SQL Server source instance for your migration process, do the following:

  1. Connect to your source instance with a SQL client. See Connecting to a DB instance running the Microsoft SQL Server database engine in Amazon RDS documentation.
  2. On your source instance, run the following commands:
    1. Create a dedicated migration user and grant it the db_owner and db_denydatawriter roles. 
      USE master;
CREATE LOGIN YOUR_LOGIN WITH PASSWORD = 'PASSWORD';
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
EXEC sp_addrolemember 'db_owner', 'USER_NAME';
EXEC sp_addrolemember 'db_denydatawriter', 'USER_NAME';

    2. For each database you want to migrate, enable change data capture (CDC) and snapshot isolation. A migration job can only migrate one database at a time. If you plan to create multiple migration jobs, repeat this command for every database you want to migrate. 

      EXEC msdb.dbo.rds_cdc_enable_db 'DATABASE_NAME'
ALTER DATABASE DATABASE_NAME SET ALLOW_SNAPSHOT_ISOLATION ON;
    3. For each database, enable CDC on all tables you want to migrate. Execute this command separately for each table. 
      USE [DATABASE_NAME]
EXEC sys.sp_cdc_enable_table
@source_schema = N'SCHEMA_NAME',
@source_name = N'TABLE_NAME',
@role_name = NULL
GO
  3. Database Migration Service requires that SQL Server Agent is running at all times in your source instance. Amazon RDS databases run SQL Server Agent by default, so you don't have to configure anything. See Use SQL Server Agent in Amazon RDS documentation.
  4. At this stage, we also recommend that you check for the following entities in your source database: tables without primary keys and objects defined with EXECUTE AS DATABASE_USER statements.

    There are additional steps that you need to perform for each of these entities. Expand the following sections for more information.

    Tables that don't have primary keys or a unique index

    Database Migration Service migrates only tables that have primary keys. If your source database includes tables that don't have primary keys or a unique index, you need to manually create primary key constraints in the converted tables in the destination database at a later stage.

    There are no extra steps to perform at this moment, but we recommend you consider which approach to creating primary keys can work best for your scenario. For more information, see Migrate tables without primary keys.

    Objects defined with the EXECUTE AS statement

    Database Migration Service doesn't migrate user and server login data. If your source database contains objects defined with the EXECUTE AS DATABASE_USER clause, these objects might not function properly after the migration because the destination database won't have these users.

    You can check for such objects in your source database ahead of migration with the following SQL query: 

    SELECT s.name AS schema_name, o.name AS object_name, p.name AS user_name
FROM sys.sql_modules m  
INNER JOIN sys.objects o ON o.object_id = m.object_id
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
INNER JOIN sys.database_principals p ON p.principal_id = m.execute_as_principal_id

    If the query returns any results, you have to create the same user logins in the destination database when you Create and configure the destination Cloud SQL instance.

Configure a Microsoft Azure database

To configure a Microsoft Azure SQL Managed Instance or Microsoft Azure SQL Database source instance for your migration process, do the following:

  1. Connect to your source instance with a SQL client, for example the sqlcmd utility, Azure Data Studio, or SQL Server Management Studio.
  2. At the SQL prompt, run the following commands:
    1. Create a dedicated migration user and grant it the db_owner and db_denydatawriter roles. 
      USE master;
CREATE LOGIN YOUR_LOGIN WITH PASSWORD = 'PASSWORD';
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
EXEC sp_addrolemember 'db_owner', 'USER_NAME';
EXEC sp_addrolemember 'db_denydatawriter', 'USER_NAME';

    2. For each database you want to migrate, enable change data capture (CDC) and snapshot isolation. A migration job can only migrate one database at a time. If you plan to create multiple migration jobs, repeat this command for every database you want to migrate. 

      EXEC sys.sp_cdc_enable_db;
ALTER DATABASE DATABASE_NAME SET ALLOW_SNAPSHOT_ISOLATION ON;
    3. For each database, enable CDC on all tables you want to migrate. Execute this command separately for each table. 
      EXEC sys.sp_cdc_enable_table
@source_schema = N'SCHEMA_NAME',
@source_name = N'TABLE_NAME',
@role_name = NULL
GO
  3. Start SQL Server Agent and make sure it's running at all times. For more information on SQL Server Agent, see the following pages in Microsoft documentation:
  4. At this stage, we also recommend that you check for the following entities in your source database: tables without primary keys and objects defined with EXECUTE AS DATABASE_USER statements.

    There are additional steps that you need to perform for each of these entities. Expand the following sections for more information.

    Tables that don't have primary keys or a unique index

    Database Migration Service migrates only tables that have primary keys. If your source database includes tables that don't have primary keys or a unique index, you need to manually create primary key constraints in the converted tables in the destination database at a later stage.

    There are no extra steps to perform at this moment, but we recommend you consider which approach to creating primary keys can work best for your scenario. For more information, see Migrate tables without primary keys.

    Objects defined with the EXECUTE AS statement

    Database Migration Service doesn't migrate user and server login data. If your source database contains objects defined with the EXECUTE AS DATABASE_USER clause, these objects might not function properly after the migration because the destination database won't have these users.

    You can check for such objects in your source database ahead of migration with the following SQL query: 

    SELECT s.name AS schema_name, o.name AS object_name, p.name AS user_name
FROM sys.sql_modules m  
INNER JOIN sys.objects o ON o.object_id = m.object_id
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
INNER JOIN sys.database_principals p ON p.principal_id = m.execute_as_principal_id

    If the query returns any results, you have to create the same user logins in the destination database when you Create and configure the destination Cloud SQL instance.

What's next