Delivering High Quality Databases Using Visual Studio 2022

Delivering High Quality Databases Using Visual Studio 2022

Course Length: 2 days
Delivery Methods: Available as private class only
Course Overview

This two-day, instructor-led course provides students with the knowledge and skills to effectively develop, test, and deliver modern SQL Server databases. Student will experience how to manage changes to database schema, ensure quality through T-SQL unit testing and static code analysis, and automate the building and deploying of SQL Server databases.

This course is intended for database developers and administrators working with modern versions of SQL Server or SQL Azure. Application developers who are involved with writing and testing T-SQL code and working with test data will also find value from this course. It would also be beneficial if the student has worked on a team-based software development project and is familiar with their organization’s development lifecycle and practices.

The course can also be taught on Visual Studio 2019.

Course Benefits
  • SQL Server Data Tools (SSDT) overview
  • SSDT and SSIS, SSAS, and SSRS development
  • Agile database development
  • SQL Server and Azure SQL version support
  • Connected vs. disconnected development
  • SQL Server Object Explorer (SSOX)
  • T-SQL editor, debugger, and IntelliSense
  • Imperative vs. declarative development
  • Agile database techniques
  • SQL Server database projects
  • Thinking of schema as source code
  • Solution Explorer vs. SQL Server Object Explorer
  • Importing a database schema
  • Importing a data-tier application (.dacpac)
  • Importing T-SQL Script
  • Cross-database dependencies
  • Referencing a database project or .dacpac
  • Database development lifecycle
  • Database project activities and tasks
  • Integration with Azure DevOps (Azure Repos)
  • Creating and configuring a team project
  • Using Git to manage schema changes
  • Creating and cloning a Git repository
  • Commit, pull, push workflow in Visual Studio
  • Comparing and synchronizing schema changes
  • Creating and using a project snapshot (.dacpac)
  • Comparing and synchronizing data changes
  • SQL Server unit testing principles
  • Creating a unit test project
  • Testing stored procedures, functions, and triggers
  • Using initialize, pre/post-test, and cleanup scripts
  • T-SQL assertions vs. test conditions
  • Automatic test generation
  • Creating negative unit tests
  • Using tSQLt for SQL Server unit testing
  • Generating test data
  • Using static code analysis
  • Configuring, viewing, and suppressing messages
  • Database refactoring overview
  • SSDT refactoring commands
  • Understanding the refactoring log
  • Building the database project
  • Deploying/publishing to SQL Server
  • Using variables and custom scripts
  • Using build events and actions
  • Advanced project types
  • Integrating with Azure Pipelines
  • Configuring a self-hosted pipeline agent
  • Using build and release pipelines
  • Running SQL Server unit tests in a pipeline
  • Practicing Continuous Integration (CI)
  • Practicing Continuous Delivery (CD)
Course Outline
  1. Agile Database Development
    1. Agile database development overview
    2. Agility challenges and blockers
    3. SQL Server Data Tools overview
    4. Connected vs. disconnected development
    5. Imperative vs. declarative development
    6. Agile database techniques
  2. SQL Server Database Projects
    1. Creating a SQL Server database project
    2. Treating schema as source code
    3. Working with SQL Server Object Explorer
    4. Importing database schema
    5. Importing data-tier applications (.dacpac)
    6. Importing scripts
    7. Managing cross-database dependencies
    8. Referencing a database and .dacpac file
  3. Managing Schema Changes
    1. Azure DevOps overview
    2. Azure Repos overview
    3. Using Git to manage schema changes
    4. Cloning, committing, pushing, and pulling
    5. Comparing schemas and sync’ing changes
    6. Comparing data and sync’ing changes
    7. Rolling back changes using Git
    8. Rolling back changes using project snapshots
  4. Assuring a High-Quality Design
    1. Database unit testing principles
    2. Levels of database unit testing
    3. SQL Server unit tests
    4. Database unit test designer
    5. T-SQL assertions vs. test conditions
    6. Using tSQLt for SQL Server unit testing
    7. Static code analysis
    8. Database refactoring tools
  5. Building and Deploying
    1. Building the database project
    2. Using pre-build and post-build events
    3. Including additional build scripts
    4. Using script build actions
    5. Using the LocalDB isolated sandbox
    6. Deploying changes to SQL Server
    7. Creating and using publishing profiles
    8. Using pre and post deployment scripts
    9. Using SQLCMD variables within scripts
    10. Building/deploying composite projects
    11. Building/deploying SQL CLR objects
    12. Azure Pipelines overview
    13. Using an automated build pipeline
    14. Practicing Continuous Integration (CI)
    15. Using an automated release pipeline
    16. Running tests in the release pipeline
    17. Practicing Continuous Delivery (CD)
Class Materials

Each student will receive a comprehensive set of materials, including course notes and all the class examples.

Request a Private Class
  • Private Class for your Team
  • Online or On-location
  • Customizable
  • Expert Instructors
Request Pricing