The purpose of Visual Studio Database project

Question

I have a couple of questions about work process. Recently I was entered to the new project with a lot of database activities involved. One of our repositories is Visual studio Database project and has all stored procedures, functions and so on inside it. We don't use it for CI, like Azure. As I understand we publish everything manually. The questions are:

1) What is the purpose of the visual studio database project at all? Could you give me some points about it?

2) Should I debug and run all sql code in SSMS or in Visual studio db project during development process?

3) What is the purpose of 'publish' and 'build' functions in it? Are they able to break something in the existing database? How, why and when safetly use them?

I'll be very grateful for providing any tutorials, videos, best practices related to this theme.

Answer 1

I love ❤ this project type.

The database project gives you several things:

1. It puts your schema in source control
2. It allows you to validate your schema
3. It provides semantic rename across objects
4. It integrates with Azure DevOps pipelines
5. It allows you to deploy incremental updates
6. It allows you to script pre/post operations
7. It easily deploys dev/stage versions of your db

Is using it a good idea?

Yes. I think it should be part of every project with SQL Server.

Problems I have seen with it:

1. It is not supported on VS for Mac

I use it in Visual Studio 2019.

Answer 2

This is SQL Server Data Tools (SSDT). If you google that phrase, you will find dozens of websites that discuss it at length. I have no preferred site for this--when I'm trying to figure something out, I read several sites to get a "composite" view of things.

The key thing is, SSDT provides is a state-based deployment methodology, as opposed to the more familiar migration-based methodologies. "State based vs migration based" is another good term to Google--all those website do a better job of describing it than I could here.

Build is roughly analogous to "will the code compile", e.g. is the syntax good, or are you referencing tables, columns, procedure that don't exist. As it's managed in Visual Studio, tracking down and fixing those bugs can be very easy. Well, tracking them down, at least.

Publish is, essentially, "take the database as defined in the code in this project, compare it with a target database, and make that database look like what we have in code". You feed the (table, procedure, etc.) definitions in, SSDT will write all of the necessary CREATE, ALTER, and DROP statements for you. Yep, tons of issues if there's data in the way, with various work-arounds to deal with it, but you don't have to make sure your five years of scripts are processed in the correct order.