About the Program
This is a detailed MS SQL Server DBA training course from Intellipaat that will help you understand all about database administration and become a Database Administrator. You will get a clear understanding about installation, functioning and authentication types in SQL Servers. This course also covers managing, maintaining and securing of databases and automation of SQL Server. Further, you will also receive official course material issued by Microsoft for Administering Microsoft® SQL Server® 2014 Databases.
Learning objective
By the end of the course, you’ll be able to:
- Roles and responsibilities of SQL Server DBA
- Various SQL Server tools available
- Query Design and Query Performance Tuning
- Disaster Recovery and High-availability solutions
- DBA creation, modification and file addition
- SQL Server security authentication measures
- Importance of backups and restorations
- Job monitoring and SQL Server Agent Clustering
- Creation of users and logins
Program Highlights
About The Course
Course Modules
- Starting with SQL Server
- 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
- Installing SQL Server
- 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
- Functioning of Databases
- 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 a database file to another location, Rename database file
- Importing and Exporting Data
- 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
- Security
- 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
- 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
- Recovery and Restoration of Database
- 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
- Monitoring SQL Server 2014
- 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
- Tracing SQL Server Activity
- 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
- Auditing Data Access and Encrypting Data
- SQL server audit feature, Implement audit, Audit data access, Implement encryption
- Hands-on Exercise – Implement audit, Audit data access, Implement encryption
- Database Maintenance
- 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
- Automating SQL Server 2014 Management
- Implementing and Managing SQL Server Agent Jobs, Managing job on multiple servers
- Hands-on Exercise – Implement Server Agent Jobs Manage Jobs on same server
- MS SQL Server DBA Project
- Project : SQL Server Audit
- Industry : General
- 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.
Course Certificates
SmartBrains is associated with The National Skill Development Corporation (NSDC) as the Training and Certification partner for various job oriented training programs across various sectors including Oil & Gas, Power, Renewable Energy, Hydrocarbon, IT & ITs, Electronics, Telecom, Agriculture, Life science etc. offering assessment based Training & certifications for a gamut of job profile.
Who should join?
- Working professionals in Domains: Software Development, Database, Admin, Web developers, Software Tester & Analyst.
- Students (Computer Science Engineering & Diploma in Computer Science Engineering) who want to develop their career in Software development, database, Web Developer, Software Tester & Analyst.
For Corporate queries
A-25, Sector-59, Noida (UP),India
Pin Code-201301
Connect with Program Advisor
training@smartbrains.com
+91 8955560560 | +91 9891108700
www.smartbrains.com
Nodal Centers
Noida, Dehradun
Guwahati, Pune
Course Features
- Lecture 0
- Quiz 0
- Duration 50 hours
- Skill level All levels
- Language English
- Students 0
- Assessments Yes