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 .

Installing SQL 2012 with a Configuration File

I've always preferred installing SQL with an answer file (configuration file) to save time and keep my installations consistent. That holds true for SQL12 as well, but it seems there are some slight changes you'll need to make from the 08/R2 days so I might as well post them here in case I forget!

SQL File Locations

  • SQL
  • SQL\CU1

For those of you also using the Enterprise edition, you want to be really careful not to accidentally deploy it as it's far more costly in term of licensing. So make a folder layout that makes sense and stick to it everywhere.

First off, if you don't have a config file, just make one. Run the SQL setup right up to the point of installation, but then stop and copy the ConfigurationFile.ini offered to you before proceeding with the install. Then just cancel the install and copy the file to a permanent location so you can modify and test it.

Edit ConfigurationFile.ini

The 1st thing you'll want to add is the acceptance of the EULA which doesn't get copied in.


Just add it under [OPTIONS] at the top of the file.

Add a semicolon before UIMODE as we're trying to stay silent and that parameter cannot be used with a silent switch.

Set either QUIET or QUIETSIMPLE to "True" - the former forces a completely silent install which is nice if you want to send this with CM. The latter is nice during testing to watch the progress.

New to SQL12 is something called Product Updates. It replaces slipstreaming and makes updates a breeze.

Product Update can pull in a cumulative update, service pack, or service pack plus cumulative update.

So instead of the old PCUSOURCE and CUSOURCE locations, you simply point SQL to either "MU" for Microsoft Updates or to a local folder or UNC.

To make it work, just make sure you have UpdatesEnabled="True" and then set UpdateSource="\\myserver\sqlshare\CU1" in the ini file. I extracted Cumulative Update 1 for SQL 12 to the CU1 folder (run it with /X). Then ran the SQL install via command line.

Command Line to Install

\\myserver\sqlshare\STD\setup.exe /ConfigurationFile=C:\Storage\ConfigurationFile.ini

And 10 minutes later, I had a fully function SQL12 v11.0.2316 running. So CU1 clearly installed.

Update for SQL 2014

Because we can now upgrade SQL for CM, I'll point out that this can also be made easy using a configuration file. And because CU1 for SQL 2014 is out too, I've set my upgrade to install that at the same time (it has many fixes I want). Also, in the instructions above, I mention that I extract the CU files. You don't have to do that. So I have a folder on a share with the single exe file for CU1 and just call that out: SQL is smart enough to extract and install it on the fly if our configuration file asks for it.

Here is a copy of the SQL configuration file I just used to upgrade my primary site which upgraded SQL to 12.00.2342. Note that I stopped CM services before starting the upgrade. I also reboot for good measure at the end though it seems I don't really need to do that.

;SQL Server 2014 Configuration File


; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.


; Use the /ENU parameter to install the English version of SQL Server on your localized Windows operating system.


; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block.

; UIMODE="Normal"

; Setup will not display any user interface.


; Setup will display progress only, without any user interaction.


; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found.


; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature.


; If this parameter is provided, then this computer will use Microsoft Update to check for updates.


; Specify the location where SQL Server Setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services.


; Displays the command line parameters usage


; Specifies that the detailed Setup log should be piped to the console.


; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system.


; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS).


; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature.


; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will incorporate the instance ID of the SQL Server instance.


; Specifies whether the upgraded nodes should take ownership of the failover instance group or not. Use 0 to retain ownership in the legacy nodes, 1 to make the upgraded nodes take ownership, or 2 to let SQL Server Setup decide when to move ownership.


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