Sql-server – Synonym/Alias equivalent for a Database

sql serversql-server-2008-r2synonyms

If we have code in an SP Database1 then queries Database2 (on the same server) we want the same code to work on the databases Database1Dev and Database2Dev. But this currently means editing the full SP each time we push to Live.

We want a single of line of code such as

select col1, col2 from databaseName..ourTable

Where databaseName is somehow set at a server level.

I understand synonyms are the wrong solution for this. Is there something similar we can use that doesn't include writing a synonym for every single object?

Best Answer

You can create a synonym to the object, however you cannot currently create a synonym to a database. This is a popular request on Connect:

So, as you've stated in your question, you'd need to run CREATE SYNONYM for each object affected. Like this, in the dev instance:

CREATE SYNONYM MyObjectSyn
FOR Database1Dev.dbo.MyObject;

And like this in the prod instance:

CREATE SYNONYM MyObjectSyn
FOR Database1.dbo.MyObject;

Then the stored procedure code references MyObjectSyn instead.

Another way to get around this problem, that I don't recommend, is to use a linked server connected to each database. The linked server can be repointed on dev to Database1Dev, and Database1 on prod.

Or, as Scott Hodgin suggested: use dynamic SQL and a configuration table on each server that would 'map' <database1> to a specific database on that server. The stored procedure would read the configuration table and REPLACE <database1> with the name retrieved from the configuration table.

Related Solutions

Sql-server – What do these statements mean in the MS β exam 70-461 “skills measured” list

DML - refers to data manipulation (update, insert, delete). A DML trigger is a trigger created to happen on update, insert or delete of table data.

The update function is the part of a DML trigger where you test if a required update has occured.

For Example:

CREATE TRIGGER myupdate_trigger
ON mytable
FOR UPDATE AS
if UPDATE(column_1)
BEGIN
-- Do something
END

This will trigger a further action every time column column_1 is updated in table mytable. The update function is UPDATE(column_1).

Sql-server – How to script out push subscription creation at the subscriber

I'm going to outline a T-SQL way that uses most of what you already have.

Create a table to hold your publication information (for the solution that I have written, it's just the name of the publication and an identity column)
Create a table to hold your subscriber information (in mine, I have name of subscriber, subscriber database, identity column, and a column that references back to the publication)
When you want to create a new subscription, you put a new row in the subscriber table with the relevant information
In your "add subscription" script, do a left join between your table and syssubscriptions to find out what subscriptions are in your table but don't yet exist. Run a cursor over that info and for each subscription that doesn't exist, create it.
After you're done adding all of the subscriptions, call sp_startpublication_snapshot for each publication that has uninitialized subscribers.
Fin (aka "you're done").

Best Answer

Related Solutions

Sql-server – What do these statements mean in the MS β exam 70-461 “skills measured” list

Sql-server – How to script out push subscription creation at the subscriber

Related Question