Best way to keep track of SQL Server Changes

What would be the best practice to follow to keep track of MS SQL
server changes... Stroed procs, tables, views, triggers, indexes, DTS
and also jobs ect...

I am not quite sure how Source safe works with sql server. Any other
way to do this... Even if its manual work, its okey.. I would
appreciate if any of the DBA's let me know how they are facing this
issue...

Thanks in advance..."SQLDBA" <pg.242w@.gmail.com> wrote in message
news:1104953120.142642.99410@.z14g2000cwz.googlegro ups.com...
> What would be the best practice to follow to keep track of MS SQL
> server changes... Stroed procs, tables, views, triggers, indexes, DTS
> and also jobs ect...
> I am not quite sure how Source safe works with sql server. Any other
> way to do this... Even if its manual work, its okey.. I would
> appreciate if any of the DBA's let me know how they are facing this
> issue...
> Thanks in advance...

There's lot of information on the web - Google 'sql 2000 source control' or
something similar to find more information. You might also want to check out
the thread in this group from yesterday called 'Backing up design changes',
as well as this approach from Erland:

http://www.abaris.se/abaperls/index.html

To get started quickly, you can script your objects to files using
Enterprise Manager, check them all in to VSS, then start using source
control for all changes. You'll need to do some basic work on source control
processes, but there's lot of stuff on the web, and you may already have
something in your organization that you can re-use.

It's also very useful to have some sort of comparison tool (I've used Red
Gate's SQL Compare before) to check the differences between databases, and
synchronize them as needed.

DTS is a bit more problematic, because packages are binary objects, not text
files, but DTS does support multiple package versions anyway, so you might
just decide to keep the last 5 versions in a package or whatever.

Simon|||Thank you very much... We do have VSS but my knowledge in VSS is
limited. So I would appreciate if you could give me a drill down on how
I can set it up. Is there any way I can link these scripts to SQL
Server objects so that everytime when someone try to make any changes
to these scripts, it needs to be checked out to get modified? Because I
need to enforce a method among the developers to follow...!|||SQLDBA (pg.242w@.gmail.com) writes:
> Thank you very much... We do have VSS but my knowledge in VSS is
> limited. So I would appreciate if you could give me a drill down on how
> I can set it up. Is there any way I can link these scripts to SQL
> Server objects so that everytime when someone try to make any changes
> to these scripts, it needs to be checked out to get modified? Because I
> need to enforce a method among the developers to follow...!

There might be means to in Visual Studio.

But in my opinion, that's flawed. Checking out - and in - should be
a concious decision, and the source for the check-in should be a file,
not an object in a database.

That's the core of configuration management: if you need to change a
file, you go the version-control system and check it out, do your
change, and then check it in. If the change is not in the version-
control system, it does simply not exists. All builds and deployments
are made from the version-control system.

Of course, if there is only one development database, this policy may
be somewhat difficult to enforce, not the least if people use tools
like Query Analyzer where it's easier to load a stored procedure to
the database than saving it to disk.

In our shop there are an unknown number of development databases, so it
becomes quite apparent that there must be a central clearinghouse for
the code, and that is is SourceSafe.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you all for your input. In my situation, I just need to enforce
some good practice methodology among the developers. We already have
some third party tool (Redgate SQL compare) that does a wonderful job
in migrating changes among different databases.. This is just to keep
track of who is changing what and to keep the process going without any
flaws... So anyone has any suggestions to a situation like this?
Thanks again..!