Mastering SQL Server Administration & Optimization

Master tools to effectively deal with your real-world SQL hurdles: creation, management, monitoring, and more

As the premier enterprise database product, Microsoft’s SQL Server is not a new product. Professionals have relied on it for years to create and manage databases. Its capabilities have grown considerably over the years into a powerhouse of tools for those who know what they’re doing. But If you’re creating a database using SQL Server, no matter which version, you’ve got a lot to know. In this two-day program, you will discover and take advantage of the hidden opportunities of SQL. This program, specifically designed for the busy manager or director, offers an intense update of how to use SQL tools to your advantage in your own situation immediately. You’ll get right to the important stuff — what you need to monitor and manage your database, great tools like Performance Studio, Systems Monitor, Database mirroring, and much more. All are key to creating and administering the flexible, reliable database you need.

The nuts-and-bolts mastery you need for a real-world database – this is not a test prep course!

You’ll dive right in. This is two full days of intensive examination and technical know-how that will kickstart your SQL project and give you the information you need to create and manage a fluid and reliable database within your existing structure. You will understand the architecture and features of Microsoft’s enterprise database product as you see how to create databases, tables, views, indices, and more. You’ll know how to properly use its tools to make things easier no matter which version you’re working with. Whether rebuilding your current database or employing SQL Server for the first time, you’ll leave with the skill sets and know how to get it done right. From planning to implementation to monitoring...you can be a turbocharged SQL Server Administrator.

Twenty-Four Powerful Demonstrations Get what you need to know firsthand from an expert — Key deliverables with no time wasted! Detailed examination of features and benefits of Microsoft’s enterprise database product – effectively implement and maintain SQL Server

• Total Cost of Ownership (TCO) of SQL Server will be reduced – learn to automate administrative tasks
• Share information with non-Microsoft database and data analysis products
• Assure data security with security configuration procedures
• Enforce regulatory compliance using built-in tools in SQL Server
• Centralized management of multiple servers made easier with CMS Servers
• Detect, locate and better react to poor performing queries, and improve performance
• Implement indexes properly at the point of database creation
• Better create and manage the integrated SQL database for the long haul

Install SQL the right way, avoiding costly, time-consuming errors

This workshop was developed to help you install the right way, avoiding many costly and time consuming errors. We’ll be getting right to the most important stuff. Install SQL Server the right way, effectively manage, tweak performance and monitor. Get everything you need, delivered in this comprehensive jumpstart program. Those who will benefit most from this program include:

• Database Administrators
• IT Managers
• Network Administrators
• Helpdesk professionals
• Anyone involved in database performance.

This course teaches you to quickly master:

• The Architecture
• Databases using GUI and T-SQL
• Centralized Management
• The SQL Profiler/Performance Studio
• Security Configuration
• Automated Administration
• Job, Operators, and Alerts
• Improving Querie Performance
• Installation Troubleshooting
• Implementing Indexes
• Database Mirroring
• Regulatory Compliance
• And Many More Tools

I. SQL Server: An Introduction

• Understand relational databases
• Discover the SQL Server features and benefits
• Why SQL Server’s architecture must be understood
• Learn about the new features introduced in SQL Server 2005, 2008 and 2008 R2

II. Installing SQL Server

• Uncover the differing hardware requirements
• Discover the various installation methods
• Verify and troubleshoot installation problems
• Configure the SQL Server management environment
• Tune Windows Server for support of the SQL Server software

III. Managing Database Files

• Data structures demystified
• Create databases
• Manage databases with ease
• Work with database files and transaction logs
• Become skilled in database optimization techniques
• Capacity planning
• Performance considerations

IV. Administration Tasks

• Learn the 3 most important configuration tasks
• Automate mundane maintenance tasks
• Create maintenance plans that will save you hours of effort
• Use alerts to warn you of potential problems
• Troubleshoot SQL Server jobs, operators and alerts
• Understand policy-based management (PBM)

V. Back Up Your Databases

• Avoid data loss through effective backup and restoration plans
• Explore various methods used to back up databases
• Learn to recover SQL Server when it fails or will not start
• Understand the essential system databases and how to recover them

VI. SQL 101

• Understand the components of the SQL language
• Create SQL queries visually
• Learn the top 10 SQL commands and their syntax
• Uncover SQL tips and tricks of the gurus

VII.Security and SQL Server

• Understanding and implementing authentication
• Assigning logins to users and roles
• Assigning permissions to users and roles
• Managing security within SQL Server
• Managing security with views and stored procedures
• Managing application security
• Understanding SQL Server attacks (injection, brute force, data theft, etc.)

VIII. SQL Server Performance Tuning

• Learn why database monitoring is important
• Monitor with the System Monitor
• Use the SQL Profiler
• Take advanced of the Database Engine Tuning Advisor (DTA)
• Understand the recommendations of the DTA

IX. Data Transfer and Manipulation

• Introduction to ETL processes
• Understand the components of SQL Server Integration Services (SSIS)
• Exporting and importing data
• Introduction to distributed data
• Introduction to SQL Server replication
• SQL Server replication agents
• SQL Server replication types

X. High Availability Solutions in SQL Server

• Consider server virtualization for increased capabilities
• Use multiple servers for redundancy
• Learn about clustering an how it’s implemented
• Implement hardware redundancy within servers

Dynamic In-Class Demos

• Installing SQL Server right the first time
• Optimizing the Windows Server for SQL Server
• Troubleshooting installation problems
• Working with Jobs, Operators and Alerts
• Using PowerShell for SQL Server administration

• Working with command line as a DBA
• Using the Surface Area Configuration Tool
• Using SP_Configure
• Central Management Server
• Implementing Policy Based Management Server
• Creating Tables, Databases, Views and Indexes

• Tuning Inquiries

• Using the SQL Profiler

• Using Database Engine Tuning Advisor
• Using the Resource Governor
• The Performance Studio
• The System Monitor in action
• Performing SQL Injection Attacks

• Securing SQL Server Service
• Event Viewer Logs
• Automating SQL Server Administration
• Performing Backups and Restorations
• Implementing Database Mirroring
• Implementing Log Shipping

N/A