SQL Server Database Aliases

Please let Microsoft know that you think this is an important feature request.

MS Connect – Expand synonym to other entities (database, linked server)

You can do so using the link above. You must be logged into your Microsoft Live account, then just click on the up arrow. Feel free to leave a helpful comment, if you like, but please do be civil. Positive reinforcement almost always works better than negative.

The Issue

The issue is that we currently are not able to abstract databases without jumping through hoops. We are able to abstract servers using linked servers and objects within the database using synonyms. But, without the ability to abstract a database, we must remember to change the database name within our scripts (stored procedures, functions, views, synonyms, etc.) from environment to environment. For developers and DBAs who manage hundreds or even thousands of databases with tens of thousands of objects, this becomes a major undertaking which is prone to error and nearly unmanageable.

There are workarounds, one of which I’ve include below, but please take the time to let Microsoft know that this is an important feature request by visiting the link above.

A Workaround

This is probably not the only workaround, but it is simple and effective.

The goal is to build an environment for development that is completely segregated from our production, UAT, and any other user environment and, when development is complete, have the ability to package up our unmodified scripts and deploy them to our user environments.

The first step is to create linked servers named the same as those used in the user environment but linked to development servers. This is easily accomplished using the linked server dialog in SSMS for separate servers. However, I prefer to have all of the databases I am developing in on the same server. So, I create linked servers on my development server that point back to my development server like so…

EXEC sp_addlinkedserver
    @server     = 'MySqlServer',
    @srvproduct     = '',
    @provider   = 'SQLNCLI',
    @datasrc    = NULL,
    @location   = NULL,
    @provstr    = 'DRIVER={SQL Server};Server=(local); Initial Catalog=master;Integrated Security=SSPI;'

I’ve heard that this is not supported and may result in transaction issues, but this is only a development environment, so I’m okay with that. If you’re not, then build separate servers to mirror your user environment.

The next step is to build an abstraction database. That is, a database intended solely for redirecting requests to their intended server/database. This database will need to exist in all of your environments and be named the same. Finally create synonyms in your abstraction database for objects used by your scripts. The abstraction database in your development environment will direct requests to development resources while the abstraction database in your user environments direct requests to the appropriate resources in their environments.

Using this workaround allows all of your scripts to be coded the same regardless of what environment they are in. This eliminates the need to modify scripts for deployment and makes deployment from source control much easier.

If you have any other suggestions, please leave a comment.

<>< Peace ><>

One Response to SQL Server Database Aliases

  1. If you are using a development framework such as Entity Framework in .NET, Django in Python, or Active Record in Ruby you have an abstraction layer in place that lets you version the database as code. Commonly these are call migrations or migration layers.