Owner mapping - one to many

PaulOckenden

I'm trying to investigate whether SQL Compare will work for us.

Database A has loads of tables, half owned by [dbo] and half by [userA]. Database B is a mirror, but all of the tables are owned by [dbo].

I can't seem to find a way to compare these two database (ignoring table ownership) using SQL Compare. The owner mapping seems to be one to one, but I guess I need one to many.

Is that possible? I also looked for an 'ignore ownership' option which might also have worked, but couldn't find one.

Thanks for any help you might be able to give,

Paul.

sam.blackburn

Sounds like you need the table mappings tab, which lets you map individual tables as long as there aren't too many to click through.

Cheers,

PaulOckenden

There are literally THOUSANDS of tables!

I don't understand why I can't do it in owner mapping.

sam.blackburn

We're wondering how we could design this kind of feature - how do you think a one-to-many owner mappings feature could look in the UI?

PaulOckenden

In the bit at the bottom (unmapped stuff) you can currently select just one item from the left and one from the right and hit the map button.

I think it should allow you to select more than one owner from the source database.

e.g.
[dbo] |
[UserA] |
| [dbo]

I should be able to select all three rows, and hit map.

This would then create TWO mappings in the table above:

[dbo] -> [dbo]
[UserA] -> [dbo]

Does that make sense?

(The other option would be to go in and tinker with the saved project definition files prior to deployment - then you wouldn't have to build a UI, but not sure whether this is feasible.)

Thanks,

P.

sam.blackburn

Thanks for your help in clarifying the expected behaviour - it's tricky for us to work out what our users want!

The difficult part here is that Compare will behave unpredictably if there are tables with the same name in both schemas. For example, the source table might have a [dbo].[tableA] and a [UserA].[tableA] - we're not sure how Compare should behave in that case. Any ideas?

PaulOckenden

Well, I guess that in this instance although you could have two source tables named the same with different schemas, you'd only have one in the target database (because you only have a single schema).

So for the tables on the left hand side one would match and one would fail during comparison.

I wonder whether another way to tackle this would be to have an 'ignore schema' in the options? But with a note saying that option can only be used when table names are unique between schemas. perhaps that might be easier from a UI point of view, and also easier for people to understand.

P.

sam.blackburn

So it sounds like you want an "Ignore Owners" option, and Compare should error if there are two tables in the same database with the same name?

I think this might work, although we should think carefully before adding another option to the UI to avoid worsening the task of verifying that your options are set correctly.

We'll soon be deciding what features to work on after we release Compare 12 to the default channel, so it's really useful to know what kinds of features should be on our list.

PaulOckenden

That's great - thanks Sam.

I always work on the theory that if one customer (or potential customer) asks for something, there will usually be a load more that are too shy to ask!

P.

sam.blackburn

If you think there are others with the same needs, creating a UserVoice request would enormously help us gauge the number of users who would like this feature.