SQL Server 2016 Administration

Explore SQL Server 2016 new features and administration, including data masking, temporal tables, stretch databases, and row-level security. Learn about live query statistics, Polybius, memory-optimized storage, and Azure integration.

Learn to set up a virtual box environment for SQL Server 2016 administration, install Windows Server 2016 with service packs, and deploy the Wide World Imports and Adventure Works databases.

Create a virtual machine in VirtualBox to run Windows Server 2016 for SQL Server 2016 administration, including VM setup, ISO installation, resource planning, and network and firewall considerations.

Learn to install SQL Server 2016 in a virtualized lab using VirtualBox, configure bridged networking, enable shared folders and guest additions, and complete prerequisites for a production-ready setup.

this sql server 2016 part 2 installation covers enabling remote access through windows firewall ports and configuring polybase with external data sources, including java prerequisites.

Install the SSME by navigating to the Esquibel 2016 folder, running the setup, downloading the latest SSME, saving to the default path, and rebooting to complete the installation.

Install and launch the new SSME, pin it to the taskbar, customize color themes, adjust query execution options to modify the schema of the table, and begin discussing data masking.

Learn how to restore sample databases for SQL Server 2016, including Adventure Works 2016 and Wide World Importers, from backups to a local folder.

Explore dynamic data masking in SQL Server 2016 to obfuscate selected table columns, protecting sensitive data without encryption and configuring default, email, custom, and random masking.

Explore dynamic data masking in sql server 2016 by applying default, email, partial, and random masking functions to columns, and control access with grant and unmask commands.

Explore row level security in sql server 2016, implementing a function and security policy to filter data by user with filter and block predicates, while keeping access transparent to applications.

Learn how SQL Server 2016 stretch database offloads archival data from on-premises databases to Microsoft Azure, reducing hardware needs while remaining transparent to users.

Explore creating a stretch database to offload cold data to Azure and ease on-premise resource strain, sustaining 24/7 availability. Configure remote data archive and online migration.

Set up azure cloud services with sql server, create an azure account and database, and demonstrate cost management with free credit while exploring stretch database and poly base integration.

Create a Microsoft Azure account and sign in to the Azure portal. Explore stretch database technology, manage costs with the 200 dollars free credit, and migrate data to Azure storage.

Learn how to stretch a SQL Server 2016 database to Azure, migrate on premises data, and configure stretch for entire database or specific tables with server setup and monitoring.

Restore a stretch database in SQL Server 2016 by transferring and extracting the backup on a virtual machine, then verify the stretch database and the people table with 100 rows.

Explore live query statistics in SQL Server 2016, compare it with older execution plans, and learn how real-time data aids troubleshooting and performance tuning.

Explore SQL Server 2016 temporal tables and history tables to track changes, audit data, and recover point-in-time data with system-versioning.

Explore temporal tables in SQL Server 2016, using system versioning to maintain a current and history table. See inserts, updates, deletes, and how to restore data from history.

Discover SQL Server 2016 Query Store, a GUI tool that captures historical query data and execution plan statistics to troubleshoot long-running queries and monitor workload changes with a low footprint.

Monitor the SQL Server 2016 query store in production, keep it in read mode, and tune retention and capture modes while using DMVs to spot slow queries.

Explore how query store tracks forced plans and plan IDs to compare execution plans and optimize with indexing in SQL Server 2016.

Learn how to force a query to use the most efficient execution plan, compare plans, and create indexes to reduce logical reads and optimize long-running queries in SQL Server 2016.

Explore SQL Server 2016's in memory OLTP, speeding transactional processing with memory-optimized tables and hash indexes. Understand durability options, memory versus disk, and the no-locks architecture.

Explore how memory-optimized tables in SQL Server 2016 boost performance with in memory OLTP, including schema only vs schema and data durability, bucket counts, hash indexes, and memory-optimized file groups.

Migrate data into in-memory OLTP tables using the import-export wizard, compare regular, schema-only, and schema-with-data setups, and emphasize memory requirements and staging with Stretch database for performance demonstrations.

Use the transaction performance analysis overview report to identify candidate tables for memory optimization, then run the memory optimization adviser to migrate and convert tables to memory-optimized tables.

Restart the services to emulate a failover and observe that schema-only tables retain their schema but lose data, while regular tables keep their data.

Demonstrate in-memory processing performance with execution plans, showing zero IO on memory-optimized tables during updates, and guide on memory use, backups, and restoring from scripts.

Explore how SQL Server 2016 Polybase integrates big data from Hadoop and Azure blob storage, set up external data sources and formats, and access data using Azure Storage Explorer.

Learn how to set up Azure blob storage with the Microsoft Azure Storage Explorer, connect to SQL Server, and access external data using Eskil Polybius for large data storage.

Set up Azure blob storage for SQL Server 2016 administration by creating a storage account and blob container, uploading a CSV, and accessing the data via PolyBase.

Set up PolyBase to access Azure storage data from SQL Server 2016 by configuring master keys, credentials, external data sources, file formats, and external tables, then run remote queries.

Demonstrates deleting Azure resources and canceling a subscription in a testing environment to avoid charges, including confirming deletions and verifying billing status.

R technology is introduced beyond the scope of this course and not demonstrated, so Rafael recommends reviewing articles or related videos to understand its role in your environment.

Dive into SQL Server 2016 performance tuning, part one of two, this bonus video covers troubleshooting, execution plans, query optimization, and bottleneck analysis for junior to mid-level SQL Server DBAs.