problem: i have two databases with the same structure (i mean the same tables, table columns, pks, check constraints, stored procedures, views, udfs, ..., you get the ideea...). is there a way to ensure that any schema modification to one of them will be automatically done to the other also?

for example, let's say i want to add a unique constraint in one of the databases on one of the tables. what i want is that this constraint to be added automatically to both databases or, if the constraint is not possible, to none of them.

thanks,

defiant.

Is one a development and one a master?
What I would do is create a view from the systables and compare any items from one that do not match another. This gives you a list of changes.

Making any changes automatically to the other schema could cause probles if you are adding a PK and the values are not unique. The schema can be the same, but the data will not match.





Sal

sal, you say that "Making any changes automatically to the other schema could cause probles if you are adding a PK and the values are not unique. The schema can be the same, but the data will not match." in this case i don't want the pk to be added in _either_ databases. i want the database's structure to be exactly equivalent all the time.

you say that "What I would do is create a view from the systables and compare any items from one that do not match another. This gives you a list of changes." ok, let's supose that i'm giving up the ideea of not manually making a change twice. than i need to know all the differences, not only what tables and columns, but all, including encrypted stored procedures, constraint names and content, etc. i will have to see if this is possible... but i would prefer to make the changes and the change to be automatically done in the 2nd database :).

defiant.

defiant,

There is a utility named 'dbsynchrocompsetup.exe' which gives you the differences between the two databases including all objects and even users. You can even select the option for that. But you have to download this from net.It's free to use for 30 days evaluation.

Now, regarding your problem of automating changes in second database, i donn't have anything to say as if data is same then it's very easy otherwise not. You can write stored procedure in which you will pass the query(changes) and then run on one database and then on second . if it runs well then ok otherwise undo the changes in both database. For that you have to keep the stored procedure in master database.

sumit