16 Replies Latest reply on Sep 14, 2018 2:59 PM by Earl Fietek

    SQL database replication

    Martin Solem
    Martin Solem Jun 30, 2017 5:20 AM

      Hey forum!

       

      With 2017 PDM Professional, the ability to replicate databases for Read Only queries, using SQL's AOAG technology also became available.

       

      I simply wonder:

      1. Has anyone used this, or tested this?
      2. Was the results satisfying, in terms of daily tasks like:
        • Get Latest Versions
        • Searching
        • Working with PDM Add-in in Solidworks?
        • Display data cards
        • other Read only tasks.

       

      Kind regards

      Martin

        • Re: SQL database replication
          Amos Avery
          Amos Avery Jul 12, 2017 9:56 AM (in response to Martin Solem)

          I sincerely wish someone would chime in on this subject.  We have replicated sites overseas that are frustrated by slow database access.  We are telling them it will get better in 2017 but that's based on the hope that this new feature will actually speed up access.  Any feedback would be appreciated. Many thanks! -Amos

            • Re: SQL database replication
              Tim Webb
              Tim Webb Jul 12, 2017 11:43 AM (in response to Amos Avery)

              Hi Amos,

               

              I have had several PDM clients ask this same question but haven't arrived at a good solution until recently.

               

              You need a test environment to "try this out". I recommend the following to help answer your questions:

              1. Use our PDM environment to test with to avoid the risk of working in your production vault
              2. Replicate our PDM database to your remote location
              3. Your remote users run a series of tests to compare performance gains with your un-replicated database
              4. We then provide your team with a recommended database replication cutover plan

               

              You get to try the technology, gain the knowledge, and work through learn lessons without risking your production vault.

               

              We would love the opportunity to lead the charge on this front with you and work with your team to define the database replication schema to help you recapture lost time and money from your overseas sites' slow database access.

               

              Tim CEPA

              equivaQ - SOLIDWORKS Solution Partner

              Learn more here Believe in The Q!

            • Re: SQL database replication
              Pete Wetzel
              Pete Wetzel Jul 13, 2017 5:07 PM (in response to Martin Solem)

              In response to this question I have finished setting up a test environment with 2017 and AOAG SQL replication. I have shown with most commands that I gain 50-70% performance with this setup. Brings the performance inline with what users see when they are in the same building as the main SQL server. As it is with the current setup, the farther away from the replicated SQL database a user gets they will see a drop in performance. Overall I am impressed with the performance but it is costly for the needed SQL licenses.

                • Re: SQL database replication
                  Martin Solem
                  Martin Solem Jul 31, 2017 3:23 AM (in response to Pete Wetzel)

                  Thank you for sharing Pete!

                  Can i ask where the sites were located in your test environment?

                    • Re: SQL database replication
                      Pete Wetzel
                      Pete Wetzel Jul 31, 2017 11:57 AM (in response to Martin Solem)

                      Martin,

                       

                      Main DB was in Minneapolis MN. Replicated DB was in Boston MA. Ping time from Boston back to Minneapolis was between 38-46 ms. I would not consider this to be slow performance, but the user experience was night and day with SQL replication in place. I have not tested it yet with locations in Asia, but I imaging the performance gain will be dramatic.

                       

                      Pete W

                        • Re: SQL database replication
                          Martin Solem
                          Martin Solem Aug 1, 2017 2:21 AM (in response to Pete Wetzel)

                          Pete, thank you. This is making me more hopeful that database replication is solving a major problem for companies across continents. My next issue is that many of the companies are so small, or the user-base is so small, that a SQL Enterprise investment is just a mad idea. But with good data and other's experiences at hand, there is a chance that some will still consider it.

                           

                          Martin

                      • Re: SQL database replication
                        Josiah Sansone
                        Josiah Sansone Mar 12, 2018 9:43 AM (in response to Pete Wetzel)

                        Hi Pete,

                        I know this is a late reply to your post but we are about to implement this for our installation as well.  The documentation on the setup/configuration for the database replication is somewhat sparse.  Did you need to install the PDM database service on the replicated SQL server(s) as well? How did the changeover from a single SQL server to replicated go, pretty smooth?

                        I am also trying to figure out if this feature in any way supports SQL failover, so that if our primary database went down, one of the replicated databases would take over.  SQL AOAG supports this with windows failover clustering but I'm not sure that PDM supports this.  I imagine at this point, from reviewing the documentation, that we would have to have true server failover so that the clients would get automatically re-routed to the secondary server when the primary went down, using automatic DNS resolution, since the clients still store the DNS name of the primary SQL database.  And then I'm not sure what would happen when the primary server came back up.

                        Any information you have on your implementation would be of great help!

                        Thanks!

                        Josiah S.

                        • Re: SQL database replication
                          Greg Johnson
                          Greg Johnson May 17, 2018 5:34 AM (in response to Pete Wetzel)

                          Pete,

                           

                          What commands are you seeing the performance improvements? I have a replicated SQL server in the Netherlands, main server in the US and I'm not seeing improvement in my testing.

                           

                          Greg Johnson

                        • Re: SQL database replication
                          Larry Walters
                          Larry Walters Aug 17, 2018 2:09 PM (in response to Martin Solem)

                          All, would any of you be willing to have a phone conversation about your experience with this to date?

                          • Re: SQL database replication
                            Josiah Sansone
                            Josiah Sansone Aug 17, 2018 2:17 PM (in response to Martin Solem)

                            We recently implemented two replicated SQL servers and in our testing, we're seeing some improvement but it doesn't seem like it's nearly what was promised in the SW literature, like Greg Johnson says. We also are encountering some weird issues - users connected to replicated servers seem to get disconnected frequently or periodically freeze up.  If you look at logs from a user on a replicated database, you see hundreds of connection messages throughout the day about the user connecting to the replicated database - it seems like the replication connection is constantly being checked and re-established.

                            We are having Solidworks come in to do an assessment next month to see if there is anything further we can do.

                            Josiah

                              • Re: SQL database replication
                                Ron Michalowski
                                Ron Michalowski Sep 13, 2018 9:56 AM (in response to Josiah Sansone)

                                Josiah,

                                Did you get your configuration fixed or are you sill having the same problems. I am working with someone who is experiencing similar issues.

                                Thank You,

                                Ron

                                  • Re: SQL database replication
                                    Josiah Sansone
                                    Josiah Sansone Sep 13, 2018 10:08 AM (in response to Ron Michalowski)

                                    We are still seeing some odd issues. Windows 7 users seem to specifically have connection issues when connecting to replicated SQL servers.  Users with poor connections to the primary database sometimes lock up and need to either reboot or restart PDM to re-establish a PDM connection.  Users on replicated databases with Solidworks and the PDM-Add-in active seem to have a lot more SW crashes.

                                    Stuttering or periodic freezes occur on users on replicated SQL servers.

                                    It's not all users but definitely some issues with this setup.

                                    Josiah

                                  • Re: SQL database replication
                                    Earl Fietek
                                    Earl Fietek Sep 14, 2018 2:59 PM (in response to Josiah Sansone)

                                    We implemented two replicated SQL servers a few months ago, and we are getting great results searching, get latest and building tree. The only issue we have come across is that the PDM client freezes or becomes disconnected. Earlier this week I changed the Availability Mode from Synchronous to Asynchronous and that seems to have helped. So far we have had no PDM freezes where some users were getting a couple a day.

                                     

                                    Earl