Multi database support - commit changes from a list of DBs
Lxocram
Posts: 25 Bronze 3
We have multible databases under source control.(some main-db's and some more modular/auxiliary "side"-databases). They're on the same server.
Each has their own repository. They're not part of one big project but they are however somewhat interdependent(cross-database references).
We try to use local copy-objects to refer to the remote database (i call them "portal"- or "gateway"- views/functions/sprocs) wich helps in tracking down dependencies and if there's a schema change (e.g. column rename) in the other DB we can mostly quickfix by modifying that local database object and later refactor.
We use schema's to distinguish the different remote databases (wich also gives us extra security control (GRANT ON SCHEMA))
e.g. CREATE SCHEMA DB2; CREATE VIEW DB2.TABLE as select * from DB2.dbo.TABLE
If the database were to be on another server we'd make a server alias/linked server e.g. SERVER.DOMAIN.COM->SERVERALIAS so we can reference the table as CREATE VIEW DB2.TABLE as select * from SERVERALIAS.DB2.dbo.TABLE
I've seen suggestions made in the ReadyRoll forum viewtopic.php?f=199&t=79564 to use synonyms
Pro's/Con's using that approach? :?:
Now for the actual question:
In SSMS we can only manage 1 database at a time using the SQL Source Control interface (interactively).
:?: How to go about committing/getting latest from sc for a list of databases? :?:
Is there a command line interface for SQL Source Control which we can script?
Each has their own repository. They're not part of one big project but they are however somewhat interdependent(cross-database references).
We try to use local copy-objects to refer to the remote database (i call them "portal"- or "gateway"- views/functions/sprocs) wich helps in tracking down dependencies and if there's a schema change (e.g. column rename) in the other DB we can mostly quickfix by modifying that local database object and later refactor.
We use schema's to distinguish the different remote databases (wich also gives us extra security control (GRANT ON SCHEMA))
e.g. CREATE SCHEMA DB2; CREATE VIEW DB2.TABLE as select * from DB2.dbo.TABLE
If the database were to be on another server we'd make a server alias/linked server e.g. SERVER.DOMAIN.COM->SERVERALIAS so we can reference the table as CREATE VIEW DB2.TABLE as select * from SERVERALIAS.DB2.dbo.TABLE
I've seen suggestions made in the ReadyRoll forum viewtopic.php?f=199&t=79564 to use synonyms
Pro's/Con's using that approach? :?:
Now for the actual question:
In SSMS we can only manage 1 database at a time using the SQL Source Control interface (interactively).
:?: How to go about committing/getting latest from sc for a list of databases? :?:
Is there a command line interface for SQL Source Control which we can script?
Comments
Thank you for your forum post.
Correct with SQL Source Control you can only manage 1 database at a time, there is no feature to perform a commit / get latest for a list of databases. If you would like to see such a feature to perform a commit / get latest for a list of databases, please post a feature suggestion on the SQL Source Control User Voice Forum available via this LINK.
There is no command line for SQL Source Control. It is a plug-in to SSMS and therefore requires SSMS to be installed before you can install and then use SQL Source Control.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com