Microsoft SQL Server Integration
Microsoft SQL Server is a free, proprietary and relational database management system from Microsoft. It is widely used and well known for simple integration into Microsoft technology. Microsoft SQL Server integrates easily with Row64 by wiring to Row64 RamDb through Python.
Integration Overview
SQL Server integration is primarily completed on the Windows platform, but as a last step, the data is moved to Linux for Dashboard Evaluation. This method simplifies Windows Authentication, and allows Windows Server Administrators to work in a familiar environment.
The following link provides an overview on using Python ODBC to gather queries from SQL Server:
The basic connection process is to use row64tools to push updates from Microsoft SQL Server to Row64. An overview is available here:
An overview of secure file transfer using SCP in Python is described here:
Security Setup
This integration example uses .env files to set login credentials separately from the integration .py files. An overview of that approach is here:
More details on the most popular Python library for .env is here:
This integration example assumes a .env file setup in:
C:\r64tools\db.env
You can modify the location and the python script as needed.
Minimal Example Setup
Here's a simple example of testing this integration. It includes:
-
A single Windows PC/Instance with SQL Server installed
-
A single Ubuntu PC/Instance with Row64 Server installed
We'll query SQL Server from Python using ODBC drivers, save a .ramdb file, and move it over to Ubuntu.
Install Python on Windows
To start, install the latest version of Python from:
It is easier to download and run the Python install manager because it installs all the environment variable to run Python from the command line.
SQL Server Install Wizard
For a minimal testing setup, just use the install wizard for installation. An overview is here:
Downloads are here:
Download the "SQL Server 2022 Developer" version for a free testing version.
Run the installer, pick "Basic Install," and keep all the default options.
Create a Test Database
Now, create a simple test database from the command line.
Open up a Windows Command Prompt and type:
sqlcmd
You can now enter SQL commands. To input the command, you type "go" after each line. Enter in a simple test:
CREATE DATABASE Examples;
go
USE Examples;
go
CREATE TABLE Sales (Date DATETIME, Amount DECIMAL(15,2), Product VARCHAR(50));
go
INSERT INTO Sales VALUES ("2025-01-21 13:23:44", 446.57, "Seal SE Robotic Pool Vacuum");
go
INSERT INTO Sales VALUES ("2025-01-21 13:23:52", 287.24, "Tikom Robot Vacuum and Mop");
go
INSERT INTO Sales VALUES ("2025-01-21 13:24:03", 1376.28, "ECOVACS DEEBOT T20 Omni Robot Vacuum");
go
INSERT INTO Sales VALUES ("2025-01-21 13:24:17", 3200.10, "Husqvarna Automower 430XH");
go
If you want to see the Example database in the command line, type:
USE Examples;
go
SELECT * FROM Sales;
go
Set Up Python Dependencies
Next, let's set up all the Python pip libraries needed for the integration. In the Command Prompt, type in:
python -m pip install row64tools
python -m pip install pandas
python -m pip install python-dotenv
python -m pip install pyodbc
python -m pip install paramiko
python -m pip install scp
Next, create our .env file in the command prompt and open it in Notepad. We can also use this directory to save .ramdb files. Type in the following commands in Command Prompt:
mkdir C:\r64tools
cd C:\r64tools
.>db.env 2>NUL
notepad db.env
Enter the following sample setup options into Notepad:
DBHost=localhost
DBUser=admin
DBPwd=#8976S#Dfs
SSH_Host=192.168.1.10
SSH_Port=22
SSH_User=row64
SSH_Pwd=temp7
You can update this later with the exact data for your setup. It is important to use SSH with the row64 user so that you transfer the files with a user that has row64server access.
Warning
Once your test version is validated, don't use example or default installation passwords.
You'll need to download the Row64 integration to SQL Server from GitHub:
Copy the SQLServer_RamDB.py test file into the folder:
C:\r64tools
Open ODBC Data Source Driver
Windows ships with ODBC Data Sources installed. You can open it from the Run menu:
Take note of the exact name of the ODBS driver in the Drivers tab. You will use this name in the connection string.
The connection string in the example .py connects to localhost using Windows login authentication:
conn = pyodbc.connect('''Driver=ODBC Driver 17 for SQL Server;Server=localhost;Database=Examples;Trusted_Connection=yes;''')
For more complex setups, you'll need to change that string based on your authentication, server, and login details.
Loading Folder and Permissions
You will need to make the loading folder that recieves your .ramdb file. For this minimal example, use the following command to create the needed folder at the specified location:
mkdir -p /var/www/ramdb/loading/RAMDB.Row64/Temp
Also, it's best to set the SSH_User field to row64 so that when the copied file is received, the row64server service has access to it.
SSH_User=row64
Call SQL Server from Python
With the integration installed, you can run the SQLServer_RamDB.py from the terminal and see the database print out:
cd C:\r64tools
python SQLServer_RamDB.py
Set Up SSH on Ubuntu
If you only completed the default Ubuntu install, it's likely that SSH is not set up on your server or instance.
In Ubuntu, check the list of installed UFW profiles with:
sudo ufw app list
If OpenSSH is not listed, install it with:
sudo apt install openssh-server
Enable SSH connections and the firewall:
sudo ufw allow OpenSSH
sudo ufw enable
Note
This integration routes a SSH login and password in the example .py file. The setup can be modified for a higher tier of security using a SSH key, which is an access credential in the SSH protocol
Troubleshoot if Needed
In the event that you need to troubleshoot the connection, this section provides some tips that might help you identify the potential issue.
Environment File Configuration
When you ran SQLServer_RamDB.py, it attempted to SCP transfer the new .ramdb file to the server specified in:
C:\r64tools\db.env
For this to work, it is important to verify that the file is set up correctly.
Install Row64 Server
On the Ubuntu machine you are copying to, make sure you've installed Row64 Server. If you don't already have Row64 Server installed, you can reference the following documentation to set it up:
Troubleshoot Loading Folder and Permissions
Ensure that you have created the loading folder and granted it the proper permissions.
Port Connections
If the .ramdb file didn't copy over to Row64 Server, debug the port connections with ping and telnet. In the following commands, substitute the example IP addresses (192.168.1.10) with your own hostnames or IPs:
Ping:
ping 192.168.1.10
Telnet:
telnet 192.168.1.10 22
For reference, the following article discusses resolving these issues:
Test with ByteStream Viewer
Once you see the file copy over to Ubuntu, you can install ByteStream Viewer to visualize the data.
To install ByteStream Viewer on Ubuntu, you can reference the following documentation:
You can drag the .ramdb file right into the ByteStream Viewer
Alternatively, you can simply open the file in Row64 Studio.
Continual Updates
On Windows, Task Scheduler is the production-proven tool for establishing continual updates. Here's a simple example on how to set it up:
You simply need to take the integration .py file and set up a cron job to run at your data refresh rate, which can range from every 20 seconds to every day.