Disabling comparisons for fully qualified database names
ElliotRodriguez
Posts: 37
We have an environment where developers put development and test databases on a single server.
We have a number of ways that development and test databases are set up right now. For example, if we just want objects and no data, or some data later, we run scripts to create objects and later use SQL Data Compare to add selected table data. Or, we simply stand it up from a production backup and reconcile with SQL Source Control.
This works fine between development and test, because the databases are all under a single server. However, in production, these databases live across a number of linked servers. So procedures in our production databases contain fully-qualified names to other linked servers.
For example:
Is it possible to have SQL Source Control ignore the fully qualified name in favor of just the database.schema.object comparison? Even if we added development and test servers for those linked servers, there will still be a diff because the linked server name will not match its production counterpart.
Setting the filter isnt a realistic option for us, as there are many objects that reference these linked servers.
Is this possible? If not, is there a useful workaround?
We have a number of ways that development and test databases are set up right now. For example, if we just want objects and no data, or some data later, we run scripts to create objects and later use SQL Data Compare to add selected table data. Or, we simply stand it up from a production backup and reconcile with SQL Source Control.
This works fine between development and test, because the databases are all under a single server. However, in production, these databases live across a number of linked servers. So procedures in our production databases contain fully-qualified names to other linked servers.
For example:
SELECT foo FROM LINKEDSERVER.Database.BobSchema.Tableis in a production procedure, while in development and test it should look like
SELECT foo FROM Database.BobSchema.Table. We end up removing these when we import via script (because they fail) but when we stand up from a backup nothing is compiled so these dont cause a problem. We prefer doing this via script though.
Is it possible to have SQL Source Control ignore the fully qualified name in favor of just the database.schema.object comparison? Even if we added development and test servers for those linked servers, there will still be a diff because the linked server name will not match its production counterpart.
Setting the filter isnt a realistic option for us, as there are many objects that reference these linked servers.
Is this possible? If not, is there a useful workaround?
Comments
Thanks for contacting us and sorry you are having this issue!
The best way to do this would be to create Synonyms for all of the linked servers, and have that across environments. There is an option in SQL Compare that ignores database and server names in synonyms, which would allow you to painlessly deploy across environments. http://documentation.red-gate.com/displ ... ct+options
I have followed up with you via email to resolve this.
Warmly,
Andrew Pierce
Technical Sales Engineer
Redgate Software
For one, the synonym must be named identically among all systems. For example, my local dev database would have a synonym (lets call it 'DBSynonym' which would point to the full local path to another separate database on the same local dev server (so it points to, say, 'elliotrodriguez.MyDatabase.MySchema.MyObject).
This is fine, but I then have to instruct other developers to create their own local synonyms of the same name that point to their own local instances:
Then we have to tell SQL SC to ignore this using filters and thats where things fall apart.
In the past I have complained about filter performance on our SQL Server 2008 R2 instances. They have seldom worked with SVN and have generally been a pain. For example, users can create a filter, but modifying it afterwards is completely ineffective and we end up having to unlink and relink the database from source control because even when we try to fix what is obviously broken, the filter never seems to update. Instead it always seems to exclude all objects for whatever reason.
The same happened to us again using Mercurial. I tried to ignore the synonym using a filter, and the filter also managed to exclude the objects I modified that included the synonym (even though I explicitly selected just the synonym). And even after removing the filter rules, we could not access the modified objects until I unlinked and relinked.
I can reproduce this reliably and have attached a video. We have had ongoing filter issues that have resulted in many tickets submitted, but we have never managed to get them working.
Assuming the workflow I described is correct for this kind of situation, what do we have to do to get the filters working correctly?
http://screencast.com/t/xL3X8U0P
Sorry for not catching your forum posts!
I will post the solution we have been working on via email here (for other users to see).
Source Control (and Compare) uses the "Ignore database and server names in Synonyms" option by default. So any Synonym committed to the repository will not take the database and server name with it.
This allows multiple users in multiple environments to have different db/server names set up for their Synonyms.
You can check out the Source Control Comparison Options for more info or go to our documentation here http://documentation.red-gate.com/display/SC11/Setting+project+options
It has been great working with you!
Technical Sales Engineer
Redgate Software