| Groups | Blog | Home |
sql server replication : Snapshot replication
hr. The issue that I have is that is locking people out of the system until the snapshot finish. Is there a way to eliminate this or do I have to look for other ways to replicate the data? Would decreasing the -BcpBatchSize alleviate the issue or
You can look at @sync_method='concurrent' or 'database snapshot' to minimise
the locking when running sp_addpublication. HTH, Paul Ibison
Are you mostly concerned with the publisher database being locked out or the
subscriber database? [quoted text, click to view] "cata" <cata@discussions.microsoft.com> wrote in message news:FB2654A1-ED6F-4597-925D-CEBDCBC95A6E@microsoft.com... >I have a snapshot replication that runs over the night and it takes about 1 > hr. The issue that I have is that is locking people out of the system > until > the snapshot finish. > > Is there a way to eliminate this or do I have to look for other ways to > replicate the data? Would decreasing the -BcpBatchSize alleviate the issue > or > would do no good?
I am concern with the publisher getting locked when the snapshot is
generated, the publisher is a production database. [quoted text, click to view] "Raymond Mak [MSFT]" wrote:
> Are you mostly concerned with the publisher database being locked out or the > subscriber database? > > "cata" <cata@discussions.microsoft.com> wrote in message > news:FB2654A1-ED6F-4597-925D-CEBDCBC95A6E@microsoft.com... > >I have a snapshot replication that runs over the night and it takes about 1 > > hr. The issue that I have is that is locking people out of the system > > until > > the snapshot finish. > > > > Is there a way to eliminate this or do I have to look for other ways to > > replicate the data? Would decreasing the -BcpBatchSize alleviate the issue > > or > > would do no good? > >
Like Paul mentioned in his reponse, you can set sync_method to 'database
snapshot' if you are running SQL2005 SP2 EE at your publisher. Note that if this is really a straight snapshot publication, you can't set the sync_method to 'concurrent' as that requires that logreader agent for tracking the reconciliation commands. -Raymond [quoted text, click to view] "cata" <cata@discussions.microsoft.com> wrote in message news:09C62FDB-FF47-47E5-A6E6-36ED7CC16E70@microsoft.com... >I am concern with the publisher getting locked when the snapshot is > generated, the publisher is a production database. > > "Raymond Mak [MSFT]" wrote: > >> Are you mostly concerned with the publisher database being locked out or >> the >> subscriber database? >> >> "cata" <cata@discussions.microsoft.com> wrote in message >> news:FB2654A1-ED6F-4597-925D-CEBDCBC95A6E@microsoft.com... >> >I have a snapshot replication that runs over the night and it takes >> >about 1 >> > hr. The issue that I have is that is locking people out of the system >> > until >> > the snapshot finish. >> > >> > Is there a way to eliminate this or do I have to look for other ways to >> > replicate the data? Would decreasing the -BcpBatchSize alleviate the >> > issue >> > or >> > would do no good? >> >> >>
Unfortunatelly the publisher is SQL Server 2k and the subscriber is a SQL
Server 2k5. [quoted text, click to view] "Raymond Mak [MSFT]" wrote:
> Like Paul mentioned in his reponse, you can set sync_method to 'database > snapshot' if you are running SQL2005 SP2 EE at your publisher. Note that if > this is really a straight snapshot publication, you can't set the > sync_method to 'concurrent' as that requires that logreader agent for > tracking the reconciliation commands. > > -Raymond > > "cata" <cata@discussions.microsoft.com> wrote in message > news:09C62FDB-FF47-47E5-A6E6-36ED7CC16E70@microsoft.com... > >I am concern with the publisher getting locked when the snapshot is > > generated, the publisher is a production database. > > > > "Raymond Mak [MSFT]" wrote: > > > >> Are you mostly concerned with the publisher database being locked out or > >> the > >> subscriber database? > >> > >> "cata" <cata@discussions.microsoft.com> wrote in message > >> news:FB2654A1-ED6F-4597-925D-CEBDCBC95A6E@microsoft.com... > >> >I have a snapshot replication that runs over the night and it takes > >> >about 1 > >> > hr. The issue that I have is that is locking people out of the system > >> > until > >> > the snapshot finish. > >> > > >> > Is there a way to eliminate this or do I have to look for other ways to > >> > replicate the data? Would decreasing the -BcpBatchSize alleviate the > >> > issue > >> > or > >> > would do no good? > >> > >> > >> > >
From what I can see the sync_method is only available for transactional and
merge replication but not for snapshot replication http://msdn2.microsoft.com/en-us/library/aa239409(SQL.80).aspx [quoted text, click to view] "Raymond Mak [MSFT]" wrote:
> Like Paul mentioned in his reponse, you can set sync_method to 'database > snapshot' if you are running SQL2005 SP2 EE at your publisher. Note that if > this is really a straight snapshot publication, you can't set the > sync_method to 'concurrent' as that requires that logreader agent for > tracking the reconciliation commands. > > -Raymond > > "cata" <cata@discussions.microsoft.com> wrote in message > news:09C62FDB-FF47-47E5-A6E6-36ED7CC16E70@microsoft.com... > >I am concern with the publisher getting locked when the snapshot is > > generated, the publisher is a production database. > > > > "Raymond Mak [MSFT]" wrote: > > > >> Are you mostly concerned with the publisher database being locked out or > >> the > >> subscriber database? > >> > >> "cata" <cata@discussions.microsoft.com> wrote in message > >> news:FB2654A1-ED6F-4597-925D-CEBDCBC95A6E@microsoft.com... > >> >I have a snapshot replication that runs over the night and it takes > >> >about 1 > >> > hr. The issue that I have is that is locking people out of the system > >> > until > >> > the snapshot finish. > >> > > >> > Is there a way to eliminate this or do I have to look for other ways to > >> > replicate the data? Would decreasing the -BcpBatchSize alleviate the > >> > issue > >> > or > >> > would do no good? > >> > >> > >> > >
What about -BcpBatchSize, would it bring any value reducing the size of it?
[quoted text, click to view] "Paul Ibison" wrote:
> Like Raymond says, the 'database snapshot' option applies to SQL 2005 > publishers. As you have a 2000 publisher, this is not an option. You'll have > to run the snapshot agent out of hours, or use an alternative mechanism. One > possibility I would consider is to use log shipping. The standby server can > be used as a source of data for the subscriber if you like. This wouldn't be > via replication, but you could use DTS for it. This way there'll be no > locking issues at all on the publisher. > Cheers, > Paul Ibison > >
Like Raymond says, the 'database snapshot' option applies to SQL 2005
publishers. As you have a 2000 publisher, this is not an option. You'll have to run the snapshot agent out of hours, or use an alternative mechanism. One possibility I would consider is to use log shipping. The standby server can be used as a source of data for the subscriber if you like. This wouldn't be via replication, but you could use DTS for it. This way there'll be no locking issues at all on the publisher. Cheers, Paul Ibison
Changing -BcpBatchSize will not do any good. You can set -MaxBcpThreads to a
higher value in an attempt to increase throughput but I am guessing you have probably tried that already. -Raymond [quoted text, click to view] "cata" <cata@discussions.microsoft.com> wrote in message news:BDA8D29C-6B04-4634-874E-C1848ABF6F6E@microsoft.com... > What about -BcpBatchSize, would it bring any value reducing the size of > it? > > "Paul Ibison" wrote: > >> Like Raymond says, the 'database snapshot' option applies to SQL 2005 >> publishers. As you have a 2000 publisher, this is not an option. You'll >> have >> to run the snapshot agent out of hours, or use an alternative mechanism. >> One >> possibility I would consider is to use log shipping. The standby server >> can >> be used as a source of data for the subscriber if you like. This wouldn't >> be >> via replication, but you could use DTS for it. This way there'll be no >> locking issues at all on the publisher. >> Cheers, >> Paul Ibison >> >> >>
Yes, I am at -MaxBcpThreads 9, from there up I haven't noticed any benefit.
For -BcpBatchSize I start decreasing the batch size from 100000 to now at 10000 with no noticeable processing time difference in the hope that decreasing the size will give users a chance to do some work between batches. I will keep going down untill there would be a jump in the time the process take to finish. [quoted text, click to view] "Raymond Mak [MSFT]" wrote:
> Changing -BcpBatchSize will not do any good. You can set -MaxBcpThreads to a > higher value in an attempt to increase throughput but I am guessing you have > probably tried that already. > > -Raymond > > "cata" <cata@discussions.microsoft.com> wrote in message > news:BDA8D29C-6B04-4634-874E-C1848ABF6F6E@microsoft.com... > > What about -BcpBatchSize, would it bring any value reducing the size of > > it? > > > > "Paul Ibison" wrote: > > > >> Like Raymond says, the 'database snapshot' option applies to SQL 2005 > >> publishers. As you have a 2000 publisher, this is not an option. You'll > >> have > >> to run the snapshot agent out of hours, or use an alternative mechanism. > >> One > >> possibility I would consider is to use log shipping. The standby server > >> can > >> be used as a source of data for the subscriber if you like. This wouldn't > >> be > >> via replication, but you could use DTS for it. This way there'll be no > >> locking issues at all on the publisher. > >> Cheers, > >> Paul Ibison > >> > >> > >> > >
Check under the publication options on the snapshot tab, there is an option
for do not lock tables during snapshot generation. Hope this helps
Couldn't find it. Is this for Snapshot Replication or Transactional
Replication? [quoted text, click to view] "Frivas" wrote:
> Check under the publication options on the snapshot tab, there is an option > for do not lock tables during snapshot generation. > > Hope this helps >
The "do not lock table" option in the UI actually corresponds to sync_method
= 'concurrent' so it is not available to straight snapshot publications. The labelling of the option is also a bit mislead as I have explained in the following post: http://groups.google.com/group/microsoft.public.sqlserver.replication/browse_frm/thread/415c42838fbef3f5/9574613bd887acd5?rnum=22#9574613bd887acd5 If you don't mind the potential for (slightly) inconsistent data at your subscriber, Paul's suggestion of using DTS is definitely the way to go. That said, we do have a lot of improvements in SQL2005 targetting your particular scenario such as the 'database snapshot' sync_method and article bcp partitioning for increasing snapshot generation throughput if your publication has a few very large tables. -Raymond [quoted text, click to view] "cata" <cata@discussions.microsoft.com> wrote in message news:A7DA50EA-D821-47C9-9F9C-BE9E83693275@microsoft.com... > Couldn't find it. Is this for Snapshot Replication or Transactional > Replication? > > "Frivas" wrote: > >> Check under the publication options on the snapshot tab, there is an >> option >> for do not lock tables during snapshot generation. >> >> Hope this helps >> >> Fernando Rivas
Thanks for your help guys.
[quoted text, click to view] "Raymond Mak [MSFT]" wrote:
> The "do not lock table" option in the UI actually corresponds to sync_method > = 'concurrent' so it is not available to straight snapshot publications. The > labelling of the option is also a bit mislead as I have explained in the > following post: > > http://groups.google.com/group/microsoft.public.sqlserver.replication/browse_frm/thread/415c42838fbef3f5/9574613bd887acd5?rnum=22#9574613bd887acd5 > > If you don't mind the potential for (slightly) inconsistent data at your > subscriber, Paul's suggestion of using DTS is definitely the way to go. That > said, we do have a lot of improvements in SQL2005 targetting your particular > scenario such as the 'database snapshot' sync_method and article bcp > partitioning for increasing snapshot generation throughput if your > publication has a few very large tables. > > -Raymond > "cata" <cata@discussions.microsoft.com> wrote in message > news:A7DA50EA-D821-47C9-9F9C-BE9E83693275@microsoft.com... > > Couldn't find it. Is this for Snapshot Replication or Transactional > > Replication? > > > > "Frivas" wrote: > > > >> Check under the publication options on the snapshot tab, there is an > >> option > >> for do not lock tables during snapshot generation. > >> > >> Hope this helps > >> > >> Fernando Rivas > >
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 |