On-premise deployment tool

L_Somers

Hi, I'm briefly looking into ReadyRoll and am trying to figure out how it should work when deploying to offline clients, so there is no connection to TFS or Octopus or anything online.

Is there a ready built (out of the box) commandline tool (or a DLL with a callable method would be even nicer) that can determine the current version of the database and run the scripts from the correct point to bring it up-to-date. (liquibase style).

If not, should I be looking at a different tool? We are already using Sql Source control. We are also working on a shared database which in our case is more convenient than distributed versions.

dnlnln

ReadyRoll's deployment packages are output in SQLCMD format, which makes it possible to bundle them with your application and deploy using the sqlcmd.exe tool which ships with SQL Server.

If you would like to be able to bundle the deployment package within your application so that it is installed on the client side, you can embed the file and call it with the SqlCmd runner library, as described in this article: https://documentation.red-gate.com/display/RR1/Embedded+Resource

I hope this makes sense. Please let me know if you have any questions

L_Somers

Thanks for your answer, it does clear some things up, but I still have some questions.

How does versioning work? Will the SqlCmd packages take care of checking the current version and incrementally upgrading, or would we have to build in a mechanism that takes care of that? (basically a cell containing a version number or a log of dates and applied versions).

If ReadyRoll takes care of it, could you explain how (show a table or something), just to boost our teams confidence :-)

In case we would have to build the versioning stuff ourselves, what would be the advantage of using ReadyRoll compared to using Sql Schema Compare to generate a script between two versions in Source Control (taking into consideration that we already use Sql Source Control and are working on a shared database)?

Again, thanks for the time you take to answer these questions (saves hours or days of research). Searching for these answers only brought me to cloud-based solutions. Maybe you could point me to a On-premise-Specific introduction document that covers these aspects.

dnlnln

I understand where you're coming from, so let's see if I can de-mystify how the package scripts work when deploying them out to your customers.

ReadyRoll's SqlCmd packages are multi-use scripts which will allow you both deploy the database from scratch and also apply incremental changes. The script contains a concatenated set of all of the migrations within your project, wrapped in guard clauses to ensure that each script is executed a single time only. The table that the package relies on to test the conditionals, the transaction that these are contained within, as well as the guard clauses themselves, are all added by ReadyRoll during the build process. This means that you don't need to worry about including versioning logic of your own, nor do you have to write your migrations idempotently to make the package script re-runnable.

When your customers run the package script against their database, the script will check the migration log table to determine which migrations have yet to be applied and execute the appropriate scripts on-the-fly. Here's a snippet from the package script that shows how a migration with a single batch is applied:

BEGIN TRANSACTION

IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] 
               WHERE [migration_id] = CAST ('1868a53c-d764-46c5-8c7a-74ae9075297d' AS UNIQUEIDENTIFIER))
  EXECUTE ('
CREATE TABLE [dbo].[MyTable]
(
[Id] [int] NOT NULL
)
')
GO
IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] 
               WHERE [migration_id] = CAST ('1868a53c-d764-46c5-8c7a-74ae9075297d' AS UNIQUEIDENTIFIER))
  INSERT [$(DatabaseName)].[dbo].[__MigrationLog] ([migration_id], [script_checksum], [script_filename], [complete_dt], [applied_by], [deployed], [version], [package_version], [release_version])
  VALUES (CAST ('1868a53c-d764-46c5-8c7a-74ae9075297d' AS UNIQUEIDENTIFIER), 'ABADB085DF4972C1BEA293298FE6401894B5CE3F113E651950968E91A9A15D99', '0001_Create-MyTable.sql', SYSDATETIME(), SYSTEM_USER, 1, NULL, '$(PackageVersion)', CASE '$(ReleaseVersion)' WHEN '' THEN NULL ELSE '$(ReleaseVersion)' END);
GO
COMMIT TRANSACTION

The MigrationLog table will progressively be populated with a row for each script that is applied successfully, when it was applied, the version it belonged to (if semver is used), etc


Note that, to ensure that your project stays in-sync with your customer databases, the golden rule with migrations is to always follow the immutability principle: once a migration has been deployed, the file should not be removed from the project or otherwise modified. For example, if a migration that adds a column has already been shipped to a customer, but you later decide the column wasn't needed, then a new migration should be added to the project to drop the column, rather than removing/modifying the original migration.

You can read more about writing migrations in the ReadyRoll documentation. I hope this helps, but please let me know if you have any further questions

L_Somers

Ok, I spent some hours of reasearch.

- ReadyRoll uses a __MigrationLog table to keep track of versioning changes.
- The generated migration scripts (SQL) can figure out what to run and what not without external code.

- Integration with Sql Source Control is not yet ready, but RedGate is working on it. Until then we have to manually import changes into the Visual Studio solution by actively checking for "drift" between a Shadow and a Target database. The shadow database will be deployed by ReadyRoll, and compared to the target in order to show differences.
- There is also an "Offline Schema Model" feature, but it seems to be a cut down write-only version of SQL Source Control to keep track of schema changes made by developers, enabling change history tracking (and BLAME) on specific objects.

So in order to make this work:
- For each release we need to synchronize ReadyRoll with our dev database, select all differences and click the "Import and generate script" button, and check in the generated migration scripts.
- Our installer should run SQLCMD with the ReadyRoll Package on the target database. (how to show progress information and extract error logs if anything goes wrong? Redirect CmdLne output?)

Cost: €445,- per developer.

If we use Sql Schema Compare to generate migration scripts and build our own __MigrationLog solution, we may be able to automate the process via the command-line on the build server. This way we would only need one SQL Compare Pro license (€675,-). One drawback here is that we cannot bundle SQL Source Control Check-ins and Visual Studio code changes together into a single change-set (results in frequent build failures that get ignored because that's "normal").

Is my analysis correct or do you have anything to add that I may have missed?

L_Somers

@dnlnln thanks again for your time and the example script. I think we posted almost simultaneously :-)

I think it's an interesting choice to use GUIDs as change-set Id's. It kind of implies that the order of execution does not really matter. We are using a Semver-like scheme for our release and patch versioning so we could take care of executing the scripts in the correct sequential order in our installer by using the version in the package filename. That would work if we make one package per release.

I'm wondering how this works for the "intended use" where developers would check in the upgrade package with other code changes in a change-set. Is there a surrounding umbrella SQL script that runs the individual scripts?

dnlnln

L_Somers wrote: »

So in order to make this work:
- For each release we need to synchronize ReadyRoll with our dev database, select all differences and click the "Import and generate script" button, and check in the generated migration scripts.

That is one approach you could take, although many customers tend to favour generating the incremental migrations at the time the changes are made, by working in private sandbox environments and committing the db changes alongside the application changes. For more information, see the ReadyRoll FAQ(Q: Which environment strategy works best with ReadyRoll?)

L_Somers wrote: »

- Our installer should run SQLCMD with the ReadyRoll Package on the target database. (how to show progress information and extract error logs if anything goes wrong? Redirect CmdLne output?)

The SqlCmd utility will output any PRINT statements to stdout as the deployment progresses (and errors to stderr). Alternatively, you can specify the "-o" argument on the SqlCmd command-line to log the output to a text file and then read it afterwards.

Note that, if you decide to use the embedded resource method to ship your deployment package, this uses a third-party library to perform the deployment within your application/installer, so errors are logged a bit differently (via exceptions I believe)

L_Somers wrote: »

If we use Sql Schema Compare to generate migration scripts and build our own __MigrationLog solution, we may be able to automate the process via the command-line on the build server. This way we would only need one SQL Compare Pro license (€675,-). One drawback here is that we cannot bundle SQL Source Control Check-ins and Visual Studio code changes together into a single change-set (results in frequent build failures that get ignored because that's "normal").

Is my analysis correct or do you have anything to add that I may have missed?

You are quite correct, you would need to roll your own migrations solution in order to follow an approach that combines SQL Source Control and SQL Compare's script generation capability.

L_Somers wrote: »

I think it's an interesting choice to use GUIDs as change-set Id's. It kind of implies that the order of execution does not really matter.

Just to clarify, the GUIDs are used simply to refer to the migration (i.e. in order to enforce the "run once" behaviour) and don't have any impact on deployment order (more on that below)

L_Somers wrote: »

I'm wondering how this works for the "intended use" where developers would check in the upgrade package with other code changes in a change-set. Is there a surrounding umbrella SQL script that runs the individual scripts?

To clarify. by "upgrade package" are you referring to the individual migration scripts? The order of your deployment is controlled by the numeric prefix that is assigned at design-time to each migration script file (001, 002, etc), and also by the containing folder if you're using the semantic versioning feature. To see what the full deployment order of your project will be, perform a ReBuild and refer to the Output window in Visual Studio.

StephanieHerr

L_Somers wrote: »

If we use Sql Schema Compare to generate migration scripts and build our own __MigrationLog solution, we may be able to automate the process via the command-line on the build server. This way we would only need one SQL Compare Pro license (€675,-). One drawback here is that we cannot bundle SQL Source Control Check-ins and Visual Studio code changes together into a single change-set (results in frequent build failures that get ignored because that's "normal").

Just to clarify - The SQL Compare Pro line should not be used on the build server. DLM Automation licenses available as part of the SQL Toolbelt are needed - https://documentation.red-gate.com/display/SC12/Changes+to+distribution+of+command+line.

L_Somers

@sherr thanks for pointing that out. So the state-based solution would cost €2.595,- per developer (including some bonus tools) while the migration-based route costs €445,- per developer...

@dnlnln thanks for your answers, I have enough info to go on for now.