ConfigMgr & SQL - some tips

Back to main menu

This blog shares some tips on how to implement SQL for ConfigMgr 2012. There are some important decisions to be made regarding SQL and they will provide the foundation for a successful ConfigMgr deployment which can easily be managed.


Local V Remote

I've seen this question asked in the Config Mgr Technet Forums many times - should I install local or remote SQL server?

Microsoft don't care. They will support both. Most ConfigMgr Admins will choose local. As long as you just use the database for Config Mgr, the license is free, so there is no additional cost.


Database Management & Security
If the ConfigMgr server is suitably resourced there can be a performance gain using a local SQL installation. However this is not the main reason to choose local SQL. The main reason is control over the database. In a larger organisation dedicated Database Administrators (DBAs) manage the SQL environment. They can have very rigid rules, in particular when considering database security.

When we are installing Config Mgr 2012 with a remote SQL server it is a required that the installation account and the Config Mgr computer account are both Local Administrator and SQL Sysadmin on the remote server. If you are deploying to a remote SQL Cluster this needs to be done on both nodes. These permissions need to be maintained after the installation. ConfigMgr needs to be able to manage and manipulate the database for successful operation. DBAs often have a problem with this.

Performance
There are, of course, some obvious performance benefits with local installations

• not sharing with resource intensive applications
• no network latency

Collation
Config Mgr 2012 requires a specific database collation - SQL_Latin1_General_CP1_CI_AS. ConfigMgr Setup will not continue if the collation is different, which can sometimes be the case for existing SQL installations.


Random SQL tips

• The only SQL features needed for ConfigMgr are the Database Engine and SSRS (if server is to be your reporting server also)
• As mentioned above database collation must be SQL_Latin1_General_CP1_CI_AS
• High Availability - ConfigMgr does not support SQL 2012 AlwaysOn
• It is not supported to manually edit the SCCM db
• Don't forget to configure SQL Maintenance tasks - backups and re-indexing
• Manually configure SQL memory usage - minimum 8GB 
• ConfigMgr 2012 shares information between sites by SQL Replication
• A secondary site must use a locally installed SQL instance on the Secondary Site server. The default is to install SQL express on the OS partition. Just like the other site hierarchy servers, the Secondary Site server will replicate to its assigned Primary Site server, by default, on port 4022.
• WSUS requires its own DB. The DB can be co-located on an instance that hosts the CAS or PS server.
• The MDT DB is a small DB with light requirements. It can be co-located with other ConfigMgr DB’s like the PS and the WSUS.
• ConfigMgr 2012 does not use a dedicated Data Warehouse. It runs its reports from the standard ConfigMgr DB.

Disk Design

Best practice implementations of SQL follow this model for presentation of disks:

Operating System, Program Files
Database
Logs 
TempDB
SQL Backup


Sizing Considerations (Disk Space, RAM etc)

There is an excellent guide here


Improved Performance Tips

Use RAID 10 configuration for high speed performance of Database and Logs drives

Consider running reports via browser rather than ConfigMgr console

• less resources consumed on ConfigMgr server
• reports can be run quicker
• don't need to give unneccessary access to the ConfigMgr console

In-place upgrade SCCM 2012 SP1 to R2

Back to main menu

I'm not usually a fan of in-place upgrades. However this particular one is OK. I've done it a couple of times now without much fuss. It wasn't seamless for me on either occasion though - I broke my PXE booting and had to reinstall WDS/PXE to resolve. If you can carry out basic troubleshooting with ConfigMgr you should be OK.

Edit 2: CU1 for R2 now contains this hotfix. Install this Cumulative Update after the upgrade.

Edit 1: Hotfix has now been released solving the two known R2 issues
1. WDS crashing
2. Slow downloads while in Windows PE

You can download it here 

Before you commence the upgrade you should read the Planning to Upgrade System Center 2012 Configuration Manager guide. This gives you full information on tasks you should carry out based on your infrastructure.
Also Review Pre-requisites for Site Systems.

Here is an extract:

Configuration Manager supports installing System Center 2012 R2 Configuration Manager to upgrade a site that runs Configuration Manager SP1. You can run the upgrade on the site servers of central administration sites and primary sites. After a primary site upgrades, you can then use the Configuration Manager console to upgrade secondary sites to System Center 2012 R2 Configuration Manager. It is not supported to upgrade a site that runs System Center 2012 Configuration Manager with no service pack to System Center 2012 R2 Configuration Manager. Instead, you must first upgrade to System Center 2012 Configuration Manager with SP1, and then upgrade to System Center 2012 R2 Configuration Manager.

Note that you do not have to have a CU installed in order to qualify for upgrade.

You MUST, at the very least, install Windows Assessment and Deployment Kit 8.1. You can download it here.

Now we are nearly ready to carry out the upgrade. But first:

• Test the upgrade process on a copy of your site database (use /testdbupgrade)
• Solve any existing issues (the upgrade won't fix them for you)
• Fully patch the server
• Reboot in case of pending reboots
• Ensure you have a good backup
• Disable site maintenance tasks for upgrade duration
• Stop your Anti-Virus scanning

So let's start the upgrade (start at the top-level site in your hierarchy).

First we must uninstall the existing version of Windows Assessment and Deployment Kit (version 8).

and then install Windows Assessment and Deployment Kit 8.1

Note that we only require the following features:

• Deployment Tools
• Windows PE
• USMT

Now launch the ConfigMgr 2012 R2 installation by double-clicking on splash.hta

The Setup wizard is launched

Click Next

When you launch the wizard on an existing SP1 site server the wizard detects this and defaults to "Upgrade the Configuration Manager site". Click Next.

Enter your license key.

Accept the License Terms.

Accept the License Terms for the additional software. 

Choose a location to download the prerequisite files and click next.

The files are downloaded.

Choose your required languages.

Verify the setup method - note it will say "Upgrade".

ConfigMgr runs the prerequisite check.. If it is successful you can click "Begin Install".

The ConfigMgr upgrade commences.

Upgrade is completed.

Open the ConfigMgrSetup.log file on the root of the C: drive.

Verify success.

Verify the new Site Version - 5.00.7958.1000.

Examine the Site Components for errors and test the functionality by carrying out simple deployments - software updates, software distribution, OSD.
(As I said at the beginning I had to repair my PXE booting configuration).


Now continue with the upgrades

Console: 
Manually upgrade each Configuration Manager console that is installed on a computer other than the site server.

Secondary Sites:
Microsoft extract:
With System Center 2012 R2 Configuration Manager, when you plan to upgrade an existing secondary site that uses SQL Server 2012 Express with no service pack, or retry a failed secondary site installation, you must first apply cumulative update 2 to the SQL Server 2012 Express installation on the secondary site server. This is because, when System Center 2012 R2 Configuration Manager installs SQL Server Express as part of a new secondary site installation, it installs SQL Server 2012 Express with no service pack and is unable to install the required cumulative update 2 as part of the installation. When you direct Configuration Manager to install SQL Server Express as part of a new site, the prerequisite check does not detect an existing installation of SQL Server Express, and then installs SQL Server Express as part of the site installation. During an upgrade or retry, if an existing version of SQL Server Express is detected that does not meet the minimum version requirement for System Center 2012 R2 Configuration Manager of SQL Server 2012 Express with no service pack and cumulative update 2, the upgrade or retry will fail.

The moral of the story is - you must install SQL Express 2012 CU2 manually before carrying out the R2 upgrade of Secondary Sites.

Clients:
When you upgrade a client, the current client software is uninstalled and the new client software version is installed. To upgrade clients, you can use any method that Configuration Manager supports.


Hotfix

Now don't forget to apply the R2 Hotfix (mentioned above).

You can download it here 
 

Direct Access Easy Step 5: Windows 8 Client and troubleshooting

Back to Direct Access main menu

That's it. We have finished the configuration. Now we will verify the Direct Access connectivity using  a Windows 8 client.

We enable Direct Access for a client device by adding the computer account to the Active Directory Security Group we previously created.

While connected to the Corporate LAN

Restart the test computer so that the computer account permissions can be updated and run gpupdate/force to update Group Policy.

No further configuration is necessary. The rest of our work is verification and troubleshooting.

Verify Group Policy on the test computer. Open the Registry > HKEY_LOCAL_MACHINE

See the Network Connectivity Assistant Registry keys.

Disconnect from the Corporate LAN

At this point you will see the new Workplace Connectivity icon (click on the network icon in the system tray).

The status of the Workplace Connection should say "Connected" (if you have an Internet connection).

If it is you can now browse corporate resources.

Type ipconfig/all in a command prompt. See that the iphttpsinterface is active.

Ping a resource using NetBIOS name only.

Troubleshooting

If the Workplace connection is not available but Group Policy has been updated.

Verify that the Network Connectivity Assistant Service is started

If you cannot start this service look in the Event log for errors. You may see an error that "This request is not supported".

In that case run msinfo32 command to look at the computer properties. Verify that the OS version is indeed Windows 8 Enterprise (not Windows 8 Pro).


If the Workplace connection is available but not connected (status Connecting) there are a couple of troubleshooting steps to take.

1. DirectAccess log file

Right click the Workplace Connection and click "View Connection Properties".

See the DirectAccess Properties.

 
Click "Collect Logs" for advanced troubleshooting.

You can now "View logs" to open the file location (or email logs to a System Admin for assistance). We configured this email address in Step 4.

Open the Log file.

The log file contains extensive configuration information.

2. DNS

Verify that the following A records have successfully been created in DNS

Remote Access creates a default web probe that is used by DirectAccess client computers to verify connectivity to the internal network. During configuration of the single server the following names should have been registered in DNS:

directaccess-WebProbeHost—should resolve to the internal IPv4 address of the Remote Access server, or to the IPv6 address in an IPv6-only environment.

directaccess-CorpConnectivityHost—should resolve to a localhost (loopback) address (either ::1 or 127.0.0.1, depending on whether IPv6 is deployed in the corporate network).

3. Windows Firewall.

When a Windows 8 device cannot connect to Corporate Resources via Direct Access this is normally the culprit.

The Windows Firewall MUST be enabled and it is configured by Group Policy to allow the connections.

Windows Firewall MUST be enabled.

Verify the Connection Security rules. Note the DirectAccess Policy - ClientToCorp.

Open the policy and view the Authentication tab. Note that first authentication is computer certificate and the second authentication is the user account (both Kerberos).

See the Main mode

and Quick Mode details of a successful Direct Access connection.

4. External Firewall.

In Step 1 we created a DNS record (eg da.contoso.com) and this was configured to direct https traffic (port 443) to one of our Public IP addresses. Our firewall was configured to allow this traffic and a NAT rule was configured to divert this traffic to the Direct Access sever.

Test this configuration by using telnet at a command prompt.

telnet da.contoso.com 443

If the firewall has been configured correctly a connection will be made (you will be presented with a blinking cursor but a connection has been made).

If you cannot make a connection then the firewall is not configured correctly.

Direct Access Easy Step 4: Configure Remote Access Role

Back to Direct Access main menu

We have added the Remote Access Role and now we must carry out the final configuration.

Open the Remote Access Management Console.

Note that the Microsoft have simplified the configuration by grouping the tasks into steps. We will configure each in turn.

1. Click Edit under Step 1 - Remote Clients

Choose to "Deploy Full Direct Access for client access and remote management".

The wizard has been pre-populated with Domain Computers. Remove this and add the Active Directory Security Group you created earlier.

Uncheck the box "Enable Direct Access for mobile computers only".


2. Click Edit under Step 2 - Remote Access Server

Verify the details

Choose the SSL certificate that you configured earlier.

We are using Active Directory credentials.

Also, see the check box to enable Direct Access support for Windows 7 clients. We will not be enabling this for now.

We will not configure any VPN settings at the moment.

3. Click Edit under Step 3 - Infrastructure Servers

Use a self-signed certificate for the NLS server.

Verify DNS server.

Verify DNS suffixes.

Verify management server.

4. Click Edit under Step 4 - Application Servers

We have nothing to configure here for now.


We are now presented with a Remote Access review. Review all settings before clicking Apply.

 Click Apply to finalise your Remote Access configuration.

Remote Access status now looks good.

See the GPOs automatically created for Remote Access. Both policies are applied to the domain. However the Direct Access server only has the permissions to apply the DirectAccess Server Settings and your AD security group only has the permissions to apply the DirectAccess Client Settings.