synonyms and deployment.

spakh

I have synonyms defined in my database that differ in their definitions depending on which server the database is installed. For example, on my dev and test servers, one of the synonym is defined as:

CREATE SYNONYM [dbo].[SYN_TMAGENT] FOR [TPSSYNC].[ANDRXTST].[AGID06USR].[TMAGENT]

On the production server the synonym is dfined as:

CREATE SYNONYM [dbo].[SYN_TMAGENT] FOR [TPSSYNC].[TPS].[AGID06USR].[TMAGENT]

When my developers sync with source control on their development systems they need to have the first definition. When deploying changes to the test server, the same definition is used as in the dev server. When database changes are deployed to production, the synonym must be defined by the latter definition.

How can I set this up using SQL Source Control where this is done without manual intervention.

David Atkinson

What is needed here is variable substitution, where you define a variable instead of the explicit value for the synonym. Depending on whether you're deploying to dev, test or production, the variable should be substituted accordingly.

Unfortunately this feature doesn't exist right now, but I think it would be great if you could post it here:

http://redgate.uservoice.com/forums/390 ... ce-control

This way others can vote it up and hopefully we'll be able to justify implementing it. In the meantime I guess the best bet is to run a post deployment script that corrects these differences. It's not ideal, but I can't think of a better option.

David Atkinson
Red Gate

spakh

I am not clear on the variable substitution part. Does Source Control have a variable substitution feature? Or are you suggeting to assign the four part synonym definition to a variable inside a stored procedure? A working example would help. Thank you.

David Atkinson

Sorry, maybe I wasn't clear enough. I was saying that there's no simple way of solving this. I was suggesting that you submit this request at the UserVoice site linked in my previous post.

If you have any alternative suggestions on how you would like for us to solve this, we'd be interested in hearing your ideas.

David

spakh

Thanks for your input David.

I added the suggestion to the uservoice forum.

http://redgate.uservoice.com/forums/390 ... ?ref=title

My thinking was to have the source control plug-in somehow provide a mechanism to select a different object script based on target server, and manage the underlying source code for each defintion seamlessly. Maybe this is better addressed in a CI type of environment where other tools provide this functionality. I am not sure at this time.

In the meantime I will look at the new Migration Scripts feature of Source Control and see if that provides a satisfactory solution.

David Atkinson

I'm not sure migration scripts will work. Your best is to run a script post-deployment to replace the dev/test synonyms with the correct ones for your production environment.

Is that something that would work for you?

David

spakh

We currently put production definition of the synonyms in SVN source control and have a separate script that we run on dev and test machines after updating the dev and test databases from SVN. This way we do not accidentally promote dev or test synonym definitions into production. This process can cause issues if a developer by accident checks in the development version of the synonym by accident if the checkmark is not deselected during the checkin process to SVN. This will not get caught in test environment since the test environment uses the same definition as the dev environment. Only when the script is promoted to production will the problems appear. So we have to double check our synonyms on production servers and in SVN to make sure nobody checked in the wrong definition into SVN. This also forces us to continue to put together update script files to give to our DBA's to update the prod database. DBAs do not conern themselves with what is in the script. They just apply them. If the script breaks, then the test process is improved to make sure that does not happen again. In this case the test process must include the check of synonym definitions in source control. Which is not what the test team should be doing.

So, I was hoping for the SQL Source Control to provide mechanism where different synonym definitions can be assigned to the same object and extract the appropriate one depending on the target environment. Maybe there is a better solution. Possibly through CI (continuous integration). We currently do not have CI setup for our database changes, but it is on the wish list .

StephanieHerr

This process can cause issues if a developer by accident checks in the development version of the synonym by accident if the checkmark is not deselected during the checkin process to SVN.

Have you tried using SQL Source Control's filter feature so that synonyms don't accidentally get checked into SVN? Right click on the db in the Object Explorer and then select Other SQL Source Control tasks > Edit filter rules... Then, uncheck synonyms on the left and save and close. Now, the synonyms should not show up on the commit list anymore so developers don't have to remember to deselect them every time they commit.

Make sure you commit the filter configuration so other developers stop seeing the synonyms on their commit lists too.

I hope this helps!