Support different static data per customer
I have a single schema for all customers but different reference data for different customers. It would be nice if I could manage this all within SQL Source Control. This should also be understood by SQL Compare and my continuous integration system when it comes time to deploy this to my different environments and customers' sites.
AdminStephanie
(Product Manager, Redgate)
shared this idea
Hi all. Thank you for your votes and feedback on this issue over the years. Here is our current guidance for this suggestion:
Post-deployment scripts give you flexibility for static data
With SQL Source Control, you can now use a post-deployment script to “dynamically” deploy static data based on a factor such as @@SERVERNAME or other query-able conditions.
SQL Source Control introduced pre- and post- scripts in v6.3.
An example post-deployment script which shows how to control deployment of static data by environment is here: https://documentation.red-gate.com/soc7/common-tasks/working-with-pre-post-deployment-scripts/static-data
If you make heavy use of Static Data, we have stronger support for this in SQL Change Automation.
SQL Change Automation:
- Supports column filtered static data tables in the SCA plugin in SSMS
Supports multiple post-deployment scripts, in case there is a preference to manage static data tables in dedicated post-deployment scripts - Allows approaches like bulk loading larger static data tables by supporting SQLCMD variables in migration and post-deployment scripts
If there are other use cases where a migrations approach would add value to the team – such as easily being able to run data modification statements as part of deployments, or being able to customize the TSQL for schema changes – then changing from SQL Source Control to SQL Change Automation makes sense.
-
Tim Climis
commented
We'd also like this, not so much for storing different customer's data, but for storing test data. Our main repo stores code tables, and other static data. But it would nice to have a separate data folder for unit test data - the database schema would obviously be the same, but the tracked testing data would contain data that is normally dynamic for predictable test results.
-
Ed Morris
commented
Using the Static data link and Source Control is awesome however we immediately ran into an issue with this "static" data being environment aware - server names are different between Dev and QA for example. The concept to transform the data when being deployed is ideal, like this request!
-
Andrew Whitcomb
commented
The concept makes sense to me, but I think is unappropriate for "Source Control". If you were talking about applications, you are effectively saying you are storing your customer's configuration files / preferences in your source control system. My company is doing SaaS hosting, so we'd prefer to see this in some sort of deployment tool rather than in our Source Control.
-
Anonymous
commented
That would be a very useful feature
-
Anonymous
commented
This would be really useful to us, being a software firm serving many customers
-
Valthor Druzin
commented
A simple solution to this would be if SSC would have the option that the user could pick the check-in folder for the data explicitly, allowing for the option to make the data location different from the location of the objects. This way you could actually have the same database structure but different data for each customer, whilst maintaining a simple branching strategy within your version control subsystem.