In Configuration Manager 1702 there is a new feature /site system role(pre-release) called Data Warehouse. This is a great addition as I cannot count the time I have setup and configured another database and then on a schedule moved data to that Database instead to be used both for historical data and by other systems that shouldn’t query our precious Configuration Manager database during production hours.
Many times, performance issues in Configuration Manager has been caused by developers querying the Configuration Manager database with really bad queries causing the overall performance being degraded.
In Configuration Manager 1702 the Data Warehouse feature holds all the answers to those issues. With the Data Warehouse Service Point role we can transfer SQL data to a another SQL database. That server doesn’t need to have the same high-spec as the Configuration Manager Database.
When we configure the Data Warehouse Service Point role we set a Schedule on when the data should be transferred to the Data Ware house and how often. Adding the Data Warehouse service connection point. At is it still a pre-release feature you need to opt-in to using pre-release features, that is done in the Hierarchy Settings.
To add the Data Warehouse service point we do add the Data Warehouse Service Point role to the server that should host the role.
We add the SQL Database Server Name, database name and Port to be used.
We can then configure how often it should synchronize the data.
We also get a couple of new reports that will show historical data from the Data Ware house database which are cool and useful as well if we have compliance rules applied to our business. No more exporting data at the end of each year to .CSV files for historical compliance reporting and Endpoint protection and software update compliance.
When configuring the Data Ware house don’t forget to grant the Reporting Service User account used in Configuration Manager “Data Reader” role permissions to the Data Warehouse Database, otherwise this message will show up when running the reports.
We grant the SQl Reporting Service user account the data reader role.
After granting the Reporting Services user account permissions to the database the reports now run as they should.
The Data Warehouse role is a great feature so you should try it out!