Schema vs Ownership comparison.

Jamie Clayton

G'day,

Just stuck trying to get SQL Compare to work under the following senario.

1. Created a blank database.
2. Compared and existing database to the blank, to generate a "duplicated" script.
3. In the blank database created a new schema call "JenasysDesign".
4. Edited the script in step two, to replace [dbo] with [JenasysDesign].
5. Ran the script on the blank database to populate it.
6. Re-ran SQL compare.

Expected Results
Expected to see Matches, but with just a schema difference against all objects.

Result
Most of the objects in the original database [dbo] schema are shown as not existing in the new database. All the objects in the new database are not showing as different in the original database. In other words, it looks almost exactly the same as the original comparison to a blank database.

Question
1. Is this a bug in Compare?
2. Is this a "feature" of schemas, due to the change in the definition by Microsoft.
3. Have I got a design problem with my use of schemas? I'm basically trying to re-use an ISP database (single) by providing separation of databases, via use of schema's.

Reviewed the User/Schema mapping to confirm [dbo] in original matched [JenasysDesign] in the second database.

I'm aware that there is a difference between SQL 2000 and SQL 2004 in the architecture of Schemas/Users/Ownership, but I'm confused as to why my results are not as expected.

Brian Donahue

Hello Jamie,

SQL Compare still treats schema and ownership the same way. So an object called [x]. is not the same object as [y].. They are two different objects and will not be compared. One possible way around this is the 'map owners' option on the bottom-left of SQL Compare's project screen. This would allow you to effectively say [x]=[y] for the basis of the comparison. Hopefully this works for you.

Jamie Clayton

G'day Biran,

:oops: I think it was a little unclear in my post, but I did try the [dbo] - [JenasysDesign] map owners. However behind the scenes dbo/Jenasysdesign had different users (=owners in SQL Compare - my assumption). This didn't work for me. All I can think is that there were different scheme/user maps behind the two.

Anyhow, if your correct and [x].table <> [y].table, then shouldn't that be displayed in Compare 5.2's results screen. At the moment [y].table doesn't get shown at all. Can you confirm this, or is it a PEBKAC issue.

Jamie Clayton

Brian,

Sorry about the typo with your name. :oops:

JonathanWatts

Hi Jamie,

I have been trying to reproduce your problem using the method you describe.

At the moment [y].table doesn't get shown at all

Unfortunately I cannot cause the failure you describe, all the objects in the script that I ran at step 5 do appear in the comparison. They all have a status of identical after I set up the dbo->JenasysDesign mapping.

I take that you are using an original db on a SQL 2000 server and a blank db on a SQL 2005 server?

The only thing that I think of is that mappings should be set up in both directions, i.e. in db1 dbo should be mapped to JenasysDesign and in db2 JenasysDesign should be mapped to dbo. Therefore you should create a user in you original 2000 db called JenasysDesign and map it to dbo in the blank database.

Hope this helps,

Jonathan

Jamie Clayton

G'day Jonathan,

Both of the databases I'm testing are hosted in SQL 2005 Standard edition.

JonathanWatts

Jamie,

I amended my test case to be only on SQL 2005 dbs, but unfortunately I still cannot recreate the issue. I take that it is effecting all types of objects; tables, procs and function etc?

If it possible, can you please email a copy of your database or an example of the problem to jonathan [dot] watts [at] red-gate [dot] com and I will be able to have a closer look at your problem.

Regards,

Jonathan

Jamie Clayton

No problem, just give me a few days, to get the scripts together. I've got an application error to resolve in some of our software first.

Jamie Clayton

Ok here is a simple example

CREATE LOGIN [Jenasys] WITH PASSWORD=N'AshesTestMatch', DEFAULT_DATABASE=DB_2, DEFAULT_LANGUAGE=[British], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO

USE [DB_1]
GO
/****** Object:  User [dbo]    Script Date: 11/17/2006 11:17:07 ******/
GO
CREATE USER [dbo] FOR LOGIN [jenasys] WITH DEFAULT_SCHEMA=[dbo]

USE [DB_2]
GO
/****** Object:  User [jenasys]    Script Date: 11/17/2006 11:17:31 ******/
GO
CREATE USER [jenasys] FOR LOGIN [jenasys] WITH DEFAULT_SCHEMA=[dbo]

If I use SQL Compare against these two database the program doesn't seem to understand the difference between the user/role/schema components and a sync will fall over.

Does this make sense? I'm getting this a lot with clients as the setup database inconsistently via the IDE (aka IT Admin who have been given control of SQL Server).