Migration script causing issues...?

robinw

Hi

I have attempted to use a migration script on my local database to do the following:

• add a column to an existing table
• populate new column with a default value of zero
• alter new column to be not null

By all the accounts the migration script works as designed - however I have come across an issue...

Another user has freshly created the same database and done his first "Get Latest". He noticed that not all roles, schemas and permissions were pulled down from Source Control (Git).

Having checked source control, it seems that the permissions are indeed there - so they should have been pulled down to his SSMS.

He then checked his Commit tab and it was suggesting that these objects freshly pulled down (without the relevant objects), should be committed to source control - which is wrong.

I have come across one correlation: the table the migration script affects needs to have associated objects pulled across as well (dependencies) before a "Get Latest" is achieved. Unfortunately, when a Get Latest is done, certain role permissions and a schema from these dependencies are missing - and the objects affected with things missing are the objects associated with the migration script object.

Steps to reproduce the issue:

- Run code similar to the following on local database:

   ALTER TABLE MetaData.Table1
   ADD Column1 BIT NULL
   GO

   UPDATE MetaData.Table1
   SET Column1 = 0
   GO

   ALTER TABLE MetaData.Table1
   ALTER COLUMN Column1 BIT NOT NULL
   GO

- Click on the Migrations tab in Source Control

- Click Generate script

- Replace all code generated with the code above

- Click on commit tab and commit the changes via migration script. Push to Git. Change works successfully

- Another user creates a fresh copy of database on another machine. Links to source control and clicks Get Latest

- All objects appear to be pulled form SC but Commit tab says otherwise

- Code lines missing are mostly permissions to objects via database roles - ie GRANT INSERT ON TABLE TO DBROLE

- MetaData schema also has GRANT SELECT ON SCHEMA permission missing

Any questions, please let me know.

robinw

Update: I have found a workaround, but it isn't pretty....

After the migration script has been used all the way to the production environment, do the following:

• Under the migrations tab, remove the existing migration script from the inventory
• Commit and push the migration script removal change
• Unlink the local database from Source Control
• Relink local database to Source Control
• Use Get Latest and the changes will be there to merge into your local database
• All previous changes covered in the migration script is still in my local database
  

My conclusion is this is definitely a problem with the migration scripts functionality - unless I am using them in the incorrect way or my filters are wrong.....

I'd be very interested to see what Redgate tech support has to say about this.

Any ideas as to why this is happening?

Version used is 7.0.11.8918

Tianjiao_Li

@robinw

Thanks for posting. I wasn't able to reproduce it with 7.0.25. Please upgrade and let us know if it make any difference. 

If problem persists, would you be able to share your git repository with us? I've raised a ticket and I'll get in touch with you by email shortly.

robinw

Hi @Tianjiao_Li

I am on a machine that has no access to the internet.

Is there a way i can install the latest version without connecting online?

I have tried uninstalling SQL Source Control and installing again but the version number still displays 7.0.11.8918...

Thanks.