We have been using RedGate SQL Source Control with mixed success on SQL Server 2008 R2. We chronically run into problems with filters and we have another problem that I hope someone else has encountered and can suggest a fix.
Our dev->test->prod deployment stack includes usage of synonyms for linked servers - basically individual synonyms on dev machines that are named identically to synonyms on test databases that are also named the same in our production environments. So in dev, it may look like this:
CREATE SYNONYM [dbo].[mySynonym] FOR [LocalDevDb].[Database].[Schema].[object]
In test/qa:
CREATE SYNONYM [dbo].[mySynonym] FOR [TestServerDb].[Database].[Schema].[object]
and finally, production:
CREATE SYNONYM [dbo].[mySynonym] FOR [ProductionDb].[Database].[Schema].[object]
In order to avoid conflicts arising from the synonyms (because as one can see, the server names are different, but the rest of the full name is the same), we have tried to exclude these from SQL Source Control on commit using filters.
However, doing this has also caused any object using the synonym to be ignored as well. We are working around it now by just not checking in the synonym at all, but obviously this is not sustainable.
Is this by design? Has anyone else seen this filter behavior?
EDIT - this workflow and usage of synonyms in this way was suggested by RG support, as a means of overcoming deployment and syncing issues we had between environments that had full linked server names to objects that were different between environments (or in some cases not needed at all, because a database that lives on a different server in production may be on the same host in dev or test)
Another option is to deploy the synonym along with the dependencies.
By default SQL Source Control uses the "Ignore Server and Database name in Synonyms" option. This allows you to commit the Synonym (and its dependencies) then when other developers pick up the synonym and edit it for their environment- that db name/server change will be ignored by Source Control.
More info here http://documentation.red-gate.com/display/SC11/Setting+project+options