What are the challenges you face when working across database platforms? Take the survey
Options

Questions about force marking 'deployed' baseline migration scripts

I have a couple of questions and would greatly appreciate your comments and help. 

Background:

I am using Sql Change Automation Core Edition with VSTS 2017 deployment pipeline.
To detect which environment a script is to be run, we have a DB_Variables table which has a row with name ENV pre-populated appropriately for each environment. 
All data scripts are wrapped with an environment condition to move/fix data per environment such as:
IF @Env ='Test'
BEGIN
   -- insert test data
END

We are already in production with db schema in place.  Moving forward with SQL change automation, we will make some db schema changes, some seed data changes and some data changes/fixes when and where necessary.

My SQL Change Automation project has 1.0.0-Baseline which is what we want.  
Q1: 
When I use the wizard to specify connections (source and target),  I had dev as source and prod as target.  However, I want the baseline out of production not out of dev. 
 How to specify the project to baseline from production database instead of dev database?  I am not sure how to stop the wizard from importing to go specify my connection string on the debug tab?

Q2: 
We are transitioning from dacpac to SQL Change Automation.  Though we intend to capture the schema and seed data baseline scripts and store them in the project, we DO NOT want them to execute.  I manually open the project, go to the project Properties/Debug tab to specify the target database and mark the baseline scripts as "Deployed".  
The Migration control table has data rows for these baseline scripts BUT the deployed column has a value '0'.
The project is smart enough to not see the baseline scripts as pending deployment.
Do I have to manually mark the baseline scripts for each environment we have ( dev, test, stage, prod) such that the migration control table gets created with data rows inserted for them and they won't accidentally be executed?




Tagged:

Best Answer

  • Options
    DiogoDiogo Posts: 67 Silver 5
    Regarding Q1 you're correct. The baseline should be made from the production database which is set as the deployment target. I don't think I understood the final part of your question.

    Regarding Q2 the migration control table will get created on the first deployment and the baseline will only be executed if you have an empty database. So if your databases for the different environments already have some objects in them then you sholdn't need to do anything else.
Sign In or Register to comment.