DocsICP FilingConsole
官方文档
首页

Integrate data from SQL Server to OSS

更新时间：
复制 MD 格式
产品详情
我的收藏

This tutorial walks you through configuring a Data Management (DMS) data integration task that exports a table from a SQL Server database into an Object Storage Service (OSS) bucket.

Important

Confirm the following hard constraints before proceeding:

  • Public preview only — the Service-Level Agreement (SLA) does not apply during this period.

  • Singapore region only — the data integration task must run in the Singapore region.

  • One table per task — each task integrates a single table. Run multiple tasks to integrate multiple tables.

  • Full-load export only — data integration tasks do not support incremental sync. For real-time or incremental sync, use Data Transmission Service (DTS).

What you'll do

This tutorial has two phases:

Preparations — create the supporting resources:

  1. Create an OSS bucket and directory

  2. Create a RAM user and grant permissions

  3. (Optional) Purchase and configure a KMS instance for data encryption

  4. (Optional) Purchase and configure an AnalyticDB MySQL 3.0 cluster for metadata storage

Procedure — configure the task in DMS:

  1. Register the SQL Server database

  2. Register the OSS instance

  3. (Optional) Register the AnalyticDB MySQL 3.0 cluster

  4. Configure and run the data integration task

Prerequisites

Before you begin, make sure you have:

  • A SQL Server database accessible over the Internet

  • The CIDR blocks of Data Transmission Service (DTS) and DMS servers added to the database security settings (security group rules, firewall policies, and IP whitelists) — see Add the CIDR blocks of DTS servers and Add DMS IP addresses and CIDR blocks to security settings

  • All resources (OSS bucket, KMS instance, AnalyticDB MySQL 3.0 cluster, RAM user, and DMS tenant) owned by the same Alibaba Cloud account

Preparations

Create an OSS bucket

  1. Log on to the OSS console.

  2. In the left-side navigation pane, click Buckets, then click Create Bucket.

  3. Configure the bucket parameters:

    • Bucket Name: Enter a name for the bucket.

    • Region: Select the Singapore region.

    • ACL: Select Private.

    • Configure other parameters based on your requirements.

    For more information, see Create a bucket.

  4. Click OK.

  5. Create a directory in the bucket:

    1. In the left-side navigation pane, click Buckets. Find and click the bucket you created.

    2. In the left-side navigation tree, choose Object Management > Objects.

    3. On the Objects page, click Create Directory.

    4. In the Create Directory panel, enter a value for Directory Name. > Note: For directory naming requirements, see Manage directories.

    5. Click OK.

Create a RAM user and grant permissions

  1. On the Users page, click Create User and configure the following:

    • Logon Name: Enter a logon name, for example, dts_testuser.

    • Access Mode: Select Using permanent AccessKey to access.

  2. Click OK. Record the AccessKey ID and AccessKey secret.

  3. Grant permissions to the RAM user:

    1. On the Users page, find the RAM user and click Add Permissions in the Actions column.

    2. Select the policies you need:

      Policy

      Description

      AliyunDTSFullAccess

      Permissions to manage DTS

      AliyunOSSFullAccess

      Permissions to manage OSS

      AliyunKMSReadOnlyAccess

      (Optional) Read-only permission on KMS

      AliyunKMSSecretUserAccess

      (Optional) Permission to obtain secrets from KMS

      AliyunKMSCryptoUserAccess

      (Optional) Permission to use KMS keys for cryptographic operations
      Note

      Instead of AliyunOSSFullAccess, you can grant AliyunOSSReadOnlyAccess and then configure fine-grained access on the Bucket Policy tab in the OSS console. Set Applied To to Whole Bucket, Authorized User to RAM User, and Authorized Operation to Full Access or Read/Write. See Bucket policies.

    3. Click Grant permissions, then click Close.

(Optional) Purchase and configure a KMS instance

Complete this section only if you want to encrypt the integrated data with Key Management Service (KMS).

Purchase a KMS instance

  1. Log on to the KMS console and select the Singapore region.

  2. In the left-side navigation pane, choose Resource > Instances.

  3. On the Instances page, click Create Instance and configure the following:

    • Key Management Type: Select Software Key Management.

    • Region: Select the Singapore region.

    • Configure other parameters based on your requirements. See Purchase and enable a KMS instance.

  4. Click Buy Now and complete the purchase.

Enable the KMS instance

  1. Return to the Instances page.

  2. On the Software Key Management tab, find the instance you purchased.

  3. Click Enable in the Actions column and complete the configuration. See Enable the KMS instance.

  4. Wait until the instance status changes to enabled.

Create a customer master key (CMK)

  1. In the left-side navigation pane, choose Resource > Keys.

  2. Click the Keys tab. On the right side of Create Key, set Instance ID to the KMS instance you created.

  3. Click Create Key and configure the following:

    • KMS Instance: Select the KMS instance you purchased.

    • Key Type: Select Symmetric Key.

    • Key Specifications: Select Aliyun_AES_256.

    • Configure other parameters based on your requirements. See Create a CMK.

  4. Click OK.

Create a generic secret

  1. In the left-side navigation pane, choose Resource > Secrets.

  2. Click the Generic Secrets tab. On the right side of Create Secret, set Instance ID to the KMS instance you created.

  3. Click Create Secret and configure the following:

    • Secret Value: Select Plain Text and enter a secret value in the Plain Text field. > Note: Run openssl rand 32 | base64 in an environment with OpenSSL installed to generate a random secret value.

    • CMK: Select the key you created in the previous step.

    • Configure other parameters based on your requirements. See Manage and use generic secrets.

  4. Click OK.

Create an application access policy

  1. In the left-side navigation pane, choose Application Access > AAPs.

  2. Click the Policy tab, then click Create Policy.

  3. Configure the following:

    • Scope: Select the KMS instance you created.

    • RBAC Permissions: Select all available options.

    • Accessible Resources: Select the CMK and generic secret you created.

    • Configure other parameters based on your requirements. See Create an AAP.

  4. Click OK.

(Optional) Purchase and configure an AnalyticDB MySQL 3.0 cluster

Complete this section only if you want to store OSS metadata in an AnalyticDB MySQL 3.0 cluster.

  1. Purchase an AnalyticDB MySQL 3.0 cluster. See Create a cluster.

  2. Create a privileged account. See Create a database account.

  3. Create a resource group for the cluster. Set Task Type to Interactive and Engine to Spark — this lets you query the integrated data after the task runs. See Create a resource group.

Procedure

Step 1: Register the SQL Server database

  1. Log on to the DMS console V5.0.

  2. Click the add icon next to Database Instances in the left-side navigation pane.

    Alternatively, choose Data Assets > Instances in the top navigation bar. On the Instance List tab, click New.

  3. In the Add Instance dialog box, click the Third-party Cloud/Self-managed tab.

  4. In the Relational Database section, click SQLServer and configure the following parameters:

    Configure other parameters based on your requirements. See Register a database hosted on a third-party cloud service or a self-managed database.
    Parameter Description
    Database Type Select SQLServer
    Instance Source Select Internet
    Instance Region Select the region where the SQL Server database resides
    Login Address Enter the public endpoint of the SQL Server database
    Port Enter the service port of the SQL Server database
    Database Account Enter the account used to log on to SQL Server. The account must have SELECT permission on the data to be integrated
    Database Password Enter the password for the database account

  5. Click Submit.

Step 2: Register the OSS instance

  1. Log on to the DMS console V5.0.

  2. Click the add icon next to Database Instances in the left-side navigation pane.

    Alternatively, choose Data Assets > Instances in the top navigation bar. On the Instance List tab, click New.

  3. In the Add Instance dialog box, click the Alibaba Cloud tab.

  4. In the File and Log section, select OSS and configure the following parameters:

    Configure other parameters based on your requirements. See Register an Alibaba Cloud database instance.
    Parameter Description
    File and Log Storage Select OSS
    Instance Region Select the region of the OSS bucket you created
    Connection Method Select Connection String Address
    Connection String Address DMS automatically populates this field based on the selected region. For example, if you set the Instance Region parameter to China (Hangzhou), the Connection String Address parameter is automatically set to oss-cn-hangzhou.aliyuncs.com
    Bucket (Optional) Select the bucket you created
    AccessKey ID Enter the AccessKey ID of the RAM user you created
    AccessKey secret Enter the AccessKey secret of the RAM user you created

  5. Click Submit.

Step 3: Register the AnalyticDB MySQL 3.0 cluster (optional)

Complete this step only if you want to store OSS metadata in the AnalyticDB MySQL 3.0 cluster.

  1. Log on to the DMS console V5.0.

  2. Click the add icon next to Database Instances in the left-side navigation pane.

    Alternatively, choose Data Assets > Instances in the top navigation bar. On the Instance List tab, click New.

  3. In the Add Instance dialog box, click the Alibaba Cloud tab.

  4. In the Data Warehouse section, select AnalyticDB MySQL 3.0 and configure the following parameters:

    Configure other parameters based on your requirements. See Register an Alibaba Cloud database instance.
    Parameter Description
    Database Type Select AnalyticDB MySQL 3.0
    Instance Region Select the region where the cluster resides
    Connection Method Select Instance ID
    Instance ID Select the ID of the AnalyticDB MySQL 3.0 cluster
    Database Account Enter the privileged account of the cluster
    Database Password Enter the password for the account

  5. Click Submit.

Step 4: Configure and run the data integration task

Create a task flow

  1. Log on to the DMS console V5.0.

  2. In the top navigation bar, choose Data+AI > Data+AI > Task Orchestration.

    In simple mode, hover over the 2023-01-28_15-57-17.png icon in the upper-left corner and choose All Features > Data+AI > Data Development > Task Orchestration.

  3. Click Create Task Flow. In the dialog box, set the Business Scenario and Task Flow Name, then click OK.

Configure the data integration task

  1. In the Task Type list on the left, drag the Data Lake Data Integration

  2. Double-click the node on the canvas.

  3. Configure the source and destination databases:

    Section Parameter Description
    Source Database Database Type Select SQL Server
    Access Method Public IP Address (fixed)
    Instance Region Select the region of the SQL Server instance registered in DMS
    DMS Database Instance Select the SQL Server instance registered in DMS
    Destination Database Database Type OSS (fixed)
    Access Method Alibaba Cloud Instance (fixed)
    Instance Region Select the region of the OSS instance registered in DMS
    DMS Database Instance Select the OSS instance registered in DMS
    OSS Bucket Select the bucket you created

  4. Click Next Step: Configure Objects.

  5. Configure the objects to migrate:

    You can select only one table.
    Configuration Description
    Destination OSS Database Integration Format DELTA (fixed)
    Destination OSS Database Path Enter the directory you created in the bucket
    Storage location of destination OSS metadata Select where to store metadata (databases, tables, columns): DMS stores metadata in DMS; AnalyticDB MySQL 3.0 stores metadata in the cluster (select the registered cluster and enter the RAM User ID from the RAM User ID page of the cluster); None skips metadata storage
    Source Objects Select a table from the list and click the Right arrow icon to add it to Selected Objects
    Selected Objects Data is stored at /<Destination OSS database path>/<Table name> by default. Right-click the table to open the Edit Table dialog box and change the subdirectory, add filter conditions, or configure KMS encryption. See Parameters and descriptions

  6. Click OK.

  7. Click Next Step: Complete.

Run the task and verify results

  1. In the upper-left corner of the page, click Try Run.

  2. Click the Execution Logs tab at the bottom of the canvas to monitor progress:

    • status SUCCEEDED — the task completed successfully.

    • status FAILED — the task failed. Review the error in the Execution Logs tab, correct the configuration, and run the task again.

  3. Verify the output in OSS: image

    1. Log on to the OSS console.

    2. In the left-side navigation pane, click Buckets. Search for and click the bucket you created.

    3. Navigate to the /<Destination OSS database path>/<Table name> directory to confirm the integrated data is present.

    <Destination OSS database path> is the Destination OSS Database Path you configured, and <Table name> is the value of Table Name in the Edit Table dialog box.

Parameters and descriptions

The following parameters are available in the Edit Table dialog box (right-click a table in Selected Objects).

Parameter Description
Schema Name Not required in this example
Table Name The subdirectory mapped to the data in the OSS bucket
Filter Conditions Not required in this example. See Specify filter conditions
Partition Information Not required in this example. Select one or more columns as the partition key for the destination
Enable KMS Encryption Whether to use KMS to encrypt the data. If set to Yes, configure the following KMS parameters
Enable Footer Encryption Whether to encrypt the footer (column name metadata) in the OSS bucket
KMS Instance ID The KMS instance you created in the Preparations phase
KMS Encrypted Credential ID The generic secret you created in the Preparations phase
KMS Encryption Algorithm The KMS encryption algorithm to use
Is Encrypt Column Whether to encrypt data in a specific column

FAQ

How do I register a RestAPI data source in DMS?

In the Add Instance dialog box, click the Third-party Cloud/Self-managed tab. In the Big data section, click RestApi.

In the Add Instance dialog box, click the Third-party Cloud/Self-managed tab. In the Big data section, click BigQuery.

Does a data integration task support incremental data sync?

No. Data integration tasks do not support synchronization of incremental data.

Previous：Integrate data from Oracle to OSSNext：Integrate data from a MongoDB replica set database to OSS