SQL Server Installtion Baseline on Physical / VM

1. If it’s a physical server, reboot into the BIOS and set power management to “High Performance.”

2. Update device drivers to the latest manufacturer-supplied driver for networking and storage HBAs

3. Configure the storage

a. Open Server Manager, File Services, and Disks.

b. Online the disks, using NTFS for all of them.

c. Format the “SQL Programs” disk to 4 K. This is where you will install the SQL binaries, programs, error logging, etc.

d. Format every other disk to 64 K:

i. All SQL data disks, SQL log disks, and SQL backup disks.

ii. Be sure to label each one appropriately and consistently when possible.

iii. I hope the reminder isn’t necessary, but all drives should be RAID protected:

1. Not RAID0.

2. Even fancy SSDs need to be RAID1/5/6/10.

3. Including the even fancier PCI-Express based drives (yes you need to buy two of them).

4. Ideally, all data and log disks should be on RAID10.

e. Talk to your storage team and confirm these items are configured properly where applicable:

i. Global disk spares

ii. Failover paths

iii. MPIO

f. If you are deploying this to Microsoft Azure IaaS, make these adjustments to storage:

i. Start with an A3 Standard VM or higher—nothing with less than four cores.

ii. Add as many disks as your VM size will allow:

1. A3 can have 8 data disks (1 TB each).

2. A4 can have 16 data disks.

3. A6 can have 8 data disks.

4. A7 can have 16 data disks.

5. A8 can have 16 data disks.

iii. Split TempDB files across two disks.

iv. Store databases on separate disks distributing input/output (IO) where possible.

v. Use every disk as reasonably possible!

4. Review error logs for the server to identify any current issues.

5. Validate that successful server backup operations are being configured in alignment with backup and retention policies.

6. Benchmark the storage:

a. Download and install SQLIO.

b. Follow the guide here.

c. Run through the benchmarks and review with your storage team.

d. If you don’t have a storage team and don’t like the results, try a different storage design.

7. Obtain domain service accounts for SQL services:

a. DB Engine

b. SQL Agent

c. SSIS, SSAS, SSRS—where applicable

8. Create domain group for your SQL Admins (people).

a. Add domain group to Remote Desktop Users group.

b. Add domain group to Local Administrators (via GPO if necessary).

9. Click Windows/Start button, type “secpol.msc”.

a. Expand Local Policies and User Rights Assignment.

b. Add the DB Engine user account to “Perform Volume Maintenance Tasks.”

c. Do not add the DB Engine user account to local administrator group as IT groups often design a GPO to assign explicit members to this group.

10. Configure Windows Permissions for SQL disks.

a. Remove “Everyone” group from security on all disks:

i. DATA

ii. LOG

iii. TempDB

iv. Backup

b. Grant the DB Engine service account FULL CONTROL to each install/data/log/tempdb/backup disk.

c. Grant the SQL Agent service account FULL CONTROL to the install/backup drives.

11. Update Windows:

a. Run Windows Update 46 times and reboot after each install.

b. If you are deploying a Failover Cluster—download all required hotfixes (listed here, as of Aug. 27, 2014).

c. If you are deploying an AlwaysOn Availability Group—add these updates.

d. Now turn off automatic download and install updates.

12. Configure antivirus (yes, you should be running antivirus on your database server).

a. Whitelist these processes:

i. %ProgramFiles%\Microsoft SQL Server\MSSQL11.\MSSQL\Binn\SQLServr.exe

ii. %ProgramFiles%\Microsoft SQL Server\MSRS11.\Reporting Services\ReportServer\Bin\ReportingServicesService.exe

iii. %ProgramFiles%\Microsoft SQL Server\MSAS11.\OLAP\Bin\MSMDSrv.exe

b. Whitelist these file extensions:

i. Mdf, ldf, ndf

ii. Bak, trn

13. Download SQL Server installer, service pack, and latest cumulative update.

14. Review error logs for the server to identify any current issues (yes, once more).

Advertisements

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