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?
Hi
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?