Delivering High-Quality Databases Using Visual Studio 2022

Delivering High-Quality Databases Using Visual Studio 2022

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

Who should take this class?
This course is intended for database developers and administrators working with modern versions of SQL Server or Azure SQL. Application developers who are involved with writing and testing T-SQL code and working with test data will also find value.

Benefits

This course provides several learning objectives and benefits

• Describe SQL Server Data Tools (SSDT) and how it can be used
• Differentiate between SSDT, SSIS, SSAS, and SSRS development
• Explain agile database development
• Understand the supported versions of SQL Server and Azure SQL
• Differentiate between connected vs. disconnected development
• Use SQL Server Object Explorer (SSOX) for database development
• Use T-SQL editor, debugger, and IntelliSense for database development
• Contrast imperative vs. declarative development
• Describe agile database techniques
• Create and manage a SQL Server database project
• Think of schema as source code
• Compare Solution Explorer and SQL Server Object Explorer
• Import a database schema
• Import a data-tier application (.dacpac)
• Import T-SQL Script
• Find and resolve cross-database dependencies
• Reference a database project or .dacpac
• Understand SSDT's database development lifecycle
• Use a database project to perform various development tasks
• Create and configure an Azure DevOps project for SSDT
• Use Git and Azure Repos to manage schema changes
• Create and clone a Git repository
• Commit, pull, push, and sync changes using Visual Studio
• Compare and synchronize schema changes
• Create and use a project snapshot (.dacpac)
• Compare and synchronize data changes
• Describe SQL Server unit testing principles
• Create a unit test project in Visual Studio
• Unit test stored procedures, functions, and triggers
• Use initialize, pre/post-test, and cleanup scripts to simplify testing
• Differentiate between T-SQL assertions and test conditions
• Automatically generate tests and test data
• Create negative unit tests
• Use tSQLt for SQL Server unit testing
• Use static code analysis to find common SQL coding issues
• Configure, view, and optionally suppress messages analysis messages
• Refactor SQL database design using SSDT refactoring commands
• Understand and leverage the refactoring log
• Build and validate a database project
• Deploy/publish a database project to SQL Server
• Use variables and custom scripts when building
• Use build events and actions when building
• Understand advanced database project types
• Use Azure Pipelines to automate database building, deploying, and testing
• Configure and use self-hosted pipeline agents for building and releasing
• Run SQL Server unit tests in a pipeline
• Practice Continuous Integration (CI)
• Practice Continuous Delivery (CD)

Outline

1. Agile Database Development

• Agile database development overview
• Agility challenges and blockers
• SQL Server Data Tools (SSDT) overview
• Connected vs. disconnected development
• Imperative vs. declarative development
• Agile database techniques
• Hands-on

2. SQL Server Database Projects

• Creating a SQL Server database project
• Treating schema as source code
• Working with SQL Server Object Explorer
• Importing database schema
• Importing data-tier applications (.dacpac)
• Importing scripts
• Managing cross-database dependencies
• Referencing a database and .dacpac file
• Hands-on

3. Managing Schema Changes

• Azure DevOps overview
• Azure Repos overview
• Using Git to manage schema changes
• Cloning, committing, pushing, and pulling
• Comparing schemas and syncing changes
• Comparing data and syncing changes
• Rolling back changes using Git
• Rolling back changes using project snapshots
• Hands-on

4. Assuring a High-Quality Design

• Database unit testing principles
• Levels of database unit testing
• SQL Server unit tests
• Database unit test designer
• T-SQL assertions vs. test conditions
• Using tSQLt for SQL Server unit testing
• Static code analysis
• Database refactoring tools
• Hands-on

5. Building and Deploying

• Building the database project
• Using pre-build and post-build events
• Including additional build scripts
• Using build actions
• Using the LocalDB isolated sandbox
• Deploying changes to SQL Server
• Creating and using publishing profiles
• Using pre and post-deployment scripts
• Using SQLCMD variables within scripts
• Building/deploying composite projects
• Building/deploying SQL CLR objects
• Azure Pipelines overview
• Using an automated build pipeline
• Practicing Continuous Integration (CI)
• Using an automated release pipeline
• Running tests in the release pipeline
• Practicing Continuous Delivery (CD)
• Hands-on

Required Prerequisites

Attendees should have experience developing, testing, and deploying SQL Server databases.

Useful Prerequisites

Experience working on a team-based development project and familiarity with their organization’s development lifecycle and practices will be beneficial, but is not required.

‍