June 6, 2012 at 10:11 am
Hello Guys,
I setup transactional replication and come to konw that the table with primary key is only accepted for Transactional replication.
I dont konw why.. Can anyone help me to understand ?
June 6, 2012 at 3:44 pm
It provides a way for the the replication engine to identify a row unambiguously. Unlike unique keys, primary keys do not allow NULLs. Why do you have tables with no primary key? Some would argue that a table without a primary key is not really a table.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2012 at 11:13 pm
jitendra.padhiyar (6/6/2012)
Hello Guys,I setup transactional replication and come to konw that the table with primary key is only accepted for Transactional replication.
I dont konw why.. Can anyone help me to understand ?
The reason is, on subscriber rows updated/deleted individually one-by-one with the help of thier identity coloumn.
For example:
If you run DELETE Product WHERE Price > 1000 on publisher.
Assume 10 rows are deleted by the above command
But on subscriber 10 delete statements would be executed as follows:
DELETE Product WHERE pkcol = @val
You can see this by running Profiler on subsriber.
June 6, 2012 at 11:58 pm
Suresh B. (6/6/2012)
jitendra.padhiyar (6/6/2012)
Hello Guys,I setup transactional replication and come to konw that the table with primary key is only accepted for Transactional replication.
I dont konw why.. Can anyone help me to understand ?
The reason is, on subscriber rows updated/deleted individually one-by-one with the help of thier identity coloumn.
For example:
If you run DELETE Product WHERE Price > 1000 on publisher.
Assume 10 rows are deleted by the above command
But on subscriber 10 delete statements would be executed as follows:
DELETE Product WHERE pkcol = @val
You can see this by running Profiler on subsriber.
Just to be clear the existence of an identity column in the table is not a determining factor for replication. An identity column can be designated as the PK, but the PK can be singular or composite and does not have to include an identity column.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 7, 2012 at 12:13 am
Thanks for correcting.
It was an error from my side. I meant PK column, not identity column.
June 7, 2012 at 8:09 am
opc.three (6/6/2012)
It provides a way for the the replication engine to identify a row unambiguously. Unlike unique keys, primary keys do not allow NULLs. Why do you have tables with no primary key? Some would argue that a table without a primary key is not really a table.
First of all thanks a lot to clear my doubt. But as you asked "Why do you have tables with no primary key?" so in big database environment there might be some tables with NO PRIMARY key..! And I think for such tables we can use other DR plan for higher avaibility like Log shipping or Mirroring.
June 7, 2012 at 9:03 am
jitendra.padhiyar (6/7/2012)
opc.three (6/6/2012)
It provides a way for the the replication engine to identify a row unambiguously. Unlike unique keys, primary keys do not allow NULLs. Why do you have tables with no primary key? Some would argue that a table without a primary key is not really a table.First of all thanks a lot to clear my doubt. But as you asked "Why do you have tables with no primary key?" so in big database environment there might be some tables with NO PRIMARY key..!
Anytime. And I understand, not only do I support a ton of tables with no PK on them, but some quite large ones with no clustered indexes either. In large environments with many developers over time, it happens.
And I think for such tables we can use other DR plan for higher avaibility like Log shipping or Mirroring.
If you're going for DR then forget Replication all together. DR is not what it was designed for. If truly looking for DR then you want to look into Log Shipping, Mirroring or Clustering. None of those place constraints on the schema of your database to implement them.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 7, 2012 at 9:56 am
jitendra.padhiyar (6/6/2012)
Hello Guys,I setup transactional replication and come to konw that the table with primary key is only accepted for Transactional replication.
I dont konw why.. Can anyone help me to understand ?
Jitendra, One of the requirement with transactional replication is you need to have primary key in the table that you want to replicate.
Regards,
TA
Regards,
SQLisAwe5oMe.
June 7, 2012 at 9:59 am
SQLCrazyCertified (6/7/2012)
jitendra.padhiyar (6/6/2012)
Hello Guys,I setup transactional replication and come to konw that the table with primary key is only accepted for Transactional replication.
I dont konw why.. Can anyone help me to understand ?
Jitendra, One of the requirement with transactional replication is you need to have primary key in the table that you want to replicate.
Regards,
TA
Yes, I know that primary key is one of the reuirement of Transactional Replication, but why ? There must be some reason to use primary key, and that some masters explains above.
Thanks,
Jitendra
June 7, 2012 at 10:01 am
opc.three
"If truly looking for DR then you want to look into Log Shipping, Mirroring or Clustering."
Hi opc.three, I just want to make sure something.....Is it 100% right to say that Clustering is a DR solution?....I know clustering is one of best high availablity solution and it's costly.....but when you set up clustering....doesn't it require you to place the 2 nodes(could be more) in the same location?......so, how can you say it's 100% DR, if you cannot prevent it from natural disaster?....just a thought...and looking forward to your clarification/opinion.
Regards,
TA
Regards,
SQLisAwe5oMe.
June 7, 2012 at 10:11 am
SQLCrazyCertified (6/7/2012)
opc.three
"If truly looking for DR then you want to look into Log Shipping, Mirroring or Clustering."
Hi opc.three, I just want to make sure something.....Is it 100% right to say that Clustering is a DR solution?....I know clustering is one of best high availablity solution and it's costly.....but when you set up clustering....doesn't it require you to place the 2 nodes(could be more) in the same location?......so, how can you say it's 100% DR, if you cannot prevent it from natural disaster?....just a thought...and looking forward to your clarification/opinion.
Regards,
TA
According to my knowledge we must use mirroring or log shipping with Clustering for DR, alone clustering will not be used as DR. Clustering will ensure hardware availability.
June 7, 2012 at 11:04 am
jitendra.padhiyar (6/7/2012)
SQLCrazyCertified (6/7/2012)
opc.three
"If truly looking for DR then you want to look into Log Shipping, Mirroring or Clustering."
Hi opc.three, I just want to make sure something.....Is it 100% right to say that Clustering is a DR solution?....I know clustering is one of best high availablity solution and it's costly.....but when you set up clustering....doesn't it require you to place the 2 nodes(could be more) in the same location?......so, how can you say it's 100% DR, if you cannot prevent it from natural disaster?....just a thought...and looking forward to your clarification/opinion.
Regards,
TA
According to my knowledge we must use mirroring or log shipping with Clustering for DR, alone clustering will not be used as DR. Clustering will ensure hardware availability.
Thanks Jitendra.
Regards,
TA
Regards,
SQLisAwe5oMe.
June 7, 2012 at 12:04 pm
jitendra.padhiyar (6/7/2012)
SQLCrazyCertified (6/7/2012)
opc.three
"If truly looking for DR then you want to look into Log Shipping, Mirroring or Clustering."
Hi opc.three, I just want to make sure something.....Is it 100% right to say that Clustering is a DR solution?....I know clustering is one of best high availablity solution and it's costly.....but when you set up clustering....doesn't it require you to place the 2 nodes(could be more) in the same location?......so, how can you say it's 100% DR, if you cannot prevent it from natural disaster?....just a thought...and looking forward to your clarification/opinion.
Regards,
TA
According to my knowledge we must use mirroring or log shipping with Clustering for DR, alone clustering will not be used as DR. Clustering will ensure hardware availability.
You said both, DR and higher[sic] availability, and sometimes they are in fact interwined. It depends on what you want, you never really specified. All three technologies can play a role in recovering from a disaster and maintaining high availability. The lines are blurry, i.e. you cannot say that Clustering will never have a role in any Disaster Recovery plan, because it can.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 7, 2012 at 12:20 pm
OPC.Three:
You are correct that clustering can be used. And I said clustering will ensure hardware availability, but its not 100% DR, thats why I said clustering alone can not be said DR plan.
Correct me if I am wrong.
Thanks,
Jitendra
June 7, 2012 at 12:29 pm
jitendra.padhiyar (6/7/2012)
OPC.Three:You are correct that clustering can be used. And I said clustering will ensure hardware availability, but its not 100% DR, thats why I said clustering alone can not be said DR plan.
Correct me if I am wrong.
Thanks,
Jitendra
Why are you hung up on 100%? Do you think Log Shipping or Mirroring are a 100% DR solution?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply