Whenever we want to start the installation of the SQL server we need to gather details of installation.
SQL Server Versions
|Sql server 2014 (Heteko)
|Trail version released
|Sql server 2012 (Denali)
|Sql server 2008 R2 (Kilimanjaro)
|Sql server 2008 (Katmai)
|Sql server 2005 (Yukon)
|Sql server 2000(Shiloh)
|Sql Server 7.0 (Sphinx)
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
- 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
- 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.
- 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
- Server Edition
- Advanced Edition
- Enterprise Edition
- 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.
- Database Services – under this service database engine will run.
- Integration Services (SSIS) – From multiple sources ETL (Extract Transform Load) will integrate data.
- Reporting Services (SSRS) – To generate reports very quickly
- Analysis Services (SSAS) – For notification purpose
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.
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,
- Mixed mode
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,
- SQL server service
- SQL agent service
- SQL browser service
- Full-text service
- 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
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.
- Net framework 4.0
- Windows Powershell 2.0
- .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 helps in reviewing
- Hardware and software requirements
- Security considerations
- Online release notes
- Install upgrade advisor
- 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.
- 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.
- Edition upgrade
- Remove node from SQL server failover cluster
We can upgrade and repair the SQL server from here.
- 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
- SQL server 2012 books online
- SQL server tech center
Resources will help in knowledge on SQL server 2012
- 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
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.
- features selection → provides all SQL components to select
- install rules → need to select default (or) named instance.
- disk space requirements → provide details whether we have specified space (or) not.
- server configuration → what are the services available in the server
- 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
- Error reporting → provides feedback to Microsoft if want to send
- Ready to install → provides all the features what we have selected
- Installation progress → actual installation starts
- 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
- SQL Server Database Administration Introduction
- SQL Server Architecture
- SQL Server Storage Architecture
- SQL Server Database Architecture
- SQL Server Transaction Log Architecture
- SQL Server installation
- SQL Server Databases
- SQL Server Backups
- SQL Server Restoration Tutorial
- SQL Server Security
- SQL Server Automation Tutorial
- SQL Server Migration
- SQL Server Upgradation
- SQL Server Log Shipping in SQL Server