2 Replies Latest reply on May 1, 2018 7:47 PM by John Messinger

    How to set-up AF SQL DB components using Amazon Web Services RDS option?

    amitpatil.vit
    amitpatil.vit Apr 16, 2018 12:12 PM

      AF SQL DB components set-up on AWS RDS

      Has anyone set-up AF SQL DataBase (PIFD) with DBaaS option in the past? We are currently in the process of identifying cloud DB services (SQL Server DataBase) for one the cloud based AF instance setup requirements. This query is specific to Amazon web services (AWS).

       

      Requirement: Set-up AF service and SQL Server as distributed components on cloud.

       

      Option1: 1 EC2 Server instance for AF Service and another EC2 instance with preloaded SQL Server (EC2 server with SQL AMI), This is pretty much straight forward.

      Option2: 1 EC2 Server instance for AF Service and AWS RDS service (SQL Server). Now here, there are some grey areas when we consider RDS. First of all, manual SQL script execution has to be executed using a command prompt (run Go.bat by passing few parameters in the command).

       

      Q1. Can we install AF SQL database scripts on one of the servers and then perform AF SQL scripts manual execution part remotely to point to RDS endpoint as our desired DB instance to host PIFD (with sufficient SQL user or Windows user permissions)?

       

      Q2. Also, I am not sure on how to manage "AFServers" local group in case of AWS RDS?

       

      Kindly let me know in case of any suggestions on the same. Thanks!

      - Amit Patil

        • Re: How to set-up AF SQL DB components using Amazon Web Services RDS option?
          gmichaud-verreault
          gmichaud-verreault Employee May 1, 2018 3:04 PM (in response to amitpatil.vit)

          Hi Amit,

           

          This question comes up from time to time so let me summarize the current state:

          We do not support using a DBaaS (Azure, Amazon RDS, etc.) for the backend database for PI AF (and also PI Vision). Although it is possible to make it work for PI Vision, using a DBaaS to host the backend SQL database for AF (PIFD) and Vision (PIVision) is not supported and there is no current plan to support it. Since those servers do not have a master database, are not windows-based (local groups, etc), wouldn't allow our feature checks and dependencies (i.e. replication, maintenance, audit, afplugin, etc.) there is no current workaround for PIFD (AF).

           

          I think it is also important to note that the DBaaS do not offer the same degree of performance as an on-prem setup. The performance is very rarely better than a SQL Express implementation...

           

          You can see one of the previous conversations here:

          AF on Azure SQL

           

          --

          Gabriel

          4 of 4 people found this helpful
          • Re: How to set-up AF SQL DB components using Amazon Web Services RDS option?
            John Messinger
            John Messinger Champion May 1, 2018 7:47 PM (in response to amitpatil.vit)

            Hi Amit,

             

            Do note Gabriel Michaud-Verreault's warning that this is NOT a supported configuration by OSIsoft.

             

            Now, having said that, it is possible to do this, as I did it a little over a year ago. You can read the details at Re: Way to run PI AF SQL scripts without sysadmin role My test EC2 instance of PI AF seemed to work fine, but there are aspects that I didn't fully test. For the most part I was able to create AF databases and manage the Elements and Templates within with no apparent issues.

             

            I would lean towards your first option, to use two separate EC2 instances, as this is most like a standard on-prem type configuration. I found that the second option can work, but lack of official support, and the 'hack' nature of getting it installed would worry me in a production environment.

             

            John

            1 of 1 people found this helpful