Support multiple Synonym definitions that change across environments
Support multiple SYNONYM definitions depending on which server (dev, test, prod) the source is being extracted to.
-
Simon commented
In our current setup using MS Database Projects, we use sql command variables to achieve this and a master file containing all these variables. This file is changed dependent on the target environment and allows us to change linked server definitions, synonyms etc.
Will SQL Source Control / Deployment Manager support this ? If it could, it would greatly simplify tackling issues like this.
-
Unfortunately, there's no way to do this in SQL Source Control at the moment. If you use SQL Compare for deployment, then you could use "Table Mapping" when setting up the comparison project to map table names that differ across your environments. You could then filter out the synonyms in the deployment so that your dev synonym doesn't overwrite what your test synonym refers to. I would recommend saving projects for dev -> test and test -> prod so that you don't have to set this up each time.
-
Sandor Pakh commented
We use synonyms to abstract table definitions that point to AS/400 systems. Depending on our servers (dev, test, prod) these synonym definitions are different. Using synonyms does NOT force us to maintain different versions of the same stored procedures or queries that differ only in table names because these table names are different between dev, test and prod systems.
ex:
dev AS/400:
CREATE SYNONYM [dbo].[SYN_TMAGENT] FOR [TPSSYNC].[ANDRXTST].[AGID06USR].[TMAGENT]prod AS/400:
CREATE SYNONYM [dbo].[SYN_TMAGENT] FOR [TPSSYNC].[TPS].[AGID06USR].[TMAGENT]You can see that the 4 part definition differs between the two environments.
It would be great if these multiple definitions of sysnonym objects can be stored in source control and an update/get operation would extract the appropriate one depending on the environment that it is being extracted to.