3

I'm trying to build a CI/CD for my Microsoft SQL Server database projects. It will work with Microsoft DevOps pipelines.

I have all databases in Visual Studio databases projects with the GIT as source control. My objective is to have something that I can release databases with the use of DevOps pipelines to the diferents enviroments:

  • DEV
  • UAT
  • PROD

I was thinking of using DBGhost: http://www.innovartis.co.uk/ but I can't find updated information about this tool (only very old info) and there is very little information about it on the internet and how to use it (is it still in use?).

I would like to use a mix of DBGhost and DevOps. DBGhost to Source Scripting, Building, Comparing, Synchronizing, Creating Delta Scripts, Upgrading and DevOps to make releases (that would call the builds created by DBGhost)

If you have any ideas using this or other methods I am grateful because currently all releases are manual and it is not very advisable to do.

tg_dev3
  • 171
  • 3
  • 18

1 Answers1

4

We have this configured in our environment using just DevOps. Our database is in a Visual Studio database project. The MSBuild task builds the project and generates a DACPAC file as an artifact, and the Release uses the "SQL Server Database Deploy" task to deploy this to the database. The deploy task needs to use an account with enough privileges to create the database, logins, etc., but takes care of performing the schema compare, generating the delta scripts, and executing them. If your deploy is going to make changes that could result in data loss such as removing columns, you will need to include the additional argument /p:BlockOnPossibleDataLoss=false in the deploy task. This flag is not recommended unless you know there will be changes that will cause data loss; without the flag any deploy which would result in data lost will fail.

Paul Pearce
  • 2,455
  • 1
  • 10
  • 11
  • Great answer and great idea. I have some questions, you have the pipelines for each enviromnet configured? You load the databases with the import on Visual Studio or with DACPAC? because I have Import without DACPAC and I have a lot of errors when building the project... Imagine that I made a simple UPDATE on a SP in DEV and I want to release that to UAT, the system will only made that update or will destroy all create all? Sorry for th questions I never use CI/CD on databases. – tg_dev3 Jan 20 '22 at 17:40
  • 2
    @tg_dev3 We have one pipeline for the build and one release with three different stages representing our three different environments. Our database project started as a full export of the schema of the database; as we add or update database objects in the project, those changes are included in the DACPAC that the build generates. The deploy updates the database object definitions, but does not change the existing data. Also the deploy does not remove objects that were not part of the project, although I believe if the object was previously in the project, the deploy will remove them. – Paul Pearce Jan 20 '22 at 18:07
  • 1
    @tg_dev3 One additional note: While you are setting this up and testing the process, I would highly recommend taking a full database backup first in case the deploy results in unexpected changes. Also, if you make structure changes to tables with large amounts of data, the deploy can take a while, as the deploy process will create a new table with the new structure, copy the data from the old table to the new table, remove the old table, and then rename the new table to the correct table name. – Paul Pearce Jan 20 '22 at 18:11
  • That's great. Thanks for all the information I will try to build something like that! I didn't understand how you import the database to the visual studio, which of these methods (https://learn.microsoft.com/en-us/sql/ssdt/import-into-a-database-project?view=sql-server-ver15) do you use? Another thing you made the changes in SSMS or in Visual Studio, because I develop in SSMS but when I open Visual Studio the project is not up to date I have to pass what I change to Visual Studio, how you deal with that? You made schema compares? – tg_dev3 Jan 20 '22 at 18:24
  • 1
    @tg_dev3 I use Visual Studio 2019, in the SQL Server Object Explorer, if you connect to your database and right-click on the database, there is a `Create New Project` option that will create the project and generate the scripts for the objects. The project will then contain the create scripts for all tables, views, stored procs, users, etc. We usually create new object in the project and let the deploy create them in our dev database, however there are times we create objects in SSMS, at which point we copy the create scripts from SSMS into the VS project. – Paul Pearce Jan 20 '22 at 18:37
  • Ah so you mostly develop on visual studio and when you use SSMS you have to made a copy manually to the visual studio? Hm I would like to avoid that... And I know that the tool DBGhost can make that. Do you know something about that? – tg_dev3 Jan 20 '22 at 22:03
  • 1
    @TG_Dev3 The Visual Studio SQL Server Object Explorer also has a schema compare that can compare your dev database against the project and generate any scripts needed to sync them up. This will let you generate the scripts for objects you create through SSMS. You have to make sure the account used for the database connection in the Object Explorer has enough permissions to view the schema definitions in the database. The same tool can also do data compares, we use it to do data compares between our environments where we have some data entered in dev that needs to also be deployed to prod. – Paul Pearce Jan 21 '22 at 16:10
  • Thanks for all the help. One last question, my database is on premise so I need to open access to exterior (in this case azure devops) because this will be a problem because security issues... – tg_dev3 Jan 25 '22 at 22:58
  • 1
    @tg_dev3 When our database was on premise, we had one server running self-hosted build and deploy agents that had access to the internet, and that server could connect to our SQL Server. The agents do not require incoming connections from DevOps, they just need to be able to connect out to DevOps, and only the agent server needs to be able to connect out. Our SQL Server could not access the internet and was not accessible directly from the internet. – Paul Pearce Jan 27 '22 at 16:18