Ensuring Seamless Database Upgrades

Ensuring a smooth transition during upgrades is paramount, especially when it comes to database. As a developer, I recently undertook a significant upgrade project, and I am excited to share a valuable trick that has been instrumental in maintaining consistency and stability throughout the process.

The Challenge

A complex system with numerous stored procedures, each intricately linked to critical functionalities. Any changes made to these procedures during an upgrade could potentially disrupt the system's functionality, affecting not just one or two, but nearly 1000 customers. With such high stakes, the pressure to ensure zero downtime and bug-free transitions is immense.

The Solution: Data Verification

To tackle this challenge, I devised a meticulous data verification strategy. At the heart of this strategy is a meticulously crafted and rigorously tested SQL script.

DROP TABLE IF EXISTS #tmpDataCompareResult

CREATE TABLE #tmpDataCompareResult
(
  StoredProcedure NVARCHAR(256),
  SourceDb NVARCHAR(256),
  SourceCount INT,
  TargetDb NVARCHAR(256),
  TargetCount INT,
  CompareResult AS (CASE WHEN SourceCount = TargetCount THEN 'Identical' ELSE 'Not Identical' END)
)

DROP PROCEDURE IF EXISTS #CompareDataResultsFrom
GO

CREATE PROCEDURE #CompareDataResultsFrom
  @TargetDbName NVARCHAR(100),
  @Sql NVARCHAR(MAX)
AS
BEGIN
  
SET NOCOUNT ON;

DROP TABLE IF EXISTS #tmpSource
DROP TABLE IF EXISTS #tmpTarget

CREATE TABLE #tmpSource ([ ] INT)
CREATE TABLE #tmpTarget ([ ] INT)

DECLARE @AlterTable NVARCHAR(MAX)
DECLARE @AlterTargetTable NVARCHAR(MAX)
DECLARE @SqlResultSchema NVARCHAR(MAX) = (SELECT CONCAT('sp_executesql ', 'N''', @Sql, ''''))
DECLARE @TargetSql NVARCHAR(MAX) = (SELECT CONCAT(QUOTENAME(@TargetDbName), '.', @Sql))
DECLARE @SourceCount INT = 0
DECLARE @TargetCount INT = 0
	
-- alter #tmpSource schema
SELECT @AlterTable = string_agg(N'ALTER TABLE #tmpSource ADD ' + QUOTENAME([name]) + ' ' + system_type_name + NCHAR(13) + NCHAR(10), '') WITHIN GROUP (ORDER BY column_ordinal)
FROM sys.dm_exec_describe_first_result_set (@Sql, NULL, 0) a
WHERE a.is_hidden = 0

SET @AlterTable += N'ALTER TABLE #tmpSource DROP COLUMN [ ]' + nchar(13) + nchar(10)

EXEC (@AlterTable)

-- alter #tmpTarget schema
SELECT @AlterTable = string_agg(N'ALTER TABLE #tmpTarget ADD ' + QUOTENAME([name]) + ' ' + system_type_name + NCHAR(13) + NCHAR(10), '') WITHIN GROUP (ORDER BY column_ordinal)
FROM sys.dm_exec_describe_first_result_set (@Sql, NULL, 0) a
WHERE a.is_hidden = 0

SET @AlterTable += N'ALTER TABLE #tmpTarget DROP COLUMN [ ]' + nchar(13) + nchar(10)

EXEC (@AlterTable)
	
INSERT INTO #tmpSource
EXEC sp_executesql @Sql
SET @SourceCount = (SELECT COUNT(*) FROM #tmpSource)

INSERT INTO #tmpTarget
EXEC sp_executesql @TargetSql
SET @TargetCount = (SELECT COUNT(*) FROM #tmpTarget)

INSERT INTO #tmpDataCompareResult (StoredProcedure, SourceDb, SourceCount, TargetDb, TargetCount)
VALUES (@Sql, DB_NAME(), @SourceCount, @TargetDbName, @TargetCount)

DROP TABLE IF EXISTS #tmpSource
DROP TABLE IF EXISTS #tmpTarget

END
GO

SET NOCOUNT ON

DECLARE @TargetDb NVARCHAR(1000) = 'MyTargetLocalDB'
DECLARE @Sql NVARCHAR(MAX)
DECLARE @CompareSql NVARCHAR(MAX)

DROP TABLE IF EXISTS #tmp
CREATE TABLE #tmp ([Sql] NVARCHAR(MAX))

INSERT INTO #tmp ([Sql]) VALUES ('dbo.StoredProc1 @parameter1 = 1')
INSERT INTO #tmp ([Sql]) VALUES ('dbo.StoredProc2 @parameter1 = 1, @parameter2 = 2')
INSERT INTO #tmp ([Sql]) VALUES ('dbo.StoredProc2 @parameter1 = 3, @parameter2 = 4')
INSERT INTO #tmp ([Sql]) VALUES ('dbo.StoredProc2 @parameter1 = 1, @parameter2 = 2')
INSERT INTO #tmp ([Sql]) VALUES ('dbo.StoredProc2 @parameter1 = null, @parameter2 = null')
INSERT INTO #tmp ([Sql]) VALUES ('dbo.StoredProc2 @parameter1 = null, @parameter2 = 1')


DECLARE tmp_cursor CURSOR LOCAL STATIC FORWARD_ONLY FOR
SELECT [Sql]
FROM #tmp

OPEN tmp_cursor

FETCH NEXT FROM tmp_cursor
INTO @Sql

WHILE @@FETCH_STATUS = 0
BEGIN

  SET @CompareSql = CONCAT('EXEC #CompareDataResultsFrom @TargetDbName = ', '''', @TargetDb, '''', ', @Sql = ', '''', @Sql, '''')
	
  EXEC sp_executesql @CompareSql

  FETCH NEXT FROM tmp_cursor
  INTO @Sql

END

CLOSE tmp_cursor
DEALLOCATE tmp_cursor

SELECT * FROM #tmpDataCompareResult a
ORDER BY a.CompareResult, a.StoredProcedure        

Sample Result

Below is a screenshot illustrating a sample result of the data comparison:

The Results

Thanks to this robust data verification strategy, I am proud to report that our upgrade process has been a resounding success. Despite the complexity of the system and the potential impact on a significant number of customers, we have experienced zero downtime and, as of this writing, encountered no bugs.