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.


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.


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


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.


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.


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


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


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.


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.


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.


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.


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


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).


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.


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).


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.


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.


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


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.


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

Measure and Monitoring Disk Performance

Performance on a SQL server much down to how the server handles disk I/O operations. Many enterprise SQL servers are using large SAN instead of local storage systems. When the price development in high-speed storage cards might reduce the concern for poor disk performance. Large datacenters are using these high performance storage cards when systems needs the best IO performance possible. There exists essentially four main storage types, and these are either traditional magnetic drives or SSD disks:

  • Internal drives
  • PCI-E: high speed storage cards
  • DAS: Direct-Attached Storage
  • SAN: Storage Area Networks

How can you measure disk performance? Has the vendor or IT-department given you disks with poor, good or excellent performance? These are questions database administrators have to ask when they receive hardware for a new server. When you a specifying new hardware, don’t just ask for storage based on space requirements. Ask the vendor or IT-department for performance requirements.  Performance parameters for different database types are often measured in the following:

  • Sequential performance in MB/second or GB/second
  • Random performance in input/output operations per second (IOPS)

Sequential performance is very important for data warehouses (DWH) database where large sequential reads are performed on the data files. Random I/O performance very important for databases for Online Transaction Processing (OLTP) where frequent writes to data files and log file. The also applies for Online Analytical Processing (OLAP) where many random reads from cube files.

  • CrystalDiskMark. Is used to measure sequential and randon read/write throughput.
  • SQLIO. Same as CrystalDiskMark, but this is a command line Microsoft tool that is used to determine the I/O capacity of a given configuration. You are able to simulate different block size of the IO request, duration of tests and number of outstanding requrests.

Have have done some testing locally on my laptop where I have a SSD as Disk 0. Here is a screenshot of the result from my SQLIO test. So the performance should be pretty good.