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\STD
  • SQL\ENT
  • 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.

ACCEPTSQLSERVERLICENSETERMS="True"

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
[OPTIONS]

IACCEPTSQLSERVERLICENSETERMS="True"

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

ACTION="Upgrade"

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

ENU="True"

; 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.

QUIET="True"

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

QUIETSIMPLE="False"

; 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.

UpdateEnabled="True"

; 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.

ERRORREPORTING="True"

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

USEMICROSOFTUPDATE="True"

; 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.

UpdateSource="\\myserver\sqlshare\SQL\14\LatestCU"

; Displays the command line parameters usage

HELP="False"

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

INDICATEPROGRESS="False"

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

X86="False"

; 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).

INSTANCENAME="MSSQLSERVER"

; 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.

SQMREPORTING="True"

; 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.

INSTANCEID="MSSQLSERVER"

; 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.

FAILOVERCLUSTERROLLOWNERSHIP="2"

  • Created on .

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.

Reboot

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.

Rebooted.

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!

http://verbalprocessor.com/2012/04/11/configuration-manager-and-the-mdt-database-1-of-5/
http://verbalprocessor.com/2012/04/11/dynamic-osd-using-the-mdt-database-2-of-5/
http://verbalprocessor.com/2012/04/11/application-replacement-3-of-5/
http://verbalprocessor.com/2012/04/11/application-replacement-4-of-5/
http://verbalprocessor.com/2012/04/11/osd-and-the-mdt-database-5-of-5/

  • 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 .
Copyright © 2019 - The Twin Cities Systems Management User Group