existing database [SQLChangeAutomation-Prod] on [SQL2K12] needs to be baselined prior to deployment

eliassal

HI, I have created a build definition in TFS, then a release definition (followed videos at https://www.red-gate.com/hub/university/sql-change-automation-with-sql-source-control-projects/automated-deployments-using-the-sca-tfs-vsts-extensions/defining-use-database-release-task).
The deployment fails with the following error:

Building patch for the [SQLChangeAutomation-Prod] database on [SQL2K12]...
[warning]RedGate.Versioning.Engine.Api.Exceptions.VersioningEngineConfigurationException:
The existing database [SQLChangeAutomation-Prod] on [SQL2K12] needs to be baselined prior to deployment
(a baseline is required to ensure that existing database objects are not affected during an initial SQL Change Automation project deployment).
See the following article for more information: https://www.red-gate.com/sca/dev/baseline. Alternatively, you can attempt to deploy without a baseline by specifying SkipBaselineCheck=True...
......
......

The project that is built by TFS isI created with SCA , dev DB is on localdb, and on Target server I created manually the initial DB with a suffix -Prod.
So how can I baseline the target DB as it is a Q/A environment and want tables and procs added to the dev go through my Release definition. In the video, the instructor does not mention anything about this.

2nd, in the error message it is indicated that I can attempt
you can attempt to deploy without a baseline by specifying SkipBaselineCheck=True

Where should I indicate this? in TFS or in the project in VS?
Thanks for your help

Alex B

Hi @eliassal,

If you are attempting to release to an existing database that currently has objects within it you would need to create a baseline as it says.  I believe the page it links is the same as this one on workign with an existing database: https://documentation.red-gate.com/sca3/tutorials/worked-examples/working-with-an-existing-database

From what you say,  it sounds like you have restored your Q/A database that has existing objects to a new database called SQLChangeAutomation-Prod and have an empty development database.  The baseline wizard lets you create a baseline as shown in the documentation page, which will then be able to create these objects to the development database.  Then your future changes are added on top of this and when you go to deploy to the database that already has the baseline it only runs the migrations for the new changes.

I hope that helps explain how it should work - if I've misunderstood something please let me know!

Kind regards,
Alex

GHood

The link you provided works great when creating a new project. How is a new database added to an existing project? When I attempt to deploy it says the new db needs to be baselined, but I can't get the wizard to come up, nor can I find information about how to baseline a new db in an existing project.

Alex B

Hi @GHood,

What do you mean by a new database in an existing project?  Can you outline the steps you are taking to get to the point where you are experiencing the issue?  Why you would be adding another database to an existing SCA project?

If I have a SCA project and add another new SCA project (so two projects in one solution) it prompts me with the setup wizard where I can specify the baseline.

Kind regards,
Alex

GHood

I created a project and the wizard popped up. I configured the 2 databases. That all worked without any problem. Now, I have another db that I need to deploy the functions and sprocs to. When I connect to the new db and try to deploy, I receive the message saying the new database needs to be baselined. I don't know how to get that done since the wizard is never presented again.

eliassal

No Alex, the DB that I am trying to deploy to has no object, as I said , I just created the DB. Now let me explain: using SCA I created a project one DB on localdb called SQLAutomationchange, Target DB, on a 2nd dev server called SQLAutomationchange. Everything is OK, i can compile and checked in to source control. Now, in my release pipeline, I want to deploy to a real Q&A DB or prod to a DB called SQLAutomationchange-Prod and here where I am getting the errorr, so 1st of all how we deploy? 2nd how can I base line this 3rd DB or 4th in the future

PeterDaniels

Not sure if this relates to your exact situation, but this discussion may be helpful:
 
https://forum.red-gate.com/discussion/84708/best-way-to-handle-starting-sca-project-from-existing-dev-db#latest

I was running into baseline issues, too.

-Peter

eliassal

I am more and more confused with those concepts for a very simple basic concept "Deploy a database objetcs to a new empty one". 
The discussion does not apply to me as I am using TFS for build and release pipeline I don't want to baseline for the time being, what is the use of baselining an empty DB? maybe later after 1st deployment by not now, this is ridiculous

PeterDaniels

@eliassal, I understand this can be confusing. I believe I had similar issues when attempting to deploy to a DB that I thought was empty, but SCA didn't, so it failed to deploy.  For my case,I was able to:

1) uncheck "mark first folder as baseline" in the project settings.

2) add the "skip baseline check" flag to the project settings file by:

2a) right-click on the project file in solution explorer and select "unload project"

2b) right click on the unloaded project file and select "edit"

2c) in the first property group, add the following element:

<SlipBaselineCheck>True</SkipBaselineCheck>

2d) save the project file and close it

2e) right click the project file and select "load..."

Than you can use the powershell cmdlets to build and deploy. Any rogue objects in your target db might cause SCA to mark the first baseline script in your project as already deployed during the baseline check.

I found success with this. I also found success (alternatively) by discovering that my "empty" target db actually had a user that was in the model db. This user caused SCA to decide not to deploy the baseline script to my "empty" db. Removing that user from model and therefore from any new db I created allowed the project to successfully deploy to a new empty target without the "skip baseline check hack.

One of these options should work gro you.

I hope this is helpful rather than more confusing.

-Peter

eliassal

I DONT USE POWERSHELL cmdlets, I am using TFS, please read what I am writing before responding.
Question for this forum, is there anyone who has expertise in TFS and how to use your tools with TFS?

PeterDaniels

I believe TFS (and all of the CI server add-ons) are using the powershell cmdlets behind the scenes.  I believe my suggestions will help you.  That said, this will be my last post on this thread.  Good luck.

eliassal

OK but how to follow those steps/modifications in TFS? in TS pipeline, there is no notion of PowerShell script, only an interface to be configured with some values, how? I really don't know know or can figure what to or where to put.
Peter, this is exactly why I asked for someone who has experience with TFS config and not PowerShell, sorry for being rude q little bit as I am spending time, according to me should have been resolved in 5 minutes

PeterDaniels

I understand this can be frustrating, @eliassal. The changes I'm suggesting are made in your SQL Change Automation project - not in TFS or powershell.  I provided instructions on how to change your project in my post on feb 8.  Of course you are welcome to create a ticket with RG support, too.  That may provide a more expedient solution.

eliassal

Thanks Peter, hopefuly we are on the same line. I am aware of how to modify the sca project but is absolutly not my intention. I do a lot of DevOps pocs and train people mainly using TFS or Jenkins. I will stop here my efforts to evaluate this tool

Alex B

Hi all,

First, thank you @PeterDaniels for your input - I think it covers the bases well.

As shown in Peter's other post - if you have just created a blank database and it's indicating you need to create a baseline, it's likely a similar situation where there was an object in the model database being created and making it seem to be user objects in the new database. 

You would need to stop this or add these objects as the baseline so that they are ignored - or modify the SCA project to skip the baseline check as Peter also suggested.

The addons in TFS and other build/release systems use the PowerShell cmdlets behind the scenes (also as Peter pointed out) and the alternative would be if one were to use MSBuild on the project from Visual Studio (like was necessary when it used to be ReadyRoll).  

If none of those options are what you want to do @eliassal then I'm there's not much else we can do to help.  If you have tried those things and are still having issues or truly do not think that is the same issue, please send in screenshots and further explanation of what you are seeing including build logs to support @ red-gate.com and we can try to help out further

@GHood - If you are changing the connection to a database with a different set of starting objects, it's not appropriate to change the baseline so that it works with that new database; you should create a new project with a new baseline.  The point behind the baseline is that it exists in the target you are deploying to so that you don't count those objects, but if you are deploying to a blank database it will create them; it shouldn't be changed to deploy to different starting points.

I think that covers it all to this point, but if I've missed something please let me know.

Kind regards,
Alex

eliassal

Appreciate your efforts Alex. I will prepare all necessary details that allows you understand the issue Friday afternoon as I am out of town for a couple of days.