Anyone can install SQL Server on a stock deployment of Windows Server. It will run, it may even meet your expectations, but it can perform much better with some straight-forward tuning. There are several configuration settings to change prior to running the SQL Server installation.
I’ve compiled this deployment checklist after several years of deploying SQL Server for Credera’s clients. I’ve tried to be general where possible and specific where required. Hopefully this will avoid any future odd behavior or unusually subpar performance in SQL Server.
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).
The next steps will be running the SQL Server installer and getting the real work done! To contact a Windows Server Guru or SQL Ninja, email info@credera.com or call 972.759.1836. For more information on Microsoft Windows Server 2012 R2 and SQL Server 2012 and 2014, please visit our blog or follow us at @CrederaMSFT and @CrederaIT.
Contact Us
Ready to achieve your vision? We're here to help.
We'd love to start a conversation. Fill out the form and we'll connect you with the right person.
Searching for a new career?
View job openings
