Installing SQL Server 2008 R2


Since I have been writing blog on how to install previous version of SQL Server –  SQL-2000, SQL-2005, and partly SQL Server 2012, I decided to complete the series with installing SQL Server 2008 (R2). So, why do I write these posts? The first two posts I wrote just for the sake of nostalgia and fun. I thought it was nice to have a reference to older version and screenshot on how the SQL Server was done in the past. It will probably be nice to look back on in few years time.

Installing SQL Server does not differ much between versions. Off course, there are new features but there are always a set off pre-installation tasks you have to go through and consider before the installation can begin. In addition, there are a set of post-installation tasks. Brent Ozar has some good posts on pre-install tasks and post-install tasks for SQL Server. Jonathan Kehayias also has a very good and compact post SQL Server installation checklist.

When you have completed you reinstallation tasks, such as preparing disks, creating service accounts and so on, you will fire up the setup.exe from the installation media. In my case, I did the installation on a local VM instance, not on the network. As you might see from the screenshot below, I have created disks for OS, Data, Log, Tempdb and Backup, just our company standard.

SQL2008_install1

The startup of setup.exe might take some time, depending on the installation box, but when appears, click on the installation meny and choose “New Installation or add feature…” entry.

SQL2008_install2

The next step of the installation process will check if your box is ready to be installed.

SQL2008_install4

The next screen enables you to select the SQL Server edition you shall install. You can select an “Evalutation” licence, and you have 180 days to evaluate the installation. The product key is the second option. This might be default, depending on where you have downloaded the installation media.

SQL2008_install5

You have to accept the licence terms for SQL Server on the next screen. You are also enble to help Microsoft to collect information about what features that you use on the server. This will help them to prioritize most used features in future version, and decide what functionallity that should be depricated.

SQL2008_install6

The next step in the installation process is to copy support files to the local drive.

SQL2008_install7

Together with the support files, some setup rules are checked to see if the computer and OS is ready.

SQL2008_install8

The next step is to choose type of installation. The three choices is a full installation (the bottomn one) with all default values. If you chose this option, you are ending up with lots of post-installation tasks that could be easily configured during the installation. You also have the option to install PowerPivot for Sharepoint if you are managing a Sharepoint BI solution. The first option is the most feasable where you can choose whatever feature you want.

SQL2008_install9

The next step in the installation process is to select the necessary feature to serve clients with the functionally needed for their applications. I have selected a set of features that is common for a developer database. The database engine is almost required, but for my use, I don’t need Analysing and Reporting Services. In addition, I don’t need the Sync framework.

SQL2008_install10

As you might see above, the “Shared feature directories” is located at the C: drive. There are lot of options on wheather these files should be installed on the C: drive or another SQL-Bin drive. I like to set these to the C: drive and have never had any issues with this setup.

The screenshot below show the validation installation rules that is passed. If no errors occurs, the setup installation process will continue.

SQL2008_install11

The next step is the screen where you chose the instance where the database engine shall be installed. If you don’t have any good argument to choose named instance, you should choose the default instanse. A production server should be installed on the default instance. If you have other enviorment, you could an instance DB_TEST and/or DB_DEV. Security is another argument to should two or more instances.

SQL2008_install12

The next informational screen should the distribution for the different drives where the system files and shared fetaures.

SQL2008_install13

Service accounts are important for SQL Server, and most of the SQL Services should be assigned to a service account. This apply for at least db engine, Agent, Integration, Analysing and Reporting Services. Service accounts are normal domain users accounts. The service account for have some policy setting such as “Perform maintenance task” that should be assigned, but all other service accounts are pretty much only normal domain users.

I normally use this namning convension for a server named SQL-01, if customers don’t have other preferences:

  • Database engine = Svc-SqlDB-P-SQL01
  • Agent = Svc-SqlAG-SQL01
  • Integration Service = Svc-SqlIS-P-SQL01
  • Analysing Service = Svc-SqlAS-P-SQL01
  • Reporting Service = Svc-SqlRS-P-SQL01

If there are mulitple enviroment, I normally prefix the server name with P (Production), T (Test), Q (QA), D (Development).

SQL2008_install15

The next step of the installation process enable you decide how user shall authenticate against you server. SQL Server allow both Windows and SQL Server Authentication. When you choose both, it is called “Mixed Mode”, and you have to create a SA account (System Administrator) with a known passord to the DBA for the particular server. You have to write down the password and store it a safe place. I recommend KeePass where you can organize all you usernames and password for your servers in a safe way. I also recommend to have a long and complex master password.

SQL2008_install16

It is normal to add either one person or an AD group as SQL administrators, in addition to the SA account. If you create an AD group SQLAdmins, you can add this group during the installation, and just add new persons in this AD group when you get a new SQL DBA for your servers.

The next screen is maybe the one where beginner do most errors. Been there, done that. The first directory (1) is where all the SQL Server binaries shall be installed. This included the system databases, such as master, model and msdb. The tempdb system database should be installed a dedicated drive (LUN), shown in directory (4). The data files for all user databases should also be located on a dedicated disk, shown in directory (2). The same goes for the Log files for the user databases (3).

SQL2008_install17

The location of the backup files is widely discussed (5). Some mean that the backup should be located on a dedicated disk locally on the server for performance, and be pulled by the enterprise backup software to be stored in a secure location. Other prefers to store the backup files directly on a network share within the same SAN system. Others again stores the backups on a different SAN system is case the SAN system collapse. But backup and restore strategies are a huge topic, and I normally don’t consider this for the customers if they don’t mention when I deliver my requirements for the SQL Server.

The feature FILESTREAM must be enbled if you want to use it from T-SQL. This feature is enhanced with the FileTable in SQL Server 2012, and will not be covered in this blog post.

SQL2008_install18

The final screen before the actual installation start is show below. It shows all the features that you have selected and configured. At this point, SQL Server setup program has created a configuration file with all these settings. The path is shown in the red recangle. This configuration file can be used to create an automatic installation script for unattended install for that can be used for all you SQL Server 2008 R2 servers.

SQL2008_install20

After you have pressed “Install” button in the previous screen, the installation progress is shown as the screenshot below.

SQL2008_install21

When the installation has compled successfully the following screen is shown. The full installation process can be found in the file path shown in the red rectangle.

SQL2008_install22

Now, you have a fully installed server, and the post-installation tasks has to be completed before the server is usable for others. See the post from Ozar post-install tasks and Kehayias checklist for more information.

I normally complete the following tasks:

  • Set “Max Memory” to approx 70-80% rule. Here is the numbers I use for server with total memory:
    •   4GB total =>  2048 MB
    •   8GB total =>  4096 MB
    •  16GB total => 11264 MB
    •  32GB total => 25600 MB
    •  64GB total => 54272 MB
  • Make sure port 1433 is open in the firewall and TCP protocol is enabled.
  • Make sure the Browser and Agent services are enabled, running and configured to Automatic
  • Verify the firewall setting for the sqlservr.exe
  • Verify “AutoClose = false”
  • Verify “AutoShrink = false”
  • If customer doesn’t have any backup strategy, I normally install and configure the maintenance script til Ola Hallengren. This is widely recongnized in the SQL community. I also create addidional Agent Schedules for “midnight”, “evening” and “morning” to make is easier to add jobs for these times of day.
    • Configure daily backup of system databases
    • Configure daily full backup of all user databases
    • Configure weekly (sunday) index/statistics mainteance and standard cleanup tasks
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s