Database metadata versioning and synchronization

Question

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

Most of the time developers change their own copy of 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 tool like SQL Data Compare (from RedGate) to see which rows changed and compare by 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 [Name] 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 their reference the same Type Name).

How to compare these database in more "intelligent" way? Is there a possibility to do it without 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?

Answer 1

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.