SQL-backed hosting environment

This page describes the steps how to install Thriot in fully MS SQL (Express)-based runtime environment. It means that all management data, telemetry data and of course messages are stored in a Microsoft SQL (Express) 2014 database.

We will discuss the installation procedure of a two-box environment, where the boxes and their responsibilities are the following:

  • Web frontend server
    • Central website
    • Management API
    • Platform API
    • Platform Websocket Service
    • Reporting API
  • SQL backend server
    • Messaging API (private)
    • Management Database
    • Telemetry database
    • Messaging database

Based on this description with quite minimal phantasy you can find out how to install the system on a single box or even scale it out to more servers. When scaling out please consider the following rules:

  • The APIs can be freely scaled out except for the Messaging API
  • Messaging API should run in single instance mode (failover clustering is OK)
  • The Websocket service can be freely scaled out
  • The database can run in Always on/HA mode

For easier configuration please refer to the following page: http://portal.thriot.io/package-autoconfiguration/

Server names used in this tutorial

This tutorial assumes that the target servers are the following:

thriotsqlweb.cloudapp.net – Web frontend server

thriotsqlbe.cloudapp.net – SQL Backend server

Of course you will install servers with different names, these are just examples. Ensure to use the correct names in all configurations and settings.

Prerequisites

This tutorial supposes the release v0.6 of source code.

Having the source code you can use the following command the produce the binaries with default SQL configuration:

src\Service\Build> .\build.ps1 -config sql -configtmt sql -configmsg sql -copyConfigs yes -linuxify no

The results will go to the output\{CURRENTDATE}_sql folder.

Please note that in case of upgrading (to be discussed in the Upgrading section below) the -copyConfigs parameter must not be used to ensure that no config files are accidentally overwritten.

The output folder contains the following subfolders:

Folder Description Type
api Management API REST API
msvc Messaging Service API REST API
papi Platform API REST API
rapi Reporting API REST API
web Central website Thin website
websocketservice Websocket Service Windows Service
install install\configtemplates – Configuration templatesinstall\storage\management – Program that initializes management storageinstall\storage\messaging – Script to create Message database storage Templates, scripts and applications
plugins Plugins for telemetry data and queueing Plugins

Please take into account that the rest of this page will assume that the build output is copied to the c:\Thriot folder on the target servers.

Install SQL backend server

In the example we are using Windows Server 2012 R2 running as Azure VM – Small instance.

Prerequisites

As a Prerequisite to successfully install Microsoft SQL Server Express 2014 you must first ensure that .NET 3.5 is installed on the system.

image1

Installing Microsoft SQL Express 2014

After you’ve successfully installed the prerequisites it’s high time to download SQL Express 2014 (with Tools) installation package.

Download location: http://www.microsoft.com/en-us/download/details.aspx?id=42299

For a 64 bit system you must choose ExpressAndTools 64BIT\SQLEXPRWT_x64_ENU.exe.
image2

 

Double click on the installation execute to start the wizard. Here choose “New SQL Server stand-alone installation …”.

image3

 

Always consider using the default settings while ensuring that all the required components are set up:

image4

If the servers are not in Windows AD Domain environment you must ensure that mixed mode authentication is enabled (also in our case). In case you are is a domain environment you should consider using Windows authentication only.

In case of mixed mode authentication you are to set the password for the sa user that is a full-permission sysadmin user in the system so that it’s super-important to choose a highly secure password. Ensure to add some windows user to the SQL server admins role.

image5

You may select data directories here. For example you may choose a high-performance disk to storage the databases.

After setting all these parameters you should start and wait for the finish of the installation process.

SQL server will be used from the outside world. Open port number 1433 in “Windows Firewall with Advanced Security” snap in.

image6

 

Moreover in Sql Server Configuration Manager enable the TCP/IP endpoint …

image7

… and set the TCP port to 1433. Of course you may choose other port number than 1433 but in this case you must use this port number in all the connection strings.

image8

Configuring databases

Open SQL Management Studio and connect to the SQL Backend server using the sa user.

Create 3 database:

  • Thriot– for management
  • ThriotTelemetry – for storing telemetry data
  • ThriotMessaging – for messaging

 

Create a user called thriot with a strong password. Disabled “Enforce password policy” otherwise you must change the password at next logon. It’s recommended to set the default database to Thriot.

sqlthr

Ensure to provide db_owner role for thiort user to the Thriot database.

sqlthr2

Do the previous steps similarly to the thriottelemetry and thriotmessaging user, namely:

  • Create thriottelemetry with strong password, disable password policy, add ThriotTelemetry as default DB, put iottelemetry to the db_owner role of ThriotTelemetry
  • Create thriotmessaging with strong password, disable password policy, add ThriotMessaging as default DB, put thriotmessaging to the db_owner role of ThriotMessaging

To prepare the ThriotMessaging database load the install\storage\messaging\CreateDB.sql script and run it against the ThriotMessaging database.

sqlthr3

As you have previously built the application, you will find the following configuration file: install\storage\management \Thriot.CreateSqlStorage.exe.config. Modify the connection string called ManagementConnection to point to the Thriot database with using the thriot user with the appropriate password and connection parameters. Run the Thriot.CreateSqlStorage.exe program which will create all the tables in the Thriot database that are used by the system.

image12

Open the Settings table in SQL Management Studio. Ensure to correctly modify the configuration settings like this:

sqlth4

 

Explanation of the configuration settings:

Category Setting Value
Connection TelemetryConnection Connection string for recording telemetry data in SQL.e.g.Server=*******;Database=ThriotTelemetry;User=thriottelemetry;Password=*****;
Microservice MessagingServiceApiKey Shared secret for authenticating to the messaging service.It’s an automatically generated value.
Microservice MessagingServiceEndpoint Messaging service url.e.g.http://thriotsqlbe.cloudapp.net/v1/messaging
Microservice TelemetrySetupServiceApiKey Shared secret for authenticating to the telemetry setup service.It’s an automatically generated value.
Microservice TelemetrySetupServiceEndpoint Telemetry setup service endpoint.e.g.http://thriotsqlweb.cloudapp.net/papi/v1/telemetryDataSinkSetup
PublicUrl ManagementApiUrl Management API url.e.g.http://thriotsqlweb.cloudapp.net/api/v1
PublicUrl PlatformApiUrl Platform API url.e.g.http://thriotsqlweb.cloudapp.net/papi/v1
PublicUrl PlatformWsUrl Platform Websocket url.e.g.ws://thriotsqlweb.cloudapp.net:8080
PublicUrl ReportingApiUrl Reporting API url.e.g.http://thriotsqlweb.cloudapp.net/rapi/v1
PublicUrl WebsiteUrl Central website url.e.g.http://thriotsqlweb.cloudapp.net
Runtime EmailActivation Specify If email activation is required.For production environment should be true.e.g.true
Runtime ServiceProfile ServiceProvider, SingleCompany or SingleService.ServiceProvider

 

Install IIS

In Server Manager select Add roles and features.

Here tick the Web Server (IIS) Role.

image14

 

Select the following role services:

image15

image16

image17

image18

Hit Next and do the installation.

Install HttpPlatformHandler

Follow this page to add HttpPlatformHandler:

http://www.iis.net/downloads/microsoft/httpplatformhandler

On some environments it may be preinstalled.

Installing Messaging Service application in IIS

On the target server create the c:\Thiort\msvc folder and copy the content of the msvc folder here created by the build described on the top of this page.

In the config folder (located at C:\Thriot\msvc\approot\packages\Thriot.Messaging.WebApi\1.0.0\root\config) edit the connectionstring.json and the connectionstringmsg.json config files to configure the main and the messaging connection strings respectively.

msgc

connectionstring.json should look like this:

{
    "ConnectionString": {
        "ManagementConnection": {
            "ConnectionString": "Server=********;Database=Thriot;User=thriot;Password=********************",
            "ProviderName": "System.Data.SqlClient"
        }
    }
}

connectionstringmsg.json should look like this:

{
    "ConnectionString": {
        "MessagingConnection": "Server=*******;Database=ThriotMessaging;User=thriotmessaging;Password=********************"
    }
}

 

Ensure to have the correct connection string instead of the examples above.

 

web.nlog config is fine if your logging directory is C:\Thriot\log, too.

Create a new website called Thriot and point it to the c:\Thriot\msvc\wwwroot folder. Ensure to delete or disable the Default Website first.

msvcweb

Create a folder named: c:\Thriot\log

Add Modify permissions to the c:\Thriot\log folder to the iis apppool\thriot user.

image22

image23

If you are running on Azure then on the Azure Portal open the HTTP – 80 port for the thriotsqlbe VM. SQL port shouldn’t be opened as we don’t communicate directly with the SQL server just the IIS application which counts as internal communication.

image24

Do a quick test. Navigate to the http://thriotsqlbe.cloudapp.net/v1/messaging/enqueue

folder. If the messaging is HTTP 405 (or 404) – Method not allowed then the IIS Application is working properly.

Install Web frontend server

Install IIS

Follow exactly the same steps as you did for the SQL backend server in case of IIS installation procedure.

Install HttpPlatformHandler

Follow this page to add HttpPlatformHandler:

http://www.iis.net/downloads/microsoft/httpplatformhandler

On some environments it may be preinstalled.

Install Thriot applications as IIS websites and Applications

From the build output

  1. copy the content of the api folder to the c:\Thriot\api folder,
  2. copy the content of the papi folder to the c:\Thriot\papi folder,
  3. copy the content of the rapi folder to the c:\Thriot\rapi folder,
  4. copy the content of the web folder to the c:\Thriot\web folder,
  5. copy the content of the websocketservice folder to the c:\Thriot\websocketservice folder,
  6. copy the content of the plugins folder to the c:\Thriot\plugins folder.
The configuration files folder is located at the following places respectively:
  1. c:\Thriot\api\approot\packages\Thriot.Management.WebApi\1.0.0\root\config\
  2. c:\Thriot\pap\approot\packages\Thriot.Platform.WebApi\1.0.0\root\config\
  3. c:\Thriot\rapi\approot\packages\Thriot.Reporting.WebApi\1.0.0\root\config\
  4. c:\Thriot\web\wwwroot\config\
  5. c:\Thriot\websocketservice\config\

Prepare the config files in the following way:

connectionstring.json should look like this:

{
    "ConnectionString": {
        "ManagementConnection": {
            "ConnectionString": "Server=*********;Database=Thriot;User=thriot;Password=********************",
            "ProviderName": "System.Data.SqlClient"
        }
    }
}

Ensure to have the correct connection string instead of the examples above.

 

siteroots.js (for the central website):

app.constant('siteRoots', {
   managementRoot: 'http://thriotsqlweb.cloudapp.net/api/v1',
   reportingRoot: 'http://thriotsqlweb.cloudapp.net/rapi/v1'
});

Ensure to have the correct HTTP endpoints instead of the examples above.

 

smtpsettings.json (for management api):

{
    "SmtpSettings": {
        "FromAddress": "no-reply@thriot.io",
        "FromName": "Thriot",
        "BouncesAddress": "bounces@thriot.io",
        "Host": "smtp.sendgrid.net",
        "Port": "587",
        "UserName": "***",
        "Password": "***"
    }
}

Ensure to have the correct SMTP settings instead of the examples above.

If you are running an asp.net5 rc1 app under a virtual directory in IIS you need to do some workaround to support virtual directories. Thriot implements this by vdir.json config files.

vdir.json shoud look like this:

{“VDIR”: “/api”}
{“VDIR”: “/papi”}
{“VDIR”: “/rapi”}

respectively for management, platform and reporting services (they are reachable under api,thriot.io/management, etc). The vdir.json has to be put under the regular config directory as the other configurations.

Create the root website that will host the Central website:

siteaspnet5

Create api Application for the Management API (right click the Thriot website / add application) – c:\Thriot\api\wwwroot:

api

 

Create papi Application for the Platform API- c:\Thriot\papi\wwwroot:

papi

 

Create rapi Application for the Reporting API- c:\Thriot\rapi\wwwroot:

rapi

 

The final structure should look like this if you look at the website in IIS Manager:

siteaspnet5_2

 

Installing the Websocket service

In the c:\Thriot\websocketservice run c:\Windows\Microsoft.NET\Framework64\v4.0.30319\InstallUtil.exe Thriot.Platform.WebsocketService.exe

image27

To provide more reliability set recovery settings in the service control manager. Start Service Control Manager (services.msc), locate the Thriot Websocket Service, an on the Recovery tab select “Restart the service” option for all occurrences.

Setting up logging

Create a folder named c:\Thriot\log.

Add Modify permissions to this folder for the iis apppool\thriot user.

image21

Do the same for the nt service\ThriotWebSocketService user.

image28

Final steps

HTTP endpoint will be opened by default on the firewall, you should open the 8080 port for the websocket service.

image29

To start the Windows service you can use the Service Control Manager (services.msc) or issue the following command from the command line:

net start ThriotWebSocketService

If you are running on Azure or behind a firewall do not forget to open the 80 and 8080 ports.

Testing if everything works fine

Now navigate to the address of the web frontend server, register a new user, accept the registration, login with the user, create new company, service, network and device. If everything works fine you can start using the .NET or Linux C++ client libraries.

Upgrading

To upgrade to a newer/newest version do the following steps:

  1. Get the latest source snapshot
  2. Do a full rebuild with the same parameters as for the first time but ensure that you specify -copyConfig no setting.
  3. Update the binaries
  4. Execute the Management storage creator application with the same connectionstring settings (CreateSqlStorage) which won’t delete any data just upgrade the DB schema
  5. For the Messaging storage execute the appropriate CreateDB.sql script, which won’t delete any data just upgrade the DB schema
  6. Do a smoke test
Advertisements