SQL Change Automation is always running the baseline script

cgipson

I have an existing database in azure, lets call it dev.  I set dev as the deployment target for my change automation project in vs 2017.  I have a new database on my local machine.  I run through the wizard and it deploys everything (Except data) down to my local machine.  I save my projects and push back up to VSTS.  In VSTS, I have a build pipeline that utilizes the Redgate SQL Change Automation Build step.  This builds the project and creates the necessary package which is picked up by my release pipeline.  The pipeline then runs a deployment of said package using the Redgate SQL Change Automation Release step.  Each and every time, I get an error saying that a user already exists in the target database.  If I pulled down the baseline from dev, should the script not run against dev, only new changes?  It was my understanding that if there were already objects in the database the baseline script would not run.  Please help, I have been trying to get this to work for two days now.

cgipson

As an added note, I downloaded the build package and extracted the PackageScript.sql and ran it directly against the deployment database, and it ran as expected.  Does the Redgate SQL Change Automation release step not run that script?  

way0utwest

The build process always builds a new database. That's the point of build, to verify all the SQL works without issues. The release step is what does the upgrade step. Make sure your target is set to an Azure database, otherwise there are sometimes items included from the on-premises SQL Server perspective that cause issues in Azure SQL DB.

tee

we set our SCA test project without a target DB and when the release runs it errors because the agent can't login to the SQL Server database. But the database doesn't exist, yet in the failed step our agent has SA so it can do whatever it needs to do).. 

tee

Follow on note:
The target DB is set in the release environment to the target server and same DB name is used.

way0utwest

Can you post some of the log? Are you using the cmdlet to create the release db, or just point to the target and generate an upgrade script?

tee

We decided to create the target DB before we create the SCA package. 

tee

@SERVERNAME = 'NOTHING'" and the condition does not stop the baseline from executing.  What can I do to ensure that the baseline does NOT execute. Once it's created, I can't delete it. The documentation states that the condition will restrict the baseline from executing even on the first release. Should I un-comment the baseline header?

ACM13

I was having a similar problem, and I found that in setting up my dev DB, I'd created __MigrationLog et al. by accident.  I think that was confusing SCA into thinking that it needed to run the baseline (see the Deployment Behavior section here).  Running the following script on my DB fixed me right up:

DROP VIEW __MigrationLogCurrent;DROP TABLE __SchemaSnapshot;DROP TABLE __MigrationLog;