How can I connect to a Microsoft SQL Server database from my Streaming and/or LiveView applications?
 

Outlines the steps to configure a SQL Server JDBC data source.

First, determine the correct URL of your SQL Server database. The URL should be in the following format:

jdbc:sqlserver://<dbhost>:<dbport>;databaseName=<dbname>

.where:

• dbhost = the database hostname
• dbport = the database listen port (default is 1433)
• dbname = the database name (e.g. "master")

To quickly spin up a SQL Server db for simple testing purposes, you can use Docker..

docker pull mcr.microsoft.com/mssql/server:2017-latest
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=P@ssw0rd!' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest

This will start a local SQL Server 2017 database with database name master. For this database, you would use the following connection URL:

jdbc:sqlserver://localhost:1433;databaseName=master

Next, install a compatible mssql-jdbc driver into your Maven repository. To determine the correct driver for your SQL Server version, refer to Microsoft's guidance here. Typically, it is best to download the latest driver that is compatible with both your SQL Server database version and the JDK version used to run your Streaming application. For example:

mvn install:install-file -Dfile=mssql-jdbc-9.2.1.jre8.jar -DgroupId=com.microsoft.sqlserver -DartifactId=mssql-jdbc -Dversion=9.2.1.jre8 -Dpackaging=jar

Then add the driver as a dependency in your project's pom.xml:

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>9.2.1.jre8</version>
</dependency>

Finally, create a JDBC HOCON configuration:

configuration = {
  JDBCDataSourceGroup = {
    jdbcDataSources = {
      "mssql2017" = {
          driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
          serverURL = "jdbc:sqlserver://localhost:1433;databaseName=master"
          userName = "sa"
          password = "P@ssw0rd!"
      }
    }
  }
}

You are now configured to connect to your SQL Server data source.
 

For additional details on the JDBC HOCON configuration, refer to the Help under Home > Configuration Guide > StreamBase Configuration Types > StreamBase JDBC DataSource Configuration.