sql server misc: I'm a veteran software developer who's relatively new to the world of
databases. The project I'm currently working on is growing to the
point that I'm starting to wish I had the kind of project tools that
I'm used to with languages like C++ and Java, namely compilers, "make"
utilities, source control, packaged unit tests, etc. I'm sure that
this is a subject that has an established body of opinions and
discussion, if not a consensus on what is the best way to do it. But
I haven't seen it discussed in any of the books I've read on SQL
Server so far.

Our current development environment for non-database code is Java,
using Ant for build control, and CVS for source control. We have
accumulated a fair collection of table schemas, views, stored
procedures, and configuration data, which we would like to package in
a way that makes it easily maintainable (in particular, modular), and
store it in CVS with everything else. We would like to use text file
formats wherever possible (as opposed to binary backups, etc.)

Can anyone recommend resources to go to for discussion of different
approaches for this sort of thing... how to build and manage a SQL
Server project to make it scalable and maintainable?

Thanks,

Like, John Bell, we keep all SQL scripts in source control. We run the
scripts via a custom action during installation of the server
components.

There are many configuration management considerations. Take a look at
ABAPERLS (http://www.abaris.se/abaperls/) for the approach SQL Server
MVP Erland Sommarskog uses in his organization.

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

[quoted text, click to view]

Hi

The method I prefer to maintain code with a source control environment is to
do it at individual object level. Therefore tables are scripted separately
to indexes and permissions etc. You will then be able to retrieve the
individual files and either run them separately or concatenate them into a
larger scripts. My preference is to place similar object in their own
directory as it is quite often useful to have scripts that will create all
the indexes or all the primary keys or all the foreign keys etc as separate
files, for instance you may want to populate your empty schema with data
before applying indexes.

Also see http://tinyurl.com/qkec

HTH

John


[quoted text, click to view]

Hi Allan.

I've found that managing the t-sql source code alone is often not doing the
job well enough, depending on the project. Managing release / versioning of
SQL objects in a source controller is important, but what about domain data
that lurks in the schema as well? The more meta driven (as opposed to
attribute driven) the database design is, the more important this is as the
data itself forms part of the schema (eg your customer contact info /
address info is stored in rows rather than columns).

So, you might need to take into consideration versioning some data from your
schema along with the SQL object DDL as well.

I've seen many developers struggle with this issue - they're given access to
a source code control application but no way to access domain data. They
waste valuable time developing scripts to populate domain tables with their
own interpretation of that data, often making critical mistakes & / or
different versions of the data between them. As a result, the development
process often breaks down, leading to lost time, bugs & inconsistent test
results. When component / unit testing fails, you might hear this question -
"then what data SHOULD I have developed against?" & you'll realise that it
was always as important to provide the developers with version-controlled
data along with the schema.

I'm personally an advocate for implementing fully logged backups into a
development environment because it gives you everything you could ever want
to manage the SQL side of code management in a dev project. You can roll
your environment backward / forward to any point in time & you get the added
benefit of developing deep understanding of the behavioural characteristics
of the database long before it goes into production. You learn about
important admin things such as log growth rates, restore times, capacity
planning, archive requirements etc. Often this stuff is "worked out" when
planning deployment, or even during design phase but the information gleaned
through the development phase of the SDLC is invaluable to DBAs in the field
once they take over your app.

This was just my 20c - I agree with the others, but just think you've really
got to consider managing data along with the DDL scripts for objects. You
can do this with further INSERT INTO type scripts, but this misses out on
some other useful experience you can derive from implementing full logging
dev environments.

HTH

Regards,
Greg Linwood
SQL Server MVP

[quoted text, click to view]

You may wish to look at mssqlXpress http://www.mssqlXpress.com its a
complete IDE for SQL Server development

Bryan

[quoted text, click to view]