Microsoft 70-462: Administering SQL Server 2012 Databases

Begin with an introduction to a simple server, then examine its architecture, instances, and the editions available within SQL Server 2012.

Explore how SQL Server 2012 functions as a suite beyond its relational engine, covering integration services, analysis services, reporting services, and full text indexing, plus instances and default port 1433.

Learn to deploy multiple SQL Server 2012 instances, manage default and named instances with per-instance administration, and handle remote connections via the browser service and dedicated admin connection.

Explore why multiple SQL Server instances may be used for security boundaries and version separation (named vs default), while noting that extra instances consume resources and require careful baseline testing.

Explore SQL Server 2012 licensing shifts to core-based models and server plus CAL options across editions, and navigate the hierarchical instance structure from physical server to tempdb.

Explore SQL Server tools such as SQL Server Management Studio and SQL Server Data Tools, formerly BIDS, and learn how PowerShell and command-line interfaces enable automated database management.

Learn to use SQL Server Management Studio as the primary SQL Server GUI, connecting to all editions with IntelliSense, debugging, and native scripting, plus object explorer and project-based organization.

Customize management studio with fonts, line numbers, and display options, adjust connections and ansi settings, and use the dedicated admin connection for troubleshooting via sql server data tools.

Explore SQL Server Management Studio by connecting to a named instance, using Object Explorer, and scripting a table with DDL. Create queries, save changes, and instantiate databases from parameterized templates.

Create parameterized templates in Management Studio to standardize stored procedures and functions. Adjust the interface with tools and options, switch results between grid and text, and enable line numbers.

Learn to debug sql server management studio using Adventure Works 2012, declare and step through a variable, query with intellisense, and organize scripts in a project and solution.

Explore SQL Server Data Tools in Visual Studio to create business intelligence projects with integration services, analysis services, and reporting services; build a SSIS package and run a script task.

Use PowerShell to administer SQL Server, create and enable server audits, and configure file audits with a path; run SQL Server Profiler to monitor traffic and trace performance.

Explore exam scenarios by practicing with SQL Server data tools, Management Studio, and data tools for reports and Analysis Services projects.

Explore how SQL Server evolved into a data preparation and presentation suite beyond a relational database. Review instances, the browser service, profiler, Power Shell, and reporting, analysis, and integration services.

Explore the installation of SQL Server 2012, examining the architecture, required server resources, installation requirements, and the installation process.

Explore sql server architecture, 32- and 64-bit differences, installation, client connections via named pipes or tcp/ip, and how the query execution layer parses, optimizes queries, and manages buffer pages.

Identify hardware requirements for SQL Server 2012, including 32-bit and 64-bit processor minimums and supported processors. Learn memory and parallelism essentials, with edition-specific limits and configurations.

Assess network throughput, configure default and named ports (1433/1434), enable firewall and browser services for DAC remote connections, and evaluate disk performance with data/log placement and I/O simulations to establish baselines.

Explore software requirements for installing SQL Server, including .NET Framework 3.5 SP1 and 4.0, PowerShell, Internet Explorer, and supported Windows versions, and explain collations and data sorting.

Choose the right SQL Server services and service accounts with the least privileges to ensure a proper security context. Use domain or local accounts to avoid unnecessary elevation during installation.

The lesson guides the sql server installation, starting with the system configuration checker validating minimum requirements, then feature selection and system server configuration, including service accounts and administrator roles.

Review the installation log for warnings, then errors; verify service start in the event log, and check the service account permissions and automatic start in SQL Server Configuration Manager.

Learn how to upgrade from previous versions and configure unattended installations for SQL Server environments.

Compare side-by-side upgrades with new hardware or virtual machines and in-place upgrades for SQL Server 2012, detailing downtime, rollback, and connection redirection, plus unattended installations and automated updates.

Configure memory and CPU for a SQL Server instance using Management Studio and Configuration Manager, setting min/max memory and processor affinity. Review network configuration, file stream, and system databases.

Learn configuration checks and troubleshooting for SQL Server 2012, covering bootstrap logs, system configuration reports, and service startup issues. Review Event Viewer, registry keys, and service account permissions.

Summarize exam-focused topics for SQL Server 2012, including installation options, hardware and software requirements, in-place vs side-by-side upgrades, backout plans, downtime, and buffer pages and transactions handling by the engine.

Review installation prerequisites for SQL Server 2012, covering memory, disk, and network requirements, compare 32-bit and 64-bit builds, upgrade paths, and update types.

Explore database architecture, examine file and file groups, and discuss backing up and restoring databases in SQL Server 2012.

Explore database architecture, data files, and log files, and examine the recovery model and database file groups.

Explore database architecture: primary data and log files, model database, data pages and extents, buffer cache, write-ahead logging, checkpoints, and recovery models (full, bulk-logged, simple) and their backup implications.

Database data files reside in file groups with a primary group; log files are not. Assign tables and indexes to file groups to spread writes across multiple data files.

Explore full database backups, including differential, transaction log, tail log, and file and file group backups, plus copy-only backups, then examine backup compression and mirrored media sets.

Master full, differential, and transaction log backups for SQL Server 2012, and perform piecemeal restores with file group backups, ensuring point-in-time recovery and proper restore order.

Learn how SQL Server backup strategies use full, differential, and transaction log backups, including copy-only options, compression benefits, media set mirroring, and backup verification with checksums and restore verify.

Explore the restoration process and perform restores of full, differential, transaction log, and file and filegroup backups, learn point-in-time recovery, and use the standby option.

Learn how to restore SQL Server databases from full, differential, and transaction log backups, applying redo and rollback with or without recovery, ensuring log order and point-in-time options.

Explore how to restore the system databases in SQL Server, focusing on practical recovery steps and best practices.

Back up the master, model, msdb, and resource databases, then restore the master in single-user mode from backup and restart the server; tempdb is not backed up and is recreated.

Demonstrates backup and recovery concepts in SQL Server 2012, showing how to switch recovery models, perform full, differential, and transaction log backups, and restore with standby.

Demonstrates creating full, differential, and transaction log backups, performing a tail-log backup, and restoring a database to a point in time using recovery options in SQL Server 2012.

Learn to back up and restore SQL Server databases using both the sequel and Management Studio, covering full, differential, and log backups, point-in-time recovery, and handling master database restoration.

Learn about SQL Server database files, including the primary data file and secondary (ndf) files, transaction logs, recovery models, and backup types from full to differential and filegroup backups.

Provide an overview of tracing options in SQL Server and introduce SQL Server objects that implement tracing, then examine Extended Events.

Explore auditing options for SQL Server 2012, including an overview of available methods, C-2 auditing, common criteria compliance, SQL trace, and DDL and DML triggers.

Explore auditing options in SQL Server 2012, including C-2 auditing, Common Criteria compliance, SQL traces and extended events, plus DDL/DML triggers, with pros, cons, and configuration notes.

Explore the fundamentals of SQL Server auditing, including audit objects, server audits, audit action groups, and creating audits.

Explore SQL Server 2012 auditing, built on extended events with server, database, audit scopes. Audits can be synchronous or asynchronous, stored in file, Windows application log, or security log.

Configure a server audit to define storage and failure behavior (synchronous or asynchronous), then deploy server- and database-level audit action groups to capture select, insert, update, and delete.

Explore an overview of extended events in SQL Server 2012 administration, as part of extended events part 1.

Explore extended events, introduced in SQL Server 2008, as a lightweight, native eventing infrastructure, and use SQL Server 2012 profiler in Management Studio with GUI and wizard to create events.

Demonstrate tracing activity and auditing in SQL Server with profiler and server-side traces, including security configuration and ddl triggers for auditing database events.

Demonstrates capturing DDL activity by inserting parsed event data into an audit table and reporting command text, login, and server details via extended events and server audit.

Trace activity with SQL Server 2012 auditing by creating and managing server and database audit specifications, handling audit file folders, and diagnosing startup issues in single-user mode.

Explore extended events in Management Studio by creating a locks session with templates, viewing live data, and evaluating server-side tracing versus event handlers for low-overhead auditing.

Master exam scenarios by identifying when to use trace profiler, extended events, server side trace, and auditing for DDL and DML triggers, while understanding their limitations and overhead.

Examine auditing and tracing options in SQL Server 2012, including server-side trace, DML and DDL triggers, and extended events with the Management Studio profiler for lightweight server auditing.

Learn to import and export data, transfer data to and from Siebel, use extract, transform, and load tools, and improve data transfer processes.

Explore an introduction to data transfer and examine the edl tools available within SQL Server.

Master the ETL process: extract, transform, and load data from SQL Server to a destination, enabling data cleansing, aggregation, and consistent collation across sources.

Explore sql server 2012 etl tools, including the bulk copy program, bulk insert, openrowset, format files, the import/export wizard, and integration services for complex transformations.

Improve data transfer by temporarily disabling constraints, indexes, or triggers during the load, then re-enable them after the eteo process, using no check to skip revalidation.

Disable indexes during the load process to reduce transactional overhead, then rebuild them afterward, since disabled indexes won't be used for queries during the load within organizational hours.

Disable triggers and constraints during the data load to reduce overhead, then re-enable them after the load so new rows fire correctly, while preserving referential integrity.

Minimize locking with granular locks or table locks to improve load concurrency; avoid escalations to full-table locks, and changing from full to bulk or simple affects logging and backups.

Explore three methods to copy databases—copy database wizard, backup and restore, and detach and attach—and compare offline detaching with online copying via the sequel management object model.

Explore detach-attach method for moving a SQL Server database by copying its data and log files to another media or server, then reattach it, noting the database must be offline.

Master data transfer using DCP, the import export wizard, and the copy database wizard to export the Adventure Works 2012 currency table to a flat file.

Explore transferring data and copying databases in SQL Server 2012 using SSIS packages, script tasks in Visual Basic or C#, and the copy database wizard with detach and attach options.

Explore exam essentials for administering SQL Server 2012 databases, including transfer tools, the import export wizard, offline versus online migration, detach and attach, and backup and restore.

Explore data transfer methods in SQL Server, including integration services and the import/export wizard, plus bcp, openrowset, bulk insert, and detach/attach options.

Explore SQL server security by examining authentication, server and database roles, and permissions to safeguard access and data in SQL server environments.

Explore how SQL Server 2012 enforces security through authentication and authorization, including logins, contained databases, and mapping to database users, with guest accounts and explicit grant rules.

Explore fixed and user-defined server roles in SQL Server 2012, including the public role and fixed database roles, with the least amount of administrative effort.

Learn how to authorize access by configuring scope (server, database, schema), define principals (Windows users, Windows groups, logins), and manage permissions with grant, deny, revoke, cascade, and implicit deny.

Grant, deny, and revoke permissions on tables and columns, plus execute rights for stored procedures and views; manage ownership chains and default schemas for proper object access.

Explore securing SQL Server by managing logins, database users, server and database roles, permissions through grants, revokes, contained database authentication, and creating a schema.

Secure SQL Server 2012 by managing logins, users, server roles, grants, explicit deny, and schemas, including user defined server roles for granular security.

Explain exam scenarios for administering SQL Server 2012 databases, covering fixed server and database roles, user defined roles, and permissions via grants, revokes, denials, and schemas.

Review SQL server security concepts including authentication vs impersonation, delegation, and mapping Windows users to SQL logins, plus server and database roles and schema-based permissions.

Learn to automate SQL Server tasks using SQL Server Agent, configure database mail, and manage operators, alerts, and jobs.

Learn how SQL Server Agent manages scheduling, alerts, and jobs for SQL Server 2012. Explore security roles, proxies, database mail notifications, and event forwarding across instances.

Learn to configure database mail in SQL Server, using profiles and accounts, with sp_send_dbmail, Service Broker delivery, and attachment support, plus logging and retention policies.

Learn to configure operators and alerts, create and manage jobs with SQL Server Agent, use proxies for elevated security, and troubleshoot via the job history and the job activity monitor.

Configure database mail via wizard, create a sequel admin profile with an smtp account on localhost, enable retry and logging. Test an email and review logs to troubleshoot connections.

Create operators and configure alerts and database mail to notify me of job events and user defined errors, log them, then design and schedule jobs with steps and failure handling.

Explore configuring and troubleshooting SQL Server Agent jobs, including step-level failure handling, database mail integration, alert profiles, and credential-based access to drive resources for reliable automation.

Create a proxy for the operating system subsystem, map it to credentials, and run a sql server agent job step as administrator to escalate permissions; configure database mail and alerts.

Explore SQL Server agent configuration, security contexts for jobs, and how alerts differ from predefined events, including history logs in the MSDE database, proxies, and credentials for escalating operator notifications.

Explore sql server agent security, options and configurations, and how operators and alerts manage notifications of job success or failure; define sequential job steps and use proxies for least-privilege escalation.

Explore monitoring SQL Server using dynamic management functions, profiler or server-side traces, and the data collector, engine tuning advisor, and activity monitor.

Explore dynamic management views and functions in SQL Server, introduced in 2005, organized by categories. Differentiate current status, which reflects real-time data, from accumulated historical information.

Explore SQL Server Profiler and server-side traces to collect trace and workload information, establish baselines, and apply the Database Engine Tuning Advisor and Performance Monitor recommendations to optimize performance.

Leverage the database engine tuning adviser to analyze workloads and schemas and generate index and statistics recommendations, with guidance on realistic workloads and partitioning considerations.

Explore activity monitor, reliability and performance monitor, and data collector sets in sql server 2012 to visualize activity, data file io, waits, and expensive queries.

Explore enterprise data collection with the data collector, using a central data warehouse, predefined packages, and reports to monitor disk usage, queries, and server activity across instances.

Explore dynamic management views and functions to identify missing indexes and monitor execution plans, profiler, server-side trace, activity monitor, and data collector to optimize queries.

Demonstrates using sql profiler with default trace to monitor Adventure Works 2012 database, capturing batch and statement events, integrating with performance monitor to collect cpu, memory, disk, network metrics.

Create and run a data collector with performance monitor to capture SQL activity, save traces, and import performance data; analyze counters to correlate queries, cache metrics, and waits.

Run a tuning trace and use the database engine tuning advisor to analyze the workload and underlying schema, yielding recommendations for indexes and statistics.

Learn to monitor SQL Server performance using Activity Monitor in Management Studio, view processes, waits, and expensive queries, and configure data collection with a management data warehouse.

Explore data collection workflows by reviewing disk usage and query statistics, configure SQL Server Agent jobs and schedules, and upload collected data to the data collection warehouse for reporting.

Analyze a single query with the database engine tuning advisor against Adventure Works 2012, generate index recommendations for the person table, and manage scheduled changes.

Apply dynamic management views and functions for performance monitoring, compare sql profiler and activity monitor options, and optimize the workload using data collector sets and the database engine tuning advisor.

Review SQL Server monitoring using dynamic management views and functions for current and historic data, and implement profiler, server-side traces, and data collector collection sets for low-overhead, periodic counters.

Explore maintaining SQL server, including an overview of checking database integrity, index maintenance, ongoing maintenance, and managing multiple servers.

Run DBCC CHECKDB to verify the logical and physical integrity of a SQL Server 2012 database, and choose repair rebuild or repair allowed data loss based on results.

Compare clustered and non-clustered indexes and how B-tree ordering across leaf, intermediate, and root levels enables fast exact and range searches. Understand fill factor, fragmentation, and re-organizing or rebuilding.

Learn how non-clustered indexes sit above clustered indexes, how include columns affect leaf pages, and how fill factor and pad index reduce page splits while considering maintenance impact.

Reorganizing indexes sorts data pages and can run online with less logging, though it uses resources and may degrade performance during the operation. Rebuilds are advised when fragmentation exceeds about 30 percent, require free disk space, and can make the index unavailable, especially for clustered indexes.

Explore master and target servers, central administration server, and multi server queries to centrally manage, enforce, and automate policy based administration across SQL Server 2012 databases.

Demonstrates running dbcc checkdb on Adventure Works 2012, suppressing info messages, interpreting results, analyzing index fragmentation with dynamic management views, and rebuilding or reorganizing nonclustered indexes based on fragmentation.

Rebuild and reorganize indexes to reduce fragmentation using Management Studio, and enforce naming rules via policy based management on stored procedures and system databases, including DDL change controls.

Master policy-based management across SQL Server instances with a central administration server. Register servers, evaluate and apply policies, and run multi-server queries to compare production, test, and development databases.

Explore exam scenarios for administering SQL Server 2012 databases, including clustered and non-clustered indexes, index fragmentation and maintenance, policy based management, and central management server concepts.

Learn to use dbcc checkdb for database integrity, manage index fragmentation by reorganizing or rebuilding, and apply policy-based management with central administration server and multi-server queries.