SQL Server Installation to PC and PLC
Install and Configure SQL Sever for Automation project
Capturing data from the devices are very important for automation project. There are many ways of capturing, processing, and storing data. It can be stored in the local database or can be stored in the cloud and then it can be processed there. In this tutorial, we shall focus on how to capture data by using the Microsoft SQL Server.
Downloading and installing SQL server and management studio is not difficult. But the configuration of the SQL server can be tricky and in the worst case, it can be frustrating. I have sometimes tried similar computers with different results.
=> Download SQL server developer edition (or express edition) from https://www.microsoft.com/en-us/sql-server/sql-server-downloads
=> Download SQL Server Management Studio from https://docs.microsoft.com/fi-fi/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15
If the installation has been successfully done, we see a similar screenshot.
Figure 1: MS SQL Server Installation has been successful
=> After installing SQL Server we need to customize it.
=> Now follow these to continue the SQL configuration
=> Note the Server name or the Server (our case: DESKTOP-0GSQE8L, you can find the name also by executing SELECT @@SERVERNAME in the Management Studio’s New Query)
Open SQL server management studio and login as windows authentication and select the properties of login name sa.
Follow the following steps. We may not need all steps, but good to follow these.
Figure 2: Log in as Windows Authentication, another way is SQL Server and Windows authentication mode
By default, Windows Authentication mode is active, we need to activate SQL Server and Windows Authentication mode.
Figure 3: Selection of SQL Server and Windows authentication mode
Figure 4: Enable the login
Figure 5: Red cross means disabled
=> Now we need to create a user and password for the database
=> In management studio, create login | new login, this provides the following dialog
=> If we don’t have a user and password then we can’t create tables by using the script
Figure 6: Creation of user account, should be SQL Server authentication
=> Now select the root of SQL server in the management studio | properties and make sure that SQL Server and Windows Authentication are selected.
=> In the server role page, allow all, meaning select all permission
=> On the status page grant login and connection permission to the database. Now the user should be able to connect to the database by the program.
=> As a final step, select the SQL server and properties | Security | SQL Server and authentication mode.
Figure 7: Creation of user account, should be SQL Server authentication
What could be the reason for this?
See the solution at https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/troubleshoot-connecting-to-the-sql-server-database-engine?view=sql-server-ver15
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
If the above error remains then check the following issue, TCP/IP port number.
Figure 9: Setting of TCP/P port
Check all the TCP ports for all IPs and set it to 1433
Now all should be fine. We are able to use SQL server with TwinCAT HMI.
=> If you want to drop a user you could execute the following command in SSMS
DROP LOGIN testUser ;
If we want to know the current SQL version running in this machine, execute the following command in management studio.
SELECT @@VERSION
If no user has been created but you are trying to use the user by script then we get the following message.
Msg 15007, Level 16, State 1, Server DESKTOP-0GSQE8L, Line 2
‘databaseusername’ is not a valid login or you do not have permission.
Now our database is ready and we shall be able to connect to the database.
Create a Database :
By using a script (You must have a user id and password created by the management studio)
We can create a database by using a script or by using MS SQL server management studio. Download the script (before using please check the content as a good practice) from this link, and unzip it.
=> Take a command prompt as admin and execute as follows (you can open the content by notepad for example):
If there is no such database (using for the first time)
C:\HomeAutomation\SQL_Database\Db>initmydatabase.bat
If there is already the same database (you are recreating it for example)
C:\HomeAutomation\SQL_Database\Db>initmydatabase.bat -r
If we want to run the script automatically by executing by C# code, we can use the following code.
System.Diagnostics.Process proc2 = new System.Diagnostics.Process();
proc2.EnableRaisingEvents = false;
proc2.StartInfo.FileName = "initmydatabase.bat";
proc2.Start();
Thread.Sleep(1000);
If you want to recreate the database then use -r option. If you don’t have the database already then you can execute the batch file without any option. If you have already same database and you don’t have to use r option.
By using management studio
Using management studio is straight forward, follow the graphical menu, we shall add it later.
As a final check, we need to match our Database as shown below. MyDatabase is empty, no table yet, my user is visible. The default password I used was mypassword =>very secret?
Figure 10: Table creation by using management studio
Now we are ready to develop application both TwinCAT HMI and Windows Application, we have few application that we can download and go through with the explanation as necessary.
Create Table Manually by Management Studio
We can create table manually by the management studio. We need to format the command as follows.
=> Select MyDatabase from the drop down list
=> Execute a new query (we need to define the dataset when we create the table)
CREATE TABLE [MyDatabase].[dbo].[MQTTData] (
TimeStampSet varchar(255),
ID varchar(255),
Value varchar(255),
Unit varchar(255),
Response varchar(255)
);
We can insert a set of data to the table by executing the following command
INSERT INTO [MyDatabase].[dbo].[MQTTData](TimeStampSet, ID, Value, Unit, Response)
VALUES ('2022-12-02T13:35:43', '1', '3.34', '1', '2');
We can check back the data by executing following command
SELECT TOP (1000) [TimeStampSet]
,[ID]
,[Value]
,[Unit]
,[Response]
FROM [MyDatabase].[dbo].[MQTTData]
In the Results window, we can see the data.
Delete data from the table
DELETE FROM [MyDatabase].[dbo].[MQTTData] WHERE TimeStampSet='2022-12-02T13:35:43';
TIP 00:
public class Employee
{
public virtual int Id { get; protected set; }
public virtual string FirstName { get; set; }
public virtual string LastName { get; set; }
public virtual Store Store { get; set; } //a reference to the Store that they work in.
}
public class EmployeeMap : ClassMap<Employee>
{
public EmployeeMap()
{
Id(x => x.Id); // x is an instance of Employee
Map(x => x.FirstName);
Map(x => x.LastName);
References(x => x.Store); // References creates a many-to-one relationship between two entities (many x in a store).
//a many-to-one to Store (many Employees to one Store) through the Store property. } }
TIP 01: If we create a DLL then we need to make sure to use the target framework as (.NET Framework). If we use other versions for example .NET standard, it will not work (or we need to find the combination of the framework).
TIP 02 : How to query numbers of table in the database (execute the following when database has been created)
select count(*) as [tables]
from sys.tables
We shall create our home automation system for loading data to SQL Server and later post it to the cloud for generating reports.
See also: https://www.hemelix.com/automation/structured-text-mqtt/
Now we are ready with the installation to continue with the next modeling https://www.hemelix.com/sql/sql-data-modeling/
Ask questions related to Hemelix sample code and design at Google group https://groups.google.com/g/hemelix