Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am currently using Tortoise SVN to source control a .NET Web Application. What would be the best way to bring our SQL Server stored procedures into Source Control? I am currently using VS 2010 as my development environment and connecting to an off-premise SQL Server 2008 R2 database using SQL Server Data Tools (SSDT).

What I have been doing in the past is saving the procs to a .sql file and keeping this files under source control. I'm sure there must be a more efficient way than this? Is there an extension I can install on VS2010, SSDT or even SQL Server on the production machine?

share|improve this question
2  
If you're using the SSDT project type in Visual Studio, add that project to source control. That's it. –  Mark Storey-Smith Mar 26 '13 at 16:10
1  
Please clarify your objective(s) -- are you just looking for versioning of the database objects, or are you trying to use this as a deployment platform as well? –  Jon Seigel Mar 27 '13 at 1:24
add comment

6 Answers 6

up vote 6 down vote accepted

There are tools out there, such as this from Redgate, but I have always found that best is to save as SQL files, perhaps even in a Database Project (SSDT?) in your solution.

Along with this, I suggest the following guidelines:

  • Always assume the SVN version as the "current" / "latest"
  • Ensure that every script you run has an appropriate "if exists then drop" at the start
  • Remember to script your permissions, if any

You can initially create these SQL files by scripting directly from SSMS, and you can set SSMS to script all your "drop" and "create" as well as your permissions.

share|improve this answer
    
I was unaware of the database project type and have only just started exploring SSDT, but this looks promising. I've opted for this solution as there's no dependency on 3rd party tools and I can easily drop the .sql files into our current Source Control. –  QF_Developer Mar 27 '13 at 9:41
    
Also do not allow rights to devs on prod and those with rights only deploy from source control. –  HLGEM Apr 2 '13 at 14:02
    
Be careful with "if exists them drop, (re)create with new definition" if changing tables/views that are referenced by other views/procs. I have hit circumstances where the output of such dependent views is corrupt (column type and contents moved but names not) due to a query plan being reused without recompile assuming the previous structure. A safer option is "if not exists create dummy" followed by "alter table/view/proc" as alter will follow sysdepends records to invalidate plans as needed and drop+create will not as drop wipes such records and create won't scan for dangling references. –  David Spillett May 19 at 12:55
add comment

Try Ankhsvn, highly recommended and free.

From the homepage:

AnkhSVN is a Subversion Source Control Provider for Microsoft Visual Studio 2005, 2008, 2010 and 2012.

AnkhSVN provides Apache™ Subversion® source code management support to all project types supported by Visual Studio and allows you to perform the most common version control operations directly from inside the Microsoft Visual Studio IDE.

The Pending Changes dashboard gives you a unique insight in your development process and provides easy access to the source code and issue management features. The deep source code control (SCC) integration allows you to focus on developing, while AnkhSVN keeps track of all your changes and provides you the tools to effectively handle your specific needs.

share|improve this answer
add comment

Saving the SQL files in source control provides control over the SQL files only. It doesn't control the changes of the actual database objects, nor it prevents simultaneous changes of the same database object by multiple users (and I guess you would like to have that under control, too). What we use is a 3rd party tool (ApexSQL Version), it integrates both with SSMS and VS, you can chose whether to work with a database version of the object, or with a Source Control version. If you're editing a database version, it's automatically checked out only to you, so no one else can edit it (it doesn't merge changes from different users). Only when you check it in again, others can modify it. And you can have your SC version different from the version of a live object (I use that when I leave for the day and plan to finish the edits and test it on the next)

share|improve this answer
add comment

I have tried both RedGate and Visual Studio's database project and I prefer the storing the database definition in the database project. As soon as the database becomes part of the solution, you can use your preferred source control provider. Most have excellent Visual Studio integration.

With the SSDT tools you have the 'lastest version' of the database definition, allowing you to easily make schema comparisons and generate schema upgrade scripts.

That said, the schema is usually only a part of the equasion. In real life it turns out that databases allready have a lots of data. And my users tend to get rather disappointed when they loose it.

So as soon as I rolled out v1.0 the need arrises to maintain upgrade scripts. Sometimes these just contain schema changes, but many times I need to create defaults based on the content of some other table, need to release a particular constraint until I seeded the data etc. Usually simply upgrading the schema does not quite cut it. My preference is to have these upgrade script in a separate folder in the database project too. These would usually look like 'upgrade from v1.0 to v1.1'.

My databases always have a reference table that tells me the current version number, so I can block incompatible upgrades. The first statement in my upgrade scripts check the current version and bail out if it's different from what's expected.

Another benefit from the database projects is to be able to deploy different sets of data based on the same schema. I have a different datasets for development, the QA team, user acceptence test and for automated integration tests. Since a database project can have only 1 post-deploy script, the trick here is to make a new database project that references the 'master' project and to make the custom dataset part of the post deployment proces of that project.

These were my 2 cents, Whatever proces you come up, above all, it must fit you and your team and hopefully support you with most of the common tasks.

share|improve this answer
add comment

Use RedGate Source Control to hook it up to your source control.

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

It will hook your SSMS directly to your source control repository and even allows for checking in static data.

Works like a charm

share|improve this answer
add comment

There's also this option: (somewhat different take that what Red-Gate does, but still does the job with little effort on your part)

http://nobhillsoft.com/Randolph.aspx

share|improve this answer
    
If you are associated with the product mentioned, you need to show that in your answer. Either way, you should attempt to put more details into your answer - what does the product do/not do, why is it great, etc. –  Max Vernon Apr 2 '13 at 14:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.