What are the challenges you face when working across database platforms? Take the survey

Do I need Both a Shadow Database And Target Database?

The shadow database takes about a minute for us to build. Is there a reason I need both the shadow database and a target database on my build server? It seems like it would be best if I could just make sure readyroll can upgrade a backup of my production database? Is that possible?

Best Answer

  • Options
    dnlnlndnlnln Posts: 234 Gold 2
    edited April 10, 2018 9:54AM Answer ✓
    Both of these things are actually optional, but it depends on what level of validation and how you intend to deploy your database as to whether you might want to keep them:
    • If neither the TargetServer or ShadowServer properties is specified in your build configuration, then your project scripts will only be syntax validated and prepared into a package deployment script (enabling deployment through tools such as VSTS and Octopus Deploy). This is the least expensive type of build; as no database interaction occurs, the build should complete very quickly.
    • If the TargetServer is specified (only), a patch deployment script containing the migrations that are pending deployment to the SQL Server instance, will also be prepared during build. When the TargetServer is specified on its own, the build process still completes quite rapidly in this scenario, as it only needs to read from the __MigrationLog table in the target to do its work.
    • If the ShadowServer is specified (only), ReadyRoll will deploy the database from scratch to the given SQL Server instance, if you invoke the Clean or Rebuild target or use a new SQL Server instance each time you do a build (e.g. if the agent machine is re-generated with each build). This process verifies that the scripts are actually deployable, as well as syntactically valid. A snapshot file containing the schema definition will then be output from the build, which is subsequently stored in the target database during deployment.
    • If both the TargetServer and ShadowServer properties are specified, then in addition to the above, reports will also be generated as part of the build process, presuming that the initial deployment has been performed to the target (which inserts the aforementioned schema snapshot into the db) and your organization has a license to ReadyRoll Pro. This includes a preview report which details the schema changes that are pending deployment, and another report which reports on drift in the target environment. Both reports include a side-by-side diff of schema changes. Depending on the size of your schema, this can be an expensive operation as it needs to use the SQL Compare engine to interrogate the contents of the system catalogs to do its work. If you don't want these reports to be generated, then specify SkipDeployPreview=True and/or SkipDriftAnalysis=True (see Continuous Integration for more information).

    In terms of whether the shadow database can be cached to avoid having to deploy it from scratch with each clean build, unfortunately there is no support for this built into the product at the moment. However there's nothing in the build engine that should prevent you from restoring a backup as part of a pre-build step in your build configuration. I would recommend, though, that the backup actually be taken from a pre-existing shadow database, as ReadyRoll is quite precise about the sequence and content of data within the __MigrationLog table; any discrepancies could result in cache invalidation being triggered, resulting in slower builds.

    I hope this helps. Any questions please let me know.
    Daniel Nolan
    Product Manager
    Redgate Software


Sign In or Register to comment.