Replication scenario. Heavy usage and limited bandwidth

Hi all
I'm evaluating a potential replication scenario and I'd like some input from
the experts if possible.
I've got a 150GB database hosted at my headoffice that I need to replicate
to 4 regional offices. The regional offices are connected to the head office
by a WAN and I've been told that the available bandwidth is 512k
I would imagne that I would need to transfer the initial snapshot to the
subscribers by tape or the like, as I can't see that volume of data going
across the WAN in anything close to a reasonable amount of time.
I examined my transactional log backups to try ad get an idea of the amount
of data that would be replicated. The transaction log is backed up every 5
minutes and the log backups range from 5MB to 200MB
My concern is first, will the amount of replicated transactions flood the
512k line, and second, what kind of delay would I be looking at for
transactions to reach the regional servers.
Thanks very much
Gail Shaw (MCSD)
http://gail.rucus.net/
I think you will find that the 200Mg log backup probably occurred when you
were reindexing. I think you will find that your average log dumps will be
smaller, but this depends on load and frequency of dumps.
The replication commands should not consume all available bandwidth, but
again this depends on your load.
The delay is a function of availability, throughput and polling interval.
But just to through something out there, on some ISDN lines between NJ and
Guam we were hitting 1 minute.
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
"GilaMonster" <gshaw [AT] sentechsa [DOT] com> wrote in message
news:EEA8A148-5685-43A8-9E38-A7237C845495@.microsoft.com...
> Hi all
> I'm evaluating a potential replication scenario and I'd like some input
from
> the experts if possible.
> I've got a 150GB database hosted at my headoffice that I need to replicate
> to 4 regional offices. The regional offices are connected to the head
office
> by a WAN and I've been told that the available bandwidth is 512k
> I would imagne that I would need to transfer the initial snapshot to the
> subscribers by tape or the like, as I can't see that volume of data going
> across the WAN in anything close to a reasonable amount of time.
> I examined my transactional log backups to try ad get an idea of the
amount
> of data that would be replicated. The transaction log is backed up every 5
> minutes and the log backups range from 5MB to 200MB
> My concern is first, will the amount of replicated transactions flood the
> 512k line, and second, what kind of delay would I be looking at for
> transactions to reach the regional servers.
> Thanks very much
> Gail Shaw (MCSD)
> http://gail.rucus.net/
|||"Hilary Cotter" wrote:

> I think you will find that the 200Mg log backup probably occurred when you
> were reindexing. I think you will find that your average log dumps will be
> smaller, but this depends on load and frequency of dumps.
The 200MB is after the completion of some of the nightly data importation
jobs. During the day the backups are around 5-10 MB, log backed up every 5
minutes. At night, during heavy data import, the logs range between 20MB and
200MB. Reindexing is only done on sundays, as it's the only downtime we have.

> The replication commands should not consume all available bandwidth, but
> again this depends on your load.
Any reliable way of measuring the load? I thought that the log size would
provide a good estimate. According to a monitoring tool I have access to, the
transactions/minute max out at 2000 during the afternoon, but that should be
mostly reads.

> The delay is a function of availability, throughput and polling interval.
> But just to through something out there, on some ISDN lines between NJ and
> Guam we were hitting 1 minute.
Doesn't sound too bad. What's the bandwidth on those lines and what kind of
load did you have?
One of my main worries is what happens if the subscribers get out of sync. I
can't just reinitialise the subscriptions and nosync initialisations look
like a fair bit of trouble

> --
> Hilary Cotter
sql

Replication scenario question...Merge or Transactional?

The situation I am faced with is we have a web application supported by a SQL
2000(sp4) database that resides on a limited bandwidth network. Our
distributed users are constantly complaining of "slow" response times. Our
local users have no such complaints. Some of our leadership has suggested
sending a SQL server/IIS server to the remote location and using some type of
replication to synchronize the data between these boxes. The requirements
are for minimal latency and concurrent updating of data. The leadership also
want this solution to be completely automated (little or no supervision of
the replication process) and as with everything we do they want it right away
(we're talking days, not weeks). I am very new to replication and have read
through the BOL section and am in the process of reading Hillary Cotter's
book. I am leaning toward an implementation of Merge Replication but I am
unsure if this is the right solution. Any advice or informed opinions would
be greatly appreciated.
There is no concurrent replication option ie each solution will have a degree
of latency. If you use merge then you can select from a variety of conflict
resolvers and easily work offline. This might be your best option. There are
alternatives - queued updating subscribers, immediate updating subscribers
and bidirectional transactional replication. Do you have BLOBS in the table?
Are the subscribers always connected? Should they be able to continue if not
connected? These questions will clarify and narrow down the options a bit.
Whichever option you select, don't rush - you'll need time to configure it in
a test environment to establish a set of protocols (change management, error
handling...) and to simply verify that it all works for your situation.
HTH,
Paul Ibison
"Dave Stokes" wrote:

> The situation I am faced with is we have a web application supported by a SQL
> 2000(sp4) database that resides on a limited bandwidth network. Our
> distributed users are constantly complaining of "slow" response times. Our
> local users have no such complaints. Some of our leadership has suggested
> sending a SQL server/IIS server to the remote location and using some type of
> replication to synchronize the data between these boxes. The requirements
> are for minimal latency and concurrent updating of data. The leadership also
> want this solution to be completely automated (little or no supervision of
> the replication process) and as with everything we do they want it right away
> (we're talking days, not weeks). I am very new to replication and have read
> through the BOL section and am in the process of reading Hillary Cotter's
> book. I am leaning toward an implementation of Merge Replication but I am
> unsure if this is the right solution. Any advice or informed opinions would
> be greatly appreciated.

Replication scenario - seeking suggestion

I have two sites. Site A and Site B

Each site has two databases

Site A

Db1

Db2

Site B

Db1

Db2

Site A Db1 has to perform transaction replication to Site A- Db2 and Site B- Db1 and Db2.

I started Site A as pubisher and distributor and Site A and Site B both as subscriber.

Site B is in a different geographical area (state).

-

Please suggest the best scenario to save bandwidth and server load for Publisher, and Distributor.

-

Earlier I thought that I will implement local replication between Site B - in between Db1 and Db2. The Sql Server does not let me set Db1 as publisher, and distributor for its local database Db2.

-

P.S. My all databases need same transactions though they are connected to different hardware at different places. So please don't question that why I need four similar databases.

You can publish to Db1 then use Db1 as a republisher to publisher to Db2.

See in Books Online: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/a1485cf4-b1c4-49e9-ab06-8ccfaad998f3.htm

Martin

|||

Thank you!!

looks good.

For Site A - Db1

publisher, distributor and subsriber(Site A- Db2)

Db 2

Publisher Site A

Distributor Site B

Subscriber Db1

Subscriber Db2

I think, this is what you are suggesting.

|||

My suggestion would have the distributor on the same machine so:

A - DB 1 (master publisher)

A - DB 2 (subscriber)

B - DB 1 (subscriber , republisher)

B - DB 2 (subscriber (to B - DB 1)

Martin

|||

Thank you!!

I never tried republisher, I am running Sql Server 2000.

Let me read it, if I will have any question then I will get back.

Moreover, I could not access that help, this does not work from my computer.

This is exactly I would prefer, because otherwise it seems stupid to send data twice to the other site.

|||

Oh this is for SQL 2005 only. You need to install the SQL2005 books online to view the help link.

Martin

|||Thank you!

Replication scenario

Hi all,

I have a huge replication task I need to perform. The source table has over 250,000,000 records and everyday approximately 400,000 records are added to the system regularly.

Currently, I am running snapshot replication and it's taking 10 to 11 hours to complete (The internet connection between the production and the report server is slow). The reason I am using this method is because the source table does not have a timestamp column (so I can run an incremental replication) and I cannot modify that table because it belongs to a third party software. It does have a field which is linked to another table holding the timestamp.

Here is the source table and the other table's structure for reference:

DataLog

Name Datatype Size

DataLogStampID int 4
Value float 8
QuantityID smallint 2

DataLogStamp

ID (Which is foreign key to DataLogStampID field in the above table) int 4

SourceID smallint 2

TimestampSoruceLT datatime 8

What I would like to know is, what is the best practice to handle such replication with MSSQL Server 2000.

I am thinking of developing a procedure which follows the following step using a temp table:

This procedure will run (say) every night.

The temp table will contain a timestamp field.

For one time only, I will run a snapshot replication, so that I can have the existing data into the destination db.

I will record this timestamp into a repl_status table which holds the timestamp of the last replication.

The procedure will read the record from repl_status and select all records from the source table which were added since this date and put them into a new temp table (this table will have the same structure as the source and destination tables) and then a snapshot replication will "add" these new records only every night to the dest. table. By using this method, I will only transfer the records which have been added since the last replication (last night - less records).

Any comments would be greatly appreciated.

Thanks for your time in advance,

Sinan Topuz

Hi,

Last year I made a query that replicated a table between two databases (say db1 and db2) on two remote servers (say from SRV1 to SRV2).

I added a column called "_replicated" to the table of SRV1.db1 which took binary values (0 or 1). 0 meant not yet replicated and 1 meant already replicated.

When I run the query, it inserted into SRV2.db2.table the rows of SRV1.db1.table that had "_replicated = 0".

Then I updated the column's values to 1 for SRV1.db1.table. In case of an error it would roll back the transaction.

Before execution, I also dropped the indexes on SRV2.DB2 to speed up the insertion and then recreated them.

Be sure you have a recent backup of SRV2.db2 before 'playing' in this manner (or maybe create it exactly before every replication just in case)

GOOD LUCK!

|||

Hi JohDas, Thank you very much for your answer. I would be able to use that method, if I were able to modify the source table, but in my case I have to find out if my algoritm is the best approach in a situation of this sort.

I did not mean that your post is not helpful, but just hoping to get some more suggestions from other experts as well.

Thanks for your time again.

Sinan Topuz

|||

sinan.topuz wrote:

Currently, I am running snapshot replication and it's taking 10 to 11 hours to complete (The internet connection between the production and the report server is slow). The reason I am using this method is because the source table does not have a timestamp column (so I can run an incremental replication) and I cannot modify that table because it belongs to a third party software.

Transactional replication, which replicates incremental changes, does not require a timestamp column. It does, however, require a PK column. If your tables do have a PK column, then you can easily use transactional replication by just scheduling the replication agents to run nightly.

If by chance your use of "timestamp column" is a typo, and you meant to say PK column, then you do have other options, one of which is just doing a nightly database backup, copy it to your destination, and restore it. This may or may not be faster than snapshot replication.

A third option is to use Log Shipping, you can read more about it in Books Online topic "Understanding Log Shipping" - http://msdn2.microsoft.com/en-us/library/ms187103.aspx.

|||

Greg,

The table does not have a primay key. In fact, it should not because I think it is being written constantly and with a lot of rows. Right now it has almost 3 million records. (This the way the apps designers may have thought while designing the system).

I appreciate your time and your comment.

Thanks,

Sinan Topuz

|||I reread your original post and now understand what you're trying to do, it's your own "change tracking" mechanism. We are developing a "change tracking" solution in the next release of SQL Server which would fit your needs as well. Otherwise your home grown solution should work, just make sure to test it out.|||

Thanks,

Just a correction, not because it is important, but I just wanted to give the correct info. The table has 300 million records now not 3 million.

|||

ok, now your 10-11 hours figure makes more sense :) However I would also take a look at logshipping if you can.

replication scenario

Hi there,
I have two servers setup at two different locations, one a publisher(server
A) the other subscriber(server B). I'm using Merge replication and SQL server
2000.
We have teams working in remote areas gathering data in laptops that have
been setup as subscribers to server A.
How can i get the laptops to synchronise with server B incase server A fails?
Samman,
have a look in BOL for "merge replication, alternate synchronization
partners".
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication problem after reduce no of columns to less than 254

There was a replication problem between db1 and db3. The scenario is that a
table was added with more than 254 nos. of columns. An error
[Error 2757: RAISERROR failed due to invalid parameter substitution(s) for
error 20068, severity 16, state 1]
is prompted during the creation of publication for the replication.
Therefore, the new added columns are deleted. However, there is another
error [Error 220: Arithmetic overflow error for data type tinyint, value =
256.]
is prompted when the publication for the replication was created again.
How to fix this error?
David NG
can I see the schema for this reduced table? How many columns did it have
originally?
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David NG" <David NG@.discussions.microsoft.com> wrote in message
news:384758F8-4985-4566-B8C0-7532A0C7DFC3@.microsoft.com...
> There was a replication problem between db1 and db3. The scenario is that
> a
> table was added with more than 254 nos. of columns. An error
> [Error 2757: RAISERROR failed due to invalid parameter substitution(s) for
> error 20068, severity 16, state 1]
> is prompted during the creation of publication for the replication.
>
> Therefore, the new added columns are deleted. However, there is another
> error [Error 220: Arithmetic overflow error for data type tinyint, value =
> 256.]
> is prompted when the publication for the replication was created again.
> How to fix this error?
> David NG
>

Replication issues after a Database Restore - Unable to drop or create Transactional Repli

Hi,

I have transactional replication set up on on of our MS SQL 2000 (SP4)
Std Edition database server

Because of an unfortunate scenario, I had to restore one of the
publication databases. I scripted the replication module and dropped
the publication first. Then did a full restore.

When I try to set up the replication thru the script, it created the
publication with the following error message

Server: Msg 2714, Level 16, State 5, Procedure SYNC_FCR To
GPRPTS_GL00100, Line 1
There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in the
database.

It seems the previous replication has set up these system views
SYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replication
module again to see if it drops the views but it didn't.

The replication fails with some wired error & complains about this
views when I try to run the synch..

I even tried running the sp_removedbreplication to drop the
replication module, but the views do not seem to disappear.

My question is how do I remove these system views or how do I make the
replication work without using these views or create new views.. Why
is this creating those system views in the first place?

I would appreciate if anyone can help me fix this issue. Please feel
free to let me know if any additional information or scripts needed.

Thanks in advance..

Regards,
Aravin Rajendra.you should be able to drop them using query analyzer.

--
RelevantNoise.com - dedicated to mining blogs for business intelligence.

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
"Query Builder" <querybuilder@.gmail.comwrote in message
news:1189702889.303360.189580@.o80g2000hse.googlegr oups.com...

Quote:

Originally Posted by

Hi,
>
I have transactional replication set up on on of our MS SQL 2000 (SP4)
Std Edition database server
>
Because of an unfortunate scenario, I had to restore one of the
publication databases. I scripted the replication module and dropped
the publication first. Then did a full restore.
>
When I try to set up the replication thru the script, it created the
publication with the following error message
>
Server: Msg 2714, Level 16, State 5, Procedure SYNC_FCR To
GPRPTS_GL00100, Line 1
There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in the
database.
>
It seems the previous replication has set up these system views
SYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replication
module again to see if it drops the views but it didn't.
>
The replication fails with some wired error & complains about this
views when I try to run the synch..
>
I even tried running the sp_removedbreplication to drop the
replication module, but the views do not seem to disappear.
>
My question is how do I remove these system views or how do I make the
replication work without using these views or create new views.. Why
is this creating those system views in the first place?
>
I would appreciate if anyone can help me fix this issue. Please feel
free to let me know if any additional information or scripts needed.
>
Thanks in advance..
>
Regards,
Aravin Rajendra.
>

|||Thanks for your response.. I tried dropping it thru QA.. Now the
replication doesn't show up on the publication. But the replication
monitor still has this replication with a failed status....

Can you please point me to the direction on safely removing all
components of a particular replication module (I have other publishers
in this server)..

Thanks again..

Aravin Rajendar.

On Sep 13, 2:37 pm, "Hilary Cotter" <hilary.cot...@.gmail.comwrote:

Quote:

Originally Posted by

you should be able to drop them using query analyzer.
>
--
RelevantNoise.com - dedicated to mining blogs for business intelligence.
>
Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
>
Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com"Query Builder" <querybuil...@.gmail.comwrote in message
>
news:1189702889.303360.189580@.o80g2000hse.googlegr oups.com...
>

Quote:

Originally Posted by

Hi,

>

Quote:

Originally Posted by

I have transactional replication set up on on of our MS SQL 2000 (SP4)
Std Edition database server

>

Quote:

Originally Posted by

Because of an unfortunate scenario, I had to restore one of the
publication databases. I scripted the replication module and dropped
the publication first. Then did a full restore.

>

Quote:

Originally Posted by

When I try to set up the replication thru the script, it created the
publication with the following error message

>

Quote:

Originally Posted by

Server: Msg 2714, Level 16, State 5, Procedure SYNC_FCR To
GPRPTS_GL00100, Line 1
There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in the
database.

>

Quote:

Originally Posted by

It seems the previous replication has set up these system views
SYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replication
module again to see if it drops the views but it didn't.

>

Quote:

Originally Posted by

The replication fails with some wired error & complains about this
views when I try to run the synch..

>

Quote:

Originally Posted by

I even tried running the sp_removedbreplication to drop the
replication module, but the views do not seem to disappear.

>

Quote:

Originally Posted by

My question is how do I remove these system views or how do I make the
replication work without using these views or create new views.. Why
is this creating those system views in the first place?

>

Quote:

Originally Posted by

I would appreciate if anyone can help me fix this issue. Please feel
free to let me know if any additional information or scripts needed.

>

Quote:

Originally Posted by

Thanks in advance..

>

Quote:

Originally Posted by

Regards,
Aravin Rajendra.