As of SQL Server 2012 it is easier to slipstream the latest service packs and cumulative updates to create new SQL Server installations. It is a slow process to first install SQL Server and then apply the latest updates afterwards.

The Usual Approach

The usual approach to slipstream SQL Server 2008 and SQL Server 2008 R2 has been documented on MSDN blogs where you needed to download update packages for all architectures and extract to a directory and copy over to the original SQL Server Setup files. Make changes to the configuration files to specify the updates and then proceed with the installation of SQL Server. It was basically allot of steps to do.

The Change Approach

Microsoft decided to change the way updates are applied with SQL Server 2012 installations. The new approach is much better. The change is documented on MSDN article about “Deprecated SQL Server Features of SQL Server 2012”.

The steps is as follow. Download the latest cumulative update for example CU2 for SQL Server 2012. Then you need to run the downloaded file to extract to a directory C:\SQLUpdates for example.

The CU2 download filename is “449398_intl_x64_2012_SQL_CU2.exe” and when you run the file it exact an file with the name “SQLServer2012-KB2703275-x64.exe”. You do not have to extract the the file again.

Now open command prompt and navigate to the original SQL Server 2012 setup files. In command prompt type the following command to start the setup:

Setup.exe /Action=Install /UpdateEnabled=TRUE /UpdateSource="C:\SQLUpdates"

The additional parameters are self explanatory, but lets go through them:

• Action = Specify that we want to perform an install of SQL Server 2012.
• UpdateEnabled = Specify that the setup should include updates in installation.
• UpdateSource = Specify location where updates are available

After running the command you will see the default install screens for SQL Server 2012. After the setup checks screens you will see the Product Updates screen that list available updates in the specified directory.

That is basically it. The SQL Server Setup will only use the updates that are available in the specified directory and automatically extract them. You don’t have to download all the available architectures, just the ones that you need.

Summary

In summary you can see that the new approach is much easier than before. You can still use the ConfigurationFile.ini and DefaultSetup.ini to specify available updates as well. I hope this change encourage you to always stay up to date with the latest service packs and cumulative updates as they come out.

@cecildt

I was busy setting up a new VM with SQL Server 2012 with the latest cumulative update (Build: 11.0.2325.0) and all the features enabled for installation. On the end of the installation summary page it show that SQL Server Analysis Services failed. The installation did install the Analysis Service but viewing the SQL Server Services in SQL Server Configuration Manager it show the Analysis Service did not start, that is why the installation show failed.

Problem

I went and look into Windows Application Event Viewer for further details and the following error has been logged:

Message-handling subsystem: The message manager for the default locale cannot be found.
The locale will be changed to US English. Message-handling subsystem: The message manager for the default locale cannot be found. The locale will be changed to US English. File system error: The following error occurred during a file operation: Access is denied. . (\\?\E:\Data\CryptKey.bin).

At first I thought there is some server locale issue. As you can see at the end of the message there is an “Access is denied” issue. Which is related to permissions to some resource. To understand why I get this error I need to explain my server setup.

The newly created VM has a C drive and an additional E drive. As you know the C drive is for OS and program files. The E drive is where I put all the SQL Server databases files and folders. On the E drive I created the following directory structure:

For each SQL Server Service I create dedicated accounts:

• SQL Engine Service: sql_eninge_service
• SQL Analysis Service: sql_analysis_service
• SQL Reporting Service: sql_report_serivce
• SQL Agent Service: sql_report_service

Solution

You get the picture. Now back to the “Access is denied” error. I had to give the SQL Analysis Service account write access on E:\Data and E:\Logs directory and then the SQL Analysis service started up successfully.

Summary

In summary if you create your own service accounts and have your own predefined folder structure you can get permission errors from SQL Server. Always keep your eye on Windows Application Event Viewer to see any SQL Server permission errors and give the right permissions to each service account.

Cheerio!

I had a issue with SQL Server Agent Service that cannot start up after creating a new clean installation of SQL Server 2012 with the latest cumulative update (Build: 11.0.2325.0). My first step was to look at trusty old Windows Application Event Viewer to see what messages get logged when I try to start the SQL Server Agent Service.

Problem

In Windows Application Event Viewer the following error message is logged:

OpenSQLServerInstanceRegKey:GetRegKeyAccessMask failed (reason 2).

The problem is that the default account that is created by the SQL Server installation that is used by the SQL Server Agent Service does not have the correct permissions.

Solution

The easy solution is to use the SQL Server Configuration Manager to change the SQL Agent Service account to Local Service and then the SQL Agent Service will start up successfully.

My preferred approach is to create a dedicated domain account for example: sql_agent_service. Assign this account to the SQL Agent Service via SQL Server Configuration Manager. When you start the SQL Agent Service you will see that the service start up successfully.

If you go back and view the Windows Application Event Viewer you will see the error again, but additional messages are logged where an advance configuration setting has been automatically changed in SQL Server 2012. The Agent XPs configuration option has been enabled which allows the Agent Service to start up successfully.

To make sure the error is gone, clear the Windows Application Event Viewer. Stop and start the SQL Server Agent Service and make sure there are no errors logged again.

By default the advance Agent XPs configuration option is disabled on new installations. You should enable it to make sure the SQL Agent Service work correctly.

Here is the TSQL to enable Agent XPs manually:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

Summary

In summary you should define dedicated accounts for each SQL Server Service up front before you install SQL Server 2012. This will allow you to know which account has permission issues when a SQL Server service cannot start up successfully. Always keep your eye on the Windows Application Event Viewer for SQL Server errors!

Cheerio!