Install and Configure SQL Sever for Automation project
Capturing data from the devices are very important for automation project. There are many ways for capturing, processing and storing data. It can be stored to the local database or can be stored to cloud and then it can be processed there. In this tutorial, we shall focus on how to capture data by using Microsoft SQL server.
Downloading and installing SQL server and management studio is not difficult. But configuration of the SQL server can be tricky and in worst case it can be frustrating. I have sometimes tried to similar computers with different result.
=>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
=> After installing SQL Server, configure it
=> Now follow these to continue the SQL configuration
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.
By default, Windows Authentication mode is active, we need to activate SQL Server and Windows Authentication mode.
=> 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 user and password then we can’t create tables by using script
=> Now select the root of SQL server in the management studio | properties and make sure that SQL Server and Windows Authentication is selected.
=> In the server role page, allow all, meaning select all permission
=> In the status page grant login and connection permission to database. Now the user should be able to connect to database by program.
=> As a final step, select the SQL server and properties | Security | SQL Server and authentication mode.
You might need to put windows fire wall OFF or need to open particular port (not really sure :-))
What could be the reason for this?
See 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.
Check all the TCP port for all IP and all 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 script
We can create a database by using 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:
If there is no such database (using for 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 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.
I found a tutorial on Fluent Nhibernate in YouTube, I did not see the source code anywhere but I made and adapted the sample and that can be downloaded from this link.
As we are progressing this tutorial for our fictitious home automation system, in the mean time we can take a look in this site:
https://github.com/FluentNHibernate/fluent-nhibernate/wiki/Getting-started
The sample provided by the Fluent NHibernate that I adapted to my version 15.0.2000.5, the working sample can be download from this link.
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); //Referencescreates a many-to-one relationship between two entities (many x in a store).
//a many-to-one toStore(manyEmployees to oneStore) through theStoreproperty. } }
TIP : If we create a DLL then we need to make sure to use target framework as (.NET Framework). If we use other version for example .NET standdard, it will not work.
