Configure your source SQL Server database for continuous migrations

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

• Cloud SQL for SQL Server
• Self-hosted SQL Server
• Amazon RDS for SQL Server
• Microsoft Azure sources: Microsoft Azure SQL Managed Instance and Microsoft Azure SQL Database

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_datareader and db_denydatawriter roles to the migration user you created in step 1.

      EXECsp_addrolemember'db_datareader','USER_NAME';
      EXECsp_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.

      The database user account you use to enable CDC must have the db_owner role. Execute the following commands:

      EXECmsdb.dbo.gcloudsql_cdc_enable_db'DATABASE_NAME';
      ALTERDATABASEDATABASE_NAMESETALLOW_SNAPSHOT_ISOLATIONON;

   3. For each database, enable CDC on all tables you want to migrate. The database user account you use to enable CDC must have the db_owner role. Execute this command separately for each table:

      USE[DATABASE_NAME]
      EXECsys.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 objects defined with EXECUTE AS DATABASE_USER statements.

   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:

   SELECTs.nameASschema_name,o.nameASobject_name,p.nameASuser_name
   FROMsys.sql_modulesm
   INNERJOINsys.objectsoONo.object_id=m.object_id
   INNERJOINsys.schemassONs.schema_id=o.schema_id
   INNERJOINsys.database_principalspONp.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_datareader and db_denydatawriter roles.

      USEmaster;
      CREATELOGINYOUR_LOGINWITHPASSWORD='PASSWORD';
      CREATEUSERUSER_NAMEFORLOGINYOUR_LOGIN;
      EXECsp_addrolemember'db_datareader','USER_NAME';
      EXECsp_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.

      The database user account you use to enable CDC must have the db_owner role. Execute the following commands:

      USE[DATABASE_NAME]
      GO
      EXECsys.sp_cdc_enable_db
      GO

   3. For each database, enable CDC on all tables you want to migrate. The database user account you use to enable CDC must have the db_owner role. Execute this command separately for each table:

      USE[DATABASE_NAME]
      EXECsys.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 objects defined with EXECUTE AS DATABASE_USER statements.

   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:

   SELECTs.nameASschema_name,o.nameASobject_name,p.nameASuser_name
   FROMsys.sql_modulesm
   INNERJOINsys.objectsoONo.object_id=m.object_id
   INNERJOINsys.schemassONs.schema_id=o.schema_id
   INNERJOINsys.database_principalspONp.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_datareader and db_denydatawriter roles.

      USEmaster;
      CREATELOGINYOUR_LOGINWITHPASSWORD='PASSWORD';
      CREATEUSERUSER_NAMEFORLOGINYOUR_LOGIN;
      EXECsp_addrolemember'db_datareader','USER_NAME';
      EXECsp_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.

      The database user account you use to enable CDC must have the db_owner role. Execute the following commands:

      EXECmsdb.dbo.rds_cdc_enable_db'DATABASE_NAME'
      ALTERDATABASEDATABASE_NAMESETALLOW_SNAPSHOT_ISOLATIONON;

   3. For each database, enable CDC on all tables you want to migrate. The database user account you use to enable CDC must have the db_owner role. Execute this command separately for each table:

      USE[DATABASE_NAME]
      EXECsys.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 objects defined with EXECUTE AS DATABASE_USER statements.

   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:

   SELECTs.nameASschema_name,o.nameASobject_name,p.nameASuser_name
   FROMsys.sql_modulesm
   INNERJOINsys.objectsoONo.object_id=m.object_id
   INNERJOINsys.schemassONs.schema_id=o.schema_id
   INNERJOINsys.database_principalspONp.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_datareader and db_denydatawriter roles.

      USEmaster;
      CREATELOGINYOUR_LOGINWITHPASSWORD='PASSWORD';
      CREATEUSERUSER_NAMEFORLOGINYOUR_LOGIN;
      EXECsp_addrolemember'db_datareader','USER_NAME';
      EXECsp_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.

      The database user account you use to enable CDC must have the db_owner role. Execute the following commands:

      EXECsys.sp_cdc_enable_db;
      ALTERDATABASEDATABASE_NAMESETALLOW_SNAPSHOT_ISOLATIONON;

   3. For each database, enable CDC on all tables you want to migrate. The database user account you use to enable CDC must have the db_owner role. Execute this command separately for each table:

      EXECsys.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:
   • SQL Server Agent.
   • Start, stop, or restart an instance of SQL Server Agent.

4. At this stage, we also recommend that you check for objects defined with EXECUTE AS DATABASE_USER statements.

   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:

   SELECTs.nameASschema_name,o.nameASobject_name,p.nameASuser_name
   FROMsys.sql_modulesm
   INNERJOINsys.objectsoONo.object_id=m.object_id
   INNERJOINsys.schemassONs.schema_id=o.schema_id
   INNERJOINsys.database_principalspONp.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

• Learn about source database network connectivity. See Source database networking methods.

• To get a complete, step-by-step migration walkthrough, see SQL Server to Cloud SQL for PostgreSQL migration guide.