May 6, 2015 at 9:57 am
Just as a general call, if anyone is interested in sharing how they do this for specific code, perhaps an example of how you handle data, config, table changes, etc. in an idempotent way, we'd love to have examples people can use to better understand how they can transition to this in practice.
May 6, 2015 at 10:43 am
Steve Jones - SSC Editor (5/6/2015)
Bradley Deem (5/6/2015)
I've been writing idempotent scripts for years. However, even that's not the best defense against a script run twice. I've found it's more economical to write a guard at the top of the script to check database/app version or existence of a change already being performed.In other words, stop the script immediately at start instead of reading through 20k lines to make sure everything was idempotent.
Any examples here? What do you use to check for db version?
Unfortunately, I don't have a SQL Server example, but here's the code in Oracle using SQLPLus. The key to this solution is to carry the upgrade version in a script variable that would survive DDL and DML changes.
-- Required database version for upgrade. This is the upgrade "from" version.
DEF db_version_expected = '6'
-- New database version after upgrade scripts. This is the upgrade "to" version.
DEF db_version_new = '7'
PROMPT
PROMPT -- Validate Database Version --
PROMPT
WHENEVER SQLERROR EXIT SQL.SQLCODE
BEGIN
DECLARE
db_version_actual VARCHAR2(200);
BEGIN
SELECT VALUE
INTO db_version_actual
FROM schema.config
WHERE KEY = 'DB_VERSION'
;
-- Halt on invalid database version
IF db_version_actual <> '~db_version_expected' THEN
RAISE_application_error(-20000,'Database version is incorrect expected '''
|| '~db_version_expected' || ''', found ''' || db_version_actual || '''.');
END IF;
-- Expected database version found
-- Set the database version to an upgrade state.
UPDATE schema.config
SET VALUE = '~db_version_expected.U' -- Version is set to #U, ie '7U' for upgrade.
WHERE KEY = 'DB_VERSION';
COMMIT;
END;
END;
/
-- Halt on failure
WHENEVER SQLERROR EXIT -1
WHENEVER OSERROR EXIT -2
PROMPT
PROMPT -- Begin Upgrade Scripts --
PROMPT
-- DO WORK HERE
PROMPT
PROMPT -- End Upgrade Scripts --
PROMPT
-- Upgrade Database version
UPDATE schema.CONFIG
SET VALUE = '~db_version_new'
WHERE KEY = 'DB_VERSION';
COMMIT;
PROMPT
PROMPT -- End Upgrade Scripts --
PROMPT
May 6, 2015 at 12:42 pm
Bradley Deem (5/6/2015)
Unfortunately, I don't have a SQL Server example, but here's the code in Oracle using SQLPLus. The key to this solution is to carry the upgrade version in a script variable that would survive DDL and DML changes.
Interesting. Certainly you can track db version in the database. quite a few vendors do this and it works well as long as you can keep control of the db and ensure no drift (changes unexpected) to the database. Otherwise your version scripts still need idempotent code to account for things.
However I'm curious, if your script fails halfway through, you have a version at 7U, not 6 or 7 and now your upgrade script won't run. Do you manually reset to 6 and your script skips the sections that worked? Do things roll back?
May 7, 2015 at 3:22 am
I learnt the term idempotent a few years ago, even though I ensured my code was re-runable long before that.
Personally, I don't trust version numbers in a database, they are a useful first glance when comparing systems but unless security is locked down, something I have yet to see properly implemented, you cannot guarantee someone hasn't gone into the system and changed something manually following a deployment.
As requested Steve, here's how I write most of my scripts:
-- Always include the database that the script is expected to be run against
USE MyDatabase;
GO
-- Inserting new data.
-- I use merge as a preference so my solution is always consistent,
-- rather than using IF NOT EXISTS ... INSERT sometimes and MERGE on others
MERGE dbo.MyTable t
USING (
VALUES (1, 'Value1')
, (2, 'Value2')
) s (Id, Value) ON s.Id = t.Id
WHEN NOT MATCHED BY TARGET THEN
INSERT (Id, Value)
VALUES (s.Id, s.Value);
GO
-- Altering/creating sp's/functions/views/triggers
IF (OBJECT_ID('dbo.MyProc') IS NULL)
EXEC ('CREATE PROCEDURE dbo.MyProc AS SELECT 1;');
GO
ALTER PROCEDURE dbo.MyProc
AS
BEGIN
SET NOCOUNT ON;
END;
GO
-- New table, depends on the situation, sometimes I drop if it exists, sometimes I leave the table
IF (OBJECT_ID('dbo.MyTable') IS NOT NULL)
DROP TABLE dbo.MyTable;
GO
CREATE TABLE dbo.MyTable (
Id INT NOT NULL CONSTRAINT PK_dbo_MyTable PRIMARY KEY CLUSTERED
, Value VARCHAR(10) NOT NULL
);
GO
-- OR...
IF (OBJECT_ID('dbo.MyTable') IS NULL)
CREATE TABLE dbo.MyTable (
Id INT NOT NULL CONSTRAINT PK_dbo_MyTable PRIMARY KEY CLUSTERED
, Value VARCHAR(10) NOT NULL
);
-- New column
IF (NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = 'NewColumn' AND [object_id] = OBJECT_ID('dbo.MyTable')))
ALTER TABLE dbo.MyTable ADD NewColumn BIT NOT NULL CONSTRAINT DF_dbo_MyTable_NewColumn DEFAULT (1);
-- I may drop the constraint if not required, always checking
IF EXISTS (SELECT 1 FROM sys.default_constraints WHERE name = 'DF_dbo_MyTable_NewColumn')
ALTER TABLE dbo.MyTable DROP CONSTRAINT DF_dbo_MyTable_NewColumn;
Writing deployment scripts like this is so important. The pain of one command in one script generating an error, and then picking through it all find the bits you need to re-run (that command may have had a knock on effect to following commands) is something I really hate.
May 7, 2015 at 3:33 am
Steve Jones - SSC Editor (5/6/2015)
Gary Varga (5/6/2015)
This also applies to application code. Not just scripts. I use this technique in stored procedures too.Examples? Want to share in an article, Gary?
I'll add it to my list. This should only be a brief thing so whilst I could question the value you, Mr Jones, could question any excuse I could come up with for not writing it 😉
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
May 7, 2015 at 7:28 am
Interesting. Certainly you can track db version in the database. quite a few vendors do this and it works well as long as you can keep control of the db and ensure no drift (changes unexpected) to the database. Otherwise your version scripts still need idempotent code to account for things.
Correct. You must have the discipline to deploy version next without drift. If you experience drift you should integrate that back into your code base before deployment of version next. If tracking a version is not feasible, for example due to a chaotic environment where anybody can make changes whenever, then you must pick something else to verify has not been completed. For example, you could check that the first thing performed in your script has not been performed.
However I'm curious, if your script fails halfway through, you have a version at 7U, not 6 or 7 and now your upgrade script won't run. Do you manually reset to 6 and your script skips the sections that worked? Do things roll back?
So it's assumed that the deployment process has gone through the necessary quality control gates that this will rarely happen. If it does happen then the database would be in an unexpected state. This would require manual intervention to correct and resume the script from the point of failure. Alternatively, a rollback to before the deployment could be performed via Flashback (Oracle) / Snapshot (SQL Server) or a Restore. Admittedly, it's even more rare to perform a flashback or restore.
The key to a successful deployment despite a failure is to write idempotent scripts when practical to save on headache time in the event of failure. Also, multiple idempotent scripts are not necessary idempotent when performed together. This integration side effect is even more difficult to spot when multiple developers are involved.
May 7, 2015 at 7:42 am
'Flashback'. What is that?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 7, 2015 at 8:00 am
Phil Parkin (5/7/2015)
'Flashback'. What is that?
Sorry, Oracle terminology. In SQL Server this would be Snapshot. Regardless, use technology to allow you to rollback to the point in time before deployment without the need of a restore operation. I've edited my previous post for clarification.
May 8, 2015 at 7:18 am
Bradley Deem (5/7/2015)
Phil Parkin (5/7/2015)
'Flashback'. What is that?Sorry, Oracle terminology. In SQL Server this would be Snapshot. Regardless, use technology to allow you to rollback to the point in time before deployment without the need of a restore operation. I've edited my previous post for clarification.
Has anyone here actually used Dabase Snapshots to rollback a deployment? Will it rollback only DML and DDL modifications made by your session, or is it a point in time rollback for the entire database?
Oracle's Flashback featureset goes well beyond SQL Server snapshots. For example, they have some interesting SELECT statement extensions for querying previous versions of table rows.
-- The following query retrieves the state of the employee record for JOHN at 9:30AM, April 4, 2003:
SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN';
-- This update then restores John's information to the employee table:
INSERT INTO employee
(SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN');
http://docs.oracle.com/cd/B13789_01/appdev.101/b10795/adfns_fl.htm
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 8, 2015 at 7:25 am
Eric M Russell (5/8/2015)
Bradley Deem (5/7/2015)
Phil Parkin (5/7/2015)
'Flashback'. What is that?Sorry, Oracle terminology. In SQL Server this would be Snapshot. Regardless, use technology to allow you to rollback to the point in time before deployment without the need of a restore operation. I've edited my previous post for clarification.
Has anyone here actually used Dabase Snapshots to rollback a deployment? Will it rollback only DML and DDL modifications made by your session, or is it a point in time rollback for the entire database?
Oracle's Flashback featureset goes well beyond SQL Server snapshots. For example, they have some interesting SELECT statement extensions for querying previous versions of table rows.
-- The following query retrieves the state of the employee record for JOHN at 9:30AM, April 4, 2003:
SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN';
-- This update then restores John's information to the employee table:
INSERT INTO employee
(SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN');
http://docs.oracle.com/cd/B13789_01/appdev.101/b10795/adfns_fl.htm
We've used snapshots. It's like rolling back your database to the state it was at at the time the snapshot was taken (in effect, a point-in-time restore).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 14, 2015 at 1:20 am
Maybe this https://www.simple-talk.com/sql/database-administration/using-migration-scripts-in-database-deployments/ may be of use. This formed the basis for our scripts.
We use one script to update data models of two versions of the same application. (Though we split those scripts every quarter).
Both versions differ quite a bit. So each code fragment basically starts with a version check. Therefore we store the version in a global temp table, which is dropped at the end of the script.
SET NOCOUNT ON;
DECLARE @Version TINYINT = 0;
/* Specify correct version... */ /* This has to be changed before deployment */
IF COALESCE(@Version, 0) = 0 BEGIN
PRINT 'Unspecified version, process is terminated.'
SET NOEXEC ON /* ! */
RETURN;
END;
IF OBJECT_ID('tempdb..##Version') IS NOT NULL BEGIN
DROP TABLE ##Version;
END;
CREATE TABLE ##Version (version TINYINT);
INSERT INTO ##Version (version) SELECT @Version;
PRINT 'Selected version: ' + CAST(@Version AS VARCHAR);
GO
Because of the combination of DDL and DML, we had to use dynamic SQL. And we use batches to separate modifications.
One made up example could be:
/* [<Version>] [<Date>] [<Change request info>] */
DECLARE @StatementExecuted BIT = 0;
IF EXISTS (SELECT 'Version' FROM ##Version WHERE version = 2)
/* only for a certain version */
AND NOT EXISTS(SELECT 'CR' FROM information_schema.columns c WHERE c.table_name = 'OrderDetail' AND c.column_name = 'IsConfirmed')
/* do we still have to create the column? */
AND EXISTS(SELECT 'CR' FROM information_schema.tables t WHERE t.table_name = 'OrderDetail' )
/* and does the table exist? */
BEGIN
PRINT 'Modifying TABLE OrderDetail could take some time...';
BEGIN TRY
ALTER TABLE OrderDetail ADD IsConfirmed BIT NOT NULL CONSTRAINT df_OrderDetail_IsConfirmed DEFAULT 0;
PRINT 'Modifying TABLE OrderDetail succeeded';
SELECT @StatementExecuted = 1; /* Alter historical data */
END TRY
BEGIN CATCH
PRINT 'Modifying TABLE OrderDetail: ' + ERROR_MESSAGE();
END CATCH;
END;
IF @StatementExecuted = 1 BEGIN /* so this will not execute when the script runs a second time */
BEGIN TRY
EXEC sp_executesql N'
UPDATE OrderDetail SET
OrderDetail.IsConfirmed = 1
FROM OrderHeader
JOIN OrderDetail ON OrderHeader.OrderHeader_id = OrderDetail.OrderHeader_id
WHERE OrderHeader.IsInvoiced = 1;';
PRINT 'Modifying data OrderDetail succeeded';
END TRY
BEGIN CATCH
PRINT 'Modifying data OrderDetail: ' + ERROR_MESSAGE();
END CATCH;
END;
GO
So far this way of scripting has served our needs. Most of the changes are trivial.
Scripting a UDTT - modification can be challenging. 🙂
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply