Objective :

After completing this course, Participants will be able to:

• Describe core database administration tasks and tools
• Install and configure SQL Server 2014
• Configure SQL Server databases and storage
• Plan and implement a backup strategy
• Restore databases from backups
• Import and export data
• Monitor SQL Server
• Trace SQL Server activity
• Audit data access and encrypt data
• Perform ongoing database maintenance
• Automate SQL Server maintenance with SQL Server Agent Jobs
• Configure Database Mail, alerts and notifications
• Plan the components of a BI solution
• Plan a BI infrastructure
• Design a data warehouse
• Design an ETL solution
• Plan analytical data models
• Plan a BI delivery solution
• Design a Reporting Services solution
• Design an Excel reporting solution
• Plan a SharePoint Server BI solution
• Monitor and optimize a BI solution
• Plan for BI operations
• Use Report Designer and Report Manager.
• Work with solutions and projects.
• Create basic reports.
• Enhance reports with formatting, images, and charts.
• Effectively group and summarize data.
• Work with parameterized reports.
• Build dynamic reports with expressions and parameters.
• Incorporate charts and custom maps.
• Work with drillthrough reports.
• Manage reports.
• Utilize Report Builder.
• Authenticate and authorize users
• Assign server and database roles
• Authorize users to access resources
• Protect data with encryption and auditing
• Describe recovery models and backup strategies
• Backup SQL Server databases
• Restore SQL Server databases
• Automate database management
• Configure security for the SQL Server agent
• Manage alerts and notifications
• Managing SQL Server using PowerShell
• Trace access to SQL Server
• Monitor a SQL Server infrastructure
• Troubleshoot a SQL Server infrastructure
• Import and export data

Content :

Administering Microsoft SQL Server Databases

1 - Introduction to SQL Server 2014 Database Administration

• Database Administration Overview
• Introduction to the SQL Server Platform
• Database Management Tools and Techniques
• Lab : Using SQL Server Administrative Tools
• Using SQL Server Management Studio
• Using the sql cmd Utility
• Using Windows PowerShell with SQL Server

2 - Installing and Configuring SQL Server 2014

• Planning SQL Server Installation
• Installing SQL Server 2014
• Post-Installation Configuration
• Lab : Installing SQL Server 2014
• Preparing to Install SQL Server
• Installing SQL Server
• Performing Post-Installation Configuration

3 - Working with Databases and Storage

• Introduction to Data Storage with SQL Server
• Managing Storage for System Databases
• Managing Storage for User Databases
• Moving Database Files
• Configuring the Buffer Pool Extension
• Lab : Managing Database Storage
• Configuring temp DB Storage
• Creating Databases
• Attaching a Database

4 - Planning and Implementing a Backup Strategy

• Understanding SQL Server Recovery Models
• Planning a Backup Strategy
• Backing up Databases and Transaction Logs
• Using Backup Options
• Ensuring Backup Reliability
• Lab : Backing Up SQL Server Databases
• Backing Up Database
• Performing Database, Differential, and Transaction Log Backups
• Performing a Partial Backup

5 - Restoring SQL Server 2014 Databases

• Understanding the Restore Process
• Restoring Databases
• Advanced Restore Scenarios
• Working with Point-in-Time Recovery
• Lab : Restoring SQL Server Databases
• Restoring a Database Backup
• Restoring Database, Differential, and Transaction Log Backups
• Performing a Piecemeal Restore

6 - Importing and Exporting Data

• Introduction to Transferring Data
• Importing and Exporting Table Data
• Copying or Moving a Database
• Lab : Importing and Exporting Data
• Using the SQL Server Import and Export Wizard
• Using the bcp Utility
• Using the BULK INSERT Statement
• Using the OPENROWSET Function

7 - Monitoring SQL Server 2014

• Introduction to Monitoring SQL Server
• Dynamic Management Views and Functions
• Performance Monitor
• Lab : Monitoring SQL Server 2014
• Collecting Baseline Metrics
• Monitoring a Workload

8 - Tracing SQL Server Activity

• Tracing SQL Server Workload Activity
• Using Traces
• Lab : Tracing SQL Server Workload Activity
• Capturing a Trace in SQL Server Profiler
• Generating Database Tuning Recommendations
• Using SQL Trace

9 - Managing SQL Server Security

• Introduction to SQL Server Security
• Managing Server-Level Security
• Managing Database-Level Principals
• Managing Database Permissions
• Lab : Managing SQL Server Security
• Managing Server-Level Security
• Managing Database-Level Security
• Testing Database Access

10 - Auditing Data Access and Encrypting Data

• Auditing Data Access in SQL Server
• Implementing SQL Server Audit
• Encrypting Databases
• Lab : Auditing Data Access and Encrypting Data
• Implementing Auditing
• Implementing Transparent Database Encryption

11 - Performing Ongoing Database Maintenance

• Ensuring Database Integrity
• Maintaining Indexes
• Automating Routine Database Maintenance
• Lab : Performing Ongoing Database Maintenance
• Managing Database Integrity
• Managing Index Fragmentation
• Implementing a Maintenance Plan

12 - Automating SQL Server 2014 Management

• Automating SQL Server Management
• Implementing SQL Server Agent Jobs
• Managing SQL Server Agent Jobs
• Managing Job Step Security Contexts
• Managing Jobs on Multiple Servers
• Lab : Automating SQL Server Management
• Creating a Job
• Scheduling a Job
• Configuring Job Step Security Contexts

13 - Monitoring SQL Server 2014 by Using Alerts and Notifications

• Monitoring SQL Server Errors
• Configuring Database Mail
• Configuring Operators, Alerts, and Notifications
• Lab : Monitoring SQL Server by Using Alerts and Notifications
• Configuring Database Mail
• Implementing Operators and Notifications
• Implementing Alerts

vDesigning and Optimizing Database Solutions with Microsoft SQL Server 

1. Plan a BI Solution

• Elements of a BI Solution
• The Microsoft BI Platform
• Planning a BI Project

2. Plan SQL Server Business Intelligence Infrastructure

• Considerations for BI Infrastructure
• Plan Data Warehouse Hardware

3. Design a Data Warehouse

• Data Warehouse Design Overview
• Design Dimension Tables
• Design Fact Tables
• Design a Data Warehouse Physical Implementation

4. Design an ETL Solution

• ETL Overview
• Plan  Data Extraction, Transformation, and Loads

5. Design Analytical Data Models

• Introduction to Analytical Data Models
• Design an Analytical Data Model
• Design Dimensions
• Enhance Data Models

6. Plan a BI Delivery Solution

• Considerations for Delivering BI
• Common Reporting Scenarios
• Choose a Reporting Tool

7. Design a Reporting Services Solution

• Plan a Reporting Services Solution
• Design Reports
• Plan Report Consistency

8. Design a Microsoft Excel-Based Reporting Solution

• Use Excel for Data Analysis and Reporting
• PowerPivot and Power View for Excel

9. Plan a SharePoint Server BI Solution

• Introduction to SharePoint Server as a BI Platform
• Plan Security for a SharePoint Server BI Solution
• Plan Reporting Services Configuration
• Plan PowerPivot Configuration
• Plan for PerformancePoint Services

10. Monitoring and Optimizing a BI Solution

• Overview of BI Monitoring
• Monitor and Optimize the Data Warehouse
• Monitor and Optimize Analysis Services
• Monitor and Optimize Reporting Services

11. Operate a BI Solution

• Overview of BI Solutions
• ETL Operations
• Data Warehouse Operations
• Analysis Services Operations
• Reporting Services Operations

 

vAdministering a SQL Database Infrastructure 

1. SQL Server Security

• Authenticating connections to SQL Server
• Authorizing logins to connect to databases
• Authorization across servers
• Partially contained databases

2. Assigning Server and Database Roles

• Working with server roles
• Working with fixed database roles
• Assigning User-Defined Database Roles

3. Authorizing Users to Access Resources

• Authorizing user access to objects
• Authorizing users to execute code
• Configuring permissions at the schema level

4. Protecting Data with Encryption and Auditing

• Options for auditing data access in SQL Server
• Implementing SQL Server audit
• Managing SQL Server audit
• Protecting data with encryption

5. Recovery Models and Backup Strategies

• Understanding Backup strategies
• SQL Server transaction login
• Planning a backup strategy

6. Backup of SQL Server Databases

• Backing up databases and transaction logs
• Managing database backups
• Working with backup options

 

7. Restoring SQL Server 2016 Databases

• Understanding the Restore Process
• Restoring databases
• Point-in-time recovery
• Advanced Restore Scenarios
• Performing a Piecemeal Restore

8. Automating SQL Server Management

• Automating SQL Server management
• Working with SQL Server agent
• Managing SQL Server agent jobs
• Multiserver management

9. Configuring Security for SQL Server Agent

• Understanding SQL Server Agent Security
• Configuring credentials
• Configuring proxy accounts

10. Monitoring SQL Server with Alerts and Notifications

• Monitoring SQL Server errors
• Configuring database mail
• Operators, alerts and notifications
• Alerts in Azure SQL database

11. Introduction to Managing SQL Server by using PowerShell

• Getting Started with Windows PowerShell
• Configure SQL Server using PowerShell
• Administer and Maintain SQL Server using PowerShell
• Managing Azure SQL Server databases using PowerShell

12. Tracing Access to SQL Server with Extended events

• Extended Events Core Concepts
• Working with Extended Events

 

13. Monitoring SQL Server

• Monitoring activity
• Capturing and managing performance data
• Analyzing collected performance data
• SQL Server utility

14. Troubleshooting SQL Server

• A Trouble Shooting Methodology for SQL Server
• Resolving service related issues
• Resolving login and connectivity issues

15. Importing and Exporting Data

• Transferring data to/from SQL Server
• Importing and exporting table data
• Using BCP and BULK INSERT to import data
• Deploying and upgrading data-tier applications

vSQL Server Reporting Services

1. SSRS Overview and Tools

• Tools Available with SQL Server 2014
• SQL Server Documentation

1. Working with Solutions and Projects

• Working with SSDT-BI for Visual Studio 2013
• Understanding Solutions and Projects
• Working with the Visual Studio Developer Environment

1. Creating Basic Reports

• What is a Data Source?
• What is a Dataset?
• Using the Report Wizard
• What is Tablix?
• Creating a Tabular Report
• Creating a List Report
• What is a Matrix?
• Deploying a Project

1. Formatting Reports

• Rich Text Formatting
• Adding and Managing Text Boxes
• Drawing Lines and Boxes
• Images
• Adding Page Headers and Footers
• Using the Built-in Fields Folder
• Adding a Document Map
• Rendering Reports

1. Custom Expressions

• Understanding Expressions
• Defining Expressions
• Working with Report Variables
• Accessing Multiple Datasets with Lookup Functions
• Additional Samples

1. Summarizing and Sorting

• Adding Groups with the Report Wizard
• Creating Groups
• Parent/Child vs. Adjacent Groupings
• Adding Totals and Aggregations
• Creating Aggregates of Aggregates

1. Add Flexibility with Parameters

• Adding Parameters to a Report
• Report Parameters vs. Query Parameters
• Manage Report Parameter Properties
• Use Parameters with SQL Queries and Stored Procedures
• Work with Cascading Parameters
• Sort Based on a Parameter Setting
• Filtered Reports

  

1. Enhanced Report Items

• Charts
• Gauges
• Add a Map to a Report
• Data Bar, Indicator, and Sparkline Report Items
• Nesting Data Regions
• Working with Subreports
• Working with Drillthrough Reports
• Performance Implications of Subreports and Drillthrough Reports

1. Using Report Manager

• Deploying Projects
• Navigating Report Manager
• Understanding Permissions
• Viewing Reports
• Working with Shared Data Sources
• Managing Reports

10. Using SQL Server 2014 Report Builder

• Click Once vs. Stand-alone Installations
• Installing the Stand-alone Version
• The Report Builder Tool Interface
• Creating Shared Datasets with Report Builder
• Adapt report creation to the Report Builder Application
• Opening Existing Reports
• Working with Report Parts