Price 20,000.00 GST

Course Features
language
Language: English
access_time
40 hours
spellcheck
Study Level: Intermediate
terrain
Certificate of Completion

 

SQL DBA (with Server) Complete Practical & Real-time Training

SQL DBA Training Highlights:

✓ Job Support

✓ In-depth Performance Tuning

✓ Complete Practical

✓ Always-On – WINDOWS Clusters

✓ Certification Support

✓ Always-On – LINUX RHEL Clusters

✓ Real-time Scenarios, Handouts

✓ Database Repairs, Azure Upgrades

✓ Study Material & Practice Labs

✓ Azure Data Sync, Failover, HA DR

ALL OUR TRAINING SESSIONS ARE COMPLETELY REAL-TIME and PRACTICAL.

Modes of Training:

LIVE Online Training Classroom Training Video Training

** Fast Track, One – One Trainings and Corporate Trainings are also available.

SQL Server DBA (SQL DBA) : Complete Course Plan

Course Description Duration

Module

1. Database Basics, SQL Server Architecture & T-SQL Plan A; B

2. Weeks Module 2 Basic SQL DBA : Backup- Restores, Jobs, Tuning & Security Plan A; B 2 Weeks Module

3. Advanced SQL DBA: Always-On, Repairs, HA DR – Errors Plan A; B 2 Weeks Module

4. Azure SQL Database Fundamentals and Azure Tuning Plan B 1 Week Module

5. Azure SQL DBA Concepts, HA & DR, Security, Power Shell Plan B 1.5 Week

Total Duration : 8.5 W * 5 Classes a Week. Mon – Fri

MODULE 1 : Database Basics, SQL Server Architecture, T-SQL

DAY 1: SQL DBA – JOB ROLES (INTRO)

Need for Databases & RDBMS Software; OLTP & OLAP; DWH and HTAP Databases; MS SQL Server – Technical Advantages; SQL Server Hosting : OnPremise (non-Cloud); SQL Server Hosting in Cloud: Azure; AWS; Editions and Versions – Licensing Plans; SQL DBA Job Roles and Checklist Plan; Roles & Responsibilities For SQL DBA; SQL DBA Job : Routine DBA Activities; Maintenance and Emergency DBA Activities; Course Plan and Weekly Mock Interviews & Resume; MCSA Certification Guidance and Virtual Labs;

DAY 2: SQL SERVER INSTALLATION

SQL Server 2019 Installation Guidance; SQL Server 2017 and 2016 Installation; Installation Pre-Requisites; Precautions; SQL Server Management Studio (SSMS); SSMS Tool: Installation; Use; Connections and Instances; Instance Types & Instance IDs; Windows Authentication Type and its Use; Mixed Mode Authentication Type and Use; Default Admin Account in SQL Server : “sa”; SQL Server Services and Service Accounts; File Stream and Collation in SQL Instances; Installation Errors; Config.INI Files @ DAY 30;

DAY 3: BASIC SQL QUERIES – 1

DDL; DML; SELECT; DCL and TCL; Creating SQL Databases and Tables; CREATE; ALTER; DROP Statements; INSERT; UPDATE; DELETE Statements; Data Inserts; Values and Table Scan; INTEGER; CHAR; FLOAT Data Types; INSERT and INSERT INTO Statements; Single Row Inserts; Multi Row Inserts; SELECT Statement for Table Retrieval; WHERE Examples : =; !=; <; >; <=; >=; AND; OR; NOT; IN; NOT IN Conditions; BETWEEN; NOT BETWEEN Conditions;

DAY 4: BASIC SQL QUERIES – 2

CHAR Versus VARCHAR Data Types; VARCHAR & NVARCHAR Data Types; GO Statement; SQL BATCH Concept; BIGINT; BIT; SQL_Variant Data Types; IS NULL; NOT NULL. LIKE; NOT LIKE; ORDER BY with ASC; DESC Options; DISTINCT; TOP and COUNT() Options; FETCH; OFFSET; NEXT ROW Options; UNION; UNION ALL and Sub Queries; Single Quotes; Double Quotes; Aliases; UPDATE : Conditional & Unconditional; DELETE and TRUNCATE Commands; ALTER Command DROP Command;

DAY 5: SERVER and DB ARCHITECTURE

Client Server Architecture; TDS Packets; SNAC and OLE DB Command for TDS; TDS Packets and SQL Query Processing & Protocols; SQL Native Client (SNAC); Query Optimizer (QO) and SQL Manager; Storage Engine; File and DB Manager; Transaction Manager and Lock Manager; Buffer Manager; SQL OS and IO Buffer; DB Data Files (mdf) and Log Files (ldf); Primary/Secondary Files (ndf) and Filegroups; Write Ahead Log (WAL) and Lazy Writer; Logs; Checkpoints; Virtual Log File (VLF); Log Sequence Number (LSN), Mini LSN;

DAY 6 : CONSTRAINTS & JOINS

Constraints; Keys – Table Data Integrity & NULL; NOT NULL Property on Tables; UNIQUE KEY Constraints: Importance; PRIMARY KEY Constraint: Importance; FOREIGN KEY Constraint: Importance; REFERENCES; OLTP Relational Tables; CHECK, DEFAULT and Identity Property; JOINS – Types and Real-time Usage; JOIN Types and JOIN Options in T- SQL; INNER JOIN – Examples, WHERE, ON; OUTER JOIN – LEFT, RIGHT & FULL; CROSS JOIN, “,” and CROSS APPLY; MERGE JOIN, LOOP JOIN, HASH JOIN; Query Tuning with JOIN OPTIONS;

DAY 7: VIEWS & INDEXES – TUNING 1

Indexes Architecture and Index Types; Clustered and Non Clustered Indexes; Included and Column Store Indexes; FILTERED and COVERING Indexes; UNIQUE Indexes and Query Optimizer; LIVE Online Indexes; Real-time use; B Tree Structures: Root; IAM Pages; Tuning Joined Queries and Conditions; Views on Tables – SCHEMABINDING; ENCRYPTION and CHECK OPTION; Orphan Views – Real-world Solutions; Cascaded Views and Encrypted Views; Metadata Access with System Views; Indexed Views / Materialized Views;

DAY 8: FUNCTIONS & PROCEDURES

User Defined Functions in SQL Server; Scalar Functions and Inline Table Functions; Multi – Line Table Valued Functions; Table Variables and Table Data Type; Variables & Parameters in SQL Server; OBJECTID; OBJECTNAME Functions; System Functions & Metadata Access; Stored Procedures – Purpose; Usage; IF .. ELSE and ELSE IF Conditions; Using Stored Procs with Parameters; Recompilation of Stored Procedures; Sp_help; Sp_helpdb and sp_helptext; Sp_recompile and sp_pkeys System SPs;

DAY 9: TRIGGERS & TRANSACTIONS

Triggers – Purpose and Real-world Usage; FOR/AFTER Triggers – Real time Use; INSTEAD OF Triggers – Real time Use; INSERTED; DELETED Memory Tables; Enable Triggers and Disable Triggers; Database Level DDL Triggers – Usage; Server Level DDL Triggers – Usage; COMMIT and ROLLBACK Statements; EXPLICIT & IMPLICIT Transactions; Auto Commit Transaction; ACID Options; Open Transactions and Query Impact; Query Blocking Scenarios @ Real-time; NOLOCK and READPAST Lock Hints;

DAY 10: LINKED SERVERS, CTEs Linked Servers – Creation and Tests; sp_addlinkedserver System Procedures; Linked Server Security, Remote Logins; RPC and RPC Out. Data Access Modes; Four Part Naming Conventions – Options; Using Linked Servers for Remote Joins; Tuning Remote Join Queries, Options; CTEs: Common Table Expressions; Using CTEs for Data SELECT, FETCH; Using CTEs for Insert, Update, Delete; CTEs for In-Memory Data Storage; Overview of Cursors and FETCH; Cursor Types and KEYSET Indexes; SQL Browser Service & SSCM Tool;

MODULE 2 : Basic SQL DBA : Backup- Restores, Jobs, Tuning, Security

DAY 11: BACKUPS – DB, File and FileGroup

Database Backups; File group Backups; Log File Backups; COPY_ONLY Backups; Mirror Backups; Split Backups; FORMAT; Partial Backups and Backup Media Files; UNLOAD; INIT; FORMAT; VERIFY; SKIP; COMPRESSION; CHECKSUM; Retain Days and ContinueOnError and Backup Validations; Backup History Tables in MSDB – Joins; Backup Audits. HOT and COLD Backups; Backup Devices – Creation and Usage; Database Recovery Models – DB Logging; Common Backups Errors and Solutions;

DAY 12: RESTORES & DB RECOVERY

Restore Phases – COPY; REDO; UNDO; RECOVERY and NORECOVERY Options; STANDBY; REPLACE Options in Restores; File; File Group and Meta Data Restores; Backup Verifications using GUI; Scripts; FileListOnly, VerifyOnly and HeaderOnly; PARTIAL / PIECEMEAL Restores – Use; Tail Log Backups. UNDO; REDO Phases; Restores using GUI and T-SQL Scripts; Point-In-Time Restore; Checkpoint LSN; Standby Restores and Read- Only State; Restores from SQL Server 2017 to 2019;

DAY 13: JOBS & MAINTENENCE PLANS

SQL Server Agent Service and Agent XPs; SQL Agent Jobs – GUI; Script Creations and Job Steps – Creation; Edits and Parse; Job Schedules and Email Notifications; Job Executions; Disable/Enable Options; Job History Purge. Job Activity Monitor; Database Maintenance – Backup Jobs; Scheduling Database Maintenance Plans; Backup Cleanup & History Clean Up Jobs; Backup Strategies For Minimal Data Loss; DB Mail Configurations and Alert System; DB Mail Profiles; SMTP Agent Operators;

DAY 14: PERFORMANCE TUNING 2 – AUDITS, PARTITIONS

Audit Long Running Queries : DMVs, DMFs; Activity Monitor Tool, Query Statistics; Logical & Physical I/O, DB IO, Waits; Recent and Active Expensive Queries; Plan Handle Executions, Performance; Resumable Indexes: ONLINE, RESUME; PAUSE, MAX_DURATION. Query Store; Partition Mechanism : Database Filegroups; Partition Functions & Partition Schemes; Aligned / Indexed Partitions – Importance; Partition SPLIT and MERGE, NextUsed; Partition Compression : ROW, PAGE; Data Archival & SWITCH. Partitioned Views;

DAY 15: PERFORMANCE TUING 3 – STATISTICS & FTS

Full Text Search (FTS) – Architecture; StopWords, Stemmer, Thesaurus For FTS; Indexer, Query Processor & Compilation; Full Text Catalogs and Filter Daemon; FDHost.Exe and Daemon Threads for FTI; Full Text Indexes (FTI), Crawler Threads; CONTAINS(), FREETEXT() with SELECT; In-Memory Tables : Creation, Tuning; Memory Snapshots at DB and Table Level; FileStream Files and MOT Filegroups; MEMORY_OPTIMIZED_ELEVATE Snapshot; Stats Updates : FullScan, NoRecompute; Temporal Tables Usage & History Tracking; Statistics : Index and Column Statistics;

DAY 16: PERFORMANCE TUING 4 – INDEX MANAGEMENT

Index Management : Performance Tuning; Internal and External Fragmentation; Fragmentation Audits : DMFs, Thresholds; Index Reorganization, Index Rebuilds; Database Maintenance Plans (DMP) Jobs; Index Page Count and Index Condition; Fast, Detailed Scans. Statistics Updates; LOCKS : Types, Concurrency Control; X, S, IS, IX, U, MD, Sch-M and Sch-S; Lock Audits : SP_WHO2, SP_LOCK; Deadlock Simulation and Prevention; Deadlock Audits & Events in Profiler; Isolation Levels and Query Blocking; Choosing Isolation Levels in Real-time;

DAY 17: PERFORMANCE TUING 5 – TUNING TOOLS

Tuning Tools : Workload and Trace Files; SQL Profiler Tool – Tuning Template, Events; DTA

Tool with Profiler : Recommendations; DTA with Procedure Cache & .SQL Files; Execution Plans – Internals. Plan Types; Query Costs : IO Cost and CPU Cost; Query Costs : SubTree Cost and Operator Cost; Numa Nodes, Boost Priority & Thread Count; Perfmon Counters and Real-time Tracking; Processor, Disk, Memory, Database Counters; Perfmon for Big Query Audits. Memory; Longest Running Transactions amd TPS; Log File, Tempdb, Memory Issues. Solutions; Performance Tuning – Final Tuning Checklist;

DAY 18: SECURITY MANAGEMENT

Authentication Types – Windows; SQL Server; Windows Logins and REGEDIT with T-SQL; SQL Server Logins : POLICIES & EXPIRY; Logins, Users, Roles and Schemas Creation; Server Roles and Database Roles – Usage; Password Resets and Security Scripting; Object Level Security; Column Security; Logon Failures and Server Management Logs; Grant, With Grant and Deny; Revoke Settings, CONTROL and OWNERSHIP; Authorization and Keys & Certifications for Data Encryption; Credentials and Proxies for Job Security; DMVs for Security Audits; Orphan Users; Containment Databases – Users and Roles;

DAY 19: DB MIGRATIONS, ORPHAN USERS

Logins and User Mapping Concepts; Detecting / Auditing Orphan Users; Resolving Orphan Users in Databases; sp_resolve_login and Logins RPT File; Containment Authentication Type; Creating Users without Logins. Roles; Containment Users – Permissions; Database Migration Options with SSIS; CDW : Copy Database Wizard @ SSMS; Database Detach and Attach Options; FORATTACH, sp_single_file_attach_db; SMO Method and Database Scripting; CDW SSIS Packages, SSIS Proxies Use; Scheduling Database Migration Jobs;

DAY 20: DB HEALTH CHECKS AND REPAIRS

Database Health Check : DBCC Commands; Allocation Errors, Consistency Errors; DBCC ShowContig, Extent Fragmentation; EstimateOnly, NoInfoMsgs with Tempdb; DDBC Page- GAM, SGAM, PFS, ML, Bitmap; DBCC Shrink, Suspect Pages and Repairs; Repair_Rebuild, DB States, Page Restores; Resource Governor : Real-time Usage; Resource Pools and Workload Groups; Query Priority and DOP, Grants; Classifier Function : RECONFIGURE; Policy Based Management (PBM); Facets and Conditions for Policies; Database Recovery Model Policies;

MODULE 3 : Advanced SQL DBA: Always-On, Repairs, HA DR, Errors

DAY 21: REPLICATION For HA – LEVEL 1

Replication Architecture and Topology; Publication Types – Purpose; Importance; DB Articles; Publications; Subscriptions; Distribution DB Configuration; Snapshots; Snapshot Replication and Repl Agents; Adding Articles to Existing (LIVE) Replica; PUSH; PULL Subscriptions. N/W Shares; Transactional Replication Configuration; Log Reader Agent – Configuration; Keys; Tracer Tokens Latency; Ranking Options; Replication Monitor – Usage and Options; Replication Warnings and Agent Alerts;

DAY 22: REPLICATION For HA – LEVEL 2

Merge Replication and Merge Agent Job; Replication Conflicts and ROWGUIDCOL; Subscription Reinitialization; Expiry Setting; Server Subscription & Client Subscription; Peer-Peer Replication Connections; Nodes; Replication Conflicts: Options; sp_MSRepl; MSSQL_ENGXXXXX Errors & Solutions; sp_changedbowner; backup initialization; Replication Conflicts and Priority Settings; Replication Verify – Row Count, Checksum; Disabling; Cleaning Replication Topology; Replication Strategies for HA and DR Plan;

DAY 23: LOG SHIPPING (HA – DR)

Log Shipping Topology for HA and DR; Primary and Secondary: Recovery Plan; Log Shipping Monitor; Jobs and Alerts; NORECOVERY Mode – Configuration; STANDBY Mode Configuration & Jobs; Log Shipping Jobs and Manual Failover; Log Shipping Mode Changes – cautions; Re-Restoring Log Backups for Recovery; LS Backup, LS Copy & LS Restore Jobs; TUF Files and Standby Options in LS; Broken Log Shipping Chains & Issues; Log Shipping Jobs – Errors & Solutions;

DAY 24: DB MIRRORING (HA – DR)

DB Mirroring Architecture For HA & DR; Log Shipping Versus Database Mirroring; TCP Endpoints; TCP Network Security; Heartbeat and Polling Concepts in DM; Automatic Fail- Over Procedures; Tests; PARTNER OFFLINE Conditions; Options; DB Mirroring Monitors and Commit Loads; SYNCHRONOUS & ASYNCHRONOUS; Mirroring Monitor; Stop/Resume Options; Need for Always-On & Higher Availability; DB Recovery without Witness. Failover; Mirroring Monitor Jobs – Real-time Usage; Database Mirroring – Errors & Solutions;

DAY 25: PATCHES, UPGRADES, CUs

Establishing Downtime For Maintenance; Precautions for Maintenance Activities; Service Packs and Patch / hotfix Activities; Cumulative Updates (CU); Hotfix Process, Instance Selectivity for Updates; Cautions, Verifications; Smoke Test and Rollbacks; SERVER Upgrades; VERSION Changes; Planning for DB Maintenance Activities; System Database REBUILDs using CMD; Silent Installation & Installation Repairs; SQLCMD Tool and Instance Connections; DAC Dedicated Administration Console & Verifications; Smoke Test, Rollbacks;

DAY 26: CLUSTER CONFIGURATION

Windows Clusters For HA and DR; Domain Controller (DC) Configuration; Active Directory (AD) Accounts; SAN [Storage Area Network] and LUN; Public IP Address; Private IP Address; Windows Level Clusters and MSCS Service; Need for DNS Hosts – Server Manager; SQL Clustering Service & RAID Levels; RAID Levels for Data Files & Log Files; SQL Cluster Groups; Domain Accounts; Active-Active; Active-Passive Clusters; Quorum and MSDTC Disks. LUN Grows; MSCS Service Startup; Drains; Need for Always – On Availability (AAG);

DAY 27: ALWAYS-ON AVAILABILITY – 1

Always On Availability Group [AOAG]; Synchronous and Asynchronous Modes; Replica Recommendations for Always-On; Backup Preferences & Location Options; Synchronization & Automated Seeding; Data Synchronization Settings for AOAG; Port Settings; Backup Strategies in AAG; AOAG Verifications and Dashboards; Adding Availability Replica; Database; Adding Availability Listeners and DNS; Automated Failovers; Manual Failovers; New Hosts and AAA Hosts in AOAG; Forward Lookup Zones – AOAG Domain;

DAY 28: ALWAYS-ON AVAILABILITY – 2

Understanding LINUX OS Environment; LINUX OS Components – Kernel, Daemons; BootLoader, Shell and Desktop Env; SQL Server Options on LINUX Platform; Installing SQL Server on RHEL Cluster; su and sudo Commands in LINUX Cluster; Creating Linux Packages, REPO Files; Installing mssql-server Packages; Service Enable and Activation Options; Firewall Configurations, sa Passwords; Installing SQL Server Tools in LINUX; ms- sqltools Installation: SQLCMD, BCP; UNIXODBC; Packages Installation, Verify; Running Test Connections and Queries;

DAY 29: SLA/OLA, RAID, PROCESS

Routine SQL Server DBA Activities; Maintenance SQL DBA Activities; Emergency SQL DBA Activities; End to End Process for SQL DBAs; Tiers & Levels of Production Support; Real- time Project Plan, Support Terms; Capacity Planning with SQL Server; SLA and OLA Process – Production; RAID Levels for SQL Databases; RAID for SQL Server Data Files; RAID for SQL Server Log Files; Bit Level and Block Level Parities; Speed and Safety Options with RAID; Database Size, RAM Capacity Options;

DAY 30: Ticketing Tools, Licenses

Ticketing Tools – REMEDY (Cloud); IR, CR and SR (Incident Management); SLA – OLA Reports and Tickets; Tickets : Properties, Ownerships; Impact, Urgency and Ticket Priority; Status, Events, Customers, Details; Incident Tickets and Resolutions; SQL Server Licensing Plans, Pricing; Device Type and Core Type Licenses; Device CAL and User CAL : Selections; CORE Licensing : Processor, Core; Multiplexing and Software Assurance; Pricing Options with SQL Server License; Version & Edition Comparisons;

DAY 31 & 32: REAL-TIME PROJECT (With Solution, For your Resume)

Our SQL DBA Online Training Course includes one Real-time Project on E- Commerce Database. This project involves Real-time DBA Issues and Solutions including the u sage of Ticketing Tools, 3rd Party Tools. We deal with Routine SQL DBA Activities, Emergency DBA Activities and Maintenance Activities with Solutions. Also include 200+ Errors and Solutions, Database Migrations, Upgrades, Always-On Availability Groups Implementation in a Clustered Environment. This Real-time Project gives greater in-depth insight on Real-world SQL DBA Job Work and can also be used in your Resumes. Very much helpful to clear your Interviews and MCSA Certifications.

PHASE 1 : Auditing Login Failures Monitoring Connectivity Issues Auditing Long Running Queries Memory Issues and Solutions PLE (Page Life Expectancy) MSDTC and Remote Connections Job Monitoring and Precautions Monitoring HA & DR Mechanisms Database and Schema Refresh Creating Dump Files and Audits Idle Sessions and Precautions Index Management and Integrity Deadlock : Monitoring, Prevention Monitor Server Health, Dashboards

PHASE 2 : Server Down : Issues, Solutions Database Down : Issues, Solutions Data Synchronization : Issues, Solutions Slow Running Queries : Issues, Solutions Database Response : Issues, Solutions Slow Replication : Issues, Solutions Replication @ HA-DR : Issues, Solutions Log Shipping @ HA-DR : Issues Important Events and Errors, Solutions Hot CPU and Processor Node, Priority CPU Thread Counts and Windows Fibers Contained Databases and Orphan Users Memory Dump Files and Solutions Always-On (AAG): Issues and Solutions

Module 4: Azure SQL Database Concepts

Applicable for SQL DBA Plan B

Day 33 : AZURE CLOUD & AZURE SQL DATABASE Introduction to Cloud. Need for Cloud, Advantages; Cloud Architecture Basics – Iaas, PasS and SaaS; Advantages of Microsoft Cloud – Azure Platform; Azure Products and Azure Services – MarketPlace; Comparing Azure with Google Cloud for SQL Server; Comparing Azure with AWS Cloud for SQL Server; Azure Sources – Types, Microsoft Market Place; SQL Database Implementations in Azure Platform; Logical Servers, Virtual Machines, Managed Instance; Azure SQL Database Architecture Components; Creating Azure SQL Server (Logical Server); Creating Azure SQL Databases and Pricing Tiers; Price Tiers: Basic, Standard, Premium, PremiumRS; DTUs Allocation for Database Size, Cost Models; Firewall Settings for Azure SQL Server (Logical Server); Adding Firewall Rules – IP for Remote Access; Password Resets and Azure SQL Server Name Format;

Day 34 : DTU ARCHITECTURE, ELASTIC QUERIES DTU : Data Transaction Units : Architecture, Pools; DTU – Memory and IO Resources for Reads & Writes; Bounding Box Model for Optimal Performance; Static Pools (DTU) and Elastic Pools (eDTU); eDTUs and Elastic Pool, per Database Settings; EDTU Cost, eDTU max/min Limits and Performance; Configuring Elastic Pools for Azure SQL Databases; Elastic Pools & Tier Selection – Recommendations; Elastic Scale for Azure SQL Database – Strategies; Vertical Partitioning and Horizontal Partitioning; Elastic Database Tool Libraries for Elastic Queries; Sharding – Topology for Elastic Query Processing; Split-Merge Servie for SaaS Software Applications; Elastic Database Features – ShardMap, ShardKey; MultiShard Queries and Elastic Transactions; LOOKUP, HASH and RANGE Strategies for Sharding;

Day 35 : AZURE SQL DB MIGRATIONS, AZURE DATA STUDIO Data Migration Assistant (DMA) Tool; On-premise to Azure SQL Database Migration; Logical Server, Virtual Machine, Managed Instance; Schema Generation and Compatibility For Migration; Generating Data Scripts and Assessment; Generate and Validate Schemas. Migrations; Migration Scopes : Schema, Data, Schema & Data; Compatibility Checks and Assessment Checks; Resolving Database Migration Compatibility Issues; Azure Data Studio Tool for Database Connections; Azure Data Studio Features – Insights, Metrics; Comparing Azure Data Studio with SQL OPS Tool; SSMS Tool for Azure SQL Database Connections; SQLOPS Tool for Azure SQL Database Connections; SSDT (Visual Studio) for SQL Database Connections; Query Editor for Azure Database Access – in Portal; Azure Database Connections with Other Tools;

DAY 36 : Azure SQL DATABASE TUNING, AZURE SEARCH Azure SQL Server Level Tuning Options; Azure SQL Database Level Tuning Options; Automated Tuning Options and Peak-Loads; Force Plan, Create Index and Drop Index; Query Performance Insight, Intelligence; Index Recommendations with CPU and IO; IO Metrics, CPU Metrics & Query Statistics; Data File IO, Log File IO, Custom Reports; Identify Long Running Queries, Intensive Queries; Query Level Recommendations and Query Costs; Azure Search Service – Configuration, Pricing Tiers; Azure Search for Data Import and Indexer Options; Suggester and Analyzer Index Modes for Tuning; Retrievable, Facetable, Filterable Indexes; Facetable and Searchable Indexes for Tuning; Change Tracking Options, Watermark Columns;

DAY 37 : AZURE SQL DATABASE VERSUS ON-PREMISE, EVENTS Azure SQL Server Architecture Differences; Network Protocols and DB Engine Differences; File Structure and Filegroup Allocations; Secondary Files and FileStream Differences; Table Architecture for Partitions, FT Queries; Query Processing Differences with TDS Packets; Query Monitoring and Resources – Dashboards; Unsupported Commands with T-SQL Queries; ; SQL Traces : Creation and Audits. Limitations; SQL Traces : Events, Class, Category, Filter, Conditions; Extended Events and Traces for T-SQL Query Analysis; Extended Events Packages, Targets, Actions, Sessions; Extended Events Architecture and Engine Sessions; TSQL and SP Debug Events with XEL Files for EventInfo; Global Fields and Event Filters with XEL Files @ Traces; XE Objects : Catalog Views, Dynamic Management Views; XE Profiler – Default Templates for Event Profiling;

DAY 38 : AZURE SQL DB VERSUS ON-PREMISE, STRETCH DATABASE Stretch Databases in Azure SQL Databases; Stretch Databases – Tuning Benefits, Cautions; able Level Migrations with Azure SQL Databases; Compute Performance Levels and DSU Pricing; Data Storage and Azure Database Snapshots; Geo Backup Of Tables and Hybrid Cloud Settings; Remote Data Archive Configuration in Azure; Database Master Key [DMK], Cold Data Migration; SQL Database Managed Instance; Native Virtual Networks and Scalability; Lift and Shift LOB Applications; Fully Managed PaaS Infrastructure; Frictionless Migration for On-Premise; Managed Instances – Key Features; vCore Purchasing Model for Azure SQL DB; Gen 4 and Gen 5 Logical CPUs, Resource Limits; Key Differences : On- Premise Vs Managed Instances;

Module 5 : Azure SQL DBA Concepts, HA & DR, Security, Power Shell

Applicable for SQL DBA Plan B

DAY 39: AZURE DB BACKUPS, RESTORES

SQL Storage Management (SMB); SMB File Storage and Azure Storage; Manage Storage Pools, Failed Storage Recovery; Space Availability on Data Volumes; Recovery Service Vaults, Policies with RA-GRS; Storage Accounts and Storage Containers in Azure; BLOB Data Storage: LRS, GRS, and “RA – GRS”; Cell Level Encryption and Always-Encrypted; Transparent Data Encryption (TDE); Automated Backups in Azure SQL Database; Long Term Backup Retentions. Backup cost, Frequency; Automated Backups – Retentions, Audits. LTR Policy; Restore Points and Azure SQL Database Recovery; Local Backups, BLOB Storage Backups, Restores; Encrypted Backups in Azure SQL Database; Database Level Exports in Portal, SSMS; Server Level Imports with Portal, Azure Studio;

DAY 40: AZURE SQL DATABASE – HA and DR

Failover Groups and Active Geo-Replication; Read-Write Failover Policy, Automated Failover; Geo Replication Configurations – Azure Locations; Primary-Secondary and Read / Write Modes; GeoReplication : Monitoring and Forced Failover; Perform Disaster Recovery with (DR) Drills; System Views for Audit of GeoReplication; Backup Errors, Geo Replication Errors – Solutions; SQL Data Sync Service and Sync Groups; HUB, MEMBER and SYNC DATABASE; Sync Direction, Sync Schema and Sync Interval; bi-directional data transfer with Azure SQL DBs; Conflict Resolution Policy with HUB and MEMBER; Cloud to Cloud and Cloud to On- Premise Synchronization; Hub to Member and Member to Hub Sync Policies; Azure DB Sync for Load Balancing and Failover; Common Errors and Solutions – POC;

DAY 41: SECURITY MANAGEMENT, ROW LEVEL SECURITY

Firewall and Virtual Network Configurations; Server-level Firewall & Database Firewall Rules; System Stored Procedures for Firewall Settings; Logins, Users, Roles and Permissions using T-SQL; Creating Logins without Logins. Containment Users; GRANT, DENY, REVOKE and WITH GRANT using T-SQL; Dynamic Data Masking, Transparent Database Encryptions; Data Masking Functions and Column Level Security; Vulnerability Assessment (VA) and Threat Detections; Implementing RLS – Row Level Security; Creating Users with & without Logins; Using SECURITY Schema for RLS Functions; Creating Security Predicates and Filters; Creating Security Policies for RLS; EXECUTE AS options for RLS. STATE ON/OFF; Data Level Encryption and Connection Encryption; Troubleshoot Encryption Errors in Azure SQL DB;

DAY 42: AZURE POWER SHELL, AZURE CLI

Azure Cloud Shell – Concepts, Architecture; Azure Power Shell – Install and Configure; Installing and Scripting with Power Shell; PowerShellGet and Version Paths; Cloud Shell to run the Azure Power Shell; Power Shell Scripts for Configurations; Power Shell Scripts for Job Schedules; Power Shell Scripts for Firewall, ARM; Azure CLI – Purpose, Real-time Use; Command Line Interface for Automations; Azure CLI – Downloads and Installations; Azure CLI – Command Prompts, Azure Login; Command Line Interface for Automations; Authorization Code, CLI Communication; Azure telemetry Commands and Usage; Help, Show, Create and Firewall Commands;

DAY 43: AZURE VIRTUAL MACHINE, OTHER CLOUDS

Plan for IaaS, Azure VM. Install Azure Virtual Machines; Install/Configure Windows Server, Readhat, Ubuntu; SQL Server on Azure VM – Config Settings, Elastic Pools; Resource

Groups, Resource Pools and eDTUs; Verify SQL Instances in Azure VM. sysadmin Account; Azure Storage Blades and Pricing Options; Azure VM – Settings, Power Shell Scripts; Azure SQL Database Creation in Azure VM; Azure Virtual Machines ; Security Options; Azure SQL Database in VM to SQL Cloud; Azure SQL Database in VM to on-Prem; Deployment Automation Process, Automation Scripts; SQL Database in AWS and Google Cloud; Azure Virtual Machine Configuration Settings; Azure SQL Database Advantages in Virtual Machines;

DAY 44: AWS, GOOGLE CLOUD For SQL SERVER

Comparing Azure SQL DB with AWS Cloud; Comparing Azure SQL DB with Google Cloud; RDS Implementation for SQL Server Databases; “License Included” licensing model of AWS; Amazon RDS for SQL Server in the AWS Console; Amazon RDS for SQL Server Pricing; Reserved Instances and Database Storage; Minor and Major Versions of SQL Server; Google Cloud Platform for SQL Server; SQL Server Virtual Machines in Google Cloud; Google Grade Infrastructure, DB Migrations; Data Transfer with in-out Policies; SQL Server Compute Engine in Google Cloud; Setting Up Windows Firewall, SQL Tools; SQL Server Creation and DB Creation, Access; SQL Server Tables Creation, Data Storage; SQL Server Migrations with Google Cloud;

chat_bubble_outlineReviews

Average Rating

0
No Votes 0 Votes
20,000.00 GST
0 Ratings

Detailed Rating

5 Stars
0
4 Stars
0
3 Stars
0
2 Stars
0
1 Stars
0

There are no reviews yet.

Be the first to review “SQL server DBA”

Your email address will not be published. Required fields are marked *