Microsoft SQL Server Data Driver
Information
Loads point-data from mssql databases.
The database is expected to be in a one-value-per-row format, as shown in the examples below.
| Driver Info | Detail |
|---|---|
| Driver Type: | Live and History |
| Platforms: | All |
Performance Notes
We suggest you avoid using a relational database for high-speed time series data.
If you are using an SQL database rather than a dedicated time-series database, you may need to ensure your tables contain appropriate keys.
You should have a key that includes both the Lookup Field and the Date Stamp, and vice-versa. This will make queries significantly faster, particularly if you are querying large amounts of data.
Note that for compatibility reasons, the driver does not use some of the function calls available in SQL Server 2016 and above. As such, the performance of this driver is not ideal.
Driver Settings - Live
Live drivers have the following options
| Option | Description |
|---|---|
| Host | The IP address or host name of your MSSQL server |
| Port | The TCP port to connect to |
| Database | The name of the MySQL database |
| Username | The username to login as. Must have read access to the table. |
| Password | The password to go with the username above |
| Table | The database table to query |
| Lookup Field | The field that contains the point name |
| Date Stamp | The field that contains the timestamp for the record (optional) |
| Value | The field that contains the value for the record |
| Filter | An SQL filter you might want to add to the query (optional) |
Example
We have a table in the recordings database on my.mssql.com with the following structure…
Records Table:
| PointName | PointValue |
|---|---|
| R201_TEMP | 12.5 |
| R202_TEMP | 13.2 |
| R203_TEMP | 11.9 |
Here are the options you could use to connect…
| Option | Value |
|---|---|
| Host | my.mssql.com |
| Port | 3306 |
| Database | recordings |
| Username | DBUser |
| Password | DBPassword |
| Table | Records |
| Lookup Field | PointName |
| Date Stamp | |
| Value | PointValue |
| Filter | |
Point Names
Point names are simply the value of the Lookup Field to find in the MySQL table.
Using the above example, a good point name would be R201_TEMP.
Driver Settings - History
History drivers have the following options
| Option | Description |
|---|---|
| Host | The IP address or host name of your MSSQL server |
| Port | The TCP port to connect to |
| Database | The name of the MySQL database |
| Username | The username to login as. Must have read access to the table. |
| Password | The password to go with the username above |
| Table | The database table to query |
| Lookup Field | The field that contains the point name |
| Date Stamp | The field that contains the timestamp for the record (optional) |
| Value | The field that contains the value for the record |
| Filter | An SQL filter you might want to add to the query (optional) |
| Date Format | The format the date is stored/communicated in. Almost always 'Timestamp' |
| Date Distribution | See the section on Scattered vs Synchronised Data Sources |
| Timezone | The timezone all date stamps are stored in |
Installation
Windows:
Available on the ARDI Downloads site for Windows servers.
Linux:
sudo apt-get install ardi-mssql