Just wanted to know if encrypting sp's using the WITH ENCRYPTION will
affect sp runtime performance?
Does sql server encrypt the actual sp, query plans, etc... or does it
just not show you the sp contents when you try and modify the sp or
profile it?
It just doesn't show it to you. It's actually obfuscated, not really
encrypted...
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"bendlam" <bendlam@.gmail.com> wrote in message
news:1186683048.146983.90170@.x35g2000prf.googlegro ups.com...
> Just wanted to know if encrypting sp's using the WITH ENCRYPTION will
> affect sp runtime performance?
> Does sql server encrypt the actual sp, query plans, etc... or does it
> just not show you the sp contents when you try and modify the sp or
> profile it?
>
|||On Aug 9, 2:26 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> It just doesn't show it to you. It's actuallyobfuscated, not really
> encrypted...
> --
> Adam MachanicSQLServerMVP -http://sqlblog.com
> Author, "ExpertSQLServer2005Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> "bendlam" <bend...@.gmail.com> wrote in message
> news:1186683048.146983.90170@.x35g2000prf.googlegro ups.com...
>
>
> - Show quoted text -
So does this affect performance?
|||> So does this affect performance?
Did you try it?
|||On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Did you try it?
Yes I did. Didn't look like there was much of a performance
difference. What I'm trying to understand is how obfuscating the code
affects performance. i.e. Is there more work for sql server to read
the code since its obfuscated? There is little to no documentation
on this in BOL.
|||The encrypted stored procedure text needs to be decrypted each time it
is compiled or recompiled. Since compiling a batch of statements is a
relatively expensive operation, I would expect the performance
difference to be unnoticable.
HTH,
Gert-Jan
bendlam wrote:
> On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
> Yes I did. Didn't look like there was much of a performance
> difference. What I'm trying to understand is how obfuscating the code
> affects performance. i.e. Is there more work for sql server to read
> the code since its obfuscated? There is little to no documentation
> on this in BOL.
Showing posts with label sps. Show all posts
Showing posts with label sps. Show all posts
Tuesday, February 14, 2012
does using WITH ENCRYPTION for sps affect performance?
Just wanted to know if encrypting sp's using the WITH ENCRYPTION will
affect sp runtime performance?
Does sql server encrypt the actual sp, query plans, etc... or does it
just not show you the sp contents when you try and modify the sp or
profile it?It just doesn't show it to you. It's actually obfuscated, not really
encrypted...
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"bendlam" <bendlam@.gmail.com> wrote in message
news:1186683048.146983.90170@.x35g2000prf.googlegroups.com...
> Just wanted to know if encrypting sp's using the WITH ENCRYPTION will
> affect sp runtime performance?
> Does sql server encrypt the actual sp, query plans, etc... or does it
> just not show you the sp contents when you try and modify the sp or
> profile it?
>|||On Aug 9, 2:26 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> It just doesn't show it to you. It's actuallyobfuscated, not really
> encrypted...
> --
> Adam MachanicSQLServerMVP -http://sqlblog.com
> Author, "ExpertSQLServer2005Development"http://www.apress.com/book/bookDis
play.html?bID=10220
> "bendlam" <bend...@.gmail.com> wrote in message
> news:1186683048.146983.90170@.x35g2000prf.googlegroups.com...
>
>
>
> - Show quoted text -
So does this affect performance?|||> So does this affect performance?
Did you try it?|||On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Did you try it?
Yes I did. Didn't look like there was much of a performance
difference. What I'm trying to understand is how obfuscating the code
affects performance. i.e. Is there more work for sql server to read
the code since its obfuscated? There is little to no documentation
on this in BOL.|||The encrypted stored procedure text needs to be decrypted each time it
is compiled or recompiled. Since compiling a batch of statements is a
relatively expensive operation, I would expect the performance
difference to be unnoticable.
HTH,
Gert-Jan
bendlam wrote:
> On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
> Yes I did. Didn't look like there was much of a performance
> difference. What I'm trying to understand is how obfuscating the code
> affects performance. i.e. Is there more work for sql server to read
> the code since its obfuscated? There is little to no documentation
> on this in BOL.|||On Aug 13, 1:45 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> The encrypted stored procedure text needs to be decrypted each time it
> is compiled or recompiled. Since compiling a batch of statements is a
> relatively expensive operation, I would expect the performance
> difference to be unnoticable.
> HTH,
> Gert-Jan
>
> bendlam wrote:
>
>
>
> - Show quoted text -
just to clarify,
Did you mean compiling a batch of statements is a relatively
"inexpensive" or "expensive" operation? in which case you'd expect
performance difference would be unnoticable.
I'm assuming you meant inexpensive, and that "WITH ENCRYPTION" doesn't
affect a sql servers ability to cache its query plans to avoid
excessive recompilations, correct?
Thanks for your response.
ben|||> just to clarify,
> Did you mean compiling a batch of statements is a relatively
> "inexpensive" or "expensive" operation? in which case you'd expect
> performance difference would be unnoticable.
I think what Gert-Jan meant was, the decryption operation is such a small
part of the work that it would be negligible overall.
A|||"Aaron Bertrand [SQL Server MVP]" wrote:
>
> I think what Gert-Jan meant was, the decryption operation is such a small
> part of the work that it would be negligible overall.
> A
Exactly. Compilation is a relatively expansive operation, but decryption
is not.
To answer your other question: encryption (or the lack thereof) does not
affect the caching behavior of compiled query plans.
Gert-Jan|||On Aug 14, 1:01 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>
>
>
>
> Exactly. Compilation is a relatively expansive operation, but decryption
> is not.
> To answer your other question: encryption (or the lack thereof) does not
> affect the caching behavior of compiled query plans.
> Gert-Jan
Thanks for your help guys.
affect sp runtime performance?
Does sql server encrypt the actual sp, query plans, etc... or does it
just not show you the sp contents when you try and modify the sp or
profile it?It just doesn't show it to you. It's actually obfuscated, not really
encrypted...
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"bendlam" <bendlam@.gmail.com> wrote in message
news:1186683048.146983.90170@.x35g2000prf.googlegroups.com...
> Just wanted to know if encrypting sp's using the WITH ENCRYPTION will
> affect sp runtime performance?
> Does sql server encrypt the actual sp, query plans, etc... or does it
> just not show you the sp contents when you try and modify the sp or
> profile it?
>|||On Aug 9, 2:26 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> It just doesn't show it to you. It's actuallyobfuscated, not really
> encrypted...
> --
> Adam MachanicSQLServerMVP -http://sqlblog.com
> Author, "ExpertSQLServer2005Development"http://www.apress.com/book/bookDis
play.html?bID=10220
> "bendlam" <bend...@.gmail.com> wrote in message
> news:1186683048.146983.90170@.x35g2000prf.googlegroups.com...
>
>
>
> - Show quoted text -
So does this affect performance?|||> So does this affect performance?
Did you try it?|||On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Did you try it?
Yes I did. Didn't look like there was much of a performance
difference. What I'm trying to understand is how obfuscating the code
affects performance. i.e. Is there more work for sql server to read
the code since its obfuscated? There is little to no documentation
on this in BOL.|||The encrypted stored procedure text needs to be decrypted each time it
is compiled or recompiled. Since compiling a batch of statements is a
relatively expensive operation, I would expect the performance
difference to be unnoticable.
HTH,
Gert-Jan
bendlam wrote:
> On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
> Yes I did. Didn't look like there was much of a performance
> difference. What I'm trying to understand is how obfuscating the code
> affects performance. i.e. Is there more work for sql server to read
> the code since its obfuscated? There is little to no documentation
> on this in BOL.|||On Aug 13, 1:45 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> The encrypted stored procedure text needs to be decrypted each time it
> is compiled or recompiled. Since compiling a batch of statements is a
> relatively expensive operation, I would expect the performance
> difference to be unnoticable.
> HTH,
> Gert-Jan
>
> bendlam wrote:
>
>
>
> - Show quoted text -
just to clarify,
Did you mean compiling a batch of statements is a relatively
"inexpensive" or "expensive" operation? in which case you'd expect
performance difference would be unnoticable.
I'm assuming you meant inexpensive, and that "WITH ENCRYPTION" doesn't
affect a sql servers ability to cache its query plans to avoid
excessive recompilations, correct?
Thanks for your response.
ben|||> just to clarify,
> Did you mean compiling a batch of statements is a relatively
> "inexpensive" or "expensive" operation? in which case you'd expect
> performance difference would be unnoticable.
I think what Gert-Jan meant was, the decryption operation is such a small
part of the work that it would be negligible overall.
A|||"Aaron Bertrand [SQL Server MVP]" wrote:
>
> I think what Gert-Jan meant was, the decryption operation is such a small
> part of the work that it would be negligible overall.
> A
Exactly. Compilation is a relatively expansive operation, but decryption
is not.
To answer your other question: encryption (or the lack thereof) does not
affect the caching behavior of compiled query plans.
Gert-Jan|||On Aug 14, 1:01 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>
>
>
>
> Exactly. Compilation is a relatively expansive operation, but decryption
> is not.
> To answer your other question: encryption (or the lack thereof) does not
> affect the caching behavior of compiled query plans.
> Gert-Jan
Thanks for your help guys.
does using WITH ENCRYPTION for sps affect performance?
Just wanted to know if encrypting sp's using the WITH ENCRYPTION will
affect sp runtime performance?
Does sql server encrypt the actual sp, query plans, etc... or does it
just not show you the sp contents when you try and modify the sp or
profile it?It just doesn't show it to you. It's actually obfuscated, not really
encrypted...
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"bendlam" <bendlam@.gmail.com> wrote in message
news:1186683048.146983.90170@.x35g2000prf.googlegroups.com...
> Just wanted to know if encrypting sp's using the WITH ENCRYPTION will
> affect sp runtime performance?
> Does sql server encrypt the actual sp, query plans, etc... or does it
> just not show you the sp contents when you try and modify the sp or
> profile it?
>|||On Aug 9, 2:26 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> It just doesn't show it to you. It's actuallyobfuscated, not really
> encrypted...
> --
> Adam MachanicSQLServerMVP -http://sqlblog.com
> Author, "ExpertSQLServer2005Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> "bendlam" <bend...@.gmail.com> wrote in message
> news:1186683048.146983.90170@.x35g2000prf.googlegroups.com...
>
> > Just wanted to know if encrypting sp's using the WITH ENCRYPTION will
> > affect sp runtimeperformance?
> > Doessqlserverencrypt the actual sp, query plans, etc... or does it
> > just not show you the sp contents when you try and modify the sp or
> > profile it... Hide quoted text -
> - Show quoted text -
So does this affect performance?|||> So does this affect performance?
Did you try it?|||On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> > So does this affect performance?
> Did you try it?
Yes I did. Didn't look like there was much of a performance
difference. What I'm trying to understand is how obfuscating the code
affects performance. i.e. Is there more work for sql server to read
the code since its obfuscated? There is little to no documentation
on this in BOL.|||The encrypted stored procedure text needs to be decrypted each time it
is compiled or recompiled. Since compiling a batch of statements is a
relatively expensive operation, I would expect the performance
difference to be unnoticable.
HTH,
Gert-Jan
bendlam wrote:
> On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
> > > So does this affect performance?
> >
> > Did you try it?
> Yes I did. Didn't look like there was much of a performance
> difference. What I'm trying to understand is how obfuscating the code
> affects performance. i.e. Is there more work for sql server to read
> the code since its obfuscated? There is little to no documentation
> on this in BOL.|||On Aug 13, 1:45 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> The encrypted stored procedure text needs to be decrypted each time it
> is compiled or recompiled. Since compiling a batch of statements is a
> relatively expensive operation, I would expect the performance
> difference to be unnoticable.
> HTH,
> Gert-Jan
>
> bendlam wrote:
> > On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
> > <ten...@.dnartreb.noraa> wrote:
> > > > So does this affect performance?
> > > Did you try it?
> > Yes I did. Didn't look like there was much of a performance
> > difference. What I'm trying to understand is how obfuscating the code
> > affects performance. i.e. Is there more work for sql server to read
> > the code since its obfuscated? There is little to no documentation
> > on this in BOL.- Hide quoted text -
> - Show quoted text -
just to clarify,
Did you mean compiling a batch of statements is a relatively
"inexpensive" or "expensive" operation? in which case you'd expect
performance difference would be unnoticable.
I'm assuming you meant inexpensive, and that "WITH ENCRYPTION" doesn't
affect a sql servers ability to cache its query plans to avoid
excessive recompilations, correct?
Thanks for your response.
ben|||> just to clarify,
> Did you mean compiling a batch of statements is a relatively
> "inexpensive" or "expensive" operation? in which case you'd expect
> performance difference would be unnoticable.
I think what Gert-Jan meant was, the decryption operation is such a small
part of the work that it would be negligible overall.
A|||"Aaron Bertrand [SQL Server MVP]" wrote:
> > just to clarify,
> >
> > Did you mean compiling a batch of statements is a relatively
> > "inexpensive" or "expensive" operation? in which case you'd expect
> > performance difference would be unnoticable.
> I think what Gert-Jan meant was, the decryption operation is such a small
> part of the work that it would be negligible overall.
> A
Exactly. Compilation is a relatively expansive operation, but decryption
is not.
To answer your other question: encryption (or the lack thereof) does not
affect the caching behavior of compiled query plans.
Gert-Jan|||On Aug 14, 1:01 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
> > > just to clarify,
> > > Did you mean compiling a batch of statements is a relatively
> > > "inexpensive" or "expensive" operation? in which case you'd expect
> > > performance difference would be unnoticable.
> > I think what Gert-Jan meant was, the decryption operation is such a small
> > part of the work that it would be negligible overall.
> > A
> Exactly. Compilation is a relatively expansive operation, but decryption
> is not.
> To answer your other question: encryption (or the lack thereof) does not
> affect the caching behavior of compiled query plans.
> Gert-Jan
Thanks for your help guys.
affect sp runtime performance?
Does sql server encrypt the actual sp, query plans, etc... or does it
just not show you the sp contents when you try and modify the sp or
profile it?It just doesn't show it to you. It's actually obfuscated, not really
encrypted...
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"bendlam" <bendlam@.gmail.com> wrote in message
news:1186683048.146983.90170@.x35g2000prf.googlegroups.com...
> Just wanted to know if encrypting sp's using the WITH ENCRYPTION will
> affect sp runtime performance?
> Does sql server encrypt the actual sp, query plans, etc... or does it
> just not show you the sp contents when you try and modify the sp or
> profile it?
>|||On Aug 9, 2:26 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> It just doesn't show it to you. It's actuallyobfuscated, not really
> encrypted...
> --
> Adam MachanicSQLServerMVP -http://sqlblog.com
> Author, "ExpertSQLServer2005Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> "bendlam" <bend...@.gmail.com> wrote in message
> news:1186683048.146983.90170@.x35g2000prf.googlegroups.com...
>
> > Just wanted to know if encrypting sp's using the WITH ENCRYPTION will
> > affect sp runtimeperformance?
> > Doessqlserverencrypt the actual sp, query plans, etc... or does it
> > just not show you the sp contents when you try and modify the sp or
> > profile it... Hide quoted text -
> - Show quoted text -
So does this affect performance?|||> So does this affect performance?
Did you try it?|||On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> > So does this affect performance?
> Did you try it?
Yes I did. Didn't look like there was much of a performance
difference. What I'm trying to understand is how obfuscating the code
affects performance. i.e. Is there more work for sql server to read
the code since its obfuscated? There is little to no documentation
on this in BOL.|||The encrypted stored procedure text needs to be decrypted each time it
is compiled or recompiled. Since compiling a batch of statements is a
relatively expensive operation, I would expect the performance
difference to be unnoticable.
HTH,
Gert-Jan
bendlam wrote:
> On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
> > > So does this affect performance?
> >
> > Did you try it?
> Yes I did. Didn't look like there was much of a performance
> difference. What I'm trying to understand is how obfuscating the code
> affects performance. i.e. Is there more work for sql server to read
> the code since its obfuscated? There is little to no documentation
> on this in BOL.|||On Aug 13, 1:45 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> The encrypted stored procedure text needs to be decrypted each time it
> is compiled or recompiled. Since compiling a batch of statements is a
> relatively expensive operation, I would expect the performance
> difference to be unnoticable.
> HTH,
> Gert-Jan
>
> bendlam wrote:
> > On Aug 10, 11:28 am, "Aaron Bertrand [SQL Server MVP]"
> > <ten...@.dnartreb.noraa> wrote:
> > > > So does this affect performance?
> > > Did you try it?
> > Yes I did. Didn't look like there was much of a performance
> > difference. What I'm trying to understand is how obfuscating the code
> > affects performance. i.e. Is there more work for sql server to read
> > the code since its obfuscated? There is little to no documentation
> > on this in BOL.- Hide quoted text -
> - Show quoted text -
just to clarify,
Did you mean compiling a batch of statements is a relatively
"inexpensive" or "expensive" operation? in which case you'd expect
performance difference would be unnoticable.
I'm assuming you meant inexpensive, and that "WITH ENCRYPTION" doesn't
affect a sql servers ability to cache its query plans to avoid
excessive recompilations, correct?
Thanks for your response.
ben|||> just to clarify,
> Did you mean compiling a batch of statements is a relatively
> "inexpensive" or "expensive" operation? in which case you'd expect
> performance difference would be unnoticable.
I think what Gert-Jan meant was, the decryption operation is such a small
part of the work that it would be negligible overall.
A|||"Aaron Bertrand [SQL Server MVP]" wrote:
> > just to clarify,
> >
> > Did you mean compiling a batch of statements is a relatively
> > "inexpensive" or "expensive" operation? in which case you'd expect
> > performance difference would be unnoticable.
> I think what Gert-Jan meant was, the decryption operation is such a small
> part of the work that it would be negligible overall.
> A
Exactly. Compilation is a relatively expansive operation, but decryption
is not.
To answer your other question: encryption (or the lack thereof) does not
affect the caching behavior of compiled query plans.
Gert-Jan|||On Aug 14, 1:01 pm, Gert-Jan Strik <so...@.toomuchspamalready.nl>
wrote:
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
> > > just to clarify,
> > > Did you mean compiling a batch of statements is a relatively
> > > "inexpensive" or "expensive" operation? in which case you'd expect
> > > performance difference would be unnoticable.
> > I think what Gert-Jan meant was, the decryption operation is such a small
> > part of the work that it would be negligible overall.
> > A
> Exactly. Compilation is a relatively expansive operation, but decryption
> is not.
> To answer your other question: encryption (or the lack thereof) does not
> affect the caching behavior of compiled query plans.
> Gert-Jan
Thanks for your help guys.
Does transactional replication replicate SPs?
I set up TR on table T1. Works fine.
While it was running, I tried adding an article, mysp.
I couldn't quite figure out what to do to send the initial snapshot of
just mysp, while replication was already running for T1.
Eventually I ... well, I lost track. I think I deleted the
subscription, then pushed it again, then finally told it explicitly to
run the snapshot agent immediately. OK, the SP travelled.
So, do I now have both the table and the SP replicating?
No. I can add rows to the table on the publisher, and gosh if they
don't show up on the subscriber within about a minute. But I can
modify the SP on the publisher, and the changes to not show up on the
subscriber.
So, I'm wondering, is that even supposed to work?
Thanks.
Josh
Josh,
Changes to stored procedures and other database objects do not replicate in
SQL Server 2000. You have to wait for SQL 2005 to do that!
Jon Jahren
"jxstern" <jxstern@.nowhere.com> wrote in message
news:ugj5p0137irl7tvc5oi6nopf3g3u2lkpmf@.4ax.com...
> I set up TR on table T1. Works fine.
> While it was running, I tried adding an article, mysp.
> I couldn't quite figure out what to do to send the initial snapshot of
> just mysp, while replication was already running for T1.
> Eventually I ... well, I lost track. I think I deleted the
> subscription, then pushed it again, then finally told it explicitly to
> run the snapshot agent immediately. OK, the SP travelled.
> So, do I now have both the table and the SP replicating?
> No. I can add rows to the table on the publisher, and gosh if they
> don't show up on the subscriber within about a minute. But I can
> modify the SP on the publisher, and the changes to not show up on the
> subscriber.
> So, I'm wondering, is that even supposed to work?
> Thanks.
> Josh
>
|||Replication of stored procs is intended to be a one time event. If you
modify procs frequently and you wish to replicate them bundle them up in a
script and use sp_addscriptexec to deploy them to all subscribers. The proc
sp_addscriptexec only works for subscriptions deployed via UNCs. If are
using FTP subscribers you will have to create a seperate publication to
deploy your procs. It seems that the procs schemas are cached and if you
redeploy the snapshot after modifying the procs the updates won't be
replicated. You are best to drop the proc publication and recreate it.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"jxstern" <jxstern@.nowhere.com> wrote in message
news:ugj5p0137irl7tvc5oi6nopf3g3u2lkpmf@.4ax.com...
> I set up TR on table T1. Works fine.
> While it was running, I tried adding an article, mysp.
> I couldn't quite figure out what to do to send the initial snapshot of
> just mysp, while replication was already running for T1.
> Eventually I ... well, I lost track. I think I deleted the
> subscription, then pushed it again, then finally told it explicitly to
> run the snapshot agent immediately. OK, the SP travelled.
> So, do I now have both the table and the SP replicating?
> No. I can add rows to the table on the publisher, and gosh if they
> don't show up on the subscriber within about a minute. But I can
> modify the SP on the publisher, and the changes to not show up on the
> subscriber.
> So, I'm wondering, is that even supposed to work?
> Thanks.
> Josh
>
|||On Thu, 11 Nov 2004 11:33:11 -0500, "Hilary Cotter"
<hilary.cotter@.gmail.com> wrote:
>Replication of stored procs is intended to be a one time event. If you
>modify procs frequently and you wish to replicate them bundle them up in a
>script and use sp_addscriptexec to deploy them to all subscribers. The proc
>sp_addscriptexec only works for subscriptions deployed via UNCs. If are
>using FTP subscribers you will have to create a seperate publication to
>deploy your procs. It seems that the procs schemas are cached and if you
>redeploy the snapshot after modifying the procs the updates won't be
>replicated. You are best to drop the proc publication and recreate it.
Thanks to you and Jon.
Since the dialog lets you click tables and procs equally, and BOL is
not terribly clear on the matter, it's easy to think the procs do
replicate.
But, they do replicate in Yukon?
And back here in SQL2K, I thought there was something in BOL about
replicated SP's also executing on the subscriber, ... or is that
another confusion?
http://msdn.microsoft.com/library/de...limpl_0d2m.asp
I'd appreciate further insight on this.
Thanks!
Josh
|||On Thu, 11 Nov 2004 09:55:38 -0800, jxstern <jxstern@.nowhere.com>
wrote:
>And back here in SQL2K, I thought there was something in BOL about
>replicated SP's also executing on the subscriber, ... or is that
>another confusion?
>http://msdn.microsoft.com/library/de...limpl_0d2m.asp
>I'd appreciate further insight on this.
Also, the radio button that is supposed to turn on this SP execution
replication is greyed out on my dialog. Why would this be (because it
is an existing? Yes! Just as the warning box suggests, to enable it
I guess I'd have to drop and reinitialize, since when I add a new SP,
the option is enabled. Guess I don't have a question here after all,
but could still use any further info anyone cares to drop on me.)
J.
|||You could drop the article from the subscription and readd if you don't want
to reinitialize the complete publication.
sp_dropsubscription @.publication = 'northwindxxx'
, @.article = 'region'
, @.subscriber = 'pll-lt-16'
sp_droparticle @.publication = 'northwindxxx'
, @.article = 'region'
sp_refreshsubscriptions @.publication ='northwindxxx'
To Readd, just do the reverse. Running the snapshot agent will snapshot just
the article you need.
To avoid this type of issue, I tend to put all the stored procs in a
separate publication.
rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
While it was running, I tried adding an article, mysp.
I couldn't quite figure out what to do to send the initial snapshot of
just mysp, while replication was already running for T1.
Eventually I ... well, I lost track. I think I deleted the
subscription, then pushed it again, then finally told it explicitly to
run the snapshot agent immediately. OK, the SP travelled.
So, do I now have both the table and the SP replicating?
No. I can add rows to the table on the publisher, and gosh if they
don't show up on the subscriber within about a minute. But I can
modify the SP on the publisher, and the changes to not show up on the
subscriber.
So, I'm wondering, is that even supposed to work?
Thanks.
Josh
Josh,
Changes to stored procedures and other database objects do not replicate in
SQL Server 2000. You have to wait for SQL 2005 to do that!
Jon Jahren
"jxstern" <jxstern@.nowhere.com> wrote in message
news:ugj5p0137irl7tvc5oi6nopf3g3u2lkpmf@.4ax.com...
> I set up TR on table T1. Works fine.
> While it was running, I tried adding an article, mysp.
> I couldn't quite figure out what to do to send the initial snapshot of
> just mysp, while replication was already running for T1.
> Eventually I ... well, I lost track. I think I deleted the
> subscription, then pushed it again, then finally told it explicitly to
> run the snapshot agent immediately. OK, the SP travelled.
> So, do I now have both the table and the SP replicating?
> No. I can add rows to the table on the publisher, and gosh if they
> don't show up on the subscriber within about a minute. But I can
> modify the SP on the publisher, and the changes to not show up on the
> subscriber.
> So, I'm wondering, is that even supposed to work?
> Thanks.
> Josh
>
|||Replication of stored procs is intended to be a one time event. If you
modify procs frequently and you wish to replicate them bundle them up in a
script and use sp_addscriptexec to deploy them to all subscribers. The proc
sp_addscriptexec only works for subscriptions deployed via UNCs. If are
using FTP subscribers you will have to create a seperate publication to
deploy your procs. It seems that the procs schemas are cached and if you
redeploy the snapshot after modifying the procs the updates won't be
replicated. You are best to drop the proc publication and recreate it.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"jxstern" <jxstern@.nowhere.com> wrote in message
news:ugj5p0137irl7tvc5oi6nopf3g3u2lkpmf@.4ax.com...
> I set up TR on table T1. Works fine.
> While it was running, I tried adding an article, mysp.
> I couldn't quite figure out what to do to send the initial snapshot of
> just mysp, while replication was already running for T1.
> Eventually I ... well, I lost track. I think I deleted the
> subscription, then pushed it again, then finally told it explicitly to
> run the snapshot agent immediately. OK, the SP travelled.
> So, do I now have both the table and the SP replicating?
> No. I can add rows to the table on the publisher, and gosh if they
> don't show up on the subscriber within about a minute. But I can
> modify the SP on the publisher, and the changes to not show up on the
> subscriber.
> So, I'm wondering, is that even supposed to work?
> Thanks.
> Josh
>
|||On Thu, 11 Nov 2004 11:33:11 -0500, "Hilary Cotter"
<hilary.cotter@.gmail.com> wrote:
>Replication of stored procs is intended to be a one time event. If you
>modify procs frequently and you wish to replicate them bundle them up in a
>script and use sp_addscriptexec to deploy them to all subscribers. The proc
>sp_addscriptexec only works for subscriptions deployed via UNCs. If are
>using FTP subscribers you will have to create a seperate publication to
>deploy your procs. It seems that the procs schemas are cached and if you
>redeploy the snapshot after modifying the procs the updates won't be
>replicated. You are best to drop the proc publication and recreate it.
Thanks to you and Jon.
Since the dialog lets you click tables and procs equally, and BOL is
not terribly clear on the matter, it's easy to think the procs do
replicate.
But, they do replicate in Yukon?
And back here in SQL2K, I thought there was something in BOL about
replicated SP's also executing on the subscriber, ... or is that
another confusion?
http://msdn.microsoft.com/library/de...limpl_0d2m.asp
I'd appreciate further insight on this.
Thanks!
Josh
|||On Thu, 11 Nov 2004 09:55:38 -0800, jxstern <jxstern@.nowhere.com>
wrote:
>And back here in SQL2K, I thought there was something in BOL about
>replicated SP's also executing on the subscriber, ... or is that
>another confusion?
>http://msdn.microsoft.com/library/de...limpl_0d2m.asp
>I'd appreciate further insight on this.
Also, the radio button that is supposed to turn on this SP execution
replication is greyed out on my dialog. Why would this be (because it
is an existing? Yes! Just as the warning box suggests, to enable it
I guess I'd have to drop and reinitialize, since when I add a new SP,
the option is enabled. Guess I don't have a question here after all,
but could still use any further info anyone cares to drop on me.)
J.
|||You could drop the article from the subscription and readd if you don't want
to reinitialize the complete publication.
sp_dropsubscription @.publication = 'northwindxxx'
, @.article = 'region'
, @.subscriber = 'pll-lt-16'
sp_droparticle @.publication = 'northwindxxx'
, @.article = 'region'
sp_refreshsubscriptions @.publication ='northwindxxx'
To Readd, just do the reverse. Running the snapshot agent will snapshot just
the article you need.
To avoid this type of issue, I tend to put all the stored procs in a
separate publication.
rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Subscribe to:
Posts (Atom)