One of the things that bugs me about SQL projects is that there are perfectly legitimate scenarios than can be done on a database server than cannot be done in a project. The latest example of this has to do with linked servers.
I have many databases participating in Always-On availability groups, configured so that the secondary is available for read-only querying. I have linked servers pointing to the primary and secondary nodes, so depending on the type of query the client can used one linked server or the other. So for example:
- LINKED_SERVER_NAME points to the primary (read/write) node.
- LINKED_SERVER_NAME_READ_ONLY points to the secondary (read only) node.
Representing this in source control is a problem. I cannot have multiple database references in my SQL project that point to the same dacpac file. When I try, Visual Studio returns the error "This project already contains this database reference". So even though there is no problem implementing this configuration on the actual server, it does not allow me to represent the configuration in the Visual Studio project.
Obviously the databases that reside on the primary and secondary node of the availability group are exact copies, and it would be ridiculous to have 2 instances of the database in source control. The best solution I have thought of is to have a post-build script on the SQL Project of the destination database that would make a copy of the dacpac, and the read-only linked server definition could point to that file. But that is still kind of hack-ey, not to mention that it would be a pain to modify every database project to produce two output files.
Is there a better way?