We offer Online MS SQL Server DBA Training by Microsoft Experts Learn MS SQL Server DBA Certification Training with Course Material Pdf Attend Demo Free Live Tutorial Videos Download Best MS SQL Server DBA Interview Questions by Spiritsofts institute Reasonable Fee
Spiritsofts is the best Training Institutes to expand your skills and knowledge. We Provides the best learning Environment. Obtain all the training by our expert professional which is having working experience from Top IT companies. The Training in is every thing we explained based on real-time scenarios, it works which we do in companies.
Experts Training sessions will absolutely help you to get in-depth knowledge on the subject.
Course Content
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(From Sql server 2005 to Sql server 2017)
- Pre-requisites, Installation of Server, Post Installation configuration and verification.
- GUI Installation,
attended/unattended Installation, INI based installations. SlipStream(Simple and Advanced) Installation, Sysprep Installation - Hands-on Exercise – 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, Contained Database, SQL Server Database Architecture, Pages, Extents, Filegroups, Datafiles Architecture, Transaction log Architecture, Creating Database, Modifying Database, Adding Files, Moving and renaming of Database files, Database modes, Recover corrupted databases, managing suspect pages, suspect users, 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.
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, access admin, security admin, 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
- 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 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
SQL Server High Availability
• Server Clustering concepts
• Windows Server Failover Cluster
•Failover of SQL Server Cluster
• Log shipping concept and setup
• Database Mirroring
• The Witness Server and Automatic Failover
• Configuring Database Mirroring
• Transaction Safety Level
• High Availability Operating Mode
• High Protection Operating Mode
• High-Performance Operating Mode
• Monitoring Mirroring
• Database States for Database Mirroring
• Comparison between Log shipping and mirroring Replicating Information
• Replication concept
• Distributor Administration
• Creating and Managing Publications and setting up of various types of replication
• Subscribing to a Publication
• Transforming Published Data
• Managing Replication Monitors
• Always on High availability
• SQL Server Profiler
• Optimal Data Storage
• Windows Perfmon Monitors
• DMV’s
• Query Analyzer
• Database Engine Tuning Advisor
• Index Optimization
•Different types of Indexes
•Creating Indexes
• Partitioned Indexes
• Lock optimization
• Server Clustering concepts
• Windows Server Failover Cluster
•Failover of SQL Server Cluster
• Log shipping concept and setup
• Database Mirroring
• The Witness Server and Automatic Failover
• Configuring Database Mirroring
• Transaction Safety Level
• High Availability Operating Mode
• High Protection Operating Mode
• High-Performance Operating Mode
• Monitoring Mirroring
• Database States for Database Mirroring
• Comparison between Log shipping and mirroring Replicating Information
• Replication concept
• Distributor Administration
• Creating and Managing Publications and setting up of various types of replication
• Subscribing to a Publication
• Transforming Published Data
• Managing Replication Monitors
• Always on High availability
• SQL Server Profiler
• Optimal Data Storage
• Windows Perfmon Monitors
• DMV’s
• Query Analyzer
• Database Engine Tuning Advisor
• Index Optimization
•Different types of Indexes
•Creating Indexes
• Partitioned Indexes
• Lock optimization
Managing Services
• Server/Engine Connectivity issues
• Security/Firewall access issues
• Starting and Stopping Services through
• Configuration manager
• Net Command
• Management Studio
• StartUp parameters
• Starting SQL Server in single-user mode
• Starting SQL server with minimum configuration
• Case Study: Moving System Databases from one location to another location.
• Case Study: Starting SQL server Without System Databases
• Server/Engine Connectivity issues
• Security/Firewall access issues
• Starting and Stopping Services through
• Configuration manager
• Net Command
• Management Studio
• StartUp parameters
• Starting SQL Server in single-user mode
• Starting SQL server with minimum configuration
• Case Study: Moving System Databases from one location to another location.
• Case Study: Starting SQL server Without System Databases
Upgrading SQL server
• Patching SQL servers with Service packs and Cumulative updates.
• Inplace up-gradation, Side by Side Migration.
• Migrating Logins
• Creating Linked Servers
• Import Export Wizard
• Migrating Jobs & Logins using SSIS packages
• Patching SQL servers with Service packs and Cumulative updates.
• Inplace up-gradation, Side by Side Migration.
• Migrating Logins
• Creating Linked Servers
• Import Export Wizard
• Migrating Jobs & Logins using SSIS packages
Performance Tuning
• Locking, Blocking, Dead Locks
• Database Tuning Advisor
• System Health Checks
• Performance Monitor
• New Dynamic Management Views
• Locking, Blocking, Dead Locks
• Database Tuning Advisor
• System Health Checks
• Performance Monitor
• New Dynamic Management Views
For More Details