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 Deadlocks – Trace 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