Database name as a parameter
Niall
Posts: 36 Bronze 1
I appreciate the facility to ignore the Database name when object naming includes it in synonyms, but I wonder if you might take it one step further for me?
We have a number of instances on the same server of databases that are grouped together, ie dev, systest and uat environemnts that may include say a warehouse and source. These would be clled dev_warehouse, dev_source for development and uat_warehouse, uat_source for the uat environment and so on. To reduce code complexity and make for cleaner code the ETL code in warhouse uses synonyms to the source database(s) so in the ETL proc the merge would use (on dev) dev_source.table as the source. At the moment when I ship across the new code using sql compare the synonyms in uat get created pointing to dev. It is not too much of a pain to go in and edit these (using the Object Details view on them all then selecting them all, right clicking and creating a drop - create script and using search and replace) however if I or another team member forgets to do this it tend to result in data being cross populated between environments.
What would really help in terms of release quality is if in SQL Compare I ahve the facility to substitute database anmes when included in synonyms, ie replace dev_source with uat_source, doing it via a pattern match would be even better, ie replace dev% with uat_%.
We have a number of instances on the same server of databases that are grouped together, ie dev, systest and uat environemnts that may include say a warehouse and source. These would be clled dev_warehouse, dev_source for development and uat_warehouse, uat_source for the uat environment and so on. To reduce code complexity and make for cleaner code the ETL code in warhouse uses synonyms to the source database(s) so in the ETL proc the merge would use (on dev) dev_source.table as the source. At the moment when I ship across the new code using sql compare the synonyms in uat get created pointing to dev. It is not too much of a pain to go in and edit these (using the Object Details view on them all then selecting them all, right clicking and creating a drop - create script and using search and replace) however if I or another team member forgets to do this it tend to result in data being cross populated between environments.
What would really help in terms of release quality is if in SQL Compare I ahve the facility to substitute database anmes when included in synonyms, ie replace dev_source with uat_source, doing it via a pattern match would be even better, ie replace dev% with uat_%.
Comments
Is this more or less what you had in mind? Can I ask if you're using SQL Source Control or not?
David Atkinson
Product Manager
Red Gate Software
Product Manager
Redgate Software