Introduction

An increasing number of applications require access to multiple databases located at different sites, perhaps widely separated geographically.

A common business scenario is a retailer with a network of warehouses spread across the country in order to speed delivery of its products. A distributed database system allows applications to access database local and remote transparently.

When data might be distributed? Why not gather all data of the enterprise in one central site? There are answers to these questions:

• Data might be distributed in order to balance the workload.
• Data might be on the site where it was created for maintenance and security purposes.
• A client sends query to the closest database in order to minimized communication costs and increase throughput.
• In order to increase data availability in the event of system crashes.

It is remarkable to say that there are two main approaches to distributed data: replication and distribution of queries. In this article, I will cover on distribution of queries where each system manages a single copy of data, and uses distributed transaction mechanisms to access local and remote data. In this case, the distributed database looks like a collection of individual databases with its own schema. If the database systems are from different vendors, the system is referred to as heterogeneous, otherwise homogeneous.

Implementing distributed databases using SQL Server 2005

Linked servers provide SQL Server the ability to access data from remote data sources. Using these mechanisms, you can issue queries, perform data modifications and execute remote procedures. You can use the T-SQL function OPENROWSET to query a remote data source without a linked server.

Let us now create a linked server to another SQL Server instance using the T-SQL procedure sp_addlinkedserver. The syntax of sp_addlinkedserver is shown in Listing 1:

Listing 1

And each parameter is described in table 1.

Table 1.

You can configure the linked server properties such as collection compatible, collation name, connect timeout and so on (for more information refer to the SQL Server online help) using the stored procedure sp_serveroption.

When executing a distributed query against a linked server, SQL Server maps your local login and credentials to the linked server. Based on the security on the remote data source, your credentials are accepted or rejected. By default, when a linked server is created, your local credentials are used to access data on the remote site. If you do not have the proper permission on the remote site, then your connection attempt is rejected. You can set up the mapping from your local login to any existing remote login using the stored procedure sp_addlinkedsrvlogin.

The syntax of this procedure is shown in Listing 2.

Listing 2.

The parameters of this procedure are described in the Table 2. 

Table 2.

Let us create and configure a linked server from one instance of SQL Server 2005 to one instance of SQL Server 2000 in order to execute queries on the sample database Northwind. The local and remote login mapping is done using Integrated Security associated to the current logged user. The code in TSQL is illustrated in Listing 2.

Listing 3.

Now let us execute some queries on the remote instance, specifically on the database Northwind. You can issue distributed queries referring to linked server name by using the following syntax as shown in Listing 3.

[linked_server_name].[catalog].[schema].[object_name]

Listing 3.

Let us query the remote table Categories in the Northwind database using the connection provided by the linked server as shown in Listing 4.

select * from REMOTE_NORTHWIND.Northwind.dbo.Categories

Listing 4.

As you can see you have long name to refer to remote database object. SQL Server 2005 provides a mechanism named synonym to referring to long name. Let us create a synonym to the remote table as shown in Listing 5.

create synonym dbo.remote_northwind_categories for REMOTE_NORTHWIND.Northwind.dbo.Categories;

Listing 5.

Now the query in the Listing 4 is transformed into the query shown in Listing 6.

select * from dbo.remote_northwind_categories

Listing 6.

Conclusion

In this article I covered the main approach of distributed database and how it is realized using SQL Server 2005 systems.