In the previous post, I talked about how the federation metadata is formed to let you traverse the whole federation relationship within a database. In part 2 we will take a look at how you can basically go back in time to look at all the changes on federation metadata within the database with the federations. The idea with the *history table is simply to expose all changes to federations through the CREATE, ALTER and DROP commands to let you reconstruct what happened in your database. This is particularly interesting in cases where you want to recover say a federation member or an atomic unit at a point back in time.

Assume an example where tenant_id is the federation key and assume tenant 55, made an error and deleted some data. Assume 2 days ago a member 5 was holding the range of tenants data from 0 to100. Lets call that m#1. Then you split that into 0-50 (m#2) and 50-75(m#3) and 75-100(m#4). And you then dropped 0-50 and now have a new member holding 0-75 (m#5). You’d like to recover from a mistake for tenant_id=55. However, when federation members keep repartitioning, how do you figure out which federation member to restore? You need to know which federation member at the time was holding that data? That is only possible if you have the full history.

*History tables are fairly simple to explain because they mimic the federation metadata we discussed in part-1 exactly except that history tables also contain create_date and drop_date to signify the active period for each instance of the history. if drop_date is NULL, that signifies that the metadata is still active and reflect the current situation.

Here is the basic shape of the schema for the *history system views.

image

 

image

Here are a few common queries;

-- when were my federations created?
select federation_id, name, create_date 
from sys.federation_history 
where drop_date is NULL
go
 
-- Which federation member held id=55 2 minutes ago in 'tenant_federation'?
select dateadd(mi,-2,getutcdate()),f.name,fmdh.* from sys.federation_member_distribution_history fmdh join sys.federation_history f on fmdh.federation_id=f.federation_id where f.name='tenant_federation' AND 55 between cast(range_low as int) and cast(range_high as int) AND ((fmdh.drop_date is not null AND dateadd(mi,-5,getutcdate()) between fmdh.create_date and fmdh.drop_date) OR (fmdh.drop_date is null AND dateadd(mi,-5,getutcdate())>fmdh.create_date) ) GO

This should help get all of you started...Smile 

Enjoy.

-cihan biyikoglu