KristofDM

Osi to the cloud

Blog Post created by KristofDM on Dec 15, 2016

So, we are moving the entire pi infrastructure to the cloud or at least are trying to...

We are looking at IaaS and PaaS where possible.

I'll write down my successes (and losses)  here to help the community members that might have the same idea.

 

Please note that not all things we do are 'supported' by osisoft.. (yet!).

 

First some info on the Environment.

We have a pi data archive 3.4.935.72 (2015 R2) server.

Pi coresight

Pi AF

Pi SQL Data access server

and a  'heavy consumer app' server.

SQL server for coresight and AF.

 

Testing:

To make sure we make the most out of it i wanted to have the AF and coresight databases hosted in PaaS.

This is not yet supported and was only tested on an older PaaS sql edition (pre v12) .

We have VPN with azure and are able to join AzureVM's in our local AD.

 

PiArchive

I went ahead and Installed the PI data archive on a AzureVM with ssd storage;

No problems here

 

PiCoresight

Installed Coresight on an AzureVM and adapted connect strings to use PaaS.

Migrated on-premise database to PaaS using sql management studio.

issues

1: The DvService user (in the COresight SQL database) is linked with a sql login from the domain !

=> Not supported in Azure,Dropped and recreated on PaaS.

2: Coresight connection string to sql in web.conf adapted so a Azure database can be used

code:

<add name="CoresightConnectionString"  connectionString="Data Source=yourPaaSSQlServer.database.windows.net;Initial Catalog=YourPaasDatabaseName;User ID=DatabaseUSer;Password=DatabaseUSerPassword;Encrypt=true;Trusted_Connection=false; Application Name='Coresight Web Service'" />

 

It works ! the Coresight overview page does not show any more errors.

 

PiAfServer

Moving the AF database to the cloud sql PaaS.

I probably have and advantage here since i'm a classic Database Admin so if anything is not clear. shoot!

 

The AF Database in SQL servers uses some stored procedures/functions  to create a replica, get the status of the sql agent, get the Sysadmin name, etc. ...

Since these are all calls to the database instance (the sql Engine) they will no longer work in a PaaS database. For obvious reasons Microsoft does not allow these kind of queries.

The instance catalog is not the traditional sql server kind and thus is unusable.

 

Since this is a tricky part, we made a copy of the database in our local sql server (PiAfCopy) and:

 

Changed the stored proc on the sa user to always return SA as string value.

Changed the Check SQLagent Job to always return 1

Dropped the procs for the replica (Osi discourages users to use it anyway)

Changed all windows logins to sql logins.

 

Adapted Functions:

fn_getSaName

IsSqlAgentEnabled

 

Dropped Stored Procedures:

[Ddbo].[USP_AUDIT_ENABLED]

[dbo].[usp_replicate_primary_createdistributor]

[dbo].[USPC_CDC_ENABLE]

 

Now , using sql management studio 2016, i deployed the database to the PaaS sql.

Issue: Out of memory

This can be quite intensive on the client computer so having enough RAM is essential !

In case you haven't, try closing as much apps ass possible.

You could also do this on the database server itself but it needs an internet connection!

 

After some time (it takes a long time) the database should be deployed in Azure PaaS.

 

Installing AF server + System Eplxorer!

 

We'v setup an IaaS server and installed AF like we normally did. We disabled the database execution utility and disabled check connection while specifying the database server.

Just specified the PaaSSQLserver.

 

Installation went smooth.

 

The connect string needed to be changed.  this time in AfService.Exe.Config

It's located  C:\Program Files\PIPC\AF. It is somewhat different from coresight...

 

    <add key="connectString" value="Persist Security Info=False;server=YourPaasSqlServer.database.windows.net,1433;database=YourPaasDatabase;USER id=DatabseUser;PASSWORD=DatabaseUserPassword; Application Name=AF Application Server;" />

 

Restarted af and..

 

It worked!

Initially i had an error while creating new ellements.

"cannot check in model with unique id {Some very long SID} because there is another model with an identical name"

But after retrying some times, it vanished!

 

Next steps:

Analysis service, Notifications, pi sql data access server.

To be continued!

 

 

SQL DATA ACCESS (PIOLEDB)

After installing the pi data access layer on a IaaS server, running the sql commander fails.

Under the hood  the PI sql commander tries to use the (undocumented) system sp sp_oledb_ro_usrname .

The sp_oledb_ro_usrname stored procedure returns the oledb database read only status.

This kind of queries are not supported on Microsoft SQL Azure Paas.

 

Here ends the SQL PaaS try-out.

 

We'll look at Hosting the SQL on premise ...

Outcomes