June 11, 2003 at 9:09 am
I am trying to setup a replication method between 5 servers, called csprod, csqa, cspreprod, csmast, and csdev.
The issue that I have is that most of the tables that need to be replicated do not have primary keys defined, and the database developer's will not define primary keys, so standard replication through publishing and subscribing is out of the question.
What I'd like to do is put a system of triggers in place, where when a row is inserted or updated on csprod, it will update the other servers. Any suggestions on how to do this, or any better methods?
Thanks,
Scott
June 12, 2003 at 3:04 am
Although I do use replication a great deal we do have a situation where we use DTS to copy data from one server to another.
You can also use log shipping i.e. back up your transaction log off one machine, x-copy the logs to the other servers and restore.
The problem you face is if there are modifications going on at the target servers.
Why won't your developers use primary keys? They are pretty fundamental RDBMS stuff!
I can understand dropping them and recreating them either side of a bulk insert but not to have them at all......?
June 12, 2003 at 4:51 am
You can still use merge replication, which actually uses a trigger system to keep track of changes. PK are not required for merge.
[font="Verdana"]Markus Bohse[/font]
June 12, 2003 at 6:05 am
You could consider using the triggers to perform the remote updates. To do this you can look at building distributed transactions to perform the actual updates. Remember to set xact_abort option on if trying this method. DTs require RPC which may make the overally transaction quite slow.
Best advice Build the primary keys and then go for DTS or Transactional Replication.
What are the possible benefits of not creating the PKs????????
June 12, 2003 at 6:32 am
Only transactional replication must have primary keys in all the published tables. But you can still use merge or snapshot.
June 12, 2003 at 6:42 am
Hi racosta,
quote:
Only transactional replication must have primary keys in all the published tables. But you can still use merge or snapshot.
are there any pitfalls when implementing merge replication?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 12, 2003 at 6:48 am
The main pitfall that springs to mind is making sure that unique id's on each machine do not overlap and that you have a strategy to cope with replication conflicts in the event that they do.
I did quite a bit of research when I first got into replication and decided that I was going to leave merge replication until I absolutely had to deal with it.
June 12, 2003 at 6:51 am
Hi Dave,
quote:
The main pitfall that springs to mind is making sure that unique id's on each machine do not overlap and that you have a strategy to cope with replication conflicts in the event that they do.I did quite a bit of research when I first got into replication and decided that I was going to leave merge replication until I absolutely had to deal with it.
what did you do?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 12, 2003 at 6:59 am
quote:
what did you do?
We had a staging database and a live database. In effect the live database was almost totally read-only and therefore I used continuous transactional replication with push subscribptions.
As we were effectively installing a setup and forget system we didn't want to touch merge replication because the customer would not have the knowledge necessary to look after replication conflicts.
June 12, 2003 at 9:18 am
quote:
We had a staging database and a live database. In effect the live database was almost totally read-only and therefore I used continuous transactional replication with push subscribptions.As we were effectively installing a setup and forget system we didn't want to touch merge replication because the customer would not have the knowledge necessary to look after replication conflicts.
Was the server down for some time?
I'd like to ask a few questions on this tomorrow.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 12, 2003 at 9:30 am
I'm the server administrator and responsible overall for making sure the website is available, and don't have the authority to setup primary keys or make any database structure changes unfortunately. I don't know why the person won't implement PK's or some other unique fields.
I setup a trigger based system in my test lab for inserts, but I can't set it up for updates since very few of the tables have a column with unique values that I can reliably query against to see if the other fields in that record changed. Overall, it's probably one of the worst db designs I've seen.
I was unaware that merge replication didn't require PK's, so I'll investigate that since the remote db's should not be changed unless they are failed over to when the main production server is down. I looked at snapshot replication and log shipping, but some of the remote db's are across slow wan links that are already heavily utilized.
Thanks for all the input! I'll give a summary of what I come up with!
Scott
June 12, 2003 at 11:41 am
BTW...Merge replication uses a "primary key" of sorts: It will create a GUID field of type "uniqueidentifier" on each table if none exists, and it will create a PK on that GUID column, or a unique index if a PK already exists on another column.
-Dan
-Dan
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply