Installing SQL Server 2008 R2 on Windows Server 2008 R2

Wednesday, December 28, 2011

Installing SQL Server is something that I am not doing every day and once in a while when I have to, I am always losing time by searching through my books and the Internet. So I thought that it would be wise to invest some time in creating a blog post that goes to every necessary step. As a .Net developer I mostly use the express or developer edition of SQL Server. I usually install the express edition on my workstation and the developer edition on a virtual server with Windows 2008 R2.

The developer edition has the same features as the enterprise edition but may only be used for development purposes. The express edition is free of charge and has the most limited set of features. For a complete comparison of all editions you should check the Microsoft SQL Server website. In this blog post we focus on installing the developer edition on a Windows 2008 R2 machine.

Before we start

Installing a production database server is a whole different game then installing a development database server. This includes more then just installing the software and are just as important. Just to name a few:

  • Choosing the right hardware configuration for supporting your application load and volume.
  • Creating proper protecting against disc failure.
  • Database disaster recovery planning.
  • Disk configuration for best I/O performance.

While I do try to guide you through a proper installation this post is not about that!

One final note before we start with the walkthrough:

  • You can’t install SQL Server on a debug checked build of Windows Server 2008 R2. If you do, your installation will fail with a vague exception message “External component has thrown an exception”.

Preparing for installation

During the installation of SQL Server several new Windows services are installed on your system. Which services and how many depend on the features and options you choose during the installation. For each installed database instance the following services can be created:

  • SQL Server
  • SQL Server Agent
  • SQL Server Analysis Service
  • SQL Server Reporting Service
  • SQL Server Integration Service

Each of these services will be running under a account you choose during the installation. Now you could use one of the built-in Windows accounts but I would advise to create a separate account for each of the installed services. Now if you are not following up my advice and want to use one of the built-in accounts, be sure to not use the LOCAL SERVICE account as it’s not supported by the SQL Server and SQL Server Agent service and skip to the next section “Starting the Installation”.

The recommend way would be creating a domain account which makes it possible to access other SQL Server instances and resources in the domain, just by simply granting the necessary permissions. Obviously this isn’t possible when your machine is in a workgroup configuration (then your domain is your machine). My virtual servers are, so I am stuck with creating local user accounts. Because you have to set the accounts during installation it is important that you create them in advance. Now when you don’t now which services you need, you don’t now how many accounts you need to create right? So let’s go trough all of the optional services besides SQL Server, so you could make a decision yourself.

SQL Server

This service doesn’t need very much explanation but it’s the core database engine and is responsible for storing, processing and securing your data in a safe manner. You will need this service before you can use any of the other services. Part of the database engine is the service broker and provides asynchronous, distributed messaging system within the database engine itself. This is useful when you want to benefit from a messaging system inside the database but without going to a outside/external queue like Microsoft Message Queuing (MSMQ) for example. This benefits for example in the performance of processing transactions, better reliability and recoverability and many other things.

SQL Server Agent

This service let’s you schedule automated jobs and gives you the possibility to react to certain events or notifications.

SQL Server Analysis Service (SSAS)

This service provides online analytical processing (OLAP) and data mining functionality for business intelligence (BI) solutions. OLAP systems are used quite differently then online transaction processing (OLTP) systems which is what SQL Server basically is. This is because the emphasis lays most of the time on reading large sets of data which can be analyzed (i.e. patterns, trends, prospects) for making business decisions. This is why they also call it a decision support system. You don’t want to do this kinds of queries on your production OLTP database. That said SSAS allows you to choose more then just a OLTP database as it data source. Last but not least SSAS also comes with PowerPivot for Excel and SharePoint.

SQL Server Reporting Service (SSRS)

This service is a server-based reporting platform with all the functionality you need to create and manage reports from a variety of data sources in various formats. The reports can be designed in Report Designer which is integrated in Visual Studio or with Report Builder a much simpler tool with less functionality.

SQL Server Integration Service (SSIS)

This services provides a high performance Extract-Transform-Load (ETL) platform which you can put to work for a lot of things. For example you can integrate or create workflows, clean and collect data, data transformations and conversions, interact with other services or processes, administrative tasks, sending e-mails and so forth. I say this service is a winner.

Now if you don’t know if you’re going to use a service then don’t install it. This will limits your attack surface and increases the overall performance of your system. If you at a later moment in time change your mind you can always add them.

Now you know how many accounts you need, let’s create them by going to the Server Manager > Configuration > Local Users and Groups (lusrmgr.msc).

 

image

 

Right click on the Users node and create a new user for each service.

 

image

 

image

 

Now do this for every account you need and also create a SQLADMIN account which you will be granting full access to the database server during the installation. This will be asked during the setup initialization and gives that account administrative rights for your database server. I won’t want my Administrator user to have full database access so I create a SQLADMIN user as well.

 

image

 

After your done we could start the setup, but one little thing I need to mention, is that during the installation all the accounts are given the proper permissions they need. We don’t have to do anything special for that. I also create a little batch file which creates the users for me, so I don’t have to do that the next time.

Starting the installation

When the setup is started you will get the following screen and is the starting point for a lot tasks. A lot of them goes behind the scope of this post and therefore will not be discussed. To start our installation we click on the Installation tab.

 

image

 

image

 

Click on New SQL Server stand-alone installation or add features to an existing installation.

 

image

 

The installation does some checks which must be passed before you can continue the setup. Click OK to continue.

 

image

 

The Product Key screen lets you choose to enter a product key for your version of SQL Server or choose the express or evaluation version. I am going to install the Developer Edition so I need to enter the product key. I have got my version from MSDN so this is already filled in for me. Click next when you fill like it.

 

image

 

 

image

 

Accept the terms and click next.

 

image

 

The setup support files are needed for the installation. Click on the Install button. After they are installed the setup does some additional checks, as seen in the screen below.

 

image

 

The setup warns me here that my Windows Firewall is enabled and that we must not forget to open the appropriate ports if we want to enable remote access. So write it down and open the ports in the firewall and the end of the setup. Click Next to continue setup.

 

image

 

We choose the first options because we don’t want to install SQL Server PowerPivot for Sharepoint and we don’t want to install all the features with defaults as well. Click next to to go the feature selection.

 

image

 

In this screen you have to choose which features you want to install but before you do, let’s go through al the available features we haven’t discussed yet. The features are split up in three parts: instance features, shared features and redistributable features. The instance features are installed for every instance of SQL Server. Since SQL Server 2000 it’s possible to install multiple instances of SQL Server on one server. More on this later. The shared features are obviously shared by all instances of SQL Server and some of them are subject for discussion whether you want them installed on your server or not. For example the Business Intelligence Development Studio or Management Tools aren't necessary if you manage and develop solutions on SQL Server remotely. The last one, redistributable features can’t be chosen and are installed when needed. You can click on it to get a more detailed explanation in the description pane.

SQL Server Replication

Replication is used to synchronize or distribute data between databases be it on the same or different server, be it a Oracle, Access, SQL Sever or any other database that supports ODBC or OLE DB.

Full-Text Search

This feature will let you execute full-text queries against character-based data in your SQL Server tables. This is useful for when you need to search through large text fields. This feature adds a additional SQL Full-Text Filter Deamon Launcher Windows service.

Business Intelligence Development Studio (BIDS)

BIDS is used for developing business solutions that integrate with SSAS, SSIS and SSRS. BIDS is build on Visual Studio 2008 and is a fully independent installation. If you already have Visual Studio 2008 installed then you can also create business intelligence solutions using the available project templates. BIDS is no more then Visual Studio 2008 with business intelligence plugins. Visual Studio 2010 does not support the BIDS templates and also will not support it in the future because BIDS will be replaced by SQL Server Data Tools in SQL Server 2012.

Client Tools Connectivity

This feature installs all the components needed for communication between client and server, including network libraries for DB-Library, OLEDB for OLAP, ODBC, ADODB, and ADOMD+.

Client Tools Backwards Compatibility

This feature installs all the components needed for communication between client and server for legacy systems. It includes SQL Distributed Management Objects (SQL-DMO, Decision Support Objects (DSO) and Data Transformation Services (DTS).

Client Tools SDK

There is not a lot of information available on this subject but it contains developer resources of the corresponding client tools. You don’t need it on a production environment.

SQL Server Books Online

The core documentation of SQL Server 2008 R2

Management  Tools – Basic & Complete

The most important tool for managing your databases. The basic version will give you most of the options you need to manage your databases including the SQL Server command line utility and the SQL Server PowerShell provider. The complete version also comes with support for SSRS, SSAS, and SSIS, SQL Server Profiler, Database Tuning Advisor, and SQL Server Utility management.

SQL Client Connectivity SDK

There is not a lot of information available on this subject but it contains developer resources of the corresponding SQL Client Connectivity components. You don’t need it on a production environment.

Microsoft Sync Framework (MSF)

This is the framework you need for building Occasionally Connected Applications (OCA). MSF is a complete framework for syncing data, files, feeds, or other information between clients, peers, and servers over common transport protocols and across networks. For example you could use it for synchronizing local (offline) databases on handhelds with the corporate main database.

Well now it’s up to you to choose which features you want. Also change the shared features directory if you want. I will keep the chosen features and install directory like mentioned in the screenshot and click Next.

 

image

 

If everything is passed you can click next otherwise fix the failed rules and re-run the checks.

 

image

 

This screen will let you choose how to setup your instance of SQL Server and gives you two possibilities default (unnamed instance) or named instances. Each server can only have one default instance but up to 50 named instances. The amount of allowed instances depends on the version of SQL Server you’re installing. If you have already installed some instances before you will see them in the bottom of the screen. Multiple instances provides various benefits like controlling the amount of memory and CPU resources to every instance and maintain different service packs and collations for each instance.

I will choose to install a default instance and change the instance ID to my servername + DB and click next. The instance ID will be used in the installation directory and registry keys.

 

image

 

Click Next.

 

image

 

On the first tab of this screen you will have to set the earlier made accounts on the services you choose during installation. The recommend account for the SQL Full-text Filter deamon Launcher is the local service account and should not be running under any other account. If you only have one SQL Server instance on your server you will never need the SQL Server Browser service. This service is used to provide information about the different SQL Server instances running on your server. Keep it disabled like me if you have only one instance. If you do use multiple instances enable it and create a separate account for it.

 

image

 

The second tab collation, let’s you select a collation of your choice. A collation is used for sorting and comparing textual data for example in indexes or queries. By default the SQL Server setup matches the collation used in the Windows server. Change this if you know what you’re doing otherwise leave it like it is. Click next.

 

image

 

On the first tab you will have to specify the authentication mode and which Windows users you want to add to the SQL Server administration group. Now Windows authentication mode is the most secure option and is chosen by default but isn’t gonna cut it if you want clients to be able to connect which aren’t on a Windows domain. In this case you can use Mixed Mode and you will have to provide a strong password for the system administrator (sa) account. Regardless of the authentication mode you always have to specify a Windows user you want to grant unrestricted access to the database engine. Click on the next tab Data Directories to continue installation.

 

image

 

This tab let’s you specify the directories for the data, log, temp and backup directories. For a production environment it is advisable to install each of this directories on a separate disk. I’ll leave it like it is and go to the next tab FILESTREAM.

 

image

 

FILESTREAM is a new feature in SQL Server 2008 and make it’s possible to store unstructured data (documents, images, videos, binaries in general) directly on the NTFS filesystem. Basicly FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. The advantages over the already known BLOBS are increased performance and it’s get rid of the 2GB size limitation on BLOBS. There are also limitations; database mirroring won’t be possible, snapshots of databases won’t involve the filestream data and filestream data can’t be encrypted.

Now I would like to play with this feature so I am going to install it. If you are unsure, you can enable FILESTREAM data at any time via the SQL Server Configuration Manager. The first checkbox enables the FILESTREAM option and makes it available through TSQL. The second option will make the FILESTREAM data available via Windows as a share. The third and last will make this share available via remote clients which I don’t want. Click next to go to the Reporting Services Configuration.

 

image

 

I choose to use the default configuration values for the reporting service. If you don’t want the default values choose the last option. This way you can run the Reporting Services Configuration tool yourself. Click Next.

 

image

 

Click Next.

 

image

 

If everything is passed you can click next otherwise fix the failed rules and re-run the checks.

 

image

 

This is the last screen before the magic happens. One interesting detail is the configuration file shown below. In this file all your made installation choices are stored so you can re-use it in other installations. Timesaver I would say. You can use this file in a command prompt installation like below:

setup.exe /g /ACTION=Install /Configurationfile=c:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20111227_175316\ConfigurationFile.ini

Click install to start the installation. Depending on the features you choose this could take a while.

 

image

 

image

 

Basic configuration

Now when are done with the installation we have to do some final configuration steps. This involves disabling the protocols we don’t need, allowing remote connections and putting a hole in the firewall.

Open the SQL Server Configuration Manager.

 

image

By default only shared memory is enabled and is used only for local connections to instances on the same machine. I disable it because I want to use TCP/IP for all my connections. Be sure to restart the SQL Server service before your change has effect.

 

Now if you’re using a firewall and you should, we must open it to allow inbound connections on port 1433. My server is using Windows Firewall with Advanced Security (wf.msc).

 

Create a new rule like below.

 

image

 

image

 

Click Next.

 

image

 

Enter the port number. Normally this would be 1433. You can change this in the SQL Server Configuration Manager if you want. Click Next.

 

image

 

Click Next.

 

image

 

Click Next.

 

image

 

Click finish and you’re done. Last things is allowing remote connections. Open SQL Server Management Studio from the server.

 

image

 

image

 

Check “Allow remote connections to this server” and you’re done. Now test it by connecting to SQL Server with SQL Server Management Studio from a other machine or your host OS.

When the SQL Server 2012 release goes public I will try to find some time to make a installation guide for that one as well. If you have any comments, fixes, suggestion please feel free to contact me or leave a comment.

Comments are closed