STOP ! Read this SQL Server Pre Deployment Checklist Before….

SQLChecklistThere are many things to check BEFORE implementing an Enterprise or even a medium scale SQL Server based infrastructure.  Below are some key points to consider before deploying SQL Server in a production environment to help prevent a whole lot of pain once a system has been deployed and  is in production.  This post relates to general pre-installation checks rather than the logical and physical architectural decision making process for the implementation Microsoft SQL Server 2005 through to SQL Server 2012 SP1+.

image  Hopefully this article will help some people to avoid learning the hard way !

There are many reasons for following a checklist and ensuring the environment is correct. Examples are recording a snapshot of the current configuration and benchmarking performance. The most important reason to use a checklist and get things right the first time is that it is much simpler to get things right pre-build rather than once the system has gone into production and WHEN problems arise.  Note the missing IF statement here.

 

Pre-Installation Checks

Server Level

 
Firmware Ensure BIOS, RAID controller and HBA firmware is up to date. 
Remove any unnecessary software

If the server is not a fresh build, remove any unnecessary software such as old and unused SQL Server versions or components, any agents that may no longer be required or need updating,

   

Operating System and Server Environment

Ensure environmental aspects are considered such as AD, DNS, server configuration and health etc.
Windows Server Edition

Ensure you have the correct Edition for current and future requirements. E.g. note memory limitations in Windows Standard Edition (32GB). If you have a license to use Enterprise then that maybe a better option to start with.  Only relevant to Windows 2008 and 2008 R2.  Note in Windows 2012 there are NO differences between Standard and Datacenter, only virtualization rights  There is no Windows Server 2012 Enterprise Edition.

Windows patch level

Ensure the server is up to date on patching and that an update strategy is defined and implemented.

Hardware Drivers

Ensure all drivers are up to date especially storage, network and HBA drivers.

Windows OS Regional Settings

Ensure the Region settings of the OS are correct for the country of use. I have seen many installations in the the UK for example, set with the default region as US.  Once SQL is installed with defaults, the collation will be incorrect for the region it is being used in, resulting in incorrect date/time configuration etc..

Consider using DNS A records for the server name

Consider the use of a DNS A record for application access to the SQL Server.  This way if a database, set of databases or a whole instance needs to be moved to a new server it can be done with ease by changing the DNS record in AD without making changes to applications and reports for example.  A good example of this is Reporting Services or SharePoint.   Yes you can use SQL Aliasing too which may require a bit more configuration. e.g. for SharePoint you would have to create an alias on each SharePoint Server. With a DNS alias you change the Alias in one place (DNS!).

Check Group Policies

Check and audit which group policies are being applied to the server to ensure there are no unexpected or unwanted GPOs being applied and vice versa.

Check Event logs for pre existing errors or warnings Ensure event logs are clean. Take special note of the system logs which can highlight potential system and hardware errors or time bombs.
Network Configuration and bandwidth

Is your networking optimally configured ? This includes iSCSI or Fibre Channel links to any SAN that might be in the mix.  Test and benchmark throughput.  Use tools such as CrystalDiskMark or SQLIO. Are you getting expected throughput ? i.e are you hitting 100MB/s max sequential write throughput if you have 2 x 4GB HBA cards ?  Ensure drivers and multi-pathing configuration and software is installed and correct. Note some SANs may not allow Active/Active configuration or may have this as an additional cost option.   If all ok move onto disk storage configuration.

If SQL Service account Permissions. Grant SQL Server permissions to “Perform Volume Maintenance Tasks” to allow for instant file initialisation.  More info here http://msdn.microsoft.com/en-us/library/ms175935(v=sql.105).aspx

Also grant the SQL Server the “Lock Pages In Memory” right – only if required.  More on these options will be covered in later blogs.  There are many blogs around lock pages in memory and whether it should be used or not.
See https://www.simple-talk.com/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/
by Jonathan Kehayias
and
http://blogs.technet.com/b/askperf/archive/2008/03/25/lock-pages-in-memory-do-you-really-need-it.aspx
from the Windows Server Performance Team.  A lot depends on the environment, it is not generally recommended in virtual environments when there is a risk of a host requiring memory to start up VMs in an outage scenario for example.

SQL Service Account Permissions The SQL Server service account does not require any special permissions.   Any permissions required are granted during installation or when changing the account using SQL config manager.  This is the main reason why you should use this tool for changing accounts and NOT the Windows Admin Services console.
   

Storage

Optimal storage configuration is essential for SQL Server to perform well, especially on larger databases.

Disk Configuration

Ensure your disks are formatted correctly using 64K blocks.  The default of 4k will provide poor performance as SQL stores data pages in 8k chunks which fit perfectly into 64k cluster size (or file allocation unit size),  Each 8k block is split if using 4k blocks.  This can and does cause a lot of fragmentation in the database. This simple configuration can help improve IO performance by up to 30%.  If formatting is incorrect, all data must be moved and disks reformatted once in production.  Disk alignment is no longer a requirement unless you are still using Windows 2003.  If you are using Windows 2003, an upgrade is WELL overdue as mainstream support has ended. Also, Windows Server 2003 will not be capable of taking advantage of modern hardware (i.e. major processor advancements since 2003).  Further info here.

RAID Configuration

Are your RAID arrays optimised for SQL Server and expected usage ? There are many articles explaining the benefits of why RAID groups are important. More info can be found here ….Raid Levels and SQL Server.
Of course modern SANs coupled with tiered storage make RAID pretty much redundant now by using storage pools.  This is essentially using a large pool of disks to create LUNS (Logical Unit Numbers) as logical disks. Correct setup and configuration can provide better performance and use less disk space by removing storage silos compared to traditional RAID.  See here for a great blog by Paul Galjan. Disk and File Layout for SQL Server.

Disk Sizing

Are your disks sized appropriately  to accommodate at least medium term growth expectations ? Do you have capacity to add more storage if required easily ?

SAN Configuration and load

Ensure SAN is not already saturated or at capacity, ensure it can deliver reasonable IOPS for your application to perform as required.  Refer to Microsoft Certified Master and MVP Brent Ozar who provides tonnes of excellent SQL Server information in his blogs.

SAN Software and vendor recommendations Ensure you look at the vendors recommendations for SQL Server as they may differ from standard best practices for a particular SAN. 
SAN – HBA Drivers and software Ensure the correct and latest drivers for any HBA cards are installed and configured correctly.  E.g. Ensure Multi-pathing is set up and working correctly.  Some vendors do not allow Active / Active (for max bandwidth) configuration by default, check with vendor.
   

SQL Server Specific

 
SQL Server Version and Edition

Do you have the correctly licensed SQL Server Edition ?  Per core, VM, Standard, BI, Enterprise Edition ?  Ensure latest service pack is applied and license key is correct for use case.

SQL Server Service Accounts

Ensure the service accounts are correctly planned and created in Active Directory prior to the installation. e.g. separate accounts for Reporting Services and Analysis Services for example.  Ensure you have the account details.

SQL Cluster Considerations

If using a Windows Cluster ensure that the storage and resource groups are assigned correctly.  Ensure the quorum method and mode is designed correctly, e.g. disk or file share, node majority options. See the following for Configure and Manage the Quorum in Windows 2012 Clustering
And
Understanding Quorum Configurations in a Failover Cluster
Consider Cluster Aware updating for patching clusters, especially clusters with many nodes. This feature is only available in Windows Server 2012.

Most importantly ensure you thoroughly test and document the failover process.  You do not want to be looking for documentation or ringing support (Panic…Microsoft or Storage Vendor !!?) in the event of a cluster failure during a production outage.  The reason a SQL cluster even exists is to minimise downtime of critical systems.  You would not be popular if you could not show evidence of testing the failover and failback.

Thank you for reading, I will try and follow this up with a post on what to check AFTER the SQL server installation is complete.

Also check out my post on how to complete a scripted install of SQL Server on Windows Server 2012 Core, which will of course also work with the full GUI shell of Windows Server 2012 .   

MIcrosoft_SQL_Badges_SQLGeekMIcrosoft_SQL_Badges_ServerFan2012

Check out the newly released SQL Server community badges above, they are just for fun !

I hope this article is useful to at least a handful of people and spares someone from possible future pain.

Did you find this useful ? Do you have further good checklist tips ? Post a reply to share…

Advertisements

About Mitesh Chauhan
Mitesh Chauhan, Azure Cloud Solutions Architect. This is my blog where I share articles and thoughts on IT Infrastructure and architecture. The topics I am most passionate about are Implementation and architecture of rock solid Cloud Infrastructure based around SQL Server and Windows Server mainly using Microsoft Azure. MCTS - Azure Architecture MCTS - Azure Implementation MCSE Server Infrastructure (Windows Server 2012) , MCITP SQL Server 2008, Togaf Certified, Prince 2 Practitioner.

One Response to STOP ! Read this SQL Server Pre Deployment Checklist Before….

  1. Pingback: STOP ! Read this SQL Server Pre Deployment Checklist Before… - Information Management Group Ltd.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: