SQL Server Administration for Developers and DBAs

Zenler Player

Your course is loading. Hang tight.

Who is this course for ? Preview
Who is Greg ? Preview
What will I learn in this course ? Preview
Configuring your lab environment
Labs overview and instructions

Module introduction
What is SQL Server?
What is T-SQL?
Types of SQL statements
Logins
Users
SSMS
Documentation
Server names and instances
Databases
Schemas
Tables
Columns
Stored procedures
Functions
Module 1 quiz
Module 1 lab

Module introduction
Determining required resources
Planning for IO
Planning for CPU
Planning for memory
Planning for networking
Planning for service accounts
Virtualizing SQL Server
Pre-installation testing
Downloading installation media
Downloading latest CU
Downloading Report Server
Module 2 quiz
Module 2 lab

Module introduction
Installation resources
Installation window order
Edition
License terms
Global rules and updates
Install rules
Installation type
Azure extension
Feature selection
Instance configuration
Server configuration
Reporting services
Patching
Post-installation checks
Unattended installation
Renaming a server
Changing service accounts
Module 3 quiz
Module 3 lab

Module introduction
System databases
Database files
Database data
Number of database files
Size of database files
tempdb
Creating a user database
Configuring database options
Filegroups
Moving database files
Moving system databases
Shrinking database files
Module 4 quiz
Module 4 lab
Module 4 lab solution

Module introduction
Data modification
Transaction log
Full recovery model
Simple recovery model
Bulk logged recovery model
Defining RTO
Defining RPO
Defining 9s of availability
Recommended strategy
Backup testing and retention
Module 5 quiz
Module 5 lab
Module 5 lab solution

Module introduction
Full backup
Differential backup
Log backup
Partial backup
Copy only backup
File or filegroup backup
Tail log backup
Backup media
Restore phases
Recovery types
Strategy example 1
Strategy example 2
Strategy example 3
Strategy example 4
Module 6 quiz

Module introduction
Perform full backups
Mirrored backups
Striped backups
Verifying backups
Backup compression
Backup encryption
Perform differential backups
Perform transaction log backups
Module 7 quiz
Module 7 lab

Module introduction
Viewing backup file contents
Restore a database
Restore transaction logs
With standby
Point in time recovery
Stopping at marked transactions
Perform file or filegroup restore
master database restore
model database restore
Restoring other system databases
Module 8 quiz
Module 8 lab

Module introduction
Authentication vs Authorization
SQL Server authentication options
Creating Windows logins
Creating SQL Server logins
Database users from logins
Contained database users
Special users
Inpersonation vs Delegation
Mismatched SIDs
Server level permissions
Fixed server roles
public server role
User-defined server roles
Fixed database roles
User-defined database roles
Application roles
Adding role members
Module 9 quiz
Module 9 lab
Module 9 lab solution

Module introduction
Server principals
Database principals
Securables
Assigning permissions
GRANT option
Table and view permissions
Column permissions
Default schemas
Resolving object names
Schema permissions
Stored procedure permissions
Ownership chaining
Function permissions
Assembly permissions
Testing permissions by impersonation
Module 10 quiz
Module 10 lab
Module 10 solution

Module introduction
SQL Server auditing prior to 2008
Using triggers for auditing
Using SQL Trace for auditing
C2 common criteria
SQL Server auditing from 2008
Enabling SQL Server audit
Audit destinations
Server audit specifications
Database audit specifications
Reading audit output
Audits on failover servers
Module 11 quiz
Module 11 lab
Module 11 lab solution

Module introduction
Files extents and pages
Table structure heap
Table structure clustered index
Non-clustered index over heap
Non-clustered index over clustered index
Index column order
RID and clustered index lookups
Covering indexes
Included columns
Filtered non-clustered indexes
Indexed views
Primary keys and indexes
Unique constraints and indexes
Foreign keys and indexes
Module 12 quiz
Module 12 lab
Module 12 lab solution

Module introduction
Table partitioning overview
Using partitioned views
Using vertical partitioning of data
Using partitioned tables
Create partition function
Create partition scheme
Create partitioned table
Benefits of partitioning
Sliding window designs
SWITCH data in and out
Partition alignment for indexes
Module 13 quiz
Module 13 lab
Module 13 lab solution

Module introduction
Large databases are costly
Compress data files?
Table compression
Deprecated vardecimal data type
Page compression
Create with compression
Rebuild with compression
Index compression
Estimating space savings
Space requirements
xVelocity compression
Columnstore indexes
Restrictions in 2012
Enhancements in 2014
Enhancements in 2016
Module 14 quiz
Module 14 lab
Module 14 lab solution

Module introduction
Automating administration
SQL Server Agent
SQL Server Agent core objects
Creating jobs
Creating job steps
Scripting jobs
Job schedules
Job security
T-SQL job step permissions
Non-T-SQL job step permissions
Credentials and proxies
Working with Powershell and DBA tools
Module 15 quiz
Module 15 lab

Module introduction
Database mail
Configuring database mail
Managing mail profiles
Mail system parameters
Sending email
Viewing email logs
Module 16 quiz
Module 16 lab

Module introduction
Creating and configuring operators
Enabling agent mail properties
Configuring notifications
Configuring alerts
Job activity monitor
Viewing job history
Configuring job history retention
Module 17 quiz
Module 17 lab

Module introduction
Database integrity
Database page verification
Logical consistency checks
Useful DBCC options for Consistency
Repairing databases
Indexes and performance
Intentional internal fragmentation
Adjusting fragmentation
Database maintenance plans
Module 18 quiz
Module 18 lab

Module introduction
bcp
BULK INSERT
Improving insert performance
OPENROWSET
SQL Server import and export data
Integration Services
Azure Data Factory
Module 19 quiz
Module 19 lab

Module introduction
Resource governor overview
Alternatives to resource governor
Governed resources
Workload groups
Resource pools
Internal and default groups and pools
Effective max and shared percentages
Classifier functions
Useful DMVs
Dynamic change effects
Detecting classifier failures
Best practices to follow
Module 20 quiz
Module 20 lab

Module introduction
Encryption
Transparent database encryption
In-memory OLTP and natively compiled procedures
Filestream and file tables
Replication
Row level security
Data masking
High availability
Arc enabling servers
Tracing activity using SQL Trace and Extended Events
Linked servers
Full-text indexing
Service Broker

Clean up resources
Summary and further steps