Retrofit DB for use with RoundHouse

Question

I'm looking into getting our db into source control. I can't find any info on the best way to retrofit existing dbs for use with rh.

I can see the tables created should I just script those out and add them to our db and things will proceed from there? Or should I get a bak of the db and run rh with restore flag? Seems like there should be some guidelines of this.

If you have any insights please let me know.

thanks

Answer 1

Have you seen PowerUp? https://github.com/chucknorris/powerup

This is a utility to extract current items out of a database into an idempotent RoundhousE format.

As far as tables, you can script that out separately and then put those into a runAfterCreate table.

There is a little guidance in the wiki on this - https://github.com/chucknorris/roundhouse/wiki/RoundhousEModes

Answer 2

From what I gather, this is the intended workflow:

1. Create a full backup of the existing database.

This backup file is your baseline image. Move the file to a file share with a fixed location. Fixed means that the full path doesn't change when the baseline image is updated to a newer version (typically right after a successful deploy).

\\BuildServer\Data\Backups\LegacyDb.bak

2. Use SSMS or PoweruP to script "Anytime" database objects (stored procedures, functions, views and indexes) one object per file. Move the scripts to directories named "sprocs", "functions", "views" and "indexes".

Note that you're not supposed to script "Onetime" database objects at this point.

3. Do some actual work that involves adding Onetime scripts or changing Anytime scripts. Perhaps add a new table and change a view. Update your migration scripts accordingly.

4. Use the "RunRestore" mode to deploy your changes to a development database and verify that your migration scripts work as intended.

In this mode, the existing database is replaced with the baseline image before executing migration scripts. Restoring the backup is necessary; otherwise, RH won't execute migration scripts that were previously executed because the version info in the development database is ahead of the baseline info. You absolutely want to be able to make changes and test changes to migration scripts for as long as they are still under development.

5. When you feel confident enough, use the "Normal" mode to deploy your changes to the production database.

In this mode, RH does not restore any backups before executing migration scripts. In theory, your production database and your baseline image have remained the same throughout the sprint, so restoring the backup is not needed. Not to mention that it would probably destroy weeks/months worth of data.

If this is the first deploy, RH will create 3 tables that it uses to store its version info for that database. From now on, version info will be included in your baseline image.

6. Create a database backup.

This backup file is the new baseline image for the next sprint.