I am pretty new to replication and have been setting it up in a test
environment using the test databases delivered during the sql install
(northwind and pubs).
I noticed that when I would setup Transactional Replicational (NON –
updateable subscriber) that the primary keys would NOT come over with tables
to the subscriber. But, if I set up Transactional Replication with
Updateable Subscriber, the primary keys would come over with the tables on
the subscriber. Am I missing something here? Or, is this indeed how it
works?
Hi Janet,
As Paul mentioned, transactional replication typically (or traditionally)
replicates the primary key as just a unique index. Assuming that you are
using a SQL2000 publisher, you can enable the 0x8000 (PKUKAsContraints)
article schema option so primary key will be replicated as primary key. The
behavior that you saw for updateable subscriber was our attempt to
"out-smart" the user as updateable subscriptions requires primary key
constraint (not just the index) at the subscriber to work properly.
-Raymond
"Janet" <Janet@.discussions.microsoft.com> wrote in message
news:A81443F2-9BEF-40B2-9236-3D91DD44D9BC@.microsoft.com...
>I am pretty new to replication and have been setting it up in a test
> environment using the test databases delivered during the sql install
> (northwind and pubs).
> I noticed that when I would setup Transactional Replicational (NON -
> updateable subscriber) that the primary keys would NOT come over with
> tables
> to the subscriber. But, if I set up Transactional Replication with
> Updateable Subscriber, the primary keys would come over with the tables on
> the subscriber. Am I missing something here? Or, is this indeed how it
> works?
>
>
Showing posts with label transactional. Show all posts
Showing posts with label transactional. Show all posts
Monday, March 26, 2012
Primary Keys and Transactional Repl
I have read through the materials and still don't feel like I have a
definitive answer to the following: does (one way) transactional replication
require a primary key on all articles/tables?
THx.
Abosolutely!
Hilary
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:BB9CFBC7-A26E-4152-978E-AE4BA56F47A4@.microsoft.com...
>I have read through the materials and still don't feel like I have a
> definitive answer to the following: does (one way) transactional
> replication
> require a primary key on all articles/tables?
> THx.
|||ANY transactional replication(1 way, immediate,queued) requires PK's
definitive answer to the following: does (one way) transactional replication
require a primary key on all articles/tables?
THx.
Abosolutely!
Hilary
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:BB9CFBC7-A26E-4152-978E-AE4BA56F47A4@.microsoft.com...
>I have read through the materials and still don't feel like I have a
> definitive answer to the following: does (one way) transactional
> replication
> require a primary key on all articles/tables?
> THx.
|||ANY transactional replication(1 way, immediate,queued) requires PK's
Primary Keys - Not Replicating
It appears that when performing a push replicaton of the transactional type
that the primary keys are not being created. In table design they show up as
clustered unique indexes type = index instead of unique constraints/ type =
primary
the clustered unique indexes are really the functionally equivalent of
primary keys (with the exception that a unique index will allow a single
null).
To replicate PK's as PK's right click on publication your publication,
select properties, click on the articles tab, and in the browse button,
click the three ellipses to the right of the article. In the snapshot tab,
click Include DRI.
"scovillesm" <scovillesm@.discussions.microsoft.com> wrote in message
news:4667423F-9248-446C-8EAB-04A67AC8E78E@.microsoft.com...
> It appears that when performing a push replicaton of the transactional
type
> that the primary keys are not being created. In table design they show up
as
> clustered unique indexes type = index instead of unique constraints/ type
=
> primary
that the primary keys are not being created. In table design they show up as
clustered unique indexes type = index instead of unique constraints/ type =
primary
the clustered unique indexes are really the functionally equivalent of
primary keys (with the exception that a unique index will allow a single
null).
To replicate PK's as PK's right click on publication your publication,
select properties, click on the articles tab, and in the browse button,
click the three ellipses to the right of the article. In the snapshot tab,
click Include DRI.
"scovillesm" <scovillesm@.discussions.microsoft.com> wrote in message
news:4667423F-9248-446C-8EAB-04A67AC8E78E@.microsoft.com...
> It appears that when performing a push replicaton of the transactional
type
> that the primary keys are not being created. In table design they show up
as
> clustered unique indexes type = index instead of unique constraints/ type
=
> primary
Friday, March 23, 2012
Primary Key Violation - Transactional Replication
Hi All,
I have setup a transactional replication between 2 SQL 2005 servers.
Unfortunately, I am getting the error listed below:
Replication-Replication Distribution Subsystem: agent
JFCIS3TRM02-JFJDAT-JFJDAT_PUBLICATION-JFCIS3TRM01-13 failed.
Violation of PRIMARY KEY constraint 'ARSTRUN_KEY_0'. Cannot insert duplicate
key in object 'dbo.ARSTRUN'.
Is this error being raised because I haven't enabled automatic range
management? If so, how would I fix this error?
Regards,
JN
Hi Paul,
The type of transactional replication setup is just the plain one, not the
one with updatable subscription.
I am not sure whether it is nosync or automatic as I used the wizard and I
didn't recall being asked for those settings.
Regards,
JN
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eWzOLQD2HHA.4680@.TK2MSFTNGP03.phx.gbl...
>I really need to know what type of transactional replication setup you have
>configured - plain, updatable (immediate or queued) and nosync or
>automatic...
> Cheers,
> Paul Ibison
>
|||That could be possible since I have created an ODBC connection to the
replicated database which an end user can select from a drop down list when
they open the application.
What do you suggest I do to get the two databases synchronized again?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OjdWiMF2HHA.5164@.TK2MSFTNGP05.phx.gbl...
> OK - in that case my suspicion is that someone has entered a row on the
> subscriber. Is that possible? In this plain transactional case the
> subscriber data is supposed to be read only and only changed via the
> distribution agent.
> HTH,
> Paul Ibison
>
I have setup a transactional replication between 2 SQL 2005 servers.
Unfortunately, I am getting the error listed below:
Replication-Replication Distribution Subsystem: agent
JFCIS3TRM02-JFJDAT-JFJDAT_PUBLICATION-JFCIS3TRM01-13 failed.
Violation of PRIMARY KEY constraint 'ARSTRUN_KEY_0'. Cannot insert duplicate
key in object 'dbo.ARSTRUN'.
Is this error being raised because I haven't enabled automatic range
management? If so, how would I fix this error?
Regards,
JN
Hi Paul,
The type of transactional replication setup is just the plain one, not the
one with updatable subscription.
I am not sure whether it is nosync or automatic as I used the wizard and I
didn't recall being asked for those settings.
Regards,
JN
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eWzOLQD2HHA.4680@.TK2MSFTNGP03.phx.gbl...
>I really need to know what type of transactional replication setup you have
>configured - plain, updatable (immediate or queued) and nosync or
>automatic...
> Cheers,
> Paul Ibison
>
|||That could be possible since I have created an ODBC connection to the
replicated database which an end user can select from a drop down list when
they open the application.
What do you suggest I do to get the two databases synchronized again?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OjdWiMF2HHA.5164@.TK2MSFTNGP05.phx.gbl...
> OK - in that case my suspicion is that someone has entered a row on the
> subscriber. Is that possible? In this plain transactional case the
> subscriber data is supposed to be read only and only changed via the
> distribution agent.
> HTH,
> Paul Ibison
>
Wednesday, March 21, 2012
Primary Key in Transactional Replication
How I can publish tables wihout primary key in transactiona Replication?
Dave,
you can't
If at all possible, just add the PK.
As a workaround, if schema changes are not allowed, I suppose you could
create an indexed view and transactionally replicate this, but this is not a
nice solution.
Equally not-nice is to use a trigger to synchronize another table (which
itself has a PK) and replicate that.
Finally, you could use snapshot or merge replication (EXCHANGETYPE to force
downloads only), but these may not be appropriate due to their nature of
replication.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Dave,
you can't
If at all possible, just add the PK.As a workaround, if schema changes are not allowed, I suppose you could
create an indexed view and transactionally replicate this, but this is not a
nice solution.
Equally not-nice is to use a trigger to synchronize another table (which
itself has a PK) and replicate that.
Finally, you could use snapshot or merge replication (EXCHANGETYPE to force
downloads only), but these may not be appropriate due to their nature of
replication.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Monday, March 12, 2012
Primary Key
Hello,
I'm trying to set a transactional replication. Everything is working very
well, but i don't know why primary key and default values are not transfered
to the subscribers.
Any idea !!!!!!!
Thanks
Matthew,
have a look at the article properties (elipsis button). You can select to
transfer DRI, in which case the PKs will be created at the subscriber,
otherwise there is a unique index created instead.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Paul,
Thanks a lot for your answer.
I'm very new in using replication. I didn't know exactly what you meant with
DRI, but i repeated the whole process and checked the article properties. I
have Still the same problem. Everything is transfered to the subscribers,
even indexes, but when i right-click on my table and select the "Design
Table", I can not see that "Primary Key" icon beside my key and none of my
"Default Values" is set.
Even in that "Default Table Article Properties" wizard, I see that there is
written "Indexes for primary keys are always copied", and it doesn't even let
you mark or unmark it, but still it doesn't work.
Please help !!!!!!!!!!!!!!
THanks again,
Matthew
"Paul Ibison" wrote:
> Matthew,
> have a look at the article properties (elipsis button). You can select to
> transfer DRI, in which case the PKs will be created at the subscriber,
> otherwise there is a unique index created instead.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Matthew,
have a look at the article properties (elipsis button on the publication
properties). On the snapshot tab, select the 'Include declared referential
integrity' option. After that, you'll need to reinitialize to have the whole
table definition go down to the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Paul,
Thanks a lot for your help. It worked this time. After your first answer i
selected all those options on the snapshot tab. I'm positive that i did it,
but unbelievably, after your second answer I wanted to double check it that i
saw only this one ,"Include Declared Referential Integrity", was unmarked.
Anyways, It's fine now.
I have another problem now. I don't know why after transfering stored
procedures it puts SP names in a "" at the subscribers.
Thanks again,
Matthew
"Paul Ibison" wrote:
> Matthew,
> have a look at the article properties (elipsis button on the publication
> properties). On the snapshot tab, select the 'Include declared referential
> integrity' option. After that, you'll need to reinitialize to have the whole
> table definition go down to the subscriber.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
I'm trying to set a transactional replication. Everything is working very
well, but i don't know why primary key and default values are not transfered
to the subscribers.
Any idea !!!!!!!
Thanks
Matthew,
have a look at the article properties (elipsis button). You can select to
transfer DRI, in which case the PKs will be created at the subscriber,
otherwise there is a unique index created instead.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Paul,
Thanks a lot for your answer.
I'm very new in using replication. I didn't know exactly what you meant with
DRI, but i repeated the whole process and checked the article properties. I
have Still the same problem. Everything is transfered to the subscribers,
even indexes, but when i right-click on my table and select the "Design
Table", I can not see that "Primary Key" icon beside my key and none of my
"Default Values" is set.
Even in that "Default Table Article Properties" wizard, I see that there is
written "Indexes for primary keys are always copied", and it doesn't even let
you mark or unmark it, but still it doesn't work.
Please help !!!!!!!!!!!!!!
THanks again,
Matthew
"Paul Ibison" wrote:
> Matthew,
> have a look at the article properties (elipsis button). You can select to
> transfer DRI, in which case the PKs will be created at the subscriber,
> otherwise there is a unique index created instead.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Matthew,
have a look at the article properties (elipsis button on the publication
properties). On the snapshot tab, select the 'Include declared referential
integrity' option. After that, you'll need to reinitialize to have the whole
table definition go down to the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Paul,
Thanks a lot for your help. It worked this time. After your first answer i
selected all those options on the snapshot tab. I'm positive that i did it,
but unbelievably, after your second answer I wanted to double check it that i
saw only this one ,"Include Declared Referential Integrity", was unmarked.
Anyways, It's fine now.
I have another problem now. I don't know why after transfering stored
procedures it puts SP names in a "" at the subscribers.
Thanks again,
Matthew
"Paul Ibison" wrote:
> Matthew,
> have a look at the article properties (elipsis button on the publication
> properties). On the snapshot tab, select the 'Include declared referential
> integrity' option. After that, you'll need to reinitialize to have the whole
> table definition go down to the subscriber.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
Monday, February 20, 2012
Preventing subscrition expiration?
How can I prevent a subscription to a transactional publication from expiring?
Here's a brief overview of our situation. We have 2 db's on one server. One
of the db's is used for update, the other is used for search and retrieval
(read-only). Both db's have tables that are fulltext indexed. We would like
to be able to set up transactional replication so that updates can be applied
"on demand" without requiring that the indexes be rebuilt.
We've been successful at getting transactional replication to support this
scenario ... for a few days at a time! We have the need to go an extended
period of time (up to 2-3 weeks in extreme cases) without synchronizing,
however, it appears that the subscription expires and requires
re-synchronizing after 3 days.
The db that is getting updated is not very large (500mb) and only one user
performs updates on it. We have plenty of storage available, so space is not
a problem. I just need to figure out how to prevent to subscription from
expiring so that we can truly replicate "on-demand".
Thanks
MT
P.S. – we've ordered the recommended book on replication but it is not
scheduled to arrive until next week.
Hi MT,
If you right-click the publication in EM, you will see an option to change
the subscription expiration properties. You can set the hours to 32767
(about 3.7 years). The other option is to select the "Subscriptions never
expire, but they can be deactivated until they are reinitalized." This can
lead to other issues and I would opt for the former and not the later.
-Jose
"MTurner" <MTurner@.discussions.microsoft.com> wrote in message
news:F0F83E80-607C-482F-B3F5-CDCB797C63B2@.microsoft.com...
> How can I prevent a subscription to a transactional publication from
> expiring?
> Here's a brief overview of our situation. We have 2 db's on one server.
> One
> of the db's is used for update, the other is used for search and retrieval
> (read-only). Both db's have tables that are fulltext indexed. We would
> like
> to be able to set up transactional replication so that updates can be
> applied
> "on demand" without requiring that the indexes be rebuilt.
> We've been successful at getting transactional replication to support this
> scenario ... for a few days at a time! We have the need to go an extended
> period of time (up to 2-3 weeks in extreme cases) without synchronizing,
> however, it appears that the subscription expires and requires
> re-synchronizing after 3 days.
> The db that is getting updated is not very large (500mb) and only one user
> performs updates on it. We have plenty of storage available, so space is
> not
> a problem. I just need to figure out how to prevent to subscription from
> expiring so that we can truly replicate "on-demand".
> Thanks
> MT
> P.S. - we've ordered the recommended book on replication but it is not
> scheduled to arrive until next week.
>
|||You'll also need to change the transaction retention
period, and the history retention period.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks - I think this is the piece of the puzzle that I've been missing.
I hate to ask but I'm not finding anything in books on-line - how do you
change the retention periods?
Thanks again
"Paul Ibison" wrote:
> You'll also need to change the transaction retention
> period, and the history retention period.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Right-click on the replication monitor and select the distributor
properties - you'll see the settings there.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Here's a brief overview of our situation. We have 2 db's on one server. One
of the db's is used for update, the other is used for search and retrieval
(read-only). Both db's have tables that are fulltext indexed. We would like
to be able to set up transactional replication so that updates can be applied
"on demand" without requiring that the indexes be rebuilt.
We've been successful at getting transactional replication to support this
scenario ... for a few days at a time! We have the need to go an extended
period of time (up to 2-3 weeks in extreme cases) without synchronizing,
however, it appears that the subscription expires and requires
re-synchronizing after 3 days.
The db that is getting updated is not very large (500mb) and only one user
performs updates on it. We have plenty of storage available, so space is not
a problem. I just need to figure out how to prevent to subscription from
expiring so that we can truly replicate "on-demand".
Thanks
MT
P.S. – we've ordered the recommended book on replication but it is not
scheduled to arrive until next week.
Hi MT,
If you right-click the publication in EM, you will see an option to change
the subscription expiration properties. You can set the hours to 32767
(about 3.7 years). The other option is to select the "Subscriptions never
expire, but they can be deactivated until they are reinitalized." This can
lead to other issues and I would opt for the former and not the later.
-Jose
"MTurner" <MTurner@.discussions.microsoft.com> wrote in message
news:F0F83E80-607C-482F-B3F5-CDCB797C63B2@.microsoft.com...
> How can I prevent a subscription to a transactional publication from
> expiring?
> Here's a brief overview of our situation. We have 2 db's on one server.
> One
> of the db's is used for update, the other is used for search and retrieval
> (read-only). Both db's have tables that are fulltext indexed. We would
> like
> to be able to set up transactional replication so that updates can be
> applied
> "on demand" without requiring that the indexes be rebuilt.
> We've been successful at getting transactional replication to support this
> scenario ... for a few days at a time! We have the need to go an extended
> period of time (up to 2-3 weeks in extreme cases) without synchronizing,
> however, it appears that the subscription expires and requires
> re-synchronizing after 3 days.
> The db that is getting updated is not very large (500mb) and only one user
> performs updates on it. We have plenty of storage available, so space is
> not
> a problem. I just need to figure out how to prevent to subscription from
> expiring so that we can truly replicate "on-demand".
> Thanks
> MT
> P.S. - we've ordered the recommended book on replication but it is not
> scheduled to arrive until next week.
>
|||You'll also need to change the transaction retention
period, and the history retention period.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks - I think this is the piece of the puzzle that I've been missing.
I hate to ask but I'm not finding anything in books on-line - how do you
change the retention periods?
Thanks again
"Paul Ibison" wrote:
> You'll also need to change the transaction retention
> period, and the history retention period.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Right-click on the replication monitor and select the distributor
properties - you'll see the settings there.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Subscribe to:
Posts (Atom)