Thursday, August 27, 2009

Building a High-end Windows 2008 Database Server - Page 5

Software Setup


After installing Windows 2008, I added the PowerShell and Backup components and nothing else.  I will talk about the important setting changes in the next section.

Next I installed the latest drivers for all my components.  The motherboard has a number of built-in components, such as the LAN controller, that must be considered separately. 

With the drivers installed, I could then connect to the Internet to run the Windows update.  I finished the installs with the Adaptec Storage Manager, which is the setup software that goes with my RAID controllers.

Windows Settings

There are many tweaks that I do to the standard Windows environment to make it more efficient for the way I work.  There are also many common practices for improving general performance.  I won’t be going over any of those here.  I will mention just a few things that are too often skipped.

Always disable services that aren’t needed, such as the Print Spooler and the WinHTTP Web Proxy Auto-Discovery service.  Also, this kind of server will never go into hibernation, so delete the hibernation file with the following command:

      powercfg -h off

The easiest way to apply the Windows license is with the following command:

      slmgr -ipk {PUT LICENSE # HERE}

RAID Settings

SQL Server is very efficient at reading and writing to hard drives.  To maximize performance, you need to understand a little about how it works.

SQL Server arranges data in groups of 8K pages called Extents.  Each extent contains eight pages for a size of 64K.  SQL Server can read one page at a time but always writes in full extents.  This is actually more efficient since the lazy writer can try to group data together to get the most out of what is otherwise one of the slowest tasks.

When reading/writing to a RAID array that uses striping, such as RAID 0, 5, or 10, you will get the best performance when extents do not cross disk boundaries.  There are two things you have to do to ensure this.  The first is to make sure your stripe size is a multiple of the extent size.  The other is to make sure the disk partitions are block aligned.  I will discuss the second part in the next section.

I tested using 64K stripes and 256K stripes with these controllers and different size blocks of data for both read and write.  With 64K data blocks, I saw no meaningful difference in performance.  But when I used larger blocks, the 256K stripes were faster.  I believe these results are due to the fact that the RAID controller may be optimized for 256K stripes.

My final setup is as follows:
Controller 1
 OS: two drives, RAID 1, use all space,
stripe size = 256,
enable write and read caches.
 DATA: six drives, RAID 10, use all space,
stripe size = 256,
enable write and read caches.
Controller 2
 LOGS: four drives, RAID 10, use all space,
stripe size = 256,
disable write cache.
 TEMPDB: two drives, RAID 0, use all space,
stripe size = 256,
enable write and read caches.
 BACKUP: one drive, use all space,
enable write and read caches.

To avoid putting the disk volumes on the wrong arrays, I create the above arrays one at a time while doing the next step.

Disk Volumes

There are two performance considerations here.  The first has to do with block aligning the disk partitions as mentioned in the previous section.  The second involves a very important technique called “short stroking.”

Block Aligned
It is critical for performance that a single written block go to only one hard drive and not get split into a partial block on one and a partial block on another.  The calculation for where to align the first block is:

Partition offset = Stripe size in bytes / Physical sector size of the hard drive in bytes

All hard drives, unless stated otherwise, use a sector size of 512 bytes.  As stated in the RAID Settings section above, we are using 256K strips.  So, the calculation is:

Offset = (256 * 1024) / 512 = 512

I used the command line program DiskPart that comes with Windows to create my aligned partitions.  I believe that, starting with Windows 2008, all partitions are automatically block aligned, but I will continue to use DiskPart because it is faster then loading Computer Management.

Short Stroking
Before I create my partitions, there is one more thing to consider that has a huge impact on performance.  It is common knowledge that data on the outside tracks of a hard drive platter is read and written faster than on the inside tracks.  This is because the track is longer and therefore holds more data even though the entire track takes the same amount of time to pass the read/write heads.  Even more important, however, is that it takes a relatively long time to move the read/write heads from one track to another.  The further away the track, the longer the delay before more data can be processed.

Short stroking takes advantage of the above two facts.  If you were to put all your data on only the outermost tracks, you would be using the fastest tracks while minimizing the movement of the heads.  The easiest way to do this is to break your disk arrays into multiple partitions.  Each partition gets spread across all the disks in the array by the RAID controller and partitions are created from the outside of the platter first.

For my database servers, I used a little over 10% for the first partition on the Data, Logs, and TempDB arrays.  I also created a second partition on Data and Logs of 10% for databases that don’t get used much.  The rest of the space I am throwing away.  I can afford to use so little capacity because I have so many drives in my system with plenty of room to add more.  The OS and Backup arrays use the entire disk.

The Commands
If you enter diskpart at a command prompt, a console window will open where you can enter the diskpart commands.  You can enter the command, “list disk”, at any time to get the disk numbers needed by the select command.

To create two 30 GB partition on the LOGS array and assign drive letters L and M:

 select disk {disk#}
create partition primary align=512 size=30720
create partition primary align=512 size=30720
select partition 1
format label=Logs-Primary quick
assign letter=L
select partition 2
format label=Logs-Secondary quick
assign letter=M

As a matter of practice, I use drive letters L and M for LOGS, F and G for DATA, T for TempDB, and W for Backup.  This leaves room for adding partitions in the future with consecutive letters if I need to, and makes it easy to remember where everything is when temporarily mapping to a drive from another server.

The commands to create the two DATA partitions and the one TempDB partition are the same except for the label names and drive letters and I used 51200 for size to create 50 GB partitions for DATA.

The commands for Backup are:

 select disk {disk#}
create partition primary
format label=Backup quick
assign letter=W

When I am finished:


Final Drive Settings
There is no need for file indexing on my non-OS drives, so I turn that off.  Right-click on a drive letter, select Properties, and unselect “Index this drive.”

The size of the recycle bin is unacceptably large on all the drives and entirely not needed on TempDB.  I accept that it is unlikely that a database is going to fit anyway, and most everything else is going to be small.  So I use the following recycle bin sizes:

 OS and Logs: 1024 MB
Data and Backup: 4607 MB
TempDB: None

The last thing is to create directories on the new volumes.  I know I will be upgrading SQL Server at least once during the life of these machines and I will likely have overlap where I have multiple versions installed.  To simplify life down the road, I start by including the version name in the directory names.  Here are the DOS commands that I used:

 MD "F:\SQL2005 Data"
MD "G:\SQL2005 Data"
MD "L:\SQL2005 Transaction Logs"
MD "M:\SQL2005 Transaction Logs"
MD "M:\SQL2005 Error Logs"

       (Add shortcut to the above directory on L:\)
  MD "T:\SQL2005 TempDB"
MD "W:\SQL2005 Backup"
MD "W:\System Backups"


You may recall that my motherboard has four gigabyte Ethernet ports that support teaming.  I took advantage of that by creating two teams of two ports each, with each team on two switches within my redundant private network.  The two ports on the left use the Intel Pro-1000EB controller and the other two use the Intel Pro-1000PL.  I chose not to cross controllers when creating my teams although I could have put all four ports on one team.  Each team gets its own IP address and settings that are used instead of the IP and settings of the individual ports.

SQL Server

There are quite a few settings that need to be changed depending on your situation.  Some are performance related but require testing to determine what is best for your specific server and databases.  Covering those settings is an article in itself and will be skipped here.  Many other settings are required in your situation because of the features you are using.  There is no need for me to cover those since they will be forced upon you anyway.  The only settings I will cover are ones that are completely optional yet I consider always required.  I know others will believe I have omitted important things from this list, but their lists don’t contain all my settings either.

Once you have everything set up, don’t forget to install the latest service release and patches.

SQL Server Service Account
Always use the most restricted account possible for running the SQL Server Service.  If you’re not sure what permissions/restrictions to give such an account, start by using the Local System account.  Never use an Administrator account (local or domain) except for troubleshooting.  Whatever account you use, I will be referring to it as the SQL Server Service account below.

Instant Initialization
An important feature in Windows starting with 2003 (and XP) is called Instant Initialization.  Usually when you create a file initialized to be a specific size, Windows will fill the file with binary zeros.  This prevents random data from appearing within a file and also prevents a user from seeing secure data that had previously been “deleted”.  Instant Initialization is an optional way to create a file and skips the zeroing process.

SQL Server, starting with 2005, is able to request Instant Initialization for files.  This is important because the files created by SQL Server tend to be huge and would otherwise take a considerable amount of time to create.  Because of the security implications of being able to create a file that is not zeroed, only processes that have “Perform volume maintenance tasks” permission can request this.

To give the SQL Server Service account this capability, open the Local Security Policy MMC and go to Local Policies..User Rights Assignment. You can then add the account to the “Perform volume maintenance tasks” entry.  SQL Server will then automatically use Instant Initialization after it has been restarted.

While you are at User Rights Assignment, you should also give the SQL Server Service account the ability to “Lock pages in memory” since SQL Server does its own efficient memory management.

As stated in the Hard Drives..RAID section near the top of this article, TempDB should be spread across multiple files – one per CPU core.  I am using two 4-core CPUs, so I will use eight files.  I will also move the TempDB from its default location.  Here is the SQL script that accomplishes this using the drive letters and directory names I created above.  The sizes I set are based on trends from our pre-existing servers.  Ideally, the initial size should be set so that the files are not overly large yet never need to grow.  Your sizes will likely be different.  SQL Server will recreate the TempDB files every time it is started.

 use master
  (NAME = templog,
  'L:\SQL2005 Transaction Logs\templog.ldf')
  (NAME = tempdev,
  FILENAME = 'T:\SQL2005 TempDB\tempdb.mdf',
  (NAME = tempdev2,
  FILENAME = 'T:\SQL2005 TempDB\tempdb2.ndf',
  (NAME = tempdev3,
  FILENAME = 'T:\SQL2005 TempDB\tempdb3.ndf',
  (NAME = tempdev4,
  FILENAME = 'T:\SQL2005 TempDB\tempdb4.ndf',
  (NAME = tempdev5,
  FILENAME = 'T:\SQL2005 TempDB\tempdb5.ndf',
  (NAME = tempdev6,
  FILENAME = 'T:\SQL2005 TempDB\tempdb6.ndf',
  (NAME = tempdev7,
  FILENAME = 'T:\SQL2005 TempDB\tempdb7.ndf',
  (NAME = tempdev8,
  FILENAME = 'T:\SQL2005 TempDB\tempdb8.ndf',

To verify the results of the script, you can run the command:

 select name, physical_name, state_desc
from sys.master_files

Backup Directory
There are many SQL Server settings that can only be done in the registry.  The path for the default Backups directory is one of those.  Browse to:

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer

Set the value for BackupDirectory to:

W:\SQL2005 Backup

Default DATA and LOGS Directories
Protect the integrity of your optimized disk arrangement by setting the default paths for new databases.  Otherwise, you or someone else may forget to specify the correct locations at database creation, and then you will find yourself needing to take the database offline in order to move it.

From Enterprise Manager, right-click on the Server and select Properties.  Go to the Database Settings tab to set the directories.

Other Files
I want to use the directory “M:\SQL2005 Error Logs\” for all error logs and I will need to tell SQL Server that I moved the master database to my DATA drive.  I can do all of this using the SQL Server Configuration Manager.  For the master database, I used the Configuration Manager to change the startup command line options for SQL Server.

Open SQL Server Configuration Manager and select SQL Server 2005 Services in the left pane.  Right-click on SQL Server ({instance name}) and select Properties.  Go to the Advanced tab.  For Dump Directory, I entered:

M:\SQL2005 Error Logs\

For Startup Parameters, I entered the following values for the -d, -e, and -l parameters:

 -dF:\SQL2005 Data\master.mdf;
-eM:\SQL2005 Error Logs\ERRORLOG;
-lF:\SQL2005 Data\mastlog.ldf

To change the path of the SQL Server Agent error logs, right-click on SQL Server Agent ({instance name}) in the right pane and select Properties.  On the General tab, you can change the path to:

M:\SQL2005 Error Logs\SQLAGENT.OUT

Don’t close the Configuration Manager yet as you will need it for the next step.

Set Port for TCP/IP
Of course I will be accessing my databases from other servers, which means I have to set up a firewall rule, and that means I need to set a fixed port for TCP/IP.  Since I already have the Configuration Manager open from the previous step, I will set the port now.  The default instance of SQL Server uses tcp port 1433 which I will use in all my examples below.

Select the server instance under SQL Server 2005 Network Configuration.  In the right pane, right-click on TCP/IP and select Enable if necessary.  Right-click on TCP/IP again and select Properties.  On the IP Address Tab, scroll down to “IPALL” at the bottom and set TCP Port to 1433.

After you close the Configuration Manager, you will need to restart the SQL Server service.

Whatever port number you use, you will need to include it as part of your connection strings if you do not start the SQL Browser service.  I recommend keeping the service disabled in production environments to reduce the attack surface and reduce the number of running programs.  Your connection strings will now use “{ServerName},1433” instead of “{ServerName}\{InstanceName}”.

Enabling Remote Administration
The first installed instance of SQL Server uses tcp port 1434 for remote administration.  You will not only need to open this port on the firewall, but will also need to enable it in SQL Server.

You can use either the Surface Area Configuration tool or Management studio.  Below are the instructions using the Surface Area Configuration tool.

Open SQL Server Surface Area Configuration and click on the link for “Surface Area Configuration for Services and Connections”.  Expand your instance in the hierarchy and go to Database Engine..Remote Connections.  Set “Local and remote connections” with “Using TCP/IP only”.

You need to allow incoming connections to port 1433 and 1434.  Below are instructions for the built-in Windows 2008 firewall using minimal access.

Run “WF.msc” or open the Windows Server Manager and go to Configuration..Windows Firewall with Advanced Security..Inbound Rules.  Add a new rule of – Local specific ports: TCP 1433, 1434; Remote: all ports; Profiles: Domain.  You can add other restrictions, such as limiting the allowed IP addresses, if your situation allows.

What Else

There are really a hundred other things that go into the setup of a server such as backup schedules and account policies.  Much of what is left falls under common company standards and I feel comfortable skipping all that in this article.  It is easy to see why larger companies assign a team to set up an individual server and pay far too much for their hardware.  For most humans, this is all just too much to know.

Next Page: Pictures

Please go to the first page to read or post comments.