CM07 and SQL12: Pull the Triggers!

Yes, pull the trigger on installing SQL12 if you have CM07 as it's supported, but what I'm talking about pulling here are two useless triggers in your CM07 database; even if you didn't go to SQL12 yet.

What am I talking about? There are two redundant triggers in the CM07 database which I have disabled.

Add_Remove_Programs_64_ and dd_Remove_Programs_64_ do the exact same thing.
Virtual_Application_Packages_DATA_ins and _Application_Packages_DATA_ins do the exact same thing.

If you install KB2676776 it rewrites many things in the DB so that SQL12 doesn't choke on old syntax, but these 2 triggers don't get rewritten.

You could just delete them. And if you do, the cool thing is that the weekly indexing task (hopefully you have enabled that) will rewrite them using clean code that SQL12 doesn't hate. But because these triggers are just duplicates and the work is already being done, you might just as well disable them. I can't say if you'll see a performance boost or not (because it does seem to be a duplication of work).

My PFEs told me it was OK to go ahead and disable these until the day the CM team addresses them. To disable, just right click and choose disable. Yes, by all means, test in a lab 1st. So I last month, I disabled dd_Remove_Programs_64_ and _Application_Packages_DATA_ins and our logs have been error free.

  • Created on .

Guru follow up on MP Replicas

I'll track updates to my Guru session yesterday regarding MP replicas in this post.

First off, I've had positive feedback from others saying they're excited to try SQL replication to MPs which is great. That was really the goal of the session. I just don't know how many people know about them.

Here is a testimonial I just received today from a large corporation who is running SQL offbox. They know now that they could have gone onbox by using replicas:

Read more: Guru follow up on MP Replicas

  • Created on .

MPLIST fails with error 500, MPCERT works - MP Replicas

I've mentioned before that there is a benefit to using MP replicas in CM07/CM12. The primary site isn't nagged by every single client's policy requests directly (so you can scale better) and you get more uptime. If the primary site goes down, your clients can still get policy and still upload inventory and metering data.

The ease of setting up a replica for MPs in CM12 is much improved over CM07 because Microsoft gives us a stored procedure to run which not only creates the publication on the primary, but sets permissions to it correctly as well.

Microsoft's wording about generating the cert for the MP is odd. Because you COULD install the replica on a middleman box and then point all your MPs to it, they keep talking about the database replica server instead of just saying "the MP." But don't ever do it that way. Install SQL on the MP (the license is already included as this is a CM role) and create a replica on the MP. If you just offloaded the replica to another server and told all MPs to use that remote server, you only get the benefit off eleviating pressure on the primary site, but you get nothing for redundancy. If that one replica goes down, your MP will be useless.

So to clarify, you just run the PowerShell command on the MP and don't need to import anything. If you think you are supposed to run it on the primary, export it and import it to the MP, you are wrong. You'll just get a bunch of schannel errors in the event log and you'll get error 500 on your MPLIST tests. And the mpcontrol log will reflect the same error.

  • Created on .

Kim's Chrome Search

I had a post back in March showing how to use Bing to help you find the same documentation. This weekend, Kim Oppelfens (MS MVP) made a nice post to help us find Microsoft documentation using search engine providers. He said he didn't test it with Chrome and I just did.

Kim's search page

If you go to Kim's post, you'll see a button to add providers based on what you're searching for. I clicked on the one for CM12 and got an error. Replace the word bing.comwith CM12 for the keyword.

So now if you type CM12 in the address\seach bar of Chrome (I'm using version 21), you'll see a box show in that bar to reflect that you're using the search engine provider targeting Microsoft's CM12 docs. So assume I'm looking for how to setup a replica for my MP, I just continue after typing CM12 with the word replica.

Search CM12 for the term replica

I assume you could do the same for each of these buttons using a keyword of your own choosing.

You can see how easy it is to get good results back, which blow away Google's search. Thanks Kim for the boost!


  • Created on .

Planning for some redundancy in CM07 or CM12?

I've made plenty of arguments for not installing a CAS. Microsoft just added another by giving us the ability to join a primary to a new CAS in SP1.

But what about the idea of redundancy? The worry that if you take the advice and go with one primary and no CAS (or no central for CM07), that all your eggs are in one basket. That if the server goes down, clients can't update or get software. So maybe to primary sites are better? Not so.

What you can do is just add a couple servers that clients talk to. Put the MP\SUP\DP roles onto each box and you're almost there. An MP still needs to go talk to the primary site to get information to give to a client requesting policy. So if you set up a very simple, small, supported SQL replica to the MP, it will have all the information it needs to give clients their policy, even if the primary site is down. Clients can then get policy, scan, patch, run software, send in inventory, etc., all by talking to one of the 2 new servers.

Note that by adding just one MP\SUP\DP box does nothing for redundancy other than to offer uptime for servicing or patching of the primary site. But if that one new box goes down, you're in the same boat; clients can't update or get software. If you go this route, you really want at least 2 of these servers. And preferably in different sites (if you're worried about the outage of a site).

This gets more complicated with more roles, but the concept is the same. Once you get the strategy, it's easy to start thinking about other client communications you might need to keep going.

And if you're wondering if that SQL replica to the primary isn't just as complicated as a primary to a CAS or a central, it's nothing like it. It's wafer thin! In my world of 1000's of packages and collections, that DB replica still amounts to just a 5GB file. Enough to reside in RAM on any 8GB box. And we've never had replication issues since we got off SQL2K many years ago. Set it and forget it.

  • Created on .

Getting Valid Clients off a Spreadsheet

I had a request last week from someone wanting to know which of his machines had the CM client on them.

"What OU are they in?" I asked.

"They're all over the place. Lots of OUs," he replied.

"OK, so do they have a common name or some identifying feature at all?" I asked.

"Nope. I can send you a spreadsheet."


What is it with people and spreadsheets? So I start thinking about how I could compare a spreadsheet to my clients in CM. I wasn't about to import that list into a collection; it was like 2000 clients.  So I figured I'd import them to a temp table in SQL and then just join that to the v_r_system_valid view. With a little online searching I came up with this:

   name NVARCHAR(30) NULL
  FROM 'C:\Storage\test.csv'   WITH
      ROWTERMINATOR = '\n'

Note that the csv file location is in relation to SQL, so in my case, I parked it on the C drive of my central (where SQL is installed; we never install CM off box).

Also note that my ROWTERMINATOR is a carriage return to match a list of machines that are top down in a list. If you have a real CSV file where they are comma seperated, you can just use ROWTERMINATOR = ','instead.

Now you just execute this query to find matching machines and copy the output to email to whoever is asking for it:
FROM   v_r_system_validASsys
       ON sys.netbios_name0 =

And if you want to be nice, you can drop the table:


If you don't do that, it's erased when you close your session.

  • Created on .
Copyright © 2019 - The Twin Cities Systems Management User Group