Microsoft Learning Logo is a registered trademark of Microsoft. All rights reserved.

Administering a SQL Database

Schedule

Start End Duration Location Details

Course Details

Administering a SQL Database

Duration: 5 Days

Course Code: 55316AC

Prerequisite:

• Experience using applications on Windows Servers
• Experience working with SQL Server or another RDMS

Course Description:

This five-day course provides students who manage SQL Server and Azure SQL databases with the knowledge and skills needed to administer a SQL server database infrastructure. The material will also be useful to individuals who develop applications that deliver content from SQL Server databases. This material updates and replaces course 20764C. 

Course Objective:

• Authenticate and authorize users 
• Assign server and database roles 
• Authorize users to access resources 
• Use encryption and auditing features to protect data 
• Describe recovery models and backup strategies 
• Backup and 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
 
Intended Audience:

The primary audience for this course is individuals who administer and maintain SQL Server databases. These individuals perform database administration and maintenance as their primary area of responsibility or work in environments where databases play a key role in their primary job. 

The secondary audiences for this course are individuals who develop applications that deliver content from SQL Server databases.

Course Outline:

Module 1: SSIS Overview
• Import/Export Wizard 
• Exporting Data with the Wizard 
• Common Import Concerns 
• Quality Checking Imported/Exported Data

Module 2: Working with Solutions and Projects
• Working with SQL Server Data Tools 
• Understanding Solutions and Projects 
• Working with the Visual Studio Interface

Module 3: Basic Control Flow
• Working with Tasks 
• Understanding Precedence Constraints 
• Annotating Packages 
• Grouping Tasks 
• Package and Task Properties 
• Connection Managers 
• Favorite Tasks

Module 4: Common Tasks
• Analysis Services Processing 
• Data Profiling Task 
• Execute Package Task 
• Execute Process Task 
• Expression Task 
• File System Task 
• FTP Task
• Hadoop Task 
• Script Task Introduction 
• Send Mail Task 
• Web Service Task 
• XML Task

Module 5: Data Flow Sources and Destinations
• The Data Flow Task 
• The Data Flow SSIS Toolbox 
• Working with Data Sources 
• SSIS Data Sources 
• Working with Data Destinations 
• SSIS Data Destinations

Module 6: Data Flow Transformations
• Transformations 
• Configuring Transformations
    Module 7: Making Packages Dynamic
• Features for Making Packages Dynamic 
• Package Parameters 
• Project Parameters 
• Variables
• SQL Parameters 
• Expressions in Tasks 
• Expressions in Connection Managers 
• After Deployment 
• How It All Fits Together

Module 8: Containers
• Sequence Containers 
• For Loop Containers 
• Foreach Loop Containers

Module 9: Troubleshooting and Package Reliability
• Understanding MaximumErrorCount 
• Breakpoints 
• Redirecting Error Rows 
• Logging 
• Event Handlers 
• Using Checkpoints 
• Transactions

Module 10: Deploying to the SSIS Catalog
• The SSIS Catalog • Deploying Projects 
• Working with Environments 
• Executing Packages in SSMS 
• Executing Packages from the Command Line 
• Deployment Model Differences

Module 11: Installing and Administering SSIS
• Installing SSIS 
• Upgrading SSIS 
• Managing the SSIS Catalog 
• Viewing Built-in SSIS Reports 
• Managing SSIS Logging and Operation Histories 
• Automating Package Execution

Module 12: Securing the SSIS Catalog
• Principals 
• Securables 
• Grantable Permissions 
• Granting Permissions 
• Configuring Proxy Accounts