SQL Server Advance L3 Workshop in HCL, NOIDA – Oct 04 to Oct 08, 2010

Leave a comment (0) Go to comments

5 day workshop for SQL Server Advance features for level 3 DBA’s in Noida by DBATAG for HCL.

The following topics were covered during workshop

1. Evolution of SQL Server with SQL Server 1.0

2. SQL Server Version History

3. Key Business Scenarios for SQL Server 2008

4. SQL Server 2008 Editions including Data centre and Parallel Data Warehouse Edition

5. Hardware and Software Requirements for SQL Server 2008

6. Considerations for Installing SQL Server 2008

7. What’s New in SQL Server 2008?

8. Install SQL Server 2008 R2 on 2 node Cluster on Windows 2003 using VMware 7.0

9. Build Active/ Active SQL Cluster using VMware 7.0

10. Failover of Cluster resources

11. How to set up MSDTC on Cluster resources

a. Perquisite

b. Steps

12. Rebuild System databases on a clustered named instances

13. How to install SQL Server named instance on Windows Cluster

14. Troubleshooting with clustering services

a. Event Viewer

b. Cluster Logs

c. SQL Server Error Logs

15. Reporting Services

a. Install reporting services

b. Configuring reporting services

c. Scheduling report

d. Building and deploying reports

e. Authorizing Reports

f. Uploading reports

g. Backing up Report Databases

16. Central Management Servers

a. Creating a Central Management Server and Server Groups

b. Executing Multi server Queries

c. Demonstration: Using Central Management Servers and multi Server Query

17. Policy Framework

a. What Are Policies?

b. What Is Policy-Based Management?

c. What Are Targets, Facets, and Conditions?

d. Policy Evaluation Modes

e. Creating Policies

f. Assigning Policies

g. Evaluating Policies

h. Applying Policies

i. Using Policies with Server Groups

j. Demonstration: Using Policies

i. Compatibility Model should be 10 for all databases, notify, if there is a deviation

ii. There should not be any SQL Server Login

iii. How to Export a policy

iv. How to import a policy

v. Policy evaluation directly from a policy XML file

vi. Built in policy templates in SQL Server 2008

vii. Built in policy templates in SQL Server 2008 using are being used by Best practice Analyzer

viii. Evaluating Policies using Central Management Server

18. Design & Test Patching Plan

i. What are patches like, Service Packs, Cumulative Updates and Hot fixes

ii. Patching strategy based on Risk and Cost

1. Preventing

2. Reactive

iii. Technical, Pre Patching steps

iv. Technical, Post patching steps

19. Migration and Upgrade Planning

a. What is upgrade advisory wizard

b. Shared a step by step trainer experience for upgrading SQL Server 2005 clustered environment to SQL Server 2008, which covers, Pre migration activities, Migration activities and Post migration activities

20. Design and Implement Monitoring

a. How to set up DB mail : DEMO

b. What is SQL mail and limitations: No demo for configuring SQL Mail as attendees are using this?

c. How to receives Email in case job success or fails : DEMO

d. WMI Scripting / VB script for other monitoring : discussion

21. Designing Security

a. Security defense of depth , OS, Network , SQL Server ,

b. SQL Server Security Framework

c. What Are Principals?

d. What Are Securable?

e. SQL Server Permissions

f. What Are SQL Server Authentication Methods?

g. Password Policies

h. Server-Level Roles

i. Managing SQL Server Logins

j. Delegation Between Instances

k. What Are Credentials?

l. Server-Scope Permissions

m. What Are Database Roles?

n. What Are Application Roles?

o. Managing Users

p. Special Users

q. Database-Scope Permissions

r. Schema-Scope Permissions

s. Ownership Chains

t. What Are Keys?

u. What Are Certificates?

v. When to Use Keys and Certificates

w. Transparent Data Encryption

x. Credentials

y. How to create credentials :Demo

22. Designing Auditing

a. Using SQL Server Profiler

i. Overview of SQL Server Profiler

ii. SQL Server Profiler Trace Options

iii. Trace Categories, Events, and Columns

b. Monitoring with DDL triggers

i. Overview of DDL Triggers

ii. Creating DDL Triggers

iii. Managing DDL Triggers

c. Monitoring with DML triggers

i. Overview of DML Triggers

ii. Creating DML Triggers

iii. Example Hoe to create DDL and DML Triggers

d. Scripting Trace Files

e. Scheduling of Traces via SQL Jobs

f. SQL Server Audit, which is new in SQL Server 2008

i. What Is SQL Server Audit?

ii. Scenarios for SQL Server Audit

iii. Creating an Audit

iv. Creating an Audit Specification

v. Demonstration: Implementing Auditing by Using SQL Server Management Studio

1. Logins Success and Failure

2. Select / Update Queries on table level

23. Designing Encryption

a. Secure Data Scenarios

b. Encryption in Previous Releases

c. What Is Transparent Data Encryption?

d. Encrypting a Database

e. Backing Up and Restoring an Encrypted Database

f. Decrypting a Database

g. Demonstration: Encrypting and Decrypting a Database

h. Demonstration: How to share keys and certificate for user to perform encrypted database restore

i. What Are Keys?

j. Columns Level Encryption, How to implement column level encryption and hoe to read information for a encrypted column

24. High Availability

a. Introduction to High Availability

b. Implementing Replication

c. Implementing Log Shipping

d. Implementing Database Mirroring

e. Implementing Server Clustering

f. Replication

i. Overview of Replication

1. Distributing and Synchronizing Data

2. What is Replication?

3. Components of Replication

4. Server Roles in Replication

5. Types of Replication

6. What are Replication Agents?

ii. Managing Publications and Subscriptions

1. How to Create a Publication

2. How to Create a Subscription

iii. Configuring Replication in Some Common Scenarios

iv. Demo for Peer to Peer Replication

g. Mirroring

i. What Is Database Mirroring?

ii. Database Mirroring Server Roles

iii. Options for Database Mirroring Configuration

iv. What Are Database Mirroring Endpoints?

v. Establishing a Mirror Session

vi. Handling Failover

vii. Transparent Client Redirection

h. Log shipping

i. What Is Log Shipping?

ii. Configuring Log Shipping

iii. Switching Server Roles

25. Backup / Restore

a. Planning a Database Backup Strategy

i. Overview of Microsoft SQL Server Backup Types

ii. What Are Recovery Models?

iii. What Is a Full Database Backup Strategy?

iv. What Is a Database and Transaction Log Backup Strategy?

v. What Is a Differential Backup Strategy?

vi. What Is a File or Filegroup Backup Strategy?

b. Backing Up a User Database

i. Checking Database Integrity

ii. Performing a Full Database Backup

iii. Performing a Transaction Log Backup

iv. Performing a Differential Backup

v. Performing a Tail log and a Copy Only Backup

vi. What Is Backup Compression?

vii. Options for Ensuring Backup Integrity

c. Restoring User Databases

i. How the Restore Process Works

ii. Types of Restores

iii. Restoring a Database

iv. Restoring a Transaction Log

v. Point in Time recovery with a attendees example

vi. How to recover a data in case of data inconsistency with application owners

d. Recovering Data from Database Snapshots

i. What Is a Database Snapshot?

ii. How Database Snapshots Work

iii. Recovering Data from a Database Snapshot

e. System Databases and Disaster Recovery

i. Considerations for Backing Up Systems Databases

ii. Considerations for Restoring Systems Databases

iii. Restoring the Master Database

iv. Restoring msdb and model Database

26. Designing Jobs

a. Automating Administrative Tasks in SQL Server 2008

i. Benefits of Automation

ii. What is a Job?

iii. What are Operators?

iv. Creating Job Steps

v. Using the Job Activity Monitor

b. Using SQL Server Agent

i. What is SQL Server Agent?

ii. Considerations for SQL Server Agent Configuration

iii. Using Database Mail with SQL Server Agent

c. Creating Maintenance Plans

i. What is a Maintenance Plan?

ii. Creating a Maintenance Plan

d. How to send Job Success and Failure Email Alerts

e. How to write Job Success and Failure information on File or Event Logs

f. SQL Server Agent Proxy Account

g. MSDB special database roles

27. Resource Governor

a. What Is Resource Governor?

b. What Are Resource Pools and Workload Groups?

c. Managing Resource Governor

d. Assigning a Workload to a Workload Group

e. Demo giving one user 90 % resources and other user is using only 10 % CPU

28. Performance troubleshooting and Root Cause analysis

a. Performance Monitoring Overview

b. Monitoring Database Servers

i. Windows Performance Monitor

ii. Useful SQL Server Performance Counters

iii. SQL Server Management Studio Reports

iv. Monitoring SQL Server Logs

v. Using Dynamic Management Views (DMVs)

vi. DMV Examples

vii. Using SQLDiag

viii. SQL Server Report Examples

1. Server Level Reports

2. Database Level Reports

c. Using SQL Profiler

i. SQL Profiler Terminology

ii. Creating and Managing SQL Traces

1. SQL Profiler (GUI)

2. System Stored Procedures (Transact-SQL)

iii. Trace Templates

1. Standard (Default), SP_Counts

2. TSQL, TSQL_Duration, TSQL_Grouped,TSQL_Replay, TSQL_SPs

3. Tuning

iv. Demo: Creating Profiler Traces

1. Launching SQL Profiler

2. Connecting to a database instance

3. Configuring output options

4. Create a trace definition

5. Specifying events, columns, and filters

6. Scripting out trace

7. Scheduling Traces

8. Running and viewing a trace

9. Default Trace

10. Built in schema change report using default trace

d. Using the Database Engine Tuning Advisor

e. Application Design Tips

f. Managing Processes, Locking, and Deadlocks

i. Understanding Processes

ii. Monitoring Processes

iii. Managing Processes

iv. Understanding Locking

v. Understanding Blocking

vi. Transaction Isolation Levels

vii. Monitoring Locking Activity

viii. Understanding the Deadlock Process

ix. Avoiding Deadlocks

x. Deadlock Victims

xi. Deadlock Priorities

xii. Deadlock resolution

xiii. Monitoring DeadlocksTrace Flag at startup parameter

29. Automation of SQL Management

30. Management of SQL Analysis Servers

a. How to install analysis services

b. How to create databases in analysis services

c. How to backup databases

d. How to restore databases

e. How to schedule analysis servers database backup via SQL Server Agent

31. Decommissioning of SQL Server

a. Process

i. Business Approval

ii. Ensure no User connection / No usage

iii. Disable Monitoring third party, In case of Remedy, delete related CI

iv. Last backup the system and system configuration

v. Uninstall SQL Server

vi. Update Inventory

EOF - SQL Server Advance L3 Workshop in HCL, NOIDA – Oct 04 to Oct 08, 2010, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

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


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.