web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Recommended HQ Server ...
Microsoft Dynamics RMS (Archived)

Recommended HQ Server configuration

(0) ShareShare
ReportReport
Posted on by Robert Angell 92

We have 160+ stores connecting to a single RMS HQ installation. 

Until recently we had a single HQ Server installed and all 160+ stores were configured to connect to it for all worksheet syncs, including 401 once a day. 

We often experienced performance issues with the syncs and they would often run through the night and into the next day, resulting in incomplete reporting availability at HQ and poor performance while syncing at the stores. 

Review of various community posts here lead us to install a further 3 HQ Servers.  The reason we chose a total of 4 was that we have 4 vCPU assigned to the RMS HQ Database server.  Overall vCPU usage during the nightly syncs is rarely more than 25-30%. 

The idea of having multiple HQ Servers made sense to improve the efficiency of stores syncing, however we're finding now that there is a lot of Blocking occurring on the SQL Server, particularly on the ItemDynamic table. 

Is this sort of behaviour to be expected, or can something be done to reduce the SQL Header Blocks and Waits caused by blocking of the ItemDynamic table?

I'm considering increasing the sync frequency of the stores to try to reduce the amount of data that needs to be updated with each sync.  I'm not sure that it will put any extra load on the store side database and cause performance issues. 

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    If you look at the Item Dynamic table, there is a trigger to update item inventory details in item table when communication happens. This trigger sometime also causes for communication failure when you run 501 with there are a lot items in your DB.

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    You will not be able to reduce the blocking unless you use seperate databases.  This is by design because one sql instance can update the HQ database at a single time.   I don't recommend splitting the database.  - This is called Sharding

    As far as the store, you can increase the update frequency.  This would help to resolve the massive update at the off hours.  It is always recommended to pick times of the day with the lightest business load.  Most of the data load will be sales data from the store to the HQ database, so shouldn't cause a slow at the store level.  But I recommend you try this with a few test sites prior to a change to all stores.  This way you can keep an eye on the performance of those stores.  

    The other aspect you could investigate is IO disk speed.  SQL Server is memory and disk intensive.  Especially with updates from that many stores.  You could move to a high read/ write speed disk array.  Something like a RAID0 arry of SSD drives.  I recommend entreprise grade disks/SSD for your implementation.  

    Disk throughput is critical to a fast and responsive sql server.  

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Hi Robert,

    As suggested by ed, increasing number of connections from stores to HQ will help, we recently started spooling data hourly by running 4 HQ servers for 70+ Stores and currently its working fine.

    We do not see any blocking as we do not update high number of items from HQ to stores. Also we issue 250 worksheet to stores after trading hours.

    Sometimes we are facing Dayna zip error @ HQ level which is really irritating as it blocks all the store connections to HQ. We ensure to keep the HQ temp folder clear but sometimes it appears from nowhere.

    When we updated the RMS to FP2 , hotfix was not available to fix this issue, however MS has added the fix in CU5 but as you know updating RMS version is not a easy task!. If you are planning to increase the frequency and have not applied CU5, clear your HQ temp folder frequently.

    Let me know how it goes!

  • Robert Angell Profile Picture
    Robert Angell 92 on at

    Thanks for everyone's suggestions.  It's been a little over a month since we implemented the 3 extra HQ Servers.  As Ed explained, we still see the ItemDynamic table locking so have accepted that as a fact of life.  Overall, 401 syncs have been much better and reliable.  We sometimes get a Dynazip error, but it has only ever affected 1 HQ Server at a time so it is much more manageable when it occurs.  If only we could find a way to monitor for the Dynazip errors!

    We started to investigate performing per regular 401 syncs throughout the day and we found that the step where Global Customer updates are synchronized down to each store takes 3-5 minutes per store (this is the time taken to write the updates into the Customer table).  The load placed on the SQL Database and disks at each store during the customer sync is enough to make each POS struggle to process transactions and they often result in SQL Timeout errors.  

    We have approximately 1.2 million customers, with around 30-40,000 being updated each day.  We run nightly Index Rebuild operations on all tables in the database to help maintain efficiency, however we still get poor write performance when syncing customers. 90% of our PC's that run the local store SQL Database instance use RAID 1 hard disk configuration, albeit with SATA2/3 disks, so not as high spec as SSD, but still better than a single disk.  

    I've also run a SQL Database Tuning Adviser session to review the sync and it indicated no further SQL related tuning would achieve any benefits.  

    Is there anything that can be done to perhaps remove the Global Customer sync and have it only run once per day, or something like that?  If that were achievable then we'd happily schedule 401 syncing to occur throughout the day.  

    Any thoughts?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics RMS (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans