Skip to main content
Version: v4

Microsoft SQL Server

Step 1: Create a data source​

There are two ways to create a data source: click the "+" button next to Data Source in the sidebar, or click the Create a Data Source button on the Overview page.

1_sql_server

Step 2: Configure the connection​

In the dialog form, fill in the connection information and click Submit.

3_sql_server

The connection fields are described below.

Basic​

  • Name: The display name of the database in Canner Enterprise. You can change it later.
  • Data Source Type: The database type. Select SQL Server.

Data Source Properties​

  • URL: The database address, in the format <hostname>:<port>.

  • Username: The username used to connect to this SQL Server database.

  • Password: The password used to connect to this SQL Server database.

  • Database Name: The name of the database to connect to.

  • Version: Your SQL Server version.

    1. Others (default): SQL Server versions later than 2008 R2 (excluding 2008 R2).
    2. SQL Server 2008 R2: SQL Server 2008 R2 or earlier. Because Microsoft has ended support for this version, some syntax may not be supported in Canner Enterprise. We recommend not using this SQL Server version unless necessary.

  • Case Sensitive: Table names in Canner Enterprise data sources are displayed in lowercase, and case is not distinguished. Choose how to handle multiple tables whose names differ only in case.

    1. One Only (default): Multiple tables whose names differ only in case are not supported, so the system shows a connection error after you submit.
    2. First Result: The first table scanned is kept; other tables with the same name are not connected.
    3. Lowercase First: The first table scanned with an all-lowercase name is kept; other tables with the same name are not connected (if none is all lowercase, the first table scanned is kept).
    4. Uppercase First: The first table scanned with an all-uppercase name is kept; other tables with the same name are not connected (if none is all uppercase, the first table scanned is kept).
    Example: the database you connect to has three tables named Table, table, and TABLE
    1. One Only - after submitting, the connection fails.
    2. First Result - the Table table is kept.
    3. Lowercase First - the table table is kept.
    4. Uppercase First - the TABLE table is kept.

  • Table Pattern: A Java regular expression to connect only the tables that match. We recommend setting this in the following three situations:

    1. You only want to connect tables matching a specific pattern to Canner Enterprise.
    2. You want to manage one source database as multiple data sources in Canner Enterprise.
    3. The number of tables in the database exceeds the system limit, so you need to split it into multiple data sources.
    Three Table Pattern examples:
    Example 1: ^p.*\..* connects only the tables in schemas starting with p.
    Example 2: public\..* connects only the tables in schemas starting with public.
    Example 3: (public\..*)|(testing\..*) connects the tables in schemas starting with public or testing.
info

Table names in Canner Enterprise data sources are displayed in lowercase and case is not distinguished, so Table Pattern only supports lowercase conditions. If your Java regular expression contains uppercase characters, they are automatically converted to lowercase.

  • Join Pushdown (off by default): Pushes JOIN conditions in SQL down to the source SQL Server database for execution.
info

Enabling Join Pushdown can improve SQL query performance, but note that:

  1. Whether it improves performance depends on the JOIN conditions in your queries
  2. It uses more resources on the source SQL Server database than when it is off

We recommend considering both points before enabling it

  • Snapshot Isolation Disabled: If Snapshot Isolation is turned off in your SQL Server database, turn this option on.
  • Explore Timeout: The timeout for connecting to the data source. If connecting takes longer than this, the system stops and shows a connection error. The default timeout is 5000 ms. If the database you are connecting to has many tables, consider increasing this timeout.

Step 3: Finish​

After you submit, the Microsoft SQL Server data source appears in the sidebar shortly. Click it to open the data source details page.

4_sql_server