ConfigMgr 2012 RTM to R2 Upgrade checklist

July 9, 2014

There are a number of good articles on how to upgrade from ConfigMgr 2012 RTM to Sp1, and from Sp1 to R2. If anyone needs to upgrade from RTM to R2, here are the steps:

Note: that the upgrade was performed on a single site, SQL Server was local, and on Server 2008 R2.  OSD was not in use. WSUS used a SUSDB database on SQL Server.

    Review system status/heath check – the ConfigMgr 2012 site was given a clean bill of health, with the exception of native SQL backup missing the ConfigMgr database. This was corrected.

    Backup – reviewed and corrected backup strategy for ConfigMgr 2012. Starting with ConfigMgr 2012 Sp1 and later, only native SQL Server backups are needed. (For more information, see: ConfigMgr 2012 Site Backup and Recovery)

    SQL Server database optimizationImplement Ola Hallengrens’ script; index & statistic optimization plan, to run 1x/week on all user databases. The benefit of this optimization, SUSDB will process updates faster, and ConfigMgr database optimization will benefit console users and update processes.

    ConfigMgr 2012 SP1 Upgrade – using installer, download all ConfigMgr prerequisites locally.

    Note:

    In SCCM 2012 the download pre-reqs command is:

    SetupDL.exe <target dir>

    The SetupDL.exe file is located in the SMSSETUP\BIN\X64 directory.

    Install ADK 8.1 – (ADK – required for Sp1 & R2 upgrade: select deployment tools, Win PE & USMT)

    Note: if the ADK was previously installed, an uninstall would be required here…

    Run SP1 installer upgrade – verify upgrade was successful by checking console version, and sitecomp.log.

    Backup SQL Server DB

    SQL Server upgrade – SQL Server was presently at SQL Server 2008 R2, decision was made upgrade to SQL Server 2012 Sp1, then apply SQL Server 2012 Sp1 Cu9

    ConfigMgr 2012 R2 Upgrade – using installer, download all ConfigMgr prerequisites

    Run R2 upgrade – verify upgrade was successful by checking console version, and sitecomp.log.

    Run R2 CU1 – verify upgrade was successful by checking console version, and sitecomp.log.

    Client Update – ConfigMgr 2012 RTM to R2

    Enable Automatic Site Hierarchy client upgrade – this upgraded the clients directly from RTM to R2.

    ConfigMgr R2 Cu1 Update – verified update.

    Create client deployment to upgrade clients from ConfigMgr R2 to R2 Cu1 – x64 & x86.

Post deployment, Software Updates refused to synchronize.

The WCM.log file will indicate:

Checking for supported version of WSUS (min WSUS 3.0 SP2 + KB2720211 + KB2734608)

Supported WSUS version not found

Resolution: These WSUS updates needed to be deployed post ConfigMgr 2012 SP1 to correct the supported WSUS version failure.

The updates are:

· KB2734608: http://support.microsoft.com/kb/2734608

· KB2720211: http://support.microsoft.com/kb/2720211

How to Uninstall Lync 2010 using ConfigMgr 2012

July 8, 2014

 

Once you’ve deployed Office 2013, with integrated Lync 2013; you may have noticed that Lync 2010 is still installed.

And, that Lync 2010 does not always uninstall cleanly.

Here’s how to remove Lync 2010.

Create a task sequence.

Add > General > Run command Line

Rename Name: Taskkill – Communicator

Copy/paste into the command line

taskkill /f /im communicator.exe /t

clip_image001

For the first two steps, click option tab and enable "continue on error"

clip_image002

Add > General > Run command Line

Rename Name: Taskkill – ucmapi.exe

Copy/paste into the command line

taskkill /f /im ucmapi.exe /t

clip_image003

Add > General > Run command Line

Rename Name: Uninstall Lync 2010

Copy/paste into the command line

msiexec /x {81BE0B17-563B-45D4-B198-5721E6C665CD} /q

clip_image004

Reference:

To locate MSI uninstall GUIDs:

When you install an Office 2010 suite or one of the stand-alone Office 2010 programs, one or more product codes, or GUIDs, are created in the following registry subkey:

HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall

From <http://support.microsoft.com/kb/2186281>

Softchoice – Pre-sales position

July 7, 2014

We’re hiring! Softchoice is looking for a motivated individual for a pre-sales technical position.

Position will be located in Southern California, would consider someone located in Seattle.

Responsibilities

  • Responsible for Microsoft Product & Services Target Attainment
  • Project Manage Microsoft Enterprise Agreement Renewal & True Up Process
  • Drive Key Renewal & True Up Metrics
  • Manage a pipeline of Microsoft Sales Opportunities
  • Provide Pre Sales Support to Sales Reps & Customers on Microsoft Licensing & Solutions
  • Identify new Enterprise Agreement Prospects
  • Identify upsell & cross sell opportunities on existing customer base
  • Successfully sell & position Microsoft product and services to customers
  • Develop customer proposals & pricing comparisons for Microsoft licensing solutions
  • Deliver Microsoft product roadmap, licensing and financial content to customers
  • Participate in local customer events to deliver Softchoice content
  • Develop & Deliver training to Softchoice sales teams on Microsoft products & services
  • Engage with local Softchoice sales teams to sell jointly into sales territories and customer accounts
  • Engage with local and phone based Microsoft sales teams to accomplish joint goals
  • Engage with Softchoice Enterprise Architects and Professional Services for full technology solution design and deployment
  • Identify and recommend remediation solutions to customers as a result of Softchoice Asset Management licensing audits

Requirements:

  • Minimum Education – Bachelor’s Degree or equivalent
  • Minimum 2 years relevant industry experience;
  • Minimum 3 years in either a sales or marketing capacity
  • Strong Microsoft background
  • Must have work experience in the IT industry
  • Solid understanding of the commercial IT market for hardware and software
  • Strong knowledge of Microsoft Office (Outlook, Word, Excel, PowerPoint)
  • Excellent verbal and written communication skills
  • Experience managing executive level relationships
  • Creative and strategic thinker with strong presentation skills
  • Strong analytical skills are required
  • Self-managed and motivated individual with strong leadership skills

For more information, please contact Steve at steve (dot) thompson (at) softchoice (dot) com

SQL Server 2012 SP2 released

June 16, 2014

 

Download here:

http://www.microsoft.com/en-us/download/details.aspx?id=43340 

List of bugs that are fixed:

http://support.microsoft.com/kb/2958429

ConfigMgr 2012 Index Optimization

May 27, 2014

A recent blog post on index defragmentation and comments deserve some space here.

The blog:

http://blogs.technet.com/b/smartinez/archive/2014/03/28/talking-database-in-configmgr.aspx

The blog does a really good job exposing how the Index optimizer decides to rebuild indexes. That is a good thing!

Lets walk through some of the key points:

This query is what ConfigMgr executes when running the Rebuild Index site maintenance task

SELECT DISTINCT sch.name + ‘.’ + Object_name(stat.object_id),
                ind.name,
                CONVERT(INT, stat.avg_fragmentation_in_percent)
FROM   sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, ‘LIMITED’) stat
       JOIN sys.indexes ind
         ON stat.object_id = ind.object_id
            AND stat.index_id = ind.index_id
       JOIN sys.objects obj
         ON obj.object_id = stat.object_id
       JOIN sys.schemas sch
         ON obj.schema_id = sch.schema_id
WHERE  ind.name IS NOT NULL
       AND stat.avg_fragmentation_in_percent > 10.0
       AND ind.type > 0
ORDER  BY CONVERT(INT, stat.avg_fragmentation_in_percent) DESC 

[ST] Identify any table index with more than 10% fragmentation.

 

EXEC Sp_msforeachtable
  @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)" 

[ST] The DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table.

Using the sp_MSforeachtable will run this statement for each table, allowing an 80% fill factor for each index. (20% index free space will reserved for additional index growth). Assumption is made here that the re-indexing will occur for each table located within the previous query.

The remainder of the article shows as an alternative to the (broken) maintenance plan how to create an SQL Server Maintenance Plan Wizard to rebuild indexes and update statistics.

Then summarizes with how to validate the degree of index fragmentation, both before and after.

Now to the comment…

…that basically involves creating a maintenance database and then running Ola Hallengren’s SQL script against it to create the necessary objects and then run the index optimizer script against the ConfigMgr 2012 database on a weekly basis. There seems to be some, dare I say disagreement, amongst some SCCM MVPs regarding the best way to go about doing this. Some of the MVPs believe that the Rebuild Indexes Site Maintenance Task in ConfigMgr 2012 still has some issues. Therefore, some of them are stating that the best method is to keep the Rebuild Indexes disabled and then use the Steve Thompson method. I just want a consensus and use a method that is going to work, but one that is not going to create additional overhead for me.

Summary

Consensus is always a good thing, however, it is always not that simple or easy. Also, I do not believe there is really any disagreement within the MVP community about this information. In light of that, I’d like to share what I know.

I first discovered Ola’s excellent index and statistics optimization task after looking for a solution to the Configuration Manager Index Maintenance task not always running. We’ve seen this failure on SCCM 2007 and ConfigMgr 2012. This has been reported by other MVPs, and observed first hand by this MVP on multiple occasions, on multiple sites. This led to a presentation at MMS 2013:

http://channel9.msdn.com/Events/MMS/2013/UD-B407

The script usage was documented here:

http://stevethompsonmvp.wordpress.com/2013/05/07/optimizing-configmgr-databases/ 

Ironically, at the MMS 2013 conference, we learned that Microsoft IT that is responsible for running their ConfigMgr 2012 site runs Ola’s Index Maintenance script as well.

What makes Ola’s script unique, it does not take a blunt force approach to rebuilding every index for every table, rather only rebuild indexes that need to be reorganized or rebuilt if necessary. And, this is based on thresholds you can configure.

Other advantages:

Further, it will only update the Statistics that need to be updated.

If indexes are only lightly fragmented a reorganization might be the most efficient method. However, if heavily fragmented (>30%), then it might be faster to rebuild (essentially drop and create an index).

It is possible to set the number of pages the index occupies before running any tuning… if the index falls on less than 1,500 pages, SQL will likely read the entire index into memory. No real need to optimize that index scenario.

Furthermore, the more frequently this task is run, the less time it takes to complete, because there is less work to do!

Conclusions

Will the other method optimize your indexes and statistics? Yes it will.

Is it better than a non functional site maintenance task? Yes it is.

Is it the most efficient way to optimize a ConfigMgr database? Not in my opinion. Further, this approach does not scale well to very large sites and/or databases.

My recommendation, do your own benchmark testing and then decide.

PowerShell – SQL Audit Script

May 19, 2014

 

As demonstrated at TechEd 2014, here is the PowerShell script.

Actually there are two scripts.

The first script is contained in this page, copy/paste (as Johan says ‘steal with pride’ ;) and name the first:

‘SQLAuditv02.ps1’

The second was a community script that converts the CSV files to Excel.

Download that one, and name it:

‘compilereports.ps1’

Note that these scripts need to be copied to, and run from  the C:\Temp drive, change first (3) variables as needed.

As requested, if there are any enhancements that you would like to see, please let me know?

 

$SQLServer = "CM01" #use Server\Instance for SQL instances
$SQLCMDBName = "CM_Poc"

# Change path as appropriate
$dir = "C:\TEMP\"

# Default SQL Server initial DB connection
$SQLDBName = "Master"
 
# Create and Open new connection 
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

# 01-Server Properties
$SqlQuery = "SELECT SERVERPROPERTY('MachineName') AS [MachineName], SERVERPROPERTY('ServerName') AS [ServerName],  
SERVERPROPERTY('InstanceName') AS [Instance], SERVERPROPERTY('IsClustered') AS [IsClustered], 
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS], 
SERVERPROPERTY('Edition') AS [Edition], SERVERPROPERTY('ProductLevel') AS [ProductLevel], 
SERVERPROPERTY('ProductVersion') AS [ProductVersion], SERVERPROPERTY('ProcessID') AS [ProcessID],
SERVERPROPERTY('Collation') AS [Collation], SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled], 
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly];"
 
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
 
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
$DataSet.Tables[0] | export-csv ($dir + "01-Server Properties.csv") -notypeinformation

# 02-Windows Info
$SqlQuery = "SELECT windows_release, windows_service_pack_level, 
       windows_sku, os_language_version
FROM sys.dm_os_windows_info WITH (NOLOCK) OPTION (RECOMPILE);"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "02-Windows Info.csv") -notypeinformation

# 03-Version Info
$SqlQuery = "SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version Info];"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "03-Version Info.csv") -notypeinformation

# 04-SQL Server Install Date
$SqlQuery = "SELECT @@SERVERNAME AS [Server Name], createdate AS [SQL Server Install Date] 
FROM sys.syslogins WITH (NOLOCK)
WHERE [sid] = 0x010100000000000512000000;" 

$SqlQuery = "SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version Info];"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "04-SQL Server Install Date.csv") –notypeinformation

# 05-Configuration Values
$SqlQuery = "SELECT name, value, value_in_use, [description] 
FROM sys.configurations WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);" 

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "05-Configuration Values.csv") -notypeinformation


# 06-DB File Names and paths
$SqlQuery = "SELECT DB_NAME([database_id])AS [Database Name], 
       [file_id], name, physical_name, type_desc, state_desc, 
       CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] > 4 
AND [database_id] <> 32767
OR [database_id] = 2
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "06-DB File Names and paths.csv") -notypeinformation


# 07-DB Properties
$SqlQuery = "SELECT db.[name] AS [Database Name], 
suser_sname( owner_sid ) As [Database Owner],
db.recovery_model_desc AS [Recovery Model], 
db.log_reuse_wait_desc AS [Log Reuse Wait Description], 
ls.cntr_value AS [Log Size (KB)], lu.cntr_value AS [Log Used (KB)],
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %], 
db.[compatibility_level] AS [DB Compatibility Level], 
db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on, db.is_auto_update_stats_on,
db.is_auto_update_stats_async_on, db.is_parameterization_forced, 
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
db.is_auto_close_on, db.is_auto_shrink_on, db.is_cdc_enabled
FROM sys.databases AS db WITH (NOLOCK)
INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)
ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK) 
ON db.name = ls.instance_name
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%' 
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ls.cntr_value > 0 OPTION (RECOMPILE);"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "07-DB Properties.csv") -notypeinformation


# 08-Server Hardware
$SqlQuery = "SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count], 
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)], 
sqlserver_start_time --, affinity_type_desc -- (affinity_type_desc is only in 2008 R2)
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0]  | export-csv ($dir + "08-Server Hardware.csv") -notypeinformation


# 09-System Manufacturer
$SqlQuery = "EXEC xp_readerrorlog 0,1,'Manufacturer';" 

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "09-System Manufacturer.csv") -notypeinformation


# 10-Fixed Drive Freespace
$SqlQuery = "EXEC xp_fixeddrives;" 

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "10-Fixed Drive Freespace.csv") -notypeinformation



# 11-IO Util by DB
$SqlQuery = "WITH Aggregate_IO_Statistics
AS
(SELECT DB_NAME(database_id) AS [Database Name],
CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
GROUP BY database_id)
SELECT ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS [I/O Rank], [Database Name], io_in_mb AS [Total I/O (MB)],
       CAST(io_in_mb/ SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)) AS [I/O Percent]
FROM Aggregate_IO_Statistics
ORDER BY [I/O Rank] OPTION (RECOMPILE);"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "11-IO Util by DB.csv") -notypeinformation



# 12-System Memory
$SqlQuery = "SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)], 
       available_physical_memory_kb/1024 AS [Available Memory (MB)], 
       total_page_file_kb/1024 AS [Total Page File (MB)], 
       available_page_file_kb/1024 AS [Available Page File (MB)], 
       system_cache_kb/1024 AS [System Cache (MB)],
       system_memory_state_desc AS [System Memory State]
FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);" 

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "12-System Memory.csv") -notypeinformation



# 13-Process Memory
$SqlQuery = "SELECT physical_memory_in_use_kb/1024 AS [SQL Server Memory Usage (MB)],
       large_page_allocations_kb, locked_page_allocations_kb, page_fault_count, 
       memory_utilization_percentage, available_commit_limit_kb, 
       process_physical_memory_low, process_virtual_memory_low
FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "13-Process Memory.csv") -notypeinformation



# 14-SQL Log file freespace
$SqlQuery = "DBCC SQLPERF(LOGSPACE);"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "14-SQL Log file freespace.csv") -notypeinformation

# Part 2
# Change to CM database for remainder of queries
# Reconnect to previous connection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLCMDBName; Integrated Security = True"

# 15-CM File Sizes
# DB Name needed here...
$SqlQuery = "SELECT f.name AS [File Name] , f.physical_name AS [Physical Name], 
CAST((f.size/128.0) AS decimal(15,2)) AS [Total Size in MB],
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS decimal(15,2)) 
AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name]
FROM sys.database_files AS f WITH (NOLOCK) 
LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) 
ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);"

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "15-CM File Sizes.csv") -notypeinformation

# 16-CM DB Statistics
$SqlQuery = "SELECT DISTINCT
     OBJECT_NAME(s.[object_id]) AS TableName,
     c.name AS ColumnName,
     s.name AS StatName,
     s.auto_created,
     s.user_created,
     s.no_recompute,
     s.[object_id],
     s.stats_id,
     sc.stats_column_id,
     sc.column_id,
     STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated
 FROM sys.stats s JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
     JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
     JOIN sys.partitions par ON par.[object_id] = s.[object_id]
     JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
 WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
    AND (s.auto_created = 1 OR s.user_created = 1);"  
    
$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "16-CM DB Statistics.csv") -notypeinformation

# 17-CM Index Frag
# This one may be a long running query, it is important to determine if the indeses are fragmented.
$SqlQuery = "SELECT DB_NAME(database_id) AS [Database Name], OBJECT_NAME(ps.OBJECT_ID) AS [Object Name], 
i.name AS [Index Name], ps.index_id, index_type_desc,
CONVERT(decimal,  avg_fragmentation_in_percent), fragment_count, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,N'LIMITED') AS ps 
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON ps.[object_id] = i.[object_id] 
AND ps.index_id = i.index_id
WHERE database_id = DB_ID()
AND page_count > 1500
ORDER BY avg_fragmentation_in_percent DESC OPTION (RECOMPILE);" 

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "17-CM Index Frag.csv") -notypeinformation

# 18 - VLF Information
# Virtual Log File - numbers below 50 are generally good. Large numbers of VLF can affect write performance, and startup times
$SqlQuery = "DBCC LOGINFO;" 

$SqlCmd.CommandText = $SqlQuery
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
clear
 
$DataSet.Tables[0] | export-csv ($dir + "18-CM VLF Info.csv") -notypeinformation


$SqlConnection.Close()

# Call *.CSV to Excel converter!
Invoke-Expression c:\temp\compilereports.ps1

TechEd 2014 – MVP Experts Panel

May 19, 2014

On Monday, May 10 2014, 5 MVPs presented at TechEd. I had the privilege of appearing on stage with Greg Ramsey, Jason Sandys, Johan Arwidmark and Kent Agerlund.

Session bio:

In this session, Configuration Manager MVPs demonstrate how to take Configuration Manager 2012 R2 to the next level. Attend this session to see how Configuration Manager MVPs are using the product and providing solutions you won’t find in a book or in the documentation. This session covers: ConfigMgr and PowerShell for the win; management point selection details—all the guts, and all the glory; deep debugging of ConfigMgr OS deployment in under 10; collection deep dive—best practices for designing and performance, as well as deep troubleshooting; and SQL Server and ConfigMgr 2012 R2—best practices for backup and maintenance, and building audit reports of SQL using PowerShell.

 

Watch it here:

http://video.ch9.ms/sessions/teched/na/2014/PCIT-B410.mp4

Session scripts to follow.

SQL 2014 officially supported for ConfigMgr 2012

April 29, 2014

 

SQL Server 2014 is now supported for ConfigMgr 2012 Sp1 and ConfigMgr 2012 R2. Note, you’ll need to install ConfigMgr 2012 first on SQL 2012, then upgrade SQL 2012 to SQL 2014.

Details here:

http://blogs.technet.com/b/configmgrteam/archive/2014/04/29/support-announcements-for-april-2014.aspx

Creating a Linked server from SQL 2005/2008 to SQL 2012

April 24, 2014

Resolved an interesting issue today, where a client needed to create a linked server from SQL 2005/2008 to SQL 2012.

Received the infamous login failed ‘NT AUTHORITY\ANONYMOUS’ error.

NT AUTH

This typically means that the target SQL Server needs to have a SPN (Service Principal Name) created, or it is a Kerberos issue. In this case, we had checked that the SPN was properly created and Kerberos were not an issue, as these had been previously validated.

We retested the linked server with SQL 2012 to SQL 2012, this worked fine. Strange.

In researching the root cause, located this article: Linked Servers (Database Engine) which provided a nice graphic of the architecture and indicates that SQL 2012:

“The Microsoft SQL Server Native Client OLE DB Provider (PROGID: SQLNCLI11) is the official OLE DB provider for SQL Server.”

From this it made sense that the down-level SQL versions did not have the proper SQL Native client library installed. Two ways to install the Native Client

- From the SQL 2012 install media, install the client data tools

- Found that the native client redistributable is available from this location:

In addition to installing SQL Server Native Client as part of the SQL Server installation, there is also a redistributable installation program named sqlncli.msi, which can be found on the SQL Server installation disk in the following location: %CD%\Setup\.

The linked server now works perfectly.

Alternately, you can download information about the SQL Server Native Client drivers that are used here:

http://support.microsoft.com/kb/2726013

Connecting to SQL Server 2012 on Windows Server 2012

April 11, 2014

Windows Server 2012 is more secure by default, which is a good thing.

However, be sure to enable the windows firewall rule for SQL Server. Otherwise, you get a cryptic connection dialog like this one:

image

Description is outlined here:

http://technet.microsoft.com/en-us/library/ms175043.aspx


Follow

Get every new post delivered to your Inbox.