Installing Microsoft SQL Server 2008 on Windows Server 2008R2 Enterprise ( step by step screenshots )

April 28 2011

In: Blog
Posted By: Lawrence Patrick
Tags: Intalling, screenshots, SQL Server 2008R2, Windows Server 2008R2
Comments: 0

Installing Microsoft SQL Server 2008 on Windows Server 2008R2 Enterprise ( step by step screenshots )

Background Information:

When you are installing SQL on a cluster, VM or standalone server there are a few key points you must carefully consider before starting your installation.

Each type of installation scenario, will have some specific settings you will have to watch out for. For example, take the SSIS service; if you are installing on a cluster you must follow specific steps to ensure this services fails over with the cluster. That topic is out of the scope of this article. This article illustrates how to install MS SQL Server 2008R2 on Windows Server 2008R2 Enterprise to be used as a standalone MS SQL server. This installation shows how to properly configure for optimal performance using attached storage device i.e SAN. Disclaimer; depending on what SAN you are using, what purpose your SQL Server will server, what type of drives make up the storage presented to SQL Server & the size of the drives could all affect design decisions for an installation. This one illustrates best practices that should be followed in a generic way.

Step 1: Preparation for Installation

1. Ensure you have all the media you need to Install Microsoft SQL Server.

2. Ensure your OS installation is patched & up to date.

3. If it is a VM, please ensure you are using the right type of storage for your installation, ensure all logical drives are accessible.

4. If you are installing SQL 2008 R2 on Win2k8R2 Enterprise; please ensure you install the .Net Framework 3.5.1 & you download the latest SQL Service pack for this version of MS SQL.

5. Create all domain accounts necessary for the installation. Do not install & run the database engine using a domain admin account. Simply ensure you have local admin rights to the box and the right to add objects in Active Directory. This will ensure all SPN’s are properly registered on the domain.

TIPS:

a. Use a naming convention for the accounts example: SQL_MSFT_SQLS_<SERVER_NAME> this example prefixes the account name with SQL so when you search for SQL in Active Directory, all accounts using that prefix will show up on the screen. This example places the company domain or short name next; this example using MSFT representing of course, Microsoft. (this is arbitrary) Next this example uses SQLS and that stands for SQL “Service”. You can extend that further and use SQLA for the Agent and SQLI for Integration services if you will be using this server as an SSIS box.

b. You should not promote all your MS SQL Servers to be SSIS servers. Try to dedicate one box(cluster) for this purpose.

6. Preparing your Disks for optimal performance: there are many ways to do this depending on your hardware, database size & purpose. Generally speaking, you should try to separate IO types and do your best to optimize parallel reads. That information alone should steer you in the right direction.

TIPS:

a. Storage: The fastest type of storage is RAM (solid state disks) but it is very costly, next we have RAID types to aid in speed and redundancy. Raid 10 is the fastest for reading and writing data. Raid 5 is fast for reading but slower for writing. Both RAID levels provide for redundancy. The other RAID levels will not be discussed in this article. Lastly, the speed of the drives & the size of the drives all matter. Smaller drives are always faster than larger drives(why? because the read head (needle) has less area to travel and gather all the data off the disk.

b. Storage: Even if you do not have attached storage you should still logically separate database files. Why? Because it will show that you are a DBA that is prepared to move into an environment that provides optimal performance.

c. Database Files: Generally speaking, 1. separate mdf and ldf files (data and logs respectively). Why? because your objective is to isolate IO types and data files perform Non-Sequential IO operations. Conversely, log files perform Sequential IO operations. If both of these files types reside on the same physical drive; IO operations will compete with each other for disk resources. Other considerations are operations that happen in the TEMPDB database like sorts, orderby’s and other operations that have a mix of IO types; this is why it is recommended to move TEMPDB to a different directory than the logs and the data files.

d. Another considerations is backup operations, full text catalogs & other activities that should not compete with the disks running the production data. You should follow best practices and have a dedicated drive for backups, full text catalogs and other activities of this type.

e. Take the time to make everything very descriptive and in a standard fashion; I had to manage 300+ database servers and I can’t begin to tell you how standards pay dividends later on. Use D for DATA, L for LOGS, T for TEMPDB, M for BACKUPS.

IMPORTANT

– If you have a VLDB (very large database; your installation disk considerations will be different, for example you may want to employ file groups and assign specific tables to one file group and specific tables to another file group, additionally you may want to move around indexes to further gain parallel red performance) you should at least consult with an expert: Toll Free Call the koverus.com Database Experts at 1-888-950-DATA

Here is a screen shot of the storage setup for this installation; in some environments you will see an additional drive for the windows paging file (pagefile.sys):

Step 2: Prepare for the SQL Installer

1. Domain account permissions: Once your machine is setup, patched,has the .Net frame work installed, has all logical disks configured and all your domain accounts have been created. Ensure your accounts have the correct permissions. Please follow this article

2. In each of your drives, create the following folders:
a. D:MSSQLDATA

b. L:MSSQLLOGS

c. T:MSSQLDATA

d. T:MSSQLLOGS

e. M:MSFTNASQL01_BACKUPS

Step 3: Start the SQL Installer

1. Browse to your SQL Media and invoke setup.exe

2. The installer will kick off and start the installation; if you get the following dialog box just click “Run Program”

The SQL Installation Center: If you are not familiar with all the selections and links on this tool; please take the time do so. We will skip directly over to the installation.

3. Select Installation on the left and bring up the following screen.

4. Click on the first option on the right of the tool:

if you get this popup; just click “Run program”

5. Make sure you pass the Setup Support Rules & click “OK”. If you run into issues, address them and restart the process:

6. Enter your product key and click “Next”

7. Accept the terms and click “Next”:

8. Install the setup support files by clicking “Install”:

9. Once the support files are installed and you have passed all checks click “Next”

10. Feature Selection Screen: Deceptively Tricky screen, if you are installing a development server but this server is for production and we will make our selections and click “Next”

11. Instance Configuration: select the default instance (SQL server will assume the hostname of the server you are installing on. If you choose named instance, the server will behave differently and carve out resources based on how many instances you have running on the box. If you are not sure always use the default instance. We will leave everything as the default:

12. Disk Space Requirements: ensure you meet the requirements and click “Next”:

13. Server Configuration: Input the domain accounts you created and configured for each service:

13.a Collation: if you are unsure as to what collation to use, just leave the default setting. Accept the settings and click “Next”

14. Database Engine Configuration: Set your value and then select the Data Directories Tab

TIPS

a. Make sure you select mixed mode and provide a passphrase not password for this account.

b. Create a domain group in Active Directory called “SQL_Production_DBA” and add your account to that group. This group should be a member of the local admin group on every SQL server on your network (minus the ones you do not have control over)

Change the default values below to the image on the next screen: