October 9, 2002 at 3:03 pm
I've inherited responsibility for a set of replicated databases with one big flaw, and need to develop a means to resolve it. Fortunately this issue arises infrequently. Here's the story ...
Multiple SQL Server 2000 SP2 servers, with publisher and distributor on Srv1, publishing database on Srv1, subscribers on Srv2, Srv3, etc. Replication is the chosen data copying methodology because not all subscribing servers are online all of the time.
Srv1 is a corporate-level server with one published database of all corporate-controlled data, such as customer lists, invoicing methods, zip codes, and other look-up tables. This database contains 93 tables that are published as one large transactional replication (1-way) publication. Push subscriptions exist for all subscribing databases.
The site-specific servers each have their own production database. All of the tables published from Srv1 are part of the production database, but there are many more tables, not replicated, in the database.
The subscribing databases have foreign keys into the replicated tables. For instance, a production-level invoice table in the subscriber references the customers table, which is replicated from the publisher, so that only valid customers can be invoiced. I'm aware of Microsoft's admonition that there should not be FKs on published tables in subscribing databases, but this system already exists as is.
Everything works fine most of the time - if corporate adds a new (or changes an existing) customer to their master database, within moments all of the subscribing databases on all of the other servers contain the new customer entry in the replicated table.
Here's the problem - corporate can delete a customer from the publishing database, even if invoices exist in any of the subscriber databases. Of course, replication then fails, and I, as DBA, must scramble to resolve the problem and get replication started again.
Does anyone have experience with this scenario, and if so, how do you handle this problem? I can kludge together a solution using triggers on the publisher calling stored procs on the subscribers to verify that no dependent records exist, but that will work only as long as the link between the publishing and subscribing servers is active. Alternatively, I can force the corporate applications to not allow deletes on the published tables.
I'd greatly appreciate your thoughts, opinions, warnings, suggestions, or solutions.
Thanks!
Steve Armistead,
Database Administration
Panther Systems Northwest, Inc.
Vancouver, WA
Steve
October 28, 2002 at 6:31 pm
This was removed by the editor as SPAM
November 1, 2002 at 4:49 pm
Stopping the deletes doesnt seem bad, though it may impact reports and apps if you need to treat them as deleted even though they still exist. Removing the foreignkeys on the subscriber doesnt seems a bad way to go either. I think checking the subscriber is only going to reduce the problem, not make it go away. What steps are you taking when the problem occurs now?
Off topic some - I'd recommend splitting that publication into two or more. Nice to be able to snapshot problem tables without sending everything over.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply