DBA

MS SQL server remote DBA training course topics Chennai India

Advertisements

Microsoft SQL server training course topics:

 

  • Overview of IT environment with daily DBA activity and health check
  • A brief of history (RDBMS & DBMS)
  • SQL server internal architecture
  • SQL server overview (Editions)
  • SQL Installation overview (System DBs)
  • Review of new features
  • Transact -SQL overview
  • Normalization

TABLES AND CONSTRAINTS

  • Standard data types
  • Tables and columns in SQL server
  • Objects, Tables and columns in T-SQL
  • Modifying tables and columns
  • Database diagram architecture
  • Constrains

TRANSACT SQL QUIERES

  • Querying SQL server tables
  • SQL and transact SQL
  • Using the select statement
  • Transact SQL built-in function
  • Summary queries
  • Sub queries

MERGING DATA WITH JOINS&UNIONS

  • Working with inner joins
  • Using left outer joins
  • Multiplying data with cross joins
  • Referencing self –joins
  • Working with complex joins
  • Nested loop join
  • Merge join
  • Hash join
  • Join hints in SQL queries

AGGREGRATING DATA

  • Using aggregate function
  • Grouping a data set
  • Nesting aggregations
  • Filtering groups &adding grand totals
  • MERGE statement

VIEWS

  • Uses of views
  • Limitation of views
  • Calculated columns
  • Creating views
  • Overview of indexed views

INDEXES

  • Overview of indexes
  • Clustered versus non-clustered indexed
  • Filtered index
  • Creating indexed
  • Using SHOW PLAN to see if the index is being used
  • Using composite indexes to cover queries
  • Duplicate keys and rows and how to solve problem
  • The distribution page and update statistics
  • Understanding the index statistics steps

TRANSACTIONS

  • How data is changed in SQL server
  • Manipulating data within transactions
  • Transactions processing commands
  • Transaction isolation levels
  • Locking issues
  • Using sp- lock, Whoisactive
  • Table hints(locks)

PROGRAMING BASISCS

  • Variables
  • Flow control statements
  • Error handling
  • Using case expressions
  • Working with nulls& coalesce
  • Working with dates and times
  • Parsing strings
  • Converting data

USER DEFINEDD FUNCTIONS

  • Scalar function
  • Table function
  • Appropriate usage of function

STORED PROCEDURES

  • Creating stored procedures
  • Passing parameters
  • Returning values and updating parameters
  • Compilation problems and methods
  • Temporary procedures and auto start procedures

TRIGGERS

  • Basic trigger assumptions and syntax
  • Improving trigger performance
  • Checking the number of rows affected
  • Nested triggers
  • Ensuring integrity with triggers

 

MONITORING SQL SERVER

  • Viewing current activity
  • Using DDL triggers
  • Using event notifications

AUTOMATING ADMINISTRATIVE TASKS

  • Automating administrative tasks in SQL server
  • Configuring the SQL server agent
  • Creating jobs and operations
  • Creating alerts
  • Managing multiple servers
  • Managing SQL server agent security

Dynamic Management Views (DMVs & DMFs)

  • Understanding DMVs and DMFs
  • Use of DMVs and DMFs

INSTALLING AND CONFIGURATION SQL SERVER 2005

  • Preparing to install SQL server
  • Installing SQL server
  • Managing a SQL server installation

DATA STORAGE & MANAGEMENT

  • How is data organized in SQL server?
  • Creating a database
  • Using file groups
  • Shrinking database

MANAGING

  • Server roles
  • Data base roles
  • User mappings

LOGINS AND USERS

  • Authentication and authorization

IMPELEMENTING DISASTER RECOVERY STRATEG

  • Implementing a backup strategy
  • Implementing a recovery model strategy
  • Plan a backup strategy
  • Backup user database
  • Restore user database
  • Backup and restore system databases
  • Restoring database
  • Rebuild the master database

MAINTAINING HIGH AVAIABILITY

  • Introduction to high availability
  • Implementing Always ON availability groups
  • Implementing server clustering
  • Implementing database mirroring
  • Implementing log shipping

INTRODUCTION TO REPLICATION

  • Overview of replication
  • Replication scenarios

IMPLEMENTING REPLICATION

  • Creating a publication
  • Creating a subscription
  • Describe replication and its component
  • Describe common  replication scenarios

LOG SHIPPING AND DATABASE MIRRORING

  • Configuring transaction log shipping
  • Understanding database mirroring requirement
  • Configuring database mirroring

PERFORMANCE AND SQL SERVER

  • Query tuning methods
  • Execution plan reading
  • Choosing and Building indexes online
  • SQL statistics update
  • Transact SQL improvements
  • Monitoring resource consumption
  • Establishing baseline metrics
  • SQL server management studio
  • SQL server configuration manager
  • Database engine tuning advisor
  • SQL server profiler
  • Availability and scalability

DATABASE DESIGN FOR PERFORMANCE

  • Introducing logical Database design for performance
  • Introducing physical Database design
  • Data volume analysis
  • Transaction analysis
  • Hardware environment consideration

SQL SERVER AND WINDOWS

  • How SQL server uses CPU
  • Priority
  • Use of symmetric multiprocessing system
  • Thread use
  • Query parallelism
  • Investigation CPU bottlenecks
  • Solving problems with CPU
  • SQL server and memory
  • An overview of windows virtual memory management
  • How SQL server uses memory
  • Configuring memory for SQL server
  • Investigation memory bottlenecks
  • SQL server and disk I/O
  • An overview of the data cache
  • Keeping tables and indexes in cache
  • Investigation disk I/O bottlenecks

ARCHITECTURAL PERFORMANCE OPTIONS AND CHOICES

  • Storage Raid array

MONITORING PERFORMANCE

  • System stored procedures
  • System monitor, performance logs , and alerts
  • Client statics
  • The SQL server profiler
  • Database engine tuning advisor

SQL SERVER SSIS & SSRS walk through.

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

Your email address will not be published. Required fields are marked *

6 + 1 =