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. |
If you're looking to seed a table with a larger amount of data (10,000+ rows) then you may find that the approaches outlined in this article unsuitable, as INSERT-ing many rows with literal values does not tend to scale very well. For larger data sets, we recommend including a seed file in your project and using the BULK INSERT statement to upload the data. Read more about using the BULK INSERT method. |
In addition to synchronizing schema and code objects, the ReadyRoll tool-window 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:
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.
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 tool-window if data 'marked' table exceeds 1MB. For larger data sets, we recommend using the data seed method with BULK INSERT method instead. |
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.
There are a few draw-backs of the offline method to consider prior to selecting this approach:
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.
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.
For example:
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.
Naming your Pre/Post-Deployment scripts in this way isn't mandatory for your project to build, however we recommend sequentially numbering your scripts to make it easier to explicitly define the order of execution. This is particularly important if there are interdependencies between your static data tables. |
Paste the SQL copied in the previous step into the newly created script file.
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.