Course: Visual Studio 2017 – Managing Database Changes with SQL Server Data Tools (SSDT)

Course 6417: Two Days (with hands-on); Instructor-Led; Course Syllabus

INTRODUCTION

Don’t forget about the data tier! Many organizations focus heavily on managing the source code of their applications and neglect managing changes to the databases those applications depend on, leaving an expensive gap in their Application Lifecycle Management (ALM) process. This 2-day course provides students with the understanding and skills to effectively implement best practices for managing changes to Microsoft SQL Server database systems in a collaborative environment using the latest Microsoft Team Foundation concepts and technologies. Students will learn how to harness SQL Server Data Tools to create database and server projects, put new and existing databases under source control, compare and synchronize database instances, make changes to database systems under source control, create database unit tests , and deploy database changes including data.

AUDIENCE

This course is intended for database professionals and application developers who have a working knowledge of SQL Server 2005 or later and experience working in a collaborative development environment.  Attendees are expected to be familiar with the Application Lifecycle Management (ALM) concepts and basic methodologies. Experience with source control practices is helpful.

AT COURSE COMPLETION

After attending this course, students will be able to:

  • Explain the database development lifecycle
  • Recognize the components of a database development environment
  • Identify the capabilities of SSDT
  • Navigate SSDT user interface components
  • Create and configure database projects
  • Identify the components and structure of database projects
  • Put an existing database schema under source control
  • Troubleshoot common problems encountered
  • Compare and synchronize database schemas
  • Configure an isolated environment for database development
  • Project-Oriented Offline Database Development
  • Understand Data-tier applications (DACs) and DACPACs
  • Use references in database projects
  • Create and execute scripts using SSDT Transact-SQL Editor
  • Use static analysis to detect common Transact-SQL code errors
  • Make changes to database objects under source control
  • Refactor database objects in a team environment
  • Verify Database Code by Using SQL Server Unit Tests
  • Compare and synchronize data among database instances
  • Build and deploy changes to databases under source control
  • Using Pre and Post Deployment Scripts

PREREQUISITES

The following prerequisites are expected:

  • A working knowledge of SQL Server 2005 or later
  • An understanding of basic source control and version management concepts
  • Understand Microsoft Windows operating system basics

COURSE OUTLINE

The Database Development Life Cycle

This module introduces students to the database development life cycle and Team Foundation.  Students will learn how the database development life cycle relates to the software development life cycle, and how key Team Foundation technologies support application and database life cycle management.

Microsoft Team Foundation

  • Work items
  • Visual Studio
  • Source control
  • Team Build

Getting Started with SSDT

This module introduces students to the components and settings required to perform database development in a team environment.  Students will learn how to configure a SSDT client environment, and how to create database projects for both new and existing database systems.

Database Development Environment – Tools and Concepts

  • Microsoft SQL Server Environments
    • Isolated development
    • Staging and production
  • SSDT Tooling and Compatibility
  • Introduction to the DAC Framework
  • DACPACs

Connected Development

  • Using SQL Server Object Explorer (SSOX) for Connected Development
    • User Interface Components
    • Differnces between connected development with SSDT vs SSMS
  • Project based Development
    • New Database Project Wizard
    • Importing objects and settings
  • Structure of Database Projects
    • Structuring solutions to accommodate database projects
    • Properties of Database Projects
  • Making changes
  • Working with references and variables
  • Working with Partial and Composite Projects
  • Common challenges and solutions when getting started
    • Correcting common errors
    • Using DACPACS to resolve circular references

Working with Databases under Source Control

This module introduces students to making changes to database objects and server configurations that are under Team Foundation source control using best practices.  Students will learn how to compare and synchronize objects and data among multiple instances of a database, refactor databases, and utilize work items.

Working with Team Foundation Server

  • Using Team Explorer
  • Work Items and process integration
  • Source control

Making Changes

  • Creating and executing Transact-SQL scripts
  • SSDT features for Refactoring databases

Building Databases

  • Preparing for deployment
  • Generating deployment scripts

Deploying Databases to an Isolated Environment

  • Creating an Isolated Development Environment
  • Deploying changes to a test environment

Managing and Verifying Database Deployments

  • Comparing and Synchronizing Database Schemas
  • Comparing and Synchronizing Data

Testing and Reviewing Databases Changes

This module introduces students to basic unit testing for Transact-SQL database systems.  Students will learn how to create a test project for database testing, establish a baseline state for a database, and run unit tests to verify changes to a database.

Generating Deployment Scripts

  • SQLCMD
  • Moving data during deployment

Unit Testing

  • Create a database test project
  • Creating unit tests
  • Executing unit tests

Application Integration and Production Deployment

This module introduces students to build and deploying databases to staging and production environments.  Students will learn how to minimize the risk of data loss when deploying database changes.

Releasing to Production Environment

  • Preparing for a production deployment
  • Defining SETVAR variables
  • Pre- and post-deployment scripts
  • Deploying from the Command Line using SQLPackage.exe

ALM Integration

  • Verifying application compatibility
  • Maintaining the development environment
  • Demonstration of using SSDT with automated build process
  • Demonstration of using SSDT with TFS Release Management

Course Designers

This course was designed by InCycle Software.

This document contains confidential and proprietary information and is supplied purely to enable you to evaluate details concerning InCycle Software Solution’s products and services.  No part of this publication may be reproduced or transmitted in any form or by any means, including photography and recording, without the written permission of InCycle Software.

If you have any questions or would like to register for this course, please fill out the form below or call us at (800) 565-0510.


RSS feed

Upcoming events

  • No event

Our services