Here I will run through a basic installation of SQL Server 2008 R2. This does not include Analysis Services or Reporting Services as they are out of the scope of this article. This literally covers the basic install and key settings that need to be considered prior to running SQL Server for the first time.
Also any complex features that are included in this install will not be discussed at length in this document, but in other installation articles.
So… having entered the disc, running setup.exe should present you with the following:
As we are doing a fresh install from scratch, click “New installation or add features to an existing installation” and move on:
SQL Server will run a few checks on your machine to make sure there aren’t any problems that would stop the installation from being able to complete. All being well you should get the green light as above.
The most common problem I have found is that the installer does NOT like Visual Studio unless you have all service packs in place, so if you do run Visual Studio I would advise you update it first to save some hassle.
Click OK and you’ll be asked to install the Setup Support Files. Click “Install”:
Next up we have more checks being done:
As you can see, I’ve got a warning and clicking on it tells me the following:
So as you can see, it literally is a warning and not a failure as it’s simply letting me know that if I want to use certain functionality later on, I’ll need to open a few ports on the firewall. Therefore I’ll ignore this for the moment and move on…
Enter your product key (or select the free 180 day trial), and then click “Next”. On the next screen just click Accept and then Next to move on…
We’re going to do a custom install, therefore on the next screen choose the top option…
This will then present you with a feature list for you to choose from…
Note that on my screen the Shared Features are greyed out as I have these installed already on my machine, for a completely fresh install these should be available to select. The key one that you will need is Management Tools – Complete. As for the main features, we’ll be selecting the top three: Database Engine Services, SQL Server Replication, and Full-Text Search. Then click Next…
After another rule check, hit Next again to get the first Instance Configuration screen…
If you have no other installations of SQL Server on your machine, then it’s easier to select “Default Instance”. This simply sets the name of your SQL Server to reflect that of the machine you’re installing it on. Ie. If your machine is called “Bob”, then you can connect to your SQL Server using Management Studio by entering the name “Bob”.
However, if you have a default instance already installed, or you perhaps want your SQL Server name to be unique, then you can select “Named Instance” and type whatever you like. If, for example, you typed “SQLServer2008R2” then on a machine named “Bob” you would connect to your SQL Server as “Bob\SQLServer2008R2”.
In this article I’ll be using the Default Instance.
Also key to note is that you are given an option to change the Instance Root Directory. This is here SQL Server will create numerous folders in which to store the application binaries along with your system databases.
In my case I don’t want these residing on my C drive as I’m using my desktop which has a relatively small SSD, so I’m going to change this to a folder on my SATA D drive instead.
Please note though, once you have selected a location for your system databases, this is very hard to change (by no means impossible and I will write an article demonstrating this, but it’s not simple).
This is the same window after my settings have been applied:
Click Next and you’ll be shown a small summary of the installation locations:
Click Next again to move on to Service Configuration:
This screen is quite key to the installation of your instance as you are effectively setting the user access under which the SQL services can run. The default options you will be provided with are…
(I tend to use the same account for all services… the window above was accessed by selecting that option).
At this stage I am going to select NT AUTHORITY\SYSTEM because it’s a local admin account and will effectively be a sysadmin on the SQL Server, therefore I know I’ll not have access issues running scripts and code. However, this may be too high an access level to provide on a production server in a work environment and therefore you can use a domain account in order to set your own specific access, or consider the NETWORK SERVICE account. For full details about how these accounts work and their limitations, I would recommend this article from MSDN: http://msdn.microsoft.com/en-us/library/ms191543.aspx
(Note: The NT AUTHORITY accounts do not require a password)
As for the startup type for your services, this is up to you on how you with SQL Server to behave. On a dedicated SQL Server machine I would always have these as Automatic. As this is my desktop, I’m going to limit them to Manual:
You can also change the collation of the server by using this Collation tab. This will provide you with a vast array of collation options so that you can tailor your SQL Server to use the characters of your chosen language and also whether you would like the server to be Case Sensitive or Accent Sensitive…
Once chosen, click Next and you’ll move on to the Database Engine Configuration:
With regard to Account Provisioning there are 2 options… Windows Authentication Mode and Mixed Mode.
In Windows Authentication Mode you can ONLY connect to SQL Server using a Windows account. This can be local or domain, but it must be Windows.
With Mixed Mode (the most commonly used option) this allows you to use Windows accounts AND SQL created users. This provides more flexibility around user and application access along with better control over application security. Selecting this also creates a system administrator account (sa) and allows you to enter a password for it.
This means that you can always get into your SQL Server as a sysadmin even if something happened and windows accounts were accidentally disabled.
You can also add your current Windows user into the sysadmin group by clicking “Add Current User” so that you can more easily set up the server post install without relying on the sa account.
Next we move on to Data Directories:
As default SQL Server will use the Root Directory we gave it and create a selection of folders within in order to hold all our database files…
However, in an ideal world we would not want things set up in this fashion. If space and disks allowed, we would wish to have User databases on one physical drive, logs on another, tempDB on a third, and backups on a fourth. (More can be used, this is just an example).
I don’t have these available on my desktop, but instead I’ve created folders for each scenario just to demonstrate the splitting of the files:
Next we have “FILESTREAM”…
This feature is best described by MSDN here: http://msdn.microsoft.com/en-us/library/bb933993(v=sql.105).aspx
It basically allows SQL Server to store large object data in the NTFS file system rather than in the database itself, therefore providing a faster and more efficient means of accessing large object data. As such I am going to turn this feature on (mainly because I intend to cover this topic at a later date and will require it enabled), but I would also advise that you do the same as it is a feature that you may find very useful in time.
Click Next and then tick the checkbox if you wish…
Clicking Next again does one final checklist…
Next again will give you a list of everything you’ve selected so that you can review it and make sure nothing’s missing…
Lastly, hit Install and sit back and wait…
That’s it. We’re done.