SQL Server

Preparation and Constraints

• Network requirements

  The SQL Server data source can communicate with CDM. This ensures smooth data transmission. For details, see Enabling Network Connectivity.

• Required permissions
  • Reading SQL Server offline:
    • The account must have the read-only permission (SELECT) on the table to be synchronized so that data can be read securely and accurately. This prevents unauthorized data modification and ensures data integrity and consistency.
    • If the data integration task involves reading data from views or stored procedures, the account must have the SELECT permission.
    • The account should at least have the db_datareader role permissions so that the account can read data from all user tables in the database.
  • Writing SQL Server offline:
    • The account must have the write permission (INSERT, DELETE, and UPDATE) on the table to be synchronized so that data can be correctly written to the destination table. These permissions allow DataArts Migration to insert, update, and delete data in the destination table, ensuring the integrity and accuracy of data synchronization.
    • If a data integration task involves creating or deleting tables, the account must have the CREATE TABLE or DROP TABLE permission. This usually requires the db_ddladmin or db_owner role.
    • The account should at least have the db_datawriter role so that the account can write all user tables in the database. The db_ddladmin or db_owner role is recommended for modifying or creating table structures.
  • Notes:
    • In some complex data migration scenarios, especially those involving cross-database operations, dynamic SQL execution, or advanced configurations, the sysadmin permission may be required. However, for security purposes, you are advised to grant this permission only when necessary and revoke it immediately after the task is complete.
    • The account must have the CONNECT permission to connect to the SQL Server instance. This permission is usually granted to all users by default. However, if there are restrictions, ensure that the account has this permission.
• Recommended versions

  Recommended Microsoft SQL Server version: 2005 and later

Driver Selection

Supported Data Types

DataArts Migration supports the following field types and their common variants in SQL Server 2016. This ensures that DataArts Migration can correctly read and write data.

Supported Migration Scenarios

DataArts Migration supports the following offline synchronization modes:

• Single table synchronization

  DataArts Migration supports table/file synchronization in data ingestion into a data lake or data migration to the cloud.

• Database and table shard synchronization

  DataArts Migration supports synchronization of data from multiple databases and tables in data ingestion into a data lake or data migration to the cloud.

• Entire DB migration

  DataArts Migrations supports synchronization of data from an on-premises database in data ingestion into a data lake or data migration to the cloud.

DataArts Migration supports synchronization of data from an on-premises database to the cloud. For details about the supported data source types, see the data source types supported by entire database synchronization.

Database and table shard synchronization and entire DB migration are not supported in all regions. The following table lists the supported SQL Server migration scenarios.

Core Capabilities

• Connection configuration

  Configuration Item	Supported	Description
  User/AK	√	User authentication ensures connection security.
  SSL encryption	√	SSL encryption ensures secure data transmission. Currently, SSL authentication can be enabled only for RDS.
  SSL authentication	x	By default, certificate authentication is disabled.
  Private certificate	x	Private certificates are not supported.
  Connection configuration optimization	√	Connection configuration such as connectTimeout can be optimized to improve connection performance.
  Custom driver	√	Custom drivers are supported and provide better flexibility.

• Read capabilities

  Configuration Item	Supported	Description
  Shard concurrency	√	Horizontal sharding based on primary keys or common fields and multi-thread concurrent extraction significantly improve the throughput and efficiency.
  Dirty data processing	√	Abnormal data can be written to the dirty data bucket to prevent job failures caused by a small amount of abnormal data.
  Custom fields	√	You can add computed columns, constant columns, or masking functions for tasks to meet personalized service requirements.
  Incremental read	√	Where conditions and the SQL mode enable incremental data reading.
  Stream and batch reading	Batch reading	Batch reading improves efficiency when there is a small or medium amount of data.
  Optimization of the number of rows read	√	You can set Fetch Size in the connection to properly control the amount of data to be transmitted. This improves performance and prevents a transmission delay or the system from being overloaded when there is a large amount of data.

• Write capabilities

  Configuration Item	Supported	Description
  Data source optimization parameters	√	Optimization parameters such as batchSize and socketTimeout are supported at the source. They improve write performance.
  Dirty data processing	√	Abnormal data can be written to the dirty data bucket to prevent job failures caused by a small amount of abnormal data.
  Conflict resolution	x	The conflict resolution mechanism is not supported.
  Pre- and post-import processing	√	Operations such as preSql and delete can clean and process data before and after data import.
  Concurrent write	√	Concurrent write improves efficiency.
  Optimization of the number of written rows	√	You can set the number of rows written by each request in the connection to properly control the amount of data to be transmitted. This improves performance and prevents a transmission delay or the system from being overloaded when there is a large amount of data. This function is not supported for this data source.

Creating a Data Source

Create a data source in Management Center. For details, see Configuring Data Connection Parameters.

Creating an Offline Data Migration Job

Create a SQL Server migration job in DataArts Factory. For details, see Creating an Offline Processing Migration Job.

Best Practices

Incremental Migration of Relational Databases