SQLCourse - Interactive Online SQL Training for [email protected]
SQL SERVER DBA COURSE CONTENT
SQL SERVER ARCHITECTURE
INTRODUCTION TO SQL SERVER 2012
Overview on RDBMS and Beyond Relational
What’s New in SQL Server 2012
SQL SERVER COMPONENT OVERVIEW
The Relational Engine
The Command Parser
The Query Optimizer
The SQL Manager
The Database Manager
The Query Executor
The Storage Engine
PAGES & EXTENTS
Pages
Extents [Uniform & Mixed]
Managing Extent Allocations
Tracking Free Space
FILES AND FILE GROUPS
DATABASE FILES
PRIMARY DATA FILES
SECONDARY DATA FILES
LOG FILES
DATABASE FILE GROUPS [PRIMARY & USER-DEFINED]
MEMORY ARCHITECTURE
32-bit Vs 64-bit Architecture
Dynamic Memory Management
Effects of min and max server memory
Buffer Management
Using AWE
The Buffer Pool and the Data Cache
Checkpoints
INSTALLING, UPGRADING, CONFIGURATION, MANAGING SERVICES AND MIGRATION
SQL SERVER 2012 INSTALLATION
Planning the System/Pre-Requisites
Installing SQL server 2008 R2 /2012
Installing Analysis Services
Installing & Configuring Reporting Services
Best Practices on Installation
Uninstalling SQL server
Common Installation Issues
Case study 1: Moving system databases
Case study 2: Troubleshooting on a Failed Installationtion
UPGRADING TO SQL SERVER 2008 R2/2012
UPGRADING THE SERVER BY APPLYING SERVICE PACKS
UPGRADING THE SERVER BY APPLYING HOT FIXES
IN-PLACE UP GRADATION FROM SQL SERVER 2008 R2 TO 2012
PRE-UPGRADE CHECKS
UPGRADE ADVISOR
BEST PRACTICES TO FOLLOW WHILE UPGRADING
CONFIGURING SQL SERVER
Configuring Network Protocols from SQL Server configuration manager
Configuring features by using SQL Sever surface area configuration manager
Configuring other settings through SP_Configure
Dedicated Administrator Connection
Connecting to DAC
Configuring Server and Agent property settings
Configuring Database Settings
Configuring Memory Settings
Configuring Database Mail
Configuring Alert system in SQL server agent
Tempdb configuration
Best Practices on configuration tempdb & Database settings
MANAGING SERVICES
Starting and Stopping Services through
Configuration manager
Net Command
Command Prompt [sqlsrvr.exe]
Start Up parameters
Starting SQL server in single user mode
Starting SQL server with minimal configuration
Case study 1 : Tempdb failure issue
Case study 2 : Rebuilding system databases
Case study 3 : Changing SQL Server collation
MIGRATING SQL SERVER
Side-By- Side Migration Techniques
Difference between in-place & Side by Side Migration/Upgradation
Advantages/Disadvantages of In-Place to Side-by-Side
Migrating Databases
Migration by using Attach and Detach Method
Migration by using Back and restore method
Migration by using Copy Database Wizard
Migrating Logins [Fixing Orphaned Users]
Migrating Jobs
Import & Export
Migrating jobs & logins by using SSIS
SECURITY, AUTOMATION & MONITORING
SECURITY
Security Principles
Server Roles
Server and Database Principles
Database Roles
Creating Logins and mapping Users to databases
Creating credentials
Creating Schemas
Server & Database Securable
Granting to Object level Permissions
Case study 1: How to Recover "SA" password when you forget it.
Case study 2: How to grant execute permissions to a single stored procedure
Case study 3: How to grant execute permissions on all stored procedures
Best Practices on security
AUTOMATING ADMINISTRATIVE TASKS
About SQL server Agent
Creating Jobs, Alerts and Operators
Scheduling the Jobs
Creating Maintenance Plans
Working with Job activity Monitor
Resolving failure Jobs
Best practices on job maintenance
MONITORING SQL SERVER
The Goal of Monitoring
Choosing the Appropriate Monitoring Tools
Monitoring Job activities by job activity monitor
Monitoring SQL Server process by server activity monitor
Monitoring SQL Server Error Logs/Windows by log file viewer
Best Practices on Monitoring
HIGH AVAILABILITY & REPLICATION
T-LOG ARCHITECTURE
Transaction Log Logical Architecture
Transaction Log Physical Architecture
Checkpoint Operation
Write-Ahead Transaction Log
Managing T-log
Truncating and shrinking the log file
Managing T-Log issues by using DBCC commands
BACKUP & RESTORE:
Recovery Models [Simple, Bulk-Logged & Full]
How Backup Works
Types of backups
Full backup
Diff backup
T-log backup
Copy Only
Mirror
Tail-Log
Compressed backups
Restoring Modes [With Recovery, No Recovery, Read only/Standby]
Performing Restore (point-in-time recovery)
Disaster Recovery Planning
Case study on developing and executing a Backup Plan
Case study 2: How to restore a suspect database
Case study 3: How to recover the database without having ldf file
Case study 4: Recovering crashed SQL Server
Resolving Backup failures in Real time scenarios
Best Practices on Backup & Recovery
LOG SHIPPING
Log-Shipping Architecture
Building DRS for log-shipping
Pre-requisites/Log-Shipping Process
Deploying Log Shipping
Working with Log Shipping Monitor
Logs hipping Role changing [Fail-Over]
Removing Log Shipping
Frequently Raised Errors In Log-Shipping
Case study: How to add files to a log-shipped database
Best Practices on Log-Shipping
DATABASE MIRRORING
Overview of Database Mirroring
Operating Modes in Database Mirroring
Pre-Requisites for Database Mirroring
Deploying Database Mirroring
Fail-Over from Principle to Mirror
Working with Database mirroring monitor
Advantages & Disadvantages of database mirroring
Database Snapshots
Using Database Snapshots for reporting purposes.
Case study on moving mirrored files
Best practices on Mirroring
REPLICATION
Replication Overview
Replication Models (snapshot/Transactional/Merge/Updatable/Peer to Peer)
Replication agents
Configuring Distributor
Deploying Transactional Replication for High Availability
Creating Subscriptions [Homogeneous / heterogeneous]
Monitoring Replication by using replication monitor
Scripting Replication
Best Practices on Replication
Case study: Configuring peer to peer replication
SQL SERVER CLUSTERING
SERVER CLUSTERING CONCEPTS
What is a cluster and Overview of windows cluster
Server cluster technologies
Server clusters
NLB clusters [Network load balancing]
Basic architecture of server clusters
How cluster works
Types of quorums
Basic elements of a 2-Node cluster with single quorum.
INSTALLING SQL SERVER 2012 FAIL-OVER CLUSTERING
Pre-SQL Server Installation Tasks.
Configure SQL Server–Related Service Accounts and Service Account Security
Stop Unnecessary Processes or Services
Check for Pending Reboots
Install SQL Server Setup Support Files
SQL Server 2012 Setup
Install the First Node
Add Nodes to the Instance
Perform Post installation Tasks
Verify the Configuration
Remove Empty Cluster Disk Resource Groups
Set the Preferred Node Order for Failover
Configure a Static TCP/IP Port for the SQL Server Instance
ADMINISTERING A SQL SERVER 2012 FAILOVER CLUSTER
Install SQL Server Service Packs, Patches, and Hot fixes
Introducing Failover Cluster Management
Monitoring the Cluster Nodes
Destroying a Cluster- Using Failover Cluster Management
Clustered SQL Server Administration
Fail over groups between the nodes
Automatic failover & Failback
Uninstalling a Failover Clustering Instance
Best Practices on Clustering
Case study 1: Changing the Network Name of a SQL Server Failover Cluster
Case Study 2: Changing the Service Accounts for a SQL Server Failover Cluster
Case Study 3: Changing the IP Address of a SQL Server Failover Cluster
PERFORMANCE TUNING & INDEXING
INDEXING
Index Architecture
How to optimally take advantage of indexes
Clustered & Non-Clustered indexes
Index Fragmentation
Index Defragmentation options\update Statistics
How to determine fragmentation
Best Practices on Indexing
PERFORMANCE TUNING
Factors That Impact Performance
Tools used Activity Monitor, SQL Profiler, Database Tuning Advisor
Working with Activity Monitor
Blocking [SP_Who2]
SQL Profiler [How to capture events data by using Profiler]
Deadlocks and deadlock chain detection.
Analyzing the data by using DTA [Database Tuning Advisor]
Performance Monitor [System Monitor]
Dynamic Management Views (DMV)
Best Practices on Performance Tuning
Case Study A: Performance Counters Setup-Collect-Analyze
Case Study B: Performance Counters- Thresholds
SQL SERVER 2012 ENGINE/HA ENHANCEMENTS
SQL SERVER 2012 EDITIONS AND ENGINE ENHANCEMENTS
Principal Editions
Specialized Editions
Breadth Editions
2008 R2 FEATURE AT A GLANCE:
Policy Based management
Resource Governor
Central management servers
Change data capture
Compression
2012 SECURITY ENHANCEMENTS
Default Schema for Groups
User-defined server roles
Audit Enhancements
User-defined audit event
Database authentication enhancements
Enabling contained databases
Creating users
New permissions
2012 HIGH-AVAILABILITY AND DISASTER-RECOVERY ENHANCEMENTS
SQL Server AlwaysOn
AlwaysOn Availability Groups
Understanding Concepts and Terminology
Configuring Availability Groups
Monitoring Availability groups
Backups on Secondary
AlwaysOn Failover Cluster Instances
Support for Server Message Block
Database Recovery Advisor
Online Operations
2012 HIGH AVAILABILITY: INTEROPERABILITY AND COEXISTENCE
Database Mirroring and Failover Clustering
Database Mirroring and Log Shipping
Database Mirroring and Database Snapshots
Replication and Log Shipping
Replication and Database Mirroring
2012 PERFORMANCE AND SCALABILITY
Columnstore Index Overview
Columnstore Index Fundamentals and Architecture
New DMV’s and DMF’s
CORPORATE AWARENESS AND HANDLING THE ENVIRONMENT
Defining the process
How they implement the security
How they implement process by using CRM tools
Knowledge on ticketing tools
Succeeding the interviews.
please mail us all queries to [email protected]
You can reach us anytime for your valuable Queries.
For Free Demo and fee details and course structure, you can reach us anytime at:
www.mindqonline.com and http://sqldbaonlinetraining.in
Mind Q Systems Private Limited
==============================
Corporate Office Address
Mind Q Systems Pvt Ltd
8-3-214/7, 1st Floor, Srinivasa Nagar Colony(W)
Above HDFC Bank, S.R.Nagar, Hyderabad-500038
Telephone: 040-66664291/92/93
Mobile: +91 9502991277/88
Mailing Address