|
|
You're in the
sql server replication
group:Unique Constraints and Replication with Purging
sql server replication:
transactional data, and one for reporting data. In the transactional database we will allow data purging via a stored proc. We will not replicate the deletes caused by the purge stored proc to the reporting database. This has already been tested and it works fine. However, we do have an issue with various unique constraints in the reporting database causing replication to fail because duplicate data is allowed in the transactional database because of purging. Is it standard practice to just remove all the unique constraints from the reporting database after the snapshot has been completed? Is there a way to not bring unique constraints over in the snapshot to begin with? Thanks, Mark
Mark,
have a look at the @schema_option argument in sp_addarticle. It finely controls what is taken across in the snapshot and there is a value for unique keys. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
Either modify your insert logic to do an existence check before doing the
insert, or use the continue on data consistency error. Note that you have to be a little careful here otherwise your reporting database will get quite out of sync. Basically the same insert or an insert with the same key value is coming across twice. If it is the same row you are ok, if it has the same key data, but different non key values, you have to ensure that this meets your business case as you will be throwing away the new values. -- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "mrprice" <mrprice@discussions.microsoft.com> wrote in message news:B83C7C10-7F7A-48E4-9FCE-254160971E68@microsoft.com... > We're using transactional replication to create two databases, one for > transactional data, and one for reporting data. In the transactional > database we will allow data purging via a stored proc. We will not > replicate > the deletes caused by the purge stored proc to the reporting database. > This > has already been tested and it works fine. However, we do have an issue > with > various unique constraints in the reporting database causing replication > to > fail because duplicate data is allowed in the transactional database > because > of purging. > > Is it standard practice to just remove all the unique constraints from the > reporting database after the snapshot has been completed? Is there a way > to > not bring unique constraints over in the snapshot to begin with? > > Thanks, > Mark >
Paul,
I looked at this but I can't seem to figure out the correct value. Right now the value being passed for tables is 0x00000000000000F3. Basically all I want to replicate is the primary key for each table. I don't need DRI, triggers, or other constraints replicated. Thanks,
Hilary,
Thanks for response. The unique contraints I mention here are not key values. The are simply constraints, which become indexes at the sunscriber, on non primary key fields. This is why I thought it would be better to simply remove them via a post snapshot script. Thanks, Mark P.S. I can't tell you how much of a help your SQL 2000 replication book has been to me. Thanks! [quoted text, click to view] "Hilary Cotter" wrote:
> Either modify your insert logic to do an existence check before doing the > insert, or use the continue on data consistency error. Note that you have to > be a little careful here otherwise your reporting database will get quite > out of sync. Basically the same insert or an insert with the same key value > is coming across twice. If it is the same row you are ok, if it has the same > key data, but different non key values, you have to ensure that this meets > your business case as you will be throwing away the new values. > > -- > Hilary Cotter > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > > "mrprice" <mrprice@discussions.microsoft.com> wrote in message > news:B83C7C10-7F7A-48E4-9FCE-254160971E68@microsoft.com... > > We're using transactional replication to create two databases, one for > > transactional data, and one for reporting data. In the transactional > > database we will allow data purging via a stored proc. We will not > > replicate > > the deletes caused by the purge stored proc to the reporting database. > > This > > has already been tested and it works fine. However, we do have an issue > > with > > various unique constraints in the reporting database causing replication > > to > > fail because duplicate data is allowed in the transactional database > > because > > of purging. > > > > Is it standard practice to just remove all the unique constraints from the > > reporting database after the snapshot has been completed? Is there a way > > to > > not bring unique constraints over in the snapshot to begin with? > > > > Thanks, > > Mark > > > >
Mark,
this should be what you need: @schema_option = 0x8083 The options I'm including are: 0x01 Generates the object creation (CREATE TABLE, CREATE PROCEDURE, and so on). This value is the default for stored procedure articles. 0x02 Generates custom stored procedures for the article, if defined. 0x80 Includes declared referential integrity on the primary keys. 0x8000 Replicates primary key and unique keys on a table article as constraints using ALTER TABLE statements. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
Paul,
I think I should better explain. I have a table with three fields. alertTypeID int IDENTITY (1, 1) NOT NULL name nvarchar(50) NOT NULL warningFl bit NOT NULL The second field "name" has a UNIQUE constraint, CONSTRAINT [AKalertType_name] UNIQUE NONCLUSTERED. As we are doing data purging on the source database, there is always the remote possibility that an entry in the "name" field could be duplicated. If I allow the UNIQUE index to be created at the subscriber, replication will stop if this scenario occurs. How might I either 1) prevent the UNIQUE index from being creating during the snapshot, 2) remove the UNIQUE index after the snapshot has completed, or 3) prevent replication from stopping when it encounters such and error? Thanks,
(1) To prevent the unique constraint, have a look at sp_addarticle
(@schema_option) as in my previous post. (2) to remove the constraint after the table has been created you can use a post-snapshot script, or sp_addscriptexec (3) The SKIPERRORS parameter (provided you know the error number) will allow replication to continue after such errors. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
Paul,
Thanks! #1 worked perfectly. Mark [quoted text, click to view] "Paul Ibison" wrote:
> (1) To prevent the unique constraint, have a look at sp_addarticle > (@schema_option) as in my previous post. > (2) to remove the constraint after the table has been created you can use a > post-snapshot script, or sp_addscriptexec > (3) The SKIPERRORS parameter (provided you know the error number) will allow > replication to continue after such errors. > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com > (recommended sql server 2000 replication book: > http://www.nwsu.com/0974973602p.html) > >
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |