Sharepoint 2010: SQL Server Database Engine Configuration
Overview
The following is a collection of checks that can be run against an instance of SQL Server 2005/2008/2008R2/2012 that is currently or will be used as a dedicated instance for sharepoint databases. The checks come from standard best practices and also from the Sharepoint best practices for SQL Server documentation. The script can be used to check an instance and have an easy to view list of items that have failed and the reason for the failure. The script must be run as an entire execution and cannot be run for specific parts.
The Script
--Declaration of Global Variables and Environment Settings
SET NOCOUNT ON
DECLARE @ReturnCode INT
SET @ReturnCode = 0
--Environment Check
DECLARE @Version INT
-- get the spot of the period
SELECT @Version = CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(10)), 0)
SELECT @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(10)), @Version - 1) AS INT)
/*******************************************
Matrix for Sharepoint 2010 SQL Support
8 = SQL Server 2000 - Not supported
9 = SQL Server 2005 - Supported with SP3 CU3
10 = SQL Server 2008/R2
11 = SQL Server 2012
*******************************************/
IF(@Version = 8)
BEGIN
PRINT 'Sharepoint 2010 does not support SQL Server 2000!'
GOTO Finish
END
PRINT 'Checks Run Date: ' + CAST(GETDATE() AS VARCHAR(30))
PRINT 'Checks Run By: ' + SUSER_SNAME()
PRINT 'Checks Run On Server: ' + @@SERVERNAME
PRINT 'Checks Run On Instance: ' + @@SERVICENAME
PRINT 'SQL Server Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(30))
PRINT ''
PRINT 'Starting Checks...'
/* Security Checks */
--Windows Auth Only
DECLARE @AuthMode INT
SET @AuthMode = -1
-- 1 = Windows Only
-- 2 = Mixed
EXEC @ReturnCode = xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', @AuthMode OUTPUT
IF(@ReturnCode != 0)
BEGIN
PRINT 'Windows Only Auth Mode: FAILED With Error'
END
ELSE
BEGIN
IF(@AuthMode = 1)
BEGIN
PRINT 'Window Only Auth Mode: Passed (Windows Auth Only)'
END
ELSE
BEGIN
PRINT 'Windows Only Auth Mode: FAILED (Mixed Mode)'
END
END
SET @ReturnCode = 0
--Non-Standard Port
DECLARE @PortNumber NVARCHAR(6)
DECLARE @Path NVARCHAR(4000)
SET @PortNumber = -1
SET @Path = ''
-- 1433 tcp is the *standard*
IF @@SERVICENAME = 'MSSQLSERVER'
SET @Path = 'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp'
ELSE
SET @Path = 'Software\Microsoft\Microsoft SQL Server\' + @@SERVICENAME + '\MSSQLServer\SuperSocketNetLib\Tcp'
EXEC @ReturnCode = xp_regread 'HKEY_LOCAL_MACHINE', @Path, 'TcpPort', @PortNumber OUTPUT
SELECT @PortNumber = CAST(@PortNumber AS INT)
IF(@ReturnCode != 0)
BEGIN
PRINT 'Non-Standard Port Check: FAILED With Error'
END
ELSE
BEGIN
IF(@PortNumber = -1)
BEGIN
PRINT 'Non-Standard Port Check: FAILED with Error'
END
IF(@PortNumber = 1433)
BEGIN
PRINT 'Non-Standard Port Check: FAILED (Standard Port 1433 TCP)'
END
ELSE
BEGIN
PRINT 'Non-Standard Port Check: Passed - ' + CAST(@PortNumber AS VARCHAR(6))
END
END
SET @ReturnCode = 0
/* Server Configuration Checks */
--Edition (note that Enterprise is preffered)
DECLARE @Edition VARCHAR(20)
SELECT @Edition = CAST(SERVERPROPERTY('Edition') AS VARCHAR(20))
SELECT @Edition = LEFT(@Edition, CHARINDEX(' ', @Edition, 0) - 1)
IF(@Edition = 'Standard')
BEGIN
PRINT 'Edition Check: Passed but not preferred (Standard Edition)'
GOTO EditionFinish
END
IF(@Edition = 'Enterprise')
BEGIN
PRINT 'Edition Check: Passes and Preferred (Enterprise Edition)'
GOTO EditionFinish
END
PRINT 'Edition Check: FAILED using Edition "' + @Edition + '"'
EditionFinish:
--MAXDOP
DECLARE @MAXDOP INT
SELECT @MAXDOP = CAST(value_in_use AS INT) FROM sys.configurations WHERE [name] = 'max degree of parallelism'
IF(@MAXDOP != 1)
BEGIN
PRINT 'MAXDOP Check: FAILED as MAXDOP needs to be 1 (currently: ' + CAST(@MAXDOP AS VARCHAR(2)) + ')'
END
ELSE
BEGIN
PRINT 'MAXDOP Check: Passed (MAXDOP = ' + CAST(@MAXDOP AS VARCHAR(2)) + ')'
END
--Backup Compression
IF(@Version >= 10)
BEGIN
IF((SELECT COUNT(*) FROM sys.configurations WHERE name = 'backup compression default') > 0)
BEGIN
DECLARE @BackupCompression INT
SELECT @BackupCompression = CAST(value_in_use AS INT) FROM sys.configurations WHERE [name] = 'backup compression default'
IF(@BackupCompression = 1)
BEGIN
PRINT 'Backup Compression Check: Passed'
END
ELSE
BEGIN
PRINT 'Backup Compression Check: FAILED (Set This With SP_CONFIGURE)'
END
END
ELSE
BEGIN
PRINT 'Backup Compression Check: FAILED (This Version Doesn''t Support Compressed Backups)'
END
END
ELSE
BEGIN
PRINT 'Backup Compression Check: FAILED (This Version Doesn''t Support Compressed Backups)'
END
--TempDB on own disks
/* One item that is not checked is whether or not the
the folder that TempDB lives in is a mount point or
not. This could complicate and have a false positive
for this check while the drive is technically the same
the mount point is on different hardware. */
DECLARE @FailureCounts INT
SET @FailureCounts = -1
SELECT @FailureCounts = COUNT(*)
FROM (SELECT LEFT(physical_name, 1) AS [Drive_Letter] FROM sys.master_files WHERE database_id = 3 GROUP BY LEFT(physical_name, 1)) TempDBInfo
INNER JOIN sys.master_files mf
ON TempDBInfo.Drive_Letter = LEFT(mf.physical_name, 1)
WHERE mf.database_id != 3
IF(@FailureCounts != 0)
PRINT 'TempDB Seperate Drive Check: FAILED With ' + CAST(@FailureCounts AS VARCHAR(4)) + ' Conflicts.'
ELSE
PRINT 'TempDB Seperate Drive Check: Passed'
--TempDB 1/2 to 1/4 of cores
/* Depending on your setup, this may or may not be applicable.
Testing should be done to affirm where the sweet spot is. */
DECLARE @CoreCount INT
DECLARE @TempDBFileCount INT
/* differ just in case something happens, comparison will fail */
SET @CoreCount = -1
SET @TempDBFileCount = 0
/* one scheduler per CPU */
SELECT @CoreCount = scheduler_count FROM sys.dm_os_sys_info
SELECT @TempDBFileCount = COUNT(*) FROM sys.master_files WHERE database_id = 3 AND type_desc = 'ROWS'
IF( @CoreCount = @TempDBFileCount OR @CoreCount/2 = @TempDBFileCount OR @CoreCount/4 = @TempDBFileCount)
BEGIN
PRINT 'TempDB Datafile Ratio Check: Passed'
END
ELSE
BEGIN
PRINT 'TempDB Datafile Ration Check: FAILED ( ' + CAST(@CoreCount AS VARCHAR(3)) + ' CPUs | ' + CAST(@TempDbFileCount AS VARCHAR(2)) + ' TempDB Datafiles )'
END
/* Database Configuration Checks */
--Create the table variable used when checking database
-- specific items.
IF EXISTS(select 1 from tempdb.sys.objects where name like '#FailedDatabases%')
BEGIN
DROP TABLE #FailedDatabases
END
CREATE TABLE #FailedDatabases
(
Database_Name SYSNAME NOT NULL
);
DECLARE @CurrentDatabaseName SYSNAME
--Auto_Create Stats is disabled
INSERT INTO #FailedDatabases(Database_Name) SELECT [name] FROM sys.databases WHERE is_auto_create_stats_on = 1 AND state_desc = 'ONLINE' AND database_id > 4
IF((SELECT COUNT(*) FROM #FailedDatabases) > 0)
BEGIN
PRINT 'Auto Create Stats Check: FAILED'
WHILE ((SELECT COUNT(*) FROM #FailedDatabases) > 0)
BEGIN
SELECT TOP 1 @CurrentDatabaseName = [Database_Name] FROM #FailedDatabases ORDER BY [Database_Name] ASC
PRINT ' ' + @CurrentDatabaseName
DELETE FROM #FailedDatabases WHERE [Database_Name] = @CurrentDatabaseName
END
END
ELSE
BEGIN
PRINT 'Auto Create Stats Check: Passed'
END
--Auto_Close is disabled
----This is a stndard best practice and
----not specific to sharepoint.
DELETE FROM #FailedDatabases
SET @CurrentDatabaseName = ''
INSERT INTO #FailedDatabases(Database_Name) SELECT [name] FROM sys.databases WHERE is_auto_close_on = 1 AND state_desc = 'ONLINE' AND database_id > 4
IF((SELECT COUNT(*) FROM #FailedDatabases) > 0)
BEGIN
PRINT 'Auto Close Check: FAILED'
WHILE ((SELECT COUNT(*) FROM #FailedDatabases) > 0)
BEGIN
SELECT TOP 1 @CurrentDatabaseName = [Database_Name] FROM #FailedDatabases ORDER BY [Database_Name] ASC
PRINT ' ' + @CurrentDatabaseName
DELETE FROM #FailedDatabases WHERE [Database_Name] = @CurrentDatabaseName
END
END
ELSE
BEGIN
PRINT 'Auto Close Check: Passed'
END
--Auto_Shrink is disabled
----This is a standard best practice and not
----specific to Sharepoint.
DELETE FROM #FailedDatabases
SET @CurrentDatabaseName = ''
INSERT INTO #FailedDatabases(Database_Name) SELECT [name] FROM sys.databases WHERE is_auto_shrink_on = 1 AND state_desc = 'ONLINE' AND database_id > 4
IF((SELECT COUNT(*) FROM #FailedDatabases) > 0)
BEGIN
PRINT 'Auto Shrink Check: FAILED'
WHILE ((SELECT COUNT(*) FROM #FailedDatabases) > 0)
BEGIN
SELECT TOP 1 @CurrentDatabaseName = [Database_Name] FROM #FailedDatabases ORDER BY [Database_Name] ASC
PRINT ' ' + @CurrentDatabaseName
DELETE FROM #FailedDatabases WHERE [Database_Name] = @CurrentDatabaseName
END
END
ELSE
BEGIN
PRINT 'Auto Shrink Check: Passed'
END
--Data and Log seperated
----Again, this doesn't account for mountpoints.
SET @CurrentDatabaseName = NULL
;WITH BadLogCTE AS
(
SELECT DISTINCT LogDrives.Database_Name
FROM
(
SELECT DB_NAME(database_id) AS [Database_Name], LEFT(Physical_Name, 1) AS [Drive_Letter] FROM sys.master_files WHERE type_desc = 'LOG' AND database_id > 4
) AS LogDrives
INNER JOIN
sys.master_files mf
ON LogDrives.Drive_Letter = LEFT(mf.physical_name, 1) and LogDrives.Database_Name = mf.name
WHERE
mf.database_id > 4
AND mf.type_desc = 'ROWS'
)
INSERT INTO #FailedDatabases(Database_Name) SELECT Database_Name FROM BadLogCTE
IF ((SELECT COUNT(*) FROM #FailedDatabases) = 0)
BEGIN
PRINT 'Data And Logs Seperated Check: Passed'
END
ELSE
BEGIN
PRINT 'The Following Databases Do Not Have Data and Logs Seperated:'
WHILE ((SELECT COUNT(*) FROM #FailedDatabases) > 0)
BEGIN
SELECT TOP 1 @CurrentDatabaseName = Database_Name FROM #FailedDatabases
PRINT ' ' + @CurrentDatabaseName
DELETE FROM #FailedDatabases WHERE Database_Name = @CurrentDatabaseName
END
END
--Only one log file
----This check isn't part of the sharepoint guide
----but it is an important check, especially since
----sharepoint is a critical system in most
----environments and the recoverability of it
----is paramount.
DELETE FROM #FailedDatabases
INSERT INTO #FailedDatabases(Database_Name)
SELECT DISTINCT DB_NAME(Database_ID) AS [Database_Name]
FROM sys.master_files
WHERE database_id > 4 AND type_desc = 'LOG'
GROUP BY Database_id, LEFT(Physical_name, 1)
HAVING COUNT(*) > 1
IF ((SELECT COUNT(*) FROM #FailedDatabases) = 0)
BEGIN
PRINT 'Single Log File Check: Passed'
END
ELSE
BEGIN
PRINT 'The Following Databases Have More Than 1 Log File:'
WHILE ((SELECT COUNT(*) FROM #FailedDatabases) > 0)
BEGIN
SELECT TOP 1 @CurrentDatabaseName = Database_Name FROM #FailedDatabases
PRINT ' ' + @CurrentDatabaseName
DELETE FROM #FailedDatabases WHERE Database_Name = @CurrentDatabaseName
END
END
--Autogrowth Settings (fixed size, MB)
----Initially I questioned whether to follow the sharepoint
----guide or not. I chose to NOT follow the guide
----as their best practice has the datafile should
----grow as a percentage and NOT a fixed limit.
----Would someone really want a 50GB DB growing by 5-10GB
----based on percentage? I didn't think so, I also don't
----believe that someone would want a bunch of smaller
----growths when the database was smaller, causing
----fragmentation on the disk.
----Growth is in PAGES if is_percent_growth = 0
DELETE FROM #FailedDatabases
INSERT INTO #FailedDatabases(Database_Name)
SELECT DB_NAME(Database_ID) AS [Database_Name]
FROM sys.master_files
WHERE database_id > 4 AND type_desc = 'ROWS' AND (is_percent_growth = 1 OR growth < 113072) /* 1 GB growth fixed, disregard LOG files */
IF ((SELECT COUNT(*) FROM #FailedDatabases) = 0)
BEGIN
PRINT 'Datafile Growth Check: Passed'
END
ELSE
BEGIN
PRINT 'The Following Databases Need To Have Their Growth Values Checked:'
WHILE ((SELECT COUNT(*) FROM #FailedDatabases) > 0)
BEGIN
SELECT TOP 1 @CurrentDatabaseName = Database_Name FROM #FailedDatabases
PRINT ' ' + @CurrentDatabaseName
DELETE FROM #FailedDatabases WHERE Database_Name = @CurrentDatabaseName
END
END
--Max size check (50GB limit recommended)
---- 50GB = 6553600 Pages
---- This is only valid for CONTENT databases
---- Personally, this seems like an absurdly
---- low number.
DELETE FROM #FailedDatabases
INSERT INTO #FailedDatabases (Database_Name)
SELECT DB_NAME(database_ID) AS [Database_Name]
FROM sys.master_files
WHERE database_id > 4
AND type_desc = 'ROWS'
GROUP BY database_id
HAVING SUM(max_size) != 6553600
IF ((SELECT COUNT(*) FROM #FailedDatabases) = 0)
BEGIN
PRINT 'Database MAX_SIZE Check: Passed'
END
ELSE
BEGIN
PRINT 'The Following Databases Need To Have Their Max_Size Values Checked:'
WHILE ((SELECT COUNT(*) FROM #FailedDatabases) > 0)
BEGIN
SELECT TOP 1 @CurrentDatabaseName = Database_Name FROM #FailedDatabases
PRINT ' ' + @CurrentDatabaseName
DELETE FROM #FailedDatabases WHERE Database_Name = @CurrentDatabaseName
END
END
--Free Space in DB (25%)
DELETE FROM #FailedDatabases
EXEC sp_MSforeachdb 'USE [?];
INSERT INTO #FailedDatabases (Database_Name)
SELECT TOP 1 db_name(database_id) AS Database_Name
FROM sys.master_files
where database_id = DB_ID()
AND database_id > 4
AND (CAST(FILEPROPERTY([name], ''spaceused'') AS DECIMAL(12,2))/CAST(size AS DECIMAL(12,2))) > 0.75'
IF ((SELECT COUNT(*) FROM #FailedDatabases) = 0)
BEGIN
PRINT 'Database Files Free Space Check: Passed'
END
ELSE
BEGIN
PRINT 'The Following Databases Need To Have Their File Free Space Checked:'
WHILE ((SELECT COUNT(*) FROM #FailedDatabases) > 0)
BEGIN
SELECT TOP 1 @CurrentDatabaseName = Database_Name FROM #FailedDatabases
PRINT ' ' + @CurrentDatabaseName
DELETE FROM #FailedDatabases WHERE Database_Name = @CurrentDatabaseName
END
END
/* Availability Checks */
----Note that the only item the sharepoint guide
----specifies is that some sort of HA be setup.
----It doesn't go in to detail with topology,
----technology (or combinations), or what a good
----set of settings are for those. Thus I am just
----checking to see if any type of HA is setup.
DECLARE @IsClustered INT
DECLARE @IsMirrored INT
DECLARE @IsAG INT
SELECT @IsClustered = -1, @IsMirrored = -1, @IsAG = -1
--Clustering
SELECT @IsClustered = COUNT(*) FROM sys.dm_os_cluster_nodes
--Mirroring
select @IsMirrored = COUNT(*) FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL
--Availability Groups
IF(@Version > 10)
BEGIN
SELECT @IsAG = COUNT(*) FROM sys.availability_groups
END
ELSE
BEGIN
SET @IsAG = -1
END
IF( @IsAG > 0 OR @IsClustered > 0 OR @IsMirrored = (SELECT COUNT(*) FROM sys.databases WHERE database_id > 4 AND state_desc = 'ONLINE'))
BEGIN
PRINT 'High Availability Check: Passed'
END
ELSE
BEGIN
PRINT 'High Availability Check: Failed'
IF(@IsAG < 1)
PRINT ' No Availabitlity Groups Found'
IF(@IsClustered < 1)
PRINT ' No Clustered Instances Found'
IF(@IsMirrored = 0)
PRINT ' No Mirrored Databases Found'
IF(@IsMirrored > 0 AND @IsMirrored < (SELECT COUNT(*) FROM sys.databases WHERE database_id > 4 AND state_desc = 'ONLINE'))
PRINT ' Only ' + CAST(@IsMirrored AS VARCHAR(2)) + ' Mirrored Databases Found, Not All User Databases Are Mirrored'
END
Finish:
PRINT 'Checks Complete!'