Archive for September, 2008

Using SYNONYMS in SQL Server for cross database dependencies

September 10, 2008

Whenever possible, I prefer to keep the dependencies for production code minimized and well organized.
I am very fond of using synonyms in SQL Server 2005 to manage all cross database references, this makes it easy both to enumerate and/or change all pointers to a given database.
Here is and example of creating two databases and defining some synonyms from one to the other.

create database orders
create database products
go

use products
go
create schema Catalog
create table Catalog.Offerings
(
ProductId int identity primary key
,ProductTitle nvarchar(128) not null
)
create table Catalog.NewOfferings
(
ProductId int references Catalog.Offerings(ProductId)
,AvailableDate datetime not null
)
create table Catalog.BestSellers
(
ProductId int references Catalog.Offerings(ProductId)
,PopularityRank smallint not null constraint bestSellers_uq_popularityRank unique
)
go

insert Catalog.offerings (productTitle) values ('product 1')
insert Catalog.offerings (productTitle) values ('product 2')
insert Catalog.offerings (productTitle) values ('new product 3')
insert Catalog.newofferings (productId, availableDate) values (3, dateadd(day, 1, getutcdate()))
insert Catalog.bestsellers (productId, popularityRank) values (2,1)
insert Catalog.bestsellers (productId, popularityRank) values (1,2)
go

use orders
go
create schema ProductsCatalog
go
create synonym ProductsCatalog.NewOfferings for Products.Catalog.NewOfferings
create synonym ProductsCatalog.Offerings for Products.Catalog.Offerings
go
select ofr.productId, ofr.productTitle, newofr.availableDate
from ProductsCatalog.NewOfferings as newofr
join ProductsCatalog.Offerings as ofr
on newofr.productId = ofr.productId
go

Now we will assume that a business requirement has appeared that necessitates updating all existing references to Products to now use the new Products09 database. We can script out all synonyms in the orders database with SQL management studio like:
Tasks=>Generate Scripts=>Choose ‘Orders’ db=>choose synonyms object types=>select all

Then we can update these synonym definitions to point to the new Products09 database in one transaction:
BEGIN TRAN
DROP SYNONYM [ProductsCatalog].[NewOfferings];
DROP SYNONYM [ProductsCatalog].[Offerings];

CREATE SYNONYM [ProductsCatalog].[NewOfferings] FOR [Products09].[Catalog].[NewOfferings];
CREATE SYNONYM [ProductsCatalog].[Offerings] FOR [Products09].[Catalog].[Offerings];
COMMIT

You can simulate moving to the new Products09 database by renaming the existing Products DB to Products09. Running the above select query will show that the synonyms are happily referring to the new database and returning the same results as before.

Advertisements