Introduction to database management systems and databases
– What is a database and a database management system (DBMS).
– Introduction to Microsoft SQL Server and its overall SQL Server Data Platform.
– History and Current trends of DBMS and Database Administration.
– Tasks of a Database Administrator.
Microsoft SQL Server 2016 Installation
What are the editions of Microsoft SQL Server 2016 Data Platform ?.
– Learn the hardware and software pre-requisities for a successful SQL Server 2016 installation.
– Understanding SQL Server 2016 components.
– Perform an successful initial installation of Microsoft SQL Server 2016.
– Understanding SQL Server version identifiers, installed directories and conventions.
– Add features to an existing SQL Server 2016 installation including Analysis Services and Reporting Services.
– Install Microsoft SQL Server 2016 Management Studio.
Microsoft SQL Server 2016 Architecture
– Understanding SQL Server system databases and business (non-system) databases.
– Understanding the SQL Server database structure.
– What are SQL Server filegroups and datafiles.
– What are SQL Server Transactional logs and database recovery models.
– What is the difference between a SQL Server instance and database.
– Client-Server Communication in SQL Server 2016.
How to create database objects using SQL Server 2016 Management Studio
Create two sample databases (“AdventureWorks” databases) for practice in the training.
– How to create tables and indexes using the SQL Server Management Studio (SSMS).
– How to manage indexes using SSMS including rebuilding indexes to reduce fragmentation.
– How to create primary key foreign key relationships between tables using SSMS.
– How to add new file groups in SQL Server using SSMS.
Microsoft SQL Server 2016 Security
What are instance-level principals and database-level principals ?.
– Understand SQL Server fixed server roles.
– How to create a new database user and map it with an instance login.
– What are SQL Server database schemas and learn about their association with database users.
– Under database role memberships and how to grant permissions to database users.
– What is SQL Server Configuration Manager and how to change the ownership of a SQL Server database instance.
– How to enable login auditing in SQL Server 2016.
Microsoft SQL Server 2016 database session administration
– Understand the differences between database connections and sessions.
– Learn about the details of various dynamic management views (DMVs) for monitoring database activity.
– How to kill (terminate) an SQL Server session id using SSMS.
– How to identify and run built-in reports in SSMS.
– Learn about the auto-commit, implicit and explicit transactions in SQL Server.
SQL Server 2016 Backup and Recovery Part 1
– Introduction to Backup and Recovery strategy for databases.
– How to detach and attach databases in offline mode.
– How to copy databases using the
Copy Database Wizard (CDW).
– Full Database backups using the SIMPLE Recovery Model.
– Full Database and Transactional log backups with FULL Recovery Model.
Differential database backups with FULL Recovery Model.
– How to view and maintain database backup history information.
– How to create maintenance plans for automating backups in SQL Server.
– How to create maintenance plans for purging old backups.
– Learn about the SQL Server Agent for scheduling backups.
SQL Server 2016 Backup and Recovery Part 2
– Introduction to SQL Server Database Restore and Recovery.
– How to perform a full database restore and recovery.
– How to recover from SQL Server transactional logs.
– How to recover a database using time based recovery option (STOPAT).
– How to use the checksum and compression options in backup operations.
Applying service packs and performing database upgrades
– How to apply SQL Server 2012 Service Pack 3 (SP3) to SQL Server 2012 RTM version.
– How to upgrade databases from SQL Server 2012 SP3 to SQL Server 2016 RTM.