April 2 2013

SQL Server guide for System Center 2012

Today I came across a great post today on with comprehensive information on SQL configuration for System Center 2012.  Even if you aren’t deploying System Center products, this is still a great guide for configuring SQL for optimal performance and best practise.

Link is http://gallery.technet.microsoft.com/SQL-Server-guide-for-8584c403

 



----------------------------------------------------------------------------
I use a maximum of one Google Ad per post to help offset some of my blog hosting costs.

----------------------------------------------------------------------------

May 24 2012

SCCM 2012 console in read-only mode

After setting up a SCCM 2012 primary site underneath a central site, when firing up the console on the primary site (even 24 hours later) I get the following error: “Your Configuration Manager console is in read-only mode while this site completes tasks related to maintenance mode. After these tasks are complete you must reconnect you Configuration Manager console before you can edit or create new objects.” 

Checking the Database Replication Status shows that the link is being configured:

All servers run Windows Server 2008 R2 Ent SP1. Both the central and primary sites are configured to use separate SQL instances on a remote SQL 2008 R2 SP1 CU6 server.

Solution:

On the primary site server, I needed to run setup.exe from c:program filesMicrosoft Configuration Managerbinx64 and select ‘perform site maintenance or reset this site’, the ‘modify SQL server configuration’. and change the SQL Server Service Broker (SSB) port to an unused port that is something other than the default (4022 is default). I used 4122.

Click Next and finish the setup wizard. Monitor the rcmctrl.log and sitecomp.log files for activity over the next 20 minutes and you will see reconfugration happening, once this is complete you will have healthy Database Replication and you should be able to open your SCCM console as normal.

 

 

August 15 2011

SCCM report for SQL server Developer, Enterprise and Standard version installations

I had a requirement to report on SQL Server installations in our environment as part of our Microsoft EA true-up process. This included SQL 2000, 2005 & 2008 installations. The difficulty was that you can’t run a simple Add / Remove Programs report because it will report all of the SQL client tools installations as well as the Express and Windows Internal Databases version. I needed to actually report on just the SQL Server Developer, Enterprise and Standard version installations.

After using the MOF edit by Sherry Kissinger (http://myitforum.com/cs2/blogs/skissinger/archive/2010/12/20/installed-sql-05-and-08-version-information-via-configmgr-hardware-inventory.aspx), I then create a report that filtered out much of the details and gave me a summary of SQL 2000-2005-2008-2008R2 Server Developer, Enterprise and Standard version installations. The query for the SCCM report is:

select
sys1.Netbios_name0 as [Machine],
max(Case sql.PropertyName0 when 'SKUName' then
sql.PropertySTRValue0 end) as [SQL 2008 / R2 Edition],
max(Case sql2.PropertyName0 when 'SKUName' then
sql2.PropertySTRValue0 end) as [SQL 2005 Edition],
sys1.ad_site_name0 as [Site Name],
v_R_User.Full_User_Name0 As [Primary Machine User]
from v_r_system sys1
left join v_gs_sql_property0 sql on sys1.resourceid=sql.ResourceID
left join v_gs_sql_property_legacy0 sql2 on sys1.ResourceID=sql2.ResourceID
left Outer Join v_R_User On sys1.User_Name0 = v_R_User.User_Name0
where
(sql.PropertyName0 in ('SKUNAME','SPLevel','version','fileversion')
or
sql2.PropertyName0 in ('SKUNAME','SPLevel','version','fileversion'))
and
(
sql.PropertyStrValue0 LIKE '%standard%' or
sql2.PropertyStrValue0 LIKE '%standard%' or
sql.PropertyStrValue0 LIKE '%enterprise%' or
sql2.PropertyStrValue0 LIKE '%enterprise%' or
sql.PropertyStrValue0 LIKE '%develop%' or
sql2.PropertyStrValue0 LIKE '%develop%'
)
group by sys1.Netbios_name0, sys1.User_Name0, sys1.ad_site_name0, v_R_User.Full_User_Name0

 
 

April 5 2011

SCCM database size

I’ve never been able to find any detailed information about the expected size of the SCCM database. I decided to keep track of this during a recent deployment.

As a general rule, I had usually allowed 500MB for SCCM system database and about 2-5MB for each client that is deployed. Since there are no definative guidelines in the SCCM capacity planning documentation, I decided to closely track the database size on this SCCM deployment.

I have attached the results here – SCCM growth data.

In summary I found that you should allow 500MB for system databases, then allow 5MB for each client you are going to deploy. This number allows space for SQL data and log files. My deployment included almost all features of SCCM including OSD (many driver packages), Software Updates, approx 200 software packages and around 15 secondary sites.

 
 

February 3 2011

SCCM report to show all Apple devices (iPhone, iPod, iPad)

As part of the network discovery process, I noticed that it was picking up the iPhone and other Apple machines (Macs, iPads) that were on the corporate network either via being plugged in or on the wireless LAN.

Shortly afterwards, there was a requirement to report on all the Apple devices found in the environment. SCCM to the rescue.

After visiting the IEEE website (http://standards.ieee.org/develop/regauth/oui/public.html) and doing a search for ‘Apple’, I found the OUI (first 6 hex characters of the MAC address) that all Apple product would have (keep in mind some manufacturers subcontract component manufacture and others include registered firm OUIs in their products).

I then wrote a collection membership rule SQL query to find all the devices that have these OUIs as the start of their MAC address:

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.MACAddresses like "58:55:CA%" or SMS_R_System.MACAddresses like "00:03:93%" or SMS_R_System.MACAddresses like "00:03:93%" or SMS_R_System.MACAddresses like "00:05:02%" or SMS_R_System.MACAddresses like "00:0A:27%" or SMS_R_System.MACAddresses like "00:0A:95%" or SMS_R_System.MACAddresses like "00:10:FA%" or SMS_R_System.MACAddresses like "00:11:24%" or SMS_R_System.MACAddresses like "00:14:51%" or SMS_R_System.MACAddresses like "00:16:CB%" or SMS_R_System.MACAddresses like "00:17:F2%" or SMS_R_System.MACAddresses like "00:19:E3%" or SMS_R_System.MACAddresses like "00:1B:63%" or SMS_R_System.MACAddresses like "00:1C:B3%" or SMS_R_System.MACAddresses like "00:1D:4F%" or SMS_R_System.MACAddresses like "00:1E:52%" or SMS_R_System.MACAddresses like "00:1E:C2%" or SMS_R_System.MACAddresses like "00:1F:5B%" or SMS_R_System.MACAddresses like "00:1F:71%" or SMS_R_System.MACAddresses like "00:1F:F3%" or SMS_R_System.MACAddresses like "00:21:E9%" or SMS_R_System.MACAddresses like "00:22:41%" or SMS_R_System.MACAddresses like "00:23:12%" or SMS_R_System.MACAddresses like "00:23:32%" or SMS_R_System.MACAddresses like "00:23:6C%" or SMS_R_System.MACAddresses like "00:23:DF%" or SMS_R_System.MACAddresses like "00:24:36%" or SMS_R_System.MACAddresses like "00:25:00%" or SMS_R_System.MACAddresses like "00:25:4B%" or SMS_R_System.MACAddresses like "00:25:BC%" or SMS_R_System.MACAddresses like "00:26:08%" or SMS_R_System.MACAddresses like "00:26:4A%" or SMS_R_System.MACAddresses like "00:26:B0%" or SMS_R_System.MACAddresses like "00:26:BB%" or SMS_R_System.MACAddresses like "00:30:65%" or SMS_R_System.MACAddresses like "00:50:E4%" or SMS_R_System.MACAddresses like "00:A0:3F%" or SMS_R_System.MACAddresses like "00:A0:40%" or SMS_R_System.MACAddresses like "04:1E:64%" or SMS_R_System.MACAddresses like "08:00:07%" or SMS_R_System.MACAddresses like "10:93:E9%" or SMS_R_System.MACAddresses like "10:9A:DD%" or SMS_R_System.MACAddresses like "18:E7:F4%" or SMS_R_System.MACAddresses like "24:AB:81%" or SMS_R_System.MACAddresses like "28:E7:CF%" or SMS_R_System.MACAddresses like "34:15:9E%" or SMS_R_System.MACAddresses like "3C:07:54%" or SMS_R_System.MACAddresses like "40:A6:D9%" or SMS_R_System.MACAddresses like "40:D3:2D%" or SMS_R_System.MACAddresses like "44:2A:60%" or SMS_R_System.MACAddresses like "58:1F:AA%" or SMS_R_System.MACAddresses like "58:55:CA%" or SMS_R_System.MACAddresses like "58:B0:35%" or SMS_R_System.MACAddresses like "5C:59:48%" or SMS_R_System.MACAddresses like "60:33:4B%" or SMS_R_System.MACAddresses like "60:FB:42%" or SMS_R_System.MACAddresses like "64:B9:E8%" or SMS_R_System.MACAddresses like "70:CD:60%" or SMS_R_System.MACAddresses like "78:CA:39%" or SMS_R_System.MACAddresses like "7C:6D:62%" or SMS_R_System.MACAddresses like "7C:C5:37%" or SMS_R_System.MACAddresses like "88:C6:63%" or SMS_R_System.MACAddresses like "8C:58:77%" or SMS_R_System.MACAddresses like "8C:7B:9D%" or SMS_R_System.MACAddresses like "90:27:E4%" or SMS_R_System.MACAddresses like "90:84:0D%" or SMS_R_System.MACAddresses like "98:03:D8%" or SMS_R_System.MACAddresses like "A4:67:06%" or SMS_R_System.MACAddresses like "B8:FF:61%" or SMS_R_System.MACAddresses like "C4:2C:03%" or SMS_R_System.MACAddresses like "C8:2A:14%" or SMS_R_System.MACAddresses like "C8:AA:CC%" or SMS_R_System.MACAddresses like "C8:BC:C8%" or SMS_R_System.MACAddresses like "CC:08:E0%" or SMS_R_System.MACAddresses like "D4:9A:20%" or SMS_R_System.MACAddresses like "D8:30:62%" or SMS_R_System.MACAddresses like "D8:A2:5E%" or SMS_R_System.MACAddresses like "DC:2B:61%" or SMS_R_System.MACAddresses like "E0:F8:47%" or SMS_R_System.MACAddresses like "E8:06:88%" or SMS_R_System.MACAddresses like "F0:B4:79%" or SMS_R_System.MACAddresses like "F8:1E:DF%"

I then had a collection that has all the devices that have Apple MACs. Then I simply created a report that shows all the members in this collection.

This report will display all of the Apple devices discovered on the network. This may include computers that have Apple devices connected (eg iPod touch) as their MAC addresses are recorded by SCCM.

Useful.
 
 

February 2 2011

SCCM report to count enabled users in every domain

I needed a SCCM report to count all enabled users in every domain. This is different to the builtin ‘Count users by domain’ as that one only lists domain users that have logged onto a system (that one is from v_R_System).

Query =

SELECT Windows_NT_Domain0, count(*)
FROM
v_R_User
WHERE User_Account_Control0 = '512'
GROUP BY Windows_NT_Domain0
Order by Windows_NT_Domain0

While I am here, I may as well list 2 other reports that were useful to count all enabled computers in every domain, one report for workstations and one for servers:

SELECT Resource_Domain_OR_Workgr0, count(*)
FROM
v_R_System
WHERE Operating_System_Name_and0 LIKE '%workstation%' AND Resource_Domain_OR_Workgr0 NOT LIKE ''
GROUP BY Resource_Domain_OR_Workgr0
Order by Resource_Domain_OR_Workgr0

SELECT Resource_Domain_OR_Workgr0, count(*)
FROM
v_R_System
WHERE Operating_System_Name_and0 LIKE '%server%' AND Resource_Domain_OR_Workgr0 NOT LIKE ''
GROUP BY Resource_Domain_OR_Workgr0
Order by Resource_Domain_OR_Workgr0