Dataphin:Create Microsoft SQL Server Data Source

Permission Description

Only custom global roles with the New Data Source Permission Point and roles such as Super Administrator, Data Source Administrator, Section Architect, and Project Administrator can create data sources.

Procedure

1. On the Dataphin home page, in the top menu bar, select Management Center > Datasource Management.

2. On the Datasource page, click + New Data Source.

3. In the New Data Source dialog box, in the Relational Database area, select Microsoft SQL Server.

   If you have recently used Microsoft SQL Server, you can also select it in the Recently Used area. Alternatively, enter the keyword for Microsoft SQL Server in the search box for a quick search.

4. In the New Microsoft SQL Server Data Source dialog box, configure the connection data source parameters.

   1. Configure the basic information of the data source.

      Parameter	Description
      Datasource Name	Enter the name of the data source. The naming conventions are as follows: Can only contain Chinese characters, uppercase and lowercase English letters, numbers, underscores (_), or hyphens (-). Cannot exceed 64 characters in length.
      Datasource Code	After configuring the datasource code, you can directly access the Dataphin data source table in Flink_SQL tasks or using the Dataphin JDBC client through the format datasource code.table name or datasource code.schema.table name for quick consumption. If you need to automatically switch data sources based on the task execution environment, access it through the variable format ${datasource code}.table or ${datasource code}.schema.table. For more information, see Dataphin Data Source Table Development Method. Important Once the data source encoding is successfully configured, it cannot be modified. After the data source encoding is successfully configured, data preview can be performed on the object details page of the asset directory and asset checklist. In Flink SQL, currently only MySQL, Hologres, MaxCompute, Oracle, StarRocks, Hive, and SelectDB data sources are supported.
      Datasource Description	A brief description of the data source. Must not exceed 128 characters.
      Datasource Configuration	Select the data source to configure: If the business data source distinguishes between production data source and development data source, select Production + Development Data Source. If the business data source does not distinguish between production data source and development data source, select Production Data Source.
      Tag	You can classify and tag the data source based on tags. For information on how to create tags, see Manage Data Source Tags.

   2. Configure the connection parameters between the data source and Dataphin.

      If your data source configuration selects Production + Development Data Source, you need to configure the connection information for Production + Development Data Source. If your data source configuration is Production Data Source, you only need to configure the connection information for Production Data Source.

      Note

      Typically, production and development data sources should be separate to maintain environment isolation and minimize the impact of development activities on production. However, Dataphin allows them to be configured identically, using the same parameter values.

      Configuration Method can be either JDBC URL or Host, with JDBC URL being the default.

      JDBC URL Configuration Method

      Parameter	Description
      JDBC URL	The format of the connection address is jdbc:sqlserver://host:port/dbname.
      Schema	Please fill in the Schema associated with the username.
      Username, Password	Enter the username and password for the target database.

      Host Configuration Method

      • Host Configuration Method

        Parameter	Description
        Server Address	Enter the IP address and port number of the server. Supports clicking + Add to add multiple sets of IP addresses and port numbers. Click the icon to delete redundant IP addresses and port numbers, but at least one set must be retained.
        dbname	Enter the database name.

      • Parameter Configuration

        Parameter	Description
        Parameter	Parameter Name: Supports selecting an existing parameter name or entering a custom parameter name. Custom parameter names only support uppercase and lowercase English letters, numbers, half-width periods (.), underscores (_), and hyphens (-). Parameter Value: When a parameter name is selected, the parameter value is required. Only supports uppercase and lowercase English letters, numbers, half-width periods (.), underscores (_), and hyphens (-), with a length not exceeding 256 characters. Note Supports adding multiple parameters by clicking + Add Parameter. Click the icon to delete redundant parameters. Up to 30 parameters can be added.
        Schema	Fill in the Schema information associated with the username.
        Username, Password	Log on to the Microsoft SQL Server instance with the username and password.

      Note

      When you select Host as the configuration method and complete the new data source, the system will generate a JDBC URL using the server's IP address and port number.

   3. Configure the advanced settings of the data source.

      Parameter	Description
      loginTimeout	The loginTimeout duration of the database (unit: seconds), default is 900 seconds (15 minutes). Note If you have a loginTimeout configuration in the JDBC URL, the loginTimeout is the timeout configured in the JDBC URL. For data sources created before Dataphin V3.11, the default loginTimeout is -1, indicating no timeout limit.
      socketTimeout	The socketTimeout duration of the database (unit: milliseconds), default is 1800000 milliseconds (30 minutes). Note If you have a socketTimeout configuration in the JDBC URL, the socketTimeout is the timeout configured in the JDBC URL. For data sources created before Dataphin V3.11, the default socketTimeout is -1, indicating no timeout limit.
      Connection Retries	If the database connection times out, it will automatically retry the connection until the set number of retries is completed. If the maximum number of retries is reached and the connection is still unsuccessful, the connection fails. Note The default number of retries is 1 time, supporting configuration of parameters between 0～10. The connection retry count will be applied by default to Offline Integration Tasks and Global Quality (the asset quality module needs to be enabled). Offline integration tasks support configuring task-level retry counts separately.

   Note

   Value Rules for Duplicate Parameters:

   • If a parameter is in the JDBC URL, Advanced Settings, and Host Configuration, the JDBC URL value prevails.

   • If a parameter is in both the JDBC URL and Advanced Settings, the JDBC URL value prevails.

   • If a parameter is in both the Advanced Settings and Host Configuration, the Advanced Settings value prevails.

5. Click Test Connection to verify the data source's connectivity with Dataphin.

   After you have configured the data source information, you can click Test Connection in the operation column to test the connectivity of either the default cluster or a registered scheduling cluster that is registered and functioning normally within Dataphin. By default, the system selects the default cluster, which cannot be deselected. If there is no resource group under the registered scheduling cluster, the connection test cannot be performed. You must first create a resource group before you can test the connection.

   • The selected cluster is only for testing network connectivity and is not used for subsequent task operations.

   • Connection tests typically complete within 2 minutes. If it times out, click the icon to view the specific reason and retry.

   • Regardless of whether the test result is Connection Failed, Connection Successful, or Succeeded With Warning, the system will record the time when the final result was generated for you.

   • If the test result shows Connection Failed, you can click the icon to see the specific reason for the failure.

   • If the test result shows Succeeded With Warning, this indicates that the application cluster is connected successfully, but there was a failure in connecting to the scheduling cluster. Consequently, the current data source is not suitable for data development and integration tasks. You can click the icon to access the log details.

     Note

     Test results for the Default Cluster show one of three connection statuses: Success with Risk, Connection Successful, or Connection Failed. However, test results for the Registered Scheduling Cluster in Dataphin indicate only two statuses: Connection Successful or Connection Failed.

   • Ensure at least one cluster can connect to the data source; otherwise, saving the data source information is not possible.

6. After a successful test, click OK to finalize the creation of the Microsoft SQL Server data source.