Connect to Microsoft SQL Server

The SQLServer Source enables Drasi connectivity to Microsoft SQL Server databases.

Data Model

The SQL Source translates the relational data from change events to more closely resemble property graph data change events so that they can be processed by subscribed Continuous Queries. To achieve this, it represents table rows as graph Nodes, as follows:

• Each row gets represented as a Node with the table columns as properties of the Node.
• The Node is assigned an id the is a composite of the table id and the row’s primary key. This is Node metadata, not a property of the Node.
• The name of the table is assigned as a Label of the Node.

The SQL Server Source does not interpret foreign keys or joins from the relational source, instead relying on the Source Join feature provided by Continuous Queries to mimic graph-style Relations between Nodes based on the values of specified properties. See the Source Joins topic in the Continuous Queries section for details.

Requirements

On the computer from where you will create the Source, you need the following software:

• Change data capture must be enabled on the database and each table you wish to observe. See the documentation on configuring SQL Server for CDC.
• Drasi CLI
• Azure CLI (If using Azure Managed Identities)

Creating the Source

To create a Source, execute the drasi apply command as follows:

drasi apply -f my-source.yaml

The drasi apply command is how you create all new Drasi resources (in this case a Source). The -f flag specifies that the definition of the new Source is contained in the referenced YAML file my-source.yaml.

Source Definitions

The YAML file passed to drasi apply can contain one or more Source definitions. Here is an example of a SQL Server source definition:

apiVersion: v1
kind: Source
name: my-source
spec:
  kind: SQLServer
  properties:
    host: <SQL Server host name>
    port: 1433
    user: user
    password: password
    database: database
    encrypt: true
    trustServerCertificate: false
    tables:
      - dbo.Table1
      - dbo.Table2

In the Source resource definition:

• apiVersion must be v1
• kind must be Source
• name is the id of the Source and must be unique. This id is used in a Continuous Query definitions to identify which Sources the Continuous Query subscribes to for change events.
• spec.kind must be SQLServer

The following table describes the SQL Server specific properties:

Secret Configuration

It is best practice to store the sensitive values, such as passwords in a secret.

kubectl create secret generic sql-credentials --from-literal=password=xxxxx -n drasi-system

You can then reference the secret when you create a SQLServer source as follows:

apiVersion: v1
kind: Source
name: my-source
spec:
  kind: SQLServer
  properties:
    host: <SQL Server host name>
    port: 1433
    user: user
    password:
      kind: Secret
      name: sql-credentials
      key: password
    database: database
    encrypt: true
    trustServerCertificate: false
    tables:
      - dbo.Table1
      - dbo.Table2

Inspecting the Source

You can check the status of the Source using the drasi list command:

drasi list source

This will return a simple list of all Sources in the current namespace and their overall status. For example:

        ID          | AVAILABLE
--------------------+------------
  my-source         | true

If an error has occurred during the creation or operation of a Source, the AVAILABLE column will contain the error text instead of true or false.

For more details about the Source you can use the drasi describe command:

drasi describe source my-source

This will return the full definition used to create the Source along with more detailed status information.

Modifying the Source

To modify the Source, you can simply use the drasi apply command again with the same source name that you used before.

Deleting the Source

To delete a Source you use the drasi delete command. There are two ways to do this. Firstly, you can specify the type of resource (Source) and its name, for example:

drasi delete source my-source

Secondly, you can refer to the YAML file(s) that contain the definitions used to create the Source(s):

drasi delete -f my-source.yaml <file2.yaml> <file3.yaml> <...>

This is a convenience, especially if a single YAML file contains multiple Source definitions.