any way to speed replication creation through scripts

As part of my previous thread on migrating the publisher to a new server, I
had scripted the drop and create replication scripts and I have around 100
articles and a few subscribers and what i want to do is to create the trans
replication with the "nosync" option as i ensured that the publisher and
subscribers are in sync.
So what i noticed is that although the drop replication runs in a min or 2,
the create runs for almost 30 mins
And looking into the script, I see that for each article,
it first runs
1) sp_add_article
and then for every subscriber runs
1) sp_addsubscription
2) sp_addpushsubscription_agent
and it runs all this in serial.
So the question is, can i somehow speed this up ? Is there a way to run
sp_addsubscription that say run it for "all" articles
Also I noticed that sp_addpushsubscription_agent just run the same set of
parameters except the subscriber name is different and it repeats it every
time for every article
Can someone please let me know if I could somehow avoid those default
scripted sql and make this all work at the end faster with less stored
procs?
I am using SQL 2005
Thanks
Replication puts schema locks on tables momentarily as part of the
publication creation process. You will get much faster publication creation
times if you kick all your users out of the database before creating the
publication(s).
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <hassan@.hotmail.com> wrote in message
news:eMmCKuUPIHA.4440@.TK2MSFTNGP06.phx.gbl...
> As part of my previous thread on migrating the publisher to a new server,
> I had scripted the drop and create replication scripts and I have around
> 100 articles and a few subscribers and what i want to do is to create the
> trans replication with the "nosync" option as i ensured that the publisher
> and subscribers are in sync.
> So what i noticed is that although the drop replication runs in a min or
> 2, the create runs for almost 30 mins
> And looking into the script, I see that for each article,
> it first runs
> 1) sp_add_article
> and then for every subscriber runs
> 1) sp_addsubscription
> 2) sp_addpushsubscription_agent
> and it runs all this in serial.
> So the question is, can i somehow speed this up ? Is there a way to run
> sp_addsubscription that say run it for "all" articles
> Also I noticed that sp_addpushsubscription_agent just run the same set of
> parameters except the subscriber name is different and it repeats it every
> time for every article
> Can someone please let me know if I could somehow avoid those default
> scripted sql and make this all work at the end faster with less stored
> procs?
> I am using SQL 2005
> Thanks
>
|||So what i tried yesterday and would like to validate with the experts here
is I first ran the sp_addarticle for each and every article
and then ran once for every subscriber the following 2 stored procs
sp_addsubscription -- I specified all articles here
sp_addpushsubscription_agent
If you script replication the above 2 stored procs runs for every article,
hence has 1000 more calls to these sprocs as opposed to what i did above and
run it once for every subscriber
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eYEdzKaPIHA.1756@.TK2MSFTNGP06.phx.gbl...
> Replication puts schema locks on tables momentarily as part of the
> publication creation process. You will get much faster publication
> creation times if you kick all your users out of the database before
> creating the publication(s).
> --
> http://www.zetainteractive.com - Shift Happens!
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:eMmCKuUPIHA.4440@.TK2MSFTNGP06.phx.gbl...
>