I had a similar experience with countless hours connected with support yielding no functional solutions. As a note, since updating  from 18.2.2 to 18.4.1 we haven't tried to implement this again, but I do not remember this being a big talking point of the release.

Alternatively Anterra has a much more stable product, but (aside from a small exception) I currently just replicate data daily.

Steven,

I would be interested to see what responses you can get from this.  We tried the replicator for the first time in Version 18 in Live mode, and I had to kill it.

For us, it was completely locking up the SQL instance, which is well overpowered for the task.  The Sage instance showed no noticeable performance hit, but if the synchronization was in the running state, we couldn't login to the SQL box anymore.  If I disable the synchronization, the server immediately returns to its normal state.

In the meantime, we have fallen back to our SSIS packages to synchronize data between Pervasive and MSSQL, but I was hoping that I would be able to retire the maintenance of those tools with the Live feature from Timberline.

Here's hoping that we can get some answers on what is happening under the covers of this particular tool.

Thanks,

Jay

Hello Jay,

Could I ask what you are referring to with the SSIS packages as an alternate to Sage SQL Replicator? 

Thanks

We just set up Replicator on 18.4 and I am not sure the replication is happening either, the SQL Database time stamp is not changing, and we have the same issue as another poster here....when replicating it brings the SQL Database down and is not accessible so no reports can be run off it.  

Jeff,

SSIS stands for SQL Server Integration Services.  If you have a license for Microsoft SQL Server, it is a subcomponent that you can use.  Ultimately, it lets you create data pipelines between a source and destination system.  It's a tool that I have used for years building Business Intelligence solutions.

It can be a bit of a difficult solution to learn at first, but I like the flexibility that it offers.  Basically, I use the ODBC connector as a source, I perform different tasks in the middle like adding tracking tokens and reporting on statistics, and then I use an OLEDB destination to point back to my MSSQL instance.

It was a solution that I built 3-4 years ago that has been extremely performant, and has only gotten better as Sage has improved the processing performance of the ODBC connector.  One of the other reasons that I chose it is that it can batch process. If you have very large tables, which we do, then it avoids having to load the entire source table into RAM to perform the synchronization.

Thanks Jay.  I was afraid it was something specific to SQL licensing.  We are not licensing for SQL and trying to pull this off with a SQL Express instance.  I am seeing the same issue as you in that the SQL Instance is "off line" when replication runs so that eliminates any chance of using live and really limits the actual replications capability to night time when no one would need it.  As I also mentioned in another post I am not even sure it is replicating the data despite the log showing as such. I have to test that a bit further though. 

Hello,

So can I assume from your post you are using the Anterra product? Is the setup and maintenance fairly straight forward and capable of being handle by a small shop?

Thanks 

Jeff,

I was able to confirm that replication is in fact taking place, and it works properly, except for the resource hit.  If I turn it off, and then go and query the records in the MSSQL instance, the data is all there.  The downside is that as soon as I turn on live mode, I can no longer connect to the instance at all.

Our total database is over 50GB, so we have a large instance, but it is pretty strange that it is completely consuming 100% of the resources on the MSSQL box 100% of the time if it is running and then immediately frees up those resources as soon as I disable the job.  Since my CPU is basically pegged, I cannot run a Profile to see exactly what is hitting the box to try and troubleshoot and narrow down the issue further.

Here's hoping they can shed some light on the situation.

We used Anterra for a while, and had 0 issues with their team or the product.  Once everything was configured, it was set it and forget it, unless you had to do a Sage update.  If the database schema changed, Anterra would have to update their sync tool as well, but they handled all of that for us.

Great partner to work with if you are looking for either a replication solution, or their complete BI suite.

I have had much better luck with Anterra over SQL Replicator. I don't know how it would do with a replication live or every 15 minutes but for a daily SQL copy we have a stable setup. If you are using this to build dashboards or something MS SQL is clearly a better answer than the performance you get from the applications database.

There are a few considerations with the implementation, such as how you would like multiple Sage 300 CRE data folders to appear, but during initial setup support connected and went over these considerations along with basic testing. For ongoing support I have been able to reconfigure the application on my own. As long as you or someone is comfortable with data sources I would think this could be accomplished. Beyond this if you have issues they have a support email, and I have had great luck with the team that monitors this.

We purchased this years ago, so I don't remember exact licensing costs, and I know there is a yearly maintenance.

I am not affiliated with them, just a satisfied user.

Jay,

In my case I have not routed anything to use the SQL data yet and was just looking at the file level time stamps of the SQL Database not changing for my wonderment if whether any new data is being replicated over.  I am going to try and point a report to it this weekend and see if the data is updated.   In my case I am not having an issue with CPU on the SQL server vs. the whole database literally goes off line when a replication is running.  If I have an odbc connection to it pre replication I can see the data but during replication the database is not available and I cannot connect to it until the replication is done.  Each replication takes 45 min for my approx. 8GB Pervasive DB.  I did see a note somewhere in Sage's documentation stating that the DB is "unavailable" during replication. 

One other thing I wanted to add to this thread is that when watching the non live replication's take place it goes through the "Upgrading database" process each time.  That is surprising to me and suggests an issue with that alone. 

Thanks very much for your reply.   If we cannot get this to work successfully I may have to explore that though not sure our budget will like that idea. 

Hi Steven. I checked in with one of our replicator experts here at Sage and they advise upgrading to 18.4.1, if you are not already on that version so you have improvements that have been made in performance. Then contact support for assistance in clearing out old replicator data and starting fresh. I hope this advice helps. Terry

I upgraded our system from 17 to 18 but the only way I could get the replicator to work correctly was to delete the 17 version replicated data and create a new one. Before this the data didn't change and I would see error messages in it. At least now I can verify changed data is in our file. I'm trying to run mine as a schedule service but it always shows the status as replicaing. I have also done it as Live but this occassionally causes record locks espescially with Payroll. I haven't had time to properly check the different methods.

Thank you Terry for your response. I'm finally able to circle back on this after a few days off. We're running 18.3.1, so we'll try this and see where it gets us. I see one or two other users in this post that say they are on 18.4 and still run into issues, so I won't hold my breath. If this doesn't work, I'll follow up on this post and let everyone know how it goes.

Is there any news regarding an 18.5 that may further address these issues, or even better, when a full SQL version of Sage 300 CRE is ready to be released? We can't deal with PSQL anymore.

Hi GraniteStateGlassSage . One of our analysts reached out to you yesterday to work with you and will be checking back with you today to see how things are going. Are there other questions that I can help you with for now? 

Since this seems to be the place where all the Replicator stuff is going I am going to throw out one more issue I have seen.  I created a local sql user, read only, so another part of our company can access the replicated data to run their own reports.  Worked great until the first time I hit the sync security button after creating a new user in Sage.  It took this sql only user and put a red x beside it though it is not actually disabled.  I have tried several things to get the user to reactivate but have not found the fix yet.....it seems the sync security piece destroys access for any none Sage based user except the sa account. 

Jeff, I had something like this occurring and am interested if you or someone finds a cause/solution. This issue actually is what led to us stopping our SQL Replicator and the Mobile Dashboards setup over replication issues. Our setup is a little complex with domain trusts and such. Possibly because of this troubleshooting with support got us little results. I was able to identify failed SQL logins, but with the replicator running I did not ever get this functional. Looking forward to finding any further info.

Hey Jstone-

Will just list out what I have found so far as it is so convoluted I am not sure I have it all down yet:

1-Any non Sage/Windows based users are disabled in some manner when a sync security is run.

2-I am finding that even new Sage/Windows based users do not work just from the sync.  Their user account is created in SQL but the password does not work (and I did log into Sage as them first per the instructions) so I have to go into Sage Studio and set their password.  In addition it does not add the proper settings to the Securables section of their account so I have to manually copy what is in another users that is working.

3-As I have stated before when the sync is running the database becomes inaccessible because all the user accounts seem to be rewritten from new each time.  If you go into Sage Studio while a sync is running you will see all users have the red x on them. 

4-It seems that any further security changes do not break the manually fixed accounts from step 2 so fingers cross we have a sort of duct taped solution to use it for now.

So in summary I have it working knowing that when I create a new Sage user (they require Windows accounts as well and that is documented in the Replicator setup) I have to log into Sage as them first, then go back to the server and do a Replicator sync then go into Sage Studio and fix the stuff that is not right from the sync.  After doing all this it works as expected.

Testing all this is EXTREMELY time consuming since syncs take between 50 and 70 minutes each. 

Good day everyone! Sorry I've been MIA on this post for 2 weeks as I've been slammed scheduling-wise. I've been working with Suron Woods (awesome guy to work with) from Sage off and on regarding this issue, and for everyone's knowledge, I'd like to outline what we've done so far and what issues we're still having.

Fixed issues pertaining to our setup:

1. Suron pointed out some authentication struggles with our setup. I'll do my best to remember all the details but what we ended up discovering is that if you have a Sage account that matched a Windows Auth user account in SQL, these two accounts will conflict, causing some replication issues. For example, if a user's Sage account name is JDoe and their domain login is also JDoe, SQL Replicator tries to create a SQL login matching the Sage username, but if there's already a Windows Auth account in SQL matching username (even with the domain\ in front of the name), it sees it as the same account and fails to create the account, thus causing authentication issues. Our fix for this was to change the name of the 1-2 users matching this case within Sage itself. We only had to do this for users that needed to authenticate with the SQL database. Those that used Sage but never needed to pull data from SQL did not need to be touched.

2. Our PSQL database was about 10GB. Replication to SQL will expand this data due to overhead in the replication process, so it's expected the size of SQL is 1.6 - 2.0x the size of your PSQL database. In our case, our 10GB PSQL database grew to be 45GB in SQL! This was the first indicator something was wrong. Suron had to go in, delete the old database (we detached it just in case rather than deleting it), clean out some other Sage-specific things in SQL (not sure on the specifics), then kick off a fresh replication into a fresh SQL database. This ran overnight and the following morning, we had a ~14GB database instead of a 45GB one.

3. Suron installed a very new tool that monitors the health of replication, detects if there's been no changes after 20 mins, then will work to restart the replication service on its own to make sure it continues without manual intervention. He stressed this is super new and still in testing, so this is more of a band-aid than a fix, but I wanted to at least mention it.

4. Terry, regarding your instruction to upgrade from 18.3.1 to 18.4.1, we ended up not doing this because according to Suron, there were no updates to the replicator between versions, so there was no reason to update. The version we're on is the most reliable at this time, and many of Suron's other clients he worked with have said upgrading to 18.3.1 have fixed a lot of their issues already.

Remaining issues we're having:

1. Since doing the above three fixes, our Live replication has been much more reliable, however we're not out of the woods yet. I'm still finding the SQL replicator switching from Live replication mode to Scheduled mode on its own every 2-4 days due to "Downshifting" when Live replication can't catch up. We don't know why it's downshifting... could be CPU, RAM, network... something. Our server is a VM with 8 vCPUs, 32GB RAM, and 10k SAS drives in RAID6. Our SQL database is right on the same server, so network speeds are not a factor. I doubt we are running out of resources for replication to run, so we're working to see why this is happening. The health monitor Suron installed is not helping this situation as the monitor only checks to confirm Live replication isn't hanging up, and since the mode will shift to Scheduled after a downshift, the monitor stops monitoring the replication status.

What the future holds:

- Side note for everyone, Suron let me know that their developers are working on releasing "SQL Replicator 2.0" in the next few months (take that with a grain of salt, it's all uncertain right now). This 2.0 version is a complete rewrite of the existing replicator with the goal of fixing all of our problems with the current one. The stakes are high on this version fixing things because Suron also mentioned to me that it's very unlikely we'll ever get a full SQL version of Sage 300 CRE because the original code is so poor. They would need to rewrite the entire problem, which they're not going to do. Instead, they're working on a cloud-only version of Sage 300 CRE. This new SQL replicator is as close as we're going to get when it comes to CRE being SQL-based.

I'll update this as things unfold, but so far, that's the status of everything.

PS: I didn't proof read, I spend more time on this than I thought I would, sorry if some things don't make sense, haha.

Hello, thanks for your long run down of what you went through.  We found the same issue as you with Sage/Windows accounts but only for SQL accounts we created manually. Replicartion faield if there was a dupe account. 

The replicator does create a SQL account with the same exact name as our users Windows accounts during replication.  Unsure if you read the related security issue we have seen and confirmed that when a replication happens it disables any SQL created accounts except the SA account.  So if I go into SQL Manager and create an account out side of Sage, during the next replication that account is disabled and cannot be reused as I have not figured out how to enable it again.  Even if you delete it and try to create a similar account in Sage the Sage replication will fail and say the old enabled account is still there when it is not.  In addition we have found the replication only properly creates SQL accounts on first replication.  On any subsequent replications where a new Sage user was created it creates a matching SQL account but does not properly apply the password nor does it give it the proper rights to the replicated DB.  One has to go into SQL Manager and manually set these properties on the new user.  There is a statement in the Replicator instructions that speaks to something similar to this saying a new user must log into Sage before replication but I have tried that and it did not help the situation. 

We are on 18.4 so even though you were told it is no different it could be different.  The issues I have were confirmed to exist in 3 other 18.4 replication scenarios (one person spoke about it here...Jstone).  So if you have your replication and security working I would not upgrade to 18.4.

Hell Jeff,

     We are using SQL Replicator 18.3.1 and I have the same 4 issues you have reported.  I am comforted to see that I am not the only one logging onto Sql Management Studio to manually set passwords for users so that they can access reports when SQL is enabled.  Not all users require manually synchronizing their SQL account password with their Sage password, but there are enough that I have to make this part of my process for transitioning report to using SQL vs PSQL.  

     I have also had some complaints about SQL reports returning with no data.  I found this article on the Sage Knowledgebase referencing a defect ID 542119.  As a workaround, I have been creating a new report instance that uses the same report file but has the SQL option enabled so that when the SQL report fails the PSQL report can be run.  

     I was previously running the synchronization using the Live option, but we are changing to this to a scheduled nightly replication to see how that impacts SQL report performance.  The decision to use the scheduled replication is also related to records being locked by the replication process when we run payroll. This is not an optimal solution since our users will want the most current data, which they will have get by using PSQL which takes almost twice as long to run vs SQL reports.

Hey guys, Just swinging back by to say I have had FAR better luck with Replicator/Mobile Projects after updating to 18.4.1.