SQL Server Administration training course

Course Detail

SQL Server 2012/2014 Overview

• Availability and Scalability Features
• Security Features
• Data Management Features
• Administration and Maintenance Features
• Development Features
• To enable SQLiMail
• Testing mail delivery

Installing SQL Server 2012/2014

• SQL Server Editions
• Installing Microsoft SQL Server
• System Requirements
• Preparing for a SQL 2012/14 Installation
• Installation Checklist
• Setup Features
• Using the System Consistency Checker
• Component Install
• Unattended Installation
• Installation Sequence
• Installation Maintenance
• Upgrading from previous versions of SQL Server
• Retaining SQL previous versions
• To verify the Installation
• Administrative Tools

Upgrading to SQL Server Management Studio

• Management Studio Windows
• Registered Servers
• Object Explorer
• Review Database Objects
• Modify Database Properties
• Create Database Scripts
• The Maintenance Plans Node
• Solution Explorer
• Query Editor
• Template Explorer
• Dynamic Help
• Upgrading to SQL Server Computer Manager
• SQL Server 2012 or 2014 Services node
• SQL Server 2012 or 2014 Network Configuration node
• SQL Server 2012 or 2014 Native Client Configuration node
• The SQLCMD Management Utility
• SQLCMD Syntax
• SQLCMD Variables
• Dedicated Administrative Connection
• SQL Server Management Studio SQLCMD Mode
• Using SQL Management Objects
• SMO Object Model

Monitoring the Database Server

• Managing Metadata Views
• Metadata Storage
• System Views
• Information Schema
• Catalog Views
• Sys All
• Dynamic Management Views
• System Stored Procedures

Database and Index Maintenance

• Index Management
• New Index Features
• Creating Indexes
• Online Index Maintenance
• Parallel (multiple CPU) Index Operations
• Lock Options
• Included Columns in Indexes
• Partitioned Tables and Indexes
• XML Index Support
• Altering an Index
• Rebuilding an Index
• Reorganising an Index
• Disabling an Index
• Dropping an Index
• Index Fragmentation
• Using the Database Tuning Advisor

Securing SQL Server 2012 2014

• Implementing Securables
• New Security Features
• Principals
• Securables
• Permissions
• Permission Types
• Managing Logins
• Managing Users
• Managing Schemas
• Schema Definition
• Using Schemas
• Managing Execution Context
• Managing Permissions
• Manage Server Permissions
• Manage Server-Scope Securable Permissions
• Manage Database Permissions
• Manage Database-Scope Securable Permissions
• Managing Certificates
• Native Data Encryption
• Code and Module Signing

SQL Server Disaster Recovery

• Disaster Recovery
• SQL 2012 / 2014 Disaster Recovery Feature
• Enhanced Features
• Database Snapshots
• Creating a Snapshot
• Common Snapshot Applications
• Undeleting table rows
• Undoing a table update
• Recovering a dropped object
• Backup and Restore
• Backup Changes
• Restore Changes
• Media Integrity Changes
• Recovering the MASTER Database
• Create a mirrored backup
• Restore from the backup mirror
• Create a snapshot
• Restore the snapshot data
• Automate the creation of daily snapshots

Database Availability

• SQL Server 2012 /2014 Data Availability
• Database Mirroring
• The Witness Server and Automatic Failover
• Configuring Database Mirroring
• Transaction Safety Level
• Operating Modes
• High Availability Operating Mode
• High Protection Operating Mode
• High Performance Operating Mode
• Mirroring Security
• Monitoring Mirroring
• Database States for Database Mirroring
• Prepare the Servers
• Configure Mirroring
• Verify Principal and Mirror Access
• Execute the Mirror Failover
• Verify Mirror and Witness Status
• Terminating the Mirror Session

Replicating Information

• Replication Enhancements
• Stand-alone Replication Monitor
• Support for Oracle Publishing
• HTTP Synchronisation for Merge Replication
• Subscriber Initialisation
• Replicating DDL Statements
• Modified User Interface
• Replication Management Objects
• To Configure Publishing and Distribution
• Configuring Peer-to-Peer Replication
• Create a Publication
• Create a Subscription with RMO

SQL Server 2012 /2014 Integration Services

• Creating the Database Objects
• Creating a Package Using Business Intelligence Studio
• Integration Services Project Template
• OLE DB Command object
• Column Mappings
• Adding a Data Viewer

Monitoring and Performance

• SQL Server Profiler
• Optimal Data Storage
• Diagnostic Tools
• Query Analyzer
• Database Engine Tuning Advisor
• Index Optimisation
• Federated Database Servers
• Partitioned Indexes
• Lock optimsation
• Best Practices for Native XML Web Services