Database metadata versioning and synchronization

Our application's DB (SQL Server 2005) has tables for an application's metadata. Changes to these metadata tables (I mean insert/update/delete) can be done via GUI (not using SSMS).

Most of the time the developers change their own copy of the database. The question is - how to "merge" these changes to metadata records into one? The problem is that there are no "update" scripts and that rows with the same primary key can mean different things. One possible way is to use a tool like SQL Data Compare (from RedGate) to see which rows changed and compare them by the unique key. The problem can be better understood by example:

CREATE TABLE [dbo].[Type](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Type] [nvarchar](50) NOT NULL,
     CONSTRAINT [PK_Type] PRIMARY KEY CLUSTERED ([ID] ASC)
    ) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [Type] ON [dbo].[Type] ([Type] ASC)

CREATE TABLE [dbo].[Form](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [TypeID] [int] NOT NULL,
    CONSTRAINT [PK_Form] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [Nam开发者_如何学运维e] ON [dbo].[Form] ([Name] ASC)

ALTER TABLE [dbo].[Form]  WITH CHECK ADD  CONSTRAINT [FK_Form_Type] FOREIGN KEY([TypeID]) REFERENCES [dbo].[Type] ([ID])
ALTER TABLE [dbo].[Form] CHECK CONSTRAINT [FK_Form_Type]

First DB contains:

Type

1, 'First type'

2, 'Second type'

Form

1, 'First form', 1

2, 'Second form', 2

Second DB contains:

Type

1, 'First type'

2, 'Third type'

3, 'Second type'

Form

1, 'First form', 1

2, 'Second form', 3

Table "Form" foreign key to table "Type" are different but logically they are the same (as they reference the same Type Name).

How can we compare these databases in a more "intelligent" way? Is there any possibility to do it without the Red Gate (or any other 3rd party) tools? Also, how to version metadata when it is being changed not with "update" scripts but via GUI?

You've just run into one of several problems with using meaningless primary keys. If the Name of the type identifies it, then that should at the very least have a unique index on it. You could then use that to do your merging and it could be automated instead of relying on human intervention to determine that 2=3.

Even if you want to use a surrogate key, your tables should still generally have some sort of identifying column(s) that relate to the real world.

If you already have a way to identify the rows across databases then you should be able to use those identifiers with Red Gate's SQL Data Compare (I believe that it can use unique indexes instead of the PK as an option).

Alternatively, you could use SSIS to point at two databases and bring them in sync. That would allow for the handling of more complex cases where you're not simply making one database like the other, but actually merging differences. You still need to handle conflict resolution (easier to do in SSIS than the Red Gate tool IMO), but that is all dependent on your own business rules as to which database is the "master", which items can have all children from both databases simply added to each other instead of replacing items, etc.