Friday, December 12, 2014

MSSQL: Synonyms, Post Deployment scripts and Database Project.

MSSQL Synonyms allow to create proxy tables in one database for another database so we can query tables from both databases and even make joins between them. Obviously it can be done via cross-database queries, however sometimes second database is on the remote server and it is useful to have synonym for it in your database.

Lets say we have the following databases

In this case DB2 and DB1 is on the same server, but lets imagine that DB2 is on remote server. Our goal is to create synonyms for DB2 in DB1. One more condition is that we already have Database Project for DB1 and we want to create synonyms in PostBuild script.

First of all lets create a Database Project

By default it is empty, in order to associate it with database right click the project and choose "Schema compare…" which allows to compare target database with project and import schema if necessary.

Now we need to add "Scripts" folder with "PostDeployment" folder inside it. The amount of post deploy scripts is limited to one, it means that only one script file in this folder can have Build Action set to PostDeploy. However, this file can serve as an entry point where you can reference other script files.

Now we setup SQLCMD Variables in DB project properties. Those will be used in post deployment script in order to create synonyms.

To create synonyms we have to create linked server and user credentials for this server. Make sure, that this user exists on remote server and has necessary permissions.

But first of all lets make a new schema for synonym tables in DB1.

use DB1
go
create schema [DB2] authorization [dbo];
go

If you use "compare schema" once more now new schema [DB2] will be imported in database project as you can see it on the screenshot in the Security folder. This schema will help to distinguish tables of the local DB1 database and the remote DB2 database. It is useful if both databases have tables with same names.

Now lets add some post deployment scripts. We need 3: one to link the remote server, one to create synonyms, and one script as an entry point where we will include other two scripts.

In the Index.sql file properties set Build Action to PostDeploy. And the content of this file will be the following:

:r .\LinkRemoteServer.sql
GO
:r .\CreateSynonyms.sql
GO

LinkRemoteServer.sql script will use SQLCMD variables:

begin
 if not exists(select * from sys.servers where name = N'$(RemoteServer)')
  -- link remote server
  exec sp_addlinkedserver @server = N'$(RemoteServer)'

 if not exists(select * from sys.linked_logins where remote_name = N'$(RemoteLogin)')
  -- create user for remote server
  exec sp_addlinkedsrvlogin N'$(RemoteServer)', 'false', NULL, N'$(RemoteLogin)', N'$(RemotePassword)'
end

It will link remote server if it does not exist yet.

And for CreateSynonyms.sql script it will look like this:

BEGIN TRANSACTION;

 declare @dbname nvarchar(200);

 set @dbname = '[$(RemoteServer)].[$(RemoteDbName)]'

 -- create synonym tables
 create table #tables (
  tableid int identity(1,1) not null primary key clustered,
  table_name sysname
 )

 declare
 @sql nvarchar(4000),
 @id int

 set @sql = N'select table_name from ' + @dbname +'.information_schema.tables where table_schema = ''dbo'''
 
 insert #tables exec sp_executesql @sql, N'@tableschema sysname', 'dbo'
 select @id = max(tableid) from #tables
 while @id > 0 begin
  select @sql = 'create SYNONYM [DB2].[' + table_name + '] for ' +@dbname + '.[dbo].[' + table_name +']' from #tables where tableid = @id
  exec sp_executesql @sql
  set @id = @id - 1
 end

COMMIT TRANSACTION;

It’s not the best way, but this script allows to create synonyms for all database tables at once.

All what’s left now is to rebuild database project (it’s important, since it stores previous deploy scripts in debug folder, and we don’t want that) and publish it. Don’t forget to specify DB1 database connection and SQLCMD variables and tick “Always re-create database” checkbox in advanced settings.

After publish is finished refresh DB1 and take a look at the synonyms. Since there is only one table in DB2 only one synonym was created.

That’s it. Now you can write queries to the remote database and to your local database combined. For example:

use DB1
select * from Customers c
join [DB2].[Orders] o on c.CustomerID = o.CustomerID

Download test solution here

No comments :

Post a Comment