How To Ensure a Successful Database Upgrade

Ensuring smooth transitions during application upgrades are important. Maintaining system consistency and stability with a database upgrade is paramount.

Databases are complex systems with numerous stored procedures, each intricately linked to critical functionalities. Changes to these procedures during an upgrade could potentially disrupt overall system performance and negatively impact thousands of customers. With such high stakes, the pressure on IT operations to ensure zero downtime and bug-free transitions is immense.

The Data Verification Solution

As a senior backend developer, I recently undertook a significant database upgrade project. To tackle this challenge, I devised a meticulous data verification strategy. At the heart of this strategy is a well-crafted and rigorously tested SQL script. Take a look:

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) = 'KnockMaster-PROD-ESv1'
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.sproc_MasterClient_GetDetail @clientId = null')

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 Results

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

The End Result

The verification process yielded excellent results – zero downtime and no bugs – despite the complexity of the system and the potential impact on so many customers. Using my SQL script and robust data verification strategy, any IT practitioner can ensure similar success.

As more and more companies create, deploy and maintain applications, DevOps teams need to embrace software project management best practices to maintain consistently stable, high-performance applications. The customer experience is always priority one, and leveraging the right processes and tools for the job is imperative.

Here at Stackify, our DevOps team uses the deployment tracking features of our own Retrace lifecycle APM to ensure successful upgrades. With code-level tracing, centralized error and log management, deployment tracking and more, Retrace helps IT teams keep applications fully optimized.

Jesse Gador is a senior backend .NET developer for Retrace by Stackify – a BMC Company. He is passionate about optimizing system performance and enhancing code quality, especially during complex migration projects involving SQL, Azure Kubernetes, .NET, C#, Docker and more.

Author

Stackify

More articles by Stackify