DataWorks SQL Server data synchronization capabilities-DataWorks(DataWorks)-阿里云帮助中心

The SQL Server data source lets you read data from and write data to SQL Server over a bidirectional channel. This topic describes the SQL Server data synchronization capabilities supported by DataWorks.

Supported versions

SQL Server Reader uses the com.microsoft.sqlserver sqljdbc4 4.0 driver. For driver capabilities, see official documentation. The driver supports these SQL Server versions:

Version	Supported (yes/no)
SQL Server 2016	Yes
SQL Server 2014	Yes
SQL Server 2012	Yes
PDW 2008R2 AU34	Yes
SQL Server 2008 R2	Yes
SQL Server 2008	Yes
SQL Server 2019	No
SQL Server 2018	No
Azure SQL Managed Instance	No
Azure Synapse Analytics	No
Azure SQL Database	Yes

Limits

Batch synchronization can read views.

Supported field types

For the full set of SQL Server field types, see SQL Server documentation. The following table uses SQL Server 2016 as an example to show common types and whether they are supported.

SQL Server 2016 field type	SQL Server Reader	SQL Server Writer
bigint	Supported	Supported
bit	Supported	Supported
decimal	Supported	Supported
int	Supported	Supported
money	Supported	Supported
numeric	Supported	Supported
smallint	Supported	Supported
smallmoney	Supported	Supported
tinyint	Supported	Supported
float	Supported	Supported
real	Supported	Supported
date	Supported	Supported
datetime2	Supported	Supported
datetime	Supported	Supported
datetimeoffset	Unsupported	Unsupported
smalldatetime	Supported	Supported
time	Supported	Supported
char	Supported	Supported
text	Supported	Supported
varchar	Supported	Supported
nchar	Supported	Supported
ntext	Supported	Supported
nvarchar	Supported	Supported
binary	Supported	Supported
image	Supported	Supported
varbinary	Supported	Supported
cursor	Unsupported	Unsupported
hierarchyid	Unsupported	Unsupported
sql_variant	Supported	Supported
Spatial Geometry Types	Unsupported	Unsupported
table	Unsupported	Unsupported
rowversion	Unsupported	Unsupported
uniqueidentifier	Supported	Supported
xml	Supported	Supported
Spatial Geography Types	Unsupported	Unsupported

The following table lists the SQL Server type mappings for SQL Server Reader and SQL Server Writer.

Type category	SQL Server data type
Integer	BIGINT, INT, SMALLINT, and TINYINT
Floating-point	FLOAT, DECIMAL, REAL, and NUMERIC
String	CHAR, NCHAR, NTEXT, NVARCHAR, TEXT, VARCHAR, NVARCHAR (MAX), and VARCHAR (MAX)
Date and time	DATE, DATETIME, and TIME
Boolean	BIT
Binary	BINARY, VARBINARY, VARBINARY (MAX), and TIMESTAMP

Add a data source

Before you develop a synchronization task in DataWorks, you must add the required data source to DataWorks by following the instructions in Data source configuration. You can view parameter descriptions in the DataWorks console to understand the meanings of the parameters when you add a data source.

Develop data synchronization tasks

For information about the entry point for and the procedure of configuring a synchronization task, see the following configuration guides.

Configure a single-table batch synchronization task

For the procedure, see Codeless UI configuration and Script mode configuration.
For all script mode parameters and sample scripts, see Appendix: Sample scripts and parameters.

Configure a full-database batch read synchronization task

For the procedure, see Configure real-time full-database synchronization tasks.

FAQs

Data recovery on a primary-replica setup
In a primary-replica disaster recovery setup, the replica continuously recovers data from the primary through binlogs. Replication can lag, especially because of network delays. Therefore, the replica data may differ significantly from the primary, and data synchronized from the replica may not be a complete snapshot at the current time.
Consistency constraints
SQL Server is an RDBMS and can provide strongly consistent query interfaces. For example, if another writer updates the database after a synchronization task starts, SQL Server Reader does not capture that updated data because of the database snapshot behavior.
This describes consistency under the SQL Server Reader single-thread model. Because SQL Server Reader can extract data concurrently based on your configuration, strict consistency cannot always be guaranteed.
When SQL Server Reader splits data by splitPk, it starts multiple concurrent tasks. These tasks do not share one read transaction and start at different times, so the resulting data is not a complete, consistent snapshot.
A consistent snapshot across multiple threads is not currently technically achievable. The following engineering approaches have trade-offs; choose based on your needs.
- Use single-thread synchronization without splitting the data. It is slower but provides good consistency.
- Stop other writers so that the data remains static, such as by locking the table or stopping replica synchronization. This may affect online business.
Database encoding
SQL Server Reader uses JDBC to extract data. JDBC handles different encodings and converts them internally, so you do not need to specify the encoding for SQL Server Reader.
Incremental data synchronization
SQL Server Reader extracts data with JDBC SELECT statements. Use SELECT…WHERE… for incremental extraction in these ways:
- When an online application writes data, it fills the modify field with the change timestamp for inserts, updates, and logical deletes. SQL Server Reader can use a WHERE condition after the prior synchronization timestamp.
- For append-only records, set a WHERE condition based on the largest auto-increment ID from the previous stage.
If no business field identifies inserted or modified records, SQL Server Reader cannot synchronize incrementally and must synchronize all data.
SQL security
SQL Server Reader provides querySql so that you can write a SELECT extraction query, but it does not validate querySql for security.

Appendix: Sample scripts and parameters

Configure a batch synchronization task by using the code editor

If you want to configure a batch synchronization task by using the code editor, you must configure the related parameters in the script based on the unified script format requirements. For more information, see Script mode configuration. The following information describes the parameters that you must configure for data sources when you configure a batch synchronization task by using the code editor.

Reader sample script

{
    "type":"job",
    "version":"2.0",//Version number.
    "steps":[
        {
            "stepType":"sqlserver",//Plug-in name.
            "parameter":{
                "datasource":"",//Data source.
                "column":[//Fields.
                    "id",
                    "name"
                ],
                "where":"",//Filter condition.
                "splitPk":"",//If splitPk is specified, data is split by that field.
                "table":""//Data table.
            },
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"stream",
            "parameter":{},
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"//Number of error records.
        },
        "speed":{
            "throttle":true,//When throttle is false, mbps is ignored and throughput is unlimited; when true, throughput is limited.
            "concurrent":1 //Job concurrency.
            "mbps":"12",//Rate limit. Here, 1 mbps = 1 MB/s.
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

To query with querySql, use a Reader script like the following. Here, the SQL Server data source is sql_server_source, the table is dbo.test_table, and the column is name.

{
    "stepType": "sqlserver",
    "parameter": {
        "connection": [
            {
                "querySql": ["select name from dbo.test_table"],
                "datasource": "sql_server_source"
            }
        ],
        "datasource": "sql_server_source",
        "column": ["name"],
        "where": "",
        "splitPk": "id"
    },
    "name": "Reader",
    "category": "reader"
}

Reader script parameters

Parameter	Description	Required	Default value
datasource	Data source name. In script mode, you can add a data source. The value must match the name of the added data source.	Yes	None
table	Name of the table to synchronize. A job supports only one table.	Yes	None
column	Columns to synchronize from the configured table, described as a JSON array. By default, all columns are selected, such as [ * ]. You can select only some columns for export. You can reorder columns for export instead of following the table schema. You can configure constants according to MySQL SQL syntax, for example, `["id", "table","1", "'mingya.wmy'", "'null'", "to_char(a+1)", "2.3" , "true"]` . `id` is a regular column name. `table` is a column name containing a reserved word. `1` is an integer constant. `'mingya.wmy'` is a string constant. Include the pair of single quotation marks. `'null'` is a string. `to_char(a + 1)` is a function expression. `2.3` is a floating-point number. `true` is a Boolean value. `column`Explicitly specify a nonempty set of columns to synchronize.	Yes	None
splitFactor	A split factor that sets the number of data shards. With multiple concurrent tasks, data is split into `concurrency × splitFactor` shards. For example, concurrency = 5 and splitFactor = 5 produce 5 × 5 = 25 shards, executed over five concurrent threads. Note Recommended range: 1 to 100. An excessively large value can cause an out-of-memory error.	No	5
splitPk	When SQL Server Reader extracts data, specifying `splitPk` means that you want to split the data by the splitPk field. Synchronization starts concurrent tasks to improve efficiency. We recommend using the table primary key for splitPk because primary key values are usually distributed evenly, reducing the chance of data hotspots. splitPk currently supports only integer values, not strings, floating-point numbers, dates, or other types. SQL Server Reader reports an error if you specify an unsupported type.	No	None
where	A filter condition. SQL Server Reader assembles a SQL statement from the specified `column` and `table` and `where` conditions and extracts data with that statement. For testing, you can specify limit 10 as a WHERE condition. In practice, you may synchronize the current day’s data by setting WHERE to `gmt_create > ${bizdate}`. A WHERE condition can enable incremental synchronization. An empty WHERE condition synchronizes the entire table.	No	None
querySql	Format:`"querysql" : "query statement"`In some scenarios, WHERE cannot describe the required filter. Use querySql to provide custom SQL. When querySql is configured, Data Integration ignores tables and columns and filters data directly with the query, for example, joining multiple tables with `select a,b from table_a join table_b on table_a.id = table_b.id`. When you configure `querySql`, SQL Server Reader ignores the `column` and `table` and `where` condition.	No	None
fetchSize	The number of records retrieved in each batch by the plug-in and database server. It determines the number of network interactions with Data Integration and can improve extraction performance. Note `fetchSize`An excessively large value (> 2,048) can cause an out-of-memory (OOM) error during synchronization.	No	1024
driverVersion	The SQL Server driver version. The default is 4.0. You can specify version 12.10, which supports Active Directory Service Principal authentication.	No	4.0

SQL Server Reader assembles the configured table and column and where information into a SQL statement and sends it to SQL Server.
For the configured querySql information, SQL Server Reader sends it directly to the SQL Server database.

Writer sample script

{
    "type":"job",
    "version":"2.0",//Version number.
    "steps":[
        {
            "stepType":"stream",
            "parameter":{},
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"sqlserver",//Plug-in name.
            "parameter":{
                "postSql":[],//SQL statements to run after the synchronization task.
                "datasource":"",//Data source.
                "column":[//Fields.
                    "id",
                    "name"
                ],
                "table":"",//Table name.
                "preSql":[]//SQL statements to run before the synchronization task.
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"//Number of error records.
        },
        "speed":{
            "throttle":true,//When throttle is false, mbps is ignored and throughput is unlimited; when true, throughput is limited.
            "concurrent":1, //Job concurrency.
            "mbps":"12"//Rate limit. Here, 1 mbps = 1 MB/s.
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

Writer script parameters

Parameter	Description	Required	Default value
datasource	Data source name. In script mode, you can add a data source. The value must match the name of the added data source.	Yes	None
table	Name of the table to synchronize.	Yes	None
column	Destination table fields to write, separated by commas. Example:`"column":["id","name","age"]`. To write all columns in order, use , for example, `"column":[""]`.	Yes	None
preSql	SQL statements to run before the synchronization task. Wizard mode allows one statement; script mode supports multiple statements, such as clearing old data.	No	None
postSql	SQL statements to run after the synchronization task. Wizard mode allows one statement; script mode supports multiple statements, such as adding a timestamp.	No	None
writeMode	Choose an import mode. Supported: `insert`. If a primary key or unique index conflict occurs, Data Integration treats the row as dirty data and retains the original row.	No	insert
batchSize	The number of records submitted in one batch. This greatly reduces network interactions between Data Integration and SQL Server and improves throughput. An excessively large value can cause an out-of-memory (OOM) error during synchronization.	No	1,024
driverVersion	The SQL Server driver version. The default is 4.0. You can specify version 12.10, which supports Active Directory Service Principal authentication.	No	4.0