6

Right now we have a bunch of database servers with stored procedures running in them that are poorly documented and exist nowhere else.

When a change is made to one, there is no log, and it's really hard to figure out why something that was working suddenly fails.

We've recently switched to using proper version control using SVN, so I was hoping to add these stored procedures to version control.

We are a .Net shop, and I'm aware that there exists a Database project type. Would that be a good approach?

Alternatively I could just keep the stored procedures as text files and operate on those, but I'm wondering about the annoying deployment steps involved with doing so.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ryan Weir
  • 6,377
  • 5
  • 40
  • 60
  • AFAIK the Database project type is for creating SQL CLR projects... – Brian Driscoll Aug 24 '11 at 14:25
  • possible duplicate of [What is the best way to version control my SQL server stored procedures?](http://stackoverflow.com/questions/146543/what-is-the-best-way-to-version-control-my-sql-server-stored-procedures) – Brian Driscoll Aug 24 '11 at 14:27
  • @John thanks for letting me know - I don't have first-hand experience w/ that particular project type. – Brian Driscoll Aug 24 '11 at 14:28
  • 1
    @brian, then why comment if you don't know? Just saying. – John Saunders Aug 24 '11 at 14:30
  • @John I guess I thought my second-hand knowledge was sufficient, but clearly not. Your point is completely valid. – Brian Driscoll Aug 24 '11 at 14:32
  • You have a couple of options here: [How can I do version control of Database Schema?](http://stackoverflow.com/questions/6524246/how-can-i-do-version-control-of-database-schema/6524372#6524372) – reggie Aug 24 '11 at 14:35

5 Answers5

5

Check out redgate's sql source control. It has an interface built into SSMS and can integrate with SVN.

http://www.red-gate.com/products/sql-development/sql-source-control/

Derek
  • 21,828
  • 7
  • 53
  • 61
3

In Visual Studio, I created an empty project and added the scripts to it. Its part of my server schema solution. Now every single script in under source control. I also added a folder structure as well to try and keep the sanity. When the application is finished we will have probably close to 3000 scripts under source control under multiple server schemas. Not saying this is the best, but it is working for our project. The solution also has a utility which uses SMO to actually run/deploy all the scripts, so everything is contained in one solution. Screen shot attached showing some of the structure for your reference...

Source Control Scripts

Jon Raynor
  • 3,804
  • 6
  • 29
  • 43
2

A database project is a good choice, in my opinion. You can import the entire database, including tables, views, stored procedures, etc. Visual Studio will use this information to build an in-memory model of the database.

It can use this for several purposes, including making sure your stored procedures are properly accessing the tables. For instance, it caught me trying to insert an Integer parameter into a smallint column.

Community
  • 1
  • 1
John Saunders
  • 160,644
  • 26
  • 247
  • 397
1

We use the database project and have good results with it. All DML exists in the project (3000+ items).

Developers must make changes to the DML in source control and check them in, and only what is checked in will be promoted/deployed.

Our source control is TFS, and I noticed that deleting procs from the VS interface doesnt always mark the proc for deleting in source control. Not sure what SVN does with it.

StingyJack
  • 19,041
  • 10
  • 63
  • 122
1

Yes, I would recommend a database project -- you can synchronize database elements (tables/stored procedures, etc...) in either direction: source files to DB, or DB to source files.

You could start by creating an empty database project, then syncing from the existing database(s) to your project, which creates the .sql create scripts for you.

And you can use the database project to create deployment scripts too. Very handy.

JohnD
  • 14,327
  • 4
  • 40
  • 53