|
|
You're in the
sql server replication
group:Updateable Subscriptions - I'm a little confused
sql server replication:
According to BOL: "With snapshot replication or transactional replication, replicated data is by default read only; however, you have the ability to modify replicated data at the Subscriber by using updateable subscriptions. If you need to modify data at the Subscriber using snapshot or transactional replication, you can choose one of the following options depending on your requirements." I'm a little confused as to what "read only" really means. Using transactional replication, I have a publication that does not "Allow immediate updating subscriptions." I opened up a table on the subscriber server using EM and I CAN make changes to the replicatied tables. So does "read only" really just mean that the changes won't be pushed back to the Publisher? The answer is important because I am using the second, subscribing, server as a backup server (in the event the primary server goes down). My thinking was that I would just point my apps to the second server while the primary server is being worked on. When the primary is fixed I planned on DTS'ing the data back from the second server to the primary and reinitialize replication. All this "read only" talk is making me think that my solution won't work. Thanks in advance, Mike
Hi, Paul. Thanks for responding.
I'm not sure what you mean by a couple of things you said: 1) What has to be maintained manually using permissions/roles 2) I'm not sure what you mean by "catering for deletes and updates is no mean feat" Could you please elaborate a little. Thanks again, Mike [quoted text, click to view] "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message news:OdLxzWbaEHA.2544@TK2MSFTNGP10.phx.gbl... > Mike, > it is to be treated as Read-Only but this is not enforced by the replication > setup and has to be maintained 'manually' using permissions/roles instead. > Your methodology will work, but catering for deletes and updates is no mean > feat. An easier solution would be to use queued updating subscribers and > then run the queue reader after the problem with the primary server is > fixed. This also makes dealing with identity columns far easier. > HTH, > Paul Ibison > >
Yes, that clears it up.
Thanks again, Mike [quoted text, click to view] "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message news:ur0CUvbaEHA.2216@TK2MSFTNGP10.phx.gbl... > Mike, > > (1) the subscriber is to be treated as read-only. If a user is allowed to > access this data, he might be put into the db_datareader role and the > db_denydatawriter role to prevent him from doing anything naughty. There > have been several posts in this newsgroup where transactions fail on the > subscriber because records have been 'accidentally' modified/deleted even > though it was ostensibly used for reporting purposes etc. > > (2) consider if you use "Server B" as a failover server. On failover your > users start deleting, updating and inserting records. You mentioned that you > would like to use DTS to synchronize the data with the "publisher" (server > A) when it has been fixed. On B, you'll need to find new records, and > records which have been deleted. Admittedly, this is not too difficult using > outer joins or subqueries and a linked server to A. How are you going to > know which records have been updated though? You could use binary checksums > for this, again with subqueries and the A linked server. So you'll have a > few DTS tasks for each table, which are also particular to the table. This > has to be repeated for each table in the publication. > > If you use queued updating subscribers, all of this is done for you in the > replication setup - it doesn't use this methodology but results in the same > functionality though. > > HTH, > > Paul Ibison > >
Actually, Paul, I should clarify what I meant by DTS'ing.
I planned on Exporting/Importing all the data from the tables, then turning replication back on. [quoted text, click to view] "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message news:ur0CUvbaEHA.2216@TK2MSFTNGP10.phx.gbl... > Mike, > > (1) the subscriber is to be treated as read-only. If a user is allowed to > access this data, he might be put into the db_datareader role and the > db_denydatawriter role to prevent him from doing anything naughty. There > have been several posts in this newsgroup where transactions fail on the > subscriber because records have been 'accidentally' modified/deleted even > though it was ostensibly used for reporting purposes etc. > > (2) consider if you use "Server B" as a failover server. On failover your > users start deleting, updating and inserting records. You mentioned that you > would like to use DTS to synchronize the data with the "publisher" (server > A) when it has been fixed. On B, you'll need to find new records, and > records which have been deleted. Admittedly, this is not too difficult using > outer joins or subqueries and a linked server to A. How are you going to > know which records have been updated though? You could use binary checksums > for this, again with subqueries and the A linked server. So you'll have a > few DTS tasks for each table, which are also particular to the table. This > has to be repeated for each table in the publication. > > If you use queued updating subscribers, all of this is done for you in the > replication setup - it doesn't use this methodology but results in the same > functionality though. > > HTH, > > Paul Ibison > >
Mike,
it is to be treated as Read-Only but this is not enforced by the replication setup and has to be maintained 'manually' using permissions/roles instead. Your methodology will work, but catering for deletes and updates is no mean feat. An easier solution would be to use queued updating subscribers and then run the queue reader after the problem with the primary server is fixed. This also makes dealing with identity columns far easier. HTH, Paul Ibison
Mike,
(1) the subscriber is to be treated as read-only. If a user is allowed to access this data, he might be put into the db_datareader role and the db_denydatawriter role to prevent him from doing anything naughty. There have been several posts in this newsgroup where transactions fail on the subscriber because records have been 'accidentally' modified/deleted even though it was ostensibly used for reporting purposes etc. (2) consider if you use "Server B" as a failover server. On failover your users start deleting, updating and inserting records. You mentioned that you would like to use DTS to synchronize the data with the "publisher" (server A) when it has been fixed. On B, you'll need to find new records, and records which have been deleted. Admittedly, this is not too difficult using outer joins or subqueries and a linked server to A. How are you going to know which records have been updated though? You could use binary checksums for this, again with subqueries and the A linked server. So you'll have a few DTS tasks for each table, which are also particular to the table. This has to be repeated for each table in the publication. If you use queued updating subscribers, all of this is done for you in the replication setup - it doesn't use this methodology but results in the same functionality though. HTH, Paul Ibison
Mike,
faire enough - this is simpler. It might mean a lot of extra data which hasn't changed will be brought back, but it is definitely more straightforward to set up. Cheers, Paul
[quoted text, click to view]
>I planned on Exporting/Importing all the data from the tables, then turning >replication back on. This of course means that you have a small enough set of data for this to be an option? If you have lots of data this may be tough. Plus, if you've just recovered from a disaster, can you tell users "Yes, the Publisher is back up, but you cant use it until I re-import ALL of the data again"? Perhaps you can but I just thought Id throw it out there? [quoted text, click to view] >-----Original Message-----
>Actually, Paul, I should clarify what I meant by DTS'ing. > >I planned on Exporting/Importing all the data from the tables, then turning >replication back on. > >"Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message >news:ur0CUvbaEHA.2216@TK2MSFTNGP10.phx.gbl... >> Mike, >> >> (1) the subscriber is to be treated as read-only. If a user is allowed to >> access this data, he might be put into the db_datareader role and the >> db_denydatawriter role to prevent him from doing anything naughty. There >> have been several posts in this newsgroup where transactions fail on the >> subscriber because records have been 'accidentally' modified/deleted even >> though it was ostensibly used for reporting purposes etc. >> >> (2) consider if you use "Server B" as a failover server. On failover your >> users start deleting, updating and inserting records. You mentioned that >you >> would like to use DTS to synchronize the data with the "publisher" (server >> A) when it has been fixed. On B, you'll need to find new records, and >> records which have been deleted. Admittedly, this is not too difficult >using >> outer joins or subqueries and a linked server to A. How are you going to >> know which records have been updated though? You could use binary >checksums >> for this, again with subqueries and the A linked server. So you'll have a >> few DTS tasks for each table, which are also particular to the table. This >> has to be repeated for each table in the publication. >> >> If you use queued updating subscribers, all of this is done for you in the >> replication setup - it doesn't use this methodology but results in the >same >> functionality though. >> >> HTH, >> >> Paul Ibison >> >> > > >.
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 |