SQL Server with Mr. Denny

Sep 22 2008   11:00AM GMT

The full replication federation

Denny Cherry Denny Cherry Profile: Denny Cherry

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

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: