I have recently set up MDW across our SQL estate. There are approximately 40 instances being monitored all sending their data to a cental sysutility_mdw database. I started to run into issues with uploads failing and timing out so I staggered the upload schedules. Each set of upload jobs from an instance still kicked off at the same time (every 15 mins) but only happening once a day. However, wheh I now look at the reports a No Data Availble message appears despite the upload completed without errors.
I then staggered one set of uploads to start 2 minutes apart to upload every 15 mins and every 17 mins and this report is now displaying data again.
If the upload only happens once a day, where has the data gone? Each of these is running in cached mode, so I assumed all data for last 24 hours would just be uploaded, but this is not the case?
Can anyone shed any light on this?
In terms of locking issues on the sysutility_mdw database, what would be the best upload schedule? If I have 10 instances, should the upload times be staggered from each instance so data is hitting the sysutility_mdw database at different times, or is it safe to just stagger the upload times between jobs on the one instance, but upload the data at the same time from all instances? Or should each instance have it's own MDW database?
Hope this makes sense?