Skip to main content
Our Tech Ideas

SQL Server installation

Installation

Whenever we want to start the installation of the SQL server we need to gather details of installation.

SQL Server Versions

Version (Name)RTMSP1SP2SP3SP4
Sql server 2014 (Heteko)Trail version released
Sql server 2012 (Denali)11.002100.6011.00.3000   
Sql server 2008 R2 (Kilimanjaro)10.50.160010.50.250010.50.4000  
Sql server 2008 (Katmai)1010.00.253110.00.400010.00.5500 
Sql server 2005 (Yukon)9.00.1399.069.00.20479.00.30429.00.40359.00.5000
Sql server 2000(Shiloh)8.00.1948.00.3848.00.5328.00.7608.00.2039
Sql Server 7.0 (Sphinx)7.00.6237.00.6997.00.8427.00.9617.00.1063

SQL Server Editions

Editions available in SQL server

  • Free editions
  • Special Editions
  • Core Editions

Express and evaluation edition falls under free editions one is free of coot and evaluation expires after 180 days no need to buy licenses from Microsoft.

SQL Server Special Editions: Special editions are web edition for web developers, developer edition for designing stored proc and exclusive for developing purpose.

Workgroup edition for small scale industries: We do not use free and special editions in production servers we use mainly enterprise edition.

SQL Server Core Production Editions

  1. Standard Edition: OS maximum – No limitation from the SQL server side depends on what operating system supports. Advanced features like online registration, encryption not available
  2. Enterprise Edition: King of all SQL Server editions, No limitation from SQL server. Cost approximately 3 times more than standard. Mostly used edition in production environments.
  3. Business Intelligence Edition: Business Intelligence is a new edition released in SQL server 2012 by Microsoft. This edition consists of all standard features along with additional reporting features for Business Intelligence (BI).

Windows Operating System Editions

  1. Server Edition
  2. Advanced Edition
  3. Enterprise Edition
  4. Data Center

Windows admin will perform this installation and provides the server to SQL DBA to perform SQL installation Microsoft recommends to get complete enhanced features of SQL server use the same windows operating system.

Example: SQL server 2012 provides the best performance with windows server 2012. Most SQL server components integrated with windows operating only.

Components to be Selected

SQL Server mainly depends on services.

  1. Database Services – under this service database engine will run.
  2. Integration Services (SSIS) – From multiple sources ETL (Extract Transform Load) will integrate data.
  3. Reporting Services (SSRS) – To generate reports very quickly
  4. Analysis Services (SSAS) – For notification purpose

Instance

The instance is the name of the server. It is a logical SQL server. For the first time, if we execute SQL server setup, the installation uses the windows registered name that we called default Instance.

Named instances, if we run SQL server setup again a new SQL server will be created to work. Each and every time it creates multiple instances. Only one default instance will be there on the server. Named instance depends on SQL server. In SQL Server 2012 it supports instances.

Whenever we install SQL server it will create new binary files or each installation shared components like management studio, business intelligence studio and other features will install only once.

Binary File Location

By default, SQL Server installation creates a set of files in c:\program files\microsoft\sql server. If we want to change the path we can keep them on E (or) G drive.

Collation Settings

SQL Server by default uses character set and sort order common keys on the keyboard that is going to support by default it uses.

SQL Server Authentication

The process of validating login account is authentication. We have 2 types,

  1. Windows
  2. Mixed mode

Windows Authentication

It allows only windows logins which are stored in active directory. SQL Server uses the same account for connecting mixed-mode authentication.

Mixed mode Authentication

It allows window login and SQL server login. Whenever login tries to connect first it validates in the active directory if not found them comes to system logins for validating if found establishes the connection.

SQL Server Service Accounts

Services used for better control on sql server one program may have multiple services. Enter sql server run using these services. We have,

  1. SQL server service
  2. SQL agent service
  3. SQL browser service
  4. Full-text service
  5. MSDTC service

SQL Server Service

If this service is running. SQL Server will run most important service. All other 4 services depend on this service. DB engine run under influence of sql server service.

SQL Agent Service

Entire automation process run under this service. agent service will look at scheduled time of jobs and invoke tasks automatically.

SQL Browser Service

In the Production service, this service will be disabled due to security reasons.

Full Text Service

We keep most repeated words in the catalog for faster search of data from the database

MSDTC

Microsoft distribution transaction coordinator is useful for handling distributed transactions from other servers.

Installing SQL SERVER 2012

Before installing the SQL server, the server needs the following prerequisites.

Prerequisites:

  1. Net framework 4.0
  2. Windows Powershell 2.0
  3. .Net 3.5 with service pack 1

SQL Server 2012 Installation Process

SQL Server 2012 installation is almost the same as the SQL server 2008 menu of SQL Server installation center. Planning, Installation, Maintenance, Tools, Resources, Advanced, and Options

  • Planning
  • Installation
  • Maintenance
  • Tools
  • Resources
  • Options

Planning helps in reviewing

  1. Hardware and software requirements
  2. Security considerations
  3. Online release notes
  4. Install upgrade advisor
  5. How to get started with SQL server 2012 failover clustering

Planning various hardware and software requirements of the server. System configuration checker (SCC) scans the machine where the installation of the SQL server exists and provides results that to avoid obstacles while installing an upgrade advisor will help while upgrading.

Installation:

  • New SQL Server installation (or) add features
  • New SQL Server failover cluster installation
  • Add node to SQL Server failover cluster
  • Upgrade from SQL Server 2008 R2

The installation provides various installation options

We can install a new SQL server for the first time (or) we can add additional features to an existing installation.

  • We can install clustering and  add a node to the cluster
  • We can upgrade to SQL server 2012 from old versions.

Maintenance

  • Edition upgrade
  • Repair
  • Remove node from SQL server failover cluster

We can upgrade and repair the SQL server from here.

Tools

  • System configuration checker
  • Installed SQL server features discovering report
  • Upgrade integration services packages
  • Power pivot configuration tool

System configuration checker scans SQL server installation. Installed SQL features provide detailed reports about SQL products installed on the server.

Upgrade integration packages is a wizard step in upgrading lower version SSIS packages to SQL server 2012 packages

Resources

  • SQL server 2012 books online
  • SQL server tech center

Resources will help in knowledge on SQL server 2012

Advanced

  • Install based on the configuration file
  • Advanced cluster preparation/completion
  • Image preparation of standalone server

Configuration file installs configuration files on the existing file.

The advanced cluster provides wizards for failover clustering. Image preparation gives install methods in SQL server 2012.

In option, we can specify the architecture of SQL server install (or) to change location of install files.

Once all these details provided we can start the actual installation

SQL Server 2012 Setup

  • setup support rules
  • setup role
  • features selection
  • disk space requirements
  • server configuration
  • distributed replay controller
  • distributed replay client
  • error reporting
  • install configuration rules
  • ready to install
  • installation progress
  • complete

The installation process starts with system checks and validations setup role is new screen in SQL server 2012, to choose install all features with default settings.

  1. features selection → provides all SQL components to select
  2. install rules → need to select default (or) named instance.
  3. disk space requirements → provide details whether we have specified space (or) not.
  4. server configuration → what are the services available in the server
  5. distribution replay controlled and distributed replay client are new features in SQL server 2012, allows us to replay our captured trace files of SQL server
  6. Error reporting → provides feedback to Microsoft if want to send
  7. Ready to install → provides all the features what we have selected
  8. Installation progress → actual installation starts
  9. Complete → complete SQL server installation

SQL Server 2012 installation completed.

SQL Server Post Installation Steps

1. Verify protocols enables (or) not

Microsoft sql server 2012 — sql server configuration manager — sql server network configuration — protocols for MS sql server

2. Open services in sql server configuration manager select sql services and verify whether they are started (or) not.

3. From run command type services .msc and verify the ms sql services status and mode.

4. Verify the advanced options using

Sp_configure “show advanced option” 1
Go and reconfigure

5. Specify maximum and minimum server memory

Sp_configure ‘max server memory (MB), 8192;
Sp_configure ‘Min server memory (MB), 1024;

6. Enable backup compression, if we want

sp_configure ‘backup compression default’ 1
Go
Reconfigure with override
Go

#Ref. Ram Mohan & Rama Krishna notes

Below topics further included in this series