Skip to the content

Hoe maak ik een versiebeheer systeem in SQL Server

Iedereen die databases ontwikkelt en gebruik maakt van ontwikkel- en productieomgevingen kent de frustratie van het bijwerken van de productieomgeving. Hiervoor zijn verschillende oplossingen waarvan ik er 1 zal bespreken in dit blog.

De oplossing is eigenlijk redelijk eenvoudig en met deze oplossing zal je nooit meer een veld, query of bijgewerkte procedure vergeten te updaten in je productieomgeving.

Maak voor dit blog nu eerst 3 lege databases aan en noem deze als volgt:

  • blog_master: in deze database houden we de wijzigingen bij en werken we de productieomgeving bij
  • blog_development: hier ontwikkelen en testen we eerst onze wijzigingen.
  • blog_production: deze database is een exacte kopie van de development database nadat we de wijzigingen hebben doorgevoerd.

Voer onderstaande script uit op de database blog_master. In dit script worden 2 tabellen aangemaakt en 1 opgeslagen procedure.

De tabel LogDatabaselog bevat alle wijzigingen die worden aangemaakt in de development database. Er zijn ook velden benoemd die we in dit blog niet gebruiken maar die terug gaan komen in mijn volgende blog over het beheren van versies in SQL Server.

De tabel MetDbVersion bevat de versie informatie van de development en de production tabel. Met deze versies doen we in dit blog nog niet zoveel en ga ik in het volgende blog toepassen.

De procedure werkt, aan de hand van de data in de twee voorgaande tabellen, de productieomgeving bij.

 

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[LogDatabaselog](
	[LogLogId] [int] IDENTITY(1,1) NOT NULL,
	[LogDatabase] [nvarchar](30) NOT NULL,
	[LogTime] [datetime] NULL,
	[LogUser] [nvarchar](128) NULL,
	[LogEvent] [nvarchar](128) NULL,
	[LogSchema] [nvarchar](128) NULL,
	[LogObject] [nvarchar](128) NULL,
	[LogTSQL] [nvarchar](max) NULL,
	[LogXmlEvent] [xml] NULL,
	[SysDatabaseBuild] [int] NULL,
	[SysDatabaseVersion] [int] NULL,
	[SysDatabaseRevision] [int] NULL,
	[SysDatabasePatch] [int] NULL,
	[LogIsUpdated] [bit] NOT NULL,
 CONSTRAINT [PK_LogDatabaselog] PRIMARY KEY CLUSTERED 
(
	[LogLogId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[LogDatabaselog] ADD  CONSTRAINT [DF_LogDatabaselog_SysDatabaseBuild]  DEFAULT ((0)) FOR [SysDatabaseBuild]
GO

ALTER TABLE [dbo].[LogDatabaselog] ADD  CONSTRAINT [DF_LogDatabaselog_SysDatabaseVersion]  DEFAULT ((0)) FOR [SysDatabaseVersion]
GO

ALTER TABLE [dbo].[LogDatabaselog] ADD  CONSTRAINT [DF_LogDatabaselog_SysDatabaseRevision]  DEFAULT ((0)) FOR [SysDatabaseRevision]
GO

ALTER TABLE [dbo].[LogDatabaselog] ADD  CONSTRAINT [DF_LogDatabaselog_SysDatabasePatch]  DEFAULT ((0)) FOR [SysDatabasePatch]
GO

ALTER TABLE [dbo].[LogDatabaselog] ADD  CONSTRAINT [DF_LogDatabaselog_LogIsUpdated]  DEFAULT ((0)) FOR [LogIsUpdated]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[MetDbVersion](
	[MetDbBuild] [int] NOT NULL,
	[MetDbVersion] [int] NOT NULL,
	[MetDbRevision] [int] NOT NULL,
	[MetDbPatch] [int] NOT NULL,
	[MetDbName] [varchar](30) NOT NULL,
	[MetDbProduction] [varchar](30) NULL,
	[MetDbVersionDate] [datetime] NOT NULL,
	[MetDbIsUpdated] [bit] NOT NULL,
 CONSTRAINT [PK_MetDbVersion] PRIMARY KEY CLUSTERED 
(
	[MetDbBuild] ASC,
	[MetDbVersion] ASC,
	[MetDbRevision] ASC,
	[MetDbPatch] ASC,
	[MetDbName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[MetDbVersion] ADD  CONSTRAINT [DF_MetDbVersion_MetDbIsUpdated]  DEFAULT ((0)) FOR [MetDbIsUpdated]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[usp_UpdateProductionDatabase]
	@DATABASENAME		NVARCHAR(30)
AS
	
	--Ik gebruik hier een cursor maar een while kan natuurlijk ook	
	DECLARE 
		@LOG INT, --VARIABELE OM DE VERSIE UPDATE BIJ TE WERKEN
		@SQL NVARCHAR(MAX), --VARIABELE VOOR HET SCRIPT
		@PROD NVARCHAR(30), --VARIABELE VOOR DE NAAM VAN DE PRODUCTIE DATABASE
		@SQL_EX NVARCHAR(MAX)
		
	DECLARE Production_Cursor CURSOR FOR
	SELECT L.LogLogId, L.LogTSQL, V.MetDbProduction
	FROM dbo.LogDatabaselog AS L INNER JOIN
	dbo.MetDbVersion AS V ON L.SysDatabaseBuild = V.MetDbBuild AND L.SysDatabaseVersion = V.MetDbVersion AND L.SysDatabaseRevision = V.MetDbRevision AND 
	L.SysDatabasePatch = V.MetDbPatch AND L.LogDatabase = V.MetDbName
	WHERE (L.LogIsUpdated = 0) AND (L.LogDatabase = @DATABASENAME)

	OPEN Production_Cursor
	FETCH NEXT FROM Production_Cursor 
	INTO @LOG, @SQL, @PROD

	WHILE @@FETCH_STATUS = 0
	BEGIN

			--NU DE T-SQL VAN DE WIJZIGING TOEKENNEN AAN HET SCRIPT
			SET @SQL_EX = N'USE ['+@PROD+']; EXEC sp_executesql N'''+@SQL+''''
			--UITVOEREN SCRIPT
			EXEC (@SQL_EX)
			--BIJWERKEN LOG TABEL
			UPDATE dbo.LogDatabaselog SET 
				LogIsUpdated = 1
			FROM dbo.LogDatabaselog 
			WHERE LogLogId = @LOG
			
		FETCH NEXT FROM Production_Cursor 
		INTO @LOG, @SQL, @PROD
	END
	CLOSE Production_Cursor
	DEALLOCATE Production_Cursor	



GO

 

Voer onderstaande script uit op de database blog_development. Dit script maakt een database trigger aan die iedere wijziging aan het database model (tabellen, views, functies en procedures) wegschrijft in de tabel LogDatabaselog van de database blog_master.

 

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE TRIGGER [ddlDatabaseTriggerLog] 
ON DATABASE 
FOR DDL_DATABASE_LEVEL_EVENTS 
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @data XML;
    DECLARE @schema sysname;
    DECLARE @object sysname;
    DECLARE @eventType sysname;

    SET @data = EVENTDATA();
    SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
    SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') 

	DECLARE @BUILD INT, @VERSION INT, @REVSION INT, @PATCH INT
	
	SELECT @BUILD = MetDbBuild, @VERSION = MetDbVersion, @REVSION = MetDbRevision, @PATCH = MetDbPatch
	FROM [blog_master].dbo.MetDbVersion
	WHERE MetDbVersionDate = (SELECT MAX(MetDbVersionDate) FROM [blog_master].dbo.MetDbVersion) AND (MetDbName = DB_NAME())

    INSERT [blog_master].[dbo].[LogDatabaselog] 
        (
        [LogDatabase],
        [LogTime], 
        [LogUser], 
        [LogEvent], 
        [LogSchema], 
        [LogObject], 
        [LogTSQL], 
        [LogXmlEvent],
        SysDatabaseBuild,
        SysDatabaseVersion, 
        SysDatabaseRevision,
        SysDatabasePatch
        ) 
    VALUES 
        (
        DB_NAME(),
        GETDATE(), 
        CONVERT(sysname, CURRENT_USER), 
        @eventType, 
        CONVERT(sysname, @schema), 
        CONVERT(sysname, @object), 
        @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'Nvarchar(max)'), 
        @data,
        @BUILD,
        @VERSION,
        @REVSION,
        @PATCH
        );
END;





GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

DISABLE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE
GO

ENABLE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE
GO

 

Maak nu een nieuwe tabel aan in de database blog_development. Of voer onderstaand script uit om een testtabel aan te maken in de database development.

 

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[first_blog_table](
	[id] [int] NOT NULL,
	[name] [varchar](50) NULL,
 CONSTRAINT [PK_first_blog_table] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

 

Voer nu de procedure usp_UpdateProductionDatabase uit of gebruik onderstaand script.

 

USE [blog_master]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[usp_UpdateProductionDatabase]
		@DATABASENAME = N'blog_development'

SELECT	'Return Value' = @return_value

GO

 

Nu zie je dat er in de database blog_production dezelde tabel staat als die je hebt toegevoegd in de development database.

versiebeheer sql server | reinder.eu

 

 

In het volgende blog ga ik met dit blog het versiebeheer en het maken van een updatescript voor meerdere omgevingen behandelen.

Ik stel het op prijs als je dit blog deelt of liket als het je op weg heeft geholpen.