Installing SQL Server 2008 R2

July 31, 2012 Jayanth.Kurup For the DBA, 1

Every DBA should be familiar with how to install and configure MS SQL Server. While there are many ways to do it the DBA is expected to be familiar with at least being able to install SQL Server correctly using the GUI. Additionally the DBA should also be familiar with how to perform an unattended installation as well. We will focus on the installation process using the GUI for now and visit unattended installations in a later blog. A key thing to remember here is to plan the installation. Most DBA would use a checklist with details on the pre requisites , the installation process , components that need to be installed , features that need to be enabled or disabled and the final verification of all features to ensure that everything is working as expected. Configuring SQL Server is another aspect of the installation process and will need to be covered in detail later.

Some important consideration that need to be taken into account include the OS version i.e. is it Windows 2003 or 2008 R2 and what patch level is being used currently. Others include the platform architecture such as 32 bit vs. 64 bit. The amount of RAM and number of CPU cores, number of disk drives etc. also play a crucial role. Usually you would need to stress test the disk subsystem using a tool such as SQLIOSim etc. to make sure the server I/O throughput is sufficient. Proper database sizing is another crucial aspect that needs to be focussed on. Incorrect sizing means that the server may not scale as expected or could cost more in network and storage costs. Some software consideration to keep in mind include the Patch level of the OS and the instance of SQL that need to be setup. Also if you are installing a new instance on an existing system you need to make sure to account for additional workspace required by the new instance. Backup and other maintenance software, anti-virus etc. also need to be evaluated and tested before deploying to a production system.

 

So let us look at how to install SQL Server step by step now.

Assuming the installation cd is in a Drive or on a network share. Navigate to the setup.exe file and double click the installer. If you have downloaded the file from the website you might need to unpack the file first.

Once the files are unpacked

When you double click the setup file you should see the below screen.

This will lead you to the installation centre. This is a landing page for various options available as part of installing SQL Server. For example, on the below screen you see the option to perform a new installation or simply add new features to an existing one. You also have the option to add to a failover cluster and upgrade. The menu bar on the left also has a number of useful features such as system configuration which will let you know if the server is setup and meets the pre requisites to install SQL server. Click the “New installation or add features to an existing installation” link.

 

The below screen will appear.

The setup process will install a few files that are required to support the installation process.

Once the above step is completed the screen should display a summary of the checks and any pending actions that require the DBA to take steps to correct them

The next step is for setup to install some files that will help complete the install process , press install in the below screen.

This will result in a summary just like before. There will be status such as passed , warning and failed , evaluate the different rules output and take any action that is required. After which press Re-Run to verify once again.

In the next screen we begin the actual installation process. Here we select the type of installation we want to perform such as a new installation as default instance or a named instance. Select the radio button for “New installation or add shared feature” (adding share features means installing components that are not instance aware such as SSMS, BIDS, client protocols etc.). You might notice that there is already an instance of SQL Server in the screen below. This is an SQL Express edition installation and is actually installed as a named instance called “ServerName\SQLExpress” so we can still go ahead with a default instance installation.

The next screen will ask you to enter the product key. In this case I am installing the trail version of SQL Server 2008 R2 developer edition. I will select evaluation as a result. You can download the trail version of MS SQL server 2008 R2 and 2012 from the Microsoft website.

Press next and move on to the licence agreement screen and choose I accept and press next.

You can install SQL Server in three different modes as shown below. For now choose SQL Server Feature installation. Power pivot is a feature that allows business users perform BI on the data from the database in clients such as SharePoint and MS Excel.

The next screen is important in terms of what actually gets installed on the server. I have chosen to install all features except SSMS Management tools (this is because I already have a version of SSMS installed with my SQL Server express edition. I will need to uninstall it and reinstall the advanced toolset). The basic SSMS feature doesn’t come with some important features like profiler etc.

Unlike other database products MS SQL Server contains installer for the RDBMS as well as the BI Stack. The BI stack consists of Analysis Services, Integration services (a shared feature) and Reporting service. Choose the features that need to be installed as per your requirements and press Next.

 

You will see a progress bar

And a summary

The next step is to perform the instance configuration. Here we give a name to the named instance if we were installing one. In this case since we are installing the default instance simply leave it as is. You can also change the drive and folder in which the binaries are installed but changing the path of the instance root directory. Notice the default paths listed for each of the components below.

Press next

Press Next to move to the service account configuration screen. This is an important screen and the accounts and start up types defined here play a critical role in how SQL behaves. I will cover these in detail in a later blog. The key principle to key in mind here is to use accounts that are domain accounts and have very restricted permissions focussed only on those operation that are required for SQL to run. If you do not need a particular service consider setting the start-up type to Disabled by default SQL Browser is disabled by default and SQL Agent is configured to start up manually.

On the same screen you will notice there is another tab called collations, click it to view the below screen. Collations are how SQL identifies the local or linguistic properties of the data stored in the database tables. You can set collations at various levels within the SQL Server instance but pay close attention to choosing the right type of collation here. It is a best practice to use windows collations as the default and choosing Latin1_general will work for most users.

Press OK and next to move on to the next screen

The above screen is where you setup the administrators for the new instance of SQL you are installing. There are two types of accounts that you will be able to create: – A windows authenticated account and a SQL Account. If you select Windows authentication mode then all login on the server will need to be authenticated using window authentication i.e. via the OS or AD. If you choose mixed mode then you can create user who are authenticated via Window or as a SQL login. If you select the mixed mode option you are required to enter a password for the sa user. The sa user is a SQL login that has admin permission on the database server. Be sure to select Add current user or assign a windows authenticated user to the administrator group on windows. Press next.

You will notice there is a tab on the screen called data directories where you can specify the folder locations where different databases and other features get installed. It is a bets practice to place files on different disk LUNs to increase I/O throughout.

You can enable Filestream and configure it in the screen below; we will cover Filestream in detail in a later blog.

If you have selected any of the other options like Analysis Service or Reporting Services you will see the below screens to configure them as well. SSAS or SQl Server Analysis Services support only windows authentication so you need to add a domain account as the administrator.

The below screens show the default locations where the SSAS database will be created.

The below screen shows the steps required to install SSRS or SQL Server Reporting services. You can install Reporting services using three different modes as shown below. Select the first one and press next. We will look at how to configure Reporting services in the blog configuring Reporting services.

Press next and move on

Press Next

The below screen summarize the steps we have performed so far. These configurations are also available in the configuration file shown below.

The above configuration file is important when performing an unattended installation. Make sure you take a backup of it in case you perform frequent SQL installations using the above setup.

At this point we are done telling setup what steps need to be performed and can now press Install

The progress bar shows the steps being performed. When the installation is complete you will see the below screen and it will ask you to restart the system.

Press Close

That’s it , you have now installed SQL Server. The next Blog will focus on the checks you need to perform after installing SQL and how to configure various options.

 

Jayanth is a trainer and database consultant at www.enabledbusiness.com

 

 

2008 r2, bangalore, database, how to, install, jayanth, sql server, step by step, trainer, www.enabledbusiness.com

Copy Only backups Dedicated Administrator Connection (DAC)