Sql-server – How to better manage hard coded cross db references in stored procs

sql-server-2012

I wanted to make a copy of a db and give it a different name, and have our application connect to this new copy of the db. The only problem is that other databases our application references have stored procs with hard coded references to the original db name. So even though I can make the application connect to the copy, any other databases it connects to will still connect to the original.

I have to believe this is probably a common problem that's been solved, but I'm wondering whether the solution is something I can still do on top of the implementation we have or if it's something that needed to be designed differently to begin with… and if so what is the "proper" design to handle this issue?

Best Answer

Referencing the last last paragraph in your question

I have to believe this is probably a common problem that's been solved, but I'm wondering whether the solution is something I can still do on top of the implementation we have or if it's something that needed to be designed differently to begin with... and if so what is the "proper" design to handle this issue?

Yes, I have seen many posts and blogs about this issue and the recommended approach that I have seen is to use SYNONYMS.

A synonym is like an alias. It provides a way to reference an object like a table, view or stored procedure, but the challenging part is that a SYNONYM can only be defined as a two-part name (schema.object). This limitation would require you to make code changes to your existing objects that reference 3-part names (Database.Schema.Object).

Here is an example of a synonym from the documentation. The following example first creates a synonym for the base object, Product in the AdventureWorks2012 database, and then queries the synonym.

-- Create a synonym for the Product table in AdventureWorks2012.  
CREATE SYNONYM MyProduct  
FOR AdventureWorks2012.Production.Product;  
GO  

-- Query the Product table by using the synonym.  
SELECT ProductID, Name   
FROM MyProduct  
WHERE ProductID < 5;  
GO  

--You can also create a `SYNONYM` for a remote object.
CREATE SYNONYM MyEmployee FOR Server_Remote.AdventureWorks2012.HumanResources.Employee;  

In your particular situation, let's assume you have references to db1.dbo.Customers. You could create a synonym called Db1Dbo.Customers that points to db1.dbo.Customers. Any objects that currently reference db1.dbo.Customers would need to be changed to Db1Dbo.Customers.

Now, you want to make a copy of database db1 and call it db1copy. After creating the db1copy database, you simply drop the existing synonym in database db1copy for DB1Dbo.Customers and recreate it to point to db1copy.dbo.Customers.

There are some 'gotchas' when using SYNONYMS though. Take a look at Aaron Bertrand's post - #BackToBasics : The Beauty of the Synonym. As he says, 'But it's not all roses'. Among the 'gotchas' that Aaron discusses are:

  • They introduce a layer of abstraction.
  • IntelliSense won't always work in certain versions.
  • You can't ignore performance.
  • You can't alter a synonym.
  • You can only point to certain entity types.
  • Permissions can get messy.