Microsoft SQL Server 2012 Certification Training Exam 70-462

Pre-allocate space and use fixed-size growth for data files to minimize frequent autogrowth, and grow transaction logs in 8 gig increments to minimize virtual log files.

Configure the management data warehouse in SQL Server 2012 to capture data across servers. Use the disk usage and growth reports for capacity planning and monitor database growth.

Design database storage by choosing raid configurations (raid 1, 5, 10) and placing data files and transaction logs on appropriate arrays, while baseline monitoring response times and i/o per second.

Configure a SQL Server standby server, a full copy of the database on a secondary server to offload workload for reporting and high availability via transaction log shipping.

Configure a SQL Server standby by implementing log shipping between a primary and secondary server, with backup, copy, restore jobs, schedules, alerts, and enable read queries against the secondary.

Use the SQL Server configuration manager to configure domain and local service accounts, grant needed permissions, and restart SQL Server instances, while evaluating account strategies for security and manageability.

Test database connectivity using telnet, the odbc connection manager, and the sql cimt command line tool, verify port access and authentication, and run basic queries against the server.

Explore three basic installation methods for the sql server database engine—graphical wizard, command line, and automated deployment—emphasizing ease, repeatability, and VM readiness.

Install the SQL Server database engine using the default instance, configure service accounts and authentication, and set data, logs, backups, and a config file for automated, repeatable setup.

Demonstrates a command line installation of SQL Server 2012, detailing installation parameters, features, license terms acceptance, batch file workflow, plus image prep and gold image cloning for virtual machines.

Install and configure a prepared standalone SQL Server instance via the installation center, validating rules, licensing, using Windows authentication, and enabling automation with a config file.

Compare graphical and command-line installers for SQL Server Integration Services, highlighting ease of use and repeatability for large deployments versus one-off installations.

Enable and disable features in SQL Server 2012 using the installation center, installing or removing features on an existing instance to reduce attack surface and resource use.

Learn how to move databases by detaching and attaching them using SQL Server Management Studio or T-SQL, including single-user mode with rollback, and handling multiple database files.

Migrate logins between SQL Server instances using sp_help_revlogin to script and recreate logins with sids, or use SQL Server Integration Services transfer logins task to move logins while preserving sids.

Create a full text index by choosing storage location, accent sensitivity, and change tracking with automatic population, or schedule updates, and use int or bigint primary keys for best performance.

Configure FILESTREAM in SQL Server 2012 by enabling it in configuration manager, setting instance and database properties, and creating a FILESTREAM filegroup with a target directory.

Configure file table by enabling file stream across SQL Server configuration manager, instance, and database; then set a file group and use the network share for writes.

Learn to create and manage sql agent jobs. Use management studio or t-sql to configure steps, schedules, alerts, and step types like os commands, powershell, ssis packages, replication options.

View and manage SQL Server Agent jobs using the activity monitor, job editor, and sp_help_job to track status, history, and schedules on the local server.

Set up and deploy the same maintenance or auditing jobs across multiple servers from a single master server, with centralized job history stored on one server via multiservice jobs.

Explore multiple files and filegroups to distribute I/O, assign specific tables to filegroups, use proportional fill for even data spread, and isolate staging from production for performance.

Discover the three SQL Server recovery models: snapshot, bulk logged, and full, and how they govern logging, recoverability, and point-in-time restores. Learn how to change models and perform backups.

Create file groups to isolate IO for objects and spread files across disks, using one or more files per group. Add via SSMS or T-SQL with online, no downtime operations.

move tables between file groups by rebuilding the clustered index or creating a new table; both require significant I/O and space, with online rebuild possible on SQL Server 2012 Enterprise.

Enable contained databases at the instance level and configure each database to partial containment using SSMS or sp_configure.

Explore SQL Server data compression, including row-level and page-level methods, and how values compress while text data may not. Learn to estimate savings with sp_estimate_data_compression_savings and enable via alter index.

Configure transparent data encryption by creating a master key, a certificate, and a database encryption key, then enable encryption on the Adventure Works database via Management Studio.

Set up table partitioning by defining a partition function and a partition scheme with breakpoints, range left or range right. Bind the partitioning to tables and test with sample data.

Explore how the dollar sign partition function reveals a table's partition placement, verifies partition layouts with min/max dates, and demonstrates adding and removing partitions for monthly workloads.

Diagnose log growth in SQL Server 2012 by analyzing long transactions, recovery modes, and log backups, shrink or grow the transaction log, and monitor with DBCC LOGINFO and DBCC OPENTRAN.

Learn how transaction log growth affects virtual log files, why smaller transactions improve performance and reduce locking, and how more frequent log backups minimize data loss.

DBCC CHECKDB scans every page, table, and catalog to detect corruption, validates data values, and reports integrity issues, with repair options and backup restores as needed.

The lecture explains installing default and named SQL Server instances, outlining the wizard checks, licensing, updates, and how to configure the instance name and directory.

Install a default or named SQL Server instance, configure collations, authentication mode, service accounts, and data directories, and recognize that each instance operates independently with shared components like Management Studio.

Isolate SQL Server instances to specific processors using CPU affinity via SQL Server Management Studio or T-SQL scripts, reducing workload contention and preserving CPU resources for other services.

Explore instance level settings in SQL Server, configured via Management Studio and SQL Server Configuration Manager, including memory and containment, security, connections, parallelism, auditing, and cross-database ownership chaining.

Explore instance level settings in SQL Server, including default index fill factor, tape backup settings, backup compression, and recovery interval, then configure default database file locations and file stream settings.

Explore instance level settings in SQL Server configuration manager and Management Studio, including contain databases, file stream, triggers, blocking, parallelism, and always on options to optimize performance.

Explore memory settings in SQL Server 2012, including minimum and maximum server memory, index creation memory, and minimum memory per query, with practical Management Studio adjustments.

Configure database mail in SQL Server 2012 using profiles and accounts to send emails from different addresses. Explore authentication options, failover, and troubleshooting with logs.

Fill factor defines the free space per data page used when rebuilding indexes, with 0 as default; set 70–90 for non-clustered and 30–50 for unique identifiers, and monitor fragmentation.

Install a clustered SQL Server instance on Windows Server 2012, prepare shared storage, and configure a unique network name for a two-node failover cluster.

Install a clustered SQL Server 2012 instance by configuring resource group, cluster network, domain accounts with Windows authentication, and data on a network share while tempdb resides on local disk.

Add a node to an existing SQL Server failover cluster using the streamlined wizard, ensure edition matches (evaluation), validate with rule checks, and reuse a configuration file for nodes.

Install and verify a failover clustered instance of SQL Server 2012, configuring network name, IP address, SQL Server and SQL Server Agent, and confirm ownership and crash recovery across nodes.

Explore multi-instance clustering in SQL Server, where each instance runs on its own hostname, IP address, port, and shared disk, enabling floating resilience across cluster nodes.

Enable multi-subnet clustering to fail over SQL Server between two data centers, using two IP addresses, with IP ownership guiding which address comes online.

Evict the failed cluster node and rebuild the machine, then reinstall SQL Server 2012 and re-add it to the cluster after cleaning up DNS and Active Directory resources.

Explore SQL Server replication for reporting, detailing publisher, distributor, and subscriber roles, publications, articles, and transactional replication with push and pull configurations.

Set up transactional replication for reporting by configuring publisher, distributor, and subscriber, creating the distribution database and snapshot folder, and applying table filters and security settings.

Configure transactional replication subscriptions for reporting, choosing push or pull depending on architecture. Create the subscriber database, set up security, initialize the snapshot, and monitor progress with replication monitor.

Explore transactional replication for reporting and use the replication monitor to keep near real-time data while monitoring latency and backlog.

Explore how locking and blocking work in sql server, covering shared, update, and exclusive locks, row-to-table escalation, and using dmvs to diagnose long transactions and key lookups.

Diagnose and resolve locking and blocking in SQL Server by identifying blocking sessions with dynamic management views, then fix root causes with indexing, query rewrites, faster storage, or more RAM.

Diagnose wait stats in SQL Server 2012 using DMVs to identify bottlenecks, distinguish misleading waits like SOS scheduler yield and CXPACKET, and analyze blocking and IO waits.

Learn to use SQL Server Profiler in Management Studio to capture and analyze executed statements, durations, reads, and writes, with templates and filters, and prepare for extended events.

Use performance monitor, a windows monitoring tool, to view SQL Server metrics—from disk counters to buffer manager and page life expectancy—across remote servers in real time and historically.

Learn to create, start, and configure a performance monitor data collector set, choose counters, set sampling intervals, save logs, and view real-time and historical data locally or remotely.

Explore how extended events in SQL Server 2012 provide a lightweight, in‑engine replacement for Profiler and server‑side tracing, with easy session setup, filters, and live data viewing.

Learn to configure SQL Server auditing with audit and server audit specifications, choose destinations, set rollover and failure actions, and monitor audit changes for compliance.

Explore how data collector gathers server activity, query statistics, and utility information for a management data warehouse in SQL Server 2012, with configurable intervals and centralized reporting.

Discover how policy based management uses facets, conditions, and policies to enforce configuration and naming standards across SQL Server instances, with real-time or scheduled checks against targeted objects.

Explore the three SQL Server recovery models—snapshot, bulk-logged, and full recovery—and how they govern logging, backups, and point-in-time recoverability.

Discover how mirrored backups use a mirror to clause to create redundant copies on separate disks in parallel, enabling restoration from either backup file and protecting against disk failures.

Striped backups across multiple disks boost throughput by distributing data into several files. Restore requires all files; missing ones prevent recovery, and pairing striping with mirroring adds redundancy.

Restore full backups to return the database to the exact state at backup completion, including the transaction log. Understand the restore syntax and recovery options, including no recovery and standby.

Restore a database by first applying a full backup, then the most recent differential backup to reach state at the backup time. Differentials are cumulative until the next full backup.

Restore a database by applying the full backup, a differential, and then transaction log backups in order to reach the desired point in time, using no recovery.

Perform filegroup restores in SQL Server 2012 by restoring primary file first. Use partial restores, no recovery, and then bring other filegroups online with transaction logs to finish roll forward.

Verify backups by restoring them on another server to confirm data integrity; restore verify only checks that the media is valid, not that the backup data is valid.

Restore the master database by starting SQL Server in single-user mode, logging in with sqlcmd, and restoring from disk before restart, ensuring admin rights and firewall considerations.

Restore the msdb database in SQL Server 2012 by stopping the SQL Server Agent and any apps using it, performing a normal backup restore, and restarting the SQL Server Agent.

Restore the model database in SQL Server 2012 using a normal restore database command to recover from corruption, restoring it to its original state from backups.

Restore databases protected by transparent data encryption by first restoring the certificate in the master database, then the database with the restore command, using the private key and password.

Learn how restoring a single database page quickly fixes corruption in large databases, saving hours by restoring just one page from a full backup and rolling forward transaction logs.

Master point in time restores in SQL Server 2012 by applying transaction log backups under a full recovery, stopping at a precise moment using stop at and recovery options.

Perform index maintenance by rebuilding or defragmenting indexes to restore order and update statistics. Online or offline rebuilds depend on edition and data types; defragmentation remains online.

Identify index fragmentation in SQL Server 2012 using the sys.dm_db_index_physical_stats function with database_id, object_id, index_id, partition_id, and inspection_type (limited, sample, detail) to assess fragmentation and index size.

Identify unused indexes by querying the dynamic management view sys.dm_db_index_usage_stats to compare user seeks, scans, lookups, and updates, filter by database, and decide which indexes to remove to improve performance.

Learn how filtered non-clustered indexes index only current employees using a where clause on an active flag, and how filtered statistics require manual updates via index review or update statistics.

Discover how SQL Server updates statistics to reflect changes, triggered at 20 percent plus 500 rows, and how manual updates or espy_update_statistics adjust thresholds for large tables.

Discover how full text indexes enable natural language searches using contains, contains table, free text, and free text table predicates for text and binary data in SQL Server 2012.

Discover how column store indexes in SQL Server 2012 store data by column with x velocity compression, delivering fast scans for warehouses while remaining read-only and not suitable for OLTP.

Use the import export wizard in management studio to import and export data between SQL Server and sources like text files, Excel, and ODBC drivers, and save the SOSIAS package.

Explore the bulk copy program (BCP) in SQL Server 2012, a versatile command-line tool for exporting and importing data from tables or queries, with batch processing and format file options.

Bulk insert imports data from text files into a table, with batch size, field terminator, and options like first row skip and tab lock.

Explore how logins grant instance-level access and permissions, while users grant database-level rights, including contained users with passwords and the implications for impersonation and linked servers.

Learn to create logins in SQL Server 2012 using Management Studio or T-SQL, including simple and Windows logins. Understand password policy options and the login vs user distinction.

Explore the nine fixed server roles in SQL Server 2012, including bulkadmin, dbcreator, diskadmin, processadmin, public, securityadmin, serveradmin, setupadmin, and sysadmin, and learn their specific permissions and security implications.

Create and manage user defined server roles at the SQL Server instance level, nest them with fixed server roles, and assign permissions to support junior admins and failover availability groups.

Learn how certificate logins in SQL Server serve code signing, not authentication. Create a certificate in the master database, then create and map a login from that certificate.

Explore 31 instance level rights in SQL Server 2012, categorized into operational, visibility, and security permissions, including create any database and alter server state and availability group permissions.

Explore the 61 database level permissions, grouped into operational, object, security, and data operations, to secure objects, databases, and user access.

Master fixed database roles in SQL Server 2012, learn each role’s permissions, from data reader to security admin, and how to use denies and overrides for precise access control.

Create user defined database roles to simplify granting permissions; members inherit role permissions, and you can create or alter roles with SQL Server Management Studio or T-SQL.

Application roles add a second layer of authentication, granting permissions to the application; once activated with a password, users inherit those permissions, preventing direct access via Excel or Management Studio.

Create and manage SQL Server users: mapped, unmapped, and contained users, map to a login or create a contained user with a password, and configure default schema, roles, and permissions.

Learn to manage certificates in SQL Server by changing certificate passwords with the alter certificate statement, using old and new passwords, while meeting Windows password policy and avoiding data decryption.

Grant or deny access to specific endpoints to control which applications can connect, including default endpoints, using Management Studio or sql commands.

Database mirroring maintains a secondary copy on a mirror server and can run synchronously or asynchronously, using endpoints, encryption, and a witness for automatic failover, though deprecated yet still supported.

Enable and configure always on availability groups across sites, defining availability replicas, databases, and listener; use the wizard to set up endpoints and encryption, with synchronous and asynchronous data movement.

Configure always on availability groups with full data synchronization, endpoints, extended event sessions, and a listener, using the wizard to manage backups, replicas, and failover.

Learn how to perform a database mirroring failover from the primary server, either via sql or the gui, and understand data loss, planning, and high safety mode of synchronous mirroring.

Learn how to manually and automatically fail over Microsoft SQL Server 2012 AlwaysOn availability groups, including synchronous replicas, listener usage, and common misconceptions about downtime and data loss.

Utilize the built-in database mirroring monitor and dashboard to track synchronization state, latency, and send/receive rates, review history, configure thresholds, and watch for synchronous vs asynchronous operation with performance counters.

Master SQL Server 2012 topics for exam 70-462, including backups, encryption, data compression, always on availability groups, auditing, and recovery concepts.