Skip to main content

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 Architecture
  • Recovery Models
  • Real time Scenario
Module4:  Security 
  • Authentication & Types of Authentication in SQL Server 
  • Types of Login 
    • Windows Login 
    • SQL Login 
  • Creating Users and Logins 
    • Server roles 
    • Password policy 
    • Understanding Database and Server Roles 
    • Permissions 
    • Working on access level issues 
    • Orphan users Finding and Fixing 
    • Important Queries 
Module5:  Database Backups and Restoration 
  • Database Backups 
    • Why we need backups 
  • Types of Backup 
    • Full Backup 
    • Differential Backup 
    • Transaction Log Backup 
  • Copy-only, Mirrored, Split and Tail log Backups 
  • Differences between backups 
  • Backup Strategy 
  • Understanding how the data moving from Log to Data File, CHECKPOINT 
  • Monitoring the space usage of Log File and fixing 
  • Checking the backup files VALID or CORRUPTED 
  • Backup storage tables 
  • Important Queries 
  • Performing  Restorations 
    • Types of Restoration 
    • Attach and Detach 
    • Shrinking files 
Module6:  Automation of SQL SERVER
  • Working with SQL SERVER Agent 
    • Creating Jobs 
    • Managing Jobs and Resolving issues 
    • Monitoring Jobs 
    • Monitoring Tables 
  • Log Shipping 
  • Mirroring 
  • Replication 
  • Clustering 

HIGH AVAILABILTY CONCEPTS

Module7:  Log Shipping 
  • Log Shipping 
    • Working with Log Shipping 
    • Terminology 
    • Pre-requisites to implement Log Shipping 
    • Different modes of Log Shipping 
    • Configuring Log Shipping 
    • Monitoring 
    • Manually Performing Failover 
    • Log Shipping Tables 
  • Discussing Real Time Scenarios 
Module8:  Database Mirroring 
  • Working with Database Mirroring 
    • Terminology 
    • Pre-requisites to implement Mirroring 
    • Pre check mirroring ports ENABLED or DISABLED 
    • Architecture 
    • Operating Modes 
    • Differences between Modes 
    • Port numbers involved in Mirroring 
    • Requirements for Mirroring 
    • Configuring Mirroring 
    • Mirroring failover 
    • Issues on Mirroring 
Module9:  Replication 
  • Working with Replication 
    • Terminology 
    • Pre-requisites to implement to Replication 
    • Types of Replications 
    • DISTRIBUTOR Database 
    • Agents involved in Replication 
    • Configuring Replication 
    • Resolving Issues 
Module10: Clustering & Always on Groups
  • Introduction about clustering 
  • Clustering Architecture 
  • Importance of IP’s involved in Clustering 
  • SAN importance 
  • Drive dependency 
  • Nodes in Clustering 
  • Important commands 
  • AOAG Concept
  • Listener configuration.
  • AOAG Group.

Performance Tuning

Module12:  Monitoring Tools 
  • System Monitor 
  • SQL Server Profiler 
  • SQL Server and Windows Error Logs 
Module13:  Database Performance Tanning 
  • Indexes 
  • Types of indexes 
  • Fragmentation in Indexes 
  • Types of Fragmentations 
  • Database Tuning Advisor 
  • Dead Locks 
  • Blockings 
  • Terminating Processes 
Module14:  Managing the SQL Server and Databases 
  • Starting SQL Server in Single user mode 
  • Changing the Default path of Database files 
  • Modifying the Logical and Physical names of database files 
Module 15: Process
  • ITIL Process
  • Ticket, Monitoring Tool
  • Escalation Matrix
  • Maintenance Activities[Daily\Weekly\Monthly]
  • DR Plan
  • BCP Plan
  • RACI Matrix
  • RCA Plan

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

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

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: Pre-Prod Environments. => Which Involves Dev environment and UAT environment. 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  ->           ...
Post a Comment
Read more