Step-by-Step procedure for installing a new instance of Microsoft SQL Server 2008 R2 using the SQL Server setup installation wizard.
Here I am going to walk you thru the installation processes of SQL Server 2008 R2 on a Windows Server 2008 R2 Data center edition from a scratch.
- Download SQL Server 2008 R2 Microsoft site or else you can also use you CD / DVD.
- In case you are downloading from Microsoft, you will end up downloading following 1 file for installing SQL Server 2008 R2
- Extract the binary files
- This will extract the following files in the specified folder and start the Setup.exe file
- Once you being the setup it’s going to check first weather Microsoft .NET Framework and Windows Installer is installed or not? if it’s not present, set up will prompt you to install these components.
The moment you click OK, SQL Server Installer will automatically download and installed these components provided you have the Internet connectivity. This may take few minutes (2 minutes in my case) depends on internet connectivity. Restart is not mandatory but it’s recommended to restart as .NET framework and windows installed in just being installed.
If these components are already installed on your system then you will immediately get the SQL Server Installation Center window, which is next screen
- Once the perquisite are met, you will get SQL Server Installation Center to begin the SQL Server installation.
- Click the Installation option, which is a second option on the left hand side
- As I am going to install this SQL Server Instance as a Stand alone Instance, I will opt for “New Installation or add features to an existing Installation”. In case you are installing on a cluster then opt for “NEW SQL Server Failover cluster Installation”, which I am going to cover later.
- This will perform the Pre installation check like
- Operating system Version
- WMI services status
- SQL Server Product compatibility etc.
- Net Screen to opt for Edition and Product Key. In my case, I am installing a Evaluation edition, which is valid for 180 days only.
- License Terms, which is standard for most of Microsoft Products and which is mandatory to accept. :(
- Now SQL Server Setup will install, some files which are required to run the set up, Setup Support Files.
- Now Setup is going to perform another level of Support Rules Validation, where set will check and verify that
- ATL is installed
- This is compatible version of SQL Server Server on this operating systems
- Performance counters are OK as SQL server setup will also add SQL server related counters
- Windows Power Shell, which is now compulsory component.
- Juts for Note- SQL Server can not be installed on Domain Controller system
- On a next screen, Setup will prompt for “SQL Server Feature installation option to individually select which features components to install”. Here you can also opt for “All Features with Defaults”, where all SQL server components will be installed.
- In my case, I have opted for “SQL Server Feature Installation” option in last screen so now I get an option to select what all feature do I want to install and on which location / drive you wanted to install these features.
- Server Services
- Database Engine – required for SQL Server OLTP / OLAP databases
- Full Text – to enable full text search on SQL Server, which enable to issue full-text queries against character-based data
- Analysis Services – Hosting Cube, Dimensions and Data mining Model mainly for BI
- Reporting Services – Host Reports / .dl files, this will automatically install httpd service
- Integration Services – Support to run SSIS / DTS packages this is ETL service
- BIDS (Business Intelligence Development Studio) - a development visual studio integrated tool for developer for developing BI solutions like cube, reports, data mining solutions etc.
- Client Tools Connectivity – Compulsory component, as this provides connectivity mechanism
- Client Tools for Backward Compatibility – Compulsory component, as this provides connectivity mechanism to legacy SQL Servers like SQL Server 7.0 and 2000
- Client Tools SDK - installs the files needed to develop a SQL Server Native Client application
- SQL Server Books Online - SQL Server help files
- Management Tools – Basic - Includes Management Studio support for the Database Engine and SQL Server Express, SQL Server command-line utility (SQLCMD), and the SQL Server PowerShell provider.
- Management Tools Complete - Adds the following components to the basic management tools installation: Management Studio support for Reporting Services, Analysis Services, and Integration Services technologies, SQL Server Profiler, Database Tuning Advisor, and SQL Server Utility management.
- SQL Client Connectivity SDK- SQL Server Connectivity Software development Kit
- Microsoft Sync Framework – Includes Microsoft Sync Framework, a comprehensive synchronization platform that enables collaboration and offline for applications, services and devices.
- Server Services
- Setup validation to ensure installation process itself in not blocked by any other process.
- Now SSIW (SQL Server Installation Wizard) will ask, wheatear you we want to install this new SQL Server instance as a default instance or as a named instance.
We can have only one default instance on windows machine and can have upto 16 instance as per Microsoft Support team. But Microsoft didn’t build in a hard limit for the maximum number of named instances you can install, so technically no limit exists. However, the SQL Server developers have tested the product with up to only 10 instances on one machine and have decided that 16 instances is the maximum that Microsoft will officially support.
But in this case, I am installing a default instance only.
- Now SSIW will check the disk space requirement and verify that required disk space is available to system / drive.
- As SQL Server is server product it runs as service in the background. So now we need to specify, which account / login will be used to start the SQL Services and will SQL Services will be start automatically the moment windows get restart or someone need to start SQL Services manually.
- Now we are going to specify
- SQL Server Security/Authentication Mechanism – specify Authentication mode like only windows users will be allowed or we can have sql users too with windows users (Mixed Mode)
- on the second half of the screen, we are going to specify which windows user will have the full SA privileges, we have an option to “Add Current USER”, which will add current windows user as sys admin privileges, this is recommended setting. Later on we can add other users too in case required
- Data Directories – On the second tab od same screen, you have option to specify where to create Database data files, log files for system databases and default data and log file location user databases and we can also specify default backup directory too.
- File Stream – File stream is a new feature which is introduced in SQL Server 2008 onwards, which enables us to store image / video directly inside SQL Server. We can enable this option even later on too.
- If we have opted to install SQL Server Analysis Services too then now SSIW will ask us which windows user / login will have the admin privileges on SQL Server Analysis Services. It is recommended to add current user this time and later on we can modify the right. Analysis Services administrators have unrestricted access to Analysis Services.
- SQL Server Reporting Services Installation / Configuration Mode. We have option to select any mode out of following three modes.
- Native mode – This will install the report server and configure it in native mode with default values. This is recommended option if your are not using Share point in your organization. This will have its own hosting / storage location.
- SharePoint Integrated mode – In this mode, Reporting services will be integrated with SharePoint and reports will be accessible via SharePoint site only. If you are installing in SharePoint integrated mode, you must have contact Share Point administrator to know share point server details.
- Install but do not configure – If you are unsure, use this option, which will install the Reporting Services but will not be configured.
- Do not check this option, as this will be sending information to Microsoft for issues / errors. this is my personal view, you might want to send errors to Microsoft.
- Final Setup validation to ensure installation process itself in not blocked by any other process.
- Summary report for all options which you specified till now, this is review page of all settings.
All these setting are also saved in the configuration file too, location of this configuration file is mentioned in the last. This fil can also be used for un attended installation too.
- Setup is now copying files and registering components, which will take few minutes (18 minutes in my case). During installation, the Installation Progress page provides status so that you can monitor installation progress as Setup continues.
- Setup will be finished.
- Once the Setup is Finished, Don’t forget to check and verify SQL Server Installation Logs
- Enable the TCPIP settings and try to connect to SQL Server using SQL Server management Studio to reinsure, that all installed components are working.