Yes the directory structure looks like:
- DatabaseName
- Tables
- StoredProcs
- Views
- Triggers
- PopulationScripts
etc
A script for SP would check if SP already exists, if so, drops it and then
is followed with CREATE PROCEDURE script
and then permissions section. Add necessary "GO"es in between.
With tables, it check if table already exists by selecting from
INFORMATION_SCHEMA.TABLES and if not, creates it
With alter table scripts for ex. adding new column, check if querying
information-schema for presence of this column and if not there, creates it
With population scripts, checks if data is already in table (a single
representative record of the set for example) and if not, adds it.
This way changes to procs for example accross different branches/streams are
easily integrated.
One important rule is to have strong SQL coding standard (one field per
line, one table per line, one join per line etc) so the automatic merging is
easily done.
There is an easy way using SQL-DMO to extract all current DB objects
(scripts, tables etc) from DB and create a single SQL file per each one to
set up initial source control repository for example. Or alternatively VS
database project can be used.
These are then easily executed using command line with SQLCMD for example
and can be scripted as deployment task etc.
j.
On 1 June 2010 10:16, Wallace Turner <[email protected]> wrote:
> Could you please elaborate on
>
> * All database updates (tables, SPs, population scripts etc) are to be
> checked into source control system
>
>
>
> That is, do you have one txt file per stored proc and table definition? If
> Yes, do you simply copy the stored proc contents to a text file from VS?
>
>
>
> Wal
>
>
>
>
>
>
>
> *From:* [email protected] [mailto:
> [email protected]] *On Behalf Of *Jano Petras
> *Sent:* Tuesday, 1 June 2010 8:15 AM
> *To:* ozDotNet
> *Subject:* Re: Source control of DB scripts
>
>
>
> Greg,
>
> An approach that worked well in the past for me was:
>
> * No permission to execute DDL statements on staging / dev databases given
> to the developers. They still have full access to their local copies.
> * All database updates (tables, SPs, population scripts etc) are to be
> checked into source control system
> * All above scripts are to be re-runnable (for example a new field is to
> be added to table, the ALTER statement is condition with an IF to check if
> the field is not already in there)
> * The continuos integration server periodically (or on request) executes
> all current SQL scripts as found in repository however this is defined
> (could be a TXT file, a DB table, version numbering system etc) recreating
> the stored procedures and views and tables etc
>
>
> This way all updates are kept in repository and are 'scripted' in a way so
> the DB deployment is testable and manageable.
>
>
> j.
>
> On 31 May 2010 12:56, Alan Heywood <[email protected]> wrote:
>
> Hi Greg,
>
>
>
> I use MigratorDotNet <http://code.google.com/p/migratordotnet/> to address
> this issue. Migrator allows me to define incremental changes to the
> database using code, which is then checked into source control along with
> everything else. We have a Continuous Integration setup, and when a build
> is triggered the following occurs:
>
> 1. Code is compiled
> 2. MigratorDotNet is called, passing in a reference to the xxx.data.dll
> from our project.
> 3. Migrator uses reflection to find a list of 'migrations'. Version
> number is compared to the current one stored in a table in the database.
> If
> there are new migrations to be run then they are executed on the database.
>
> Using this approach you can create tables, define foreign key
> constraints, add new colums etc. You can also execute arbitrary SQL
> statements. This means that you can also add or modify data as part of a
> migration.
>
>
>
> Works really well for me, particularly since I would otherwise be manually
> attempting to keep > 10 databases in sync.
>
>
> Alan
>
> On 31 May 2010 11:14, Greg Keogh <[email protected]> wrote:
>
> Folks, I’m sure we’ve all had problems where multiple developers change
> SQL Server scripts and they get out of whack and waste time with stupid
> errors.
>
>
>
> I’m starting a fresh app and I thought I’d experiment with keeping scripts
> in SVN. It just means that we have to remember to always save a script to
> the source controlled file whenever it’s changed.
>
>
>
> Because scripts aren’t compiled source code, there is still the risk of
> human error in not pushing any updated script files into the DB. I was
> thinking of concocting a utility which automatically pushed changed scripts
> into the DB, but before I start fiddling I thought I’d ask about this
> subject in general first. Are there others out there who source control
> their DB scripts and have techniques for reducing human error? Or perhaps
> there are better techniques that I’ve completely overlooked.
>
>
>
> Greg
>
>
>
>
>
