Building a SQL Server 2008 R2 Database Server

Posted on 16. Mar, 2011 by joshhrob in Database

 Print This Post

This build doc demonstrates the installation of SQL Server 2008 R2 on a 64-bit Windows 2008 R2 server. It also discusses some best practices to consider when planning your database implementation.

This Document is divided into the following sections:

• Things to Consider
• Pre-Installation Tasks
• Formatting Drives
• Setting Page File
• Create Service Accounts
• Run SQL Server Setup
• Verify Installation

For installing Windows Server 2008 R2 see Building Windows Server 2008 (Enterprise R2 64 bit)

Things to consider

• When installing SQL 2008 R2, it is recommended that you use Windows Server 2008 R2. Although, you can install SQL 2008 R2 on Windows 2008 R1 or even Windows 2003, there are features that go hand-in-hand between the software and the OS, which work best when installed concurrently.
• 32-bit vs. 64-bit – If the server where you are installing the database has more than 2 GB of RAM is is highly recommended that you use the 64-bit version of both SQL and Windows. You will see dramatically better performance, with very little downside. I am only aware of 2 reasons to avoid 64-bit: 1 if you are using third-party software that is not compatible (i.e. antivirus, monitoring, etc.) or if you are using legacy VB6/OLE components as much of the support for these legacy dlls was not carried over to the 64-bit platform.
• It’s recommended that you store your OS files, Data Files, Log files, and third-party app files on separate physical drives. If you are working with a SAN environment, have your SAN administrator provision LUNS for each drive, sized to allow for growth. This is discussed more below.
• When formatting drives for Data or Log files it is recommended to use 64K blocksize
• It is recommended that your system is a member of a domain, and that SQL is configured to run as a domain service account.

Pre-installation Tasks

• Make sure the system is up-to-date with the latest updates and patches
• Install all of your third-party components (i.e. antivirus, monitoring, backup software, etc.). I always like to make sure I have all of these things installed before installing SQL, so if there are any unforeseen compatibility issues I will find out ahead of time rather than breaking my working installation and having to start over again.

Formatting Drives

In this build we assigned 40 GB for the C-drive, and added 4 additional drives formatted as follows:

(C:)	Operating System	40 GB
(L:)	Log Files	12 B
(G:)	Data Files	16 GB
(H:)	Backups	16 GB
(Z:)	Third-Party Apps	8 GB

Be sure to plan ahead, and carefully estimate how much space you will need for each drive.

To open Disk Management, click on the Windows Start button and right-click on Computer. Select Manage.

On the left side, click on Disk Management

If you just added new disks they may not appear right away. Right click on Disk Management and select Rescan Disks

The rescan takes about a minute, and the new blank disks appear

However, the disks are not online. Right click on each disk and select Online

Now right click on the first disk and  select initialize

The following screen comes up. Select each disk and press OK to initialize all of them. (make sure MBR is selected)

Now format each disk be right clicking on the black area and selecting New Simple Volume

Click Next

Use the entire disk and press Next

Assign a drive letter

Use NTFS, select 64K and give the drive a label. Press Next

Repeat for each drive. When your finished you should see all your drives

Setting Page File

If you are using 64-bit it is recommended to increase your page file to 8 GB.

Click on the Windows Start button, and Right Click on Computer. Select Properties.

On the left side, click on Advanced system settings

Under Performance, click the Settings button

At the top of the windows click the Advanced tab

Under Virtual Memory click the Change button

Uncheck the check box labeled Automatically manage paging file size for all drives

Make sure the C drive is selected. Enter 8192 for initial size and Maximum size. Press the Set button and then press OK

The page file should now be set to 8 GB and exist on the C drive. Also note that the processor is set for Background Services (not programs)

 

Create Service Accounts

It is recommended that you configure SQL to run as a domain service account. If you are not on a domain, you can use local accounts (as we do here) or configure SQL to run under the Network Service account.

You should create an account for SQL Server, and SQL Agent, and if they don’t already exist, accounts for the people who will be managing SQL. In this build we will use local Windows accounts named sqlservice, sqlagent, and user1. User1 is a member of the Administrators group, and sqlservice, sqlagent are just default users with no special privileges at this point.

Run SQL Server Setup

Make sure you are logged in as an Administrator. Insert the SQL Server 2008 R2 installation DVD. Press Run Setup

There is a prompt asking to enable the .Net Framework Core role. Press OK.

If you want, click on the link to run the System Configuration Checker to make sure you meet all of the installation requirements.

The tool runs for a few minutes, and gives you results. Click on the Show Details button to see results. Close windows when finished

 

On the left side click on Installation

Then click on New Installation or add features to an existing installation

The installer does some checks. Press OK when finished.

Enter your product key

Accept the license terms

Press the install button

Installer runs for a minute. I received one warning telling me to make sure the Windows Firewall is configured to allow the SQL ports. Press Next to continue.

Select SQL Server Feature Installation and press Next

Select the features you want to install. In this build we will install: Database Engine Services, SQL Server Replication, Analysis Services, Reporting Services, Buesiness Intelligence Development Studio, Client Tools Connectivity, Integration Services, Management Tools Basic and Complete. Press Next to Continue.

Note: I found an interesting bug here. I thought about changing the installation directory to my Z drive, I changed it, and then before pressing Next I decided to change it back. When I pressed Next I received this error: The INSTALLSHAREDWOWDIR command line value was not specified. The value must be specified when…

There was no way to make this go away. I googled it and apparently when people try to change the default directories here they get this error. There is some kind of workaround but I just started over again and left the defaults alone. Good thing I sized my C drive with extra space.

A screen labeled Installation Rules runs. When finished press next.

Leave Default Instance selected and press Next

There is a disk usage summary. Press Next

I changed a couple things on the next screen. I changed SQL Server Agent Startup Type from Manual to Automatic. Then I entered the usernames and passwords for the service accounts.

In my environment I have applications that use SQL Server accounts, so I selected mixed mode, and entered an sa password.

Next, press Add and add the usernames of the people who will be managing this database.

After you add your DBA users, click on the Data Directories tab at the top of the window

Change the data directories to the G drive, Log directories to the L drive and backup directory to the H drive

If you selected to install Analysis Services previously, Press the Add button to add users to manage Analysis Services and when finished press Next

If you selected to install Reporting Services, select Install the native mode default configuration and press Next

Decide if you want to send data to Microsoft and press next

A screen labeled Installation Configuration Rules runs. Press Next when finished.

Finally, we’re ready to install

Installation runs for a while (15 minutes or more)

If all goes well you should finally see this

Verify Installation

If you want to check to make sure your database is working, log in as one of the DBA users assigned during installation, and click on the Windows Start button -> Programs -> Microsoft SQL Server 2008 R2 -> SQL Server Management Studio

Enter the host name of this server

It might take a minute to log in for the first time, but eventually you should see this

 

If you have any questions or comments about this build document please post them below.

 

 

Tags: sql, sql server, sql server 2008

Print This Post