Product/Solution Question
richardgreenwell
Posts: 2
I've been looking at Packager and Compare, and wanted to ask for some advice on using them (or other tools) to make our database deployment easier.
Our application is built as a core application, but then has a DB instance per client. Each client has their own overridden bits of the application, from a code and database perspective, as well as their own distinct functionality.
When we deploy a new version of the product, we have changes to deploy from 2 places.
The first place is the core product. These are stored procedures, schema, and data (less data generally, mostly proc changes, and a moderate amount of schema).
These have to be deployed to the client databases first.
Then we have the changes to client specific schema, procs and data. These are deployed afterwards. As the client may also have overridden versions of core stored procs (yes, there is some business logic in some of our procs
), then those have to deployed to the client database regardless of whether there were any changes, as the core product deployment would have overwritten them.
Our issues arise as we try and promote these product releases through our environments, and I wondered if anyone had any suggestions on how we would use the RedGate suite of tools to make this easier, reliable, and eventually automated.
Thank you
Our application is built as a core application, but then has a DB instance per client. Each client has their own overridden bits of the application, from a code and database perspective, as well as their own distinct functionality.
When we deploy a new version of the product, we have changes to deploy from 2 places.
The first place is the core product. These are stored procedures, schema, and data (less data generally, mostly proc changes, and a moderate amount of schema).
These have to be deployed to the client databases first.
Then we have the changes to client specific schema, procs and data. These are deployed afterwards. As the client may also have overridden versions of core stored procs (yes, there is some business logic in some of our procs
), then those have to deployed to the client database regardless of whether there were any changes, as the core product deployment would have overwritten them.Our issues arise as we try and promote these product releases through our environments, and I wondered if anyone had any suggestions on how we would use the RedGate suite of tools to make this easier, reliable, and eventually automated.
Thank you
Comments
Any solution using Red Gate tools will most certainly involve writing custom code using the comparison SDK. Because Packager's output is static SQL code, it cannot dynamically adjust the schema updates if the customer's schema is different from the reference schema used to create an upgrade package.
The SDK will allow you to dynamically compare the schema on-site, generate the script, and run it.
You may also be able to use the command line versions of SQL and Data Compare that come with the professional edition, but you would have less flexibility.
Dynamic data updates are still pretty inflexible, though. If you compare to a backup on-site to get the data in, you may need some complex filtering to aviod overwriting your customer data.
I hope this helps.