- There are thousands of on-premises servers who are running all different versions of a piece of software including a specific version of a database (all at different companies)
- Out of a central development department regularly new versions or pushed of the software with database updates (new tables, new views, new foreign keys, new inserts for enums or specific tables, new stored procedures, etc)
- These changes come from many different development branches all bringing in their own bits of sql code that affects the schema
- Updating client is handled via a .sql file which verifies the local installed latest database schema release version and the sql code that is annotated as later is ran to update a specific physical machine to a later version (automatically without user involvement)
An idea is now to instead use the EF SSDL description of the specific assembly distributed to bring a database schema (sql server) to the latest version. It would need to compare every table, column, constraint, etc to check if they are equal and if not update them taking into account dependencies.
Question: is there an existing package which would do this? So that e.g. when starting an application it would check the existing physical database versus the SSDL and brings them in sync automagically? It would have to skip database objects that are not in the SSDL since a database could contain tables or views which were added by a specific customer and is not part of the database objects needed by the application or is the current approach the best way to go? (in itself this is another approach than e.g. redgate with comparing the physical database) ?
(the only related question i found here is Purpose of edmgen validation? Comparing SSDL and database schemas?)