Re: Use Replication or Not
Paul Ibison
in replication you can select individual tables and horizontally or
vertically partition them. You can use DTS to create a 'transformable
subscription' if the mapping to the subscriber is not too straightforward,
or alternatively you can use indexed views on the publisher to 'modify' the
schema.
HTH,
Paul Ibison
Armaghan
If I use this approach, will SQL be smart enough to synchronize the changed data or will it overwrite the whole data everytime?
Also, should I created a pull subscription or a push one.
I will need to send modified records back to the Main database and get the modified /new records from the main databse.
Thanx for you help
Armaghan
Armaghan
If I use this approach, will SQL be smart enough to synchronize the changed data or will it overwrite the whole data everytime?
Also, should I created a pull subscription or a push one.
I will need to send modified records back to the Main database and get the modified /new records from the main databse.
Thanx for you help
Armaghan
Armaghan
If I use this approach, will SQL be smart enough to synchronize the changed data or will it overwrite the whole data everytime?
Also, should I created a pull subscription or a push one.
I will need to send modified records back to the Main database and get the modified /new records from the main databse.
Thanx for you help
Armaghan
Paul Ibison
transformable subscriptions and indexed views will preclude the option for
subscribers to update the data. To have this ability, you could use some
sort of custom approach using linked servers, DTS, triggers etc.
Another option would be to replicate the tables as they currently stand and
use views on the publisher and subscriber to present the modified data to
the users. When editing the data in the view, you have an INSTEAD OF
trigger to direct the changes to the underlying tables. This is far more
maintainable and uses the standard replication setup. You could use merge
replication for this, and pull if the subscriber is to control the
synchronization.
HTH,
Paul Ibison
Armaghan
Well, replicating the current table structure is not an option. I really have to change the schema
I liked your idea of having a more custom approach, that way I will have more control. Although I am very good with SQL Server but I never got a chance to use the Replication so I'm a newbie.
Can you please elaborate further on this issue.
I would really appreciate your help
Thanx
Paul Ibison
I'm interested in why wouldn't replicating the current schema be an option,
as from the point of view of the users all they'd see would be the views so
it would end up by far as the easiest solution. In fact to be honest I think
this may be the only reasonable solution.
I can conceive of other solutions involving linked servers and triggers, but
auditing for inserts, updates and deletes then replicating these changes to
the modified schema tables followed by applying changes to the real schema
is really not a route I would advise you to go down and you would be
creating a maintenance headache for no reason IMHO.
HTH,
Paul Ibison
Armaghan
The schema of the current databae structure we have is really primitive and there is no real data integrity whatsoever currently. We had a legacy application, built in COBOL, that was converted to VB couple of years ago but the developer did not update the schema for whatever the reason was.
I wanted to clean the system with a new database design and atleast some refrential integrity to take some load off the web app.
Since the data is so messed up and the database is kinda crap... I'm pretty much stuck here.. don't really know where to start from.
Hope you understand what im trying to do here.
Thanks for all your help.
Armaghan
Paul Ibison
sounds horribly like some of my support work :-)
In this case there is no easy solution, especially as you want to be able to
have updatable subscribers.
If you could live with RO subscribers, then transformable subscriptions or
indexed views would be the way to go.
For updatable subscribers and big schema changes, I'd recommend
restructuring the source database and recoding the VB app. This is a big hit
initially but longterm it is the best way to create a maintainable solution
IMHO.
Regards,
Paul
Armaghan
Anyhow, I got another idea i wanna bounce it off you.
What if I put a whole bunch of flags in the tables to indicate new/changed records, write a DTS Package which handles all updates, inserts n stuff and then schedule it to run every set interval.
{now the trick questions}
Will this work? How reliable is the DTS job scheduling? Is this a better way to handle replication?
I really appreciate and will appreciate your help on this matter.
Thanx
Paul Ibison
being extremely complicated, as essentially you are
recreating merge replication. Complications arising from
this scenario are:
Deletes can't be flagged, so they'd have to go into an
audit table.
Updates could conflict with other updates.
Someone could update a record on the publisher while a
subscriber deletes the same record - another type of
conflict.
Once this lot is sorted out, you'd have to translate the
change accepted into your custom table and apply it into
the real table, assuming this scenario applies to modified
schemas as before.
Regards,
Paul Ibison
Armaghan
Yea you are right, it will be complicated. Already DTS is slow, it will crawl with such complications.
But C'mon man! can't microsoft comeup with a solutions.. there has to be one.. its not something out of this world.
Looks like, I have to take matters in my own hand.
How about if I try half Replication and half DTS. Meaning try to make as minimal changes in structure as possible and use Replication for unchanged tables and DTS for the rest!
How this sounds?
Thanks again.
Paul Ibison
just to recap, you are talking about replication from
publisher to subscriber. You need to both change the
schema and also allow for updatable subscribers. In SQL
Server 2000 replication, these 2 options are incompatible.
You cannot use the updatable transactional Subscriber (two-
way transactional updates) or queued updating Subscriber
feature with transformable subscriptions (transformations
are mapped in one direction, from Publisher to Subscriber).
So, this would have to be an entirely custom solution
taking care of the issues listed in my previous posts, or
alternatively use views and 'instead of' triggers.
Regards,
Paul Ibison