Actually I really like the idea of the MDT Database and the flexibility it brings into your deployments. But there are a couple of things which are, let’s say not optimal, and some drove me nuts during the creation of the web frontend.
Combined Primary Keys change during time
The “Settings_Administrators”, “Settings_Applications”, “Settings_Packages” and “Settings_Roles” tables are all built according to the same schema. They have a combined Primary Key which consists of three different values:
- Type – Type of the Identity. Typically either “L”, “R”, “M” or “C”
- ID – The unique ID of the Identity
- Sequence – Identifies the position in a sequence of the Application, Package, etc. for the specified Identity.
Actually not bad. I personally prefer to have a single Primary Key, but combined Primary Keys are also useful. And they fulfill the main requirement for a Primary Key to uniquely identify an entry.
Now assume, you have five Packages assigned to a computer. You delete the last package. No problem at all.
You delete the second package. Also no problem at first but now you would have the sequence of 1, 3, 4. That’s bad. So additional to deleting the entry you also need to adjust all following entries into a continuous sequence again. And here comes the “problem” I mentioned, you need to change one part of a Primary Key.
OK, even worse. We reorder the packages and move the 4. package to the 1. position. What does this mean from the Database perspective? You can’t simply change the Sequence as another entry with this combination already exists. So if we try to change the “4” into a “1” the database won’t allow us to do this as there is another identical Primary key.
I know, SQL as a standard doesn’t really prohibit to change a Primary Key, but experience tells that this can cause some trouble later. So generally it’s a good recommendation to have Primary keys which are immutable. And some SQL DBMS solutions doesn’t even allow you to change a Primary Key.
In the MDT Web Frontend I used the ADO.Net Entity Framework to access the MDT Database. It creates a layer between the Frontend code and the Database itself so that you can program against a Model and don’t have to deal with all the Database specific parts (I will post some examples from the Frontend in the upcoming weeks to show how nice it can be to program against a model instead of the database). But as this is a generic way of accessing Databases it did what I mentioned already, it doesn’t allow me to change a Primary key as it assumes it immutable.
So I ended up in caching the values, deleting them from the database, change them as necessary and adding them again as new entries. And even if your are able to change parts of the Primary Key directly, it needs some additional logic to be able to “reorder” a Sequence.
References by name
OK, we go back to the structure of the already mentioned tables. The fourth column in each of this tables now contains the real “value”. This value is always a string. For Administrators, Applications and Packages this might be OK as there is no Identity table we could reference. It’s an easy solution allowing you to e.g. add a package to a computer without specifying this first in the database. Having additional tables for those settings would ease some tasks like updating all instances of a package (e.g. updating Office 2007 to Office 2010) or deleting an Administrator from all identities he has been configured, but would also increase the administrative overhead. That’s probably more a nice-to-have (Actually my Frontend is able to solve this problem for you ;-) ).
Where I have some difficulties with is, why the same has been chosen for the “Settings_Roles” Table. There is a Table “RoleIdentity” so each Role can be uniquely identified by it’s ID. And the Views like “ComputerRoles” could easily be changed to show the name of the Role instead of the ID if they have a relationship. And as the standard queries used within the CustomSettings.ini (and you should also stick to this in your custom queries) are using the Views in preference of the underlying tables this wouldn’t even have any effect on the Client side.
But this configuration makes some administrative tasks harder then necessary. Just imagine renaming a Role. You not only need to rename the Role itself, you need to also rename all instances used throughout the database. OK, this task is typically done by the Deployment Workbench so that you don’t have to deal with this but if you ever need to make such changes directly in the database be aware of that.
Deleting a Role doesn’t delete it completely.
What do I mean by that? If you delete a Location, MakeModel, Role or Computer a Trigger in the database takes care that also all referenced values from the before mentioned tables are being deleted. This works well for most of them, but Role is really more a hybrid. It’s an Identity you can configure with additional settings and it can also be assigned to other Identities. So the Trigger for Roles is deleting all references from the role itself, but not the references to the Role configured in “Settings_Roles”. So if you configure a Computer to use this Role. And later this Role is deleted, this Role will still show up in the Table “Settings_Roles” and even in the Deployment Workbench. Just give this a try yourself.
It won’t actually hurt anything as all database queries simply don’t return any results for this but it is an inconsistency. OK, getting into to much self-marketing now, but again, the Frontend will take care about this for you. If you like you can also use the following snippet to update the Trigger in a way to also delete those instances. Use this on your own risk as it changes the behavior of the original implementation. I will not be reliable for anything that happens due to it’s usage!
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[RoleIdentityDelete]
ON [dbo].[RoleIdentity]
AFTER DELETE AS
declare @id int
declare @role nvarchar(255) SELECT @id = [ID] FROM deleted
SELECT @role = [Role] from deleted DELETE FROM [dbo].[Settings] WHERE [Type] = 'R' AND [ID] = @id
DELETE FROM [dbo].[Settings_Packages] WHERE [Type] = 'R' AND [ID] = @id
DELETE FROM [dbo].[Settings_Applications] WHERE [Type] = 'R' AND [ID] = @id
DELETE FROM [dbo].[Settings_Administrators] WHERE [Type] = 'R' AND [ID] = @id
DELETE FROM [dbo].[Settings_Roles] WHERE ([Type] = 'R' AND [ID] = @id) OR [Role] = @role
What I found interesting on this Trigger is this part: WHERE ([Type] = 'R' AND [ID] = @id) . It actually assumes that you can assign a Role to a Role which isn’t possible to do with the Workbench, even if this would be a nice feature. The resulting queries would probably become a bit tricky (avoiding Loops, etc.) but I wonder now if the MDT Team plans to make this possible in a future releases or if this is simply a relict of “Copy&Paste” of the other Triggers ;-)