After having published a blogpost on Master Data Services in SQL Server 2019, I have published a series of tweets expressing how much it hurts to se bad configurations of MDS in SQL Server 2019, I was encouraged a couple of people to blog about so more people will understand this problem and hopefully, at least some of them will get solved before the RTM of the SQL Server 2019.
You might ask me if I have ever contacted the Program Management Team on this topic, and my answer will be surprising:
– YES. Times after Times. Starting from many years ago.
That’s pretty much enough for an introduction, let us see some of the items (I am not pretending this list to be complete, but those are definitely driving me crazy):
Database Compatibility Level is set to 110 (SQL Server 2012)
I honestly do not know where to start. 2012 in a 2019 product ? Really ? Seems like a general exaggeration! Besides the little fact that all the juicy Query Optimiser improvements since SQL Server 2016 (and that would be compatibility level 130) are hidden behind the 130 level. This means that all those bug fixes, all the IQP (Intelligent Query Processing), Query Store, Optimised Plans, Batch Execution Mode on Rowstore, etc ).
Page Verification
I choke and cry every single time I see the default page verification set to TORN_PAGE_DETECTION for page verification. To my knowledge your chances to get a corruption unnoticed are going right straight through the roof and even before SQL Server 2005 (that’s 13 years ago) there was an official post recommending to NEVER use the TORN_PAGE_DETECTION and set everything to CHECKSUM …
Consider this – since SQL Server 2005, if you create a new database – it will be set to CHECKSUM by default … There must be a reason … There must be some kind of a reason …
Compression
We officially need to be able to start using the Columnstore Indexes – if you have millions of items (and there are enough shops like that), there are no huge reasons to avoid them.
And this should be supported. Plus custom indexing should be a real thing, since we are obliged to join with the data in the different databases and our real-life queries might have very specific necessities.
Database Design
Yeap. Uniquenes, Referencing … Those weird things … That leads to a consistent Data Quality as it is expected in the Master Data Services.
File Growth Settings
Is there anyone expecting their database to be 72 MB in the size and have 8 MB of transaction log ? In the year 2019 ? I do not care what are the limits of the default database creation are, because those defaults are definitely ignored by the MDS Product Management Team (otherwise no TORN_PAGE_DETECTION or Compatibility Level 110) would be in the final product.
Please, Please, PLEASE !
Put some reasonable defaults starting with at least 100MB and the respective growth should be at the very least 50MB.
Note: please do not start responding that some ISV might create thousands of databases, because if you have ever used MDS, you would know that a single one can easily occupy almost 100% of the CPU out of 8 cores, while still being painfully slow.
Query Store
Just enable it with the most basic defaults, for the sake of the humanity!
Impossibility to use the Secondary File Group
This seems to be a common problem, but in the year 2019 this one should have been addressed. From time to time someone will present at the conferences on the Piecemeal Restore option (partial restore of the database), and everyone will agree that the Primary Filer Group should be as small as possible so we can start getting a huge database back online as soon as possible and at least some of the functionalities will become available – but hey, I would not even dare to hope that this will get fixed anytime soon.
And yes, I do care that it is impossible even in Query Store, but I am focusing on the product that is 10 years old.
Parametrization
I would like to hear the reasons and the good reasons for the Forced Parametrisation.
Final Thoughts
Dear someone, who cares about the Databases and Query Optimisation at Microsoft – please, please, please!
Help this items to get fixed before the RTM, or at least some of the most blatant of them, such as Compatibility Level and Page Verification options!
Thank you in advance from every single loving and forced MDS user!
I’d like them to at least export / clone the audit history during an MDSModelDeploy operation. If you live in an audited environment, in SQL Server 2016 this is a showstopper
Hi Jim,
there are so many things that MDS is missing … Given that the Profisee went so far away, and that Microsoft is not updating the MDS engine since 2016 with no apparent news for SQL Server 2019, I am asking myself how alive is MDS at all …
Best regards,
Niko
Hi Niko,
last thursday i applied the march 2021 windows updates and the MDS DB needed an upgrade after that. So far so good, made a backup from the MDS DB and went for the Upgrade. Everything fine. On Monday I got a call that several things are not working anymore …. business rules cannot be applied, permissions on stored procedures were gone – strange funny stuff which eats a lot of hours …. and still does not work.
Did you experienced that issues as well?
Would be good to hear from you.
Thanks and regards Michael
Hi Michael,
no troubles with MDS so far.
I suggest you to open a support ticket with Microsoft ASAP.
Best regards,
Niko
After upgrading from 2016 to 2019 the only issue so far is hierarchies had all their levels removed into the unused levels area. What the? The hierarchies still work and each level reflects the level name but the unused shows all the levels and the hierarchy itself no longer shows them as actual values. Most disturbing. And yes the compatibility is still 110. Should I alter it to 130? Documentation says they support 2016 db and earlier (for the 2019 version of MDS). I’m not getting warm fuzzies here. I actually love MDS and very afraid support is gone for it.