Installing SCCM 2012 RTM Secondary Site using A Pre-Installed SQL Express 2008 R2 Instance

Since System Center 2012 was RTM’d few days ago, I have started updating / migrating my home environment. After I migrated my 2 Hyper-V servers from VMM 2008 R2 to VMM 2012, I have started building a brand new SCCM 2012 environment so I can migrate SCCM 2007 to it. My plan is to install a Central Admin site, a child primary site and a Secondary site so I have a simple 3-tier hierarchy like my existing 2007 and 2012 Beta 2 environments.

The Central Admin site and the child primary site installation all went pretty smoothly. But I had some issues when installing the secondary site.

When installing Secondary Site from it’s parent primary, There are two options available for the database:

1. Install and Configure a local copy of SQL Server Express on the secondary site computer
2. Use an existing SQL Server instance.

I wanted to install SQL Express myself so I can control where it’s installed to and locations for data, log and backup files. – This is pretty common and most of SQL DBAs would configure to install SQL on a volume other than C:\ and place data / logs / backups on dedicated and separate disks. By using SCCM to install SQL express for you, you don’t get to choose any of this, which can be pretty annoying.

According to Supported Configurations for Configuration Manager, secondary sites supports SQL Server Express 2008 R2 with SP1 and Cumulative Update 4. So I downloaded SQL Server 2008 R2 Express With SP1 with Tools (SQLEXPRWT_x64_ENU.exe) and SQL 2008 R2 Service Pack 1 Cumulative Update 4 and installed them in order on my secondary site site server.

Below is what I have customised during the SQL express install:

• I configured the location for SQL, SQL instance, data files, log files and backup files the way I wanted it.
• I selected the SQL instance to use the collation "SQL_Latin1_General_CP1_CI_AS" because it is the only collation that SCCM supports.
• I kept the default secondary site SQL instance name "CONFIGMGRSEC" (this name is what’s used if you choose SCCM to install SQL Express for you).
• I have given a pre-configured AD group called "ConfigMgr2012 Servers" which contains all SCCM 2012 site servers sysadmin rights in SQL Express.

After the install, I applied CU4 and all went pretty smoothly.

Now, I tried to push Secondary Site install from the primary site. Under SQL Server setting step, I selected “Use an existing SQL Server instance” option and enter the secondary site server’s FQDN under “SQL server fully qualified domain name” and “CONFIGMGRSEC” under “SQL server instance name, if applicable”. After finishing the wizard, the secondary site install failed during prerequisite checks. I got few errors in regards to the SQL collation is not set to SQL_Latin1_General_CP1_CI-AS:

This is very strange because all my SQL instances in this hierarchy are set to this collation, and because of this, the setup did not even get kicked off.

Additionally, I also found the following:

• On the primary site server, in the ConfigMgrSetup.log under System root, I get the following errors:
• CSql Error: Cannot find type data, cannot get a connection.
• [08001][17][Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
• I could use the SQL management studio from Secondary site server to connect to the SQL express instance, but I couldn’t use the SQL management studio from a remote machine to connect to it:

After spending some time troubleshooting, I got it going. Below is what I have done on the SQL Express instance:

1. I’ve assign “ConfigMgr2012 Servers” group (which I created myself and it contains the primary site server’s computer account) “dbcreator” role on top of sysadmin role it already had.

1. I realised by default, after I installed SQL express, TCP/IP protocol is disabled. So I went to SQL Server Configuration Manager, under SQL Server Network Connection —> Protocols for CONFIGMGRSEC—>TCP/IP, enabled it. I also had to configure the ports for this connection:

I removed 0 from “TCP Dynamic Ports” for each IP and added static port 1433 under “TCP Port”

After you enabled TCP/IP and changed the port, you will be prompted that you have to restart SQL server service for the change to take effect, so I restarted the SQL service.

After these steps, the prerequisite checks were passed and the Secondary site installation finished successfully.

In summary below are the steps I took to pre-configure a SQL Express instance for SCCM 2012 secondary site:

1. Install SQL Express 2008 R2 with SP1 with Tools
2. Configure SQL express install directory as per my standard (not on C:\ drive)
3. Configure SQL Express instance name as "CONFIGMGRSEC" as it is default to SCCM secondary site and there’s no reason to change it.
4. Select "SQL_Latin1_General_CP1_CI_AS" as SQL server collation.
5. Configure data/logs/backups directory
6. add primary site server’s computer account (or a group containing primary site server’s computer account) as administrator during install
7. Apply SQL Server 2008 R2 Service Pack 1 Cumulative Update 4 after SQL Express install
8. Set a limit for amount of memory SQL express can use.
9. Reboot secondary site server (just to be safe)
10. give the parent primary site server's computer account dbcreator access in SQL Express instance.
11. Enable TCP/IP for the SQL express instance.
12. Configure TCP/IP connection port settings.
13. Restart SQL service.
14. Initiate Secondary Site install from Primary site (via SCCM console). – Unlike SCCM 2007, secondary site install can no longer be performed by running SCCM setup from secondary site servers.
15. During setup wizard, choose "Use an existing SQL Server instance", enter secondary site server’s FQDN and SQL instance name ("CONFIGMGRSEC"). leave site database name and SQL broker port as default.
16. monitor install status using the SCCM console:

You can also check:

• C:\ConfigMgrSetup.log on Primary Site server (contains details for Secondary Site install’s prerequisite checks).
• C:\ConfigMgrSetup.log on Secondary Site server (contains details for the actual setup).

Now, instead of having SQL Express installed and configured by SCCM, I have more control of it so I can align the configuration with my organisation’s standard (if it’s in a real production environment ).

In this case, I have my SQL data file located under F:\SQL_Data\Microsoft SQL Server\MSSQL10_50.CONFIGMGRSEC\MSSQL\DATA:

And log files under G:\SQL_Logs\Microsoft SQL Server\MSSQL10_50.CONFIGMGRSEC\MSSQL\Data: