AFINN.NET

Knowing how to pull informational data directly from the Operations Manager database and data warehouse can be a bit tricky, but a critical part of identifying and troubleshooting performance pain points as well as obtaining key informational data in the SCOM environment. To that extent, I have always leveraged the massively cool collection of SQL scripts on Kevin Holman’s website.

Now when I needed to run these queries, I would open up Kevin’s website, fire up SQL Management Studio, connect to the database, open a query window and then start pasting and executing. In order to put all that functionality into a single resource, I quickly threw together a executable that contains most of the queries on Kevin’s website as well as the ability to connect to the SCOM backends and dump the information into a DataGridView on a windows form. I also included basic functionality for the queries that were parameterized to take input from the form rather than modifying the queries in the SQL editor window.

First and foremost, I would like to thank Kevin (and give him full credit)  for posting his queries that have proven helpful time and time again. I have included the compiled standalone executable in the links below as well as the source code. Feel free to modify the code in any way to suit your needs as you see fit. The SQL statements are simply embedded into a basic XML resource which can be easily modified to add new queries.

OpsMgrQueries.zip            OpsMgrQueries_Source.zip

This is the third part in this walkthrough tutorial series. If you need to review previous tutorials, please review to Part 1 and Part 2.

Installing SCCM 2007 on the SQL Cluster

As a quick note, I did not include the WSUS installation in the following steps as the main goal was to simply address the issue of installing SCCM on a remote SQL database cluster. If you are fairly comfortable with the install process thus far; installing WSUS is simple and intuitive to add into the steps below. Simply download and install the WSUS 3.0 SP1 components and install them before you begin the SCCM installation. The SCCM integration is extremely easy if you have WSUS already installed.

1. First thing to do is add the computer account of the SCCM server to the local administrators group on each node of the database cluster. The SMS Provider will communicate with the database via WMI and the computer account will need administrative privileges to successfully make the remote WMI connection.
2. Next we need to ensure that the SPN is set correctly for the service account that will be connecting for SCCM to the SQL database. You may use adsiedit to directly modify the servicePrincipalName attribute on the service account or use the setspn utility with the following syntax:
   1. Setspn –a MSSQLSvc/SERVERSQL.DOMAIN.COM:1433 DOMAIN\SvcSCCM
      1. Replace SERVERSQL.DOMAIN.COM with the FQDN of your cluster. Since this will be used in delegation for the service account to access the SQL services from a remote computer, be sure to specify the SQL network name, not the network name of the cluster server.
      2. Port 1433 is the standard, change if necessary.
      3. Replace DOMAIN\SvcSCCM with the domain and username of the service account that will be used to access the SQL database.
   2. Additional information on SPN configuration can be found here.
3. Verify the prerequisites are installed:

a. .NET Framework 2.0
b. IIS 6.0
c. BITS
d. WebDAV
e. MSXML 6.0
f. WinRM v1.1 (http://support.microsoft.com/kb/KB936059).
g. Secure Channel hotfix (http://support.microsoft.com/kb/942841/en-us). Only required if planning on leveraging Out of Band functionality.
h. WSUS 3.0 SP1 (if using software update services)
i. Extend the Active Directory Schema (if planning on leveraging AD functionality, not required for installation)
i. If you extend the schema, you will also need to grant the SCCM service account Full Control on the System container in Active Directory. If you do not wish to grant permissions at the top level System container, you may also create a container called System Management under the System container and grant the service account Full Control permissions to that container and all child objects within.

4. Log on with the SCCM service account and start the installation.
5. Launch the Prerequisite checker first to verify all required criteria have been met.
6. Select Primary Site at the Installation Prerequisite Check Options screen.
7. For this example, the SCCM server is named SCCM01. Fill in the fields on the Options screen as follows:
   1. SQL Server and instance, if applicable: SERVERSQL (remember to use the SQL Server network name for the clustered instance, not the cluster network name).
   2. SDK Server: SCCM01.
   3. Management point computer FQDN on the intranet: SCCM01.DOMAIN.COM
8. When all required prerequisites have been fulfilled, launch setup again and select Configuration Manager 2007 SP1 from the Install menu. (Note- it is okay to leave non-critical prerequisites remaining if the functionality will not be use.)
9. At the Available Setup Options screen, select Install a Configuration Manager site server. Click Next.
10. Select Custom Settings and then Next.
11. As this is the first site in the hierarchy, we will select Primary Site at the Site Type screen. Click Next.
12. Select your option for the Customer Experience Improvement Program. In this example, we will select No. Click Next.
13. Enter the Product Key and click Next.
14. Select the installation path target for the install files at the Destination Folder screen, click Next.
15. Enter a unique Site Code and Site Name for the Primary Site. Click Next.
16. At the Site Mode screen, select Configuration Manager Mixed Mode. Native mode requires the use of certificates and a PKI infrastructure which is beyond the scope of this tutorial. Click Next.
17. For this example, we will accept the default selections at the Client Agent Selection screen and click Next.
18. At the Database Server screen, enter the SQL network name of the database server (SERVERSQL in this example) in the SQL Server and instance, if applicable field. Leave the ConfigMgr site database field with the default database name and click Next.
19. At the SMS Provider Settings screen, select the SCCM server (SCCM01 in this example) to install the provider. This is the interface that will communicate with the database and cannot be installed on a cluster. Click Next.
20. If this Site Server will manage clients, select the radial next to Install a management point and enter in the FQDN of the server. Click Next.
21. Accept the default port 80 option under HTTP settings. Click Next.
22. At the Updated Prerequisite Components screen select the appropriate options to download the additional updates used during installation. If you are connected to the Internet the download is just over 100MB, otherwise you may specify a local folder or network location for previously downloaded components. Click Next.
23. Review the Settings Summary page and click Next.
24. The Prerequisite Checker will run again; when completed select the Begin Install button to start the installation. The setup will take several minutes.



25. If you receive the following error during setup, it is more than likely due to an issue with Kerberos settings so review your SPN settings and be sure that the SCCM server account is in the local administrators group on both nodes of the cluster. Here is a pretty good thread on some troubleshooting steps specific to this error: http://social.technet.microsoft.com/Forums/en-US/configmgrsetup/thread/b6e1beac-8030-46e3-a289-ab40e6f2867f/.

There are a couple more Next and Finish buttons to click, but I think you can probably handle those on your own by now. I hope this helped to walk you through the basic install process and address a couple of issues you might have run into along the way.
You can download the PDF version of the entire tutorial here.

I was able to get this finished much faster than I thought. You gotta love production change freeze windows to free up a little extra time, eh? Anyway, picking up where we left off let’s finish up a few more items on the cluster resources and move on to the SQL installation. If you need to review the first part of this tutorial, click here.

Preparing the Cluster for SQL

Microsoft recommends clustering the MSDTC which is used by SQL for transactions and distributed queries. From what I read on the topic you can still install SQL on the cluster without clustering the MSDTC, but it may hamper performance of the application. I’ve always clustered the MSDTC resource if I was clustering SQL.

We will need to create a couple of additional resources that will be dependencies for the MSDTC resource first. One of the resources will be the network name of the cluster. This is the name that you would use when you wanted to access a share, or RDP, etc to the cluster and access the active node without having to verify which node was currently active. For this example, I will use SERVER as the network name for the cluster. This name will allow me to simply RDP to SERVER and not care whether or not SERVER01 or SERVER02 was currently active; it will automatically connect me to whichever node is active.

Note – do not confuse the Network Name resource with the cluster management name (SCCMCLUSTER in this example). That name is simply for connecting to the cluster mgmt console.

1. Open Cluster Administrator; expand the resource group (not the Cluster Group). Select New -> Resource.

2. In the New Resource screen, name the resource IP Address.
3. Change the Resource type to IP Address.
4. Leave the Group set to default. Do not use the Cluster Group for any of these resources.
5. In the Possible Owners screen, verify both nodes are listed under Possible Owners and click Next.
6. Click Next at the Dependencies screen without making any changes. The IP Address resource does not require any dependencies.
7. Enter a valid IP address in the Address window as well as a valid subnet mask. Select Public for the Network dropdown and leave Enable NetBIOS for this address checked.
8. Click Finish.
9. When the wizard completes, right click on the IP Address resource and select Bring Online.

10. Right click on the resource group again (not the Cluster Group) and select New -> Resource.
11. Since this will be the network name of our cluster, let’s simply make the Name field SERVER.
12. For the Resource Type, select Network Name in the dropdown.
13. Leave the group as default and click Next.
14. At the Possible Owners screen, verify that both server nodes are included under Possible Owners. Click Next.
15. Since a host name needs an IP address to resolve against, select the IP Address resource from Available resources and move it over to the Resource dependencies column. Click Next.

16. At the Network Name Parameters screen, enter in the name of the cluster (in this example, it’s SERVER). Enable the checkboxes next to DNS Registration Must Succeed and Enable Kerberos Authentication. Click Finish.
17. Right click the new resource in the Cluster Administrator and select Bring Online.
1. Note – This may take several seconds as the resource will create a DNS entry in Active Directory as well create the SPN for the cluster service.
2. If the resource fails to come online, verify that a DNS record was created for the cluster name with the appropriate IP address from the dependency resource. Also verify that the server has the following SPN’s registered in Active Directory:

MSClusterVirtualServer/SERVER
MSClusterVirtualServer/SERVER.DOMAIN.COM (Replace SERVER with the appropriate server hostname and FQDN)

3. If you need to manually create the SPN’s, you can use adsiedit or create them using the setspn utility with the following syntax:
   Setspn –A MSClusterVirtualServer/SERVER SERVER
   Setspn –A MSClusterVirtualServer/SERVER.DOMAIN.COM SERVER
   (Replace SERVER with the appropriate server hostname and FQDN and yes, SERVER is listed twice in the above examples because you must specify the computer object target to modify the ServicePrincipalName attributes in Active Directory.)

18. Right click on the resource group name one more time and select New -> Resource.
19. In the Name field, enter MSDTC.
20. Select Distributed Transaction Coordinator as the Resource Type.
21. Leave the Group field unchanged as before (remember, NOT the Cluster Group). Click Next.
22. Verify both nodes are included in the Possible owners column. Click Next.
23. The MSDTC resource will require two dependencies; the Physical Disk (shared disk) and the Network Name resources as shown here.

24. Add the two resources to the Resource dependencies column and click Finish.
25. Right click on the MSDTC resource and select Bring Online.
26. Take a breather; we are now ready to start installing SQL Server 2005.

Installing SQL Server 2005 on the Cluster

1. Verify both nodes are online and SERVER01 is the active node. Run the install from SERVER01.
2. The install must be run using a domain account with local administrator access on both nodes of the cluster.
3. Accept the EULA and click Next.
4. Install the required prerequisites by selecting the Install button.
5. Select Next to begin the installation wizard.
6. Address any issues that occur in the System Configuration Check and click Next.
7. Enter the Registration Information and click Next.
8. At the Component to Install screen, select the following and click the Advanced button.

a. SQL Server Database Services
b. Create a SQL Server failover cluster
c. Workstation components, Books Online, and development tools. (only required if you want a local copy of books online and dev related items; not required for SCCM).

9. Click Next after making any adjustments to the install.
10. At the Instance Name screen, select the Default instance (unless you need to specify a Named instance on the server) and click Next.
11. At the Virtual Server Name screen, enter the name of the SQL virtual server. This is the server name that will be used to actually connect to the SQL instance, not the server OS itself. You will specify this name anytime you need to connect to this server for SQL services such as to install a database, or open the instance using SQL Management Studio, etc. For this example, we will use SERVERSQL. Click Next.
12. In the Virtual Server Configuration screen, enter an IP address that will be used for the Virtual Server Name entered in the previous step. Be sure to select Public in the dropdown for the Network to use field. Click the Add button and then Next.
13. Select the resource group (not the Cluster Group) in the Cluster Group Selection screen. Also, verify that the correct shared disk (not the Quorum disk) is listed in the Data files field. It will read something like E:\Microsoft SQL Server\. Click Next.

here.
1. Select Windows Authentication Mode at the Authentication Mode screen. Click Next.
2. Accept the default settings at the Collation Settings screen and click Next.
3. Select any checkboxes you wish on the Error and Usage Report Settings screen and click Next.
4. Click the Install button to begin the installation process.
5. Obviously when the process has completed, click the Finish button to complete.
6. Launch the SQL Server Management Studio and verify you can connect to the new instance. Remember, you will need to connect to the network name of the SQL Server instance, not the server itself. In this example, we specify SERVERSQL in the connection window instead of SERVER.
7. Update SQL to Service Pack 3

The next post in this walkthrough tutorial will cover installing System Center Configuration Manager 2007 on the clustered environment covered in these two tutorials as well as some of the pitfalls you may run into during the install process.
You can download the PDF version of the entire tutorial here.

I’ve noticed that there is not a whole lot of information on installing System Center Configuration Manager 2007 on a remote database cluster at the time I decided to write this so I thought I would attempt to tackle the process. I will attempt to be fairly granular in the steps and will be including information from the “Guide to Creating and Configuring a Server Cluster under Windows Server 2003” from Microsoft.

For this scenario I have a single domain controller (DC01), two cluster nodes (SERVER01 and SERVER02), and the SCCM Server (SCCM01). Each cluster node has two NICS. The two cluster nodes and the SCCM01 have been joined to the domain on DC01.

Configure Networking

1.       Rename the network interfaces on the cluster nodes for easier identification.

a.       Right click and rename Local Area Connection to Public.

b.      Right click and rename Local Area Connection 2 to Private.

2.       Change the binding order for the NICS on both nodes by selecting the Advanced menu and then Advanced Settings.

3.       In the Adapters and Bindings tab, verify the Connections are listed in the following order: Public, Private, Remote Access Connections. If not, move the connections using the ordering buttons on the right until they are in the proper order.

4.       You should use static IP addressing for all cluster interfaces.

Configure the Private Network Adapter

1.       Go to the Properties of the Private NIC and deselect everything except TCP/IP.

2.       Microsoft also recommends you change the speed and duplex of your adapters to 10Mbps and Half speed. I personally set my heartbeat interfaces to 100/Full, but I also use crossover cables between the interfaces of my cluster nodes or they are on a dedicated small switch. Either way, just be sure you don’t leave the selection on Auto/Auto.

3.       Be sure there are no IP entries for the DNS servers.

4.       Click on the Advanced button.

5.       On the DNS tab, be sure there are no IP addresses listed and remove the checkbox selections for Append parent suffixes of the primary DNS suffix and Register this connection’s addresses in DNS.

6.       Click on the WINS tab and deselect the option to Enable LMHOSTS lookup. Also, disable the NetBIOS functionality.

7.       Click OK to save the changes and repeat on the other node.

Setting up Shared Storage

The following steps assume you already have your storage prepared on the SAN or whatever medium of shared storage you choose and you have the LUN’s presented to the HBA’s on each cluster node. You will need to have two storage resources available to both servers; one for the quorum drive and one for the database.

1.       Turn off both nodes of the cluster.

2.       Turn on the first node only.

3.       Select Disk Management from within Computer Management.

4.       Right click each Unallocated disk and format it as a Primary partition. Do NOT convert the disk to Dynamic disk, only use Primary disks. If you use the wizard to write a disk signature it may automatically convert the disk to dynamic. If this happens, right click the disk and select Revert to Basic.

a.       Note – If you are using 64 bit, be sure to format the disks as MBR, not GPT.

5.       Verify that you can create and delete a text file on each drive from each node of the cluster. When testing this, ensure only one node is online at a time. When completed, be sure all nodes are off except the first.

Configure the First Node

1.       Open Cluster Administrator. When prompted with the Open Connection to Cluster window, select Create a new cluster. Click OK.

2.       Select a unique NetBIOS name for the cluster following the usual 15 character NetBIOS limit.

3.       Select the name of the first node to be added to the cluster, click Next.

a.       You will be prompted to enter credentials if you are not logged on with a domain account that also has local Adminstrator privileges. This is simply to complete the install and is NOT the account that will be used to start the Cluster services.

4.       The Advanced button is used when you have complex storage solutions in place and the setup wizard detects disks that you do not want to be shared in the cluster. For the sake of this document, we will simply select the Typical (full) configuration option at the Advanced Configuration Options window. Click OK.

5.       The wizard will now run a series of checks and verifications on your environment. When it’s completed, address any critical issues preventing the cluster from being created and continue by clicking Next.

a.       I have never built a cluster that did not return at least some warnings during this phase. Simply review these warning if they occur and address them as needed.

6.       At the IP Address screen, enter in the IP address that will be used to manage the cluster resources. Click Next.

7.       At the Cluster Service Account Wizard screen enter in the credentials of a domain user account that has Local Administrator rights on each node. Click Next.

8.       Review the summary and select the Quorum button. Select the shared storage drive you allocated for the Quorum drive in the drop down.

a.       Note – If you only see Local Quorum listed in the dropdown, continue on through the steps and I will show you how to correct this after the cluster node is up.

9.       When tasks have completed, click Next.

10.       You should now have the single node resource up and running.

(If you’re Quorum disk is listed as a Quorum Resource under the Resource Type column, proceed to the next section. If your console looks like the example above, you may skip the next section.)

Change the Local Quorum Resource

1.       Open the Cluster Administrator and select Cluster Group under Groups.

2.       Right click and select New/Resource.

3.       Select the Resource Type as Physical Disk. Enter a descriptive name for the disk (something like Quorum since this will be the shared drive for the quorum). Click Next.

4.       Make sure that SERVER01 is listed under Possible owners. Click Next.

5.       Leave the Resource dependencies window empty and click Next.

6.       Select the appropriate disk from the drop down and click Finish.

7.       Select the cluster name in the console (in this example, it would be SCCMCLUSTER).Right click and choose Properties.

8.       Select the Quorum tab and change the resource to the new disk resource created in the previous section.

9.       Finally, verify that the disk that will be used as the database storage drive is listed under the Group 0 folder which is automatically created. If it is missing, create the resource following Steps 3 through 6 above.

Configure the Second Node

1.       Verify that all resources are online for the first node in Cluster Administrator.

2.       Turn on the second node (leave node one online).

3.       From the Cluster Administrator on SERVER01, select File – > New -> Node.

4.       Enter in the name of the computer to be added as the second node and click Next.

5.       Review the analysis summary when completed and address any errors.

Post Installation Configuration

1.       Go to the Properties of the Private network by right clicking on the Private network interface.

 

2.       On the General tab, select the radial next to Internal cluster communications only (private network). Click OK.

3.       Right click on the Public network and select Properties.

4.       Select the radial next to All communications (mixed mode). Click OK.

5.       Right click the cluster name and select Properties.

6.       Select the Network Priority tab and ensure that Private is listed at the top of the list.

7.       Verify the failover functionality by right clicking on Cluster Group in the Cluster Administrator console. Select Move Group to begin the failover.

a.       Before failover test

b       Successful failover

In the next post, I will detail installing SQL Server 2005 on the cluster and creating the SCCM database on the remote cluster as well as the caveats along the way such as configuring SPN’s.

 

You can download the PDF version of the entire tutorial here.