sql2k sp3
Howdy kids. Well by now you know the story as good as I
do. The Subscriber has more columns than the Publisher.
example:
Publisher:
[TransDate] [smalldatetime] NULL ,
Subscriber:
[TransDate] [smalldatetime] NULL ,
[TransDateShort] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TransDateMonth] [tinyint] NULL ,
[TransDateYear] [smallint] NULL ,
So, I can write procs to handle the inserts and updates
after the initial snapshot is done. But for that initial
snapshot Ive got problems. No matter how I get the data to
the Subscriber (dts, insert...select, etc.) , how do I
keep the Subscriber in sync? In other words, while Im
doing my initial population, data will be coming into the
Publisher? How do my process' tell the difference
betwenn "snapshot" and "new" data? I really don't want to
do this off hours. This cant be a new scenario.
TIA, ChrisR
The log reader reads transactions that are committed to the database after
the snapshot agent has bcp'd the data out to the snapshot. Nothing is
missed, guaranteed.
I posted something for you to try to generate the snapshot using the custom
sync object. Does it work?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:975f01c48619$a87ec080$a601280a@.phx.gbl...
> sql2k sp3
> Howdy kids. Well by now you know the story as good as I
> do. The Subscriber has more columns than the Publisher.
> example:
> Publisher:
> [TransDate] [smalldatetime] NULL ,
>
> Subscriber:
> [TransDate] [smalldatetime] NULL ,
> [TransDateShort] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [TransDateMonth] [tinyint] NULL ,
> [TransDateYear] [smallint] NULL ,
> So, I can write procs to handle the inserts and updates
> after the initial snapshot is done. But for that initial
> snapshot Ive got problems. No matter how I get the data to
> the Subscriber (dts, insert...select, etc.) , how do I
> keep the Subscriber in sync? In other words, while Im
> doing my initial population, data will be coming into the
> Publisher? How do my process' tell the difference
> betwenn "snapshot" and "new" data? I really don't want to
> do this off hours. This cant be a new scenario.
> TIA, ChrisR
>
|||Yes. And it is greatly appreciated. However, you didnt
specify what was supposed to be in
the 'c:\temp\TestWithDifferentSchema.sql'
script. I didn't want to be pushy, so I just kep diggin on
my own. Does that hold the answer to this as well? Ive
done my own since then but couldnt get it to work the way
I wanted.
>--Original Message--
>The log reader reads transactions that are committed to
the database after
>the snapshot agent has bcp'd the data out to the
snapshot. Nothing is
>missed, guaranteed.
>I posted something for you to try to generate the
snapshot using the custom
>sync object. Does it work?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:975f01c48619$a87ec080$a601280a@.phx.gbl...
to[vbcol=seagreen]
the[vbcol=seagreen]
to
>
>.
>
|||I just tried your first example, and mad the schema on the
subscriber match the view on the Publisher. I got an EOF
like with mine.
>--Original Message--
>The log reader reads transactions that are committed to
the database after
>the snapshot agent has bcp'd the data out to the
snapshot. Nothing is
>missed, guaranteed.
>I posted something for you to try to generate the
snapshot using the custom
>sync object. Does it work?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:975f01c48619$a87ec080$a601280a@.phx.gbl...
to[vbcol=seagreen]
the[vbcol=seagreen]
to
>
>.
>
Showing posts with label publisher. Show all posts
Showing posts with label publisher. Show all posts
Sunday, March 25, 2012
Wednesday, March 7, 2012
Distrubuter has sick hardware. Need to change box.
We run our distribution on another server ie not on the publisher.
We have had some hardware errors and to be honest it's an old machine and
spares are getting sparse.
Is there a step by step for moving distribution to another server?
Thanks
Paul
The short answer is no. The longer answer is no there is not step by step
guide for moving a distributor to another server.
An even longer answer is, no, but it is possible to do a tape restore to the
new distributor with the same name bounce it and hope for the best. I don't
like remote distributors. DBAs IMHO are too quick to migrate to them, and
you need to cluster them otherwise you have a single point of failure which
can be problematic for you as you have discovered.
Hilary Cotter
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
"Paul Cahill" <noname@.anon.com> wrote in message
news:e6FVdryzFHA.2792@.tk2msftngp13.phx.gbl...
> We run our distribution on another server ie not on the publisher.
> We have had some hardware errors and to be honest it's an old machine and
> spares are getting sparse.
> Is there a step by step for moving distribution to another server?
> Thanks
> Paul
>
We have had some hardware errors and to be honest it's an old machine and
spares are getting sparse.
Is there a step by step for moving distribution to another server?
Thanks
Paul
The short answer is no. The longer answer is no there is not step by step
guide for moving a distributor to another server.
An even longer answer is, no, but it is possible to do a tape restore to the
new distributor with the same name bounce it and hope for the best. I don't
like remote distributors. DBAs IMHO are too quick to migrate to them, and
you need to cluster them otherwise you have a single point of failure which
can be problematic for you as you have discovered.
Hilary Cotter
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
"Paul Cahill" <noname@.anon.com> wrote in message
news:e6FVdryzFHA.2792@.tk2msftngp13.phx.gbl...
> We run our distribution on another server ie not on the publisher.
> We have had some hardware errors and to be honest it's an old machine and
> spares are getting sparse.
> Is there a step by step for moving distribution to another server?
> Thanks
> Paul
>
distributor_admin not a local user?
The distribution server for my publisher is a live server, and the publisher is a test server that I wish to make it's own distributor. Whenever I try to select it as it's own distributor, I get an error that "distributor_admin" is not a local user. How
do I go about fixing this?
Thanks for your help,
Stephen
Where is your Distributor? Is it on the Publisher?
Is the Subscriber in the same domain as the Publisher?
How did you configure your Subscriber? Can you go to
Tools-Replication-Configure Publishers, Subscribers, Distributors, click on
the Subscribers tab, and click on the browse button (the three ellipses) to
the right of your server name?
Make sure you are using Impersonate the SQL Server Agent account on the
Publisher if you are in the same domain.
"Stephen A." <stephen.accetta@.honeywell.com> wrote in message
news:B6D49A36-D4D7-4D51-85B5-0BDFEA948056@.microsoft.com...
> The distribution server for my publisher is a live server, and the
publisher is a test server that I wish to make it's own distributor.
Whenever I try to select it as it's own distributor, I get an error that
"distributor_admin" is not a local user. How do I go about fixing this?
> Thanks for your help,
> Stephen
|||>>Where is your Distributor? Is it on the Publisher?
No. The distributor is machine A, the publisher is machine B, I wish to
make machine B it's own distributor
Same segment, but the subscriber (Machine C) is not on any domain, I
registered it by IP address. I'm not having any problems with the
subscriber, merely with making the publisher it's own distributor.
click on
ellipses) to
I'll check, but I think so.
the
I already set up a replication account, I merely wish to make the
publisher it's own distributor.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||The solution is easy!!! Register servers to SQL by using their names
or aliasses. Do not use ip numbers.
do I go about fixing this?
Thanks for your help,
Stephen
Where is your Distributor? Is it on the Publisher?
Is the Subscriber in the same domain as the Publisher?
How did you configure your Subscriber? Can you go to
Tools-Replication-Configure Publishers, Subscribers, Distributors, click on
the Subscribers tab, and click on the browse button (the three ellipses) to
the right of your server name?
Make sure you are using Impersonate the SQL Server Agent account on the
Publisher if you are in the same domain.
"Stephen A." <stephen.accetta@.honeywell.com> wrote in message
news:B6D49A36-D4D7-4D51-85B5-0BDFEA948056@.microsoft.com...
> The distribution server for my publisher is a live server, and the
publisher is a test server that I wish to make it's own distributor.
Whenever I try to select it as it's own distributor, I get an error that
"distributor_admin" is not a local user. How do I go about fixing this?
> Thanks for your help,
> Stephen
|||>>Where is your Distributor? Is it on the Publisher?
No. The distributor is machine A, the publisher is machine B, I wish to
make machine B it's own distributor
Same segment, but the subscriber (Machine C) is not on any domain, I
registered it by IP address. I'm not having any problems with the
subscriber, merely with making the publisher it's own distributor.
click on
ellipses) to
I'll check, but I think so.
the
I already set up a replication account, I merely wish to make the
publisher it's own distributor.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||The solution is easy!!! Register servers to SQL by using their names
or aliasses. Do not use ip numbers.
distributor_admin error
Hi,
When i try to configure publisher, distributor, subscriber on the same remote server, i am getting the following error:
SQL Server Enterprise Manager could not configure '90.168.XX.XXX' as the distributor for '90.168.xx.xxx'
Error: 18483: could not connect to server 'different name' beacuse 'distributor_admin' isn ot defined as a remote login at the server
I changed the MSSQLSERVER and the SQL Server Agent and a dependant file to run froma domain account, i gave that domain account all the privileges on the database and the master database aswell. but i keep getting the error.
Also why is the error showing diffetent server name than the '90.168...' name?
Will i have problems later if i donot set up alias names rather than ip address. If so, how can i set up alias names for SQL Server's
I am quite new to all this, any detailed answer could really help.
thanks
stummalaGot that fixed by re registering the server name to the result of @.@.SERVERNAME
When i try to configure publisher, distributor, subscriber on the same remote server, i am getting the following error:
SQL Server Enterprise Manager could not configure '90.168.XX.XXX' as the distributor for '90.168.xx.xxx'
Error: 18483: could not connect to server 'different name' beacuse 'distributor_admin' isn ot defined as a remote login at the server
I changed the MSSQLSERVER and the SQL Server Agent and a dependant file to run froma domain account, i gave that domain account all the privileges on the database and the master database aswell. but i keep getting the error.
Also why is the error showing diffetent server name than the '90.168...' name?
Will i have problems later if i donot set up alias names rather than ip address. If so, how can i set up alias names for SQL Server's
I am quite new to all this, any detailed answer could really help.
thanks
stummalaGot that fixed by re registering the server name to the result of @.@.SERVERNAME
Distributor to Subscriber History
I am using publisher as 2005 and subscriber as 2000. I am getting
Uninitialized Subscription. When I double click I don't see any message on
Distributor to Subscriber Histor. Where as I can see message for one of the
subscriber which is 2005.
Is that is do with 2000?
Nita
What type of repl? Sanpshot/Trans/Merge?
did you run your snapshot agent after adding subscription to 2000 svr?
Did your distribution agent push snapshot?
Try running Profiler on subscriber to pick up activity from distributor to
help troubleshoot.
ChrisB MCDBA
MSSQLConsulting.com
"NitaDBA" wrote:
> I am using publisher as 2005 and subscriber as 2000. I am getting
> Uninitialized Subscription. When I double click I don't see any message on
> Distributor to Subscriber Histor. Where as I can see message for one of the
> subscriber which is 2005.
> Is that is do with 2000?
> --
> Nita
Uninitialized Subscription. When I double click I don't see any message on
Distributor to Subscriber Histor. Where as I can see message for one of the
subscriber which is 2005.
Is that is do with 2000?
Nita
What type of repl? Sanpshot/Trans/Merge?
did you run your snapshot agent after adding subscription to 2000 svr?
Did your distribution agent push snapshot?
Try running Profiler on subscriber to pick up activity from distributor to
help troubleshoot.
ChrisB MCDBA
MSSQLConsulting.com
"NitaDBA" wrote:
> I am using publisher as 2005 and subscriber as 2000. I am getting
> Uninitialized Subscription. When I double click I don't see any message on
> Distributor to Subscriber Histor. Where as I can see message for one of the
> subscriber which is 2005.
> Is that is do with 2000?
> --
> Nita
Distributor SQLSERVERAgent Account
What account should the SqlServerAgent run under at the distributor
(dist. and publisher are same machine)?
Mine is running under .\sqladmin but I keep getting
Error: The schema script
'\\WSCLAN08\ReplData\unc\WSCLAN08_TMS_TMS\20050209 054316\Ltab_Years_1.sch'
could not be propagated to the subscriber.
At subscriber, SqlServerAgent is running under LocalSystem.
\\WSCLAN08\ReplData\ is public share and user has permissions (incl.
sqladmin).
Also, user is in PAL of publication (incl. sqladmin).
Thanks.
Is this pull? If so, your subscriber's sql server agent should be running
under the same account as the publisher's SQL Server agent, or an account
that is part of the local admin group on the publisher.
If this is not possible the SQL Server agent account on the subscriber
should have rigths to read the snapshot share and underlying files and
folders. You will probably have to craft another snapshot share on the
publisher for this.
For push subscriptions you should not have to worry about this unless you
have a remote distributor.
Hilary Cotter
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
<richerwin@.ubs-europe.org> wrote in message
news:1107929613.327118.195320@.l41g2000cwc.googlegr oups.com...
> What account should the SqlServerAgent run under at the distributor
> (dist. and publisher are same machine)?
> Mine is running under .\sqladmin but I keep getting
> Error: The schema script
> '\\WSCLAN08\ReplData\unc\WSCLAN08_TMS_TMS\20050209 054316\Ltab_Years_1.sch'
> could not be propagated to the subscriber.
> At subscriber, SqlServerAgent is running under LocalSystem.
> \\WSCLAN08\ReplData\ is public share and user has permissions (incl.
> sqladmin).
> Also, user is in PAL of publication (incl. sqladmin).
> Thanks.
>
|||Hilary Cotter wrote:
> Is this pull? If so, your subscriber's sql server agent should be
running
> under the same account as the publisher's SQL Server agent, or an
account
> that is part of the local admin group on the publisher.
Yes, this is annonymous pull over VPN.
BTW, if default installations are used, what is the difference between
running SQL Server Agent as LocalSystem and sa? What is LocalSystem? If
SQL Server Agent is to run under sqladmin, for example, does sqladmin
have to be created as a domain administrator on the subscribers'
computer?
> If this is not possible the SQL Server agent account on the
subscriber
> should have rigths to read the snapshot share and underlying files
and
> folders. You will probably have to craft another snapshot share on
the
> publisher for this.
I've got everbody and his uncle given full rights to the share, but
since my subscriber SQL Server Agent is running under LocalSystem, I
guess all bets are off as to why he can't access the share.
Sorry for all these basic questions, but my accts are all screwed up
and my brain is mush.
Thanks
|||There is some confusion here between service startup
accounts and SQL Server logins.
Service startup accounts can be found in control panel,
services and we are really interested in the sql server
agent one, which runs the replication jobs. This must be
a domain user account if you are to replicate from one
machine to another. LocalSystem can be used if you're
doing it all on one box.
If you're on a trusted environment, then the domain user
account on the subscriber for pull subscriptions must
have rights to the snapshot share. If it's non-trusted,
you'll need FTP.
'sa' is a SQL Server login. If your agents are set to use
impersonation, then the whole thing will be using windows
security and you can forget sql logins. If you're using a
non-trusted environment, you'll be obliged to use sql
logins.
This is explained in replication, security in BOL but
admittedly is not at all a straightforward topic
Rgds,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
>--Original Message--
>Hilary Cotter wrote:
agent should be[vbcol=seagreen]
>running
agent, or an
>account
>Yes, this is annonymous pull over VPN.
>BTW, if default installations are used, what is the
difference between
>running SQL Server Agent as LocalSystem and sa? What is
LocalSystem? If
>SQL Server Agent is to run under sqladmin, for example,
does sqladmin
>have to be created as a domain administrator on the
subscribers'[vbcol=seagreen]
>computer?
on the[vbcol=seagreen]
>subscriber
underlying files[vbcol=seagreen]
>and
snapshot share on
>the
>I've got everbody and his uncle given full rights to the
share, but
>since my subscriber SQL Server Agent is running under
LocalSystem, I
>guess all bets are off as to why he can't access the
share.
>Sorry for all these basic questions, but my accts are
all screwed up
>and my brain is mush.
>Thanks
>.
>
(dist. and publisher are same machine)?
Mine is running under .\sqladmin but I keep getting
Error: The schema script
'\\WSCLAN08\ReplData\unc\WSCLAN08_TMS_TMS\20050209 054316\Ltab_Years_1.sch'
could not be propagated to the subscriber.
At subscriber, SqlServerAgent is running under LocalSystem.
\\WSCLAN08\ReplData\ is public share and user has permissions (incl.
sqladmin).
Also, user is in PAL of publication (incl. sqladmin).
Thanks.
Is this pull? If so, your subscriber's sql server agent should be running
under the same account as the publisher's SQL Server agent, or an account
that is part of the local admin group on the publisher.
If this is not possible the SQL Server agent account on the subscriber
should have rigths to read the snapshot share and underlying files and
folders. You will probably have to craft another snapshot share on the
publisher for this.
For push subscriptions you should not have to worry about this unless you
have a remote distributor.
Hilary Cotter
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
<richerwin@.ubs-europe.org> wrote in message
news:1107929613.327118.195320@.l41g2000cwc.googlegr oups.com...
> What account should the SqlServerAgent run under at the distributor
> (dist. and publisher are same machine)?
> Mine is running under .\sqladmin but I keep getting
> Error: The schema script
> '\\WSCLAN08\ReplData\unc\WSCLAN08_TMS_TMS\20050209 054316\Ltab_Years_1.sch'
> could not be propagated to the subscriber.
> At subscriber, SqlServerAgent is running under LocalSystem.
> \\WSCLAN08\ReplData\ is public share and user has permissions (incl.
> sqladmin).
> Also, user is in PAL of publication (incl. sqladmin).
> Thanks.
>
|||Hilary Cotter wrote:
> Is this pull? If so, your subscriber's sql server agent should be
running
> under the same account as the publisher's SQL Server agent, or an
account
> that is part of the local admin group on the publisher.
Yes, this is annonymous pull over VPN.
BTW, if default installations are used, what is the difference between
running SQL Server Agent as LocalSystem and sa? What is LocalSystem? If
SQL Server Agent is to run under sqladmin, for example, does sqladmin
have to be created as a domain administrator on the subscribers'
computer?
> If this is not possible the SQL Server agent account on the
subscriber
> should have rigths to read the snapshot share and underlying files
and
> folders. You will probably have to craft another snapshot share on
the
> publisher for this.
I've got everbody and his uncle given full rights to the share, but
since my subscriber SQL Server Agent is running under LocalSystem, I
guess all bets are off as to why he can't access the share.
Sorry for all these basic questions, but my accts are all screwed up
and my brain is mush.
Thanks
|||There is some confusion here between service startup
accounts and SQL Server logins.
Service startup accounts can be found in control panel,
services and we are really interested in the sql server
agent one, which runs the replication jobs. This must be
a domain user account if you are to replicate from one
machine to another. LocalSystem can be used if you're
doing it all on one box.
If you're on a trusted environment, then the domain user
account on the subscriber for pull subscriptions must
have rights to the snapshot share. If it's non-trusted,
you'll need FTP.
'sa' is a SQL Server login. If your agents are set to use
impersonation, then the whole thing will be using windows
security and you can forget sql logins. If you're using a
non-trusted environment, you'll be obliged to use sql
logins.
This is explained in replication, security in BOL but
admittedly is not at all a straightforward topic
Rgds,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
>--Original Message--
>Hilary Cotter wrote:
agent should be[vbcol=seagreen]
>running
agent, or an
>account
>Yes, this is annonymous pull over VPN.
>BTW, if default installations are used, what is the
difference between
>running SQL Server Agent as LocalSystem and sa? What is
LocalSystem? If
>SQL Server Agent is to run under sqladmin, for example,
does sqladmin
>have to be created as a domain administrator on the
subscribers'[vbcol=seagreen]
>computer?
on the[vbcol=seagreen]
>subscriber
underlying files[vbcol=seagreen]
>and
snapshot share on
>the
>I've got everbody and his uncle given full rights to the
share, but
>since my subscriber SQL Server Agent is running under
LocalSystem, I
>guess all bets are off as to why he can't access the
share.
>Sorry for all these basic questions, but my accts are
all screwed up
>and my brain is mush.
>Thanks
>.
>
Distributor server
I have a merge replication environment with 1 publisher/distributor in the
same machine and 3 subscribers with a lot of data to merge. The link between
them is slow.
I'm with performance problems with my applications I think that job
replications could be punish this performance.
Setup another machine to be a Distributor Server is a good idea ?
thank you for assistance.
Tony
Absolutely not. The location of the distribution server has little impact
with merge replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"toryi" <toryi@.ig.com.br> wrote in message
news:%23oqjrx6AFHA.3504@.TK2MSFTNGP12.phx.gbl...
> I have a merge replication environment with 1 publisher/distributor in the
> same machine and 3 subscribers with a lot of data to merge. The link
between
> them is slow.
> I'm with performance problems with my applications I think that job
> replications could be punish this performance.
> Setup another machine to be a Distributor Server is a good idea ?
> thank you for assistance.
> Tony
>
|||What advantage I'll have in setup another machine to be a Distributor server
?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23iuKZW7AFHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Absolutely not. The location of the distribution server has little impact
> with merge replication.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "toryi" <toryi@.ig.com.br> wrote in message
> news:%23oqjrx6AFHA.3504@.TK2MSFTNGP12.phx.gbl...
the
> between
>
same machine and 3 subscribers with a lot of data to merge. The link between
them is slow.
I'm with performance problems with my applications I think that job
replications could be punish this performance.
Setup another machine to be a Distributor Server is a good idea ?
thank you for assistance.
Tony
Absolutely not. The location of the distribution server has little impact
with merge replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"toryi" <toryi@.ig.com.br> wrote in message
news:%23oqjrx6AFHA.3504@.TK2MSFTNGP12.phx.gbl...
> I have a merge replication environment with 1 publisher/distributor in the
> same machine and 3 subscribers with a lot of data to merge. The link
between
> them is slow.
> I'm with performance problems with my applications I think that job
> replications could be punish this performance.
> Setup another machine to be a Distributor Server is a good idea ?
> thank you for assistance.
> Tony
>
|||What advantage I'll have in setup another machine to be a Distributor server
?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23iuKZW7AFHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Absolutely not. The location of the distribution server has little impact
> with merge replication.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "toryi" <toryi@.ig.com.br> wrote in message
> news:%23oqjrx6AFHA.3504@.TK2MSFTNGP12.phx.gbl...
the
> between
>
Distributor cannot connect to subscriber
I am setting up my 2005 Replication system...
publisher = 2005 sp1
Subscriber = 2005 sp1
I created a publication for a single table. Then I created the subscription to another 2005 server. Had to add it as a subscriber in the wizard. Told it to do the snapshot right away.
Everything seems fine right up to the point where it tries to connect to the subscriber... I get a cannot connect error. I have tried all kinds of security context and accounts for the sql agent to run under but nothing seems to work. I cannot even get a linked server to work. I have the subscriber setup to accept remote connections.
I am not sure where to look at next... I never had this issue in 2000.
Did a little more testing. My distributor/publisher also has SQL 2000 on it. I think this might have something to do with it.
I created a linked server on my subscriber to my publisher/dist and it has no problem connecting what so ever.
Could it be that my pub/dist is using the wrong client files?
|||Hi William,
Are you using merge or transactional replication?
Is the subscription that you set up a push or pull? This will determine where the distribution or merge agent is running.
In you second note, you indicate that the distributor/publisher has SQL 2000. Is this in addition to SQL 2005, per the first note?
Assuming that you have SQL 2000 and SQL 2005 on the boxes, then you are using named instances for the SQL 2005 installations. If you have named instances, not only do you need to enable remote connections, but you need to insure the SQL Browser service is running for connectivity to work properly.
This link has some more information about SQL Browser -- http://msdn2.microsoft.com/en-us/library/ms165724.aspx
Hope this helps,
Tom
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hey Tom,
I am using transactional replication. The pub/dist has both 2000 (default) and 2005 (named) instances.
The subscription is a push subscription. I will take a look at the link you sent.
=== Edited by William Lowers @. 24 Jan 2007 4:11 PM UTC===
After reading the article I check my pub/dist. It is set to listen on TCP port 1434 which I know works cause I had to have the Firewall openned to get to the server. The SQL Browser server appears to be running.
Is there anything I can do to ensure it is working?
|||update
Since my subscriber is a dev server and if I mess it up it does not matter I did the following...
I created a publication on this server with itself being the distributer also.. that way it is setup just like the server with 2000 and 2005 on it. I then created a subscription from this server (2005 only) to the 2005 database on the 2000 and 2005 server.
No problem what so ever. So I am leaning towards the fact that it has to do with having both versions on one server...
Any idea? or is this a Bug?
|||Can you try the following connectivity test from command prompt?
at your dev machine, try to connect to the other box
osql -S<publisher_server> -U<user id> -P<password>
and try the connect from publisher machine to your dev machine as well. If both work, you can rule out the protocal enabling issue. If it works only from your dev box to publisher machine, but not the other way around, it might be as simple as enabling TCP and name/pipe from configuration manager.
Gary
|||Ok... I did as suggested and the publisher has no problem connecting to the subscriber...
So what does that mean?
I can connect via command line,SMS but not replication.
|||Ok... So the above statment is only half true. After doing the above I thought about looking at my path cause it executed the osql from the default directory that I was openned to....
So when I execute from 80/tools/binn I can connect without issue.
From 90/tools/binn I get this error
[SQL Native Client]TCP Provider: No connection could be made because the
target machine actively refused it.
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote
connections.
Can you check the protocal interface are enabled for TCP and name pipe?
You can find the instruction on the following posting from Mahesh,
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1059301&SiteID=1
Gary
|||I double checked and I already have that set. I cycled the server anyway... Still no luck.
What makes no sense to me is that I can connect with 2000 osql but not 2005 osql...
Is there anyway to check what libraries each client is using?
|||What was your OS? If windows 2000 server, would install MDAC 2.7/2.8 help?
Thanks.
This posting is provided AS IS with no warranties, and confers no rights.
|||OS is Windows 2003 SP1...
I am thinking what happened was the re-install of 2000 client tools that was done when we could not work on DTS...
I
|||I will guess that the re-applying of SQL 2000 bits after SQL 2005 was installed is what's causing the problem. Multiple versions of SQL Server installed on the same box is only supported provide you install the earlier version first, then the later version second. Re-installing the earlier version after the later version is already installed can mess things up. I suggest reinstalling/repairing your SQL 2005 installation.|||That is what I am thinking but since the server is a major player in our production environment, I don't think I will be allowed to...
So I am asking my boss for other options...
Thanks for all the help.
Distributor and publisher
Hi All:
i have two publications in my local computer, then i go to
distributor tab in publisher and distributor properties ,which shows two
distributor database there. that make sense coz i have two publication one
for each. is that right?
my question is why in the publisher tab of publisher and distributor
properties only has one distributorDB ?
Cheers
nick
I don't think so. Each publisher can use a single distribution database.
However you can have one or more distribution database per server.
So consider three publishers a, b, and c. Publisher a and publisher b use
the distribution database d1 on publisher c. Publisher c used a local
distribution database on publisher c called distribution. You could also
configure publisher a to use d2 (another distribution database) on publisher
c and publisher b could still use d1 on publisher c.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nick" <fsheng@.ebreathe.co.nz> wrote in message
news:O7ceZ4kCFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi All:
> i have two publications in my local computer, then i go to
> distributor tab in publisher and distributor properties ,which shows two
> distributor database there. that make sense coz i have two publication one
> for each. is that right?
> my question is why in the publisher tab of publisher and
distributor
> properties only has one distributorDB ?
>
> Cheers
> nick
>
|||Nick,
Hilary is correct, but what you are thinking of is
something I also assumed existed when I first saw the
option of several distribution databases. I still think
this would be a good idea as disaster recovery is far
more versatile if the msrepl_commands table could be
specific to a publication rather than a publisher ie if
one publisher has 2 transactional publications, they'd go
into separate distribution databases. Unfortunately this
would require a huge amount of coding to amalgamate
different notification data so I guess it's not going to
happen soon.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
>--Original Message--
>I don't think so. Each publisher can use a single
distribution database.
>However you can have one or more distribution database
per server.
>So consider three publishers a, b, and c. Publisher a
and publisher b use
>the distribution database d1 on publisher c. Publisher c
used a local
>distribution database on publisher c called
distribution. You could also
>configure publisher a to use d2 (another distribution
database) on publisher
>c and publisher b could still use d1 on publisher c.
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>Now available on Amazon.com
>http://www.amazon.com/gp/product/offer-
listing/0974973602/ref=dp_more-buying-choices_2//102-
1802128-2428137?condition=all[vbcol=seagreen]
>Looking for a FAQ on Indexing Services/SQL FTS
>http://www.indexserverfaq.com
>"Nick" <fsheng@.ebreathe.co.nz> wrote in message
>news:O7ceZ4kCFHA.2568@.TK2MSFTNGP10.phx.gbl...
computer, then i go to[vbcol=seagreen]
properties ,which shows two[vbcol=seagreen]
two publication one[vbcol=seagreen]
publisher and
>distributor
>
>.
>
|||Thanks Hilary and Paul
so in this case, two publication are sharing one distribution DB.
Cheers
nick
"Nick" <fsheng@.ebreathe.co.nz> wrote in message
news:O7ceZ4kCFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi All:
> i have two publications in my local computer, then i go to
> distributor tab in publisher and distributor properties ,which shows two
> distributor database there. that make sense coz i have two publication one
> for each. is that right?
> my question is why in the publisher tab of publisher and
distributor
> properties only has one distributorDB ?
>
> Cheers
> nick
>
i have two publications in my local computer, then i go to
distributor tab in publisher and distributor properties ,which shows two
distributor database there. that make sense coz i have two publication one
for each. is that right?
my question is why in the publisher tab of publisher and distributor
properties only has one distributorDB ?
Cheers
nick
I don't think so. Each publisher can use a single distribution database.
However you can have one or more distribution database per server.
So consider three publishers a, b, and c. Publisher a and publisher b use
the distribution database d1 on publisher c. Publisher c used a local
distribution database on publisher c called distribution. You could also
configure publisher a to use d2 (another distribution database) on publisher
c and publisher b could still use d1 on publisher c.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nick" <fsheng@.ebreathe.co.nz> wrote in message
news:O7ceZ4kCFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi All:
> i have two publications in my local computer, then i go to
> distributor tab in publisher and distributor properties ,which shows two
> distributor database there. that make sense coz i have two publication one
> for each. is that right?
> my question is why in the publisher tab of publisher and
distributor
> properties only has one distributorDB ?
>
> Cheers
> nick
>
|||Nick,
Hilary is correct, but what you are thinking of is
something I also assumed existed when I first saw the
option of several distribution databases. I still think
this would be a good idea as disaster recovery is far
more versatile if the msrepl_commands table could be
specific to a publication rather than a publisher ie if
one publisher has 2 transactional publications, they'd go
into separate distribution databases. Unfortunately this
would require a huge amount of coding to amalgamate
different notification data so I guess it's not going to
happen soon.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
>--Original Message--
>I don't think so. Each publisher can use a single
distribution database.
>However you can have one or more distribution database
per server.
>So consider three publishers a, b, and c. Publisher a
and publisher b use
>the distribution database d1 on publisher c. Publisher c
used a local
>distribution database on publisher c called
distribution. You could also
>configure publisher a to use d2 (another distribution
database) on publisher
>c and publisher b could still use d1 on publisher c.
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>Now available on Amazon.com
>http://www.amazon.com/gp/product/offer-
listing/0974973602/ref=dp_more-buying-choices_2//102-
1802128-2428137?condition=all[vbcol=seagreen]
>Looking for a FAQ on Indexing Services/SQL FTS
>http://www.indexserverfaq.com
>"Nick" <fsheng@.ebreathe.co.nz> wrote in message
>news:O7ceZ4kCFHA.2568@.TK2MSFTNGP10.phx.gbl...
computer, then i go to[vbcol=seagreen]
properties ,which shows two[vbcol=seagreen]
two publication one[vbcol=seagreen]
publisher and
>distributor
>
>.
>
|||Thanks Hilary and Paul
so in this case, two publication are sharing one distribution DB.
Cheers
nick
"Nick" <fsheng@.ebreathe.co.nz> wrote in message
news:O7ceZ4kCFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi All:
> i have two publications in my local computer, then i go to
> distributor tab in publisher and distributor properties ,which shows two
> distributor database there. that make sense coz i have two publication one
> for each. is that right?
> my question is why in the publisher tab of publisher and
distributor
> properties only has one distributorDB ?
>
> Cheers
> nick
>
Distribution times out...
I have a transactional replication working fine till to date. I have
the publisher on ServerA and subscriber on ServerB. The transactional
replication is running fine...
I need to get a copy of the subscriber to another server and replicate
the Publisher to that serverC while the replication on ServerB is
running.
ServerA.Publisher --> ServerB.Subscriber (runs transactional
replication)
ServerA.Publisher --> ServerC.NewSuscriber (This need to run without
impacting ServerB.Subscription). I cannot resynch the databases as It
will take days to resynch and will also kill some of my data on the
ServerB.subscriber that is not in ServerA.publisher
I went with the way they had explained on MSKB 320499. I stoped the
transactions coming into the publisher and copied a full backup of
publisher to ServerC. I created the new subscription wihout re-synch
option to ServerC. I also copied all the sp's that are needed from
ServerB.Subscriber.
When i try to enable the distribution agent between ServerA and ServerC
i get the following error..
{call sp_MSget_repl_commands(11, ?, 0, 7500000)}
Timeout expired
Timeout expired
(Source: ODBC SQL Server Driver (ODBC); Error number: S1T00)
------
The replication agent from ServerA to ServerB is running fine..! (Thank
God..!)
Can anyone help me figure out how to fix this issue between
ServerA.Publisher to ServerC.Subscriber?
Thanks in advance...!
Please can you confirm that you have the latest service pack on each server
(there were issues with "sp_MSget_repl_commands" before).
If this is not a service pack issue, try increasing the -QueryTimeOut
parameter of the distribution agent and also enable logging:
http://support.microsoft.com/?id=312292 to trap any more details for us.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul,
I have SP4 Installed on both servers. SQL 2000 Standard Edition on a
Windows 2003 Enterprise server.
Paul Ibison wrote:
> Please can you confirm that you have the latest service pack on each server
> (there were issues with "sp_MSget_repl_commands" before).
> If this is not a service pack issue, try increasing the -QueryTimeOut
> parameter of the distribution agent and also enable logging:
> http://support.microsoft.com/?id=312292 to trap any more details for us.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||OK - it's not that then
In that case try increasing the QueryTimeout parameter and do some logging
if there are any issues.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
the publisher on ServerA and subscriber on ServerB. The transactional
replication is running fine...
I need to get a copy of the subscriber to another server and replicate
the Publisher to that serverC while the replication on ServerB is
running.
ServerA.Publisher --> ServerB.Subscriber (runs transactional
replication)
ServerA.Publisher --> ServerC.NewSuscriber (This need to run without
impacting ServerB.Subscription). I cannot resynch the databases as It
will take days to resynch and will also kill some of my data on the
ServerB.subscriber that is not in ServerA.publisher
I went with the way they had explained on MSKB 320499. I stoped the
transactions coming into the publisher and copied a full backup of
publisher to ServerC. I created the new subscription wihout re-synch
option to ServerC. I also copied all the sp's that are needed from
ServerB.Subscriber.
When i try to enable the distribution agent between ServerA and ServerC
i get the following error..
{call sp_MSget_repl_commands(11, ?, 0, 7500000)}
Timeout expired
Timeout expired
(Source: ODBC SQL Server Driver (ODBC); Error number: S1T00)
------
The replication agent from ServerA to ServerB is running fine..! (Thank
God..!)
Can anyone help me figure out how to fix this issue between
ServerA.Publisher to ServerC.Subscriber?
Thanks in advance...!
Please can you confirm that you have the latest service pack on each server
(there were issues with "sp_MSget_repl_commands" before).
If this is not a service pack issue, try increasing the -QueryTimeOut
parameter of the distribution agent and also enable logging:
http://support.microsoft.com/?id=312292 to trap any more details for us.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul,
I have SP4 Installed on both servers. SQL 2000 Standard Edition on a
Windows 2003 Enterprise server.
Paul Ibison wrote:
> Please can you confirm that you have the latest service pack on each server
> (there were issues with "sp_MSget_repl_commands" before).
> If this is not a service pack issue, try increasing the -QueryTimeOut
> parameter of the distribution agent and also enable logging:
> http://support.microsoft.com/?id=312292 to trap any more details for us.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||OK - it's not that then
In that case try increasing the QueryTimeout parameter and do some logging
if there are any issues.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Saturday, February 25, 2012
Distribution DB on its own server
When would one consider to have the distribution database on another server
different from the publisher ?
The distributor can use a fair amount of resources on a very busy system so
if it starts to impact performance that would be a good clue.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eYeoG$H7EHA.1112@.TK2MSFTNGP14.phx.gbl...
> When would one consider to have the distribution database on another
> server
> different from the publisher ?
>
|||TO add to Andrew's comments, be aware that it is difficult to change the
server that the distribution database lives on because that server name is
in registry entries for each publisher and subscriber... So try to make a
good decision the first time...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eYeoG$H7EHA.1112@.TK2MSFTNGP14.phx.gbl...
> When would one consider to have the distribution database on another
server
> different from the publisher ?
>
different from the publisher ?
The distributor can use a fair amount of resources on a very busy system so
if it starts to impact performance that would be a good clue.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eYeoG$H7EHA.1112@.TK2MSFTNGP14.phx.gbl...
> When would one consider to have the distribution database on another
> server
> different from the publisher ?
>
|||TO add to Andrew's comments, be aware that it is difficult to change the
server that the distribution database lives on because that server name is
in registry entries for each publisher and subscriber... So try to make a
good decision the first time...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eYeoG$H7EHA.1112@.TK2MSFTNGP14.phx.gbl...
> When would one consider to have the distribution database on another
server
> different from the publisher ?
>
Distribution DB on its own server
When would one consider to have the distribution database on another server
different from the publisher ?
When locking becomes a problem. There is no hard and fast rule, ie when you
get more than 1023 transactions per second, or you are running on a 2 Ghz
processor.
If you have a high load replication solution you might want to migrate to a
remote distributor from the start. However, you must keep in mind that if
the distributor goes down your tlogs can balloon, and it can be difficult,
but not impossible, to recover from.
Generally I like to remove this single point of failure and use a local
distribution database.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O6sibAI7EHA.1564@.TK2MSFTNGP09.phx.gbl...
> When would one consider to have the distribution database on another
> server
> different from the publisher ?
>
different from the publisher ?
When locking becomes a problem. There is no hard and fast rule, ie when you
get more than 1023 transactions per second, or you are running on a 2 Ghz
processor.
If you have a high load replication solution you might want to migrate to a
remote distributor from the start. However, you must keep in mind that if
the distributor goes down your tlogs can balloon, and it can be difficult,
but not impossible, to recover from.
Generally I like to remove this single point of failure and use a local
distribution database.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O6sibAI7EHA.1564@.TK2MSFTNGP09.phx.gbl...
> When would one consider to have the distribution database on another
> server
> different from the publisher ?
>
Distribution DB on its own server
When would one consider to have the distribution database on another server
different from the publisher ?The distributor can use a fair amount of resources on a very busy system so
if it starts to impact performance that would be a good clue.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eYeoG$H7EHA.1112@.TK2MSFTNGP14.phx.gbl...
> When would one consider to have the distribution database on another
> server
> different from the publisher ?
>|||TO add to Andrew's comments, be aware that it is difficult to change the
server that the distribution database lives on because that server name is
in registry entries for each publisher and subscriber... So try to make a
good decision the first time...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eYeoG$H7EHA.1112@.TK2MSFTNGP14.phx.gbl...
> When would one consider to have the distribution database on another
server
> different from the publisher ?
>
different from the publisher ?The distributor can use a fair amount of resources on a very busy system so
if it starts to impact performance that would be a good clue.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eYeoG$H7EHA.1112@.TK2MSFTNGP14.phx.gbl...
> When would one consider to have the distribution database on another
> server
> different from the publisher ?
>|||TO add to Andrew's comments, be aware that it is difficult to change the
server that the distribution database lives on because that server name is
in registry entries for each publisher and subscriber... So try to make a
good decision the first time...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eYeoG$H7EHA.1112@.TK2MSFTNGP14.phx.gbl...
> When would one consider to have the distribution database on another
server
> different from the publisher ?
>
Distribution DB on its own server
When would one consider to have the distribution database on another server
different from the publisher ?The distributor can use a fair amount of resources on a very busy system so
if it starts to impact performance that would be a good clue.
--
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eYeoG$H7EHA.1112@.TK2MSFTNGP14.phx.gbl...
> When would one consider to have the distribution database on another
> server
> different from the publisher ?
>|||TO add to Andrew's comments, be aware that it is difficult to change the
server that the distribution database lives on because that server name is
in registry entries for each publisher and subscriber... So try to make a
good decision the first time...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eYeoG$H7EHA.1112@.TK2MSFTNGP14.phx.gbl...
> When would one consider to have the distribution database on another
server
> different from the publisher ?
>
different from the publisher ?The distributor can use a fair amount of resources on a very busy system so
if it starts to impact performance that would be a good clue.
--
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eYeoG$H7EHA.1112@.TK2MSFTNGP14.phx.gbl...
> When would one consider to have the distribution database on another
> server
> different from the publisher ?
>|||TO add to Andrew's comments, be aware that it is difficult to change the
server that the distribution database lives on because that server name is
in registry entries for each publisher and subscriber... So try to make a
good decision the first time...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eYeoG$H7EHA.1112@.TK2MSFTNGP14.phx.gbl...
> When would one consider to have the distribution database on another
server
> different from the publisher ?
>
Distribution CleanUp creates latency
Hi,
We have one publisher, one distributor and two subscriber. We run
transactional replication.
We recently upgraded all our hardware but now we find ourselves with an
unacceptable latency. When the "Distribution Cleanup" process fires it
take up to 4 minutes to run and will cause a complete replication pause
for 1-2 minutes at times.
In a perfect world, latency would always be under 2 seconds. I'll live
with the very seldom latency of 10 seconds.
What can be done to tame the distribution cleanup jog.
Regards,
CanadianGambler
*** Sent via Developersdex http://www.codecomments.com ***
Nothing. You could run the distribution clean up job nightly and see how the
pooled commands affect performance.
Hilary Cotter
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
"Canadian Gambler" <canadiangambler@.hotmail.com> wrote in message
news:%23xLD5HnsFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have one publisher, one distributor and two subscriber. We run
> transactional replication.
> We recently upgraded all our hardware but now we find ourselves with an
> unacceptable latency. When the "Distribution Cleanup" process fires it
> take up to 4 minutes to run and will cause a complete replication pause
> for 1-2 minutes at times.
> In a perfect world, latency would always be under 2 seconds. I'll live
> with the very seldom latency of 10 seconds.
> What can be done to tame the distribution cleanup jog.
> Regards,
> CanadianGambler
> *** Sent via Developersdex http://www.codecomments.com ***
|||I guess I should provide more info.
We relocated our equipment room and in the process upgraded the hardware
for all our database servers (1 publisher, 1 distributor and 2
subscribers).
We finished this move 2 weeks ago. The serious latency issue only
started about 6 days ago. We ran the same setup at the previous
location for about 15 months without latency greater than a few seconds.
So something is not quite right. Read on, help is on the way ...
After doing more research last night and this morning we find that some
of the tables that are "suppose to be cleaned up" contain more than
12,000,000 records.
So we are now approaching this problem from a different angle. 1.) Why
are those tables so big and the "clean up" job not "cleaning up". 2.)
How do we go about cleaning those up so that the "clean up job" doesn't
take so long to do it's thing. I saw some similar post so I'm hoping
that I will find some industry wisdom that can help us figure this one
out.
Regards,
Canadian Gambler
*** Sent via Developersdex http://www.codecomments.com ***
We have one publisher, one distributor and two subscriber. We run
transactional replication.
We recently upgraded all our hardware but now we find ourselves with an
unacceptable latency. When the "Distribution Cleanup" process fires it
take up to 4 minutes to run and will cause a complete replication pause
for 1-2 minutes at times.
In a perfect world, latency would always be under 2 seconds. I'll live
with the very seldom latency of 10 seconds.
What can be done to tame the distribution cleanup jog.
Regards,
CanadianGambler
*** Sent via Developersdex http://www.codecomments.com ***
Nothing. You could run the distribution clean up job nightly and see how the
pooled commands affect performance.
Hilary Cotter
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
"Canadian Gambler" <canadiangambler@.hotmail.com> wrote in message
news:%23xLD5HnsFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have one publisher, one distributor and two subscriber. We run
> transactional replication.
> We recently upgraded all our hardware but now we find ourselves with an
> unacceptable latency. When the "Distribution Cleanup" process fires it
> take up to 4 minutes to run and will cause a complete replication pause
> for 1-2 minutes at times.
> In a perfect world, latency would always be under 2 seconds. I'll live
> with the very seldom latency of 10 seconds.
> What can be done to tame the distribution cleanup jog.
> Regards,
> CanadianGambler
> *** Sent via Developersdex http://www.codecomments.com ***
|||I guess I should provide more info.
We relocated our equipment room and in the process upgraded the hardware
for all our database servers (1 publisher, 1 distributor and 2
subscribers).
We finished this move 2 weeks ago. The serious latency issue only
started about 6 days ago. We ran the same setup at the previous
location for about 15 months without latency greater than a few seconds.
So something is not quite right. Read on, help is on the way ...
After doing more research last night and this morning we find that some
of the tables that are "suppose to be cleaned up" contain more than
12,000,000 records.
So we are now approaching this problem from a different angle. 1.) Why
are those tables so big and the "clean up" job not "cleaning up". 2.)
How do we go about cleaning those up so that the "clean up job" doesn't
take so long to do it's thing. I saw some similar post so I'm hoping
that I will find some industry wisdom that can help us figure this one
out.
Regards,
Canadian Gambler
*** Sent via Developersdex http://www.codecomments.com ***
Friday, February 24, 2012
Distribution clean up: distribution job not removing snapshots
On SQL Server 2005 SP2 for Publisher and Distributor on the same instance, my old snapshots are not being cleaned up.
The following error is in the agent history:
Executed as user: Domain\MyUser. Could not remove directory '\\vmsql01\ReplData\unc\Publication_TRANSACTIONAL\20070702104416\'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed.
xp_cmdshell is enabled and I can run commands like :
exec master.dbo.xp_cmdshell ' md c:\TestFolder\'
The permissions to the snapshot share and file system are that Domain\MyUser has full control.
I have logged into the machine as this user and can remove snapshots so it does not seem to be a permission issue.
On other machines I do not get any errors but the snapshot folder still is not cleaned up.
Any suggestion as to what the problem could be?
Thanks,
Amy
hi amy,
i think there's a sql job in msdb
if i quite remember well to do the
clean up
you may manually trigger
regards,
joey
|||Update:
I ran filemonitor while the cleanup job was running and noticed it was telling me the user that owns the SQL Server Service had access denied errors. I gave that user permissions to the snapshot folder and it is now cleaning up (well any of the really old stuff it is not but anything that has expired lately it has cleaned up).
My SQLUser1 owns the SQL Server Server and SQLUser2 owns the SQL Server Agent Service.
I thought the SQLUser2 would be the one who required permissions to the snapshot folder to clean it up?
Does xp_cmdshell need to be enabled for the cleanup job to work? I have not found anything in BOL to suggest that but ...
The other machines are still running cleanup jobs without error and still not removing any snapshots.
Any suggestions?
Thanks,
Amy
Distribution Agent Startup Parameters
Hello,
I have scenario where I use two servers: SQL 2K sp4, on Win2K3 sp1. One
server is a publisher, the other a subscriber. The replication topology is
Transaction Replication with 1 queued updating subscriber. The distribution
agent resides on the publisher.
Firstly, I am aware of nosync replication, but my environment requires some
more testing before I can implement it, because I can't take the publisher
offline, and I believe some tweaks are needed to ensure that the data will
truly be in sync. This said, one of my publications has a table article with
234 million records to transfer when the article is reinitialized. I am
having problems with the transaction log growing so large on the subscriber,
that it consumes all server space, and effectively kills the distribution
agent.
To mitigate this, I have tried to use the -BcpBatchSize parameter to try to
get the rows per batch down to manageable size so that I can manually shrink
the transaction log intermittantly while the distribution agent is applying
the snapshot. My strategy is not working.
Questions:
1) does -BcpBatchSize directly control the application of the snapshotted
article's transaction commit size, or is this parameter used outside of the
distribution agent's application of a snapshot?
2) does using -UseInprocLoader affect the -BcpBatchSize parameter? I ask
because bulk insert is different from bcp.
3) would -CommitBatchSize be a more appropriate setting to allow me to
actively manage the transaction log on the subscriber while this huge table
is being transferred?
Thanks for any input,
John T
If you are using concurrent snapshot on SQL2000, your only hope will be to
make sure that you hit the minimal logging code path when the snapshot is
delivered to the subscriber as the *entire* snapshot will be applied in an
atomic transaction. At the risk of telling you something that you may know
already, this means that you need to:
1) Make sure that 'select into\bulk copy' is enabled at the subscriber
database
2) The target table is empty with at most one index or not having any
indexes when the bulk copy occurs. This may be tricky to enforce as the
SQL2000 index creation logic in the distribution agent is not very precise.
You may want to modify the .idx scripts to leave only the primary key index
and then manually create other indexes afterwards.
3) Set -BcpBatchSize to be *larger* than the maximum number of rows in your
published tables. 2^31 - delta should work fine. Notice that you may start
seeing "Agent is waiting for database backend.." messages since the
distribution agent will not be able to log any progress messages until the
entire bulk load operation is done. You have also want to increase
the -QueryTimeout of the distribution agent.
-Raymond
"John T" <JohnT@.discussions.microsoft.com> wrote in message
news:469D5803-1C9E-447E-9513-2AE04EFEB305@.microsoft.com...
> Hello,
> I have scenario where I use two servers: SQL 2K sp4, on Win2K3 sp1. One
> server is a publisher, the other a subscriber. The replication topology
> is
> Transaction Replication with 1 queued updating subscriber. The
> distribution
> agent resides on the publisher.
> Firstly, I am aware of nosync replication, but my environment requires
> some
> more testing before I can implement it, because I can't take the publisher
> offline, and I believe some tweaks are needed to ensure that the data will
> truly be in sync. This said, one of my publications has a table article
> with
> 234 million records to transfer when the article is reinitialized. I am
> having problems with the transaction log growing so large on the
> subscriber,
> that it consumes all server space, and effectively kills the distribution
> agent.
> To mitigate this, I have tried to use the -BcpBatchSize parameter to try
> to
> get the rows per batch down to manageable size so that I can manually
> shrink
> the transaction log intermittantly while the distribution agent is
> applying
> the snapshot. My strategy is not working.
> Questions:
> 1) does -BcpBatchSize directly control the application of the snapshotted
> article's transaction commit size, or is this parameter used outside of
> the
> distribution agent's application of a snapshot?
> 2) does using -UseInprocLoader affect the -BcpBatchSize parameter? I ask
> because bulk insert is different from bcp.
> 3) would -CommitBatchSize be a more appropriate setting to allow me to
> actively manage the transaction log on the subscriber while this huge
> table
> is being transferred?
> Thanks for any input,
> John T
|||Hi Raymond, I though bcpBatchSize is analogous to the batchsize in bcp,
which means that it controls how many rows will be applied at a time in a
batch. Won't setting it to a large value create a very large transaction log
for large tables?
Hilary Cotter
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
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
news:evddMLk8GHA.4084@.TK2MSFTNGP05.phx.gbl...
> If you are using concurrent snapshot on SQL2000, your only hope will be to
> make sure that you hit the minimal logging code path when the snapshot is
> delivered to the subscriber as the *entire* snapshot will be applied in an
> atomic transaction. At the risk of telling you something that you may know
> already, this means that you need to:
> 1) Make sure that 'select into\bulk copy' is enabled at the subscriber
> database
> 2) The target table is empty with at most one index or not having any
> indexes when the bulk copy occurs. This may be tricky to enforce as the
> SQL2000 index creation logic in the distribution agent is not very
> precise. You may want to modify the .idx scripts to leave only the primary
> key index and then manually create other indexes afterwards.
> 3) Set -BcpBatchSize to be *larger* than the maximum number of rows in
> your published tables. 2^31 - delta should work fine. Notice that you may
> start seeing "Agent is waiting for database backend.." messages since the
> distribution agent will not be able to log any progress messages until the
> entire bulk load operation is done. You have also want to increase
> the -QueryTimeout of the distribution agent.
> -Raymond
> "John T" <JohnT@.discussions.microsoft.com> wrote in message
> news:469D5803-1C9E-447E-9513-2AE04EFEB305@.microsoft.com...
>
|||Hilary, what you said is true, but the goal of my reccomendations is not to
have small transactions but to hit the minimal logging (or bulk-logged) code
path in the server which is the only feasible way to have a manageable log
size when a concurrent snapshot is applied in SQL2000. If a small batch size
is specified, the server will see the target table as non-empty after the
first batch is loaded and start going down the fully-logged code path after
that. And since there is an outer transaction bounding the delivery of a
concurrent snapshot in SQL2000, the log simply cannot be truncated even with
a small bcp batch size. I can understand why you think this is
counter-intuitive, but my suggestion had worked on a few occasions before
including the following:
http://groups.google.com/group/micro... e49f1fb79fe5
-Raymond
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uuGAtgk8GHA.2316@.TK2MSFTNGP04.phx.gbl...
> Hi Raymond, I though bcpBatchSize is analogous to the batchsize in bcp,
> which means that it controls how many rows will be applied at a time in a
> batch. Won't setting it to a large value create a very large transaction
> log for large tables?
> --
> Hilary Cotter
> 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
> "Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
> news:evddMLk8GHA.4084@.TK2MSFTNGP05.phx.gbl...
>
|||Ok, got it! Thanks Raymond. Still waiting on that beer you owe me;)
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
news:Oy1jKJl8GHA.2120@.TK2MSFTNGP03.phx.gbl...
> Hilary, what you said is true, but the goal of my reccomendations is not
> to have small transactions but to hit the minimal logging (or bulk-logged)
> code path in the server which is the only feasible way to have a
> manageable log size when a concurrent snapshot is applied in SQL2000. If a
> small batch size is specified, the server will see the target table as
> non-empty after the first batch is loaded and start going down the
> fully-logged code path after that. And since there is an outer transaction
> bounding the delivery of a concurrent snapshot in SQL2000, the log simply
> cannot be truncated even with a small bcp batch size. I can understand why
> you think this is counter-intuitive, but my suggestion had worked on a few
> occasions before including the following:
> http://groups.google.com/group/micro... e49f1fb79fe5
> -Raymond
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uuGAtgk8GHA.2316@.TK2MSFTNGP04.phx.gbl...
>
|||Raymond,
Just a couple of points of clarification:
1) If I am using simple recovery mode, I should get the same benefits in
this case as using bulk recovery mode, correct?
2) If I choose the schema option to drop the tables before the snapshot is
applied, this should meet all of the requirements in your second point,
correct?
3) Regarding the -BcpBatchSize setting of 2^31 - delta, what is the delta,
my desired batch commit size?
Thanks
John T
"Raymond Mak [MSFT]" wrote:
> If you are using concurrent snapshot on SQL2000, your only hope will be to
> make sure that you hit the minimal logging code path when the snapshot is
> delivered to the subscriber as the *entire* snapshot will be applied in an
> atomic transaction. At the risk of telling you something that you may know
> already, this means that you need to:
> 1) Make sure that 'select into\bulk copy' is enabled at the subscriber
> database
> 2) The target table is empty with at most one index or not having any
> indexes when the bulk copy occurs. This may be tricky to enforce as the
> SQL2000 index creation logic in the distribution agent is not very precise.
> You may want to modify the .idx scripts to leave only the primary key index
> and then manually create other indexes afterwards.
> 3) Set -BcpBatchSize to be *larger* than the maximum number of rows in your
> published tables. 2^31 - delta should work fine. Notice that you may start
> seeing "Agent is waiting for database backend.." messages since the
> distribution agent will not be able to log any progress messages until the
> entire bulk load operation is done. You have also want to increase
> the -QueryTimeout of the distribution agent.
> -Raymond
> "John T" <JohnT@.discussions.microsoft.com> wrote in message
> news:469D5803-1C9E-447E-9513-2AE04EFEB305@.microsoft.com...
>
>
|||Hi John,
In response to your inquiry:
1) Simple recovery mode is fine.
2) You may still need to make sure that only the primary key index got
created since the index creation logic in SQL2000 distribution agent is not
as optimal as it can be.
3) Delta is just a small number, you only need to make sure that the
resulting bcpbatchsize is bigger than the maximum number of rows in any of
your tables
Hope that helps,
-Raymond
"John T" <JohnT@.discussions.microsoft.com> wrote in message
news:A677C2FE-F672-4473-9B2E-725AFE500039@.microsoft.com...[vbcol=seagreen]
> Raymond,
> Just a couple of points of clarification:
> 1) If I am using simple recovery mode, I should get the same benefits in
> this case as using bulk recovery mode, correct?
> 2) If I choose the schema option to drop the tables before the snapshot is
> applied, this should meet all of the requirements in your second point,
> correct?
> 3) Regarding the -BcpBatchSize setting of 2^31 - delta, what is the delta,
> my desired batch commit size?
> Thanks
> John T
> "Raymond Mak [MSFT]" wrote:
|||Wait a minute, I think you are the one who owe me a beer, and I have proof
for that...
http://groups.google.com/group/micro... c299fb7328e2d
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23yI5FQr8GHA.3820@.TK2MSFTNGP02.phx.gbl...
> Ok, got it! Thanks Raymond. Still waiting on that beer you owe me;)
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
> news:Oy1jKJl8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>
|||freaking Google.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
news:enfPtCt8GHA.1492@.TK2MSFTNGP02.phx.gbl...
> Wait a minute, I think you are the one who owe me a beer, and I have proof
> for that...
> http://groups.google.com/group/micro... c299fb7328e2d
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23yI5FQr8GHA.3820@.TK2MSFTNGP02.phx.gbl...
>
|||This suggestion worked excellently. I went from filling up over 275 gigs of
drive space trying to deploy these large tables, to only using 975 megs of
drive space.
Thank you,
John T
"Raymond Mak [MSFT]" wrote:
> Hi John,
> In response to your inquiry:
> 1) Simple recovery mode is fine.
> 2) You may still need to make sure that only the primary key index got
> created since the index creation logic in SQL2000 distribution agent is not
> as optimal as it can be.
> 3) Delta is just a small number, you only need to make sure that the
> resulting bcpbatchsize is bigger than the maximum number of rows in any of
> your tables
> Hope that helps,
> -Raymond
> "John T" <JohnT@.discussions.microsoft.com> wrote in message
> news:A677C2FE-F672-4473-9B2E-725AFE500039@.microsoft.com...
>
>
I have scenario where I use two servers: SQL 2K sp4, on Win2K3 sp1. One
server is a publisher, the other a subscriber. The replication topology is
Transaction Replication with 1 queued updating subscriber. The distribution
agent resides on the publisher.
Firstly, I am aware of nosync replication, but my environment requires some
more testing before I can implement it, because I can't take the publisher
offline, and I believe some tweaks are needed to ensure that the data will
truly be in sync. This said, one of my publications has a table article with
234 million records to transfer when the article is reinitialized. I am
having problems with the transaction log growing so large on the subscriber,
that it consumes all server space, and effectively kills the distribution
agent.
To mitigate this, I have tried to use the -BcpBatchSize parameter to try to
get the rows per batch down to manageable size so that I can manually shrink
the transaction log intermittantly while the distribution agent is applying
the snapshot. My strategy is not working.
Questions:
1) does -BcpBatchSize directly control the application of the snapshotted
article's transaction commit size, or is this parameter used outside of the
distribution agent's application of a snapshot?
2) does using -UseInprocLoader affect the -BcpBatchSize parameter? I ask
because bulk insert is different from bcp.
3) would -CommitBatchSize be a more appropriate setting to allow me to
actively manage the transaction log on the subscriber while this huge table
is being transferred?
Thanks for any input,
John T
If you are using concurrent snapshot on SQL2000, your only hope will be to
make sure that you hit the minimal logging code path when the snapshot is
delivered to the subscriber as the *entire* snapshot will be applied in an
atomic transaction. At the risk of telling you something that you may know
already, this means that you need to:
1) Make sure that 'select into\bulk copy' is enabled at the subscriber
database
2) The target table is empty with at most one index or not having any
indexes when the bulk copy occurs. This may be tricky to enforce as the
SQL2000 index creation logic in the distribution agent is not very precise.
You may want to modify the .idx scripts to leave only the primary key index
and then manually create other indexes afterwards.
3) Set -BcpBatchSize to be *larger* than the maximum number of rows in your
published tables. 2^31 - delta should work fine. Notice that you may start
seeing "Agent is waiting for database backend.." messages since the
distribution agent will not be able to log any progress messages until the
entire bulk load operation is done. You have also want to increase
the -QueryTimeout of the distribution agent.
-Raymond
"John T" <JohnT@.discussions.microsoft.com> wrote in message
news:469D5803-1C9E-447E-9513-2AE04EFEB305@.microsoft.com...
> Hello,
> I have scenario where I use two servers: SQL 2K sp4, on Win2K3 sp1. One
> server is a publisher, the other a subscriber. The replication topology
> is
> Transaction Replication with 1 queued updating subscriber. The
> distribution
> agent resides on the publisher.
> Firstly, I am aware of nosync replication, but my environment requires
> some
> more testing before I can implement it, because I can't take the publisher
> offline, and I believe some tweaks are needed to ensure that the data will
> truly be in sync. This said, one of my publications has a table article
> with
> 234 million records to transfer when the article is reinitialized. I am
> having problems with the transaction log growing so large on the
> subscriber,
> that it consumes all server space, and effectively kills the distribution
> agent.
> To mitigate this, I have tried to use the -BcpBatchSize parameter to try
> to
> get the rows per batch down to manageable size so that I can manually
> shrink
> the transaction log intermittantly while the distribution agent is
> applying
> the snapshot. My strategy is not working.
> Questions:
> 1) does -BcpBatchSize directly control the application of the snapshotted
> article's transaction commit size, or is this parameter used outside of
> the
> distribution agent's application of a snapshot?
> 2) does using -UseInprocLoader affect the -BcpBatchSize parameter? I ask
> because bulk insert is different from bcp.
> 3) would -CommitBatchSize be a more appropriate setting to allow me to
> actively manage the transaction log on the subscriber while this huge
> table
> is being transferred?
> Thanks for any input,
> John T
|||Hi Raymond, I though bcpBatchSize is analogous to the batchsize in bcp,
which means that it controls how many rows will be applied at a time in a
batch. Won't setting it to a large value create a very large transaction log
for large tables?
Hilary Cotter
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
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
news:evddMLk8GHA.4084@.TK2MSFTNGP05.phx.gbl...
> If you are using concurrent snapshot on SQL2000, your only hope will be to
> make sure that you hit the minimal logging code path when the snapshot is
> delivered to the subscriber as the *entire* snapshot will be applied in an
> atomic transaction. At the risk of telling you something that you may know
> already, this means that you need to:
> 1) Make sure that 'select into\bulk copy' is enabled at the subscriber
> database
> 2) The target table is empty with at most one index or not having any
> indexes when the bulk copy occurs. This may be tricky to enforce as the
> SQL2000 index creation logic in the distribution agent is not very
> precise. You may want to modify the .idx scripts to leave only the primary
> key index and then manually create other indexes afterwards.
> 3) Set -BcpBatchSize to be *larger* than the maximum number of rows in
> your published tables. 2^31 - delta should work fine. Notice that you may
> start seeing "Agent is waiting for database backend.." messages since the
> distribution agent will not be able to log any progress messages until the
> entire bulk load operation is done. You have also want to increase
> the -QueryTimeout of the distribution agent.
> -Raymond
> "John T" <JohnT@.discussions.microsoft.com> wrote in message
> news:469D5803-1C9E-447E-9513-2AE04EFEB305@.microsoft.com...
>
|||Hilary, what you said is true, but the goal of my reccomendations is not to
have small transactions but to hit the minimal logging (or bulk-logged) code
path in the server which is the only feasible way to have a manageable log
size when a concurrent snapshot is applied in SQL2000. If a small batch size
is specified, the server will see the target table as non-empty after the
first batch is loaded and start going down the fully-logged code path after
that. And since there is an outer transaction bounding the delivery of a
concurrent snapshot in SQL2000, the log simply cannot be truncated even with
a small bcp batch size. I can understand why you think this is
counter-intuitive, but my suggestion had worked on a few occasions before
including the following:
http://groups.google.com/group/micro... e49f1fb79fe5
-Raymond
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uuGAtgk8GHA.2316@.TK2MSFTNGP04.phx.gbl...
> Hi Raymond, I though bcpBatchSize is analogous to the batchsize in bcp,
> which means that it controls how many rows will be applied at a time in a
> batch. Won't setting it to a large value create a very large transaction
> log for large tables?
> --
> Hilary Cotter
> 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
> "Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
> news:evddMLk8GHA.4084@.TK2MSFTNGP05.phx.gbl...
>
|||Ok, got it! Thanks Raymond. Still waiting on that beer you owe me;)
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
news:Oy1jKJl8GHA.2120@.TK2MSFTNGP03.phx.gbl...
> Hilary, what you said is true, but the goal of my reccomendations is not
> to have small transactions but to hit the minimal logging (or bulk-logged)
> code path in the server which is the only feasible way to have a
> manageable log size when a concurrent snapshot is applied in SQL2000. If a
> small batch size is specified, the server will see the target table as
> non-empty after the first batch is loaded and start going down the
> fully-logged code path after that. And since there is an outer transaction
> bounding the delivery of a concurrent snapshot in SQL2000, the log simply
> cannot be truncated even with a small bcp batch size. I can understand why
> you think this is counter-intuitive, but my suggestion had worked on a few
> occasions before including the following:
> http://groups.google.com/group/micro... e49f1fb79fe5
> -Raymond
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uuGAtgk8GHA.2316@.TK2MSFTNGP04.phx.gbl...
>
|||Raymond,
Just a couple of points of clarification:
1) If I am using simple recovery mode, I should get the same benefits in
this case as using bulk recovery mode, correct?
2) If I choose the schema option to drop the tables before the snapshot is
applied, this should meet all of the requirements in your second point,
correct?
3) Regarding the -BcpBatchSize setting of 2^31 - delta, what is the delta,
my desired batch commit size?
Thanks
John T
"Raymond Mak [MSFT]" wrote:
> If you are using concurrent snapshot on SQL2000, your only hope will be to
> make sure that you hit the minimal logging code path when the snapshot is
> delivered to the subscriber as the *entire* snapshot will be applied in an
> atomic transaction. At the risk of telling you something that you may know
> already, this means that you need to:
> 1) Make sure that 'select into\bulk copy' is enabled at the subscriber
> database
> 2) The target table is empty with at most one index or not having any
> indexes when the bulk copy occurs. This may be tricky to enforce as the
> SQL2000 index creation logic in the distribution agent is not very precise.
> You may want to modify the .idx scripts to leave only the primary key index
> and then manually create other indexes afterwards.
> 3) Set -BcpBatchSize to be *larger* than the maximum number of rows in your
> published tables. 2^31 - delta should work fine. Notice that you may start
> seeing "Agent is waiting for database backend.." messages since the
> distribution agent will not be able to log any progress messages until the
> entire bulk load operation is done. You have also want to increase
> the -QueryTimeout of the distribution agent.
> -Raymond
> "John T" <JohnT@.discussions.microsoft.com> wrote in message
> news:469D5803-1C9E-447E-9513-2AE04EFEB305@.microsoft.com...
>
>
|||Hi John,
In response to your inquiry:
1) Simple recovery mode is fine.
2) You may still need to make sure that only the primary key index got
created since the index creation logic in SQL2000 distribution agent is not
as optimal as it can be.
3) Delta is just a small number, you only need to make sure that the
resulting bcpbatchsize is bigger than the maximum number of rows in any of
your tables
Hope that helps,
-Raymond
"John T" <JohnT@.discussions.microsoft.com> wrote in message
news:A677C2FE-F672-4473-9B2E-725AFE500039@.microsoft.com...[vbcol=seagreen]
> Raymond,
> Just a couple of points of clarification:
> 1) If I am using simple recovery mode, I should get the same benefits in
> this case as using bulk recovery mode, correct?
> 2) If I choose the schema option to drop the tables before the snapshot is
> applied, this should meet all of the requirements in your second point,
> correct?
> 3) Regarding the -BcpBatchSize setting of 2^31 - delta, what is the delta,
> my desired batch commit size?
> Thanks
> John T
> "Raymond Mak [MSFT]" wrote:
|||Wait a minute, I think you are the one who owe me a beer, and I have proof
for that...
http://groups.google.com/group/micro... c299fb7328e2d
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23yI5FQr8GHA.3820@.TK2MSFTNGP02.phx.gbl...
> Ok, got it! Thanks Raymond. Still waiting on that beer you owe me;)
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
> news:Oy1jKJl8GHA.2120@.TK2MSFTNGP03.phx.gbl...
>
|||freaking Google.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
news:enfPtCt8GHA.1492@.TK2MSFTNGP02.phx.gbl...
> Wait a minute, I think you are the one who owe me a beer, and I have proof
> for that...
> http://groups.google.com/group/micro... c299fb7328e2d
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23yI5FQr8GHA.3820@.TK2MSFTNGP02.phx.gbl...
>
|||This suggestion worked excellently. I went from filling up over 275 gigs of
drive space trying to deploy these large tables, to only using 975 megs of
drive space.
Thank you,
John T
"Raymond Mak [MSFT]" wrote:
> Hi John,
> In response to your inquiry:
> 1) Simple recovery mode is fine.
> 2) You may still need to make sure that only the primary key index got
> created since the index creation logic in SQL2000 distribution agent is not
> as optimal as it can be.
> 3) Delta is just a small number, you only need to make sure that the
> resulting bcpbatchsize is bigger than the maximum number of rows in any of
> your tables
> Hope that helps,
> -Raymond
> "John T" <JohnT@.discussions.microsoft.com> wrote in message
> news:A677C2FE-F672-4473-9B2E-725AFE500039@.microsoft.com...
>
>
Distribution Agent Process
Hi,
Our replication topology (currently in design) will use transactional
replication to replicate from a OLTP server (publisher) to a Reporting server
(subscriber) in hope of near realtime results. Not all of the tables require
this though and the plan would be to use snapshot replication once a day for
these less updated tables. We’re then going to use log shipping to ship
tranaction log files out to remote servers with the databases originating
from the Reporting server.
My question is what does the distribution agent do to the existing
subscriber tables when it applies the new snapshot files? I guess I’m
looking for information as to whether it TRUNCATES the table, DELETES the
data, or DROPS the table prior to importing the BCP files from the new
snapshot.
SQL 2005 SP1 exclusively will be used in this environment.
Any information would be appreciated.
It depends on what option you set in the article property.
The property you should look for is in the article property under the
destination objects and Action if name is in use
The options, are keep data, drop table, truncate and delete filter data.
"dgcull" <dgcull@.discussions.microsoft.com> wrote in message
news:4A2EAA92-0353-4E83-AAC0-546BD5E0C5B1@.microsoft.com...
> Hi,
> Our replication topology (currently in design) will use transactional
> replication to replicate from a OLTP server (publisher) to a Reporting
> server
> (subscriber) in hope of near realtime results. Not all of the tables
> require
> this though and the plan would be to use snapshot replication once a day
> for
> these less updated tables. We're then going to use log shipping to ship
> tranaction log files out to remote servers with the databases originating
> from the Reporting server.
> My question is what does the distribution agent do to the existing
> subscriber tables when it applies the new snapshot files? I guess I'm
> looking for information as to whether it TRUNCATES the table, DELETES the
> data, or DROPS the table prior to importing the BCP files from the new
> snapshot.
> SQL 2005 SP1 exclusively will be used in this environment.
> Any information would be appreciated.
>
|||Gopal is right, but just to add - the default is to drop the existing table
on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Our replication topology (currently in design) will use transactional
replication to replicate from a OLTP server (publisher) to a Reporting server
(subscriber) in hope of near realtime results. Not all of the tables require
this though and the plan would be to use snapshot replication once a day for
these less updated tables. We’re then going to use log shipping to ship
tranaction log files out to remote servers with the databases originating
from the Reporting server.
My question is what does the distribution agent do to the existing
subscriber tables when it applies the new snapshot files? I guess I’m
looking for information as to whether it TRUNCATES the table, DELETES the
data, or DROPS the table prior to importing the BCP files from the new
snapshot.
SQL 2005 SP1 exclusively will be used in this environment.
Any information would be appreciated.
It depends on what option you set in the article property.
The property you should look for is in the article property under the
destination objects and Action if name is in use
The options, are keep data, drop table, truncate and delete filter data.
"dgcull" <dgcull@.discussions.microsoft.com> wrote in message
news:4A2EAA92-0353-4E83-AAC0-546BD5E0C5B1@.microsoft.com...
> Hi,
> Our replication topology (currently in design) will use transactional
> replication to replicate from a OLTP server (publisher) to a Reporting
> server
> (subscriber) in hope of near realtime results. Not all of the tables
> require
> this though and the plan would be to use snapshot replication once a day
> for
> these less updated tables. We're then going to use log shipping to ship
> tranaction log files out to remote servers with the databases originating
> from the Reporting server.
> My question is what does the distribution agent do to the existing
> subscriber tables when it applies the new snapshot files? I guess I'm
> looking for information as to whether it TRUNCATES the table, DELETES the
> data, or DROPS the table prior to importing the BCP files from the new
> snapshot.
> SQL 2005 SP1 exclusively will be used in this environment.
> Any information would be appreciated.
>
|||Gopal is right, but just to add - the default is to drop the existing table
on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Subscribe to:
Posts (Atom)