19

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?

QFDev
  • 789
  • 1
  • 9
  • 21

7 Answers7

15

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.

RoKa
  • 1,614
  • 11
  • 16
4

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)

Carol Baker West
  • 1,616
  • 14
  • 13
3

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

druzin
  • 2,308
  • 13
  • 15
3

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.

Community
  • 1
SqlACID
  • 2,238
  • 16
  • 16
3

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.

souplex
  • 833
  • 1
  • 8
  • 10
0

My company has just developed this new tool (free) that helps you to easily extract scripts for SQL databases, can do comparison, can launch WinMerge for quickly comparing scripts to live database, and can also synch differences both updating the scripts or applying the changes to the database (except for tables, which would involve more complexity and more risks).

Servantt is the WinMerge for comparing SQL Server Databases to Version-Controlled Scripts.

It supports and encourages best-practices in software development:

  • Keeping Database objects under version-control (*)
  • Removing access rights from developers on production environments
  • DBA review of changes in procedures/views for performance bottlenecks and naming standards
  • Naming objects using fully qualified identifiers and bracketed delimiters (it fixes the CREATE PROCEDURE/VIEW/FUNCTION/etc scripts)

(*) Scripts are saved into a local folder that can be a working copy of Git, Subversion, TFS, Source Safe, or any other VCS.

Free Download: http://servantt.com

The professional version (which is still under development) will be a completely different beast - it's targeted at deployment automation (release management), for automating tasks such as updating IIS, updating Windows Services, etc.

Paul White
  • 94,921
  • 30
  • 437
  • 687
drizin
  • 125
  • 6
0

I ended up writing a tool myself.

It's available for free download -http://www.gitsql.net

I hope it helps other people who want to achieve the same end goal.

Here is an article which describes how to source control SQL Server. http://gitsql.net/documentation-04_SQL_Server_and_GIT

I've tried to make it as easy as possible. (3 screens)

  • Connect to SQL Server
  • Select objects
  • Chose folder to export to /import from

I also - accidentally - added the feature of being able to selectively chose individual objects to import - or export. Which makes it much easy whilst developing.

I would usually make a change to a stored procedure and a table, and then export those two objects to a GIT directory.

Then i use Source Tree to visually see the changes and then commit them into bitbucket if i'm happy.

LowlyDBA - John M
  • 11,059
  • 11
  • 45
  • 63
Anup Saund
  • 109
  • 3