changes across all dbs

I need to find the best way to make changes to all metadata my databases.

1. We can do it manually :(
2. We can use merge replication (needs investigation)
3. We can write our own scripts.
search all instances for a metadata database
if db exists check to see if the change exists (ie. new constraint, new column,cell value)
if it doesn't exist ALTER TABLE
send a mail back to DBA for each change made and where

What do you think is the best way?

-Kevinyou can use 3rd party software from www.red-gate.com

change tracking for SQL Server

Has anyone come across a change tracking tool for SQL Server. Specifically
the scenario I want is the following :

A Production DB needs some modifications to its content.
This tool will copy the DB to a dev environment.
The Dev environment will be 2 copies of the DB, 1 as a control set and the
other the change DB.
The developer makes their changes in the Change DB. They test them out and
then when they decide the changes are ready to pushto production they hit
the "go" button on the tool.
The tool calculates the delta between the Control and the Change DB and then
pushes the changes to produciton

Any ideas

Thanks

s"Steve Mew" <steve_mew@.hotmail.com> wrote in message news:<wWnib.8720$843.5779@.nwrddc03.gnilink.net>...
> Has anyone come across a change tracking tool for SQL Server. Specifically
> the scenario I want is the following :
> A Production DB needs some modifications to its content.
> This tool will copy the DB to a dev environment.
> The Dev environment will be 2 copies of the DB, 1 as a control set and the
> other the change DB.
> The developer makes their changes in the Change DB. They test them out and
> then when they decide the changes are ready to pushto production they hit
> the "go" button on the tool.
> The tool calculates the delta between the Control and the Change DB and then
> pushes the changes to produciton
> Any ideas
> Thanks
> s

I'm not aware of a single tool that would do exactly what you want,
however you could start by looking at the Red Gate SQL tools
(http://www.red-gate.com), which can compare two databases and
generate scripts to synchronize them. There are similar tools from
other vendors.

Copying the databases is easily automated (backup/restore, SQLDMO,
DTS), but you might want to consider the permissions involved. Many
sites do not allow developers to backup/restore databases or push code
directly to production (although to be fair, there are certainly many
that do).

Simon|||Steve Mew (steve_mew@.hotmail.com) writes:
> Has anyone come across a change tracking tool for SQL Server. Specifically
> the scenario I want is the following :
> A Production DB needs some modifications to its content.
> This tool will copy the DB to a dev environment.
> The Dev environment will be 2 copies of the DB, 1 as a control set and the
> other the change DB.
> The developer makes their changes in the Change DB. They test them out and
> then when they decide the changes are ready to pushto production they hit
> the "go" button on the tool.
> The tool calculates the delta between the Control and the Change DB and
> then pushes the changes to produciton

For the scenario you describe it sounds like SQL Compare from Red Gate
is the tool to use. However, I have never used this tool myself.

I cannot escape to take the occassion to point out that the best strategy
would be have all your source in a version control system, and then have
controlled procedures from building update scrtips from the version-
control system.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Change to new subscriber without snapshot?

We have a nightly replication of changes to a database going across the
WAN to a server that has been unstable and will be running out of disk
space soon.
I have a new subscriber set up, and can restore a backup from the old
subscriber, so the data will be the same.
Due to politics (!), I do not have access to the publisher. Can they
script out the replication, and point it to the new server without a
new snapshot coming from the publisher? The group that controls the
publisher estimates it would take 48 hours to push a snapshot across
the WAN.
Thanks in advance.
You can do a nosync initialization. Have a look at this article for more
info:
http://www.replicationanswers.com/No...alizations.asp
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||yes, its called a no-sync subscription. You will have to make sure that the
data is in sync, otherwise your distribution agent may fail frequently. The
group that controls the publisher will have to fix this each time.
You will also have to have them auto generated the replication stored
procedures for you by using sp_enumcustomresolvers 'PublicationName'
Run this in your publication database.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mr. DOS" <mike@.mrdos.com> wrote in message
news:1116228663.708155.103080@.f14g2000cwb.googlegr oups.com...
> We have a nightly replication of changes to a database going across the
> WAN to a server that has been unstable and will be running out of disk
> space soon.
> I have a new subscriber set up, and can restore a backup from the old
> subscriber, so the data will be the same.
> Due to politics (!), I do not have access to the publisher. Can they
> script out the replication, and point it to the new server without a
> new snapshot coming from the publisher? The group that controls the
> publisher estimates it would take 48 hours to push a snapshot across
> the WAN.
> Thanks in advance.
>