woensdag 4 juli 2018

DevOps : Releasing your database code with pre- and post deployment scripts (Part III)

Introduction

In a previous blogpost about DevOps (Part I and Part II) the basics of Build and Release process has been described. As I already mentioned in my previous blogpost, database DevOps projects are a bit more challenging than code DevOps projects. One of the problems is that tables contains data and you want preserve the (state of the) data.

For this blogpost I've used a table (sales.InvoiceLines) from the WideWorldImporters example database. First I've added an extra column to the database and as I will describe in this blogpost, I'll delete this column in the Visual studio project, execute the change script on the development database and commit and push the code to the Github repository. The automatic build and Release will execute the change on a QA database where the column is deleted. I'll show that if nothing is done (extra) the script will fail because of possible loss of data. For this blogpost, I'll execute a deployment script to handle this kind of errors.

Pre-and post deployment scripts are run once before the actual change script of Visual Studio. And, the pre- and post deployment scripts are only run with the publish option and not with the compare functionality. Although there may be other ways to handle this more clever, I approached this case as follows. In a pre-deployment script, I've created the following steps in T-SQL :
  1. Check if the column (TestColunn) in the original table exists.
  2. Check if there is a temporary table present and if so, drop the table.
  3. Copy the data from the original table in the temporary table.
  4. Disable Foreign keys.
  5. Truncate the original table.
The step in between is executed by the normal deployment script, to be exact, remove the TestColumn. Next step is the post deployment script.

In a Post Deployment script, I've written the following steps in T-SQL:
  1. Check if the rows in the original table is deleted.
  2. Copy the data from the temporary table to the changed original table.
  3. Enable the foreign keys again.
  4. Check if the rows are copied correctly.
  5. Remove the temporary table.
One thing that comes to my mind is that actually you want to put this in one transaction: Pre and post deployment. Because, you want to roll back the change when something goes wrong. Perhaps this is something for a future blogpost.

Remove a column from a table

 As said before, first I've added an extra column to the database and then I'll delete this column in the Visual studio project. Below the CREATE script

       
CREATE TABLE [Sales].[InvoiceLines] (
    [InvoiceLineID]  INT             NOT NULL,
    [InvoiceID]      INT             NOT NULL,
    [StockItemID]    INT             NOT NULL,
    [Description]    NVARCHAR (100)  NOT NULL,
    [PackageTypeID]  INT             NOT NULL,
    [Quantity]       INT             NOT NULL,
    [UnitPrice]      DECIMAL (18, 2) NULL,
    [TaxRate]        DECIMAL (18, 3) NOT NULL,
    [TaxAmount]      DECIMAL (18, 2) NOT NULL,
    [LineProfit]     DECIMAL (18, 2) NOT NULL,
    [ExtendedPrice]  DECIMAL (18, 2) NOT NULL,
-- [TestColumn]  INT    NULL,
    [LastEditedBy]   INT             NOT NULL,
 ....

If this script is deployed to a database it will return an error

       
(43,0): SQL72045: Script execution error.  The executed script:
IF EXISTS (SELECT TOP 1 1
           FROM   [Sales].[InvoiceLines])
RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127)
        WITH NOWAIT;
 

Deploying the script to VSTS will also result in the same error.



Here the actual log of the  unsuccessful deployment.



Block on possible data loss
There seems to be an option in the options window present : "Block on possible data loss". This will ignore the table check and deploy the database change anyway. Now, perhaps you have multiple changes to tables and in some cases you want to use this option and in some other cases not. So, In my opinion not very granular.



Add a pre- and post deployment file 
In order to control your deployment process, it is possible to use pre- and post deployment scripts. Pre- and post deployments are scripts that run every time a change is deployed to a database. Therefore these scripts should run in all possible situations. In the example of this blogpost, delete a column, the script should run in the case whether there is a column and there is no column (because the script has already ran before). Yet another is that you have to manage your pre- and post deployment scripts. If you have process of deployment with multiple environments you have to be sure that the code is deployed in production before deleting the code from the script.


Below, an example of the script I've created. I'm not saying that this a best practice script. It is just an example on how to handle both situations: there is a column and there is not a column.

This is an example of a Pre Deployment script.

     
--Check existence of the column in the table
IF EXISTS(SELECT 1
          FROM   INFORMATION_SCHEMA.COLUMNS
          WHERE  TABLE_NAME = 'InvoiceLines'
     AND TABLE_SCHEMA = 'Sales'
                 AND COLUMN_NAME = 'TestColumn') 
BEGIN

 --Check existence temp table
 IF EXISTS(SELECT 1
     FROM information_schema.tables
     WHERE table_schema = 'Sales' 
     AND table_name = 'tmp_InvoiceLines')
  DROP TABLE Sales.tmp_InvoiceLines

 SELECT * 
 INTO [Sales].[tmp_InvoiceLines]
 FROM [Sales].[InvoiceLines]

 --Disable the constraints
 ALTER TABLE [Sales].[InvoiceLines] NOCHECK CONSTRAINT [FK_Sales_InvoiceLines_Application_People];
 ALTER TABLE [Sales].[InvoiceLines] NOCHECK CONSTRAINT [FK_Sales_InvoiceLines_InvoiceID_Sales_Invoices]; 
 ALTER TABLE [Sales].[InvoiceLines] NOCHECK CONSTRAINT [FK_Sales_InvoiceLines_PackageTypeID_Warehouse_PackageTypes] ;
 ALTER TABLE [Sales].[InvoiceLines] NOCHECK CONSTRAINT [FK_Sales_InvoiceLines_StockItemID_Warehouse_StockItems]

 -- Remove the data.
 TRUNCATE TABLE [Sales].[InvoiceLines]
END 


This is an example of a post-deployment script.

IF (SELECT count(*) FROM  [Sales].[InvoiceLines]) = 0
BEGIN

 -- Check existence tmp table?

 --Insert the data from the tmp table in the new created table
 INSERT INTO [Sales].[InvoiceLines]
      ([InvoiceLineID]
      ,[InvoiceID]
      ,[StockItemID]
      ,[Description]
      ,[PackageTypeID]
      ,[Quantity]
      ,[UnitPrice]
      ,[TaxRate]
      ,[TaxAmount]
      ,[LineProfit]
      ,[ExtendedPrice]
      ,[LastEditedBy]
      ,[LastEditedWhen])
 SELECT [InvoiceLineID]
   ,[InvoiceID]
   ,[StockItemID]
   ,[Description]
   ,[PackageTypeID]
   ,[Quantity]
   ,[UnitPrice]
   ,[TaxRate]
   ,[TaxAmount]
   ,[LineProfit]
   ,[ExtendedPrice]
   ,[LastEditedBy]
   ,[LastEditedWhen]
 FROM [Sales].[tmp_InvoiceLines]

 --Enable the FK's
 ALTER TABLE [Sales].[InvoiceLines] CHECK CONSTRAINT [FK_Sales_InvoiceLines_Application_People];
 ALTER TABLE [Sales].[InvoiceLines] CHECK CONSTRAINT [FK_Sales_InvoiceLines_InvoiceID_Sales_Invoices]; 
 ALTER TABLE [Sales].[InvoiceLines] CHECK CONSTRAINT [FK_Sales_InvoiceLines_PackageTypeID_Warehouse_PackageTypes] ;
 ALTER TABLE [Sales].[InvoiceLines] CHECK CONSTRAINT [FK_Sales_InvoiceLines_StockItemID_Warehouse_StockItems]

 -- Drop the tmp Table if the data is copied correctly)
 IF (SELECT count(*) FROM  [Sales].[InvoiceLines]) > 0
 BEGIN
  IF EXISTS(SELECT 1
     FROM information_schema.tables
     WHERE table_schema = 'Sales' 
     AND table_name = 'tmp_InvoiceLines')
  DROP TABLE Sales.tmp_InvoiceLines
 END
END  

As mentioned before, it is just an example script. May be there are better patterns for solving these kind of changes.

Check into VSTS
Next step is checking the code in VSTS and see whether the Build and deploy works just fine. I commented the Testcolumn in the Table script in order to fake a deletion of the column and check the code into VSTS.

With the automated build and deployment of the commited code changes the deployment database.



And in the deployment database I can see that the table is recreated and the column is gone.


One more test is needed and that is that the Pre-and Post deployment script is executed when there is another change on another table and see whether that is handled properly.

Conclusion

Now, although I like the approach of pre- and post deployment in combination with an automated build and release process, there are some drawbacks:
  • The code has to be present in the script until the change is deployed in the production environment. So pre- and post deployment management is necessary.
  • The pre- and post deployment depends om some logic whether the change should execute or not. In my case whether the test column exists or not. Also, there is no 'connection' between the code in the pre- and post deployment script. I'm starting to think that this should be handled in a change management metadata system where you add a change, maintain the change and remove a change when it's in production. 

My 3 cents ;-)...

Hennie

Geen opmerkingen:

Een reactie posten