Download PDF version
Objective
This workshop covers all the advanced concepts of SQL Server 2008 Administrations. This workshop is created taking best of the real world scenarios. Attendees will learn lots of insights of performance tuning and will gain knowledge of new advanced tricks used by expert production engineers. Attendees will be able to go back and right away apply the newly learned knowledge in production environment.
Description
This 3 day workshop covers best of the best concepts and practices from popular courses offered by Solid Quality Mentors. Instead of learning theory only, the workshop focuses on providing real world experience by using demos and scenarios derived from customer engagements. The workshop is uniquely structured. Sessions are discussion based and designed to be an interactive between instructor and participants for an optimal learning experience.
The workshop is intended to be immersion-based where participants will have plenty of opportunities to get deeply involved in the concepts presented by the instructor. The best participants will receive special educational material from the instructor as further follow up.
Each learning session is followed up with Hands-On Lab (HOL). Each Hands-On Lab is carefully crafted to give maximum exposure to the subject discussed in session. After doing Hands-On Lab the learning concept will be strengthen and understood. This way attendee can be productive and ready to excel the tasks at work based on the learning experience from the workshop.
The last hour of the workshop is kept reserve to solve attendees’ real world problems and pressing issues at work. We will sit together and try to resolve them. If the subject is interesting to other attendees then we will also discuss generically. We will get our hands dirty with code and will do our best to reach to the solution.
Target audience
This seminar is intended for following audiences:
• SQL Developers – Professionals who are responsible for writing optimal SQL Query
• SQL DBA – Professionals who are responsible for performance of the server
Agenda
Day 1: Instant and Server Level Administration
Do you know? – 10 Questions in 10 Minutes
This session is very unique and serves as an ice breaker between instructor and attendees. This unique session contains 10 different performance related questions. Attendees are will participate and discuss the questions and its answers. These 10 minutes also serves as the introduction to the class as well gives idea to attendees what they are learning in next 3 days.
The Beginners Camp – Installation, Instances and Components
This entire session is for beginners who want to start installing SQL Server from the beginning. Instructor will go over various steps to install SQL Server and enable DBA with various interesting details regarding SQL Server. This session is sometime extremely useful for the DBA who has many years of experience but have not have installed SQL Server before. This session will cover details of the various components of SQL Server.
LAB: Installing SQL Server on your system
Instance Level Settings and Database Level Settings
This is very unique session included in this course. Hardly any book or course covers the various settings which need to be adjusted right after the SQL Server Installations. This session will highlight all the important topics which are important to understand. We will discuss settings like Forced Parameterized, Auto Update Stats, Fill Factors, Memory, Worker Thread, Connections, Memory Settings, Processors and various Security settings. This session will enable you with all the inner insight a DBA needs to maintain server.
LAB: Configuring database for optimal settings
Policy Management – Prevention and Post Analysis
In this session we will go over the advanced concepts of policy management. We will be discussing how to configure policy management in SQL Server. Using Policy Management we will be able to prevent future violations. We can prevent violation at the run time as well can go back and analysis them. In this session we will go very deep and also learn how to auto fix the violations of the policies.
LAB: Create policy to prevent incorrect naming convention of Stored Procedure
LAB: Validate the recovery model of all the database
Day 2: Data and Security Administration
Backup and Restore – A Extreme Important Task – The Primer
Everybody takes backup and everybody knows they are safe! This state of confidence is lost when very first time someone tries to restore the database. Backup and Restore even though extremely important is not properly developed at most of the organizations. There are many different scenario for taking backup and many different steps to restoring the backups. This session will properly explain various methods of taking backup and various details about restoring the database backup.
LAB: Take various kind of backup and Restore it
Backup and Restore – A Extreme Important Task – The Advanced Primer
We will start talking about various disaster scenarios and proper backup methods for the same. We will go over very important scenario where instructor will take create disaster and recover database in point of time. This session will explain the importance of the restore procedure. This session will cover the concepts of the tail backup as well.
LAB: Point in time recovery for database recovery.
Database FileGroup – An Architectural Decision
Filegroup management is very important decision should be taken at the time of designing the database. Most of the database when they start, they are not build keeping the scalability in mind. In this session we will learn how to add multiple filegroup and manage them. Additionally, we will learn backup and restore at individual file level.
LAB: Creating Multiple file groups and managing it.
Understanding Security – A Bulletproof Solution
In this short session we will talk about proper solutions to avoid any security loop halls in your database. We will understand the importance of Schema and various roles. We will go over the rights and understand nested hierarchy of the same. We will also discussed about Encryption and Decryption and understand the fundamentals of the same using live demonstrations. Additionally, new feature of Transparent Data Encryption will be discussed in depth.
LAB: Creating new user with limited rights.
Question and Answers & Additional Lab Time
Here is your chance to ask your questions to the instructor which are so far not covered in any of the sessions. You can ask as many as questions as you like as well bring your real world problems. The instructor will guide you towards attaining resolutions to your problems.
Day 3: Performance Tuning Advanced Administrator Technique
Performance Tuning Tools- A walk through
There are various performance tuning tools available in SQL Server. We will go over many all of them in depth and learn the advance techniques. We will learn about various counters, system monitors, SQL Server Profilers, SQL Trace, as well all the new enhancement available in SQL Server 2008 Management Studio. We will also learn about various reporting tools which enables DBA with knowledge of current happening in SQL Server. We will also understand data collection architecture. This session will enable you with many tool at your dispense to use it for server health detection.
LAB: Learn Usage of Performance Tuning Tools
Index Maintenance – A Developers Job Accomplished by DBA
T-SQL Queries are written by developers and when server gets slow the DBA is responsible for the slowness. DBA creates the indexes and developer changes query. DBA keep on changing indexes to match the queries created by developers and they never catch up. This session is going to precisely talk about this situation and propose the solution for the same.
In this session we will learn fundamentals of the Indexes and few important tasks like
Identifying Important Indexes
Identifying Unused Indexes
Table without Indexes and many more maintenance tasks.
LAB: Identify Optimal Indexes and Tune Server Indexes
High Availability – A Primer
This session will overview on high availability scenario. We will discuss about log shipping, mirroring and replication. Instructor will go over various different scenarios where high availability can be implemented. In this session instructor will demonstrate working example of logshipping, mirroring and replication in the class. We will also observe failover as well in live demonstrations.
Your Problems and Our Solution
This is the final session. During this session instructor will address all real world problem of attendees and will propose solutions of the same. If you have any performance issue in your production server, this is must attend session to get resolutions.