Last Update On:23-12-2024

SQL Server DBA

SQL Server Developer Topics / Course Contents

Introduction to RDBMS
File Management System 
• Database Management System (DBMS) and Data Models
• Physical Data Models
• Logical Data Models
• Relational Data Model (RDBMS)
• Object-Relational Data Model (ORDBMS)
• Entity-Relationship (E-R) Model

Introduction to SQL Server

Advantages and Drawbacks SQL Server
• Connecting to Server
• Server Type
• Server Name
• Authentication Modes
• Sql Server Authentication Mode
• Windows Authentication Mode
• Login and Password
• Sql Server Management Studio and Tool explanation

Introduction To TSQL

• History and Features of TSQL
• Types 0f TSQL Commands
• Data Definition Language (DDL) – Create, Alter and Drop
• Data Manipulation Language (DML) – Insert, Delete, Update, Truncate
• Data Query Language (DQL) - Select
• Data Control Language (DCL) – Grant and Revoke
• Transaction Control Language (TCL) – Rollback, Savepoint
• Constrains (Not Null, Unique, Default, Check constraints, Primary Key and Referential Integrity or foreign key constraints
• Data Types In TSQL

Clauses
Where, Group by, Having and Order by

Scalar Functions

• Numeric Functions
• Date Functions
• Aggregate Functions
•  Super Aggregates Over (partition by …) Clause
• Ranking Functions
• Common Table Expressions (CTE)

Inner Join
• Self Join
• Outer Join
• Left Outer Join
• Right Outer Join
• Full Outer Join
• Cross Join

Single Row Sub Queries
• Multi Row Sub Queries
• Co-Related Sub Queries

• Clustered Index
• NonClustered Index
• Create, Alter and Drop Indexes
• Using Indexes

• Purpose of Views
• Creating, Altering and Dropping Indexes
• Simple and Complex View
• Encryption and Schema Binding Options in creating views

Transaction Management

• Introduction
• Begin Transaction
• Commit Transaction
• Rollback Transaction
• Save Transaction

Cursor
• Working with Cursors
• Types of Cursors
• Static, Dynamic and Keyset Cursors
• Local and Global Cursors

Stored procedure
• Creating, Altering, and Dropping
• Input and Output Parameters
User-Defined Functions
• Creating, Altering, and Dropping
• Types of User-Defined Functions
• Table Valued Functions
• Inline Table-Valued Functions
• Multi Statement Table Valued Functions

Trigger

• Purpose of Triggers
• Differences Between Stored Procedures and User-Defined Functions and Triggers
• Creating, Altering and Dropping Triggers

Exception Handling

• Implementing Exception Handling

Security

• Login Creation
• SQL Server Authenticated Login
• Windows Authenticated Login
• User Creation
• Granting Permissions
• Revoking Permissions

SQL SQL Server Administration (DBA)

Module 1: Introduction   
Lessons
Introduction To The SQL Server Platform
•    Working with SQL Server Tools
•    Configuring SQL Server Services

Module 2: Preparing Systems for SQL Server 
Lessons
•    Overview of SQL Server Architecture
•    Planning Server Resource Requirements
•    Pre-installation Testing for SQL Server

Module 3: Installing and Configuring SQL Server 
Lessons
•    Preparing to Install SQL Server
•    Installing SQL Server
•    Upgrading and Automating Installation

Module 4: Working With Databases
Lessons
•    Overview of SQL Server Databases
•    Working with Files and Filegroups
•    Moving Database Files

Module 5: Understanding SQL Server Recovery Models
Lessons
•    Backup Strategies
•    Understanding SQL Server Transaction Logging
•    Planning a SQL Server Backup Strategy

Module 6: Backup of SQL Server Databases
Lessons
•    Backing up Databases and Transaction Logs
•    Managing Database Backups
•    Working with Backup Options

Module 7: Restoring SQL Server Databases
Lessons
•    Understanding the Restore Process
•    Restoring Databases
•    Working with Point-in-time recovery
•    Restoring System Databases and Individual Files

Module 8: Importing And Exporting Data
Lessons
•    Transferring Data To/From SQL Server
•    Importing & Exporting Table Data
•    Inserting Data in Bulk

Module 9: Authenticating And Authorizing Users
Lessons
•    Authenticating Connections to SQL Server
•    Authorizing Logins to Access Databases
•    Authorization Across Servers

Module 10: Assigning Server And Database Roles
Lessons
•    Working with Server Roles
•    Working with Fixed Database Roles
•    Creating User-defined Database Roles

Module 11: Authorizing Users To Access Resources
Lessons
•    Authorizing User Access to Objects
•    Authorizing Users to Execute Code
•    Configuring Permissions at the Schema Level

Module 12: Auditing SQL Server Environments
Lessons
•    Options for Auditing Data Access in SQL
•    Implementing SQL Server Audit
•    Managing SQL Server Audit

Module 13: Automating SQL Server Management
Lessons
•    Automating SQL Server Management
•    Working with SQL Server Agent
•    Managing SQL Server Agent Jobs

Module 14: Configuring Security For SQL Server Agent
Lessons
•    Understanding SQL Server Agent Security
•    Configuring Credentials
•    Configuring Proxy Accounts

Module 15: Monitoring SQL Server With Alerts And Notifications
Lessons
•    Configuration of Database Mail
•    Monitoring SQL Server Errors
•    Configuring Operators, Alerts and Notifications

Module 16: Performing Ongoing Database Maintenance
Lessons
•    Ensuring Database Integrity
•    Maintaining Indexes
•    Automating Routine Database Maintenance

Module 17: Tracing Access To SQL Server 
Lessons
•    Capturing Activity using SQL Server Profiler
•    Improving Performance with the Database Engine Tuning Advisor
•    Working with Tracing Options

Module 18: Monitoring SQL Server 
Lessons
•    Monitoring Activity
•    Capturing and Managing Performance Data
•    Analysing Collected Performance Data

Module 19: Managing Multiple Servers
Lessons
•    Working with Multiple Servers
•    Virtualizing SQL Server
•    Deploying and Upgrading Data-Tier Applications

Module 20: Troubleshooting Common SQL Server  Administrative Issues
Lessons
•    SQL Server Troubleshooting Methodology
•    Resolving Service-related Issues
•    Resolving Concurrency Issues
•    Resolving Login and Connectivity Issues