MS SQL Server DBA

Duration of the Training : 8 weekends

Introduction to Databases (3 hours)

  • Databases – Relational and Others
  • SQL Server vs. Other Relational Database Systems
  • The Database as Part of the System Architecture
  • The Role of the Database Administrator (DBA)

Introduction to Microsoft SQL Server (4 hours)

  • SQL Server Versions and Editions
  • SQL Server Services and Components
  • SQL Server Client Tools

Overview of Microsoft SQL Server 2012(1 Hr)

  • Database Engine
  • T-SQL programming interface
  • Business intelligence
  • Summary

Installing Microsoft SQL Server 2012(2 Hrs)

  • Editions of SQL Server 2012
  • Choosing hardware for SQL Server
  • Software prerequisites
  • Before installation
  • Installing SQL Server
  • After installation
  • Summary

Using SQL Server 2012 administration and development tools(1 Hr)

  • Using SQL Server Books Online
  • Using SQL Server Management Studio
  • Using SQL Server Management Studio to create solutions and projects
  • Using SQL Server Data Tools
  • Summary

Designing databases
Designing SQL Server databases(2.5Hrs)

  • Understanding SQL Server system databases
  • Understanding the SQL Server database structure
  • Adding files and filegroups
  • Detaching and attaching SQL Server databases
  • Understanding database recovery models
  • Summary

Creating your first table(2.5Hrs)

  • Developing a naming standard
  • Understanding schemas
  • Understanding SQL Server data types
  • Understanding column properties
  • Creating tables
  • Altering tables
  • Understanding computed columns
  • Adding constraints to a table
  • Understanding the FileTable
  • Creating database diagrams
  • Summary

Building and maintaining indexes (1 Hr)

  • Index structure overview
  • Adding index options
  • Adding included columns
  • Disabling and dropping indexes
  • Summary

Advanced database design topics
Transactions and Locks (4 hours)

  • Transactions Overview
  • Lock Types
  • Concurrency Issues
  • Transaction Isolation Levels
  • Locking Hints
  • Deadlocks
  • Nesting Transactions

Table compression (1 Hr)

  • Understanding row compression
  • Understanding page compression
  • Summary

Table partitioning (2 Hrs)

  • Creating a partition function
  • Creating a partition scheme
  • Partitioning tables and indexes
  • Summary

Database snapshots(1 Hr)

  • Understanding database snapshot prerequisites and limitations
  • Creating and viewing database snapshots
  • Dropping database snapshots
  • Reverting to a database snapshot
  • Summary

The SELECT statement(2Hrs)

  • Writing a SELECT statement
  • Sorting results
  • Filtering data with the WHERE clause
  • Creating aliases
  • Using the JOIN operator to return data from multiple tables
  • Limiting the data returned in your result set
  • Using UNION to combine result sets
  • Summary

Using Transact-SQL (T-SQL)
Advanced data retrieval topics(1 Hr)

  • Paging data
  • Writing expressions
  • Using variables
  • Summary

Modifying data(1 Hr)

  • Inserting data into SQL Server tables
  • Using sequence numbers
  • Updating data in tables
  • Deleting data from tables
  • Merging data
  • Summary

Built-in scalar functions(1 Hr)

  • Using date and time functions
  • Using conversion functions
  • Using string functions
  • Summary
Creating other database objects
Advanced T-SQL topics(1 Hr)

  • Aggregating and grouping
  • Using SQL Server temporary objects
  • Handling T-SQL errors
  • Summary

Views(1 Hr)

  • What are views?
  • Creating indexed views
  • Summary

User-defined functions(1 Hr)

  • Understanding user-defined scalar functions
  • Understanding table-valued functions
  • Limitations of functions

Stored procedures (1 Hr)

  • Working with stored procedures
  • Using the EXECUTE keyword
  • Parameterizing stored procedures
  • Dropping stored procedures
  • Summary

Data manipulation triggers(1 Hr)

  • Types of triggers
  • Creating triggers
  • Altering triggers
  • Dropping triggers
  • Enabling and disabling triggers
  • Summary

Database maintenance
Backup and Recovery (6.5 hours)

  • Backup Types
  • Database Recovery Models
  • Understanding the Recovery Process
  • The Restore Operation
  • Recovery Advisor
  • Performing Online Restore Operations
  • Recovering from Database Snapshot
  • Recovering System Databases
  • Planning a Backup Strategy

SQL Server Security (8 hours)

  • SQL Server Security Overview
  • Authentication Modes
  • Principals and Securable
  • Managing Permissions
  • Understanding Ownership Chaining
  • Controlling Execution Context
  • SQL Server Auditing
  • Security Best Practices

Database management
SQL Server Profiler (1 Hr)

  • Understanding SQL Server Profiler
  • Summary

SQL Server security(1 Hr)

  • Understanding principals
  • Creating server logins
  • Creating user-defined server roles
  • Creating database users
  • Creating built-in database roles
  • Configuring contained databases
  • Summary

SQL Server Agent(1 Hr)

  • SQL Server Agent components
  • Configuring jobs
  • Summary

Database Mail(1 Hr)

  • Database Mail components
  • Configuring Database Mail
  • Sending email using Database Mail
  • Monitoring Database Mail
  • Summary

Dynamic management objects(1 Hr)

  • Retrieving SQL Server metadata from DMOs
  • Querying server performance statistics
  • Querying server information
  • Querying performance information
  • Querying indexing statistics
  • Summary

Using a Standby Server (6hours)

  • Introduction to High Availability
  • Failover Clustering
  • Log Shipping
  • Database Mirroring

Implementing Replication (4 hours)

  • Overview of Replication
  • Implementing Snapshot Replication
  • Implementing Transactional Replication
  • Implementing Merge Replication
  • Implementing Peer-to-Peer Replication

Transferring Data with SQL Server (4 hours)

  • Overview of Data Transfer
  • Using the Import and Export Wizard
  • Performing Bulk Operations
  • The Copy Database Wizard
  • Introducing SQL Server Integration Services
  • Source-To-Target Mapping
  • Populating Dimension and Fact Tables
  • Identifying Data Changes