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

List db size on remote server with powershell

Today, I wanted to create a powershell script to list all databases on a remote SQL Server and their total size in MB. I haven’t made the script as robust as needed, but this should be a nice first version. The script will connect to the server given in parameter $server. I succesfully connected, the script will calculate total size of all databases, and print a header, before it lists all database with it’s size in MB.

Param ($Server = "localhost")

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') `
		-argumentlist $Server

$dataTotal = $SMOserver.Databases | Measure-Object -property Size -sum

write-host "********************************************"
Write-host "Connected to server '$server'"
write-host "  DB Count    = " $dataTotal.count
write-host "  Total size  = " $dataTotal.sum.ToString("#,##0") "MB"
write-host "********************************************"
$x2 = @{label="Size (MB)";Expression={$_.Size};FormatString="#,###,##0"}

$SMOserver.Databases `
	| select name, size `
	| sort-object Size -descending `
	| Format-Table Name, $x2 -auto

Get Powershell version

Powershell is becoming more and more important tool when preparing servers. Powershell is very powerfull entry point to Microsoft Products. “Windows 8” and “Windows Server 2012” comes with Powershell 3.0 installed with all features, but older versions of these operating system comes with Powershell 2.0.

To get the currently installed Powershell do the following:

  1. Open Powershell command line
  2. Type “$PSVersionTable.PSVersion” or “Get-Host”

Result for $PSVersionTable.PSVersion:


Result for “Get-Host”:


If you want to install PowerShell 3.0, following the instruction on TechNet.

Microsoft Support Lifecycle

When you are doing migration and upgrade projects where Micosoft technology is used it is necessary to have knowledge about the Microsoft Support Lifecycle. This means identifying the server and software state regarding when the installations go into “Mainstream” and “Extended” support. In my case, most of the servers run Windows Server and SQL Server.

Windows Server Released Mainstream End Date Extended End Date NOV-2013 JULY-2016 JULY-2019
Windows Server 2000 31.03.2000 30.06.2005 13.07.2010 Unsupported Unsupported Unsupported
Windows Server 2003 Standard  28.05.2003 13.07.2010 14.07.2015 Extended Unsupported Unsupported
Windows Server 2008 R2 Standard 22.10.2009 13.01.2015 14.01.2020 OK Extended Extended
Windows Server 2012 Standard 30.10.2012 09.01.2018 10.01.2023 OK OK Extended
SQL Server Released Mainstream End Date Extended End Date NOV-2013 JULY-2016 JULY-2019
SQL Server 2000 30.11.2000 08.04.2008 09.04.2013 Unsupported Unsupported Unsupported
SQL Server 2005 14.01.2006 12.04.2011 12.04.2016 Extended Unsupported Unsupported
SQL Server 2008 R2 20.07.2010 08.07.2014 09.07.2019 OK Extended Unsupported
SQL Server 2012 20.05.2012 11.07.2017 12.07.2022 OK OK Extended
Dynamic CRM Released Mainstream End Date Extended End Date NOV-2013 JULY-2016 JULY-2019
CRM 3 01.12.2005 12.04.2011 12.04.2016 Extended Unsupported Unsupported
CRM 4 29.02.2008 09.04.2013 10.04.2018 Extended Extended Unsupported
CRM 2011 18.05.2011 12.07.2016 13.07.2021 OK Extended Extended
CRM 2013 12.01.2014 08.01.2019 09.01.2024 OK OK Extended
Sharepoint Server Released Mainstream End Date Extended End Date NOV-2013 JULY-2016 JULY-2019
Sharepoint 2007 27.01.2007 09.10.2012 10.10.2017 Extended Extended Unsupported
Sharepoint 2010 15.07.2010 13.10.2015 13.10.2020 OK Extended Extended
Sharepoint 2013 09.01.2013 10.04.2018 11.04.2023 OK OK Extended

As you might notice, Windows Server 2000 and SQL Server 2000 is now unsupported by Microsoft. This means that if you have production systems running on these versions today, you are relying on your own backup to work if something goes completly wrong and you are not able to recover to a normal state for your servers. In most cases, you will need to rebuild the systems with a newer version and convert data.

Installing SQL Server 2005

It seems like this is the weekend for SQL Server nostalgia. My current project will handle 18 applications based on SQL Server 2005 x86, and I need to dig deep into my memory to find out when I last worked with this version of SQL Server. It must have been in 2006-07.

Lifecycle and server builds

Lifecycle SQL Server 2005 Standard/Enterprise:

  • Released: 14-JAN-2006
  • Mainstream: 12-APR-2011
  • Extended:   12-APR-2016

The latest service packs for SQL Server 2005 is found here.

When I see so many database on old SQL Server version, I become pretty sad and amazed. The database technology has evolved a lot the last few years and with changed licensing and virtualization, customer would most likely save money by upgrading their systems. Paul Randal at is one of the leading “SQL Master” certified professionals in the SQL Server Community. Earlier this year he had a survey on which server build number people had on their production server. He received information from 3085 servers where people sent the result from the T-SQL command “SELECT @@version” for their production SQL Servers. The result is presented in this post.

RESULT: 51% of all instances in the survey are out of date, by at least not being on the latest SP build, and 53% of all instances in the survey are unsupported.


The installation for SQL Server 2005 is pretty similar as other version, and when I look at it is nice to have all the screenshots in a blog post as I have done for SQL Server 2000 as well.


The first step of the installation is to install pre-requirements for the installation. This includes the native client drivers and setup files. This step has to complte before the installation continues.


The next screen shows a successfull configuration and SQL Server is ready to be installed. There is one warning on the IIS configuration, but this is just a minor warning and this can be configured as a post-installation task.



The next screen shows the feature selection for the components that you like to install for your SQL Server. Select all the components that you need and press “Next”.


The next screen enables you to set up the service accounts for the SQL Server services, such as db engine, agent, analysing services and SQL browser. Normally, you create the service account as a pre-installation task, but for this example, I just use the built-in system account, but this can be configured later asa a post-

installation task.


The next screen let you decide whether to use windows authentication or mixed mode authentication (windows and sql). This is equal for all version, and if you choose “Mixed mode”, you have to set the SA password.


The next screen let you set the desired collation for the server. The server collation will have effect on the sorting, how strings and numbers are shown in the client and stored in the database.


Nothing real important in the next screen other if you want automatic error reporting to Microsoft, that helps them creating more stable service packs and cummulative updated. In addition, you can report feature usage. This will help Microsoft in their decission process on which features to improve and enhance.


The next screen is just a final step before the installation starts. It is a summary page of what will be installed.


The installation progress will be reported as shown in the screenshot below.


When the installation is completed a screen like the one below. It will show a log file and suggestion on how to continue configuration. In the screenshot below suggestion on analysing services and reporting services.


Service packs

The Service Pack 4 is the latest one for SQL Server 2005.


Before the upgrade start, the service pack installation wizard will show what components that will be updated.


The installation progress will be show and set a status for each components. The screenshot below shows that the upgrade for “SQL Server Native Client” require a reboot of the server.


The installation will sjow a status when it is completed.


Working with SQL Server 2000 :)

Another nostalgic post with SQL Server 2000. See my previous post on “Installing SQL Server 2000“.

I haven’t worked with SQL Server 2000 since I started working for EVRY back in 2010. Even then, collegues had a good laugh and taunted me for using old school development tools. I didn’t care because SQL Server 2000 was the tool the customer used for their front-end system, and it worked fine. But today SQL Server 2000 is not supported by Microsoft anymore, and I recommend customer that use this antiquity database to plan for an upgrade in the near future. The support ended April 9th 2013.

Enterprise Manager

When I started the current project at work and logged in to the first server with SQL Server 2000, I had to think real hard to find the tools. But after a quick look at the menues, I recognized the two most common tools – enterprise manager and query analyser. Phuuu…

The screenshot below shows the Enterprise Manager (EM). This is a tool where you have a (nice) GUI for creating database and navigating through the databases on the server and do some simple tasks. The EM is not the best tool to work, and most of the work is done in the Query Analyser, descibed in the next section.


But the EM is simple to use and you create a database, user and grant/deny permission real easy while drinking coffie from your DBA cup 🙂

Creating a database

The first task is to create a new database. This example just include a simple database called ‘RoarTest’. As all other SQL Server versions, you can change the location of the data and log files. The screenshot below show the General tab where you name the database and set the collation. Use the server collation as much as possible.


The second tab – “Data Files” – enables you to select filename, location, initial filesize and which file group the data files should be placed. This is pretty much equal to the other SQL Server versions.


The next tab – “Transaction Log” – enables you to select the filename, location and initial size of the transaction log file.


Query Analyser

It is the Query Analyse (QA) where most of the work is done. This tool is the predecessor to Managment Studio (SSMS). You have the database in the left pane together with templates for different operations that you use on regular basis.


Installing SQL Server 2000

A bit of nostalgia today. I decided to install a VMware instance with Windows Server 2003 x86 and SQL Server 2000 x86. I will use it to prepare script for my co-workers in the new project where we shall migrate work in a project where 6 system are running on this configuration. It’s over 4 year since I worked with SQL Server 2000 on regular basis, and I need a servere where I can try out thing on my own. This VM instance can also be used my collegues for testing legacy systems.

Microsoft support and liftcycle

Microsoft does not support SQL Server 2000 anymore. The “Extended Support” expired April 9th 2013. Windows Server 2003 is also in “Extended Support” and will expire July 14th 2015. Here is a summary of the lifecycle for Windows Server 2003 and SQL Server 2000.

Lifecycle SQL Server 2000:

  • Mainstream Support: 8-APR-2008
  • Extended Support:   9-APR-2013

Lifecycle Windows Server 2003:

  • Mainstream Support: 13-JUL-2010
  • Extended Support:   14-JUL-2015

Migration strategies

It is not decided what SQL Server version we shall use in the target enviroment, but there are several options. The first option is to migrate the server “as-is”. This mean create a new server instance in the target enviroment with Windows Server 2003 and SQL Server 2000. This option involve less risk for the project, and the customer can use current licences for OS and DB. But the customer will have to upgrade the system in the near future if they want support from Microsoft if something happens.

A second option, is to install a new server with a newer version of Windows Server and and SQL Server and run the database in “Compability Level”. The different versions of SQL server supports 3 version back. This can be archieved by using the “ALTER DATABASE Compatibility Level” command. This options work pretty well, and I have used it for several systems where the customer does not want to take the risk of fully upgrading the application.

The third option is to perform a full upgrade of the server architecture and fully convert the application to a newer SQL Server version and take advantage of new fatures. This often require more time to complete, but the end-users and database administrators will most likely get a better application experience in their daily work.


SQL Server is a very I/O intensive application. As a result we should seperate the data and log files for the SQL server. I haven’t described any pre-installation tasks as I did for the “SQL Server 2012 installation” post which included tasks such as definition of service account, measuring disk performance and serveral other tasks that is important when you install SQL Server 2012.

The first “real” screen let you choose on which server the installation should be performed. I have never installed SQL Server 2000 with other than “Local Server”, and have no intention to try out the other two options 🙂


The second screen let you create a new database instance, upgrade an extisting or advanced installation with clustering. I have chosen a standard new default installation for this case.


The next screen is just a where you put your name or department together with the company name that owns the licence to the SQL Server.


The next screen let you choose how detailed the installation will be.. You can choose to only install client tools that let you connect to a SQL Server 2000 server instance through a common GUI interface such as Enterprise Manager and Query Analyser. The second choice is the full  installation. The third choice is just drivers and APIs/SDKs for connection to a SQL Server through other development tools such as Visual Studio.


The following screen let you decide whether to install on the default instance or to create a new named instance. The most common option is to choose the default instance. But an example, is to create two instances on the same server – one for test and one for development.


The next few screens shows the different “Size” options – typical, Minimum and Custom, all with different disk space requirement.




If you select the custom installation, the next screen let you choose which components you want to install, including extra components as documentation, examples and development tools.


The next screen let you define service account for the different components on SQL Server 2000. It is normal to create one account for each service, but it is possible to use the local system account for installation purpose, and define the service accounts when configuring the server properties in the post-install tasks. This is the case for this post, but the service accounts are defined in similar manner as for SQL Server 2012.


The next screen let you define how clients should be able to connect to the SQL Server, which supports 2 modes – Windows Authentication and Mixed Mode. This work equally as the other versions (for example SQL Server 2012). When you choose Mixed Mode, you will define a password for the sa system administrator accounts.


The next screen defines the server collation that determine the character set that is commonly used on the server and how string should be sorted and how different datatypes should be displayed to the clients.


The next screen defined the licence mode for the SQL Server. It is possible to choose “Per Seat” (client) or “Processor Licence” (Pr. CPU/Core).


When the installation is completed, you will have to install some service packs since the SQL Server is pretty old. In my case, the SP2 was included on the installation media, and I have to install the SP3 and SP4 manually. Here is a link for latest service packs for SQL Server 2000.



Finally done…just some configuration, setting up backup and maintenance tasks, and I’m ready for my last crusade with SQL Server 2000. This will probably last for a while, but hopefully, the customer will see the need for an instant upgrade project when the current migration project is completed.