Administering Microsoft SQL Server 2012 Databases - 70-462

Learn to manage SQL Server properties and configure server settings with sp_configure. Understand how SQL Server allocates memory and enable database mail.

Explore server properties in SQL Server, view and modify them through SSMS and the sp_configure command, understand configured versus running values, and apply changes with reconfigure after updates.

View SQL Server properties in SQL Server Management Studio, inspect memory, security, connections, and advanced options, and use sp_configure to set values; reconfigure reloads to align configured and running values.

Enable show advanced options, run sp_configure and reconfigure to view and modify advanced options in SQL Server. Understand default values and when to tweak them for production environments.

Demonstrates enabling advanced options in SQL Server by adjusting show advanced options, reconfiguring to reveal hidden options, and modifying them before turning off the view to revert to defaults.

Explore how SQL Server manages CPU and memory architectures, including affinity masks for CPU and IO, and how to set minimum and maximum memory to optimize performance.

Explore how SQL Server manages CPU affinity and memory configurations using the server properties processor screen, disabling automatic configuration, and applying manual affinity and memory settings via the configure commands.

Configure database mail in SQL Server to send reports and alerts from within SQL Server. Create mail accounts and profiles stored in msdb, and send mail via sp_send_dbmail.

Configure database mail via the management node wizard, enable the feature, create a new public or private profile with a mail account and smtp details, and test from the UI.

Review the Object Explorer in SQL Server Management Studio to view server properties, modify settings with sp_configure, apply affinity masks, and understand memory allocation types and database mail.

Explore managing databases, including system databases, and learn how SQL Server stores data physically, the four types of system databases, data moved across file groups, and recovery models.

Explore the three database types in SQL Server: user, system, and contained databases. See how contained databases isolate metadata and ease deployment, with replication limitations.

Create and convert a database to a contained database, then add a contained user who can only access that database; observe login behavior and a restricted current database view.

Explore four system databases—master, model, tempdb, and msdb—and learn that master stores server data, model templates new databases, tempdb is transient and cannot be backed up, and msdb tracks jobs.

Explore database options in SQL Server 2012, including auto close and auto shrink, assess space with metadata and file properties, and move data between file groups via clustered index.

Check database file locations and sizes, verify an 8 mb data file, and use queries to show 6 mb used, while reviewing auto close and auto shrink defaults.

Create a new file group called transactions, attach a data file on a new faster disk, and move the table’s clustered index to the transactions file group to relocate data.

Explore recovery models in SQL Server 2012, including simple, full, and bulk log, and learn how each model affects the transaction log, backups, and crash recovery.

Open the database properties, access the options screen, select a recovery model from the three available values, and save the setting.

Review the three database types—system, user, and contained—and the four system databases: master, model, tempdb, and msdb. Use file groups on two drives and adjust the recovery model as needed.

Plan and implement SQL Server security mechanisms to protect databases. Identify account types, manage server roles, and database roles, and grant or deny permissions to control database permissions.

Explore the SQL Server security model and defense in depth, with layered protection around data, and how Windows accounts map to SQL Server logins via mixed mode authentication.

Manage SQL Server and Windows accounts, create logins, assign default databases, and test access using Windows authentication.

Explore how server roles group logons to simplify permissions in SQL Server, manage fixed and custom roles, and apply cascading, least-privilege permissions to safeguard a SQL Server 2012 instance.

Explore managing server roles in sql server management studio, create a custom server role, assign permissions, add members, and distinguish between fixed and custom roles.

Learn how permissions work in SQL Server and how to grant or revoke them for logins, roles, and objects using grant statement, with grant option and deny statement with cascade.

Identify, grant, and deny permissions for a server role using grant and deny; verify assignments in the role properties and test login outcomes, including the grant option effect.

Explore database-level security by using fixed and custom database roles, including D-B owner, security admin, and data writer or reader, with grant and deny statements in Seagle server.

Demonstrate fixed database roles and the database owner, create a D-B query role, grant select, deny delete on tables, and test access.

Learn the basics of SQL Server security: explore Windows and SQL Server accounts, server roles for logon grouping, grant and deny permissions, and database object permissions.

Describe digital certificates, explain how sequel server auditing works, and define policy with its four possible values; demonstrate encryption and data compression.

Discover how digital certificates provide resource identity and why trusted authorities matter. See how SQL Server uses certificates to secure server and data, with self-signed cases for testing only.

Explore how asymmetric key encryption uses public and private keys with digital certificates to secure communications, while SQL Server protects keys with the service master key and database master key.

Explore certificate based logins in SQL Server 2012, enabling pre-authenticated access and encrypted server-user communications, and learn to create, export, and assign certificates for login authentication.

Learn how SQL Server stores certificates in the master database, create a master key, and map a login to a certificate; export keys for user installation to enable certificate-based logins.

Set up audit objects and attach server and database audit specifications to track server- and database-level events. Enable audits to log events to files and view searchable reports of actions.

Set up and verify audits in SQL Server 2012 by creating an audit object and configuring server and database audit specifications to track drop, delete statements, and login attempts.

Define centralized policies in sequel server to govern multiple installations, using a condition and evaluation mode with on schedule, on change log, on change pre-event, and on demand executions.

Demonstrate policy based management by creating a name length condition for tables, using the LCN function and the name property, with on demand evaluation.

Explore transparent data encryption and column-level encryption to protect SQL Server data, with on-the-fly decryption and key management. Examine enterprise compression for tables, views, and indexes and estimate savings.

Demonstrates creating a database certificate and enabling transparent data encryption, backing up certificates, and verifying encryption; then compares row-wise and page compression on a table and confirms storage savings.

Understand how digital certificates provide resource identity and how auditing tracks activity. Recognize policy, encryption, and compression for protecting data and supporting indexes and files.

Learn about managing table indexes in sql server 2012, describe the index list and the new types of indexes, and explain columnstore index, file stream, and file table.

Learn how SQL Server indexes speed queries by using clustered and non-clustered structures (often tied to the primary key) and a B-tree; understand trade-offs on inserts, updates, and deletes.

Explore creating a non-clustered index on the colored column of the Adventure Works product table, name and save it, then verify index usage via an execution plan.

Learn how full text indexing and full text search in SQL Server 2012 index individual words in text columns, parse documents, and support advanced queries using contains and free text.

Set up a full text catalog and create a full text index on the description column, then use contains and proximity queries to search terms like mountain.

Use the column store index to store column-based data with high compression for warehousing queries; updates require dropping and recreating the index.

Demonstrate creating a column store index on the sales order detail table to boost analysis performance, and show how to disable, update, and re-enable the index when needed.

Learn which file types SQL Server indexing supports via the full text document types table, and how installing 64-bit office iFilters enables indexing of docx and xlsx after a restart.

Enable file stream on the instance and database, restart, create a Documents file table pointing to a directory, then enable full text indexing to search the file stream contents.

Review how indexes locate data in a table, explore columnstore, file stream, and file table in sql server 2012, and see how file stream stores files in the file system.

Learn to run SQL Server reliably and maximize availability by detaching and attaching databases, backing up, using clusters, understanding replication, and implementing database mirrors.

discover how to detach a database, move its files to another server, and reattach them, while managing offline status and optional compression.

Detach a database from the first instance and attach it to the second instance, then view its objects and reverse the process to return it to the original instance.

Learn how to back up and restore Microsoft SQL Server 2012 databases, using full, differential, log, and file backups, with online backups via volume shadow copies and scheduled jobs.

Demonstrates creating a full database backup as a .bak file and restoring it on another server, with auto-detection and moving files to a new location to verify the restore.

Explore high availability concepts in SQL Server 2012, including clustering with failover, availability groups, and replication types (transaction, merge, snapshot) via pub-sub, with Always On replacing database mirroring.

Configure and demonstrate SQL Server replication by setting the distributor on both instances, publishing a database, selecting articles, and subscribing from the other instance using push replication.

Review detaching a database to move files between servers, keeping detached files, cover backups (database, log, file) and cluster replication for high availability with data not shared across nodes.

Learn how to benchmark and optimize performance on your sequel servers by using resource governor, Pergamon, dynamic management views, and sequel profiler.

Benchmarking SQL Server performance uses PerfMon and the Resource Governor to measure CPU, memory, I/O, and stored procedure execution, set baselines, and cap workloads.

Create a resource pool with the resource governor and set min 10% CPU and memory. Define workloads for peak, lunch, and off-peak with a time-based classifier, and test with sessions.

Explore dynamic management views to access system information and monitor server state, current values, and performance. Use SQL profiler to capture trace logs and analyze query performance to identify bottlenecks.

Use SQL Server DMVs to analyze wait statistics and I/O performance with a where clause, OS memory clerks, memory cache counters, and CPQ stats for each query plan.

Use SQL profiler to run a trace, view how queries execute, and pause or stop traces while analyzing a function or stored procedure.

Optimize indexes to boost server performance by examining index statistics and their effect on query plans, then monitor and address index fragmentation with rebuilds or drops and recreates.

We explore the indices defined on the product table and observe how creating or updating an index on color and list price triggers automatic statistics and reveals value distribution.

Open the products table from the previous demo, navigate to the indices node, right-click the index created for color, and choose rebuild to optimize the index, then click okay.

Benchmark SQL Server performance against standards, while resource governor limits resources and perfmon monitors usage. Utilize DMVs and system views, with SQL profiler trace logs, to optimize servers.

Identify errors in SQL Server 2012 and diagnose them. Define database console commands (DBCC), list and explain the different types of trace flags, and describe extended events.

Learn how sql server reports errors and handles issues using dbcc commands, and review error metadata—error number, text, severity, state, and sys messages in master database via sys messages DMV.

Explore SQL Server error messages by querying sys.messages, learn about metadata like severity and placeholders for object names, and verify database integrity with DBCC CHECKDB showing no errors.

Learn to trace server behavior with trace flags in SQL Server, including global and session scopes, and enable or disable with DBCC TRACEON or TRACEOFF.

Learn how to back up a database, view backup entries in the SQL Server log, and use trace flag 32 26 to deactivate backup logging, then verify with trace off.

Explore extended events (x events) for troubleshooting in SQL Server, querying data across the system and viewing results via query or the user interface, with a practical install demo.

Create an XEvent session that starts automatically and monitor live server events by filtering categories, selecting specific events, and adding them to the session for real-time capture.

Learn to recover a failed database by restoring from the latest backup or transaction log, or by using emergency or single-user mode and dbcc checkdb if needed.

Stop the SQL server and delete the log to simulate a recovery pending error, restart. Enter emergency mode, run dbcc checkdb with repair, recreate the log, then return to multi-user.

The lesson reviews how SQL Server reports errors, uses DBCC for viewing and troubleshooting, and explains trace flags (session and global) and extended events for server diagnostics.

Explore advanced server management concepts and define virtualization and its effect on SQL Server. Identify the different types of patches and explain the deployment process.

Explore virtualization concepts and how SQL Server uses it. See how SQL Server 2012 addresses I/O performance bottlenecks in virtual environments, delivering near bare-metal performance with Hyper-V and Windows Azure.

Use Hyper-V to host a SQL Server VM, boot the virtual machine, log in, and open SQL Server Management Studio to work with databases and write queries.

Learn to update SQL Server with patches from Microsoft, including hotfixes, updates, cumulative updates, critical updates, security updates, and service packs; test in staging and deploy via WSUS.

Check the current system version with a query to confirm the s.p number (64-bit) and version on the server, then upgrade two SQL Server 2012 instances to Espey 2.

Learn how data tier applications in sql server 2012 enable developers to work on local database copies with synchronized changes to a central server, using acpac and bacpac deployment packages.

Register the database for data tier applications on the main server, then extract to create a DACPAC or export to create a BACPAC, and deploy or import to sync changes.

Create a new publications database, adding authors and books tables, then register it for a data-tier application and export a structure-only dacpac for deployment to another server.

Demonstrates syncing and deploying database changes with DTA: create a new category table, add category id, establish relationships, package and upgrade deployment, and verify object-level changes and keys.

Export data and structure from three tables to a new server using the DTA demo, then import the bacpac and verify the complete data.

Recaps virtualization as a key infrastructure component, outlines patch types like hotfix and service pack, and describes data tier applications for deploying database packages and syncing changes.