11 Replies Latest reply on Jun 12, 2013 2:13 PM by jfarrera

    AF collective implementation

    jfarrera

      Hi , we are trying to implement AF HA using af collective wit two pairs(AF & SQL), but even though the manual mentions that multiple pairs of AF and SQL could be used, it only explains how to do it with SQL replication, so we are not sure if we have to implement the SQL replication or if we could do it without the replication? or using just SQL mirroring between the two SQL of each pair?

       

      What we want to achieve is to have a desaster failover having a pair  in two different locations

       

      I appreciate any guidance on this

       

      Regards,

       

      Julio Farrera

        • Re: AF collective implementation
          Marcos Vainer Loeff

          Hello Julio,

           

           

           

          There are a few solutions that you can use:

           

           

           

          - Install PI AF SQL Database on a Mirrored SQL Server

           

          - Install PI AF Server on Microsoft Cluster Service

           

          - Create an AF Collective

           

           

           

          If you choose to create an AF Collective,  AF will automatically configure the SQL Server Replication for you, creating a Local Publication on the SQL Server of the primary AF Server and a Local Publication on the other AF Servers.

           

           

           

          The advantage of this option is that if the service of the primary AF Server is stopped, your AF client application will still be able to connect to the AF collective through the secondary AF Server. This won't occur if you use only a Mirrored SQL Server.

           

          However, using a Mirrored SQL Server is useful, if a SQL Server instance is stopped. In this case, AF Server would still be able to connect to the other SQL Server instance,  without losing its functionalities.

           

           

           

           

           

          Below, there is more information of each option from the manual "PI Asset Framework 2012 Installation Guide":

           

           

           

           

           

          Install or Upgrade PI AF SQL Database on a Mirrored SQL Server (page 25)

           

           

           

          You can run PI AF SQL database on a mirrored SQL Server. A mirrored SQL Server session can be implemented in various manners. The instructions provided in this section address a single approach, and assume the following:

           

          • The mirrored SQL Server session includes three computers: a principal server; a mirror server; and a witness server.

           

          • Identical SQL Server editions are installed on the principal and mirror SQL Server computers (SQL Server 2008 Standard) using an instance name, while SQL Server 2008 Express edition is installed on the witness computer.

           

          • PI AF Server is installed on a separate computer from those used in the mirrored SQL Server session.

           

          • The PI AF client is installed on a computer that does not include the PI AF application service or the PI AF SQL database.

           

           

           

           

           

          Install PI AF Server on Microsoft Cluster Service (MSCS) (page 41)

           

           

           

          OSIsoft assumes that you are familiar with the configuration and operation of MSCS, and with the cluster administration tool for your operating system:

           

          • Windows Server 2008: Failover Cluster Manager

           

          • Windows Server 2003: Cluster Administrator

           

           

           

           

           

           

           

          Manage PI AF Collectives (page 65)

           

           

           

           

           

          PI Server collectives and PI AF collectives are mutually exclusive; you do not need a PI Server collective to create a PI AF collective or vice-versa. Neither the primary nor the secondary PI AF server need a PI Server installed.

           

          A PI AF collective uses SQL Server replication to copy data from the primary PI AF SQL database computer (publisher) to each of the secondary PI AF SQL database computers.

           

          Each secondary server communicates with the primary server through a Windows Communication Foundation (WCF) connection and reports its status information. The server authenticates the WCF connection using a Windows certificate that the PI AF server generates when it is started.

           

          SQL Server replication transmits the primary PI AF server’s certificate to each secondary server. After the secondary server receives the primary server’s certificate, it can communicate its status to the primary server.

           

          When PI AF data is changed on the primary PI AF server:

           

          • The log reader agent sends any changes from PIFD to the PIFD_distribution database.

           

          • For each secondary server, its agent pushes changes to the SQL Server instance on the secondary server.

           

          If the secondary server is not reachable (if there is a network problem or the computer is offline), the agent retries later.

           

           

           

          Let me know if this post is helpful,

            • Re: AF collective implementation
              jfarrera

              Thanks Marcos for your answers, yes I have reviewed the guide, so , we would like to have the AD collective functionality, but do it  connecting both af servvices to the same SQL server database being merrored, is this option possible?

               

              I just made the AF collective , but pointing each af services to a different SQL databases , and even though the collective is created sucessfully, the Sync Status: Snapshot Not Ready, and the AF services are not replicaded, I guess this is caused because the SQL servers are not configured  for repplication.

               

              So, my other question is: To implement AF collective, we have to have the SQL repplication?, Due to security policies, we can't have local groups in the servers so , I dont know if a domain group called AFServers could work for the replications mechanism? What is the purpose of that group anyway? . Other restriction is the local admin accounts in the servers, IT doesn't allow console admin accounts on the servers, just process admin accounts.

               

              Thanks in advance

               

              Julio

                • Re: AF collective implementation
                  AKolesnichenko

                  Hello, Julio.
                  Our task is to set up replication for the database PIFD without AF Collective. Attached is the replication scheme, which we need to implement (do not pay attention to the database AFDATA).

                   

                  0535.SQL_5F00_repl.png

                  1) First, we tried to set up replication snapshot means SQL Server. PIFD successfully replicated databases, tables dbo.AFDatabase, dbo.AFElement replicated database and display elements. But in the PI System Explorer display only the replicated database and templates in them. The items in these databases were missing. It was very strange. We tried to configure transactional replication means SQL Server - the same result.

                  2) Then we found in the database PIFD procedure to create publications and subscriptions (apparently, they are used to create AF Collective). And they performed manually by substituting the required parameters. In this case, the database PIFD successfully replicated and in the PI System Explorer displayed all replicated changes. But after a while repliktsiya on one of the subscribers is no longer fulfilled. In Replication Monitor outputs the following error:

                  Command attempted:

                  if @ @ trancount> 0 rollback tran

                  (Transaction sequence number: 0x0000547A000011F6001900000000, Command ID: 1)

                  Error messages:

                  The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)

                  Get help: http://help/20598

                  The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)

                  Get help: http://help/20598

                  Thus because of this error, the server started to generate a lot of traffic (with the capacity to 100Mb / s to 80GBayt a day). We wrote in support OSIsoft. We have brought the incident - 480614. Engineer poddezhrki contacted the developers who said that this configuration should work in theory, but they did not check. In this case, no reason or guesses, because of what happened to us is not named. We reinitialize the subscription. At first everything was fine, but then fell again subscription with the error. At the other server replication continued to run successfully.

                  Now we are trying to find a way to solve this problem. But as long as we do not get anything. Afterwards we start to appear more on the same subscriber the following error:

                  Command attempted:

                  if @ @ trancount> 0 rollback tran
                  (Transaction sequence number: 0x00006C6500000D3B002F00000000, Command ID: 1)

                  Error messages:

                  · Cannot insert duplicate key row in object 'dbo.User' with unique index 'UQ_User_UserSID'. (Source: MSSQLServer, Error number: 2601)
                  Get help: http://help/2601

                  · Cannot insert duplicate key row in object 'dbo.User' with unique index 'UQ_User_UserSID'. (Source: MSSQLServer, Error number: 2601)
                  Get help: http://help/2601



                  Julio, you can try the option that we are trying. Something or someone can help us, because we are fighting for a month with this problem and as long as it can not win. Technical support also can not help you ...

                  PS. I'm sorry for bad English

                    • Re: AF collective implementation
                      pcombellick

                      Julio,

                       

                      Our Tech Support team can help you setup an AF Collective via PI System Explorer.  Generally, AF will setup SQL Server to perform replication and you do not need to setup replication using SQL Server tools yourself.

                       

                      AF does not support SQL Server replication that you setup manually.  There are important replication issues that you have to handle that are handled by AF when AF sets up SQL Server replication.

                       

                      It is not necessary to use a local group to grant privileges to the account that the AF Server service runs as.  Our Tech Support team can help you setup SQL Server so that the AF Server service can connect.

                       

                      Regards,

                       

                      Paul Combellick

                       

                      AF Dev

                        • Re: AF collective implementation
                          pcombellick

                          Александр ,

                           

                          The AF replication stored procedures should only be run by AF logic.  There are additional, undocumented, steps to setting up an AF Collective in addition to setting up SQL Server replication.

                           

                          The sql error messages that you describe, appear that you have configured an AF Server to write to a Secondary SQL Server.  This causes replication to fail.  This configuration will probably not work.  AF does not support multi-master (bi-directional) replication.

                           

                          I recommend that you setup an AF Collective via the PI System Explorer, then add your 3 secondary servers to your AF Collective.

                           

                          Regards,

                           

                          Paul Combellick

                           

                          AF Dev

                        • Re: AF collective implementation
                          jfarrera

                          Thanks Александр for the detailed explanation,

                           

                          We were able to implement the AF collective using  SQL replication. But what you are doing looks interesting

                           

                          Thanks

                           

                          Julio

                    • Re: AF collective implementation
                      pcombellick

                      Julio,

                       

                      "What we want to achieve is to have a desaster failover having a pair  in two different locations"

                       

                       

                       

                      You might want to consider SQL Server mirroring for disaster failover, since an AF Collective secondary is always read-only.  Unless read-only is acceptable to you in your failover scenario.

                       

                       

                       

                      Regards,

                       

                      Paul