July 9, 2008 at 6:19 am
While tinkering with replication in a test environment, I grabbed the structure of one of the internal databases to use as a basis for the experimentation.
Interestingly, it caused an error while setting up. The crux of the matter is:
1) The database has multiple schemas (dbo, secure, sensitive,general,load etc.) designed for easy separation of tasks and enabling addition of extra subtypes without having everything lost in the morass of the dbo schema.
2) In some of these schemas, tables have names identical to tables in other schemas (such that you can have a general.UserInfo and also a secure.UserInfo).
3) When attempting to replicate these, I get the error "Cannot create if this object represents an existing object in the server". I.e. the replication seems to be ignoring the fact that this is an object in a separate schema altogether.
This would be a bit of a showstopper for some future work I was considering performing (or at least it just means I'll have to use other methods, which I consider less elegant).
Am I simply missing the obvious, or is this really a shortcoming with SQL2005 replication?
Cheers,
Rich
July 9, 2008 at 6:42 am
When you are selecting your articles, you can right-click on each article and set some properties of the destination article. Included in this is the schema - it does not default to the original schema name (go figure). You can change the table name, configure if indexes and constraints are included, etc.
July 9, 2008 at 6:59 am
it IS possible to replicate (e.g. transactional) tables with different schemas, but I have also experienced problems (eg. we have tblFund as articles in 3 of our pubs). Using the SSMS GUI is often simplistic but helps you create the first pub+sub, and then you can script that (looks crude but you can tart it up to look much better and use @variables, conditionals etc).
normally one would choose DROP action if DA finds an existing table [object].
what you can then see is what the I/U/D sprocs are called and these may be clashing unless you painstakingly nudge the GUI for every article (to replace dbo with whatever)
sp_MSins_dbo
sp_MSupd_dbo
sp_MSdel_dbo
so you will also find this easier in your [favourite] editor.
You will also find that flowing the schema to SQL2005 subs is worth doing
exec sp_addarticle @schema_option = 0x8000000
HTH
Dick
July 9, 2008 at 7:09 am
Already done that, and checked that owner is the correct schema.
I've flipped through the binding and partitioning to see if that makes a difference, but alas, no difference (the tables aren't partitioned).
If you know which of the settings is the one that I need to pay attention to in the individual table by table settings, I'll be a little more selective in my choices, but the 'obvious' ones (without reading a lot more than the 'manual' wants to give me on the subject) don't seem to help.
July 9, 2008 at 7:30 am
Thanks Dick,
I was hoping to be able to avoid scripting this to get a fast resolution for testing, but it looks as though the SSMS isn't bright enough to add the articles for differing source tables in a unique fashion (even adding a random int to the end would help no end!) in the event of conflict, and insists on calling the article by exactly the same name as the basename of the table.
Grrr.. I was hoping it was just an issue of me being a little on the "mentally challenged" side today, rather than the obstinacy of a tool.
I'll use something less 'troublesome' for the tests today, and work out the scripting for the more complex DB at a later time..
Thanks all!
July 9, 2008 at 7:47 am
repl is a big subject so sometimes a simple problem statement hides lotsa details and assumptions!
- glad you've done the obvious already
supposing simple case of
sourceserver (SS)
source db (DS)
schema (KS)
table (TS)
publication (P)
Distributor (D)
targetserver (ST)
target db (DT)
schema (KT)
table (TT)
Q1 can you create a working pub that does
SS.DS.KS.TS as article in P -> D -> ST.DT.KT.TT
??
where there can happily be differences between SS/ST, DS/DT, KS/KT, TS/TT
Q2 if you can get one pub going successfully (doing the schema dance) then what happens when you try a second pub (whether same/other PS.DS as source) into ST.DT.KT2.TT target ?
Q3 or are you trying several sources into the same (KT=KT2) target ?
(is possible with non-overlapping IDENTITY ranges)
i.e. more detail please if community is to help you make progress !
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply