Software Setup
Installs
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.”
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 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.
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:
exit
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" |
Networking
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, 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.
| use master ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'L:\SQL2005 Transaction Logs\templog.ldf') ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'T:\SQL2005 TempDB\tempdb.mdf', SIZE = 200MB, MAXSIZE = 2500MB, FILEGROWTH = 10MB) ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'T:\SQL2005 TempDB\tempdb2.ndf', SIZE = 200MB, MAXSIZE = 2500MB, FILEGROWTH = 10MB) ALTER DATABASE tempdb ADD FILE (NAME = tempdev3, FILENAME = 'T:\SQL2005 TempDB\tempdb3.ndf', SIZE = 200MB, MAXSIZE = 2500MB, FILEGROWTH = 10MB) ALTER DATABASE tempdb ADD FILE (NAME = tempdev4, FILENAME = 'T:\SQL2005 TempDB\tempdb4.ndf', SIZE = 200MB, MAXSIZE = 2500MB, FILEGROWTH = 10MB) ALTER DATABASE tempdb ADD FILE (NAME = tempdev5, FILENAME = 'T:\SQL2005 TempDB\tempdb5.ndf', SIZE = 200MB, MAXSIZE = 2500MB, FILEGROWTH = 10MB) ALTER DATABASE tempdb ADD FILE (NAME = tempdev6, FILENAME = 'T:\SQL2005 TempDB\tempdb6.ndf', SIZE = 200MB, MAXSIZE = 2500MB, FILEGROWTH = 10MB) ALTER DATABASE tempdb ADD FILE (NAME = tempdev7, FILENAME = 'T:\SQL2005 TempDB\tempdb7.ndf', SIZE = 200MB, MAXSIZE = 2500MB, FILEGROWTH = 10MB) ALTER DATABASE tempdb ADD FILE (NAME = tempdev8, FILENAME = 'T:\SQL2005 TempDB\tempdb8.ndf', SIZE = 200MB, MAXSIZE = 2500MB, FILEGROWTH = 10MB) |
To verify the results of the script, you can run the command:
| select name, physical_name, state_desc from sys.master_files |
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer
Set the value for BackupDirectory to:
W:\SQL2005 Backup
From Enterprise Manager, right-click on the Server and select Properties. Go to the Database Settings tab to set the directories.
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.
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}”.
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”.
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.
