Microsoft 70-465: Designing Database Solutions for Micros...

Explore an overview of sequel server and the tools available to work with sequel server.

Explore SQL Server 2012 editions and instances, and the database engine with integration services, analysis services, full text indexing, and reporting services. Apply components to design, development, and exam scenarios.

Understand how SQL Server instances allow multiple relational database engines on one server, including default and named instances, with a server browser directing requests and maintaining separate security boundaries.

Explore SQL Server 2012 editions—enterprise, standard, business intelligence, and developer—and apply features like partitioning and multi-node clusters. Grasp the shift to core-based licensing and server plus cal licensing.

Discover SQL Server objects from the server to databases, file groups, tables, and schemas, and learn to navigate Object Explorer and Management Studio to create and manage these objects.

This lecture covers SQL Server system databases—master as the instance heart, model as the template for user databases, and tempdb behavior on restart, plus backups and system resource database.

Master SQL Server Management Studio as GUI for development and administration, with IntelliSense, debugging, and project-based scripting, connecting to all SQL Server editions via the native client and enabling MARS.

Explore Management Studio's object explorer and templates to script objects, recreate databases, and manage multiple scripts within a project, with separate connections and query tabs.

Customize SSMS with tools and options to adjust fonts, line numbers, query execution, results display, GUI behavior, and ANSI settings, noting these changes affect SSMS, not the server.

SQL Server Data Tools (SSDT) integrates BI templates in Visual Studio to create SSIS, SSRS, and SSAS projects; for complex packages, use SSDT instead of the import export wizard.

Discover sqlcmd, a command line interface that automates database creation via input scripts, and use the dedicated admin connection (dac) with remote access for troubleshooting.

Explore SQL Server Management Studio to run queries, use IntelliSense, connect to a named instance, and leverage templates to create databases with multiple file groups.

Configure SQL Server Management Studio query results between grid and text, noting changes take effect in the next query window, and review options for grid, text, and multiservice results.

Learn to use sqlcmd to connect to a named instance, run queries against Adventure Works 2012, and perform batch execution with go for troubleshooting via the dedicated administrative connection.

Explore exam scenarios that blend administration and development, teaching how to install and configure SQL Server instances (named and default), manage editions, and implement four-node mirroring, snapshots, and security boundaries.

Review SQL Server instances, editions, and licensing models, and apply a case study to specify cost-conscious best practices for implementing database solutions.

Explore an introduction to databases, including how data is stored and the role of files and file groups.

Learn how database files store data across primary, secondary, and log files, with 8 kilobyte data pages and extents, where each page belongs to one object.

Place the data file and the log file on separate physical disks to optimize I/O, with the log using raid 1 and 0 for recoverability.

Learn to manage database data and log files by configuring initial sizes, enabling auto grow, and prioritizing manual growth while monitoring sizes to plan for growth.

Understand tempdb, a system database for temporary data and versioning. Learn its restart behavior, configurable size, file locations, and how multiple equal-size data files on dedicated disks boost performance.

Create and configure databases in SQL Server using management studio or DDL statements, defining data and log files, model database defaults, filegroups, and recovery model choices.

Explore the fundamentals of file groups and files within database design, laying the foundation for Microsoft 70-465 topics on managing storage in database solutions.

Discover how file groups organize database data, enable granular backups, and stripe tables and indexes across files. Learn the roles of primary and secondary files in storage and recovery.

Explore how transaction log files record all transactions in a SQL Server database, enabling restore and high availability features, while virtual log files and recovery models govern truncation and backups.

Understand how the recovery mode affects transactions, logging, and checkpoints in SQL Server, including memory buffer cache, data pages, transaction logs, and dirty pages written to the data file.

Move databases between servers using detach/attach, backup/restore, or the copy database wizard in Management Studio. Use these techniques to test changes, validate upgrades, and set up reporting without impacting production.

Detach a database from SQL Server via Management Studio or sp_detach_db to move or copy data and log files, noting it requires downtime, unsuitable for 24/7 operations.

Back up a database with full, file, or file-root backups and restore it. Move data to a separate instance without offline downtime, enabling automation to test or report environments.

Copy database wizard in SQL Server Management Studio enables copying a database via detach/attach or via SMO, allowing multi-location restores without taking the source offline.

Demonstrates the database catalog view in SQL Server, highlighting system versus non-system databases and key attributes like name, database ID, and recovery model.

Create a files database with primary and secondary file groups on separate drives, configure a separate log file, set the second file group as default, and define a test table.

Learn to design databases with multiple file groups and files to spread disk io across physical drives, and enable file stream and file tables for streamed data in SQL Server.

Learn exam scenarios for designing database solutions, including creating databases via sql or management studio, and understanding primary, secondary data files, and log files.

Explore databases' structure, including data files and the transaction log, and trace transactions from client to server, memory updates, and checkpoint writes.

Explore data types and essential table concepts in introduction to tables, review NOLs, and discuss schemas.

Explore data types and the supported data types in sequel server, examine collations, Unicode characters, and null values, and learn how to convert data types.

Explore how SQL Server defines and uses system data types, aliases, and user-defined types, and learn when to apply exact numerics, char/varchar, unicode, date/time, binary, XML, and spatial data types.

Learn how collation defines data comparison, storage, and sorting across server, instance, database, and column levels. Understand ASCII and Unicode collations, and how case and accent sensitivity affect queries.

Explore how Unicode supports languages such as Japanese, Chinese, and Cyrillic with 16-bit encoding, versus ASCII’s 8-bit storage, and how characters can use up to four bytes.

Explore nulls driving three-valued logic—true, false, and unknown—and why unknown differs from empty; apply is null, is not, and coalesce under ANSI standards.

Explore implicit and explicit data type conversions in SQL Server, comparing cast for ANSI standard portability with convert as a proprietary extension, and review parse, try parse, and try convert.

Explore the fundamentals of database tables, covering normalization, temporary tables, working with tables, and schemas to design effective database solutions.

Explore how SQL Server stores data with user-defined and system tables, and how catalog views and dynamic management views enable safe access, including computed columns and file tables.

Learn how normalization reduces redundancy and boosts storage and transaction performance in OLTP databases, while enabling OLAP reporting through first to third normal form with primary and foreign keys.

Compare session and global temporary tables, their scope and lifecycle, highlighting how # and ## determine accessibility. Learn when temporary tables with statistics outperform table variables for set-based operations.

Learn to create, alter, and drop tables using SQL Server Management Studio or SQL, and use alter statements for efficient changes, including temporary tables (prefix #) and truncation.

Schemas act as security boundaries and namespaces for tables, views, and procedures. SQL Server uses default schema first; if not found, check database owner schema; functions must be fully qualified.

Explore primary keys and foreign keys with cascade and unique constraints, and distinguish composite keys, natural versus surrogate keys, and the use of identity columns.

Enforce referential integrity with foreign keys referencing a primary key or unique constraint. Prevent orphaned sales records and support recursive foreign keys, such as the reports to column.

Learn how foreign keys enforce referential integrity by default and apply on delete cascade or on update cascade to propagate changes when a primary key is deleted or updated.

Enforce column-level uniqueness with a unique constraint or unique index, which automatically creates an associated index for a non-primary key like an employee login id that must be unique.

Learn sql server data types, unicode and non-unicode handling, and the difference between length and data length. Master convert, cast, try_convert, and try_parse, plus collations and case sensitivity in queries.

Explore how SQL Server handles null values and ANSI nulls using is null, is not null, and coalesce, with practical examples on concatenation and currency calculations.

Design a sales database with employee, sales, products, and sales detail tables, primary keys, identity columns, unique and check constraints, foreign keys with cascade, and default date values.

Explore data types and table constraints in SQL Server 2012, including altering tables with and without check constraints, and how file stream, file groups, and file tables extend storage.

Cover exam essentials for database design, including data types, constraints, table creation, schema resolution, and OLTP versus OLAP with normalization options.

Evaluate the date and time data type for start and end columns to capture nanosecond precision, and note that primary keys disallow nulls while unique constraints allow a single null.

Design and implement an oltp database by selecting data types and enforcing constraints such as primary keys, foreign keys, and checks, mapping business needs to logical and physical designs.

Explore the fundamentals of indexing, including heaps, clustered indexes, and non-clustered indexes, in this introductory module for designing database solutions.

Explore data storage and indexing in SQL Server, including heaps, clustered and non-clustered indexes, 8 kilobyte data pages, extents, and uniform versus mixed extents.

Understand that indexes are not recognized by ANSI standards, yet implemented in most relational databases, with primary keys and unique constraints mapped to indexes, over indexing can degrade performance.

Explore heaps, where rows lack a primary key or clustered index, triggering table scans, and learn how inserts use the 8 kilobyte page, while updates and deletes reuse space.

A clustered index orders a table's rows using a B-tree, with leaf level data pages. A table can have only one clustered index, which may be unique or composite.

Learn how non-clustered indexes sit atop a clustered index and use leaf-page pointers to the clustered index for phone-number lookups returning first name, middle name, last name, and address.

Analyze indexing strategies for average workloads by balancing clustered and non-clustered indexes, considering selectivity, density, and composite keys, to optimize overall performance.

Create filter indexes on non-clustered indexes with a where clause to limit rows and improve selectivity. Use included columns to create covering indexes within the 900-byte key limit.

Improve database performance by maintaining indexes, addressing index fragmentation, and applying index tuning techniques effectively.

Explore how indexes are physical structures that require monitoring for fragmentation, how page splits occur, and how fill factor and pad index settings manage clustered and non-clustered indexes.

Identify internal and external index fragmentation and how fill factor affects performance in an OLTP database. Decide between online reorganize or offline/online rebuild based on fragmentation thresholds and space needs.

Use SQL Server Profiler to monitor activity, capture deadlocks, and replay traces for performance tuning; for longer traces, prefer server-side traces stored in files via system stored procedures.

Learn how the database engine tuning advisor analyzes workloads from SQL Server Profiler to generate index and statistics recommendations, with options to implement immediately or script and schedule changes.

Demonstrates creating clustered and non-clustered indexes on a telephone book table, and how index use changes execution plans from table scans to index seeks, emphasizing selecting needed columns for performance.

Explore index fragmentation using management studio standard reports, index usage, index physical statistics, and dynamic management views via dm_index_physical_stats with database_id and object_id to show average fragmentation.

Address index fragmentation by rebuilding or reorganizing with alter index, using a product table example and 80 fill factor in tempdb, and use profiler traces and database engine tuning adviser.

Explore how the database engine tuning adviser analyzes workloads from a file trace to suggest indexes and statistics, save and apply recommendations, and analyze specific queries.

explore how the database engine tuning adviser uses server-side traces and workload data to suggest indexing strategies and object optimizations for query performance.

Learn how to analyze business scenarios for exam design, decide when to use clustered vs non-clustered indexes, manage fragmentation, and employ tools like profiler and the database engine tuning adviser.

Assess and optimize database performance by examining clustered and non-clustered indexes, leaf pages, and included columns to create covered indexes, review workload, and assess rebuilding or reorganizing effects.

Explore an overview of table and index partitioning, and examine how transactions and locking work in SQL Server.

Explore partitioning concepts, including partition functions and schemes, and create partitioned tables and indexes across file groups to improve scalability, manageability, and performance, with split, merge, or switch options.

Create a partition function to map rows into partitions by a column value. Define the input data type, choose left or right range (default left), and set boundary values.

Design the partition scheme to map the partition function to the file groups, referencing an existing partition function and specifying which file groups will be used.

Create a partitioned table or index with a partition function and scheme to map sales data to archive, current, and historic filegroups for improved performance and scalability.

Discover how transactions in SQL Server use explicit and implicit modes, with locking to enforce ACID properties—atomic, consistent, isolate, durable—while autocommit commits by default unless you begin a transaction.

Compare implicit and explicit transactions in SQL Server, including commit and rollback behavior, begin tran usage, and the impact of locking and isolation levels on performance and deadlocks.

Lock types include shared, update, and exclusive, with intent, schema, and table locks shaping a locking hierarchy, while serializable transaction isolation level and deadlocks guide transaction safety.

Explore partitioned database design with file groups, a date-based partition function, and a partition scheme to manage historic, current, and archive data, including transactions and locks.

Demonstrate partition tables and boundary values, use catalog views to inspect partitions, and explore transactions and locks, including isolation levels and a no where clause update.

Explore partition tables for data distribution across file groups to boost scalability, and monitor transactions and locks using isolation levels, read uncommitted, dirty reads, and dynamic management views.

Explore exam scenarios on table and index partitioning, partition function and scheme, creating partitioned tables or indexes, explicit and implicit transactions, commit or rollback practices, and locking and isolation levels.

Explore table and index partitioning to boost performance and scalability for large databases, and examine transactions and locking to ensure consistency in SQL Server.

Explore an overview of database architecture, examine files and file groups, and discuss backing up and restoring databases.

Explore database architecture by examining data and log files, the recovery model, and file groups, including the primary file group housing system tables and system objects.

Learn how the primary data file stores system and user tables and data in the primary file group, while secondary data files enable scalability and backup and restore via extents.

Explore how SQL Server records every transaction in the transaction log to preserve ACID properties, loads modified data pages into the buffer cache, and checkpoints dirty pages to data files.

Understand how full, bulk-logged, and simple recovery models affect transaction log growth, backups, and point-in-time recovery to choose the right model for your database.

Explore the main database backup types—full, differential, and transaction log backups—plus tail log, file backups, copy-only backups, backup compression, and mirrored sets, and understand performance impact and recovery model considerations.

Learn how differential backups, created after a full backup, capture all changes since the last full backup, support any recovery model, and require the full backup for restoration.

Explore transactional backups and the transaction log to enable point-in-time recovery, manage log growth, and restore data using full backups, differential backups, and tail log backups.

Explore file and file group backups for large databases, offering granular, bite-sized recovery options and faster restores by avoiding dependencies; plan carefully to prevent cross-group dependencies from breaking.

Discover copy-only backups, how they preserve restoration order and the transaction log, and how full, differential, and transaction log backups affect the backup chain when moving production data to development.

Learn how backup compression in SQL Server 2008 speeds backups and restores; compressed backups cannot co-exist with uncompressed backups, while mirrored media sets provide redundancy across up to four devices.

Discover how to verify backups for recovery using checksums and restore verification, covering full, differential, and transaction log backups, and learn that restoration is the only definitive test of viability.

restore databases by applying full backups, differential and transaction log file and file group backups, then use point-in-time or standby restore with data copy, redo, and undo of uncommitted transactions.

Restore a full backup via SQL Server Management Studio or T-SQL, with recovery by default, or use no recovery to apply tail log and differential or transaction log backups.

Restore the transaction log using management studio or the sequel restore log statement. Ensure restoration follows sequential order; the server indicates out of order attempts and recovery progress.

Take the tail log backup, restore the file or file group from the most recent backup, apply the most recent differential, then restore transaction logs, finishing with tail log recovery.

Restore a database to a point in time using Management Studio or through the sequel, with stop at mark options and immediate recovery when point lies within the time frame.

Learn how the standby option keeps a database in read-only mode during restore, using a standby file to record active transactions, and requires restoring to the same version.

Take a tail log backup to enable point-in-time restoration, then restore the latest full backup, differential backups, and all transaction logs in sequence with recovery.

Restore system databases by backing up the master and model databases, start sql server in single-user mode after corruption, and follow best practices for msde, logins, and alerts.

Restore the master database by starting SQL Server in single-user mode, issuing the restore database command for the most recent full backup, and restarting SQL Server normally.

Demonstrates backing up and restoring SQL Server in full recovery mode, including full, differential, and log backups, with standby restores and backup metadata inspection.

Explore backing up and restoring an Adventure Works database in standby, applying full, differential, and log backups, and understanding recovery and LSN progression to avoid opening the database prematurely.

Apply tail backup with standby or with no recovery to restore to a point in time, and use copy-only backups with compression to avoid impacting the restore chain.

Learn how copy-only backups avoid affecting restorations and how file and file group backups enable granular, faster backups for large databases; review full, differential, and tail log backups.

Master recovery models and backup types—full, differential, copy only, and transaction log backups—and learn how to restore using SQL Server and Management Studio, including point-in-time recovery for business scenarios.

Explore backup and recovery concepts, including full, differential, transaction log, copy-only, and tail log backups, and the restoration sequence from full to differential and log backups, noting single-user mode.

Introduce views and how to work with them, then cover functions, function types, and practical usage.

Explore an introduction to views and the different types of views. Learn how to work with views and the considerations when using them.

A view acts as a virtual table defined by a select statement in the from clause, hiding query complexity, enabling permission control, and supporting insert, update, delete, and select.

Explore system and user defined views in SQL Server, including catalog and compatibility views, information schema, dynamic management views, and how user defined views provide database-specific metadata.

Create and alter sql server views with a single select statement, using proper column aliases, and manage permissions, nesting up to 32 levels, and metadata refresh via sp_refreshview.

Learn how updatable views affect underlying tables and how indexed views with schema binding enable a unique clustered index for potential query optimization in enterprise edition.

Partitioned views consolidate data from multiple linked servers representing regional divisions, using a select statement that unionizes all divisions to present a single resultset for organization-wide queries.

Explore the fundamentals of database functions, their types, and how reusable SQL code can return a table or a single value, created with SQL or CLR, while not modifying data.

Explore system functions and user defined functions in database design, including scalar valued and table valued functions, and differentiate inline and multi statement table valued functions with their return sets.

Define scalar valued functions with input parameters via create function to return a single value, and distinguish inline from multi-statement table valued functions that return tables.

Explore system views and dynamic management views to inspect database properties and missing index details. Create a phone numbers view to surface names, addresses, and phone data for secure reporting.

Practice creating updatable views and using triggers to handle multi-table changes, explore indexed views with schema binding and clustered indexes, and review scalar and table-valued functions.

Define a fullname scalar function returning a 101-character full name from first, middle, and last names, and explain inline and table-valued functions with fully qualified schema references.

Analyze design exam scenarios to decide when to use views, functions, indexed views, and stored procedures, focusing on performance limits and data modification rules.

Explore views as single-result queries, updatable under conditions; understand indexed views with schema binding and a unique clustered index, and review user-defined scalar and table-valued functions that cannot modify data.

Discover stored procedures and how to work with them. Also explore an introduction to triggers and how to work with triggers.

Explore stored procedures, their types, and parameters, then discuss compilation and considerations for when to use them.

Stored procedures overcome views and functions limitations, support insert update delete, input output parameters, return one to many result sets and temporary tables, improving performance and security over dynamic sql.

Explore system and extended stored procedures, their admin uses and roles, prefixes like xp_ and espy underscores, and note deprecation plus limitations such as no create function or alter schema.

Explore input and output parameters in stored procedures, enforce data types to prevent SQL injection, and use return codes to indicate success or failure.

Explain how stored procedures create a query plan through parsing, resolving, and optimization, and how parameter sniffing and options like with recompile or optimize for influence plan caching.

Understand how the execution context follows the caller for stored procedures, functions, or views, and how with execute as escalates permissions during execution without overriding underlying permissions, then reverts.

Define read-only table types in SQL Server 2008 to pass a whole table as an input parameter to a stored procedure for a single insert statement with multiple values.

Explore an introduction to triggers, outline different types, and explain how to work with triggers.

Triggers execute a table's associated stored procedure on insert, update, or delete, adding overhead and risking rollback of the entire transaction; test thoroughly and consider alternatives for high-OLTP latency.

Explore dml trigger types, after and instead of, and how they fire after transactions or prevent changes. Learn about inserted and deleted virtual tables in the temp database.

Learn how triggers can nest up to 32 levels to fire cascading transactions across tables, and why recursive triggers risk endless loops; they are disabled by default, prefer non-recursive logic.

Learn how a table can have multiple triggers for insert, update, or delete, and that SQL cannot guarantee their firing order; use sp_settriggerorder to pick first or last.

Create and execute stored procedures with input parameters and default values, including parameter passing. Implement triggers on a sales table to audit inserts and updates, recording the user and action.

Explore how stored procedures accept input parameters and defaults, and how triggers—after and instead of—control data actions, with examples on audit tables, errors on delete, and handling inserted/deleted virtual tables.

Analyze exam scenarios for designing database solutions, focusing on stored procedures, input/output parameters, SQL injection defense, and when to use constraints instead of triggers.

Explore stored procedures and triggers, their role in preventing sequel injection attacks, plus input/output parameters, zero to many result sets, and transactional behavior; include instead of triggers and DDL triggers.

Investigate authentication server and database roles; discuss permissions and how they apply to various database roles.

Explore sequel security as a layered model from server logins to database access. Explain authentication options: SQL-based or Windows integrated, and how implicit and explicit denies govern authorization.

Explore two authentication methods for SQL Server: integrated security using Windows groups or users. Mixed mode covers non-windows users and a contained database where users exist only inside the database.

Learn how sql logins use windows authentication and enforce password policies. Manage logins in master or contained databases, with options for mapping to sql users.

Map database users to logins or create them via SQL Server Management Studio, using contained databases for portability; disable guest connect access by default.

Distinguish impersonation from delegation and learn how Kerberos enables cross-server access and double-hop scenarios, with SharePoint and SQL Server using service principal names.

Explore eight server roles in SQL Server, how user-defined roles since 2012 grant permissions across an instance, and the public server role with login placement and cautions about removing logins.

Utilize the nine pre-created database roles as groups to manage user permissions within a database. Create your own user defined roles for finer-grained access; modifications stay within that database.

Create application roles in a database, containing no members, to allow app connections; call the system stored procedure to switch to the application role and revert when the app closes.

Explore authorizing access in the sequel server through scope, principles, and permissions; learn grant, revoke, and deny rules, including implicit deny and cascading revocation.

Apply table and view permissions such as select, insert, update, delete, and references, prefer views for column security, and grant execute on procedures and functions while managing ownership chains.

Use schemas as namespaces to organize objects, assign permissions, and resolve objects by checking the user's default schema first, then the dbo schema, then the database schema, with qualified names.

Explore SQL server security by inspecting server principals in catalog view, creating a SQL login that doesn't map to Windows, enabling guest, and mapping a database user to a login.

Learn how to manage SQL server security with grant, deny, revoke, and implicit deny on object permissions, and implement contained databases and contained users for database authentication.

Review exam essentials: server and database roles and their permissions, understand grant, revoke, and deny, and grasp contained databases, logins, and database users mappings for least amount of administrative effort.

Explore the layered security model of SQL Server, from server to database to objects, outlining principals, server roles (including user-defined ones), and login mappings, plus grant, revoke, and deny permissions.