Design: Working Across Databases

I've been working with multiple databases recently in trying to tie a few applications together and share data without using replication, DTS, or some other data movement mechanism. I wanted to keep the data within the databases that it resides and perform some cross database work within applications. In doing so, I realized there were a few ways of doing this and came to some conclusions about the best ways to handle this.

What's the Problem

The big problem is that not all my data is in one database. When I first started working with SQL Server, everything was smaller, less data, less mature, etc. and so most applications on which I worked kept everything in one database. Occasionally we had to send or retrieve data from another server, but we tended to use messaging for that, so there weren't the type of queries that we're now used to with Linked Servers, and other remote query mechanisms. Most servers, PC based ones at least, had only one user database on them.

Now, however, it's more routine with bigger and faster servers to have more than one database, sometimes supporting completely separate applications on the same server. In the case on which I was recently working, there was an Orders database on the same server as a Subscription database. The Orders database was for an e-commerce system and received orders placed in a shopping cart and paid for. The Subscription database handled subscriptions for a periodical and was related to the information in Orders in that a sale for the periodical needed to have the information transferred to fulfill the sale.

Sounds simple and it probably could have been in the same database except the subscriptions were only a portion of the orders and other databases were used to handle other types of orders. Maybe not the best design, but that's another article.

So what's the problem? It's not really a problem per se, but rather a design idea. I tend to write things as push, so in this case, I was looking to have a process in the Orders database push information to the tables in Subscription. The design choice comes into play when looking at how you handle security and structure the cross database query.

Options

When looking to make a cross database query, there are a few options. The most basic method is the fully qualify your table access with the name of the table. In this case, I could access the Subscriber table from the Orders database as follows:

select *
 from Subscription.dbo.subscriber

I could have used the full four part name of servername.database.owner.table in accessing the table, which is something I'd do if I was using a Linked Server to get the data. I didn't because just as the database name is implied if you are querying a table in the same database, the server name is implied when accessing another database on the same server.

I didn't choose this technique, mainly because of the linked server issue. If I were to move this database to it's own server, which could happen if either of these databases or the load on the server grew, then any process that accessed this table would require recording. Not that I'm against the work, but it's easy to miss a function or stored procedure somehow when moving databases. I much prefer to centralize my code changes as much as it makes sense.

For that reason, I chose another method of accessing the table. I created a view, which I could have called subscriber so the access is transparent. However since I like to be aware of when I'm accessing a view, I named it as follows:

create view vSubscriber
as
select first
, last
, startdate
, enddate
, subscriberid
, email
 from subscription.dbo.subscriber

In this way I can develop all code in the Orders database just as if the subscriber or any other table were local. If I move the subscriber database (more likely) or rename it (unlikely), then I only have to change the code in one place: the view.

Security

Since I don't enable cross database ownership chaining, I need to be sure that I fix the security in the database being accessed. That means the user in the Orders database needs to have permissions on not only the vSubscriber view, but also permissions to log into the Subscription database and access the base table. In this case I granted select, insert, and update permissions as the process needs to perform all those functions (no deletes allowed).

Conclusions

There may be other methods of accessing data across databases, but this has proven to be the simplest and easiest for me over time. In some cases we have used this technique for archiving data by renaming the base table, creating a view with the old table name that unions the new base table with an archive table in another database. That was flaky in v6.5, but seems more stable and efficient in SQL Server 2000.

This design technique has been great for read-only access, primarily when creating reports that call on information in multiple systems. I've had entire systems change in that we moved from say a Solomon accounting system to Great Plains. The "type" of information that we needed was the same, but the server, table, even the columns changed names. But by changing the views to match up with the new table and using aliases for the columns, none of our reports or code broke.

If you agree or disagree, please give us some feedback and debate the points using the "your opinion" link below.

©2005 Steve Jones (dkranch.net)