SCCM SP1 and Itanium – NOT supported
Back in October 2007 when I was doing my original Pilot deployment of SCCM (RTM), I posted on the TechNet forums asking questions about whether putting the site database on a SQL cluster that was running on the Itanium (IA64) architecture was a supported installation scenario. Stan White replied back that both clustered as well as Itanium were supported. After installing, everything was working fine…no problems worth mentioning.
Fast forward seven months. I’m in the midst of my SCCM rebuild. I’m doing so on Server 2008 and SCCM SP1. Everything appeared to be working except that the SMS_SITE_SQL_BACKUP component refused to install on the SQL server. In the sitecomp.log file was the following message (only three lines out of a couple of hundred log entries):
Subselect Query in SCCM
Update (1/30/2010): We recently discussed subselect queries at the Minnesota System Center User Group. That discussion prompted me to write an updated post on subselect queries.
Please refer to the following post for better information on how to create subselect queries that are faster and more efficient.
https://verbalprocessor.com/2010/01/30/better-subselect-queries/
Original Post:
One of the things that I have fought with in SMS/SCCM is a “NOT” query. Example is needing a query that shows me all computers that do not have Office 2007 installed. If you create your query to look in Add/Remove Programs and find a “Display Name” that is not “Microsoft Office Professional Plus 2007”, you will not get what you might expect. The reason is that it finds a computer that has something like Adobe Acrobat installed. Well…that is a display name in Add/Remove Programs that isn’t Office 2007…so that computer gets returned by the query.
To get the expected results you have to run one query that selects all computers that DO have Office 2007 (or whatever other program you are interested in…for that matter it doesn’t have to be a program…could be anything you want to query on), then you run another query that gives you all of the computers that are NOT in the first query. This is called a subselect query. This query ends up looking like the following:
select SMS_G_System_SYSTEM.Name from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM.Name not in (select SMS_G_System_SYSTEM.Name from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = “Microsoft Office Professional Plus 2007”)
I am not a database guy. I am not a really proficient query writer. Subselect queries confuse the heck out of me. I have fought trying to write subselect queries for a couple of years now. I have really struggled with them.
Today while setting up something in SCCM, I needed a subselect query. I need to find computers that are a member of a particular AD OU, and if they don’t already have certain programs, I want the programs to automatically install. That requires a subselect query. They confuse me. I went to myITforum.com to find an example that I could then modify.
In my searching, I came across this thread. In that thread, someone linked to an article written by Rod Trent about how to create subselect queries. WOW is that a good article! What makes me feel really stupid though is that there has been a way since SMS 2003 to create a subselect query by pointing and clicking…I had no idea and wish I had known before today. It made me really happy to find it and see it work. I actually got out of my chair and started dancing in the office. The people around me looked at me funny, but honestly I was happy enough that I didn’t care!
And for those of you who know me…go ahead and try to get that image of me doing the happy dance out of your head! Good Luck! :-)
SCCM SQL Cluster Problem
Earlier this week I had an issue with backing up SCCM that was because Kerberos was not enabled on the cluster. Got that fixed, but I was noticing other things on my SCCM server that just didn’t seem right. (Instructions for how to enable Kerberos are in the link above.) In particular I noticed that my Site System Status was red. In looking into this I saw where SCCM was referencing the SQL cluster nodes directly…not the SQL cluster. That’s not good. So I took a look at the Site Systems (under Site Settings), and here is what I saw:
What you see here is that the SQL cluster does NOT hold the site database role. That role is held directly by the SQL nodes. What happened was that although Kerberos must be enabled on the cluster for normal SCCM operation, the pre-req checker apparently does not check for this. As a result it allowed the install to go through and ended up installing directly to the nodes instead of to the SQL cluster…because it could not see the cluster since Kerberos was not enabled on it. Anyway…all of that said…it’s a major problem. Site Status is red. Who knows what would happen in the event of a SQL node failover.
So I got to thinking. I’m pretty sure the problem is a result of the database being created on the SQL server before Kerberos was enabled. In theory, I should be able to move the DB elsewhere, then move it back (now that Kerberos is enabled) and everything would be lovely again. Nice theory. But will it work? Enough thinking…let’s find out.
I moved the DB to a SQL named instance on the same server using the instructions found here. [Note…at the time of this writing there is a mistake in the instructions. Between steps 2 and 3 should be a step about actually going into SQL server and detaching and attaching the site DB. I reported it, and Microsoft acknowledged that it is missing and it will be fixed in the next update of the documentation.] After bringing up SCCM on the named instance, I shut it back down and used the same process to move it back to the default instance. Here is what it looks like now:
Note that the site database role is on the SQL cluster now. The two nodes are still in the list, but they have no roles associated with them. Right clicking them does not give an option to delete. According to Wally Mead and Stan White, those two should age out of the system after 30 days. The very nice thing is that my Site Status is now a lovely shade of green.
I reported this issue as a bug in SCCM. Got a great response from Wally Mead. He assigned it to the SCCM SP1 team for possible inclusion in SP1. Very cool!
SCCM Backup Issues
For the last week I have been attempting to back up my SCCM server before it goes into production. The backup has been failing, so I have been in major “trouble shoot” mode. Basic scenario is this… SCCM is installed on a VMWare virtual machine. The SQL database is offloaded to a clustered SQL server. When the backup ran, it would fail after about five seconds and leave the following four lines in the smsbkup.log.
>>>>>>>>>>>>
Info: Sending message to start the SQL Backup…
Couldn’t connect to \\SQLcluster registry
STATMSG: ID=5049 SEV=E LEV=M SOURCE=”SMS Server” COMP=”SMS_SITE_BACKUP” SYS=SCCMserver SITE=LHT PID=3400 TID=924 GMTDATE=Wed Jan 23 19:21:16.539 2008 ISTR0=”” ISTR1=”” ISTR2=”” ISTR3=”” ISTR4=”” ISTR5=”” ISTR6=”” ISTR7=”” ISTR8=”” ISTR9=”” NUMATTRS=0
Error: Failed to send start message to the SqlBackup.
>>>>>>>>>>>>
I re-confirmed that the SCCM server’s machine account was in the admin group on the SQL server. I also knew that I had already taken care of the SPN registration issue, so I posted on the Technet SCCM forum. In hindsight, Stan White (a moderator on the forum) nailed the answer on his first reply…I just misunderstood what he was saying. After much other troubleshooting, I realized that if I started a cmd prompt as local system, I was able to map a drive to the administrative shares on the SQL server nodes as local system, but I was NOT able to map a drive to the cluster. (i.e. SQLcluster is made up of SQLserver1 and SQLserver2. I was able to map to \\SQLserver1\c$, but was not able to map to \\SQLcluster\c$.) This led me to search Google and found this thread (and Ragnar’s post in particular) which put me in the right direction…the direction that Stan specifically pointed to.
The root problem is that Kerberos authentication was not enabled on the cluster. When Kerberos is enabled on the cluster, it publishes the cluster name to Active Directory. Until that is done, the server name “SQLcluster” does not exist in AD…so it can’t be communicated with via Kerberos. I found a few articles that talk in more detail about how to enable Kerberos on the cluster here, here, and here.
After our DBA enabled Kerberos on the cluster last night, I was able to get a successful backup. Now I can move on to other things.
I’d like to acknowledge that my friend Tim is the one who asked a couple of key questions about authentication that caused me to find Ragnar’s post above.
-
Recent Visitors
Subscribe
Recent Comments
-
The Realm of the Verbal Processor 