Microsoft 70-465: Designing Database Solutions for Microsoft SQL Server 2012

    £495.00

    Description

    Description

    This course is specifically for participants to measure their knowledge and skills on making the appropriate job role decisions around designing database solutions for Microsoft SQL Server 2012.

    This course is designed for Database professionals who design and build database solutions. These Database professionals are responsible for the creation of plans and designs for Database structure, storage, objects, and servers, as well as creating the plan for the environment in which the database solution runs. Primary responsibilities may include but are not limited to designing:

    • Data compression strategies
    • Database models
    • Full-Text Search strategies
    • High-availability solutions
    • Maintenance strategy for database servers
    • Management automation strategies
    • Migration, consolidation, and upgrade strategies
    • Database, schema, and object security parameters

    This training course on designing database solutions for Microsoft SQL Server 2012 prepares participants for the Microsoft Exam 70-465:

    • Creating and altering tables (complex statements)
    • Designing, implementing, and troubleshooting security
    • Designing the locking granularity level
    • Maintaining indexes
    • Implementing data types
    • Creating and modifying constraints (complex statements)
    • Working with XML Data
    • Writing automation scripts
    • Designing and implementing stored procedures
    • Designing T-SQL table-valued and scalar functions
    • Creating, using, and altering user-defined functions (UDFs)
    • Creating and altering views (complex statements)
    • Designing tables
    • Designing for concurrency
    • Creating and altering indexes
    • Designing data integrity
    • Designing for implicit and explicit transactions
    • Optimizing and tuning queries
    • Troubleshooting and resolving performance problems
    • Optimizing indexing strategies
    • Capturing and analyzing execution plans
    • Collecting performance and system information

    Syllabus:

    Module 1

    • Course Introduction
    • Course Modules
    • Introduction to SQL Server 2012
    • SQL Server Instances
    • SQL Server Editions and Licensing
    • SQL Server Objects
    • System Databases
    • SQL Server Management Studio
    • SSMS Components
    • SQL Server Data Tools
    • SQLCMD
    • Working with SSMS
    • Working with SQLCMD
    • Exam Scenarios
    • Module Review

    Module 2

    • Introduction to Databases
    • Database Files
    • Temp db
    • Working with Databases
    • Introduction to Filegroups and Files
    • Introduction to Log Files
    • Transactions
    • Moving Databases
    • Detaching and Attaching Databases
    • Backing Up and Restoring Databases
    • Copy Database Wizard
    • Database Catalog View
    • Creating and Configuring Databases
    • Exam Scenarios
    • Module Review

    Module 3

    • Introduction to Data Types and Tables
    • Supported Data Types
    • Collations and Unicode Characters
    • Introduction to NULLS
    • Converting Data Types
    • Introduction to Tables
    • Normalization
    • Temporary Tables
    • Working with Tables
    • Schemas
    • Primary and Foreign Keys
    • UNIQUE Constraint/Index
    • Data Types in SQL Server Demonstration
    • Working with Tables Demonstration
    • Exam Scenarios
    • Module Review

    Module 4

    • SQL Server Indexing
    • Introduction to Indexing
    • Heaps
    • Clustered Indexes
    • Non-Clustered Indexes
    • Maintaining Indexes
    • Index Fragmentation
    • SQL Server Profiler
    • Index Tuning
    • Working with Indexes Demonstration
    • SQL Server Tuning Advisor Demonstration
    • Module Review

    Module 5

    • Performance Considerations
    • Partitioning Function
    • Partition Scheme
    • Partitioned Table or Index
    • Introduction to Transaction
    • Implicit and Explicit Transactions
    • Locking
    • Working with Partition Tables, Transaction and Locks
    • Exam Scenarios
    • Module Review

    Module 6

    • Backup and Restore
    • Database Data and Log Files
    • Recovery Model
    • Database Backups Types
    • Backup Compression and Mirrored Media Sets
    • Verifying Backups
    • Restoration Process
    • Restoring Backup Types
    • Restoring to a Point in Time
    • WITH STANDBY
    • Restoring a Database
    • Restoring System Databases
    • Restoring the Master Database
    • Backing Up and Restoring SQL Server
    • Exam Scenarios
    • Module Review

    Module 7

    • Views and Functions
    • Types Of Views
    • Creating and Altering Views
    • Updatable and Indexed Views
    • Partitioned Views
    • Introduction to Functions
    • Function Types
    • Scalar and Table Valued Functions
    • Working with Views and Functions
    • Exam Scenarios
    • Module Review

    Module 8

    • Stored Procedures and Triggers
    • Types of Stored Procedures
    • Parameters
    • Stored Procedure Compilation
    • Execution Context
    • Introduction to Triggers
    • Types of Triggers
    • Nested and Recursive Triggers
    • Trigger Firing Order
    • Working with Stored Procedures and Triggers
    • Exam Scenarios
    • Module Review

    Module 9

    • SQL Server Security
    • SQL Authentication
    • SQL Logins
    • Accessing a Database
    • Authorization Across Servers
    • Server Roles
    • Database Roles
    • Application Roles
    • Authorizing Access
    • Configuring Access Using Schemas
    • Working with SQL Security
    • Exam Scenarios
    • Module Review

    Module 10

    • Installing and Upgrading SQL
    • Server Resources
    • 32 vs. 64 Bit
    • SQL Requirements
    • Software Requirements
    • Collations
    • Service Accounts
    • Installing SQL Server
    • Troubleshooting Installation
    • Upgrading SQL Server
    • Unattended Installations
    • Exam Scenarios
    • Module Review
    • Course Review

    Course Delivery:    Online