How to Data Patch Safely With Confidence

There are some unavoidable situations where we have no choice but to do data patch. This article serves to present some ideas on how we can approach these situations with greater visibility and confidence.

TLDR - Simulate data patch before running the actual patch:

• Use reader account
• Create temp tables based on the actual tables
• Run Patch on those temp tables
• Check data in temp tables if results are expected
• Replace temp tables with actual underlying tables

Best Practices:

• You should only use reader_accounts - accounts with read-only permissions.

• Always simulate the patches with temp tables first and verify it.

  It is best to first use temp tables to simulate the patch rather than trying to guess what data the patch will alter. This way, we have direct visbility over the exact data that we are altering. We can then cross check if the patch was successful with some predetermined queries called by endpoints or background worker services.

• Always use the following format whenever you want to insert the current SG Time.

  Declare @CurrentDateTime as datetime = convert(datetime2, getdate() at time zone 'UTC' at time zone 'Singapore Standard Time'); -- current sgt time when transaction is executed
  

  use this declared variable @CurrentDateTime in your altering script.

• Prepare and run pre and post patch checks to ensure data integrity.

• Add row counts to ensure that you are only editing what you should be editing.

• Add block comments to be clear on what the patch is about.

• Wrap the patch in a transaction and run the entire transaction in one go.

Steps for Data Patch Simulation

Running a simulation is the key to ensuing the success of the patch.

For illustration purposes, we will use AdventureWorksLT2017 database as provided by Microsoft for all db illustration purposes. Check out the instructions here to download and create the sample database using restore from the .bak file.

Database should look something like this:

Full Simulation Sample Patch Script:

/*
Ticket Number:

Reason for patch:

Description: Change the following:
- CompanyName to 'TSP'
- Address to '138 Cecil Street'
- City to 'Singapore'
for the following CustomerIds:
29485
29489
29492
*/

/*
1. Define Base Temp Tables
- remove and replace these with the actual base tables once the patch is finalized.
*/
select *
into #BaseCustomer
from [SalesLT].[Customer]

select *
into #BaseCustomerAddress
from [SalesLT].[CustomerAddress]

select *
into #BaseAddress
from [SalesLT].[Address]

/*
2. Check and backup data to csv 
*/
select
    c.CustomerID
    ,c.FirstName
    ,c.MiddleName
    ,c.LastName
    ,c.CompanyName
    ,a.AddressLine1
    ,a.City
from #BaseCustomer as c
join #BaseCustomerAddress as ca on ca.CustomerID = c.CustomerID
join #BaseAddress as a on ca.AddressID = a.AddressID
where c.CustomerID in (
    '29485',
    '29492',
    '29489'
)
 
/*
3. Patching Script
*/

begin transaction tran1
begin try

select
    c.CustomerID
    ,a.AddressID
into #tempData
from #BaseCustomer as c
join #BaseCustomerAddress as ca on ca.CustomerID = c.CustomerID
join #BaseAddress as a on ca.AddressID = a.AddressID
where c.CustomerID in (
    '29485',
    '29492',
    '29489'
)
-- 3 rows

-- update company name
update c
set CompanyName = 'TSP'
from #BaseCustomer as c
join #tempData as td on td.CustomerID = c.CustomerID
-- 3 rows

-- update address and city
update a
set AddressLine1 = '138 Cecil Street', City = 'Singapore'
from #BaseAddress as a
join #tempData as td on td.AddressID = a.AddressID
-- 3 rows

commit transaction tran1
end try

begin catch
rollback transaction tran1
end catch


/*
4. Run the check query again to check that data is edited - same as 2.
*/
select
    c.CustomerID
    ,c.FirstName
    ,c.MiddleName
    ,c.LastName
    ,c.CompanyName
    ,a.AddressLine1
    ,a.City
from #BaseCustomer as c
join #BaseCustomerAddress as ca on ca.CustomerID = c.CustomerID
join #BaseAddress as a on ca.AddressID = a.AddressID
where c.CustomerID in (
    '29485',
    '29492',
    '29489'
)

1. Prepare Base Temp Tables#

You can select the entire table into a temp table like so:

select *
into #BaseCustomer
from [SalesLT].[Customer]

You can think of it like ‘copying certain parts of the db that you will be editing’. If you have too much data in the table then you can filter it and insert the remaining into the temp table. But this might introduce some risk.

What is the difference between #temptable and @temptable in Sql Server?#

Source: Read it here

#temptable persists within the db connection (open tab within the same script). It is also stored on disk.

@temptable is a local variable that needs to be declared. This means that if you try to run a portion of a script that uses local variables but you did not highlight and include the declared portion when running the script, you will run into errors becuase it is not declared.

Note: In other words, once you have created temp tables with #, it lives within the tab. If you try and create it again you will get an error. If you don’t want it anymore, you can run something like drop table #BaseCustomer or simply close the tab and open a new one.

2, 4. Check and backup data to csv#

Prepare select statements that show you all the data that will be altered and whatever that you need to backup.

After running the query, you can select all the results, right click and save to csv somewhere.

You can skip this step if you are confident but this is just to ensure that in the event something goes wrong with the patch, you have the previous data to refer to and re-update it back to its previous state if necessary.

3. Patching Script#

Wrap the patching script in a transaction:

begin transaction tran1
begin try

... insert patch here

commit transaction tran1
end try

begin catch
rollback transaction tran1
end catch

In the event something goes wrong, this helps to prevent the transaction from being committed fully.

Note: You must highlight and run the entire block in a single go.

3.5 Contents of the Patching Script#

This is just a suggestion but it is a good idea to write a query and insert it into a temp table. We can then use this later on to join base tables for update statements and we don’t have to worry about the filtered data becuase it was already filtered into the temp table.

select
	c.CustomerID
	,a.AddressID
into #tempData
from #BaseCustomer as c
join #BaseCustomerAddress as ca on ca.CustomerID = c.CustomerID
join #BaseAddress as a on ca.AddressID = a.AddressID
where c.CustomerID in (
	'29485',
	'29492',
	'29489'
)
-- 3 rows

-- update company name
update c
set CompanyName = 'TSP'
from #BaseCustomer as c
join #tempData as td on td.CustomerID = c.CustomerID
-- 3 rows

Note: this #tempData is not the base table defined in Part 1. This will be used in the actual script.

5. Converting Simulation Script to Actual Script#

To convert this script into the actual patch script, all we need to do is to Ctrl + f, replace the #BaseTables with the actual tables:

Add base temporary tables for all the tables that you are going to update or insert based on the 2nd step above. For e.g, if you are going to insert an application into Applcation.LeapApplications and update the Equipment.Equipments table, then you should include the preparation of the base temporary tables like so:

Then, remove the temp tables in part 1 above.

Troubleshooting / FAQ

Why is my data not altered in the temp tables?#

Check that your select query is using the temp tables and whether or not you have altered the correct temp tables.

Why are operations relating the temp tables taking a long time?#

They are not indexed and with large amounts of data, it will naturally be slower. Just wait it out unless you can’t then you might need to find a way to simulate this. Maybe you can consider reducing the amount of data you have loaded into the temp tables if you are certain some data can definitely be omitted.

I screwed up during the patch preparation - what to do now?#

If you screwed it up using temp tables, that is totally fine becuase that is the point of using them in the first place. You have 2 options:

1. open a new tab, copy paste the entire script over to the new tab and restart the process
2. run an sql command drop table #temptable and re-run the temp table creation script again

It is advised to do step 1. For more on how #temptable works, read the following sources stated in the rest of the article below.