do I need to do my "snapshot" offline

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
>
>.
>

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

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

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

Hi,
I have set a transactional replication by Interprise
Manager, but when I create a subscriber using "Pull
Subscription to ..." it creates it succesfuly and even
gives me this message that "you have created the
subscription successfully", but it doesn't start
replication and gives this message in "Last Action" column:
" 'XServer' is not Configured as a Distributor. The step
failed".
Thank you very much.
Mathew,
this could be a naming issue.
Please try:
Use Master
go
Select @.@.Servername
This should return your current server name but if it
returns NULL then try:
Use Master
go
Sp_DropServer 'XServer'
GO
Use Master
go
Sp_Addserver 'XServer', 'local'
GO
Stop and Start SQL Services
HTH,
Paul Ibison
|||can the subscriber ping Xserver? Is XServer a Publisher/Distributor or
Distributor?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Mathew" <anonymous@.discussions.microsoft.com> wrote in message
news:2c7301c47e2b$46d95c00$a501280a@.phx.gbl...
> Hi,
> I have set a transactional replication by Interprise
> Manager, but when I create a subscriber using "Pull
> Subscription to ..." it creates it succesfuly and even
> gives me this message that "you have created the
> subscription successfully", but it doesn't start
> replication and gives this message in "Last Action" column:
> " 'XServer' is not Configured as a Distributor. The step
> failed".
> Thank you very much.
>
|||Paul,
Thank you so much. I have checked it, and it's correct.
Actually we have 2 servers in 2 different locations, and
both of them are called 'HAKIM-SERVER', so when I
run "Select @.@.ServerName" they answer 'HAKIM-SERVER'. I
have registered both of them in my "Enterprise
Manager",one by it's own name which is 'HAKIM-SERVER', and
the other one by it's IP address, as we have a VPN. It was
working until 2 weeks ago that our modem and router in the
other building were burned and we had to change them with
a brand new one. Our VPN is fine and there is nothing odd,
but when I go to to "Pull subscription
to 'xxx.xxx.xxx.xxx'... " on the server, which is in our
other location and I registered it with the IP address, to
create the subscribor, it gives me that error message
>--Original Message--
>Mathew,
>this could be a naming issue.
>Please try:
>Use Master
>go
>Select @.@.Servername
>This should return your current server name but if it
>returns NULL then try:
>Use Master
>go
>Sp_DropServer 'XServer'
>GO
>Use Master
>go
>Sp_Addserver 'XServer', 'local'
>GO
>Stop and Start SQL Services
>HTH,
>Paul Ibison
>
>.
>
|||Hi,
It's a Publisher/Distributor. I wrote a complete
explanation for the first respond from Paul. Whould you
please take a look at it?
Thnks again
>--Original Message--
>can the subscriber ping Xserver? Is XServer a
Publisher/Distributor or
>Distributor?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Mathew" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2c7301c47e2b$46d95c00$a501280a@.phx.gbl...
column:
>
>.
>
|||Mathew,
can you try using an alias rather than an IP address.
Regards,
Paul Ibison
|||Dear Paul,
How can I assign an alias to a remote server when it has
the same name as the local server.
Thanks,

>--Original Message--
>Mathew,
>can you try using an alias rather than an IP address.
>Regards,
>Paul Ibison
>
>.
>
|||Matthew,
in the client network utility, you can add an alias with the TCP/IP network
library. The server alias is any name you choose, and the server name can be
the IP address you have been using.
HTH,
Paul Ibison

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 .

Distribution job failed

When the distribution agent runs trying to apply the snapshot at the subscriber I get the following error 20253 sql 2005

End of file reached , terminator missing or field data incomplete in SQL 2005

Consult the BOL for more information on the bcp utility and its supported options.

To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below.
Consult the BOL for more information on the bcp utility and its supported options.

It could be that the snapshot files for one of your articles is corrupted. Try manually deleting the snapshot files from the snapshot folder and start over - regenerate the snapshot and try to apply it.

If it still fails, you need to tell us what's different about the article that's failing: what kinds of datatypes are in the table, did you manually edit any of the bcp files, what kind of subscriber is this, is this oracle publisher, etc.

|||

Hi,

Did you ever get this issue sorted?

I am receiving the same error message on 1 table during the snapshot sync phase on SQL2005 and it is failing the sync.

Cheers

Distribution job failed

When the distribution agent runs trying to apply the snapshot at the subscriber I get the following error 20253 sql 2005

End of file reached , terminator missing or field data incomplete in SQL 2005

Consult the BOL for more information on the bcp utility and its supported options.

To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below.
Consult the BOL for more information on the bcp utility and its supported options.

It could be that the snapshot files for one of your articles is corrupted. Try manually deleting the snapshot files from the snapshot folder and start over - regenerate the snapshot and try to apply it.

If it still fails, you need to tell us what's different about the article that's failing: what kinds of datatypes are in the table, did you manually edit any of the bcp files, what kind of subscriber is this, is this oracle publisher, etc.

|||

Hi,

Did you ever get this issue sorted?

I am receiving the same error message on 1 table during the snapshot sync phase on SQL2005 and it is failing the sync.

Cheers

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 ***

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...
>
>

Distribution Agent Latency question

I am delivering replicated data from a t3
connection to a t1 connection.. My ping times to
the subscriber are pretty good.. about 40ms
consistant. I have the following rates that seem
to be really slow for distributor/subscribers on
such good connections.
delivery rate (cmds/sec) = 212.0000
latency (msec) = 26890205 <--this seems high
# trans = 12
# cmds = 83784
avg. # cmds = 6982
This latency also seems to be making my
distribution database large on my distributor.
I am using default distribution agent profiles
and it's set to run every 15min.
I don't know - does 7 hours latency sound normal to you?
Latency is occasionally wrong. It reflects the delta between the time the
transaction entered the distribution database and the time it made it to the
subscriber.
If your distribution agent is not continuous the schedule is reflected in
the latency.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Combfilter" <adsf@.asdf.com> wrote in message
news:MPG.1be9951edb17d9149896d1@.news.newsreader.co m...
> I am delivering replicated data from a t3
> connection to a t1 connection.. My ping times to
> the subscriber are pretty good.. about 40ms
> consistant. I have the following rates that seem
> to be really slow for distributor/subscribers on
> such good connections.
> delivery rate (cmds/sec) = 212.0000
> latency (msec) = 26890205 <--this seems high
> # trans = 12
> # cmds = 83784
> avg. # cmds = 6982
> This latency also seems to be making my
> distribution database large on my distributor.
> I am using default distribution agent profiles
> and it's set to run every 15min.
|||In article <uEryCWJvEHA.3624
@.TK2MSFTNGP09.phx.gbl>, hilary.cotter@.gmail.com
says...
> I don't know - does 7 hours latency sound normal to you?
> Latency is occasionally wrong. It reflects the delta between the time the
> transaction entered the distribution database and the time it made it to the
> subscriber.
> If your distribution agent is not continuous the schedule is reflected in
> the latency.
>
Anything I should start looking at to resolve why
there is such high latency for this one
subscription only? I have the log reader set to
run continuous and the dist. agent to run every
15min.
tia
-comb

Distribution Agent - Replication Monitor warning

Hi,

I am replicating a large table with a number of indexes. During the initialisation phase (creating the indexes at the subscriber), I am seing the following error/warning in Replication monitor. When the initialisation is complete the error warnings dissapear.

The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

Is there a way to increase the 10 minute time-limit?

Thanks,
Priyanga

Hi Priyanga,

You can use the -KeepAliveMessage parameter of the distribution agent to specify a larger time interval for logging the "agent suspect" messages. However, I am a bit hesitant to recommend that you simply increase the time interval in general since you would probably want to be notified that the distribution agent may be stalled in a more timely manner when it is delivering incremental changes. On the other hand, delivery of snapshot is arguably a rare enough occurrence that the "agent suspect" messages should not become a significant source of annoyance.

In truth, the following factors had conspired to make the "agent suspect" messages almost unavoidable when delivering a large snapshot:

1) BCP API does not allow the distribution agent to log a progress message until every "BcpBatchSize" number of rows have been bulk-loaded into a subscriber table
2) We use a large "BcpBatchSize" (2^31 - delta) as the default "BcpBatchSize" in SQL2005 so the distribution agent can more readily meet the minimally-logged bulk-load requirements.
3) The time required to create an index on a large table probably exceeds the default -KeepAliveMessageInterval

Based on 1) and 2) above, distribution agent will mostly not be able to log a progress message in-between a bulk-load operation;and if the amount of data that needs to be bulk-loaded is large, the distribution agent will likely not be able to finish the bulk-load operation within the default -KeepAliveMessageInterval.

HTH

-Raymond

Distribution Agent

Hi ALL, Greetings
I have set up Transactional Replication for my database, the distribution
agent stated and copied all the data in to subscriber, but its is giving me
error to copy the data again in to table, because the data already in the
table. it is giving me error that unable to load the data again. i started
the distribution agent again, it is giving mer the same error and stopped.
if i delete the data in distribution database, and start the agent? it will
work or not? i mean whcih tables should i clean up and restart the agent.
please advise me
Thanks in advance
John
John,
there must be something more to this. The distribution agent won't try to
copy the data again. Once the commands have been delivered, the XACT number
ensures only subsequent commands in msrepl_commands are read. Please can you
post up the entire error message.
Rgds,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I think at this point it is best to drop your subscription, right click on
your publication and select properties.
Then click on the articles tab, click on article defaults, Table articles,
and OK. Click on the Snapshot tab, and ensure that either Drop the existing
table and re-create it is checked (preferably), or delete all data in the
existing table.
Then recreate your subscription and let it rip.
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
"John" <John@.discussions.microsoft.com> wrote in message
news:1C185E1E-B740-442E-B6D1-39F7DB1F6EDD@.microsoft.com...
> Hi ALL, Greetings
> I have set up Transactional Replication for my database, the distribution
> agent stated and copied all the data in to subscriber, but its is giving
me
> error to copy the data again in to table, because the data already in the
> table. it is giving me error that unable to load the data again. i started
> the distribution agent again, it is giving mer the same error and stopped.
> if i delete the data in distribution database, and start the agent? it
will
> work or not? i mean whcih tables should i clean up and restart the agent.
> please advise me
> Thanks in advance
> John
>
>
>

distributing subscriber database on multiple machine - install

Hi,

Is it possible to create a 'master' subscriber, back-up the database with the replication triggers and subscription intact, then restore this subscriber database on multiple subscribers during an install? The subscription is for an anonymous web-synced publication, up to 40 subscribers, and I am trying to avoid the initial snapshot download.

Thanks,

Darrell Young
Hi Darrell,

Yes, it is possible to avoid the initial snapshot downlad on your subscribers. An alternative maybe, to initialize snapshot from backup. More information can be found in book online.

Initializing a Merge Subscription Without a Snapshot
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/ee16af24-d7e2-4b65-a25f-dc89caba2ea2.htm

Initializing a Transactional Subscription Without a Snapshot
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/75c8c1f8-60bc-44a8-944b-d18d1f6bda11.htm

Regards,

Gary Chen|||Gary,

Thanks for the reply. The issue is have is that the subscribers will only have a subset of the data and the schema of the published database, so wholly backing up and restoring the published database is not practical. I had tried taking a subscriber database, then removing the subscription and creating a backup from that - the issue is that the rowguids are removed from the tables. I suppose the choice I have left is to create a backup of the published database, restore on a subscriber, manually remove the data and schema I am not including in the publication, then create a backup of that.
Thanks,

Darrell Young
|||

Hi Darrell,

Try the following,

- Create a snapshot at the publisher

- Download the initial snapshot to one subscriber S1

- Back up the subscriber database at S1

- Before you restore the back up database to other subscriber S2, drop the subscription that you have created before at subscriber S2

- Restore the S1 backup to S2 with KEEP_REPLICATION off (I believe by default it is off)

- Re-create the subcription with no-sync option.

You probably want to make sure that no data get updated to your publisher or subscriber database while you perform this backup-restore.

Try it on one or two machines to see if this work before you restore to all 40 machines.

Regards,

Gary Chen

Distributed Transaction Coordinator

Hi,
I understand if I use Immediate update from Subscriber to Pulisher, i will
use the function of DTC, how about if I make a schedule e.g. every 1 hour,
when it runs (Push/Pull), does that use DTC also?
Second ? is do i have to use Remove Server to run or I can use Linked
Server?
Thanks so much
Ed
Ed,
the schedule refers to the distribution agent and applies to commands going
from the publisher to the subscriber. if you want to 'postpone' the relay of
commands from the subscriber to the publisher, you can use queued updating
subscribers. Replication uses remote servers, but linked servers can also be
used for the DTC (have a look at :
http://support.microsoft.com/default...b;en-us;274098 )
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)