In my previous post, I gave some overview thoughs on how to install and configure SQL Server 2012. These recommondations are collected in my work in current project and SQL Server 2012 certification. I have got me subscription at Pluralsight where they lots of training available. In addition, I pick up recommendations on MSDN SQL Server forums, SQLBlog.com, MSSQLTips.com, SQL Server Team Blog, SQLTeam.com and other resources.
Aquire service users. These users should a normal domain user, and not local administrator, and the password should be set to “Never Expire”. Here is a suggestion for naming convension for the SQL Service accounts. You might not need all the accounts. Only install the components that you need to increase the server performance.
Here is standard naming scheme I have used several times.
- Database (Engine) Service = Svc-SqlDB-xxx
- Agent Service = Svc-SqlAG-xxx
- Reporting Service = Svc-SqlRS-xxx
- Analysis Service = Svc-SqlAS-xxx
- Integration Service = Svc-SqlIS-xxx
Physical vs Virtual
The last few months, there have been an discussion (and still is) if we are able to install SQL server in a virtual environment rather than physical servers. EVRYs new concept “Future Proof” is based on high performance data center technology and we wants most of our servers to be virtualized. This will enable us to fast transitions between environments. With today’s technology, this is possible to, depending on the customer SLA requirements. Our main question for our decision is how the PCI-E storage cards perform in a SQL Cluster.
Storage type and space
You have many options when it comes to selecting storage type. Here are the four main types:
- Internal drives – traditional magnetic drives or solid state drives (SSDs)
- PCI-E Storage Cards
- Direct-attached storage (DAS) – traditional magnetic drives or SSDs
- Storage area networks (SAN) – traditional magnetic drives or SSDs
What kind of storage you shall use also depends of the main usage of the SQL server. A standard multi-usage SQL Server should have enough disk space, memory and CPU power.
- Online Transaction Processing (OLTP). Frequent writes to data files and log file make random I/O performance very important
- Relational Data Warehouse (DWH). Large sequential reads from data files makes sequential I/O performance very important
- Online Analytical Processing (OLAP). Lots of random reads from cube files makes Random I/O performance very important.
When requesting storage type for these types of databases, the sequential performance is measured in MB/second or GB/second, and random performance is measured in input/output operations per second (IOPS).
The RAID level should also be considered regarding redundancy and workload. RAID 5 is often used as a cost-effective level for backup while RAID 10 is most popular among DBA due to very good write performance.
Disks and directories
When working for customers, it is always annoying to when their servers don’t have a standardized naming scheme for disks and directories. This often make people (us consultants) wonder where things are located and we use much more time to pinpoint problems if this is our main task for the assignment. This applies specially if SQL clustering (Always-On) and/or mirroring is used for the SQL Servers. Jeremy Kadlec has good post on recommended disks and partitions.
As a general recommendation, data, log, tempdb and backups should be separated into different disks. In my current project, we use the following disk partitioning:
- DTC: H-drive
- Data: I-drive
- Log: J-drive
- Tempdb: K-drive
- Backup: M-drive
The drive letters it not important. It is the separation in different LUNs that are important.
The directories are not that important either, I normally hates “Clicking Hell” with deep directory structures, so I recommend the following
Windows Update, Service packs, drivers
Of of the first things you should do when Windows Server is up and running is to disable automatic installation for Windows Update. You will not risk outage for your SQL Server during daytime or when backup performed. Windows Update should be configured to automatic download, BUT with a notification only. Remember to have the latest service packs for Windows Server.
Depending on your company policies, you should always make sure to have the latest BIOS and other firmware. In addition, you should also update all device drivers, such as RAID controllers, Host Bus Adapters (HBAs), PCI-E storage cards and proper Network Interface cards (NICs) and the main board chip set driver packages.
If there is a power management option in BIOS, this should be set to OS controlled. If this is not possible, this BIOS setting should be disabled. The power management is by default set to “Balanced” power plan in Windows Server. For a SQL server, this can decrease the performance a lot. Therefore, you should always set the power plan to “High Performance”. You can verify the CPU speed and how it behaves in the Task Manager, or use the nice utility cpuid.com
SQL Service Account Policy Rights
There are two policy right that should be added to the SQL servcie account. The first “Perform volume maintenance tasks” enable Instant file initialization, reduce time for data file creation and speed up growth time. The second setting “Lock pages in memory” prevents OS from trimming the SQL Server working sets, but this is only available in Enterprise edition.
Max Server Memory
One of the important settings is the Max Server Memory available from instance properties in SSMS or from sp_configure. SQL Server will acquire as much as it can for its operations without regards to other applications and drivers. I have a golden rule to never let SQL Server 2008/2012 get more than 70-80% of the available memory on the server, depending on what services running and what SQL server components that is installed. Jonathan Kehayias has i nice post on this setting.
- Make sure your server has a static IP address
- SQL server should be added to the domain
- Configure anti-virus to skip .MDF, .NDF, and .LDF files
- Make sure there are no pending reboots in Windows