SQL 2012 on CM07

SQL 11.0.2316

Last night I upgraded my CM07 lab to SQL 2012. The supported method from Microsoft is to install CM07 on SQL 2008 (or R2) and then run the SQL12 in-place upgrade over it. I think part of the reasoning behind that over a fresh install is that the prereq check would likely fail if it saw SQL12.

After running the upgrade job in my CM12 lab (yes that's not supported and it broke a few things), I found that this upgrade felt a lot like the old SQL 2005 to 2008 upgrade; lots of old stuff was left behind. So I thought I'd test what I did for that case here.

The idea is to uninstall the old SQL and install the new so that all the barnacle isn't left behind (old Visual Studio apps, old SQL Mgmt Studio apps, etc.). And doing that is a bit tricky, but it worked. What I did was:

Disable and stop:

  • Symatec Endpoint Protection
  • SMS Exec, SMS Site Comp Mgr, SMS Agent Host
  • EDM 2011

Using SQL Mgmt Studio, detach all databases (we had a couple other than the CM one), and I also selected the update statics box as an option for the detach.

Uninstall SQL 2008 R2, all the Visio Studio stuff, SQL client stuff, online books, etc.


Install SQL12 (DB engine, replication - we replicate our MPs so we need that, SQL client tools).

I noticed that SQL12 doesn't want to run as local system and not knowing if thie new default of NT System would work, I selected System instead (had to browse for it). Did the same for the agent too.

Ran CU1 for SQL12 (man that thing is 463 MB!).

Attached the databases and rebooted.

Enabled the services I disabled and ran a site reset checking the SQL box.


Edit: Enable CLR in SQL. I found that CI changes weren't taking today.

Tested things out and it's all working fine.

  • Created on .

CAS Considerations for CM12

Reasons to build a CAS:

  • You will go over 100K clients.
  • You fought and lost the political battle to keep just one primary (politics).
  • Legal reasons (data must reside in country on a primary) - note that most data will still be copied to the CAS anyway.
  • Load balancing\BCP: you don't want the loss of contact to all of your clients should a datacenter go down. 3 primaries and a CAS could mean that a primary and CAS could go down, but you could still reach 2/3 of your client base by connecting to the remaining primary sites. The tradeoff is that you now have 3x the likelihood of an outage now that there are 3 primary sites instead of one.

Reasons to avoid a CAS:

  • One extra server to maintain with all its licensing, monitoring, hardware, and support costs.
  • Replication requires 8 GB RAM just for the CAS alone. Microsoft recommends a 64GB box with 16 cores for a CAS.
  • SQL Enterprise will be needed to go over 50K clients (an added expense).
  • All content is stored on the CAS; every package, application, software update, etc. Yes, it's in a content libray to help manage the size, but it's still there taking up space. See more.

Future Proofing:

  • You might merge with another company or someone might buy your company and you could grow beyond 100K clients.
    • Neither primary sites nor a CAS can be swung under another site.
    • Export objects from losing site and import to winning site (or brand new combined site).
  • You're at 90K clients and might grow.
    • Good point.

Security necessitates a split of sites:
You don't put servers in one domain and workstations in another. The Full Administrator role in CM12 is much like Domain Admins. You could simply grant an AD group permissions to that role and remove yourself from the role until needed (open a ticket to do the work, add yourself to the AD group, do the work, remove yourself from the group, and close the ticket).

Alex Semibratov points out that even that 2nd bullet for a CAS is faulty:

"The second reason does not seem to be valid since a site is no longer a security boundary. Meaning, that local system on any of primary site servers has full admin access to all sites in "hierarchy". In other words, there is no more site hierarchies for security."

Matt Granstom added the BCP consideration. Email me if you can think of valid reasons.

Fellow MVP Rob Marshall has a nice article on this topic too.

  • Created on .

SCEP Posts

Well, once again, I'm breaking my own rule about reposting other blogs and 2 days in a row, nonetheless. But It's good stuff from Jason Githens at Microsoft.

Here are three new SCEP/SUM blogs I just posted today.

And here are the others published (somewhat) recently:

  • Product Documentation. Published product documentation on Technet.
  • How-to-Videos. A series of 5-10 minute video walkthroughs I've done for all facets of the Endpoint Protection feature.
  • Blogs. These are primarily built on the theme of management+security, highlighting the better-together story of ConfgMgr and Endpoint Protection. Currently I only have one posted here, on building custom reports for Endpoint Protection, but I'm working on several others that I hope to have published over the next 3-4 weeks, and they can all be found at the provided link. Check back frequently.
  • Created on .

Jarvis Series on MDT and App Mgmt

I don't normally post about other people's blogs, but since I know what I write here will be picked up via RSS to myITforum, I'll leverage that to get the word out on a series of posts Jarvis Davis has written on managing apps with MDT.

At our last MNSCUG meeting, I asked Jarvis what's he's been doing regarding managing apps when replacing computers and he said he was going to post it. Well he has. Check it out!


  • Created on .

Report User Role

One of the security roles in CM12 I see asked for a lot that isn't there is one for reporting.

CM12 actually grants admins access to the reports they need quite well (software update admins get to see software update reports, for example). But some folks just don't want things that locked down. If you' re looking to grant domain users access to all of your SRS reports, it would be nice to have a Report User role.

One way to make your own is to clone the Read-only Analyst role. Then go in and remove all permissions but the reporting ones. (This prevents them from getting console access or other things they don't need). But this is rather time consuming to uncheck everything.

Instead, you can just download this zip file, extract it, and import it (Administration\Security\Security Roles - right click and select import).

Update: If you wish to grant users permission to run reports out of the console (I don't recommend this for the Reporting User role created above as they just don't need console access) the docs say to grant site read. That might not be enough.

If your report node looks empty for your admins, have them go look at Administrator\Site Configuration\Sites and if they don't see your sites there, your users will need their scope added. From your own console where you can see these sites, right click your sites and add any scopes you've created for other admins. It's probably set only for default which you wouldn't be granting to other admins.

  • Created on .

Microsoft Security Compliance Manager 2.5

Microsoft Security Compliance Manager 2.5 was released today.

If you haven't had a chance to look at this great tool, I highly recommend you do so. A problem I had for years was juggling all the security settings in active directory and in CM. Because a security baseline can have so many settings, trying to juggle them in a spreadsheet was hopeless.

But SCM lets you manage those settings in one spot, download the latest baselines from Microsoft, compare your settings to Microsoft's, make notes on why you deviate from Microsoft's settings, and lets you import and export to GPOs and CM baselines. And it works for CM12 too!

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