Your cart is currently empty!
Visual Studio SQL Server Database Project – The Basics
| Series Topics | ||||
| Destination | SQL Server | Azure SQL DB/Managed Instance | ||
| Orchestration | Azure Pipelines (DevOps) | GitHub Actions | ||
| Source Control | Azure DevOps | GitHub | ||
| Schema Tools | Visual Studio SQL Server Database Project |
VS Code Database Project |
Red Gate SQL Source Control |
Red Gate Flyway |
Visual Studio has been a part of my work computers throughout my career as a database and software developer. To help development teams use a single development environment, Microsoft has a set of features to work with SQL Server databases. Schema and data comparisons are tools in Visual Studio. There is also a project template for SQL Server databases to manage the database schema and related scripts. The output of a database project is a DACPAC, a “compiled” version of the schema. Azure Pipelines knows how to work with DACPACs to deploy changes to SQL Server or Azure SQL DB/Managed Instance. Given my history with Visual Studio, it seems natural for me to start the automation process here.
Throughout this series, when referencing Visual Studio, I am explicitly talking about Visual Studio 2022, the current version. If you have a small team of fewer than five users, Community Edition is available to use, and it’s FREE!
During the installation, choose Data storage and processing from the Other toolsets section at the bottom of the list. As an aside, a lightweight version of SQL Server, SQL Server 2019 LocalDB, is installed on your computer. While working with the database project, code is executed within LocalDB, ensuring the database schema is valid.
With Visual Studio installed, let’s fire it up and create a new project. Search for SQL Server in the Create a new project window. Provide a name and a place to live on your hard drive, and then it’s time to get to work. WideWorldImporters is the name of the project I’m using for demonstration purposes.
Import an Existing Schema
Whether development just started in a new database or the database has been in production for years, the easiest way to import the schema into the Visual Studio project is with a schema comparison. Right-click on the project in Solution Explorer and choose Schema Compare…
Within the schema comparison window, there are two drop-down lists at the top for the source and destination of the comparison. By default, the source will be the database project. Click the button between the lists to swap them. Choose Select Source… from the drop-down list, followed by Database and Select Connection… in the Select Source Schema window. As shown below, connect to the desired database, filling out the connection properties.
Now the source and destination are chosen, click the Compare button in the schema comparison toolbar. Since this is a new project, all objects in the database will be listed under the Add folder. Review the list of items and then click Update from the toolbar, confirming changes to the target. Visual Studio will generate scripts for the objects. The database objects are organized by schema and then object type.
Updating Objects in the Project
The next logical step is to make changes to the schema. To modify a table or other database object in the project, locate the file and open it in the Visual Studio editor. The table editor shows a grid with the table definition and the T-SQL script. As changes are made in the grid, the table script will update automatically and vice-versa. Other object types have only a script defining the object.
Adding a new object can be completed just as quickly. Right-click on the subfolder to which the new database object belongs and choose Add and either a popular object type or New Item… Provide the name of the database object, which becomes the file’s name.
Building the Project
As new objects are created in the project, a critical step is ensuring the schema is valid and can be built. Like an application, the database project is “compiled” into a DACPAC used by sqlpackage.exe to deploy the changes to a database. Shift+Ctrl+B starts the build in Visual Studio. Review the Output window to observe warnings and errors encountered during the build. A successful build occurs when there are no errors found in the code. Warnings are nearly inevitable, especially for an existing that was imported. For example, if system views are used in a stored procedure, a database reference must be made to the master database. Over time, cleaning up some warnings may be desired, but it is optional for the build to succeed.
Referencing a System Database
There may be times when the project needs to know the schema of the master database. For example, a stored procedure may execute dynamic T-SQL, and system views are used to retrieve a list of table names. The WideWorldImporters database has many stored procedures that will enable or disable functionality in the database using system views. To add a reference to master or msdb, in Solution Explorer, right-click References within the project and choose Add Database Reference… Choose System database and then master from the drop-down list (notice that msdb is also in this list). Once the reference has been made, the warnings related to system views will be eliminated.
Publish Schema Changes
Once the schema changes are complete in the database project, traditionally, updates are deployed to a development or test server for unit testing or quality assurance testing. Right-click on the project, WideWorldImporters, in my case, and choose Publish. The Publish Database dialog, shown below, has several settings. In the next post, we will spend time creating a Publish profile. Today, we are only interested in manually publishing the changes. To do so, click the Edit button to define the connection string to the destination database. Choose the database connection used earlier for the schema comparison. To complete the process, click Publish to deploy any changes to the database.
Next Steps
We have covered the basics of Visual Studio’s functionality for managing database schema and building a DACPAC. There is more Visual Studio has to offer. An upcoming article will cover the following topics.
- Managing data for lookup tables
- Adding references to other user databases
- Targeting specific versions of SQL Server or Azure SQL DB
- Defining deployment rules
Comments
One response to “Visual Studio SQL Server Database Project – The Basics”
-
[…] the basics of Visual Studio SQL Server Database projects were […]
by
Tags:
Leave a Reply