Learning New ConfigMgr Versions at the Database Level

Those of us that have been in the ConfigMgr (SCCM/SMS) business for a while have had the joy (and challenge) of learning the new versions as they’re developed and then released. There are many approaches to that learning curve, and I’m a fan of all of them. You can learn top-down, meaning you review the marketing and product documentation that highlights the differences and then focus on whatever details are relevant to you. You can experiment with the new version, seeing how it works, what’s changed, and what’s challenging - that’s a middle ground approach to me. And then there’s the bottom-up approach of looking at the technical changes and trying to understand why they were made and how you can use them.

For some reason I actually like the latter approach most of all. By seeing the technical implementation details I can understand what’s really changed. A good example is log files, both client-side and server-side. If a new log is introduced and it has some substance, then that must be an important component, and it must add some important functionality. The high-level details give us the context of that importance, but the low-level details give us the clues to make it work well.

As I’ve mentioned in the past, the first moment at which I fell in love with ConfigMgr was looking at the database. That’s hard to explain, but I still maintain that ConfigMgr is a data-centric system and thus the database is very important. When I’m learning a new version of ConfigMgr, I start by focusing on the database changes.

An easy approach to looking at database changes is to fire up SQL Server Management Studio, connect to your new ConfigMgr database, expand the Views node, and look for changes. With up to 1,000 views that’s problematic, though you can ease the process by ignoring the collection views, inventory views, and ‘secondary views’ (those that seem to be intermediate views). But that all depends on your memory of the previous version’s database schema and what’s important, so it’s easy to miss fun new views.

Another alternative is to query for the differences. Those queries (assuming you’re doing them from the database server with the previous version) would be:

-- lost views
SELECT * from sysobjects where type='V'
and name not like 'v_CM_RES_COLL_%' and name not like '_RES_COLL_%' and name not like 'v_HS_%'
and name not in
(SELECT name from [new_version_server.new_version_database].dbo.sysobjects where type='V'
and name not like 'v_CM_RES_COLL_%' and name not like '_RES_COLL_%' and name not like 'v_HS_%')
order by name

-- new views
SELECT * from [new_version_server.new_version_database].dbo.sysobjects where type='V'
and name not like 'v_CM_RES_COLL_%' and name not like '_RES_COLL_%' and name not like 'v_HS_%'
and name not in
(SELECT name from sysobjects where type='V'
and name not like 'v_CM_RES_COLL_%' and name not like '_RES_COLL_%' and name not like 'v_HS_%')
order by name

You’ll notice the queries ignore the collection and hardware inventory history views. Collections are a constant and I’d rather compare hardware inventory changes by looking at the SMS_def.mof changes.

The lost views will likely be a manageable number but the list of new views could be quite numerous, depending on how big the version differences are between the versions you’re comparing. Service packs are likely to introduce few changes, major versions will introduce a lot, and minor versions will be somewhere in between.

If there are a lot of changes then you’ll want to do variations on the above queries, filtering out groups of views as you understand them. Views do often have naming conventions that group related views to each other and so if you understand the significance of the group then you can eliminate them from your list of views to study.

Admittedly, interpreting what a new view adds can be tricky. The name of the views and the columns will give clues. Doing queries against the view when it’s got some data will help further. To a large degree you just have to follow your instincts and focus on those that seem most interesting. And this is just one way to learn the new version, so don’t spend too much time focusing on this technique.

Finally, you might ask what can be seen by using this technique to compare ConfigMgr 2007 with ConfigMgr 2012. My suggestion is that it’s too early to jump to conclusions. ConfigMgr 2012 will change as time goes on (otherwise it would be released already). Exciting view changes are blog posts for future dates.

p.s. I focus on views here, as opposed to tables, because views are what we have always been encouraged to use as ConfigMgr customers. Generally that works, but sometimes there are interesting table additions that don’t get reflected in views. For that reason you may want to also look at tables, but that’s an easy extension of the above concepts.

Published Saturday, June 18, 2011 6:52 PM by pthomsen


No Comments