Create Your First Visual Studio Database Project

Databases should be under source control and have the ability for CI/CD just like application code. Visual Studio gives you this capability. Here, we will show you how to get your databases into a Visual Studio Database Project.

Download Visual Studio

If you don’t have Visual Studio, download it from here.

During the installation, you will be asked to choose your Workload(s). You need to check Data storage and processing. You can also check others if you want to give them a try or add them later.

Create a New Project

Launch Visual Studio and select Create a new project.

Search for database and select SQL Server Database Project. Click Next.

Enter the name of the project. The Solution Name defaults to Project Name. For this example, we will use Adventure Works and make the Solution Name generic for when we add additional databases down the road. Set the location to your preference.

When Visual Studio opens, you will see the Solution Explorer window. The Solution will have one project, Adventure Works. Right-click on the project and select Import, then Database.

Enter the connection information and uncheck Import referenced logins. Logins will have different permissions in UAT and Production and we don’t want to increase or decrease permissions unintentionally.

After the Import is complete, you will see folders for each schema and a few others for administration. Example:

Managing Development

You can make changes within Visual Studio or in the database and sync the changes. If you are working with tables or making global searches or changes, I would start with Visual Studio. For code development, you may prefer to work in the database and sync views, stored procedures, etc., when you have working code.

Here is an example of editing a table in Visual Studio. It has a Design pane and a T-SQL pane. Updates in one pane are automatically reflected in the other pane. So you work where it is more comfortable.

After you do some development in the database, you must pull your changes into Visual Studio. I created a stored procedure template to illustrate the process.

CREATE PROCEDURE dbo.uspTemplate
AS
BEGIN
/**
Change Log
Date Developer Change
---------- ---------------- ---------------------------------------------------
**/
SET NOCOUNT ON; 
END; -- dbo.uspTemplate
GO

To pull the changes into Visual Studio, go to Tools, SQL Server, New Schema Comparison.

Click on the Select Source dropdown and enter your database connection. After you create a new connection it will be saved for future use.

For the target, we will select the project.

After the source and targets are entered, click Compare.

When the comparison is done, and we go into the Results pane, we will see a list of Add, Changed, and Deleted objects between the two. The Object Definitions pane will show the source code differences. If you split the window, you can see both and scroll through the objects that will be updated.

If you do not want to push any object to the target, you can uncheck the box next to the plus sign. Group highlighting and excluding works as expected.

We can now click Update to push the changes to our project. Confirm the update and the changes are applied to your project.

Publish Changes to a Database

The Publish process will do a Build of the project to validate and create a dacpac (a self-contained Data-Tier Application package used for deployments) file for processing.

The Build process will highlight warnings and errors. You can also suppress warnings. Most warnings are based on Best Practices as determined by Microsoft. Errors must be fixed because they will not execute. To show a warning, I changed the case of a column in a view. Let’s see the Build process.

Right-click on the Project and select Build.

The output shows the build succeeded, but there were warnings.

If you want to clean up the code, go to the error pane and double-click the warning. The source file will open in the editor and highlight the location of the warning.

You can suppress these warnings under Project Properties. Enter the warning number without the SQL prefix in the Suppression box.

To push the changes to a database, right-click the project and select publish. For this example, we will publish to a QA database that was created on the server and does not contain any object yet.

After we enter in the connection, we can generate the script to preview what will be happening. This is also helpful in debugging when this gets complicated and you may need pre and post-deployment scripts. For a simple change, we can click publish.

We get a successful message:

And the database results:

Conclusion

This will seem like a lot in the beginning. But after a few times it will become second nature. Spend a little time now to get your databases in a manageable state and you will reduce the stress of deployments and have more time for critical tasks.