8 Replies Latest reply on Mar 11, 2015 2:20 PM by pthivierge

    Is it possible AF Server to work with several SQL Instances and they to be changed dynamically

    VarbanVarbanov

      Hi, we woluld like to have several SQL instances on one machine, each of which contains its own PIFD and af server which to retrieve the data needed to be shown in our application (one application). To have several logical AF Databases in one PIFD database is not an option for us. is there a way that an AF Server installation can work with several separate PIFD databases and these instances to be changed dynamically (or maybe to have severeal AF Server installations on one machine)?

        • Re: Is it possible AF Server to work with several SQL Instances and they to be changed dynamically
          MaximeHT

          If the separate databases has to store the same data, Af cluster and/or AF collective could be the solution.

          • Re: Is it possible AF Server to work with several SQL Instances and they to be changed dynamically
            pthivierge

            Hello Varban,

             

            Firslty, could you please explain why it is not an option for you to have several logical databases into one PIFD database?


            Database

            I do not recommend to install several SQL Server instances on a single machine. What can be done however, is to create several PIFD Databases on the same SQL Server instance ( ex: PIFD1,PIFD2, etc... ).  Databases script need to be manually executed. I am providing you the link to the documentation on how to install the database manually:

            Create or upgrade the PI AF SQL Database manually.

             

            Creating multiple databases (assuming that the SQL Server is already installed, please refer to the documentation for each of these steps):

            • You create the AFServers local group on the SQL Server machine
            • You execute the SQL Script, N times, and you provide a different (PIFD) Database name each time ( PIFD1,PIFD1 or a more reprensentative name: PIFD_maintenance, PIFD_production, etc.. )

             

            AF Service

            The AFService (AFServer) gets its connection information from the AFService.exe.config file located in the PIPC\AF Folder. To change which database the AF Server is using, this file needs to be edited.

            See Documentation.

            To have multiple AFServers, the easiest way is to install several AF Services on different servers and point them the the right database (using the AFService.exe.config file) on the SQL Server machine.

             

            I do not recommend to create several AFServices on the same machine because you would need to change ports numbers, manually create the AFServices and would make it more difficult to configure the clients to connect to the AF Servers as well as maintaining the AF Services installation.

             

            Please give us more details on what you want to achieve and we will be happy to assist you.

            1 of 1 people found this helpful
              • Re: Is it possible AF Server to work with several SQL Instances and they to be changed dynamically
                VarbanVarbanov

                Hello Patrice,

                 

                       Thank you for your answer. It was very helpful for us. We need to replicate several PIDB databases from different SQL server sources into one target PIDB database. To do this we have to use some kind of merge replication, which is not an option for us, because we are not very familiar with PIDB structure and changes in the future. Therefore we have decided to have multiple target PIDB databases. In other words we have chosen to use one to one replication between source and target PIDB. The problem here is that all target PIDB and AF Servers have to be on one machine. Could you explain us how to setup manually multiple AF Servers on one machine or if you have a better idea for the scenario above?

                  • Re: Is it possible AF Server to work with several SQL Instances and they to be changed dynamically
                    lmlcoch

                    Best way would be to export the contents of the AF database  (Elements, Attributes, Templates, Notifications, Event Frames and so on)  into .XML file using PI System Explorer (right click on the given asset > Export to file).

                    Then you simply import the .XML file into the target AF Database (again, using PI System Explorer). That way, you would have everything in a single PIFD (AF Database).

                    • Re: Is it possible AF Server to work with several SQL Instances and they to be changed dynamically
                      pthivierge

                      Hello Varban,

                       

                      If you really want to go this way, you should look at

                      • sc create command (you have copied the AF Service files to a new directory)
                      • afservice.exe.config file ( to change listening port for the new service-> a port number can only be used by one application at the time, and the database connection string,  I am unsure of there are other options to change)
                      • AF clients configuration, to change the connecting port, this is in the options.
                      • Possibly piaflink.exe.config is you are using MDB to AF synchronisation.
                      • All existing documentation: Use technical support website to find the documentation, KB articles, etc.

                       

                      I have never done that and I cannot guarantee you that this will work, so please consider this as experimental.  Having this said, I dont see a reason why it shouldn't work.

                      Since this is not a recommended scenario, I will let you look at it and try it. 

                       

                      Please post back your results, I am curious to see if this works

                      • Re: Is it possible AF Server to work with several SQL Instances and they to be changed dynamically
                        dng

                        Hi Varban,

                         

                        As others have mentioned, this approach might work for your intended application. However, keep in mind that this might make future upgrades difficult because the configuration has not been tested. Since your intention is ultimately to merge into one target database, in terms of maintenance, it might make sense to have one PIFD database, accessed in one AF server.

                         

                        As Lubos has mentioned, you can export and import AF database objects so that you will end up with one PIFD database. Just giving you some documentations here:

                        - Refer to this section on how to export and import using PI System Explorer

                        - Refer to these documentions on how to export and import by using AF command line utilities

                         

                        For organization purposes, you can use separate AF databases (within the same PIFD back-end database) to represent each imported database. This way, you are essentially maintaining one PIFD database (ease of management), but the contents are separated (better organization, security, etc.)

                        1 of 1 people found this helpful
                    • Re: Is it possible AF Server to work with several SQL Instances and they to be changed dynamically
                      Robert Raesemann

                      As others have said, your approach is unorthodox, and I'm sure that you will have trouble getting support if you run into issues, but I have another suggestion.

                       

                      I have not tried this with AF, so I would definitely test it on a development server first, but I imagine that you could setup a SQL alias prior to installing AF, configure AF to use that alias, and then swap the alias around (shut down the AF server first, of course). I set alias for SharePoint servers so that the database can be moved transparently to the application. I don't know why this wouldn't work well with AF as well.

                       

                      Make sure to set the 32 bit and 64 bit aliases...

                      C:\windows\system32\cliconfig.exe

                      C:\windows\syswow64\cliconfg.exe

                      2015-01-14_09-39-08.png

                      • Re: Is it possible AF Server to work with several SQL Instances and they to be changed dynamically
                        pthivierge

                        Hello Varban,

                         

                        I am curious to know what you are your team have chosen to do regarding this question?

                        What solution did you consider?

                         

                        I am marking this question as assumed answered. but do not hesitate to post again on this thread if you need further help.