techblog

SP 2013 – Check for “Max Degree Of Parallelism” via PowerShell

Jan 30, 2013 by Christoph // Leave a Comment

In a automated installation of a SharePoint Server 2013 server farm we had the problem, that the underlying SQL server cluster didn’t match the requirements for a SharePoint Server 2013 installation in matter of the “MAX DEGREE OF PARALLELISM” setting. This has to be “1”, in a standard installation this is “0”.

The setting is explained here and in a lot of other blog post related to SharePoint.

Our Challenge

Via PowerShell, we wanted to check before our SharePoint installation started, what the setting is on the SQL server.

Most blog posts only describe how to set it. Because we where running on a Failover Cluster where we don’t have the permission to change the setting, we only wanted to show an error and stop the execution of our install script.

The solution

The main part in the following solution is the SQL command that gets executed against the database instance. Since the check is performed via a call to a stored procedure (EXEC sp_configure ‘max degree of parallelism’), we have to use a temp-table to store the values in and query the value from this table. The table gets deleted afterwards.

CREATE TABLE #tmpMDOP (name char(30), minimum int, maximum int, config_value int, run_value int); 
INSERT #tmpMDOP(name, minimum, maximum, config_value, run_value) EXEC sp_configure 'max degree of parallelism'; 
SELECT config_value FROM #tmpMDOP;DROP TABLE #tmpMDOP"

Here is the complete PowerShell Function we use in our current PowerShell Script to install SP 2013 automatically:

function Check-SQLServerMaxDegreeOfParallelism ([string]$myInstanceName)
{
	# This function checks if the SQL server instance is set to "max degree of parallelism" value of 1. 
	# This is necessary for SharePoint 2013 Installations and has to be checked beforehand.
	# The value has to be set on ALL Instances of the DB Server Cluster.

	$result = $false

	$sqlInstance = ""
	$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
    	$sqlConnection.ConnectionString = "Server = $sqlInstance; Integrated Security = True; Initial Catalog='master'; Connect Timeout=3"

	try 
	{
		$sqlConnection.Open()
		$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
		$SqlCmd.CommandText = "CREATE TABLE #tmpMDOP (name char(30), minimum int, maximum int, config_value int, run_value int);INSERT #tmpMDOP(name, minimum, maximum, config_value, run_value) EXEC sp_configure 'max degree of parallelism';SELECT config_value FROM #tmpMDOP;DROP TABLE #tmpMDOP"
		$SqlCmd.Connection = $SqlConnection
		$maxDegreeOfParallelismValue = $SqlCmd.ExecuteScalar()
		$SqlConnection.Close()

		if($maxDegreeOfParallelismValue -eq 1)
		{
			$result = $true
		}
	}
    	catch 
	{
		$result = $false
	}
   	finally 
	{ 
		$sqlConnection.Dispose() 
	}

	$result
}

 

Be aware

If you run into the issue during an installation, simply changing the value would not solve the issue. You also have to delete the already created database on the database server itself. Otherwise, your installation will fail if you retry it!

 

 


Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Copyright © 2012 techblog All Rights Reserved.

The posts on this weblog are provided “AS IS” with no warranties, and confer no rights. The opinions expressed herein are personal and do not represent those of my employer.

Designed & Developed by ThemeElephant