The installation process for SQL Server 2008 has many different options. These options allow you more control over your SQL Server Instance configuration. Having this additional control allows you to set up your SQL Server 2008 instances out of the box with the appropriate configuration, like directory structure, accounts, etc. In this article I'll walk you through the SQL Server installation process.
Machine Set Up
Prior to installing SQL Server 2008 you need to make sure your SQL Server machine will meet the requirements for SQL Server 2008. There are many different requirements that your SQL Server 2008 machine must meet depending on what version of SQL Server you are installing and the base operating system you are going to use. It is out of the scope of this article to describe all the different machine setups and dependences. Prior to installing SQL Server 2008 you should review the following webpage to identify the correct machine setup for your environment: http://msdn.microsoft.com/en-us/library/ms143506.aspx.
For the purpose of this article I will be installing an evaluation copy of SQL Server 2008 Enterprise Edition. The base operating system I will be installing SQL Server on will be Windows 2003 Enterprise Edition SP1 version. My machine is also configured with a C, D, E, F, and G drives. I will use these different drives to place different components of SQL Server.
Initial Setup Process
When you first stick the SQL Server 2008 CD into your drive it will automatically startup the setup process for SQL Server 2008. If the setup doesn't start then find the "setup.exe" file and run it. When the setup process starts it will check your machine to make sure the .Net Framework and an updated copy of Windows Installer are on your machine. If these are not on your machine then setup will require that these be installed first before you can install SQL Server 2008. The setup process will walk you through installing these prerequisites if they are not already installed. If you have to install them a reboot will be required before you can perform the actual install of SQL server 2008. Once your machine has all the prerequisites installed the setup process will bring up the following window:
On this window you can see you have a number of different hyperlinks you can use. There is a navigation pane on the left that has different SQL Server 2008 items you can pick from and on the right pane are the items associated with the selected left pane item. In my case I have the "Planning" item selected. Note it is displayed in bold font. To install SQL Server 2008 I will need to click on the "Installation" item. When I do that the following screen is displayed:
On this window you can also do a number of different things related to installing SQL Server. For the purpose of this article I will be going through the first item "New SQL Server stand-alone installation or add features to an existing installation" item to create a brand new SQL Server 2008 instance.
Once you click on the "New SQL Server stand-alone installation or add features to an existing installation" item a series processes will run and some dialog boxes will be displayed to allow you to configure SQL Server 2008 the way you would like. In SQL Server 2008 a number of new configuration options are available at installation time, which in prior releases had to be done after you installed SQL Server.
After the stand-alone instance installation item is click the set up process runs through a number of rule check processes to verify that your system is setup correctly. The first one does a system configuration check. After each one of these rule check processes you are given a summarized report telling you whether or not your system passed the rules checked. In that summary report you are given the option to view a detailed report of each rule. I would suggest you look at the detail rules report to verify each rule. If you find a rule that needs to be fixed you will need to resolve the issue and then re-run the rule checks.
The next window displayed is the "Product Key" window. On this window you will need to select the version you want to install and enter the product key. The list of versions displayed will depend on what media you use for the installation. Select the appropriate version and key and then click on the "Next" button. This will bring up the "License Terms" window. On this screen you should read the license terms. If you agree with the licensing terms then click on the accept checkbox followed by the "Next" button to move on with the installation. Next, you will be prompted to install the setup files. Click on the "Install" button to start the setup file installation process. Once all the setup files are installed a summary of the setup installation process will be displayed. If all the critical setup rules where meet then you will be allowed to proceed with the installation, if not you will need to fix the reported problems and re-run the setup installation. Once again this setup process has a link where you can review the details of the setup installation. I recommend you review the details prior to proceeding, so you know exactly the status of each rule installed. Once you are ready to move on with the installation click on the "Next" button. This will bring up the following window:
This window displays the different SQL Server 2008 feature you can install. Here you will click on the appropriate check boxes for the features you want to install for your instance. For the purpose of this article I'm going to only select the "Database Engine Services", "SQL Server Books Online", the "Management Tools - Basic", and the "Management Tools -Complete". Also on this screen you can select the directory where you want to install the shared features. In my case I'm installing my shared features on the "E:\" drive. Once all the features are selected and you have identified the location where you want the shared features to be installed you can then click the "Next" button to proceed. Doing that will bring up the following window:
On this window you specify whether or not you what to install a default instance or a named instance, what you want for an instance ID and the root directory of the instance. For my install I'm just going to take the defaults and install a default instance and identify that instance with an instance id of MSSQLSERVER. But for the instance root directory I'm going to specify the "E:\"as a location. Once you are ready to move forward click on the "Next" button. The installation process will then calculate the amount of disk space required for the features you selected. The process will report the disk space requirements for each drive. Review the disk space requirements. If you realized you need to specify a different location you can use the "Back" button to go back and change installation location. once you are satisfied with the disk space allocations and placement click on the "Next" button. Doing that will bring up the following screen:
On this window you can use the "Server Accounts" tab to specify the accounts that will be used to run the different SQL Server 2008 services and use the "Collation" tab to specify the collation you would like to use. I like to use domain accounts to run my services. In my case I am just going to use the same account for all SQL Server services. So I can either enter that account multiple times next to each service or I can click on the "Use the same account for all SQL Server services" and only enter the account and password once. On this screen you can also identify the status you would like each service to be placed in at startup. Your choices are "Manual", "Automatic" or "Disabled". I prefer that my SQL Server Agent and SQL Server Database engine to start automatically and the SQL Server Browser service to be disabled. To set the collation for your instance use the "Collation" tab. The default collation setting is "SQL_Latin1_General_CP1_CI_AS". I'd suggest you click on the collation tab to verify the correct collation is specified prior to proceeding. Once you are satisfied with the Server Configuration settings click on the "Next" button to proceed with the installation. Doing this will bring up the following window:
On this screen you identify database engine configuration information. There are three different tabs to accomplish this: Account Provisioning, Data Directories, and FILESTREAM. Above you can see the "Account Provisioning" configuration options. Here you can specify whether your instance is to use Windows Authentication only or you want to run in mixed mode to support SQL Server and Windows authenticated logins. If you select "Mixed Mode" then you will need to specify a password for the SA account (no more mistakes in having a blank SA account). Another change is that the "BUILTIN\Administrators" user is no longer added by default, so no more back door for member of the Administrators group. You also now have an option to add users that you want placed in the "sysadmin" built-in server role. I suggest you place at least one user in here. This will allow that user to be able to login to your instance of SQL Server 2008 after the installation is complete. You do that by adding them use the "Add" button in the screen above. You can also use the "Add Current User" button to add the user that is doing the installation to the sysadmin role.
You now have a lot more options for how you want to configure your data directories at installation time, to specify that information click on the "Data Directories" tab on the window above. Upon doing this the following window will be displayed:
As you can see here you can specify different directories for: user database DATA files, user database LOG files, tempdb DATA/LOG files, and the back up directory. This allows you to more easily separate your databases DATA and LOG files across different drives. As well allows you to make sure the system databases and tempdb files are also on different drives. Allowing you to do this now allows you to more optimally place your components at installation time to help minimize I/O bottlenecks. Here is how I configured my directories:
I have placed my user database DATA file on the E drive, where as the user LOG files are on the G drive. I also placed the tempdb DATA and LOG files on the F drive. This provided me some disk separation between components.
If you want to configure FILESTREAM during the setup process you can by using the "FILESTREAM" tab. The information under this tab allows you to specify exactly how you would like to enable and configure FILESTREAM.
Once you are happy with your Database Engine Configuration option click on the "Next" button. Doing this will bring up an "Errors and Usage Reporting" window where you can send errors and feature usage information to Microsoft. Once you have decided how to report to Microsoft there is another "Next" button to click to move on to the next step of the installation process, which is verifying all the Installation Rules. You should check this window to verify that all installation rules are successful for the components you are installing, and then click on the "Next" button. This will bring you to the final installation window. Here you get make a final review of the installation options you have selected. If there is something you want to change you can use the "Back" button to change it. once you are satisfied with the installation configuration you have identified click on the "install" button. this will start the installation process and install the components you selected in the locations you identified. once the installation process is finished the following window will be displayed:
Here you can see all the components I selected have been installed successfully. The install of my SQL Server 2008 instance with the components I selected is now done. When I click on the "Next" button here the final "Close" screen is displayed where I can click on the "Close" button to close the SQL Server 2008 setup process.
Now that I have walked you through the installation process you can see that there are many different options for how might want to configure SQL Server 2008. Hopefully this article has given you a sense of the different options you will need to consider in order to make sure your installation configuration goes smoothly. I suggest you do some planning up front for how best to map out each of the SQL Server components across the different disks drives you have and what account or accounts you want to use for the different SQL Server services. Doing this additional planning will help you be successful in setting up your SQL Server instance configuration the way you want it the first time you install SQL Server 2008.