Skip to main content

2. Installation, Patching, Migration, Upgradation of SQL Server.


Topics In This Module.

  • Environments
  • Installation of SQL Server
  • SQL Server Instances
  • SQL Server Services
  • Ports, Protocols and Service Accounts.
  • Patching
  • Migration & Upgradation.

***============================================================================***

Environments

There are two types of environments in general they are:

  1. Pre-Prod Environments.
    => Which Involves Dev environment and UAT environment.
  2. Prod Environments.
    => We do have OLAP(Online Analytical Processing) and OLTP(Online Transactional Processing) Servers in production.

SQL Server -> Instance -> database -> File groups -> Files -> as two types below

The Files in SQL Server are of two types they are

  • Data files -> Extends -> Pages -> Header part, Data part(8060 Bytes), Row off Set
  • Log files -> Virtual logs -> LSN ->                

1 page = 8KB of size = 8*1024bytes
1 extent has 8 Pages = 8pages*8Kb = 64 Kb size
Based on file size we get number of extents

Instance: A copy of SQL Servers and Its services present on a system is a instance.

==================================================================================

Installation of SQL Server

There are few steps to be followed for complete installation of an Instance\SQL Server they are 

Installation steps can be classified as few steps they are:

  •     Prerequisites
  •     Pre-Installation
  •     Installation 
  •     Post-Installation

=> Prerequisites: Gather all Prerequisites and make sure all of them are meet before installation of SQL Server instance. 

  • .NET Framework as per server Version/Edition
  • Windows Installer.
  • Resources. 
=> Pre-Installation: Check all Prerequisites are met and gather below details
  • Gather the type of installation, 
  • Gather the type of instance need to install, 
  • Check OS compatibility: Check does that SQL sits in the windows or not.
  • Software components: .NET Framework, OS and Latest patch
  • Hardware Components: RAM, CPU and storage.
=> Installation: Steps we see while installation.
  • Components selection(Services)
  • Instance type selection
  • Service account/Admins 
  • Authentication screen 
  • Collation Setting 
  • Summary
=> Post Installation: Verification part.
  • Check the path of installation and Binaries. 
  • Check the Services Installed.
  • Verify the Connection to instance.
--------------------------------------------------------------------------------------------------------------------------------------------------------
Types of installation
There are two types of SQL Server installation they are 
    1. Stand alone Installation: Application, SQL Server, Client Tools will be on same server, All in one machine.
    2. Client-Server Installation: 
      • On Client Machine: Client connectivity tools + Application 
      • On Server Machine: SQL Server components + Client Tool Components
SQL Server Components
All SQL related services are called SQL Components, Below are the SQL Server components/list of services.

    1. SQL Server (Instance Name) – Engine Service
    2. SQL Server Agent (Instance Name) – Agent Service
    3. SQL Full-text Filter Daemon Launcher (Instance Name)
    4. SQL Server Analysis Services (Instance Name)
    5. SQL Server Reporting Services (Instance Name)
    6. SQL Server Integration Services    
    7. SQL Server writer
    8. SQL Server ADH
    9. SQL Server Browser

Client tools Components
All client tools used to connect to SQL Server are Client Tool Components some of them which are used mostly are listed below.
    1. SSMS (SQL Server Management Studio)
    2. SQLCMD (SQL Command),
    3. BDC (Big Data Cluster),
    4. SCM (SQL Server Configuration Manager),
    5. SSAC (SQL Server Surface Area Configuration),
    6. SQL Profiler,
    7.  DTA (Data Tuning Advisor),
    8. Book Online,
    9. BCP (Bulk Copy Programming)
Client Connectivity Tools
The Client connectivity drivers or tool used are listed below:
    1. ODBC - Open Database Connectivity
    2. OLAP Drivers, 
    3. SQL Native Clients.
--------------------------------------------------------------------------------------------------------------------------------------------------------

The services provided by SQL server and its executable windows file and its default location are:

    1. Database Engine Services (sqlservr.exe)
    2. Analysis Services (msmdsrv.exe)
    3. Integration Services (MsDtsSrvr.exe)
    4. Reporting services (ReportingServicesService.exe)

Database Engine Services

  • The engines is divided into two engines they are:

1. Relational Engine:

    • Handles the execution of queries which are received from client application this engine contains the following components they are:

a. Query/CMD Parser.
b. Optimizer or Planner.
c. Query Executor.

              These components are explained here: Relational Engine Components Explanation

    • Simply said its an interface between the client application and storage engine.

2. Storage Engine:

    • Handles actual accessing the data from the physical files as requested by Query executor, This engine contains of the following components they are:

a. Transaction Manager.
 b. Access Methods.
 c. Buffer Manager.
 
                These components are explained here: Storage Engine Components Explanation

    • These components interacts with the databases(data and log files) to select/ insert/ update/ delete data as required.
--------------------------------------------------------------------------------------------------------------------------------------------------------

SQL Server Instances

An Instance is also a SQL Server, simply said a copy of SQL server with its related services (Instance Aware Services) is called an instance.

Types of Instance
There are two types of instances they are:
    1. Default Instance.
    2. Named instance.
Default Instance: These types of instances can be only 1 per server as the instance name is the server’s name itself so only 1 default instance can be created per server.
Named Instance: These types of instances can be maximum of 50 instances per server.

A server can contain only max 50 number of Instances - in which only 1 instance can be the default instance, rest all can be named instance, or call be all 50 named instances.

in one case we can keep all 50 instances as named instances in a server, else we can have 49 named instance and 1 default instances. 

Note:

    • For SQL Server 7.0 we can have 1 instance per sever (No Named instances)
    • For SQL Server 2000 we can have 16 Instances per server
    • For SQL Server 2008 We can have 50 Instance per server 

==================================================================================

SQL Server Services

There are different services provided by MS-SQL server, These different SQL Server services are also called as SQL Server components they are

    1. SQL server Engine Services.
    2. SQL server Agent.
    3. SQL server full text demon launcher.
    4. SQL server browser.
    5. SQL server Integration Services
    6. SQL server Reporting Services.
    7. SQL server Analysis Services.
    8. SQL server ADH: Integration of SQL with AD on SQL server 2000(Not shown in configuration manager).
    9. SQL server writer: Added functionality to backup and restore VSS (Not Shown in configuration manager).
Types of SQL Server Services
There are two types SQL Services they are:
    1. Instance Aware services.
    2. Instance Unaware services
Instance Aware Services: These are specific services to an instance 1 per instance, below services are the instance aware services.
    • SQL server Engine Services
    • SQL server Agent.
    • SQL server full text demon launcher.
    • SQL server Reporting Services.
    • SQL server Analysis Services.

Instance Unaware Services: These are shared services across servers have mostly 1 per server, below services are the Unaware services.
    • SQL server Browser.
    • SQL server Integration Services.
    • SQL server ADH.
    • SQL server writer.
--------------------------------------------------------------------------------------------------------------------------------------------------------

Protocols Ports & Service Accounts

Protocols
SQL server use 5 different Protocols they are:

    1. Shared Memory: We use this when server and client are on the same machine
    2. Named pipes: We use this when server and client are on same LAN
    3. TCP/IP: We Use this when server and client are on different networks, uses WAN.
    4. VIA: Virtual interface Adapter.
    5. TDS:

Ports
In SQL Server there are different port types they are:

    1. Static port: Will be same always, default is 1433
    2. Dynamic port: Will be changing whenever services are restarted.
Accounts

Service accounts used to run the SQL Server Services are

    • NT Service\MSSQL$instance_name for Engine Service
    • NT Service\SQLAgent$ instance_name for Agent Service
    • NT Service\MSSQLFDLauncher$ instance_name for full text demon launcher
    • NT Service\ReportServer$ instance_name for reporting services
    • SQLServerMSASUser$Server_name$ instance_name for Analysis services
    • NT AUTHORITY\LOCALSERVICE for SQL Server browser
    • NT Service\MsDtsServer130 for integration services
    • NT Service\SQLWriter for SQL Server writer service.

Note: NT Service means Network Service

"Once after instance is installed and connection to instance is successful, we see some by-default databases in instance which are called system databases, and per requirement the user defined databases to load data to use it as requirement"

==================================================================================

Patching

Applying Hotfixes/CU’s (Cumulative Updates)/SP’s (Service Packs)/Fixing vulnerabilities to an already installed instance is called patching.

What are Hotfixes/CU’s/SP’s?

  1. Hotfixes: Hotfix is a solution to a Bug, these are released whenever a bug is found, and need to be fixed.
  2. CU (Cumulative Update): Collection of all Hotfixes released till date and any more bugs to be fixed are released in this, usually these are released quarterly.
  3. SP (Service pack): Any additional features to current versions are released in this release and also all the previous Hotfixes and CU features are present here in this update. Usually these are released half yearly.

Steps to follow when patching an instance:

  • Login to server with Service Account or admin account.
  • Copy the patch file to the server.
  • Take System Db Backup. (Used to recovery instance if anything goes wrong)
  • Check the version before patch (as well as 32/64 bit and how many instances, HA) and check the space in C - Drive (Min 5 GB should be there)
    • If in HA, then the instance should be a secondary instance
  • Apply the patch
  • Reboot the server.
  • Check the version post patching (Reboot/service/Recovery/version)
  • Remove the patch file from server.
--------------------------------------------------------------------------------------------------------------------------------------------------------

Migration and Upgradation

Migration: The process of moving data from one database to other databases in same SQL or from oracle to SQL or from one instance to another instance.

Upgradation: Upgradation can be done as version upgradation, edition upgradation.
There are two types of Upgradations they are:

    1. In place Migration/In place Upgradation
    2. Side by Side Migration/Side by Side Upgradation

In place: Taking the action (Migration/upgradation) on the same instance which is already installed. Simply said Replacing the existing instance.

Side by Side: Create a new server and install a new instance with the new upgraded properties, so that the old instance will be running as usual whenever the new instance is upgraded then the connection will be sifted to this new one. And later the old is decommissioned once the new instance is perfect.

Pre-Migration:

  • Gathering the data and planning for migration.
  • Checking new server specifications.
  • Running upgrade advisor or Migration Tool.
  • And also follow the recommendations of advisor or migration.

Migration:

  • Installation of SQL server if Side by Side migration.
  • Backups and restore/Attach and detach/CDW the databases.
  • Move Logins, Jobs, Maintenance plans, and linked servers (if any)

Post-Migration:

  • Compatibility changes, and any server configuration changes.
  • Fix Orphan users if any.

Labels:

Comments

Post a Comment

Popular posts from this blog

6. SQL Server Agent.

All SQL Server Agent details are maintained by MSDB databases, which store all the below information All individual jobs and maintenances plan related jobs. Schedules of all jobs. Operator, Database mails. All Alerts related information. All the Error log information is stored here. What is SQL server Agent? SQL Server is an individual service and it’s a default service which comes with the SQL server engine service, All the agent is stored in msdb. Why SQL server Agent? This SQL server agent is used for providing automation for all SQL server administrative tasks. What is operator? All the notifications of a job are received by this person based on the job status. What is Database mail? Database mail is the from address from where and which SMTP the notification to Operator need to send is given while configuring database mail, Only user with DatabaseMailUser role can configure this, By default Database mail is not enabled in older versions, we use SSAC(SQL Surface Area Conf...
Post a Comment
Read more

Index

Microsoft SQL Server Database  Administration  Concepts. Module1:  Starting with SQL Server   Responsibilities of Database Administrator  Types of DBAs  History of SQL Server  Editions of SQL Server  Tools of SQL Server  Differences between Standard and Enterprise editions  Instances types in SQL Server  Default Instance  Named Instance  SQL Server Services  Instance aware services  Instance unaware services Module2:  Installation, Patching, Migration, Upgradation of SQL Server.   Environments Installation of SQL Server SQL Server Instances SQL Server Services Ports, Protocols and Service Accounts. Patching Migration & Upgradation. Module3:  Creation and Managing SQL Server Databases Databases. Types of databases and Its Properties Database Architecture Page Extent Files File Groups Modifying Database Modifying Files and File Parameters File Movement Database Corruption Log File ...
Post a Comment
Read more

Microsoft SQL Server Architecture

MS-SQL Server is a Client-Server Architecture. MS-SQL Server process starts with the client application sending a request, The SQL Server accepts, process and replies to the request. SQL server Architecture contains the following major components they are Protocol Layer. Relational Engine. Storage Engine. Databases (Files). Protocol Layer This supports three type of client-server architecture            1.   Shared Memory :  SHARED MEMORY PROTOCOL  Here MS-SQL server provides and supports shared memory protocol here CLIENT and MS-SQL server are on the same machine. To get connected to the default instance on the machine we can use SSMS (SQL Server Management Studio) and to connect use any of the type in SSMS connection method ".", "localhost", "127.0.0.1", "Machine_name\Instance_name"           2.  TCP/IP :  The client and MS-SQL server is on different physical location and different machines, whic...
Post a Comment
Read more

1. Starting with SQL Server

Topics In This Module. Responsibilities of Database Administrator  Types of DBAs  History of SQL Server  Editions of SQL Server  Tools of SQL Server  Differences between Standard and Enterprise editions  Instances types in SQL Server  SQL Server Services 
Post a Comment
Read more