MS SQL Server Distributed Partitioned Views

This article will explore the use of Distributed Partitioned Views for accessing multiple MS SQL Severs when configured as a Federated Database.

Introduction

When performance gains are needed on very large databases, and your stored procedures are already optimized, and the application is already n-tiered out, and your hardware is already upgraded, its time for distributing the database work over several servers. For SQL Server, this is done by horizontally partitioning large tables over multiple servers. If we think of splitting one table with many columns, into several tables of only a few columns, as Vertical Partitioning, then Horizontal Partitioning is the act of taking one table with many rows, and splitting it into many tables with only a few rows. If these new smaller tables are placed on different servers, it is called a Federated Database configuration. The word Federated is used because all the servers involved may cooperate to balance the processing load. They will act as one federation. Once your data is split among several servers, a new type of statement is needed for retrieving records. These new statements are called Distributed Partitioned Views. They use standard SQL statements, along with the key word UNION, to pull data from all the distributed servers. DML statements (INSERT, UPDATE, and DELETE) can also be used when just a few special rules are observed on the underlying tables. While performance gains are going to vary from application to application, gains of 20% to 30% seem common.

There are three main configuration tasks. Start with attaching all of the severs to each other via Linked Servers, followed by creating matching tables on each server, and finally writing the new views.

In this example, we will distribute the SQL pubs "Authors" table across two different servers. The rules and procedures are identical regardless of the number of servers involved.

Linked Servers

For a detailed explanation of linked servers see previous article Linked Servers PART1.

The first step in creating the federation is to link all of the servers involved together. Start Query Analyzer as "sa," run from the first server. This code will link the second server to the first with an alias of "server2."

USE master
GO

EXEC sp_addlinkedserver
	@server = 'server2',
	@srvproduct = 'SQLServer OLEDB Provider',
	@provider = 'SQLOLEDB',
	@datasrc = 'InfoNet'

The @server variable is our alias. The @datasrc is the actual Sql server name. If there was more than one instance on the server, ServerName\InstanceName would have been used.

If the first server will be running all the views, and your Windows login has the appropriate rights to both machines, then special logins are not needed. However, if a different client will be executing the views, then login mappings may be needed. See Linked Servers PART3 and http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q238/4/77.asp&NoWebContent=1 for detailed explanations of linked server login options and client problems. We will assume remote clients may be wanted in the future, so a linked server login, mapping the local "sa" account to remote "sa" account, is being used as an example. For production, create a new login rather than expose "sa."

EXEC sp_addlinkedsrvlogin 'server2', 'false', 'sa', 'sa', 'secret'

To test the link, execute:

SELECT *
FROM server2.pubs.dbo.authors

All of the rows from Authors should be returned. We now need to repeat the same from server 2. This will create a link back to server1. Everything is the same except for the server name and alias. Login to the second server's Query Analyzer as "sa" and run:

USE master
GO

EXEC sp_addlinkedserver
	@server = 'server1',
	@srvproduct = 'SQLServer OLEDB Provider',
	@provider = 'SQLOLEDB',
	@datasrc = 'dons13'
GO

EXEC sp_addlinkedsrvlogin 'server1', 'false', 'sa', 'sa', 'secret'
GO

SELECT *
FROM server1.pubs.dbo.authors

If there were additional servers involved, each would require links to every other. The same type of design we had with NT multi-Domain security trusts. If there were four servers in our federation, it would have this landscape:

Server1 has a linked server to Server2, Server3, and Server4.

Server2 has a linked server to Server1, Server3, and Server4.

Server3 has a linked server to Server1, Server2, and Server4.

Server4 has a linked server to Server1, Server2, and Server3.

There is not an automated way to either create, or test these reciprocal links.