Responsibilities of Database Administrator, Types of DBAs, History of SQL Server, Editions of SQL Server, Tools of SQL Server, Differences between Standard and Enterprise editions, Instances types in SQL Server, Default Instance, Named Instance, SQL Server Services, Instance aware services, Instance unaware services
Pre-requisites, Installation of Server, Post Installation configuration and verification
Hands-on Exercise – Install SQL Server on Linux, Install SQL Server on Windows, Configure the Server and verify that it is up and running
Describe Database, Types of Database and Brief explanation, System Databases, User Database, Sql Server Database Architecture, Pages, Extents, File groups, Transaction Architecture, Creating Database, Modifying Database, Adding Files, Moving and renaming of Database files, Database modes, Real time Scenario
Hands-on Exercise – Create a database schema in SQL Server, Create a table to store details of a student personal information, Add dummy data to the table, Move database file to another location, Rename database file
What is Import and Export of table data, Copy or Move a database, Tools and Techniques for data transfer
Hands-on Exercise – Import a table from a saved database file in the workspace, Export data from workspace to save in a database file
Authentication Types in SQL Server, Types of Login, Windows Login, SQL Login, Creating Users and Logins, Server roles, Password policy, Understanding Database and Server Roles, Permissions, Working on access level issues, Orphan users Finding and Fixing
Hands-on Exercise – Use SQL Server Management Studio, Create a login, Create database users, Assign different roles (owner, reader, accessadmin, securityadmin, denydatawriter, denydatareader), Provide permission level and access level, Create an orphan user, Find the orphan user
Database Backups, Why we need backups, Types of Backup, Full Backup, Differential Backup, Transaction Log Backup, Copy-only, Mirrored, Split and Tail log Backups, Differences between backups, Backup Strategy, Understanding how the data moving from Log to Data File,CHECKPOINT, Monitoring the space usage of Log File and fixing, Checking the backup files VALID or CORRUPTED, Backup storage tables
Hands-on Exercise – Perform database backup, Check transaction log backup, Monitor space usage of Log files, Use backup storage table
Types of Recovery Models, Full, Bulk Logged, Simple, Differences between Recovery Models, Setting Recovery Models according to the Scenarios with examples, Performing Restorations, Types of Restoration, Attach and Detach, Shrinking files, Point-in-Time Recovery
Hands-on Exercise – Set a recovery model, Perform point-in-time recovery, Perform restoration from the last saved state
What is monitoring sql server, Dynamic Management Views (DMV), Dynamic Management Functions (DMF), Performance Monitor, Activity Monitor, Configure database mails, alerts and notification
Hands-on Exercise – Create a DMV, Write Functions to monitor sql server activity dynamically, Configure mails, alerts and notification, Generate alerts and notification, Send email
SQL Server Profiler, SQL Trace stored procedures, Using Traces, Optimize SQL Server workloads
Hands-on Exercise – Use SQL server profiler, Create trace stored procedures and run them Optimize workloads of server
SQL server audit feature, Implement audit, Audit data access, Implement encryption
Hands-on Exercise – Implement audit, Audit data access, Implement encryption
Common database maintenance tasks, Ensuring database integrity, Maintaining Indexes, Automating common tasks
Hands-on Exercise – Create index on table column, Update index after inserting new records
Implementing and Managing SQL Server Agent Jobs, Managing job on multiple servers
Hands-on Exercise – Implement Server Agent Jobs Manage Jobs on same server
Project : SQL Server Audit
Industry : General
Problem Statement : How to track and log events happening on the database engine
Topics : This project is involved with implementing an SQL Server audit that includes creating of the TestDB database, triggering audit events from tables, altering audit, checking, filtering, etc. You will learn to audit an SQL Server instance by tracking and logging the events on the system. You will work with SQL Server Management; learn about database level and Server level auditing.
Project 2 : Managing SQL Server for a high tech company
Industry : Information Technology
Problem Statement : An IT company wants to manage its MS SQL Server database and gain valuable insights from it.
Description : In this project you will be administrating the MS SQL Server database. You will learn about the complete architecture of MS SQL Server. You will be familiarized with the enterprise edition of SQL Server, various tools of SQL Server, creating and modifying databases in real-time.
This Intellipaat SQL Server DBA training will give you hands-on experience in working with the SQL Server Database. You will master the concepts of SQL Server installation, deployment and authentication in various business scenarios. You will also learn to manage, maintain, secure, monitor and automate SQL Server in the enterprise setting. This entire course content is in line with the requirements of clearing the Microsoft Certified Database Administrator (MCDBA) Exam.
You will be working on real-time SQL Server DBA projects and step-by-step assignments that have high relevance in the corporate world, and the course curriculum is designed by industry experts. Upon the completion of the training course, you can apply for some of the best jobs in top MNCs around the world at top salaries. Intellipaat offers lifetime access to videos, course materials, 24/7 support and course material upgrading to the latest version at no extra fee. Hence, it is clearly a one-time investment.
This course is designed for clearing the Microsoft Certified Database Administrator (MCDBA) Exam. The complete course is created by industry experts for professionals to get top jobs in the best organizations. The entire training includes real-world projects and case studies that are highly valuable. You will also receive course completion certificate by Microsoft for Administering Microsoft® SQL Server® 2014 Databases.
Upon the completion of the training, you will have quizzes that will help you prepare for the Microsoft certification exam and score top marks.
The Intellipaat Certification is awarded upon successfully completing the project work, after reviewed by experts. The Intellipaat certification is recognized in some of the biggest companies like Cisco, Cognizant, Mu Sigma, TCS, Genpact, Hexaware, Sony, Ericsson and others.