This project is read-only.

Working with the development db

Feb 23, 2011 at 2:10 PM

Until now the developers in this project we worked with the dev db, like this:

  1. Attaching+detaching+zipping db, in order to have fast way to work with necessary data (avoid manually inserting test data). 
  2. We scripted the objects in separated files and add them to the repository in order to keep track of the changes (you can view the repository history and see which changes were made on the db objects since the latest release).

I found working with the development db very frustrating. We should find a way to easily work with the development db . 

Some requirements for the method that we should are (that I can think of):

  1. It must work with any version of SQL Server.
  2. Include data
  3. It should not be dependant of any db file / mdf

Working with sql server backup files is not nice because it makes references to the mdf and log files of the original db.

There is an small tool with a large name Microsoft SQL Server Database Publishing Wizard. I gave it a try and its a wizard (also a command line is available), where you select the db and it will script objects and data in an plain text file. 

Should we use this tool?

Is there another better way to work with multiple offline (we are disconnected from each other) development dbs?

Feb 27, 2011 at 6:35 PM
Edited Feb 27, 2011 at 6:43 PM

Jorge,

Sorry I didn't see this thread before. Something we could do is use Mercurial hooks on commits. For example, whenever we commit our changes, Mercurial will run a command that will script our DB for us. I haven't done it before, but it's something that we can have a look at. I agree with you: it's a heavy burden that we should automate away! :-)

I see a tradeoff though. For whatever change we make to local code and then commit I foresee that the db scripts will be regenerated by mercurial. You can have a look at:

http://stackoverflow.com/questions/1449177/using-mercurial-hooks-to-create-load-database-dumps-for-versioning

and 

http://hgbook.red-bean.com/read/handling-repository-events-with-hooks.html

what do you think?

Feb 27, 2011 at 10:17 PM

Jorge,

I created a quick and dirty hook with hg. You have to edit the hgrc file inside the .hg folder. Add the following lines:

[hooks]
pre-commit.delete_db_script = del  "YourPath\Projects\NearForums\db\scripts\mssql\NearForumsFullDB.sql"
pre-commit.add_db_script = sqlpubwiz script -d ForumsTest -S serverName\Instance "YourPath\Projects\NearForums\db\scripts\mssql\NearForumsFullDB.sql"

Those pre-commit execute before the commit command is called whereas the precommit hook execute after the commit command is called but before the commit transaction is completed. You have to do it with pre-commit otherwise it does not do the trick (mercurial keeps thinking there is stuff to commit, after each commit for all eternity ... sort of recursive infinite crap haha)

http://www.selenic.com/mercurial/hgrc.5.html#hooks

Anyways, I successfully created the hook and the DB was scripted nicely. A drawback is that for each trivial commit the db scripts are regenerated. Kinda sucks. But i guess it's better than nothing. We can also get creative and apply the hook after any hg update and execute the SQL script and load the DB into our dev environment so that we are always in sync with the dev db.

What do you think?

Feb 28, 2011 at 9:38 AM

That's a good idea!

I agree that executing before every commit could be time consuming... Maybe a pre-outgoing hook? Could a pre-outgoing hook produce a commit?

 

Anyway, What do you think of sqlpubwiz as a tool?

Also, I see that scripting every object separately is still necessary to avoid loosing db changes, Am I right?

(A developer could script its own dev db and push it without getting the latest)

 

I found this on stackoverflow: http://stackoverflow.com/questions/3133639/how-do-you-manage-databases-during-developmenthttp://stackoverflow.com/questions/988426/how-should-you-build-your-database-from-source-control but its not an easy subject...

Feb 28, 2011 at 2:08 PM

Jorge,

I think that sqlpubwiz is a nice tool. 

Using a pre-outgoing or pre-push hook is a better idea than using pre-commit. What will we do to keep in sync with the dev db automatically? if we perform the db load automatically on every update, a developer could lose his/her changes with the ones from the repo (is that what you are referring to when you mention losing db changes?) w/o having scripted his/her changes. At the same time, just like you mention that if the dev is not in sync with the repo he/she could push an outdated db script (though I think hg will not let push conflicting files, I'm not sure)

I tested the hooks on my local machine. Should we setup a hg repo somewhere and perform some tests?

Feb 28, 2011 at 2:10 PM
Edited Feb 28, 2011 at 2:10 PM
jorgebg wrote:

...Could a pre-outgoing hook produce a commit?

I don't think so. But we can investigate :-)

Feb 28, 2011 at 5:11 PM
Edited Feb 28, 2011 at 5:12 PM

Not sure if this is relevant to the discussion, but I'm looking at using this tool for DB migrations:

http://code.google.com/p/dbup/

Feb 28, 2011 at 5:28 PM

Thanks Trevor, I think that's similar to Migrator dot Net. I will have a look at it

Mar 1, 2011 at 3:10 PM

I gave it a try and it has all the necessary attributes. The only problem is that it does not support another db engine besides SQL Server...

I posted a thread about supporting other db engines on the discussion group: http://groups.google.com/group/dbup-discuss/browse_thread/thread/6efa7a6a51ef09cf

Mar 2, 2011 at 4:21 PM

I think I got it wrong...

I don't think it would be necessary to dbup to support mysql engine, because this subject is about the development db. The dev db is supposed to be MSSQL...

For releasing, the mssql db is scripted (using sql management studio) from the dev db and all then ported to mysql db.

 

So, what do you think?

Should we give dbup a try, create the first scripts and create a console application to do the transitions?

Mar 7, 2011 at 3:05 PM

We definitely should give it a try! But the DBUp only performs upgrades but we have to create the scripts first either manually or automatically and assign those the right number ordering for dbup to perform the upgrade, right? Still, we need to automate the script creation, if i'm not mistaken. We are on the right track though.

Mar 7, 2011 at 3:19 PM

What if we need to downgrade? Or Dbup executes de SQL DDL that's on the folders regardless of the version?

Mar 7, 2011 at 3:24 PM
jasonco wrote:

What if we need to downgrade?

There is no explicit downgrade, you can recreate an old version of the database by executing dbup until an specific script file (for example, until the script named "Script0012...") and get a snap shot of the dev db at that time.

I think this is good enough for mantaining version branches...

Mar 7, 2011 at 3:38 PM

Thanks for the explanation Jorge. I think that's good enough for us! I agree on this method. It's much more elegant than using Hg hooks. So, a decision has been made?

Mar 7, 2011 at 3:59 PM

OK, Lets give it a try!

I'll try to provide the first scripts and a command line app for tomorrow. For now I wont delete the current files for the dev db...

Mar 7, 2011 at 4:09 PM

Great. Let me know if I can be of any help.

Mar 8, 2011 at 1:42 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.