Another type of database federation is what I call the full replication federation. This is where you place all the dimension tables (sticking with our data warehouse example from last time) on all servers of the federation. In addition to having the dimension tables on all the servers in the federation, we also allow all the users to connect to all the servers in the federation. This effectively creates an Active/Active solution as users should be connecting to the SQL Servers through a load balancer. As the dimensions are going to be read only as far as the users are concerned it doesn’t matter which server they connect to.
I call this the full replication federation as we setup replication on all tables which aren’t our large table which has been federated.
As we are connecting to all the servers, we can’t have our view and table named the same thing. In this case we want to have our actual table and view with different names. I prefer to simply use a different schema to hide the table where I want it. This changes our view to look more like this (using a three server federation).
CREATE VIEW dbo.FACT_Sales AS
SELECT *
FROM SQL0.MyDataWarehouse.Data.FACT_Sales
UNION ALL
SELECT *
FROM SQL1.MyDataWarehouse.Data.FACT_Sales
UNION ALL
SELECT *
FROM SQL2.MyDataWarehouse.Data.FACT_Sales
GO
I like to put the local database name in the view script, so that the same script can be easily deployed to each server. You can at your discretion remove the local server and database name.
You can now query the Data.FACT_Sales table on all three servers by simply querying the view on the local table.
You may end up with some of the same “interesting” optimizer query plans as when using the Pyramid federation technique, and the same solutions which we discussed in the “The Pyramid Federation ” post will still apply.
Denny
