| Groups | Blog | Home |
sql server replication : Transactional Replication Question
My db is 10 GB in size, and want to get into a transactional replication The initial snapshot takes a long time to finish up. What will happen if during this initialization process, users work on pubisher db, insert, update delete record.... Thanks in advance, Pat
I remember that during merge replication, it was casuing problem and failing
the whole process, is it ok in transactional replication ? Thanks again, Pat [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:uE6yc4azEHA.3392@TK2MSFTNGP10.phx.gbl... > this activity will be recorded and stored in the distribution database. > After the snapshot is applied, these commands will be applied to the > subscriber. > > -- > Hilary Cotter > Looking for a SQL Server replication book? > Now available for purchase at: > http://www.nwsu.com/0974973602.html > > "Patrick" <patriarck@gmail.com> wrote in message > news:%23sg60vazEHA.3408@tk2msftngp13.phx.gbl... >> Hi Freinds, >> My db is 10 GB in size, and want to get into a transactional replication >> The initial snapshot takes a long time to finish up. >> >> What will happen if during this initialization process, users work on >> pubisher db, insert, update delete record.... >> >> Thanks in advance, >> Pat >> > >
this activity will be recorded and stored in the distribution database.
After the snapshot is applied, these commands will be applied to the subscriber. -- Hilary Cotter Looking for a SQL Server replication book? Now available for purchase at: http://www.nwsu.com/0974973602.html [quoted text, click to view] "Patrick" <patriarck@gmail.com> wrote in message news:%23sg60vazEHA.3408@tk2msftngp13.phx.gbl... > Hi Freinds, > My db is 10 GB in size, and want to get into a transactional replication > The initial snapshot takes a long time to finish up. > > What will happen if during this initialization process, users work on > pubisher db, insert, update delete record.... > > Thanks in advance, > Pat >
On Thu, 18 Nov 2004 12:19:29 -0800, "Patrick" <patriarck@gmail.com>
[quoted text, click to view] wrote: >My db is 10 GB in size, and want to get into a transactional replication >The initial snapshot takes a long time to finish up. > >What will happen if during this initialization process, users work on >pubisher db, insert, update delete record.... Mine is 14gb. You know, I've discovered you don't strictly *need* the snapshot! If you: * chase off all the users and put publisher into single-user (or dbo) mode. * take a full backup * restore the backup to the subscriber * turn on replication * let your users back on You can do without the snapshot, which takes a long time to produce, and a long time to apply. That is, I *think* you can do without. At least, it works for me in test. What problems it entails, if any, I'm still trying to understand. On my boxes, the backup and restore takes about an hour. It's only if you have to "publish from a moving source" that you have to let the snapshot mechanism do its thing. J.
Pat,
some issues like this are due to locking. By default a table lock is required, but there is the option in transactional (not merge) to have concurrent snapshot generation, which will nopt need an exclusive table lock. Rgds, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
In my cases, my prod server is 24/7 can't put in single mode or stop any
activity. [quoted text, click to view] "jxstern" <jxstern@nowhere.com> wrote in message news:f7cqp0pirdk3lnvcumuhfk3fi3k590ldsn@4ax.com... > On Thu, 18 Nov 2004 12:19:29 -0800, "Patrick" <patriarck@gmail.com> > wrote: >>My db is 10 GB in size, and want to get into a transactional replication >>The initial snapshot takes a long time to finish up. >> >>What will happen if during this initialization process, users work on >>pubisher db, insert, update delete record.... > > Mine is 14gb. > > You know, I've discovered you don't strictly *need* the snapshot! > > If you: > > * chase off all the users and put publisher into single-user (or dbo) > mode. > * take a full backup > * restore the backup to the subscriber > * turn on replication > * let your users back on > > You can do without the snapshot, which takes a long time to produce, > and a long time to apply. > > That is, I *think* you can do without. At least, it works for me in > test. What problems it entails, if any, I'm still trying to > understand. > > On my boxes, the backup and restore takes about an hour. > > It's only if you have to "publish from a moving source" that you have > to let the snapshot mechanism do its thing. > > J. >
On Fri, 19 Nov 2004 14:23:51 -0800, "Patrick" <patriarck@gmail.com>
[quoted text, click to view] wrote: >In my cases, my prod server is 24/7 can't put in single mode or stop any >activity. I respect the complications that entails and am impressed that SQLServer can handle it all, but I do wish BOL was a little clearer on how you can do for simpler situations. J.
This is known as a noinit intitialization. It works, but
the replication stored procedures need creating by hand afterwards (sp_scriptpublicationcustomprocs), and it is more restrictive in terms of future schema changes. Rgds, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
Single User mode is not required if you select the option
to concurrently create a snapshot. Rgds, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
Sorry - should have said 'nosync initialization'
(@sync_type = none). Rgds, Paul
On Mon, 22 Nov 2004 01:21:09 -0800, "Paul Ibison"
[quoted text, click to view] <Paul.Ibison@Pygmalion.Com> wrote: >Single User mode is not required if you select the option >to concurrently create a snapshot. It's not *required*, but I'm curious if it's *allowed*, with all these agents running around! J.
On Mon, 22 Nov 2004 01:19:22 -0800, "Paul Ibison"
[quoted text, click to view] <Paul.Ibison@Pygmalion.Com> wrote: >This is known as a noinit intitialization. It works, but >the replication stored procedures need creating by hand >afterwards (sp_scriptpublicationcustomprocs), and it is >more restrictive in terms of future schema changes. Aha, thank you for saying this! In my case, the "nosync initialization" worked because I had previously run a "sync initialization" on the same database, I guess, and EVEN THOUGH I HAD RUN sp_removedbreplication, it must have left behind the SPs!?!? Joshua Stern
On Mon, 22 Nov 2004 11:21:30 -0800, jxstern <jxstern@nowhere.com>
[quoted text, click to view] wrote: >In my case, the "nosync initialization" worked because I had >previously run a "sync initialization" on the same database, I guess, >and EVEN THOUGH I HAD RUN sp_removedbreplication, it must have left >behind the SPs!?!? Nope. Apparently I'm using "direct inserting" etc, so don't need the custom SPs?? Just when I thought I was getting to understand this stuff ... J.
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
October 2008
| home · blog · groups | Questions? Comments? Contact the dn |