14

I'm starting a new project and using SVN (with Tortoise) as my Version Control System. I was wondering if it was possible to also maintain a SQL Server Database using the same system.

I'd want to version my tables/functions/views/procs/triggers/etc. but not my data as it's all going to be test data anyway. I'm not really sure how to set this up. I've come across a few options but I would like to know if there were any I was missing, and if there's maybe a guide or something out there to help me get running with it.

I've seen and heard of Red Gate, but I'm looking for something free (or at least very low cost). I know I could always write something myself, but I'm not really trying to spend time on that.

One thing I did come across was an open source package put together called ScriptDB4Svn. Has anyone used this before? Is it good? Can it do the things I need it to do and is it pretty simple to get setup?

yannis
  • 39,547
  • 40
  • 183
  • 216
  • 1
    `Has anyone used this before? Is it good? Can it do the things I need it to do and is it pretty simple to get setup?` Why are you afraid to try it for yourself? Just grab it and play around. – yannis Jan 10 '12 at 02:40
  • @YannisRizos - I definitely will if I don't get too much response from this, I basically just wanted to try and save some time and see if anyone had worked with it before, or if anyone had anything tried and tested right off the bat that fit my needs so I could save some experimentation time. –  Jan 10 '12 at 03:00
  • Just noticed how new you are here. Programmers SE is not a good place to ask questions just to save some time, we really expect you to do stuff like that for yourself, i.e. [do your own research before asking](http://programmers.stackexchange.com/questions/how-to-ask). Or, alternatively, ask in [chat](http://chat.stackexchange.com/) (but don't expect solid answers). Having said that, it really doesn't matter because that last sentence is not your core question, which is actually a very good one (and properly tagged, that's rare for new users, kudos!). – yannis Jan 10 '12 at 03:07
  • @YannisRizos - thanks. I will hop into the chat to see if I can get some feedback for ScriptDB4Svn, and check back here for any updates to the core question. Edit: Looks like I can't chat until I have 20 rep. Oh well, patience I guess. –  Jan 10 '12 at 03:13

5 Answers5

2

Technically you don't even need a tool, you can script the objects directly and check them into source control. It is a bit more work without the tool, but it is definitely workable.

BTW: I've used the RedGate tool and it is pretty slick and worth the money.

JohnFx
  • 19,052
  • 8
  • 65
  • 112
  • So basically I would do my work in Management Studio, and then export the scripts to the SVN directory, and basically do that every time I work on it (replacing the old ones every export)? I guess that would work. It would keep the SVN functionality of being able to revert and stuff, but yeah, it would be kind of a hassle. Maybe I'll check out the RedGate pricing and see if it's worth it for me. –  Jan 10 '12 at 04:27
  • @Scott - The manual way can work, you just have to think about your SQL development differently. The scripted versions of the objects are the "official" ones and the one in SQL is just a compiled version of it. Just like your source code. – JohnFx Jan 10 '12 at 19:14
  • I decided to do things manually, and possibly implement a script using the help in the links that Mike Nakis provided, but for right now I am just going to use the built in GUI within Management Studio to export the DB creation scripts when I am done working, and check those in and let SVN keep them versioned that way. Since I decided to do things manually, you get the answer for pointing out I don't really need a tool to do this stuff :) –  Jan 11 '12 at 21:27
1

It sounds like you have a mostly Microsoft setup. You could have a look into Database Projects (previously known as DataDude). They basically turn T-SQL into a first-class language in Visual Studio; you can:

  • Compile projects - it does not merely create a final script, it makes sure object names etc. exist.
  • Perform static code analysis - for example, ensuring that you always refer to objects by including their schema (e.g. [dbo] in most cases) for that nice 30% performance boost.
  • Create diff scripts by having it compare different versions of the project.
  • Update your project from a database or script (reverse engineer).
  • Intellisense.
  • There are no diagramming tools.

They unify your code and your database code nicely under source control as well. If you man-up and script your database objects (instead of using Davinci Tools in SSMS) you also land up using one IDE - which is nice.

Community
  • 1
Jonathan Dickinson
  • 143
  • 6
0

Yes, I've used a similar tool (developed in-house) on a previous project. It would script all tables, views, sprocs, triggers, etc into individual .sql files. Then, we had a script that ran every night to "validate" that everything in our "development" database was reflected in source control.

So the normal workflow is that you'd change your code, change the corresponding tables and sprocs in the development database as required, then you would run the tool we had which would refresh all of the scripted .sql files. You'd then check everything in at once.

The problem was that if you forgot to run the tool, the code would "work" (and unit tests would pass) because the database was "correct", but the new sprocs/tables wouldn't be source control.

So every night, we have a script which did a checkout of the source code, then rand the tool to refresh all of the scripts. If there was any difference, it means someone forgot to check in their changes and an email notification was generated. It was basically just a way to make sure we didn't forget to keep the source control up to date.

It was a bit annoying because it made it hard to work on changes that spanned multiple days, but it was better than having nothing...

Dean Harding
  • 19,871
  • 3
  • 51
  • 70
  • Can you elaborate on `Then, we had a script that ran every night to "validate" that everything in our "development" database was reflected in source control.`? Thanks for your response. –  Jan 10 '12 at 03:35
  • @Scott: I've edited the answer to include a bit more detail. – Dean Harding Jan 10 '12 at 09:01
0

You can use Rails. Rails has a concept of database migrations which you can apply or roll back. In my experience this is the best way to version a database. You do check these migration files into SVN.

In my current project, we're not developing the application in Ruby, but we're still using Rails to manage the database. I wouldn't do it any other way.

Vinnie
  • 231
  • 1
  • 3
  • Any guides to explain this a little bit more and go into setting something like this up? –  Jan 10 '12 at 03:57
  • Actually, that's not a very good idea to use Rails together with .NET technologies. – altern Jan 10 '12 at 12:24
  • Chapter on Rails migrations (http://guides.rubyonrails.org/migrations.html). This should be enough to get you started and give you all the background you need as to why this is a good idea. @altern - since you're just using Rails to manipulate and version the database, this should have any impact on and .NET technologies. You are be able to access and use the DB in the same manner as if you were not using rails. I wouldn't mind seeing some references to your concerns. Isn't IronRuby a .Net implementation of Ruby and Rails? – Vinnie Jan 10 '12 at 21:54
  • **> Isn't IronRuby a .Net implementation of Ruby and Rails?** IronRuby is a .NET implementation of *Ruby*. I'm not sure Rails works properly on IronRuby. My general argument against using Rails for the purpose of db versioning is that Ruby and related technologies (RoR, migratinos) have quite steep learning curve, especially for such simple task as db versioning. It's ok to use it for other purposes, not just migrations. Otherwise, it will just raise complexity of the project without much positive effect. – altern Jan 11 '12 at 11:05
0

This has been discussed before on stackoverflow: https://stackoverflow.com/questions/2750278/sql-server-2008-create-database-script-schema-data-with-command-line

Also, this external article provides some additional information http://www.sqlteam.com/article/scripting-database-objects-using-smo-updated together with sample code in the form of a Windows Application.

Since what you want to do is something that I have done by myself for MS Access, I will tell you what I have done in case it gives you some ideas: I have written a module called Ado2Xml which converts the schema and data of any ADO-accessible database to xml, and back. It only knows about tables and views though; no stored procedures, no triggers, no nothing. Anyway, in your case this module gets replaced by the tool that you will presumably find which does what you want with MS-SQL. So, every time my application launches, it compares the timestamp of the database against the timestamp of the saved xml file; if the xml file is more recent, then it destroys the database and invokes Ado2Xml to re-create it from the xml file. When my application terminates, it does the reverse: it invokes Ado2Xml to export the database into the xml file. Actually, the ADO objects that extract the database schema are for some reason awfully slow, causing the exporting process to take some time. So, in order to avoid having to wait each time for my application to terminate and visual studio to switch from the debugging layout to the editing layout, right before it terminates my app launches an external app to do the exporting, so that it can terminate immediately.

Community
  • 1
Mike Nakis
  • 32,003
  • 7
  • 76
  • 111
  • The two links you provided are something I am probably interested in getting setup myself, so I can basically automatic the manual steps I am doing right now. Thanks for those! –  Jan 11 '12 at 21:25