As a part of a new book project, I am putting together a series of checklists that DBAs can use to inventory and audit their servers. Previously, I blogged about my SQL Server Hardware Checklist, in this post, I am writing about my Operating System Checklist. The goal of the checklist is to help DBAs document how the operating system is configured, which in turn could reveal some potential changes that should be made to the operating system in order to better optimize it for SQL Server.
Unlike the Hardware Checklist, this list may seem somewhat unfamiliar to many DBAs. This is because many DBAs aren’t very familiar with the operating system that SQL Server is running on, and in many cases, the operating system was setup and configured by someone other than themselves.
Below is the current version of my SQL Server Operating System Checklist, which is in the form of a spreadsheet that can be used not only to collect and store the information, but it can also be customized as needed by DBAs to better meet their environment.
| The Basics | ||
| Real or Virtual Machine: | ||
| Computer/Machine Name: | ||
| OS Version: | ||
| Windows Edition: | ||
| OS Build Number: | ||
| 32-Bit or 64-Bit: | ||
| Service Pack Level: | ||
| Are OS Updates Current: | ||
| IP Address: | ||
| What Domain or Workgroup Does the Computer/Machine Belong To: | ||
| Is OS Properly Licensed and Activated: | ||
| Product Key: | ||
| Location of OS Files: | ||
| Clustering | ||
| Is This Computer/Machine Part of a Windows Cluster: | ||
| Cluster Node Name: | ||
| Cluster Node IP Address: | ||
| Does the OS Have the Latest Hardware Drivers | ||
| System: | ||
| Display Adapter: | ||
| DVD/CD-ROM: | ||
| RAID Controller: | ||
| HBA: | ||
| Network Interface Adapter: | ||
| Other: | ||
| Partitions | ||
| Are Partitions Properly Aligned: | ||
| Are Partitions Formatted Using NTFS: | ||
| NTFS Cluster Size: | ||
| Is NTFS Compression Being Used: | ||
| Is NTFS File Encryption Being Used: | ||
| How Many Logical Partitions are Available: | ||
| Size of Each Logical Partition (by Drive Letter): | ||
| Percent Free Space Per Logical Partition (by Drive Letter): | ||
| Page File | ||
| Location of Pagefile.sys: | ||
| Size of Pagefile.sys: | ||
| Percentage Free Space in Pagefile.sys (under typical load): | ||
| OS Settings | ||
| Is "Processor scheduling" Set to "Adjust for best performance of Background services": | ||
| Is "Visual Effects" Set to "Adjust for best performance": | ||
| Is "System failure" Set to "Automatically restart": | ||
| Is Windows Update Set for "Automatic Updates": | ||
| Is "Indexing" Running: | ||
| Is the Existing Power Plan Set to "High Performance": | ||
| Is "Remote Desktop" Enabled: | ||
| Has "Audit Policy" Been Enabled: | ||
| Have Only Necessary Network Protocols Been Installed: | ||
| Which OS Roles are Installed (2008 or 2008 R2) | ||
| Active Directory Certificate Services | ||
| Active Directory Domain Services | ||
| Active Directory Federation Services | ||
| Active Directory Lightweight Directory Services | ||
| Active Directory Rights Management Services (AD RMS) | ||
| Application Server | ||
| Dynamic Host Configuration Protocol Server | ||
| DNS Server | ||
| Fax Server | ||
| File Services | ||
| Hyper-V | ||
| Network Policy and Access Services | ||
| Print and Document Services | ||
| Remote Desktop Services | ||
| Web Server (IIS) | ||
| Windows Deployment Services | ||
| Windows Server Update Services | ||
| Which OS Features are Installed (2008 or 2008 R2) | ||
| .NET Framework 3.5.1 Features Overview | ||
| Background Intelligent File Transfer Service (BITS) Overview | ||
| Biometrics Overview | ||
| BitLocker Active Directory Recovery Password Viewer Overview | ||
| BitLocker Drive Encryption Overview | ||
| BranchCache Overview | ||
| Connection Manager Administration Kit Overview | ||
| Desktop Experience Overview | ||
| DirectAccess Overview | ||
| Failover Cluster Overview | ||
| Group Policy Management Overview | ||
| Ink and Handwriting Services Overview | ||
| Internet Printing Client Overview | ||
| iSNS Server Overview | ||
| Line Printer Remote Port Monitor Overview | ||
| Message Queuing Overview | ||
| Multipath I/O Overview | ||
| Network Load Balancing Overview | ||
| Peer Name Resolution Protocol Overview | ||
| Quality Windows Audio Video Experience Overview | ||
| Remote Assistance Overview | ||
| Remote Differential Compression Overview | ||
| Remote Server Administration Tools Overview | ||
| RPC over HTTP Proxy Overview | ||
| Services for Network File System Overview | ||
| Simple Mail Transfer Protocol Overview | ||
| Simple Network Management Protocol Overview | ||
| Simple TCP/IP Services Overview | ||
| SNMP WMI Provider Overview | ||
| Storage Manager for SANs Overview | ||
| Subsystem for UNIX-based Applications Overview | ||
| Telnet Client Overview | ||
| Telnet Server Overview | ||
| Trivial File Transfer Protocol (TFTP) Overview | ||
| Windows Internal Database Overview | ||
| Windows Internet Name Service (WINS) Overview | ||
| Windows PowerShell Integrated Scripting Environment Overview | ||
| Windows Process Activation Service (WAS) Overview | ||
| Windows Server Backup Overview | ||
| Windows System Resource Manager Overview | ||
| Windows TIFF IFilter Overview | ||
| WinRM IIS Extension Overview | ||
| Wireless LAN Service Overview | ||
| XPS Viewer Overview | ||
| User Accounts & Security | ||
| List Account(s) Used for SQL Server Services: | ||
| What Permissions Have Been Given to the SQL Server Service Accounts: | ||
| List Account Used for Clustering Service: | ||
| What Permissions Have Been Given to the Cluster Service Accounts: | ||
| Does the DBA(s) Have Local Admin Rights: | ||
| Is the Windows Firewall Running? | ||
| Physical File Fragmentation | ||
| What are the Fragmentation Levels of Existing Partitions (by Drive Letter): | ||
| Is Any Active Physical File Defragmentation Being Performed Regularly: | ||
| OS Task Scheduler | ||
| Are There Any Custom OS Jobs Running in the Task Scheduler? | ||
| Is "Customer Experience Improvement Program" Task Running: | ||
| Is "Defrag" Task Running" | ||
| Other | ||
| Is Computer/Virtual Machine Dedicated to SQL Server: | ||
| Is Windows Defender Running: | ||
| Is Anti-Virus/Spyware Turned On: | ||
| If Anti-Virus/Spyware is Active, Have mdf, ldf, bak, and trn files been excluded: | ||
| What Non-Default Software is Installed Besides SQL Server: | ||
| What version of PowerShell in installed: |
In the above list, there are three sections that many DBAs may not be familiar with. They include:
–Which OS Roles are Installed: When the Windows Operating System is installed, the installer can choose if this particular server has one or more roles. In some cases, combining some of these roles with SQL Server can significantly hurt SQL Server’s performance. In other cases, one or more of these roles may be required. If you install a standard instance of SQL Server, none of these OS roles are required.
–Which OS Features are Installed: Features are similar to roles, but they are smaller in scope. As with roles, some features can hurt SQL Server’s performance, while others might be needed, depending on how SQL Server is configured. If you install a standard instance of SQL Server, none of the OS features are required.
–OS Task Scheduler: Lists the jobs that the OS might be running in the background. For example, by default, every Wednesday at 1:00 the Windows “defrag” program is executed, assuming that the server has been idle for at least 3 minutes. Did you know that?
Which of the above roles, features, and tasks should be on or off is beyond the scope of this blog post, but this topic will be covered in the new book I am working on.
As with the SQL Server Hardware Checklist, I would like your input on my SQL Server Operating System Checklist. For example, I would like your input on:
–What is missing from the list? What OS configuration information should I add? I am especially interested in tracking any OS configuration information that could affect SQL Server’s performance and availability. Keep in mind that I can’t include every possible variation.
–What on the list could be removed because it is not very important? Keep in mind that the list is designed to be generic, so there will be items on the list that will not be applicable to all SQL Server environments.
–Does my wording make sense, or should I change any of the wording so that it is more understandable or more accurate?
Please add your comments below, and as I get feedback, I will update the checklist. Thanks!
