SQL Server Cert: Administering a SQL Database Infrastructure

Understand the four SQL Server platform categories—data stores, data modeling, integration, and visualization—and the acronyms that power modern data solutions, including RDBMS, SSIS, SSAS, SSRS, ETL, and Power BI.

Compare SQL Server 2016 editions: enterprise, standard, web, developer, express, and evaluation, covering price points, features, licensing rules, and development versus production use.

Understand databases as organized collections of related data that are easily accessible and updated; learn how indexing and table structure, with io considerations, improve retrieval for sql server administration.

Explain the difference between OLTP and OLAP databases, their use in day-to-day transactions versus analytics, and how a data warehouse supports reporting while balancing performance.

Explore Transact-SQL, a proprietary flavor of the SQL standard used to create, manage, and manipulate SQL Server databases, and learn how its syntax translates to other flavors.

Download SQL Server 2016 Developer Edition for free and practice hands-on installation, configuration, and use; learn via downloading the ISO and installing on virtual machines or local machines.

Mount the SQL Server 2016 ISO, run setup as administrator, and complete the installation wizard to configure the database engine, Windows authentication, and a default or named instance.

Install SQL Server Management Studio (SSMS) separately from SQL Server 2016 by downloading version from msdn.microsoft.com and running the setup as administrator to access databases, security, management policies, and maintenance.

Attach the Adventureworks 2012 sample database to your sql server using ssms by copying the Adventureworks 2012_data.mdf to the default data folder and attaching.

Identify and use SQL Server management tools after installation, such as Configuration Manager and SSMS, and generate the installed SQL Server Features Discovery report to verify what's installed.

Explore authentication, authorization, auditing and encryption in SQL Server security, and learn how principals, securables, and permissions regulate access across server, databases, and objects.

Explore server-level security in SQL Server, focusing on authentication at the server and database levels, including Windows authentication and mixed mode, and how to switch modes with a restart.

Learn how to safely restart SQL Server using SQL Server Management Studio or the Services panel, manage authentication mode changes, notify users, and ensure SQL Server Agent restarts correctly.

Create and manage logins to let users connect to the SQL Server, using SQL Server Management Studio or T-SQL, including Windows or SQL Server authentication and database mapping.

Learn database-level security in SQL Server by creating database users, authenticating at the server level with logins, or using contained database users, and managing access with SQL Server Management Studio.

Explore database user account types in SQL Server, from server-level logins to database-level users, including mixed mode authentication, partially contained databases, and authentication methods like certificates and asymmetric keys.

Configure linked servers to access data across multiple SQL servers using four part naming. Assess security implications of cross server authorization and leverage Ole DB providers for remote objects.

Learn how partially contained databases move metadata from the SQL Server to the database, simplifying migrations and reducing orphaned logins. Plan migrations to partially contained, aiming toward fully contained databases.

Explore dynamic data masking in SQL Server 2016, a feature that masks sensitive data for non-privileged users with column-level rules and T-SQL masks like default, email, random, and custom string.

Build a sample sensitive-data table in Adventureworks 2012 with t-sql and apply default dynamic data masking to the credit rating column, as part one of a two-part walkthrough.

Explore dynamic data masking in SQL Server, masking SSN, credit card, and email data with partial masks and permission controls to reveal only necessary details for non-privileged users.

Learn how roles group users and Windows groups into fixed server and fixed database roles, create user-defined roles as needed, and assign permissions to the roles so members inherit them.

Explore fixed server roles in SQL Server to assign administrative permissions, learn the nine built-in roles, their permissions, and when to avoid blanket sysadmin by creating specific user-defined roles.

Explore fixed database roles in SQL Server, including db owner, data reader, data writer, and DDL admin, and how to assign them in SQL Server Management Studio.

Create, alter, and drop server and database level user defined roles to enforce security, and add Active Directory groups as members to grant scalable permissions.

Learn how grant, revoke, and deny permissions control access in SQL Server, including access via roles and groups, revoke’s partial effect, and how deny overrides all grants.

Explore schemas in SQL Server and apply permissions at the schema level to securely group and manage tables, procedures, and views, simplifying ownership and administration.

Explore how SQL Server encryption protects data at rest and in transit by using a multi-layer hierarchy of certificates, asymmetric and symmetric keys, stored procedures, and extensible key management.

Explore the SQL Server encryption hierarchy, linking outside keys and passwords to database master keys and certificates, to protect data at rest and in transit.

Explore encryption complexities in SQL Server, including performance impact on CPU and IO, and the need to protect encryption keys, manage backups, and store them in a separate safe location.

Compare asymmetric and symmetric encryption, detailing two-key versus single-key schemes, their security and speed trade-offs, and how combining them safeguards data in SQL Server.

Explore encryption options in SQL Server 2016, including AES 128, 192, and 256 bit keys, and understand how key size affects security and performance for encrypted backups.

Discover how encrypted backups protect SQL Server data using a database master key and a certificate or asymmetric key, and why backing up those keys matters.

Learn to perform encrypted backups in SQL Server Management Studio by creating a database master key and certificate, then back up Adventureworks 2012 with encryption and compression.

Implement TDE in SQL Server. Protect data at rest by encrypting before writing to disk and decrypting on read with the database master key, certificate, and database encryption key.

Implement transparent data encryption (TDE) on Adventureworks 2012 by creating a database master key, server certificate, and database encryption key, then enable encryption and back up certificates.

Explore always encrypted in SQL Server 2016, enabling data owners to read encrypted data while administrators cannot, via client-side encryption and column encryption keys.

Follow a step-by-step guide to implement always encrypted on a SQL Server column using SQL Server Management Studio, applying it to birth date in HR table.

Explore how clustered and non-clustered indexes shape data storage and access in SQL Server, including heaps, key sizes, and the trade-offs of index maintenance for fast queries.

Learn how to manage SQL Server indexes, repair fragmentation, and prune underutilized non-clustered indexes to keep data modifications fast and queries efficient.

Identify fragmentation in SQL Server indexes using the average fragmentation percent from sys.dm_db_index_physical_stats. Reorganize for 10–30% and rebuild for over 30% to minimize I/O and improve performance.

Repair index fragmentation by rebuilding or reorganizing indexes using SSMS or T-SQL, and check fragmentation with dm_db_index_physical_stats on human resources employee table. Rebuild the AK employee login ID as needed.

Explore the database engine tuning advisor to analyze traces or query store data and obtain index and partitioning recommendations; test them, then implement or revert based on query optimizer results.

Analyze a trace with the database engine tuning advisor to generate recommendations. Save recommendations as SQL and run the script to create indexes on sales order detail and header.

Developing a backup strategy emphasizes building a documented backup plan and thoroughly testing restoration from ground zero to ensure quick, reliable recovery, with clear responsibilities, steps, and budget considerations.

Master the six sql server 2016 backup types: full, differential, log, copy-only, file, and partial backups. Learn how faster backups affect recovery time and plan effective strategies.

Understand SQL Server recovery models—simple, full, and bulk log—and how they affect the transaction log, backups, restore options, and point-in-time recovery, with a hands-on SSMS demonstration.

Learn how copy-only backups differ from standard backups, how SQL Server tracks changed extents with bitmap/archive bits, and how to perform copy-only backups via SSMS, T-SQL, or PowerShell.

Define backup terminology from backup device and media to media set, media family, and mirrored media set, and explain full, differential, and copy-only backups on disk or Azure Blob Storage.

Back up the SQL Server system databases—master, model, and MSDB—and remember tempdb is not backed up, and replication may introduce a distribution database.

Understand the fundamentals of the SQL Server transaction log, including committed and uncommitted transactions, implicit transactions, and how logs support backups, recovery, and restores.

Learn how to perform and restore transaction log backups in SQL Server, manage recovery models, and optimize backup strategy with logs, full, differential, and incremental backups.

Tail log backups perform a point-in-time transaction log backup to capture unbacked changes and minimize data loss during a SQL Server failure.

Learn to restore SQL Server databases by exploring options from full, differential, and transaction log restores to partial, file, and page restores, with recovery, no recovery, standby, and replace settings.

Restore a database in SSMS by selecting a backup source, relocating files, and choosing recovery options (with recovery, no recovery, or standby), while considering tail log backups.

Learn to recover the master database by starting SQL Server in single-user mode via configuration manager or the SQL Server Utility, restore from backup, and remove the startup parameter afterward.

Use point in time recovery to restore a database to a precise moment using transaction log timestamps. Ensure the database uses a full or bulk logged recovery model.

Explore log shipping to create a warm standby SQL server by continuously shipping transaction log backups from the primary to secondary servers, enabling read-only access and cost-effective disaster recovery.

Explore how to monitor database performance to keep SQL Server responsive as business grows. Identify factors from applications, systems, and resources that affect response time, throughput, and locking.

Explore real-time and historical SQL Server performance monitoring using Windows performance monitor, DMVs, dbcc commands, and system stored procedures to track locks, log space, and database activity.

Explore session identification and server process IDs (spids) as the basis for monitoring SQL Server activity, and learn to use sp_who to identify blocking or poorly performing queries.

Learn how SQL Server uses locking to protect data integrity, identify blocking sessions with Activity Monitor and sp_who/sp_who2, and diagnose issues caused by excessive locks and long transactions.

Identify blocking sessions with the activity monitor, locks, and wait times; examine blockers with sp_who and sp_who2, and resolve by killing the blocking process or managing transactions.

Learn to use the utility control point (UCP) and SQL Server utility for monitoring multiple SQL Server instances, with data collected every 15 minutes into data warehouse, enterprise edition required.

Centralize performance monitoring with the data collector, gathering metrics from SQL Server instances into the management data warehouse, and display disk usage, query statistics, and server activity in real-time graphs.

Configure the management data warehouse and data collector, enable or disable collection, and view reports such as server activity history and disk usage.

Explore how the SQL Server 2016 query store simplifies troubleshooting by capturing query executions and plans, enabling plan forcing, performance analysis, and history tracking for top queries.

Learn how extended events enhance SQL Server monitoring by capturing data only when specified events occur, with lightweight overhead and flexible configuration via SSMS, Transact-SQL, or DMVs.

Create a custom extended event session in SQL Server Management Studio using the new session wizard, select capture events, apply the query detail tracking template, and configure data storage.

Edit and refine extended events sessions in sql server management studio to capture master and adventureworks 2012 activity, applying filters and viewing live data.