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:
Kind regards
Martin
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:
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!
Hi Tim,
Are we talking about the same thing here? With SQL Enterprise and database replication, not only PDM replication?
If so, how do you solve licensing when installing the remote SQL Enterprise server, even for testing purposes, i assumed a license per site was needed?
Hi Martin,
Yes, I believe there will be a CALs for SQL Server for the remote location and I am sure there are a host of other details. I'll talk with one of my guys about how this can be done as cost effectively as possible.
Tim CEPA
equivaQ - SOLIDWORKS Solution Partner
Learn more here Believe in The Q!
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.
Thank you for sharing Pete!
Can i ask where the sites were located in your test environment?
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
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
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.
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
Greg, where in the US is the main server? What's your ping latency between the servers? How are the two sites connected? (Site-to-Site VPN? MPLS?).
All, would any of you be willing to have a phone conversation about your experience with this to date?
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
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
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
Hi Josiah,
Did you ever manage to resolve this?
We're having significant performance and stability issues with clients using the replicated SQL server.
In fact the freezing/crashing issue completely goes away when these users change back to the primary SQL database. Even though the latency to the primary sql server is around 280ms.
Our availability mode is already Asynchronous.
Tim
Hi Tim,
To some degree yes. Windows 7 machines seemed to be particularly problematic, so we ended up just having to run those off of the primary server. Also, we were originally running in synchronous mode, and switching to asynchronous mode helped some of the worse freezes/crashes where the user would need to exit PDM completely in order to re-connect.
The other thing we found was that going into the PDM add-in settings and basically removing all the checkboxes on when to refresh data from the database helped a lot in general usability. Not sure why since those reads should be coming from the secondary databases but there it is.
Also, in conversations with Solidworks support on this, we think that the major problem is that the client is still very "chatty" with the primary server. Every client communicates with the primary server every 15 seconds to check which secondary server it should be connected to. If the lag to your primary is especially bad, then the client gets "stuck" while it's trying to figure out where to go for its reads. So instead of just going to the primary for its reads and then politely waiting while it gets the response, it goes to the primary, checks where it should go for the secondary, then goes to the secondary server for the actual reads. I also wonder (but cannot confirm) if this results in it repeatedly needing to reform the SQL connection (instead of just using a continuous SQL connection while it's active).
Overall, I'm fairly disappointed in the performance benefits of SQL replication. A lot was promised from some SW World presentations and I don't think its there in real world situations. While the PDM browser itself actually seems to work pretty well with replication, the add-in is terrible. And most Solidworks users work through the add-in 70-80% of the time.
Supposedly, 2020 has a completely re-designed the pdm add-in functionality so its supposed to be much less chatty and perform better (closer to just opening assemblies without the add-in open from PDM). So maybe that will improve things. However, we're currently looking at just moving our primary database to AZURE cloud and getting rid of the replicated SQL servers entirely. But we'll see how that goes.
Josiah
Hi Josiah,
Thanks for the reply.
Unfortunately we stuck with the high latency between the sites. It's just a symptom of the physical distance between the countries.
Azure probably won't be suitable for us, the best we could do is split the latency between each site then both sites would have to deal with 120ms to the servers.
The idea of the replicated servers is to cater for high latency and low bandwidth between sites. But so far the replicated SQL server only seems to make things worse.
I will test the add-in settings you suggested but it looks like we may have to pin our hopes on the 2020 add-in being optimised for WAN usage.
If anyone else out there has any suggestions please reply!
Tim
Hi Josiah,
I am currently looking to host our DB server in Azure to see if there is a way to reduce latency globally. Have you completed your implementation, was it successful, and were there any hiccups?
Aaron
Hi Aaron,
Not as of yet. We have a server set up but we're having trouble adding it to our SQL AOAG. The goal was to set it up as a RO server first and see how performance was, then transition it to the primary. We'll get there eventually I guess.
BR
Josiah
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
Hi Earl-
Were you changing the availability of the primary and secondary or just the secondary?
Also, I know it has been years later. Did this setup stay stable for you?
I changed it only on the secondary only.
Yes, this has been very stable for the last year and a half.
Thanks Earl-
May I also know which version you are running?
We tried with 19' SP5, but kept running into an check-in/out bug.
Support told us to roll back to SP4. The bug was gone but we kept running into the freezes.
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