Total Pageviews

Wednesday, May 14, 2014

SQL Server installation and initial configuration – points to consider

The journey starts with the SQL Server installation. Installing a SQL Server is key for a stabilized and healthy database system. A properly installed and configured SQL Server rarely shows any issues. So let’s get started.

Service Account recommendation:
Although a single DOMAIN\ACCOUNT can be used for the SQL Server and the SQL Agent service, it is not desirable and not recommended as both services have different execution context. Use separate domain accounts for each service.

Installation recommendation:
If a DOMAIN\ACCOUNT is used for the SQL Server service account, then
(a)   Add the DOMAIN\ACCOUNT to the local administrator group.
(b)   Log on to the intended server with this account (optional).
(c)    Run the SQL Server setup executable as “run as administrator” privileges.
(d)   Installation media can be resided in a network share or in a DVD.

Once installation is done, you need to remove the SQL Server account from the local admin group; this is recommended for security reasons. However, please note that you may still prefer the SQL Server Service account to be a part of the local admin group to avoid permission related issues.

Local policy rights to SQL Server Service Account:
SQL Server service account rights varies from edition to edition. Do not assign any local policy rights to SQL Server and SQL Agent service account in advance which are not recommended. Each edition of the SQL Server installation process will determine the required local policy rights and will be added automatically during the installation of SQL Server. To validate appropriate service rights, review the MSDN recommendations from the following link:

Windows consideration:
(a)   Network Binding order:
1.      Make sure that the domain network is the first bound network. The miss-ordering issue usually reports in SQL Server fail-over cluster installation.
2.      Usually, TCP/IP will be the most used protocol. Consider changing the order of “network protocol bindings” to make network communications faster.

(b)   Disk alignment and block size:
1.      NTFS Allocation Unit (formerly known as cluster size) must be 64K. When formatting the partition that will be used for SQL Server data files, it is recommended that you use a 64-KB allocation unit size for data, logs, and tempdb.
2.      Ensure that Volume Alignment (also known as disk, partition or sector alignment) has been set correctly. Review and align the sector to 1024KB (or 2048KB max) before formatting the disk.
3.      HBA Disk Queue Depth: In the SAN, a queue depth of 64 is generally accepted as a good starting point in the absence of any specific vendor recommendations.

(c)    Windows Power Policy option: In Windows 2008, there are three power consumption options (power plan), where “Balanced” is set to default. Make sure that the “High Performance” has been selected. To make “power plan” configuration effective, the server BIOS power management needs to be changed to “OS Control” mode.
(d)   TCP Chimney setting: Disable the TCP Chimney offload in Windows level. “TCP Chimney Offloads” functionality does not work with virtualized Windows OS (VMware and hyper-v), therefore it can be disabled permanently.
(e)   SQL Server Port: Create an appropriate inbound rule in Windows firewall for SQL Server ports (e.g. 1433, 5022) instead of disabling the firewall service.

Instant file initialization: To take advantage of the instant file initialization feature, assign the “Perform volume maintenance tasks” windows right to the SQL Server Service Account. When it is enabled, the data file (mdf and ndf, but not ldf) will be initialized instantaneously without filling any empty spaces with zeros.

Tempdb consideration: It is not necessary to create multiple tempdb data files without understanding the workload nature and concurrency in a Server. If the OLTP server experiences high concurrencies, latch contention, row versioning, a high number of sort/hash operations, then adding multiple data files will be helpful.

(a)   In a SMP based system, 8 tempdb data files can be added initially. If more data files are required, then add two tempdb data files at a time.
(b)   In a NUMA based system, add data files based on the number of NUMA nodes detected by the SQL Server Engine. For a 4-NUMA system, the tempdb might have 8 or 12 data files. If more data files are needed, then increase the tempdb data files by the number of NUMA nodes.
(c)    Make sure that all tempdb data files are equal in size.
(d)   Tempdb data files and log must be isolated and resided in a single dedicated drive. Log files of tempdb can be placed in the Log drive, but it is not necessary.

MAXDOP: The default value of MAXDOP is 0 (zero). It works best for a well written OLTP application and if the database is maintained properly. However, in some workloads, MAXDOP = 1 works best, such as SharePoint database. There is no appropriate settings for MAXDOP and it varies from server to server, and sometimes even in the same server time to time (review Following are some considerations:

(a)   In a SharePoint implementation, it is highly recommended by Microsoft to use MAXDOP=1.
(b)   If parallelism introduces blocking on CXPACKET, lowering the MAXDOP setting may help.
(c)    In a system, if there is an enormous amount of spinlock and latch contention occurring, lowering the MAXDOP value will help to improve the performance.
(d)   Consider increasing “cost threshold for parallelism” to fine tune the parallelism along with or instead of MAXDOP.
(e)   In case of high CAXPACKET, consider redesigning indexes, removing fragmentations and updating statistics instead of changing MAXDOP.
(f)     Please remember that creating or rebuilding an index will suffer from lower values of MAXDOP.

Transaction Log:  Following are the recommendations for the Transaction Log:
(a)   Place the transaction log in a dedicated drive.
(b)   Do not create multiple transaction logs for a single database.
(c)    Pre-size the log file well in advance; use 4G or 8GB increments per growth.

Database Integrity: Make sure that the “Page Verify” option for all databases is set to “CHECKSUM”. Running DBCC CHECKDB in production server is resource intensive and it does not help to protect the database from corruption. Instead of running DBCC CHECKDB, consider configuring SQL Server “Event Alert Service” for database fatal error (Severity from 022 to 025) and get notifications right away.

No comments:

Post a Comment