Hi,
I would like to know the following :
1. During replication , is the data being transmitted in binary format
2. where do i get to check the size of the whole replication(in bytes/KB) ?
3. where do i get to check the size of a single table being replicated (in
bytes/KB)
appreciate any advice
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...-msde/200512/1
Hi,
I know from some previous postings that sp_spaceused (see BOL for more
details) can be used to find out the size of a db/table but is that the size
being transmitted during Replication ?
and also how do i check the size of a packet (is it defaulted to 4096 k) ?
tks & rdgs
maxzsim wrote:
>Hi,
> I would like to know the following :
> 1. During replication , is the data being transmitted in binary format
> 2. where do i get to check the size of the whole replication(in bytes/KB) ?
> 3. where do i get to check the size of a single table being replicated (in
>bytes/KB)
> appreciate any advice
>tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...-msde/200512/1
Showing posts with label size. Show all posts
Showing posts with label size. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Replication row Commit Size
Is there a way to get replication to commit records in batches instead of all at once? I am in a 24/7 shop and some of my updates end up being thousands of rows and it locks the subscriber table for a few minutes sometimes. If I could get it to commit say every 1000 rows it might give me some relief in this area..
Or am I thinking about this wrong? If this is possible, would it help at all...
I
transactional replication adheres to the ACID properties, so if you apply 10,000 commands in one trasaction at the publisher, the distribution agent will apply the same commands in one transaction at the subscriber.
You have a couple options:
1. commit your commands at the publisher in smaller batches. That means if your update statement will affect 100k rows, break it up by updating 1,000 at a time until all 100k are completed.
2. You can use the logreader agent parameter -MaxCmdsInTran, which will break the ACID properties, but allow you to do what you want to do. This has other performance impact, but if you're committing thousands and thousands (or millions) of rows in a single transaction, this can offer some relief. You can find more information about this parameter in Books Online.
I'd recommend #1 before #2.
|||Thanks for that response... it was what I was expecting to hear but just wanted to check. My servers typically only get updated once a day with major updates and the updates can happen over time as they are not mission critical. The servers are only content related so as long as they get updated they are happy...
Thannks again for the answer.
Friday, March 23, 2012
replication question
What is the thought on replicating over WAN links.
I have a DB hosted at an off-site ISP it is about 1.2 GB in size. I would
like a local copy of the DB. So I would like to set up one-way replication.
The ISP says that replication is resource intensive. We have a 768K
Fractional T-1 they tell me that replication would suck up all that
bandwidth? There aren't a lot of updates that go on the DB in a day over
time there are a lot of updates but on a typical day there aren't a terrible
lot.
So I'm trying to gauge whether the ISP is right or if they aren't really
sure in what they are telling me. I would just
like to have a local copy of our DB on-site in-case the ISP's site goes down
for whatever reason.
Thanks,
Geo
Hi Marc
If you are applying normal transactional replication and don't have a lot of
transactions then your line would be more than adequate. The only bandwidth
intensive process would be when you initialize and apply a 1.2 GB snapshot
accross the line. Then SQL will use as much bandwidth as possible.
We've run transactional replication successfully on 256k lines without it
having a huge impact on the line. Again depending on the transaction volumes.
Regards
Cube
http://www.sqlserver.co.za
"marc" wrote:
> What is the thought on replicating over WAN links.
> I have a DB hosted at an off-site ISP it is about 1.2 GB in size. I would
> like a local copy of the DB. So I would like to set up one-way replication.
> The ISP says that replication is resource intensive. We have a 768K
> Fractional T-1 they tell me that replication would suck up all that
> bandwidth? There aren't a lot of updates that go on the DB in a day over
> time there are a lot of updates but on a typical day there aren't a terrible
> lot.
> So I'm trying to gauge whether the ISP is right or if they aren't really
> sure in what they are telling me. I would just
> like to have a local copy of our DB on-site in-case the ISP's site goes down
> for whatever reason.
> Thanks,
> Geo
>
>
I have a DB hosted at an off-site ISP it is about 1.2 GB in size. I would
like a local copy of the DB. So I would like to set up one-way replication.
The ISP says that replication is resource intensive. We have a 768K
Fractional T-1 they tell me that replication would suck up all that
bandwidth? There aren't a lot of updates that go on the DB in a day over
time there are a lot of updates but on a typical day there aren't a terrible
lot.
So I'm trying to gauge whether the ISP is right or if they aren't really
sure in what they are telling me. I would just
like to have a local copy of our DB on-site in-case the ISP's site goes down
for whatever reason.
Thanks,
Geo
Hi Marc
If you are applying normal transactional replication and don't have a lot of
transactions then your line would be more than adequate. The only bandwidth
intensive process would be when you initialize and apply a 1.2 GB snapshot
accross the line. Then SQL will use as much bandwidth as possible.
We've run transactional replication successfully on 256k lines without it
having a huge impact on the line. Again depending on the transaction volumes.
Regards
Cube
http://www.sqlserver.co.za
"marc" wrote:
> What is the thought on replicating over WAN links.
> I have a DB hosted at an off-site ISP it is about 1.2 GB in size. I would
> like a local copy of the DB. So I would like to set up one-way replication.
> The ISP says that replication is resource intensive. We have a 768K
> Fractional T-1 they tell me that replication would suck up all that
> bandwidth? There aren't a lot of updates that go on the DB in a day over
> time there are a lot of updates but on a typical day there aren't a terrible
> lot.
> So I'm trying to gauge whether the ISP is right or if they aren't really
> sure in what they are telling me. I would just
> like to have a local copy of our DB on-site in-case the ISP's site goes down
> for whatever reason.
> Thanks,
> Geo
>
>
Tuesday, March 20, 2012
replication please help
Hi gurus, plz help me out!
SNAPSHOT! :mad:
Publisher: 1.5 GB of size. Push merge subscription.256 K full duplex internet connection.
Subscriber: physically at different country. T1 connection. I dial VPN to connect to the publisher which is in seperate domain. Since the domains are different, i am using SQL authentication for my replication process.
The problem with the snapshot is "disconnectivity". Merge agent starts form beginning after running for 2 or 3 hours. I have following entries for MSrepl_errors table in the distribution database:
error_code -2147201001 "The process could not deliver the snapshot to the Subscriber"
error_code 20037 "The process could not bulk copy into table '"dbo"."MY_TABLE"'"
error_code 11 "General network error. Check your network documentation."
The thing that bothers me much is that the VPN scarcely disconnects, only a single packet drop forces the merge agent to retry!!!. To isolate the problem i started ping reqest from publisher to subscriber; i saw merge agent to fail when a packet loss occured for only one second. VPN did not disconnect.
Currently i've chosen "slow link" profile for merge agent.
I started the snapshot 2 days ago and it restarts after every 2 or 3 hours.
Why the heck merge agent starts from the beginning; couldn't it be handled to start where it was when the connection was lost? Packet-loss is normal on the internet connections. I would love to know how u people replicate VLDBs over the internet.
I tried dynamic snapshot but found it rather risky; the merge agent stops with success during the middle of snapshot if connection is dropped leaving the subscriber with inconsistent data.
Any ideas and tips would be very much appreciated.
Howdy!I need u Guys!! :(|||An awful lot depends on what got lost, and where it got lost in the process. Merge replication is also a hodge-podge kind of thing, it is usually "interesting" trying to figure out where the "beginning" of it is, since it scarcely differentiates the difference between tables, much less any order to their changes.
Connections are definitely not "parts is parts" around the world. A connection from one part of the US to another is frequently "rock solid". A connection from here to London is very nearly as good, as is one from London to Sydney. One from the United States to Sydney is absolutely awful. So, we move the data going to Sydney through London... Even though it ends up going most of the way around the world instead of only about one fourth of the way, it is still ots more reliable.
Unless you have lots of time, energy, and money, this is where I'd bring in the "hired guns" to solve the problem. If you bring in someone like Microsoft Professional Services that deals with the various IP providers, knows the problems that exist between point A and point B, and understands the way that your specific replication setup is configured, you'll get an answer much faster and in the long run cheaper than trying to find the solution yourself.
-PatP|||Hi, Pat. Thanx for ur feedback.
I've tried this thing.
Publisher name SERVER1
Subscriber name SERVER2
I uploaded the SNAPSHOT.CAB (300 MB) from the publisher using download manager (if connection disconnects, it resumes where the connection was lost). It got uploaded in 3 hours.
Now i dropped the push merge subscription. Created Pull merge subscription at SERVER2. Placed the SNAPSHOT.CAB into local C drive of SERVER2.
The location i've extracted the snapshot file is:
C:\unc\SERVER1_MBDB_MBDB_PUBLICATION\2004061923325 2
In the Snapshot Location dialogbox for pull merge agent, I specified this location to force the agent to pick snapshot locally.
When i run the merge agent; it fails, for it searches the snapshot files from the default location at the publisher rather than the one i specified :mad: . The error is:
The schema script '\\SERVER1\C$\Program Files\Microsoft SQL Server\MSSQL\ReplData\unc\SERVER1_MBDB_MBDB_PUBLIC ATION\20040619233252\UR_TABLE_1.sch' could not be propagated to the subscriber.
Could not find the cabinet file 'snapshot.cab' in folder 'c:\unc\SERVER1_MBDB_MBDB_PUBLICATION\200406192332 52\unc\SERVER1_MBDB_MBDB_PUBLICATION\2004061923325 2\'
How can i square this location problem.
Any Help!!
Thanx for bearing along!|||If you are using a Merge PULL subscription, check the subscription properties at the subscriber to be sure that they specify the correct directory.
I'm a bit nervous about making suggestions, since you are making some pretty significant changes to what you are doing (push versus pull, where the agent exectutes, etc). Be careful to think out what you are doing!
-PatP|||Hi, thanx again 4 ur reply.
I did specify the correct directory where i placed the snapshot files. But the merge agent tries to access the snapshot from publisher's default directory rather than the local path.
I am tryin to use pull instead of push, for push won't work in my case due to disconnection. However i could move the whole snapshot.cab file to subscriber using a download manager despite the connection is lost b/w the transfer. But it isn't working either here. :confused:
SNAPSHOT! :mad:
Publisher: 1.5 GB of size. Push merge subscription.256 K full duplex internet connection.
Subscriber: physically at different country. T1 connection. I dial VPN to connect to the publisher which is in seperate domain. Since the domains are different, i am using SQL authentication for my replication process.
The problem with the snapshot is "disconnectivity". Merge agent starts form beginning after running for 2 or 3 hours. I have following entries for MSrepl_errors table in the distribution database:
error_code -2147201001 "The process could not deliver the snapshot to the Subscriber"
error_code 20037 "The process could not bulk copy into table '"dbo"."MY_TABLE"'"
error_code 11 "General network error. Check your network documentation."
The thing that bothers me much is that the VPN scarcely disconnects, only a single packet drop forces the merge agent to retry!!!. To isolate the problem i started ping reqest from publisher to subscriber; i saw merge agent to fail when a packet loss occured for only one second. VPN did not disconnect.
Currently i've chosen "slow link" profile for merge agent.
I started the snapshot 2 days ago and it restarts after every 2 or 3 hours.
Why the heck merge agent starts from the beginning; couldn't it be handled to start where it was when the connection was lost? Packet-loss is normal on the internet connections. I would love to know how u people replicate VLDBs over the internet.
I tried dynamic snapshot but found it rather risky; the merge agent stops with success during the middle of snapshot if connection is dropped leaving the subscriber with inconsistent data.
Any ideas and tips would be very much appreciated.
Howdy!I need u Guys!! :(|||An awful lot depends on what got lost, and where it got lost in the process. Merge replication is also a hodge-podge kind of thing, it is usually "interesting" trying to figure out where the "beginning" of it is, since it scarcely differentiates the difference between tables, much less any order to their changes.
Connections are definitely not "parts is parts" around the world. A connection from one part of the US to another is frequently "rock solid". A connection from here to London is very nearly as good, as is one from London to Sydney. One from the United States to Sydney is absolutely awful. So, we move the data going to Sydney through London... Even though it ends up going most of the way around the world instead of only about one fourth of the way, it is still ots more reliable.
Unless you have lots of time, energy, and money, this is where I'd bring in the "hired guns" to solve the problem. If you bring in someone like Microsoft Professional Services that deals with the various IP providers, knows the problems that exist between point A and point B, and understands the way that your specific replication setup is configured, you'll get an answer much faster and in the long run cheaper than trying to find the solution yourself.
-PatP|||Hi, Pat. Thanx for ur feedback.
I've tried this thing.
Publisher name SERVER1
Subscriber name SERVER2
I uploaded the SNAPSHOT.CAB (300 MB) from the publisher using download manager (if connection disconnects, it resumes where the connection was lost). It got uploaded in 3 hours.
Now i dropped the push merge subscription. Created Pull merge subscription at SERVER2. Placed the SNAPSHOT.CAB into local C drive of SERVER2.
The location i've extracted the snapshot file is:
C:\unc\SERVER1_MBDB_MBDB_PUBLICATION\2004061923325 2
In the Snapshot Location dialogbox for pull merge agent, I specified this location to force the agent to pick snapshot locally.
When i run the merge agent; it fails, for it searches the snapshot files from the default location at the publisher rather than the one i specified :mad: . The error is:
The schema script '\\SERVER1\C$\Program Files\Microsoft SQL Server\MSSQL\ReplData\unc\SERVER1_MBDB_MBDB_PUBLIC ATION\20040619233252\UR_TABLE_1.sch' could not be propagated to the subscriber.
Could not find the cabinet file 'snapshot.cab' in folder 'c:\unc\SERVER1_MBDB_MBDB_PUBLICATION\200406192332 52\unc\SERVER1_MBDB_MBDB_PUBLICATION\2004061923325 2\'
How can i square this location problem.
Any Help!!
Thanx for bearing along!|||If you are using a Merge PULL subscription, check the subscription properties at the subscriber to be sure that they specify the correct directory.
I'm a bit nervous about making suggestions, since you are making some pretty significant changes to what you are doing (push versus pull, where the agent exectutes, etc). Be careful to think out what you are doing!
-PatP|||Hi, thanx again 4 ur reply.
I did specify the correct directory where i placed the snapshot files. But the merge agent tries to access the snapshot from publisher's default directory rather than the local path.
I am tryin to use pull instead of push, for push won't work in my case due to disconnection. However i could move the whole snapshot.cab file to subscriber using a download manager despite the connection is lost b/w the transfer. But it isn't working either here. :confused:
Friday, March 9, 2012
Replication on MSDE2000 and db size problem
Hi,
We have following scenario:
2 PC on WinXP + MSDE2000 + SP4 with production db. Secondary PC works as
failover backup. DBs are connected by merge replication where backup is as
subscriber.
The problem is related to one table with binary column (2K) and its growing
size (over 100MB per day).
sp_spaceused product_map
name rows reserved data index_size unused
Product_map 24238 1056648KB 130448KB 13520KB 912640KB
It seems that on insert rows server allocates more space than need. This
problem occures only when system works on publisher db. It is ok when I
switch it to backup PC.
I have the same systems on W2k + MSDE2000 + SP3a + merge replication and
don't observe the problem like this.
could you help me how to diagnose it more deeply and solve the problem?
Janek
A couple of points 1) are you using text in row?
2) Are you sure the data in this column is the same on the publisher and
subscriber. Depending on how you update your text or image data it may not
be replicated.
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
"Janek" <Janek@.discussions.microsoft.com> wrote in message
news:456DC0D6-FEDC-43BF-8A22-B6CE22069464@.microsoft.com...
> Hi,
> We have following scenario:
> 2 PC on WinXP + MSDE2000 + SP4 with production db. Secondary PC works as
> failover backup. DBs are connected by merge replication where backup is as
> subscriber.
> The problem is related to one table with binary column (2K) and its
> growing
> size (over 100MB per day).
> sp_spaceused product_map
> name rows reserved data index_size
> unused
> Product_map 24238 1056648KB 130448KB 13520KB 912640KB
> It seems that on insert rows server allocates more space than need. This
> problem occures only when system works on publisher db. It is ok when I
> switch it to backup PC.
> I have the same systems on W2k + MSDE2000 + SP3a + merge replication and
> don't observe the problem like this.
> could you help me how to diagnose it more deeply and solve the problem?
> Janek
|||1) data type for table is: int, smallint, int, char(11), smallint, smallint,
binary(2001), bit, datetime, int
2) All data is replicating, I can see it on reports on each machines. From
sp_spaceused report I see almost 1GB of unused space.
I know only one workaround:
dbcc dbreindex (.... + shrink db - a few cycles makes db to similar
backup db size.
At the moment system is working on backup PC without problem and new data is
refreshed to publisher.
Janek
"Hilary Cotter" wrote:
> A couple of points 1) are you using text in row?
> 2) Are you sure the data in this column is the same on the publisher and
> subscriber. Depending on how you update your text or image data it may not
> be replicated.
> --
> 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
>
> "Janek" <Janek@.discussions.microsoft.com> wrote in message
> news:456DC0D6-FEDC-43BF-8A22-B6CE22069464@.microsoft.com...
>
>
We have following scenario:
2 PC on WinXP + MSDE2000 + SP4 with production db. Secondary PC works as
failover backup. DBs are connected by merge replication where backup is as
subscriber.
The problem is related to one table with binary column (2K) and its growing
size (over 100MB per day).
sp_spaceused product_map
name rows reserved data index_size unused
Product_map 24238 1056648KB 130448KB 13520KB 912640KB
It seems that on insert rows server allocates more space than need. This
problem occures only when system works on publisher db. It is ok when I
switch it to backup PC.
I have the same systems on W2k + MSDE2000 + SP3a + merge replication and
don't observe the problem like this.
could you help me how to diagnose it more deeply and solve the problem?
Janek
A couple of points 1) are you using text in row?
2) Are you sure the data in this column is the same on the publisher and
subscriber. Depending on how you update your text or image data it may not
be replicated.
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
"Janek" <Janek@.discussions.microsoft.com> wrote in message
news:456DC0D6-FEDC-43BF-8A22-B6CE22069464@.microsoft.com...
> Hi,
> We have following scenario:
> 2 PC on WinXP + MSDE2000 + SP4 with production db. Secondary PC works as
> failover backup. DBs are connected by merge replication where backup is as
> subscriber.
> The problem is related to one table with binary column (2K) and its
> growing
> size (over 100MB per day).
> sp_spaceused product_map
> name rows reserved data index_size
> unused
> Product_map 24238 1056648KB 130448KB 13520KB 912640KB
> It seems that on insert rows server allocates more space than need. This
> problem occures only when system works on publisher db. It is ok when I
> switch it to backup PC.
> I have the same systems on W2k + MSDE2000 + SP3a + merge replication and
> don't observe the problem like this.
> could you help me how to diagnose it more deeply and solve the problem?
> Janek
|||1) data type for table is: int, smallint, int, char(11), smallint, smallint,
binary(2001), bit, datetime, int
2) All data is replicating, I can see it on reports on each machines. From
sp_spaceused report I see almost 1GB of unused space.
I know only one workaround:
dbcc dbreindex (.... + shrink db - a few cycles makes db to similar
backup db size.
At the moment system is working on backup PC without problem and new data is
refreshed to publisher.
Janek
"Hilary Cotter" wrote:
> A couple of points 1) are you using text in row?
> 2) Are you sure the data in this column is the same on the publisher and
> subscriber. Depending on how you update your text or image data it may not
> be replicated.
> --
> 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
>
> "Janek" <Janek@.discussions.microsoft.com> wrote in message
> news:456DC0D6-FEDC-43BF-8A22-B6CE22069464@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)