SQL 2012, BIDS, and SSDT

No Gravatar

With SQL Server 2012 Business Intelligence Development Studio is replaced with SQL Server Data Tools.

BIDS = Business Intelligence Development Studio

SSDT = SQL Server Data Tools (http://msdn.com/data/tools.aspx)

If you customize SQL Reporting Services reports for SCCM 2007 or ConfigMgr 2012 in you’ll probably want some GUI tool to help you edit or design the report.  Microsft ReportBuilder 2.0/3.0 is quasi built-in to Reporting Services and allows creating and editing of RDL files in a limited manner.  If you need a powerful tool to create a complex report you’ll likely turn to BIDS.  However, BIDS is now depricated and beginning with SQL 2012 SSDT is the tool going forward.

SSDT utilizs the Microsoft Visual Studio 2010 SP1 or 2012 Shell which is very nice.

A word of caution: It appears that SSDT comes in several flavors and not all support Reporting Services.  Be sure to install SQL Server Data Tools from the SQL Server 2012 SP1 install soruce and NOT the stand-alone / downloadable installer.

Some useful links:

March 1, 2013

Posted In: ConfigMgr, ConfigMgr 2007, SQL Reporting

SCCM Report of Operating Systems and Types

No Gravatar

Based on Sherry Kissinger’s blog http://myitforum.com/cs2/blogs/skissinger/archive/2009/06/23/32bit-vs-64bit-report.aspx here is the summary report for a list of Processors, OS Type, OS Name, and Computer count

[cc lang=’sql’ line_numbers=’false’]
select pr.datawidth0 [Processor bits]
, pr.addresswidth0 [OS bits]
, sys.Operating_system_Name_and0 [OS Name]
,count(sys.resourceid) [Computers]
from v_r_system sys join v_gs_processor pr on sys.resourceid=pr.resourceid
group by pr.datawidth0, pr.addresswidth0, sys.Operating_system_Name_and0
order by pr.datawidth0, pr.addresswidth0, sys.Operating_system_Name_and0

Among other things, this will let you know if you need KB2793312B (see MyITforum), Cumulative Update 1 for SCM 2012 RTM, or even Cumulative Update 2.


January 8, 2013

Posted In: ConfigMgr, ConfigMgr 2007, T-SQL

SCCM and BranchCache

No Gravatar



Configuring BranchCache on the SCCM Distribution Point

  • On the Distribution Point properties, enable “Allow clients to transfer content from this distribution point using BITS, HTTP, and HTTPS”
  • In Server Manager
    • install the BranchCache Feature
    • configure the Windows Firewall to ensure content can be requested
      • [TODO]

Configuring BranchCache on the SCCM Clients.  A domain Group Policy is best, but a local Group Policy as well as a few NETSH commands will work.

  • From a policy
    • Computer Configuration \ Policies \ Administrative Templates \
      • Enable Turn on BranchCache
      • Enable Set BranchCache Distributed Cache mode
      • Enable Configure BranchCache for network files = ?? milliseconds (default is 80, I suggest 20)
      • (optional) Enable Set percentage of disk space used for client computer cache (default is 5%)
    • Computer Configuration \ Policies \ Windows Settings \ Windows firewall with Advanced Security \ Windows Firewall with Advanced Security \ Inbound Rules
      • Enable BranchCache Content Retrieval (HTTP-In)
      • Enable BranchCache Peer Discovery (WSD-In)
    • Computer Configuration \ Policies \ Windows Settings \ Windows firewall with Advanced Security \ Windows Firewall with Advanced Security \ Outbound Rules
      • Enable BranchCache Content Retrieval (HTTP-Out)
      • Enable BranchCache Peer Discovery (WSD-Out)
  • From the command line
  • ?????

How to tell if it is working

  • Start Performance Monitor
  • Add monitor elements (probably from a remote computer)
  • Add BranchCache (specifically
    • BITS: Bytes from cache
    • BITS: Bytes from server
    • Discovery: Attempted discoveries
    • Discovery: Successful discoveries
    • Retrieval: Bytes from cache
    • Retrieval: Bytes from server
    • Retrieval: Bytes served (show how much this computer is providing to other peers)
  • Monitor these counters
  • Deploy something NEW from SCCM ensuring that “download from distribution point” is enabled in order to force BITS.  You could also manually transfer data via BITSAdmin.exe
  • Run this locally to see some good basic configuration and utilization info
  • Calcuate Savings percentage = Retrieval: Bytes from cache / (Retrieval: Bytes from cache + Retrieval: Bytes from server)
  • Calculate Bytes Saved = Hypothetical Server Bytes Without Caching – Client Cache Miss Bytes – Server Cache Miss Bytes – Hash Bytes


  • Windows Server 2008 R2
  • Windows 7 Enterprise or Ultimate
  • BITS 4.0


October 4, 2012

Posted In: ConfigMgr, ConfigMgr 2007

SCCM Unused Packages

No Gravatar

This is a t-sql query to help find Packages which you are no longer using so they can be properly organized/categorized/deleted.

Thanks to John Nelson (Number 2) and Christjan Schumann for doing the heavy lifting.  I only put the two puzzle pieces together.


September 20, 2012

Posted In: ConfigMgr, ConfigMgr 2007, T-SQL

Remove SCCM 2007 Child Primary Site

No Gravatar

Thanks to Tom Popov for a nice set of instructions (instead of Microsoft’s spaghetti @ http://technet.microsoft.com/en-us/library/bb632662.aspx).


CEN – Central Site
PRI – Child Primary Site
SEC – Secondary Site

Here is the procedure how to remove PRI SCCM Child Primary Site from its CEN SCCM Parent Site.

  1. from CEN Central Site via SCCM console
    1. delete PRI child site address
  2. from CEN Central Server execute Preinst.exe which is located in C:\Program Files (x86)\Microsoft Configuration Manager\bin\i386\00000409 folder
    1. Preinst.exe /dump -> shows SCCM sites in SCCM Parent Central Site. The target is to remove PRI Child Primary Site.
    2. Preinst.exe /deljob PRI-> Delete jobs for PRI site
    3. Preinst.exe /delSITE PRI CEN -> Delete PRI site
    4. Preinst.exe /deljob PRI -> Execute again to delete any job in meantime
    5. Preinst.exe /dump -> Executing again shows that PRI Child Primary Site has been removed from SCCM Parent site.
  3. from PRI Primary Site via SCCM console remove settings from Report to parent site to Central site

Finally you can see that all locked out items have been removed. All Software deployments packages, Advertisements, OS deployment packages, and Task Sequences have been inherited form SCCM Parent site into SCCM Child Site and can be modified.

September 20, 2012

Posted In: ConfigMgr 2007