I am working on a sql server that is a Central Management Server and has one local server group within the database engine. On that local server group instance I've noticed that there are tables that have been created (not temp tables, actual tables) on the model and tempdb due to the Microsoft Assessment and Planning processes. Generally speaking those tables (ex: AllDevices_Assessment.CategorizedDevices, AzureMigration_Assesssment.AppConfig - these are MAP created tables and there are well over 200) shoud not exist in the model or tempdb, correct? Is there a reason they would need to be in both model and tempdb? Just doesn't seem like a best practice, but I don't know enough about MAP to really know for sure.
Answer by Kev Riley ·
When you install and use MAP it will ask you where to store it's inventory database. You can either create a new one, or choose an existing one. Sounds like someone has chosen model, which is not a good idea.
The model database is the 'template' by which other new databases will be created from, so any objects in here will then be created in new databases too. In fact tempdb does the same, so the reason you are seeing those objects in there is that on the last restart, when tempdb was recreated, it took those objects too.
I would want to remove all those MAP objects from model. If someone wants the MAP data, then keep hold of a backup so that when they come looking for it you can say "it shouldn't have been there, please don't do that again, but here's a backup"