3

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

Raif
  • 8,641
  • 13
  • 45
  • 56

2 Answers2

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

ferventcoder
  • 11,952
  • 3
  • 57
  • 90
  • Right, the emodes link was helpful. Thank you. I'm a little unclear on how to handle the tables. The tables I'm referring to are: ScriptsRun, ScriptsRunErrors and Version. I presume that I should manually write out a create script for the three of those and run it before trying to run any other RH features on virgin copy of production database. I'm not sure why I would put them in a runAfterCreate table. – Raif Sep 04 '13 at 13:40
  • 1
    Those three tables are handled by RH itself. It puts those tables there if they do not exist. – ferventcoder Sep 09 '13 at 19:52
  • Yep, im on it. Implemented it then pitched it to the team and dev ops (whatever the hell that means ) and they bought it. So ill write a blog post or two about it when it's up and running. Thanks for the help and rh – Raif Sep 10 '13 at 03:05
1

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
  1. 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.

  1. 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.

  2. 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.

  1. 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.

  1. Create a database backup.

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

Steven Liekens
  • 13,266
  • 8
  • 59
  • 85