Transaction usage in SQL Source control is bad.
wpostma
Posts: 22
We have the problem that if you could fetch and apply one migration script at a time, you can be okay, but when you are three or four updates behind, redgate sql source control has the following rules, which I believe are stupid:
1. begin transaction like this:
2. do seven million lines of scripted changes.
3. at the end, commit or do not commit like this:
SQL Server can not scale the above approach out to actually make data and schema migrations work, even in 5.0.
Suppose we have the following sequence of changes:
A. We alter table T1, adding column T1C10
B. Now we insert data into T1C10, before the DML schema change is committed.
C. Now we get an error that you can't put data in some field in the table that is pending commit. The errors SQL Server shows are like this:
Msg 207, Level 16, State 1, Line 1261
Invalid column name 'T1C10'.
The product seems fundamentally designed badly in that it is unaware of these impossibilities and simply passes them along to me as an end user. My desire while using the product is to just update my database.
It seems to me that you could analyze the sequence of changes and build a sensible set of transactions within a migration, even when multiple new style 5.0 migration scripts are being fetched and applied in one batch.
This "one transaction" model is lame and has to be fixed.
In a discussion with a support person someone mentioned there are configurable "Comparison Options" but I can't find that anywhere in the GUI in RedGate SQL Source Control 5.1.1.2694. Where is that comparison option now?
Warren
1. begin transaction like this:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED GO BEGIN TRANSACTION GO
2. do seven million lines of scripted changes.
3. at the end, commit or do not commit like this:
IF @@ERROR <> 0 SET NOEXEC ON GO COMMIT TRANSACTION GO IF @@ERROR <> 0 SET NOEXEC ON GO DECLARE @Success AS BIT SET @Success = 1 SET NOEXEC OFF IF (@Success = 1) PRINT 'The database update succeeded' ELSE BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION PRINT 'The database update failed, restore from backup' END GO
SQL Server can not scale the above approach out to actually make data and schema migrations work, even in 5.0.
Suppose we have the following sequence of changes:
A. We alter table T1, adding column T1C10
B. Now we insert data into T1C10, before the DML schema change is committed.
C. Now we get an error that you can't put data in some field in the table that is pending commit. The errors SQL Server shows are like this:
Msg 207, Level 16, State 1, Line 1261
Invalid column name 'T1C10'.
The product seems fundamentally designed badly in that it is unaware of these impossibilities and simply passes them along to me as an end user. My desire while using the product is to just update my database.
It seems to me that you could analyze the sequence of changes and build a sensible set of transactions within a migration, even when multiple new style 5.0 migration scripts are being fetched and applied in one batch.
This "one transaction" model is lame and has to be fixed.
In a discussion with a support person someone mentioned there are configurable "Comparison Options" but I can't find that anywhere in the GUI in RedGate SQL Source Control 5.1.1.2694. Where is that comparison option now?
Warren
Comments
Thanks for your post and I apologize for the late reply! Note this is just a recap of what I sent via email.
You can choose the option "Don't use transactions in Deployment scripts" in both Compare and Source Control. More info here https://documentation.red-gate.com/disp ... on+options
That should solve this particular issue.
Warm Regards,
Technical Sales Engineer
Redgate Software