Deploying SQL 2014 with ConfigMgr 2012 Application Management
I had a customer ask for some guidance on deploying SQL 2014 via application management and thought that someone new to application management might find it useful.
This is intended for my lab and not a production deployment. I would probably do a few things different depending on what my SQL Admins want in a production scenario. Firstly, I wanted a single application for all my regularly used components in my lab. For my detection logic I used the Windows Installer Product Code for the SQL Server 2014 Database Engine Services. In addition to the database engine, I install reporting services and the SQL tools. Depending on your environment, you might want to separate these into different applications, but a lab one application is fine. I also cleanup the directories that are left behind after an uninstall with my application’s uninstall. This directory structure could include information that you might want to hang onto in a production environment. I skipped the pending reboot check in SQL setup. I also included service account passwords in my configuration file that you might not want on a production installation.
There are a couple of options for unattended/scripted installations of SQL 2014 available. You can use a configuration file or the setup.exe command prompt options. See technical references below for details. To show both options, I’ll use a configuration file to install and remove with the command line options. If you’d like to see start with a configuration file, SQL setup will create one for you when you use the GUI mode setup. Setup.exe will launch SQL Server Installation Center. You can then click “New SQL Server stand-alone installation or add features to an existing installation” on the Installation tab. After you walk through setup and specify your setup options, you’ll see that SQL gives you the path to the Configuration File that you have generated:
There are a few items that you’ll have to adjust from the GUI mode setup for unattended install if you want to use this ConfigurationFile.ini:
- Remove or comment out UIMODE="Normal"
- Modify QUIT value to QUIET="False"
- Add IACCEPTSQLSERVERLICENSETERMS="True"
- Add SQLSVCPASSWORD and RSSVCPASSWORD options for your SQL service accounts if you specified them during setup
- Ensure you have given the appropriate groups rights during the setup wizard and they are shown with the SQLSYSADMINACCOUNTS option
- I also added SKIPRULES="RebootRequiredCheck" for simplified testing in the lab
I'm attaching an example of a ConfigurationFile.ini to this post. Note that you’ll have to update the information for the two service accounts.
As with the creation of any ConfigMgr application, you should test the installation and uninstall syntax from a command line. To validate that it is being run just as CM would run it, you can test it as the local system using psexec. For more information see my Package Preparation Guide Post.
Using the example ConfigurationFile.ini, you should now be able to validate that your package works by adding it to the package source and using the following syntax:
Setup.exe /ConfigurationFile=ConfigurationFile.ini
As I noted above, for my uninstall I wanted to remove using the command line option and also clean up the install directories. To do so, I created RemoveSQL2014.bat with the following two lines:
Setup.exe /SKIPRULES=RebootRequiredCheck /Action=Uninstall /Q /FEATURES=SQL,AS,RS,IS,Tools /INSTANCENAME=MSSQLSERVER
RMDIR /S /Q "%programfiles%\Microsoft SQL Server"
Once these to installations have been validated, we can move to the Configuration Manager Console to create the application by clicking Create Application under Software Library. When the wizard starts, we’ll select the “Manually specify the application information” option.
Complete general information:
Tip: Check the “Allow this application to be installed……task sequence…” option if you want to create a task sequence to fully deploy CM site in your lab.
The defaults for the Application Catalog can be accepted and then click Add on the Deployment Types page. On the create deployment type wizard, select “Manually specify the deployment type information”. Supply a name like “SQL 2014” on the general information tab and click next.
The SQL installation media should be copied to a package source for use with the application. The configurationfile.ini and the RemoveSQL2014.bat described above should be in the root of your package source. On the Content Tab of the wizard, you should specify the following:
Click next to move to the Detection Method. For the detection method in my lab, I used the Windows Installer product code for SQL 2014 database engine {17531BCD-C627-46A2-9F1E-7CC920E0E94A}:
As noted at the top of this post, you could get more granular than this.
Tip: You can browse to an MSI to get a product code. You can also check the registry “HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall”.
On the User Experience, select “Install for system” as the installation behavior and "Whether or not a user is logged on" for the Logon requirement. On the Requirements tab you can specify any Hardware and Software Requirements for Installing SQL Server 2014. For example, supported OS versions:
On the Dependencies tab, select any dependent applications that SQL 2014 requires. Click next and complete the creation of your deployment type. Then click next and complete the creation of your application.
Once the application is created, you can distribute the content and deploy it to make it available to your clients. As always, it is good to deploy to a test collection for validation first. To do so, right click the application and select deploy. On the general tab, select your collection. Next select your content targets. For deployment settings, leave it as available so we can install and uninstall in software center. Complete the wizard.
Once it is available on your target machine, you can validate the install and uninstall behavior.
Hope this helps!
Ryan
Technical References:
Install SQL Server 2014 Using a Configuration File
Install SQL Server 2014 from the Command Prompt
Hardware and Software Requirements for Installing SQL Server 2014
Introduction to Application Management in Configuration Manager