Data population strategies
If you're interested in the various techniques for seeding your database in general, you may like to start by reading this article on data population strategies.
One thing to consider when source-controlling your database is how to maintain static/lookup data within your project.
This can include data that is needed by the calling application to work such as zip/postal codes, country lists or even application settings.
ReadyRoll offers two distinct ways of handling static data:
Offline method – use an open-source tool to generate re-usable MERGE statements
This method has a higher learning curve than the online method but offers more flexibility. The advantage of working offline is that you can store the data in a single file, rather than generating a new file with each change, allowing you to branch and merge the data in source control.
You can choose to use either the online or offline editing approaches above, or a combination of both, in the one database project.
For example, you may use the online method on tables that contain consistent data between all environments, and use the offline method on tables where the data changes between environments.
Handling larger data sets
In addition to synchronizing schema and code objects, the ReadyRoll DBSync tool in Visual Studio can also synchronize static data..
This allows you to edit table data in an online manner, e.g. with SQL Management Studio, and then import changes back into your database project to generate INSERT/UPDATE/DELETE statements within a new migration.
To mark a table for data tracking:
Primary Key requirement
In order to track the data within your tables, the table must include a primary key. If a primary key is not present in a given table, the data within the table will be ignored (not imported).
You can now preview and import data from the specified tables.
Working with larger data sets
When choosing tables to track for data changes, it is important to bear in mind that ReadyRoll is optimized for working with smaller sets of static data (< 1MB table size). This is to ensure that your database projects always build quickly, as larger datasets can produce a significant amount T-SQL code which can cause performance issues during project build/deployment.
A warning will display within the ReadyRoll DBSync tool if data 'marked' table exceeds 1MB.
For larger data sets, we recommend using the data seed method with BULK INSERT method instead.
Limitations in Continuous Integration / Deployment
Please note that ReadyRoll's static data tracking is not currently supported within the drift correction feature, so the drift report will not report on any data differences. Likewise, the deployment preview feature which shows object-level changes that are pending deployment (e.g. in Octopus / VSTS / TFS), does not yet support the previewing of data changes.
Editing offline means that you start by preparing a data change script before actually deploying the changes to the database.
In this example, we'll use the MERGE statement to manage the deployment of our static data. What makes MERGE so useful is not only its ability to insert, update or delete data in one succinct, atomic operation but also the fact that the statements are re-runnable. This allow for the source script to be edited and re-used for deployment to all target environments. It also gives you the ability to include SQLCMD variables in place of literal values, allowing you to centralize the deployment of configuration data (read more about deploying environment-specific data).
To generate MERGE statements, we will use the sp_generate_merge stored procedure utility. The script that is generated from the utility can be pasted into a Post-Deployment script in your ReadyRoll project, as explained below.
Limitations of the offline method
There are a few draw-backs of the offline method to consider prior to selecting this approach:
MERGEstatement: before actually running the deployment against your target environment, it can be difficult to know what changes will be applied (if any). Worst case scenario, you could hit one of the documented issues in
If you foresee that these limitations may be problematic for your deployments, then it may be worth considering the online approach to source controlling your static data instead.
Download the "master.dbo.sp_generate_merge" stored procedure and install it by simply running the script on your dev SQL Server instance. This will install the utility as a system procedure within the [master] database so that it can be used within all of your user databases.
Open Source Project
Note that you do not need to add this script to your ReadyRoll database project, nor do you need to deploy the stored procedure to any server other than your Development environment.
Full details of the "sp_generate_merge" stored procedure can be found on GitHub.
In this step we'll generate a single MERGE statement containing all the records from the "Region" table in the Northwind database:
Firstly, open SQL Management Studio and connect to your development database server and ensure that your SQL client is configured to send results to grid, rather than text.
Execute the stored procedure, providing the source table name as a parameter. If your table is in a non-default schema, be sure to supply the @schema parameter with its name.
EXEC Northwind.dbo.sp_generate_merge 'Region', @schema='dbo'
Click the hyperlink in the result set to open up the Xml fragment in a new document window.
Copy the document contents (excluding the Xml tags) to the clipboard and switch back to Visual Studio.
In the next step we'll create a script to be executed during each deployment which will ensure that the table is always in-sync.
Within Visual Studio, open the Solution Explorer tool-window (View... Solution Explorer) and expand your ReadyRoll database project's Post-Deployment sub-folder.
Paste the SQL copied in the previous step into the newly created script file.
Making Changes to Static Data
To see how the script can be re-used to repeatedly deploy changes in your static data, try making changes to the data in the script and re-run the deployment. The rows affected by the MERGE should reflect your additions and modifications to the file.
If you prefer to initiate changes to data by editing the table data directly in SSMS, you can do so by simply re-running the sp_generate_merge procedure after making changes to the live data. Copy+Paste the generated code as you did in the previous steps to update your Post-Deployment script with the new changes.
Using the offline method outlined above gives you the ability to include SQLCMD variables in place of literal values, allowing data from the deployment system to be passed in and stored within your tables (e.g. environment-specific variables from Octopus).
This means you could use your deployment system to centralize the storage of configuration settings, giving you a systematic and repeatable process for propagating updates to configuration data.