info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
|
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance
|
| | | |
|
Running SQL Server Post-Installation Checks
by BF (Principal Consultant; Architecture; Engineering)
2018-04-29
Running SQL Server Post-Installation Checks:
Print @@servername
Use Master
GO
Set NoCount On
Print ''
GO
PRINT 'Running Post-Installation Checks...' + CHAR(10)
GO
PRINT 'INFO :: Instance Name is ' + @@servername + '...'
GO
--Check Latest SP installed
Declare @SP nvarchar(10)
Set @SP = convert(varchar(10),(SERVERPROPERTY('ProductLevel')))
Print 'INFO :: ' + ISNULL(@SP, 'No SP') + ' is installed....'
GO
--Check Latest CU installed
Declare @CU nvarchar(10)
Set @CU = convert(varchar(10),(SERVERPROPERTY('ProductUpdateLevel')))
Print 'INFO :: ' + ISNULL(@CU, 'No CU') + ' Update is installed....'
GO
--Check for TCP Port, Should be 1433
DECLARE @portNo VARCHAR(10),@rootkey VARCHAR(MAX)
EXEC @rootkey = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',N'TcpPort', @portNo output, 'no_output'
PRINT 'INFO :: SQL server is running on TCP/IP port ' + convert(varchar(10),@portNo) + '...'
GO
--Check Server Collation
Declare @collation as varchar(100)
SELECT @collation = CONVERT(varchar(100), DATABASEPROPERTYEX('master', 'Collation'))
PRINT 'INFO :: SQL server collation is ' + @collation + '...'
GO
-- Check Authentication Mode
DECLARE @AuthenticationMode INT
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'LoginMode', @AuthenticationMode OUTPUT
IF(select @AuthenticationMode ) = 1
PRINT 'ALERT :: SQL is configured for Windows Authentication ONLY....'
ELSE
PRINT 'INFO :: SQL is configured for Mixed-Mode Authentication....'
GO
--Check Default User Data, Log ,Backup Directory
DECLARE @regk INT, @dir nvarchar(4000),@dir1 nvarchar(4000) ,@dir2 nvarchar(4000)
EXEC @regk = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @dir output, 'no_output'
EXEC @regk = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog', @dir1 output, 'no_output'
EXEC @regk = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @dir2 output, 'no_output'
IF (@dir is null)
BEGIN
EXEC @regk = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @dir output, 'no_output'
SELECT @dir = @dir + N'\Data'
END
IF (@dir1 is null)
BEGIN
EXEC @regk = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @dir1 output, 'no_output'
SELECT @dir1 = @dir1 + N'\Data'
END
IF (@dir2 is null)
BEGIN
EXEC @regk = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @dir2 output, 'no_output'
SELECT @dir2 = @dir2 + N'\Data'
END
PRINT 'INFO :: Default Database Location for Data is ' + @dir + '....'
PRINT 'INFO :: Default Database Location for Log is ' + @dir1 + '....'
PRINT 'INFO :: Default Database Location for Backup is ' + @dir2 + '....'
--Check for Configured Min Memory, Should not be 0
DECLARE @VALUE INT
set @value = (SELECT VALUE FROM SYS.sysconfigures where config = 1543)
IF ( (SELECT VALUE FROM SYS.sysconfigures where config = 1543) = 0 )
PRINT 'ALERT :: Min Server Memory (MB) not configured....'
ELSE
PRINT 'INFO :: Min Server Memory (MB) is configured to ' + convert(varchar(10),@value) + ' MB....'
go
--Check for Configured Max Memory
DECLARE @VALUE INT
set @value = (SELECT VALUE FROM SYS.sysconfigures where config = 1544)
IF ( (SELECT len(VALUE) FROM SYS.sysconfigures where config = 1544) >6 )
PRINT 'ALERT :: Max Server Memory (MB) not configured....'
ELSE
PRINT 'INFO :: Max Server Memory (MB) is configured to ' + convert(varchar(10),@value) + ' MB....'
go
--Check for Network Packet Size
DECLARE @NPSIZE smallint
Set @NPSIZE = (SELECT VALUE FROM SYS.sysconfigures where config = 505)
PRINT 'INFO :: Network packet size is configured to ' + convert(varchar(10),@NPSIZE) + '....'
go
--Check for Maximum Degree of Parallelism
DECLARE @MDP smallint
set @MDP = (SELECT VALUE FROM SYS.sysconfigures where config = 1539)
IF (SELECT VALUE FROM SYS.sysconfigures where config = 1539 ) = 0
PRINT 'ALERT :: Maximum Degree of Parallelism not configured....'
ELSE
PRINT 'INFO :: Maximum Degree of Parallelism is configured to ' + convert(varchar(10),@MDP) + '....'
go
--Check for Cost Threshold for Parallelism
DECLARE @CTFP smallint
set @CTFP = (SELECT VALUE FROM SYS.sysconfigures where config = 1538)
IF (SELECT VALUE FROM SYS.sysconfigures where config = 1538 ) != 30
PRINT 'ALERT :: Cost Threshold for Parallelism not configured....'
ELSE
PRINT 'INFO :: Cost Threshold for Parallelism is configured to ' + convert(varchar(10),@CTFP) + '....'
go
--Check for Default Backup Compression
DECLARE @DBC smallint
set @DBC = (SELECT VALUE FROM SYS.sysconfigures where config = 1579)
IF (SELECT VALUE FROM SYS.sysconfigures where config = 1579 ) != 1
PRINT 'ALERT :: Default Backup Compression not configured....'
ELSE
PRINT 'INFO :: Default Backup Compression is configured to ' + convert(varchar(10),@DBC) + '....'
go
--Check for Optimize for Ad hoc Workloads
DECLARE @OAHC smallint
set @OAHC = (SELECT VALUE FROM SYS.sysconfigures where config = 1581)
IF (SELECT VALUE FROM SYS.sysconfigures where config = 1581) !=1
PRINT 'ALERT :: Optimize for ad hoc workloads not configured....'
ELSE
PRINT 'INFO :: Optimize for ad hoc workloads is configured to ' + convert(varchar(10),@OAHC) + '....'
go
--Check for Command Shell
DECLARE @CmdShell smallint
Set @CmdShell = (SELECT VALUE FROM SYS.sysconfigures where config = 16390)
PRINT 'INFO :: xp_cmdshell is turned off....'
go
--Check for SQL Error Log file numbers
Use Master
go
DECLARE @SSLogsTotal TABLE (ArchiveNo INT, LogDate DATETIME, LogFileSize INT)
INSERT INTO @SSLogsTotal Exec xp_enumerrorlogs
DECLARE @Cnt int
Set @Cnt = (Select count(*) from @SSLogsTotal)
PRINT 'INFO :: Error Log Files is configured to ' + convert(varchar(10),@Cnt) + '....'
IF OBJECT_ID('tempdb.dbo.#SSLogsLoad', 'U') IS NOT NULL DROP TABLE #SSLogsLoad;
--USE [master]
--GO
--declare @regreader int, @NumErrorLogs int
--EXEC @regreader = xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'NumErrorLogs',@NumErrorLogs output,'no_output'
--IF(select @NumErrorLogs ) != 15
--PRINT 'ALERT :: Error Log Files not configured....'
--ELSE
--PRINT 'INFO :: Error Log Files is configured to ' + convert(varchar(10),@NumErrorLogs) + '....'
--GO
--Check for TempDB files created
declare @cpu_count smallint , @tempfile smallint
set @cpu_count =( SELECT cpu_count FROM sys.dm_os_sys_info )
set @tempfile = (SELECT count(fileid) as TempDBFiles from tempdb.sys.sysfiles where groupid =1)
IF (@cpu_count >= 8 AND @tempfile = 8)
PRINT 'INFO :: 8 TempDB files are configured as per available ' + convert(varchar(10),@cpu_count) +' Core CPUs'
ELSE
IF (@cpu_count >= 8 AND @tempfile < 8)
PRINT 'ALERT :: 8 TempDB files needs to be configured as number of CPU is above or equal to 8'
IF (@cpu_count <= 8 AND @tempfile = @cpu_count)
PRINT 'INFO :: ' + convert(varchar(10),@tempfile) +' TempDB files are configured as per available ' + convert(varchar(10),@cpu_count) +' Core CPUs'
ELSE
IF (@cpu_count <= 8 AND @tempfile != @cpu_count )
PRINT 'ALERT :: ' + convert(varchar(10),@cpu_count) + ' TempDB files needs to be configured as number of CPU is below 8'
--Named Pipe should be Disabled
IF (select serverproperty('isclustered') )= 0
BEGIN
IF (SELECT value_data FROM sys.dm_server_registry
where registry_key like '%\MSSQLServer\SuperSocketNetLib\Np'
and value_name = 'Enabled' ) = 1
PRINT 'ALERT :: Named Pipe Protocol is still enabled.Disable it....'
ELSE
PRINT 'INFO :: Named Pipe Protocol is Disabled....'
--Share Memory Should be Disabled
IF (SELECT value_data FROM sys.dm_server_registry
where registry_key like '%\MSSQLServer\SuperSocketNetLib\Sm'
and value_name = 'Enabled' ) = 1
PRINT 'INFO :: Shared Memory Protocol is enabled....'
END
--VIA protocol to be Disabled
IF (SELECT value_data FROM sys.dm_server_registry
where registry_key like '%\MSSQLServer\SuperSocketNetLib\Via'
and value_name = 'Enabled' ) = 1
PRINT 'ALERT :: Via Protocol is still enabled.Disable it....'
ELSE
PRINT 'INFO :: Via Protocol is Disabled....'
go
--Check for all IP to be configured with same TCP port number
DECLARE @TcpIP1 sql_variant, @TcpIP2 sql_variant,@TcpIP3 sql_variant,@TcpIP4 sql_variant,@TcpIPAll sql_variant
set @TcpIP1 = (SELECT value_data FROM sys.dm_server_registry where registry_key like '%\MSSQLServer\SuperSocketNetLib\Tcp\IP1' and value_name = 'TcpPort')
set @TcpIP2 = (SELECT value_data FROM sys.dm_server_registry where registry_key like '%\MSSQLServer\SuperSocketNetLib\Tcp\IP2' and value_name = 'TcpPort')
set @TcpIP3 = (SELECT value_data FROM sys.dm_server_registry where registry_key like '%\MSSQLServer\SuperSocketNetLib\Tcp\IP3' and value_name = 'TcpPort')
set @TcpIP4 = (SELECT value_data FROM sys.dm_server_registry where registry_key like '%\MSSQLServer\SuperSocketNetLib\Tcp\IP4' and value_name = 'TcpPort')
set @TcpIPAll = (SELECT value_data FROM sys.dm_server_registry where registry_key like '%\MSSQLServer\SuperSocketNetLib\Tcp\IPAll%' and value_name = 'TcpPort')
IF (@TcpIP1 = @TcpIP2 AND @TcpIP3 = @TcpIP4 AND @TcpIP4 = @TcpIPAll)
PRINT 'INFO :: IP1,IP2,IP3,IP4 and IPALL are configured with value ' + convert(nvarchar(10),@TcpIPAll)
ELSE
PRINT 'ALERT :: IP1,IP2,IP3,IP4 and IPALL are not configured completely and there is mismatch....'
--Check for Instant File Initialization Enabled
Declare @IFI char(1)
SELECT @IFI=instant_file_initialization_enabled
FROM sys.dm_server_services
WHERE servicename LIKE 'SQL Server (%'
IF @IFI = 'Y'
Print 'INFO :: Instant File Initialization is enabled'
Else
Print 'INFO :: Instant File Initialization is not enabled'
--Check for SQL Server Version
Declare @sqlver varchar(100)
SELECT @sqlver=LEFT(@@VERSION, CHARINDEX('(', @@VERSION) - 2) + ' - '
+ CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(300))
FROM sys.dm_server_services
WHERE servicename LIKE 'SQL Server (%'
Print 'INFO :: SQL Server Version is ' + @sqlver
--Check for O.S. Version
Declare @os varchar(100)
SELECT @os=RIGHT(@@version, LEN(@@version) - 3 - CHARINDEX(' ON ', @@VERSION))
FROM sys.dm_server_services
WHERE servicename LIKE 'SQL Server (%'
Print 'INFO :: O.S. is ' + @os
Other:
1. In Azure, placing TEMPDB on the D:\ (Temporary Disk) can provide significant read and write(especially) performance. IOPS test shows that D:\ will outperform SSD Disks.
Note: D drive is provisioned on the local host / hypervisor. When the VM moves hosts, the previous D:\ is lost. This is typically acceptable since each time the SQL Server service is re-started or the box is rebooted, the TEMPDB is re-created and the # and size of the TEMPDB Data & Log files is the same as it was before the re-start.
2. Enable Instant File Initialization:
"You may want to consider enabling Instant File Initialization (via SE_MANAGE_VOLUME_NAME a.k.a. "Perform volume maintenance tasks") for your SQL Server startup/service account. This option allows for much faster data file allocations (CREATE AND ALTER FILE) but DOES NOT WORK FOR LOG FILE ALLOCATIONS. This is enabled for each instance via the "Perform volume maintenance tasks" local security policy. In a cluster you have to grant the right on all nodes. If there are multiple instances on a server or cluster, you should grant this right to each instance’s security group.
This permission keeps SQL Server from "zeroing out" new space when you create or expand a data file (it is not applied to log files). This helps performance for CREATE DATABASE, ALTER DATABASE, RESTORE, and AUTOGROW. It can have a significant positive impact on how long it takes to create or expand a data file, but there is a small security risk in doing so. That is because a file "delete" really just deallocates the space and a new allocation can reuse that space which may still have data in it. When you do not zero out the existing space there is a possibility that someone could read data that you thought had been deleted. It is very common to turn Instant File Initialization on. Many shops consider the increased performance benefit to far outweigh the small security risk, but you must weigh the cost and benefits within your own environment."
Source: https://blogs.msdn.microsoft.com/sql_pfe_blog/2009/12/22/how-and-why-to-enable-instant-file-initialization/
3. Disk Allocation Unit Size
http://techdevops.com/Article.aspx?CID=245
4. If working with Azure, using the Web Platform Installer, install Azure Powershell.
5. If working with Azure, download and install Microsoft Azure Storage Tools (ex. AzCopy tool)
6. Enable Auditing
http://www.techdevops.com/Article.aspx?CID=252
|
|
|
|
|
|
|
|
|
|