SQL Server 2012 – installing and configuration – part 2


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.

Pre-install tasks

Service accounts

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:

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.

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).

CrystalDiskInfo is a good tool for measuring disk performance recommended from different sources. Another Microsoft tool for benchmarking disk system is SQLIO.

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

  • I:\SQLData
  • J:\SQLLog
  • K:\SQLTempdb
  • M:\SQLBackup

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.

Power Management

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.

Other considerations

  • 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
Advertisements

One thought on “SQL Server 2012 – installing and configuration – part 2

  1. Pingback: Installing SQL Server 2000 | Sveroa's Developer Blog

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