This post is here to help you set up an instance of SQL Server with the intention of using it for storing the database for System Center Configuration Manager. It goes through the settings that need to be made during the installation to be compatible, as well as post-setup configuration required to get the server fully up and running.
I am installing everything on to a single Windows Server 2008 R2 Standard virtual machine for the purposes of this. In an ideal world, all the server roles (Domain Controller, SQL Server, SCCM roles) would (and should) be split out across multiple servers with fail-over redundancy etc in place.
The Windows Server machine is an Active Directory domain controller, DHCP and DNS server. It is set up this way to closely reflect as much as possible a real life situation. The server has had all Windows Updates installed (generally a good idea).
In the end, I will be installing two SQL Server instances, one for the SCCM database and one for the WSUS database. While there are no special requirements for the WSUS database, the SCCM database requires a specific collation set, one that is different from the standard installation collation, and thus requires its own instance.
Prerequisites for SQL Server 2008 R2
There’s not much additional software required for installing SQL Server 2008 R2. The .NET Framework 3.5 is required, but you will generally find that if you have all the latest Windows Updates installed, you will have .NET 4.0 (or newer) anyway. This is why it’s a good idea to get the system updated first. It also requires Windows Installer 4.5, which as we are installing on Windows Server 2008 R2, isn’t an issue (it’s included already).
Microsoft recommends for extra security that the service account for SQL Server run as domain user accounts. This allows the administrator to apply the rule of least privilege to the accounts, preventing them from doing anything they’re not supposed to be doing.
For the purposes here, I’m going to install the database engine (and associated SQL Server Agent) and Reporting Services. If I wanted to install other items (like Analysis Services etc) will need accounts for those too. I will need to create three user accounts in Active Directory, all of which will have standard Domain User security rights (if there’s a more secure way, please let me know). I suppose in an ideal world you would use a separate password for each account, but I will use the same one for this test system.
Insert the DVD and run setup.exe from there, use auto play or run setup.exe (if you downloaded the files). The setup program will start. I’m not going to fill this up with unnecessary screenshots, so I’ll gloss over the bits that aren’t interesting in bullet points.
- In the SQL Server Installation Center, choose the Installation menu, and then select a ‘New installation or add features to an existing installation.’ From here I can set up SQL Server failover clusters and cluster nodes too.
- The Setup Support Rules runs first; everything should be fine here with no errors reported.
- Enter your product key next, or choose to run as an evaluation (you can change to a paid product later if you wish).
- Accept the license terms if you want to get any further.
- Allow the Setup Support Files to install.
- The Setup Support Rules will run, and all going well you shouldn’t have any errors.
On this version, I have to choose whether I want to install individual features or all the features as default. As I want to be choosing what I’m installing, as well as setting service accounts and collation, I choose the first option SQL Server Feature Installation.’
I select the features I want in this installation from the Feature Selection screen. I want to install the Database Engine Services as well as the sub-feature Full-Text Search (as SCCM needs that to install). I select to install the Management Tools-Basic too (note that Management Tools-Complete also gets checked). I can change where the shared features (in this case, the Management Tools) gets installed. I may have multiple hard drive partitions available for installation, so I can change to another drive here.
- The Installation Rules run, and all being well will generate no errors.
Now I can set up the instance for SCCM. I choose the Named instance option and enter a name for my instance. I can change the Instance root directory here, so I can place the files on another partition if required.
- The Disk Space Requirements screen tells me if I have enough disk space to install all the SQL Server files. You should make sure that you have enough disk space not only for installation files but also the database afterwards.
On the Server Configuration screen I choose the accounts I set up earlier in Active Directory Users and Computers (if you haven’t done it yet, you can do it now). The passwords that have been set for the accounts go here too. Change the Startup Type of the SQL Server Agent service to Automatic in the dropdown box. Also change the SQL Server Browser Startup Type to Automatic.
On this same screen, select the Collation tab and click the Customize button.
In the Customize the SQL Server 2008 R2 Database Engine Collation window, choose the ‘SQL Collation, used for backwards compatibility’ option and in the list choose SQL_Latin1_General_CP1_CI_AS
- For the Database Engine Configuration leave Authentication mode as Windows Authentication mode and click the Add Current User button to add the current logged on user as a SQL Server administrator. If another/other users are required, you can click the Add button to choose them from Active Directory.
- Still in the Database Engine Configuration screen, click the Data Directories tab and change the directories if required. These options will locate the database data files wherever you specify, this could be on another partition or disk.
- On Reporting Services Configuration leave the default option to ‘Install the native mode default configuration.’
- Choose to send error reports to Microsoft if you like.
- The Installation Configuration Rules run and should be fine.
- A summary of what is due to be installed is shown. Click the Install button, sit back and relax!
Installation for the WSUS database
Installing a SQL Server instance for the WSUS database follows the same procedure as above, with a few small differences:
- Choose to install Database Engine Services and Full-Text Search only on the Feature Selection screen.
- In the Instance Configuration screen, add a new Named Instance called whatever you like.
- You can reuse the service accounts if you like, though I’m sure it’s best practice to create more accounts for the new instance. Leave the collation alone this time.
That’s pretty much all there is to setting up the two instances for SCCM and WSUS. The next part is about configuring the communications of the services so they talk to the outside world (or to your network, at the very least).
Setting SQL Server Communication Ports
SCCM requires the SQL Server instance to use static ports (dynamic ports are not supported and will cause setup to fail). As we are running two instances of SQL Server, we’ll need to set a static port for the SCCM instance and for we’ll set one for the WSUS instance too.
In the Start menu, under All Programs, Microsoft SQL Server 2008 R2, Configuration Tools, run SQL Server Configuration Manager (you can also start typing this in the Search programs and files box in the Start menu).
Expand SQL Server Network Configuration and under Protocols for <SCCMInstance>, ensure TCP/IP is set to Enabled. Do the same for Protocols for <WSUSInstance> too.
Next you need to disable dynamic ports and set a static port for the IP address that will be used for accessing the SQL Server instance. Double click the TCP/IP protocol under Protocols for <SCCMInstance>. In the TCP/IP Properties window, change the Listen All option to No and then click the IP Addresses tab.
There are a lot of IP addresses (IP1, IP2 etc) listed, you will need to locate the one associated with the address of the server and change the Enabled option to Yes for that address.
In my case, I also set the loopback address (127.0.0.1) to Enabled=Yes too, as I will be installing everything on the same server and running Management Studio at times on it too. EDIT I have found there is not really any need to set the loopback address. For each enabled IP address, remove the number from the TCP Dynamic Ports. Scroll to the bottom of the list to the IPAll section, remove any number from the TCP Dynamic Ports section and enter a port number of your choosing in the TCP Port box. EDIT You should enter the port you want to use into the TCP Port section for the IP address you are using.
UPDATE-After a lot of teeth-gnashing, I appear to have finally solved an issue that prevented SCCM setup completing (during the install, it would fail on the first step ‘Evaluating setup environment’ with the error ‘Could not connect or execute SQL query’). With help from http://goo.gl/yXFIP I found that you have to remove any value in Dynamic Ports for ALL IP addresses (even those not in use) and any under the IPALL section at the bottom. Then, you have to enter your chosen port number in ALL TCP Port boxes for ALL IP addresses (again, even those not in use), as well as in the IPALL section at the bottom.
UPDATE-Since having written this, I have found that there is one more setting to change before remote connections work 100%. Within SQL Server Configuration Manager, expand SQL Native Client 10.0 Configuration and select Client Protocols. In the right hand side double-click TCP/IP and change the Default Port to match that you set earlier for the SCCM instance.
Don’t forget to do the same for the WSUS instance (use a different port number though). Once you’ve done that, go to SQL Server Services, right click the SQL Server (<SCCM Instance>) service and choose restart. Do the same with the SQL Server (<WSUS Instance>) service.
Next, we need to configure Windows Firewall (or another firewall, if you don’t use Microsoft’s one) to allow remote access to the SQL Server.
Configuring Windows Firewall
Click the Start button and click Control Panel. In Control Panel, under System and Security, click Check firewall status, and then click Advanced Settings in the left hand column (alternatively, you can click the Start button, type wf.msc in the search box and hit Enter).
In the left hand pane of the Windows Firewall console, click Inbound Rules and then click New Rule in the right hand pane.
In the New Inbound Rule Wizard, choose the Port option and click Next. In Protocol and Ports, ensure TCP is selected, make sure Specific local ports is selected and enter the port number you used for the SCCM instance.
In the Action screen ensure Allow the connection is selected. In the Profile screen, select the network profiles this rule will apply to. Having Public deselected is going to make things secure (in case for whatever reason the server slips into Public mode). I’ve kept Domain and Private selected for now, you will need to choose the option that best fits your network.
Choose a name for your protocol and click Finish. Rinse and repeat for the other SQL Server instance, using the port number you set for that particular instance.
EDIT You need a firewall rule for the SQL Broker service used by SCCM for communications between the sites and database server. You need a port (by default, this is TCP 4022, but is changed in SCCM setup if necessary), and follow the instructions as above.
Now that communications are up and running (you can check this by running SQL Server Management Studio and entering servername\<SCCM Instance> into the Server name box, it should connect to your server instance), we need to get everything up to date. Get Windows Update fired up and get service pack 1 installed!
Lastly, once Windows Update shows no more updates, you need to install SQL Server 2008 R2 SP1 CU4, otherwise SCCM won’t install. It’s a hotfix request you can get from http://support.microsoft.com/kb/2633146
We’re now done with SQL Server setup, and can move along with getting the prerequisites installed for our SCCM installation.
I lied. We’re not quite done. Fire up Management Studio and connect to your SCCM instance. You need to make sure the user performing setup has sysadmin rights to the instance, as well as the SCCM computer account. Adding the user is simple enough, in my case it was the administrator account, but you might use something different. Expand the Security folder in Management Studio, right click the Logins folder and choose New Login.
Click the Search button next to the Login name box and locate the installing users’ account in Active Directory (this will more than likely be the user you log in to the SCCM server with). Click Server Roles in the left hand panel and make sure ‘sysadmin’ is checked. Click Ok.
Next, the computer account of the server running SCCM needs sysadmin rights granting too. This is slightly different to the above, as on a remote SQL Server you can’t add computer accounts as logins. There are two ways to do this-either add the SCCM computer account to BUILTIN\Administrators group, or create a new group and add the computer to that. Then, you add either BUILTIN\Administrators or your new group to the sysadmin role in the same way as above, substituting the user account for the group instead.
Now, we’re done and can move on to getting the SCCM server up and ready.