SQL Server 2012 Database Administration With AlwaysOn & Clustering Techniques

Module: 1 Architecture &Internals of SQL Server Engine Module: 2 Installing, Upgrading, Configuration, Managing Services and Migration Module: 3 Security, Automation & Monitoring Module: 4 High Availability, Replication Module: 5 SQL Server Clustering Module: 6 Performance Tuning & Indexing Module: 7 SQL Server 2012 Engine/HA Enhancements Module: 8 Corporate Awareness and handling the environment

Module: 1 SQL Server Architecture

Introduction to SQL Server 2012
Overview on RDBMS and Beyond Relational Whats New in SQL Server 2012

SQL Server Component Overview

The The The The The The The Relational Engine Command Parser Query Optimizer SQL Manager Database Manager Query Executor Storage Engine

Pages & Extents

Pages Extents [Uniform & Mixed] Managing Extent Allocations Tracking Free Space Database Files 1. Primary data files 2. Secondary data files 3. Log files Database File groups [Primary & User-defined]

Files and File groups

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

Module: 2

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 Installation

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 1. Configuration manager 2. Net Command 3. 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 1. Migration by using Attach and Detach Method 2. Migration by using Back and restore method 3. Migration by using Copy Database Wizard Migrating Logins [Fixing Orphaned Users] Migrating Jobs Import & Export Migrating jobs & logins by using SSIS

Module: 3 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

Module: 4

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 1. Full backup 2. Diff backup

3. T-log backup 4. Copy Only 5. Mirror 6. Tail-Log 7. 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

Modeule : 5 SQL Server Clustering

Server Clustering Concepts
What is a cluster and Overview of windows cluster Server cluster technologies 1. Server clusters 2. 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 ServerRelated 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

Module: 6
Indexing

Performance Tuning & 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

Module: 7 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 DMVs and DMFs

Module: 8 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.