SQL Server Cert: Administering a SQL Database Infrastructure

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

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.

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.

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 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.

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 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.

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.

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.

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.

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.

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.

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.

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.

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 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.

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