PQO Operations SQL Server Standards by nlvIfZ

VIEWS: 0 PAGES: 4

									                                             SQL Server Production Build-out Checklist




                   [Enter Team Name]


                   SQL SERVER PRODUCTION BUILD-OUT QA CHECKLIST




Revision            Summary of Changes                  Contributor
December 1, 2011    Created                             Robert L Davis




                   [Enter Confidentiality Statement]-
                                                           SQL Server Production Build-out Checklist



1. Introduction
      The purpose of this document is to provide a checklist of items to be checked as part of a
QA check of a new SQL Server build-out for production.


2. Checklist
2.1 Storage Configuration
       No SQL components installed on the C drive
       SQL Server binaries and system data/log files on D drive
       E drive available for backups
       Default location of data files is a path on the H drive
       Default location of log files is a path on the O drive
       TempDB data and log files located on the T drive
              1 data file per logical CPU
              All data files the exact same size
              All data files have growth disabled
              1 log file (total) twice the size of a single data file
              Log file has growth set to a hard number, not a percentage (ex. 512 MB)
              All data and log files consume at least 90% of the T drive
              Formula for file size:
                  (Size of T * .90)/(# of data files + 2) rounded down to the nearest GB
                  Ex: 200 TB T drive with 16 CPUs = 200*.90/(16+2) = 10 = 10 GB
                           16 data files 10 GB each, 1 log file 20 GB
       No user databases, backups, or other files located on the T drive
       If this server is known to be going to participate in replication as a distributor, a
        dedicated drive is provided for its database files (not C, D, E, H, O, or T)
       If this server is known to be going to hold full-text catalogs (SQL 2005 only) , a dedicated
        drive is provided for its database files (not C, D, E, H, O, or T)

2.2 Software Configuration
       Only RTM software will be installed on production SQL Servers unless prior approval
        granted
       Only datacenter supported version of the OS
       IIS and SQL Server Reporting Services will not be installed on the same server as the SQL
        Server Database Engine or SQL Server Analysis Services
       No development tools will be allowed on production servers (BIDS, Visual Studio,
        Vulcan, etc.)

2.3 SQL Server Installation
       No non-essential SQL components installed


                                [Enter Confidentiality Statement]-
                                                      SQL Server Production Build-out Checklist


            No SSIS
            No SSAS
            Full-text okay to be installed, but should be disabled if not in use
            No Notification Services (SQL 2005 only)
            No SSRS (cannot be on the same server as the database engine for any reason)
            No BIDS (cannot be on a production server for any reason)
     SQL Server installations must be a single instance on a server unless prior approval
      provided
            Acceptable exception is for a multi-instance cluster (sometimes called
              Active/Active or N+1)
     Domain accounts must be used for the main SQL Server services, if present
            SQL Server service
            SQL Server Agent service
            Full-text engine/filter Daemon
            SQL Server Analysis Services
            SQL Server Integration Services
            SQL Server Reporting Services
     Maximum and minimum memory set to an appropriate value for the SQL instance
     All SQL Servers will be listening on TCP/IP, named pipes, and shared memory protocols

2.4 Security
     Power Management Plan set to High Performance
     SQL should use Windows authentication only
     Only failed logins should be captured
     The local Administrators group does not contain any individual user accounts or
      application/service accounts
     The SQL Server service accounts are not members of the local administrators group
     The SQL Server Agent service account must be a member of the sysadmin group in SQL
      Server
     No individual users, machine accounts, or external service accounts added to the
      sysadmin group in SQL Server
     Builtin\Administrators group must be removed
     The sa account must be disabled
     The service accounts for each SQL Server component should be a member of the
      appropriate local group for each component.
           SQL Server service: SQLServerMSSQLUser$<Server Name>$<Instance>
           SQL Server Agent service: SQLServerSQLAgentUser$<Server Name>$<Instance>
           Full-text engine/filter Daemon: SQLServerFDHost$<Server Name>$<Instance>
           SQL Server Analysis Services: SQLServerMSASUser$<Server Name>$<Instance>
           SQL Server Integration Services: SQLServerDTSUser$<Server Name>$<Instance>


                            [Enter Confidentiality Statement]-
                                                     SQL Server Production Build-out Checklist


   The following permissions should be granted in local security policies to the
    SQLServerMSSQLUser$... local group if the SQL Server service account is a member of it.
         Lock Pages in Memory
         Perform Volume Maintenance Tasks
   Verify Log on as a Service permission already granted to the SQLServerMSSQLUser$...
    local group or the SQL Server service account
         If not already granted, the installation did not complete correctly and further
             permissions will need to be granted.
                 Act as a part of the operating system
                 Adjust memory quotas for a process
                 Lock pages in memory
                 Log on as a batch job
                 Log on as a service
                 Create a token object




                           [Enter Confidentiality Statement]-

								
To top