Is there a limit on the time Replication can be down?

I was asked if there was a limit on the amount of clock time that the Replication agents may be down without snapshotting the subscribers. I think that the only factor is if the MSDB database contains the necessary data to feed a restarted Replication Agent.

Any comments?

If you don't have new subscribers, you don't need new snapshot. Is this what you were asking?|||I was asked if there was a limit on the amount of time our publisher could be "down" before having to re-snap the subscribers. I don't think there is some arbitrary limit but there could be!|||

That depends on the type of replication and your situation. i.e. if you are using merge with a 14 day retention period and you try to synchronize every month then you will run into expired subscriptions.

Martin

Is there a good book on replication?

Hello all,
Is there a really good book on replication that you can all recommend? I own
many SQL Server books but none of them cover the subject in detail.
Or do you recommend Books Online is my best option? Please advise.
Thanks!
Johnny
Sinky Hillary Bait!
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:29A8063A-028D-477F-8119-4673356634FB@.microsoft.com...
> Hello all,
> Is there a really good book on replication that you can all recommend? I
> own
> many SQL Server books but none of them cover the subject in detail.
> Or do you recommend Books Online is my best option? Please advise.
> Thanks!
> Johnny
|||Lol!
Try the book in my sig. There is a sample chap available in the link.
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
"Pepto" <pepto@.hotmail.com> wrote in message
news:uEuDDDUnFHA.4056@.TK2MSFTNGP10.phx.gbl...
> Sinky Hillary Bait!
>
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:29A8063A-028D-477F-8119-4673356634FB@.microsoft.com...
>
|||Johnny,
please check out the ling in my signature below.....
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Nice. Thank you. I should be ordering a copy of that ASAP.
P.S - Why doesnt Amazon.com have this available directly from them? Just
curious.
Johnny
"Hilary Cotter" wrote:

> Lol!
> Try the book in my sig. There is a sample chap available in the link.
> --
> 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
> "Pepto" <pepto@.hotmail.com> wrote in message
> news:uEuDDDUnFHA.4056@.TK2MSFTNGP10.phx.gbl...
>
>
|||Hilary,
Does this book cover Merge replication too? Reading the title, I dont think
it does. If it does'nt, is there a reason why? I'm just curious. I'm only
interested in transactional and snapshot replication anyway.
Johnny
"Hilary Cotter" wrote:

> Lol!
> Try the book in my sig. There is a sample chap available in the link.
> --
> 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
> "Pepto" <pepto@.hotmail.com> wrote in message
> news:uEuDDDUnFHA.4056@.TK2MSFTNGP10.phx.gbl...
>
>

Is there a doc listing the limitations of Replication under 2005?

I am looking for a doc (BOL, etc.) which enumerates things like limits on the number of columns which can be replicated, or limits on the size of the row. BOL covers this for 2000, but I can not find it in BOL 2005.

Thanks.

Please see the following topic "Maximum Capacity Specifications for SQL Server 2005" in BOL: http://msdn2.microsoft.com/en-us/library/ms143432.aspx

Peng

Is SQL Compatibility mode a factor?

A few days ago I posted a problem I am having with push, one way, transactional replication failing to replicate updates to the subscriber. The identity field is set to Not For Replication on the subscriber and the inserts work, however, my updates are fa
iling with a Cannot Update Identity column. On the publisher, the stored procedure is not updating the identity field so I am at a loss as to why I am getting this error.
Today I had a thought, both the publisher and subscriber are running these databases in 65 compatibility mode. The distribution database is running in 80 compatibiltity mode. Could this be the problem? We are running SQL 2000 standard, but until we can re
visit all of our code we are forced to stay in this compatibility mode.
For some reason the update Stored Procedure that was created by the replication Wizard creates the following line
if substring(@.bitmap,1,1) & 1 = 1
which is validating as true, then it tries to specifically update the identity field. If this were to be false I am sure the update would succeed. What is being stored in this @.bitmap variable?
Now I am really confused.
I edited the one update stored procedure created by the replication wizard that gets called when an update occurs on the table in question on the publisher. For each part of the IF Statement (if substring(@.bitmap,1,1) & 1 = 1) I added an Insert to insert
a value into a test table to determine which part (True or False) was being ran during the replication update. The false part is being ran, which means it will never try to update the identity field, however, replication is throwing an error stating it ca
nnot update the identity field. Why is SQL doing this? Is this a bug?
|||if you are replicating to a sql server using the dbcompatibilty mode of 6.5
you should
1) configure a DSN to the SQL Server
2) enable the subscriber through the DSN
3) configure your publisher to replicate to hetergeneous subscribers
4) create a push subscription to the DSN.
"Denny" <anonymous@.discussions.microsoft.com> wrote in message
news:A9D577AF-B8B4-437D-B8DA-60749358BDF7@.microsoft.com...
> Now I am really confused.
> I edited the one update stored procedure created by the replication wizard
that gets called when an update occurs on the table in question on the
publisher. For each part of the IF Statement (if substring(@.bitmap,1,1) & 1
= 1) I added an Insert to insert a value into a test table to determine
which part (True or False) was being ran during the replication update. The
false part is being ran, which means it will never try to update the
identity field, however, replication is throwing an error stating it cannot
update the identity field. Why is SQL doing this? Is this a bug?
|||Denny,
Look at the table on the publisher/subscriber and verify that the identity
column on
the table has the NOT FOR REPLICATION option set. Based on the error, I
would say it does not. Try turning this on and see if that resolves the
problem.
Regards,
Paul
|||Paul,
Thank you for your reply. The tables do have the Not For Replication setting set for the identity fields. The inserts work just fine however the update is failing. The funny thing is the update SP (sp_MSupd_RezStationLog) has two different updates that ca
n be called depending on the a value of "if substring(@.bitmap,1,1) & 1 = 1". If true it tries to update the identity column along with the other fields. If false it only tries to update the other fields (not the identity column). The expression is evaluat
ing to false so I'm not sure why it's generating that error being it's not trying to update the identity column. Even thought the true part is not being called I commented out the line that updates the identity field for the true part and everything works
as it should. Sounds like a bug to me, but I'd expect everyone else would have this problem too.
Hilary,
On our test systems I changed the compatibility mode to 80 and I am still getting the same error. Should I still try the ODBC method or does this sound like some other problem?
Thank you,
Denny
|||The databases on both servers is set to DBCompatibility mode 6.5.. Since this is all being done on test servers I went ahead and changed both of them to DBCompatibility mode 8.0 via EM. I bounced the SQL service on both servers and am still getting the sa
me error.
|||Hi,
I have the same problem. I am sure the Publishing DB table has the Not for replication option, so does the subscribing DB table.
Insertion is replicated, but updates cannot be replicated because of "Identity" column problem.
I manually synchronize the published DB and apply the scripts generated by sp_scriptpublicationcustomprocs to the subscribing DB.
Please help!!
Charles
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||Charles,
I can't see the original post so I'm a little blind on this one. It seems
that you are using Transactional replication but what I really need is the
error message and the update that causes the error.
Just a guess but if this update affects a unique/clustered index, it may be
replicated as a delete/insert pair and for singleton updates a trace flag
can be used to avoid the issue
(http://support.microsoft.com/default...microsoft.com
:80/support/kb/articles/q302/3/41.ASP&NoWebContent=1).
Regards,
Paul Ibison

Is restore of a republishing database possible from one of its subscribers in merge replic

Hi,
I've a republishing database (using merge replication) which has more than 1
subscriber. The database has gone into suspect mode and there is a problem
with 2 tables. I've all the data on its subscribers. However, each
subscriber also has some changes which have not been sent to the publisher
after it has gone to Suspect mode. Unfortunately, we don't have a backup of
the republishing database.
Is it possible to backup the database on one of the subscribers and restore
it on the publisher with the keep_replication switch? If yes, will it lead
to some data loss with the chages that remain pending at the subscribers and
have not been transferred to the republisher yet? If I can't restore using
one of the subscribers, do I have other options?
The two tables that have gone bad in the republishing database have the data
on the subscribers.
Regards,
Rajnish Gupta
You can do this, but there are some caveats - review
http://msdn2.microsoft.com/en-us/library/aa237097(sql.80).aspx - for SQL
2000 and for SQL 2005 follow:
http://msdn2.microsoft.com/en-us/library/ms152497.aspx
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
"Rajnish Gupta" <guptarajnish@.hotmail.com> wrote in message
news:OfMWOHiZHHA.4420@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I've a republishing database (using merge replication) which has more than
> 1 subscriber. The database has gone into suspect mode and there is a
> problem with 2 tables. I've all the data on its subscribers. However, each
> subscriber also has some changes which have not been sent to the publisher
> after it has gone to Suspect mode. Unfortunately, we don't have a backup
> of the republishing database.
> Is it possible to backup the database on one of the subscribers and
> restore it on the publisher with the keep_replication switch? If yes, will
> it lead to some data loss with the chages that remain pending at the
> subscribers and have not been transferred to the republisher yet? If I
> can't restore using one of the subscribers, do I have other options?
> The two tables that have gone bad in the republishing database have the
> data on the subscribers.
> Regards,
> Rajnish Gupta
>
|||Dear Hilary,
Thanks for your quick response. I'll go through the link for SQL Server 2005
as this is what I'm using.
Do you recommend recovering the database only or the log file also?
Regards,
Rajnish Gupta
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23iEGb6iZHHA.4000@.TK2MSFTNGP02.phx.gbl...
> You can do this, but there are some caveats - review
> http://msdn2.microsoft.com/en-us/library/aa237097(sql.80).aspx - for SQL
> 2000 and for SQL 2005 follow:
> http://msdn2.microsoft.com/en-us/library/ms152497.aspx
> --
> 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
>
> "Rajnish Gupta" <guptarajnish@.hotmail.com> wrote in message
> news:OfMWOHiZHHA.4420@.TK2MSFTNGP02.phx.gbl...
>
|||both.
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
"Rajnish Gupta" <guptarajnish@.hotmail.com> wrote in message
news:OPZTkNzZHHA.5080@.TK2MSFTNGP02.phx.gbl...
> Dear Hilary,
> Thanks for your quick response. I'll go through the link for SQL Server
> 2005 as this is what I'm using.
> Do you recommend recovering the database only or the log file also?
> Regards,
> Rajnish Gupta
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23iEGb6iZHHA.4000@.TK2MSFTNGP02.phx.gbl...
>
|||Dear Hilary,
I'll try the restore tomorrow. It will be a lighter day on the database.
I've gone through the SQL Server 2005 link and I understand that I just have
to restore the database (by taking any of the subscriber's backup) with the
"Keep Replication" switch. After restore the Publisher will automatically
receive updates from other subscribers. The other subscribers are configured
for pull subscriptions. The other subscribers will also receive the data
propagated to the publisher by each of them. So all of them will be
synchronised with the data each of them have been holding up while the
publisher has been down.
I hope I haven't missed anything.
Regards,
Rajnish Gupta
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23uUfJu9ZHHA.5080@.TK2MSFTNGP02.phx.gbl...
> both.
> --
> 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
>
> "Rajnish Gupta" <guptarajnish@.hotmail.com> wrote in message
> news:OPZTkNzZHHA.5080@.TK2MSFTNGP02.phx.gbl...
>
|||I think you will have to recreate the jobs unless they are all pull. Make
sure you script everything out before you do the restore.
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
"Rajnish Gupta" <guptarajnish@.hotmail.com> wrote in message
news:%2350myBlaHHA.4520@.TK2MSFTNGP06.phx.gbl...
> Dear Hilary,
> I'll try the restore tomorrow. It will be a lighter day on the database.
> I've gone through the SQL Server 2005 link and I understand that I just
> have to restore the database (by taking any of the subscriber's backup)
> with the "Keep Replication" switch. After restore the Publisher will
> automatically receive updates from other subscribers. The other
> subscribers are configured for pull subscriptions. The other subscribers
> will also receive the data propagated to the publisher by each of them. So
> all of them will be synchronised with the data each of them have been
> holding up while the publisher has been down.
> I hope I haven't missed anything.
> Regards,
> Rajnish Gupta
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23uUfJu9ZHHA.5080@.TK2MSFTNGP02.phx.gbl...
>

Is replication without network connection but file-based possible?

Hello,

I've been looking around in several SQL-Forums and in SQL Books Online but haven't found clear suggestions how to tackle my problem:

Our company has to fill a database with documentation for our contractor. Our subcontractors have to supply information from their branch/craft into this database, too.

Replication (without determining a type at this time) would do fine for data-exchange between us and our subcontractors.

But for working in different companies the networks are not connected
and in some companies/departments connection to the Internet is prohibited at all.
This would result in data-exchange based on files - which wont be a problem as data needs to be updated/exchanged only once a week and is usually done at meetings using DVD-Rs.

scenario in short:
- two-way exchange of data between SQL servers being not connected
- update needed once a week
- our server: SQL Server 2005 Standard (preferred)
- servers of our subcontractors: SQL Server Express 2005 (preferred)

That's the point where I dont know how to proceed:
Is two-way replication based on files possible at all?

I read about Snapshots, but to my understanding this replaces the "whole database" without caring about changes made to it and that's why it wont work for two-way exchange.

I would appreciate your hints how to solve this problem in general or whether I should look out for other solutions - MS Access might work with restrictions - but I would prefer SQL Server.

Thanks for reading until here and in advance for your answers.

Regards,

Michael__H

Hi Michael.

I do not think there is an easy solution for your problem. There is no offline file based replication solution that will also do 2-way replication for you.

The closest you can get by is backup restore but that will overwrite your data on the other side.

However if you have even Internet connection, then you will be able to solve your problem by using Web Synchronization which is a new feature in SQL Server Merge replication for SQL Server 2005.

|||

Hello Mahesh,

thank you for your answer.

According to your reply I'm going to solve my problem by using stored procedures writing my own little "replication":

- select records to be altered by our subcontractors
- mark them in separate column as 'currently exported'
- copy to external database/file X
- let our subcontractors complete missing data in database/file X
- compare returned database/file X with records in database marked as 'currently exported'
- import changed records

Of course it won't be easy but as far as no other solution is possible...

Michael

|||

Michael,

Your solution will work but it could be tricky.

Again I encourage you to take a look at Web Synchronization if you remotely think that the subscribers could have atleast internet connection.

|||

Hello Mahesh,

I'll have a closer look at Web-Synchronization , it seems to be a solution. And if not, my "replication" will be used.

Your suggestion has given my a direction for further reading, thank you.

Michael

Is replication without network connection but file-based possible?

Hello,

I've been looking around in several SQL-Forums and in SQL Books Online but haven't found clear suggestions how to tackle my problem:

Our company has to fill a database with documentation for our contractor. Our subcontractors have to supply information from their branch/craft into this database, too.

Replication (without determining a type at this time) would do fine for data-exchange between us and our subcontractors.

But for working in different companies the networks are not connected
and in some companies/departments connection to the Internet is prohibited at all.
This would result in data-exchange based on files - which wont be a problem as data needs to be updated/exchanged only once a week and is usually done at meetings using DVD-Rs.

scenario in short:
- two-way exchange of data between SQL servers being not connected
- update needed once a week
- our server: SQL Server 2005 Standard (preferred)
- servers of our subcontractors: SQL Server Express 2005 (preferred)

That's the point where I dont know how to proceed:
Is two-way replication based on files possible at all?

I read about Snapshots, but to my understanding this replaces the "whole database" without caring about changes made to it and that's why it wont work for two-way exchange.

I would appreciate your hints how to solve this problem in general or whether I should look out for other solutions - MS Access might work with restrictions - but I would prefer SQL Server.

Thanks for reading until here and in advance for your answers.

Regards,

Michael__H

Hi Michael.

I do not think there is an easy solution for your problem. There is no offline file based replication solution that will also do 2-way replication for you.

The closest you can get by is backup restore but that will overwrite your data on the other side.

However if you have even Internet connection, then you will be able to solve your problem by using Web Synchronization which is a new feature in SQL Server Merge replication for SQL Server 2005.

|||

Hello Mahesh,

thank you for your answer.

According to your reply I'm going to solve my problem by using stored procedures writing my own little "replication":

- select records to be altered by our subcontractors
- mark them in separate column as 'currently exported'
- copy to external database/file X
- let our subcontractors complete missing data in database/file X
- compare returned database/file X with records in database marked as 'currently exported'
- import changed records

Of course it won't be easy but as far as no other solution is possible...

Michael

|||

Michael,

Your solution will work but it could be tricky.

Again I encourage you to take a look at Web Synchronization if you remotely think that the subscribers could have atleast internet connection.

|||

Hello Mahesh,

I'll have a closer look at Web-Synchronization , it seems to be a solution. And if not, my "replication" will be used.

Your suggestion has given my a direction for further reading, thank you.

Michael

Is Replication Too Much

We are developing a system for a manufacturing operation. The planned
approach is to develop stand alone data capture applications using MSDE for
the database. The production data from these systems needs to be posted to a
central SQL database. All systems will be in the same building. We plan to
roll out this system to a number of facilities. The number of data capture
systems could range from 5 to 25. Data Transfer requirements include: 1)
Master data from the central system will need to be sent to the data capture
machines (updated 4 times a day). 2) Production order informtation will be
sent to 1 or more data capture machines (updated as orders are scheduled to
production lines). 3) Production data from the data capture machines will
need to be sent in a timely manner because 4) Summary Production data (totals
by production order and material) is needed by the data capture machines to
prevent overruns.
The current application we have uses a separate VB application to update the
stand alone and central database. Users are given a message when the central
database cannot be accesssed, however, the stand alone machines must continue
working.
Would replication be a good design for this scenario? or is it overkill? I
have heard the replication is a resource hog, we will have only the central
server to work with.
Other information:
Production data on the stand alones need only be kept for a few days, after
that it can be purged.
Production records (single table) can be created every 6 seconds, updates to
central database can be queued.
Master data consist of 6 tables that are used for look up purposes by the
application running on the stand alone systems.
There are 2 production tables (Header and detail). Application displays
summary production (for detail record) by current system and across all
systems.
Thanks in advance for you help
Who told you replication is a resource hog? Running on property sized
hardware and with a well designed topology (minimal use of filters, etc),
the performance impact should range around 10% or less. If you are running
this on Pentium III hardware naturally performance will not be optimal.
Running it on Pentium IV 2 GHz or more (or some of the modern AMD
processors) with ample RAM (2 Gigs or more) you should be fine.
I think you should use merge replication for this. You can't use
transactional as MSDE can't be a transactional publisher.
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
"Ken Ptaszynski" <Ken Ptaszynski@.discussions.microsoft.com> wrote in message
news:FA9B3270-7928-4C1C-9446-3FB489B2015A@.microsoft.com...
> We are developing a system for a manufacturing operation. The planned
> approach is to develop stand alone data capture applications using MSDE
for
> the database. The production data from these systems needs to be posted to
a
> central SQL database. All systems will be in the same building. We plan
to
> roll out this system to a number of facilities. The number of data
capture
> systems could range from 5 to 25. Data Transfer requirements include: 1)
> Master data from the central system will need to be sent to the data
capture
> machines (updated 4 times a day). 2) Production order informtation will
be
> sent to 1 or more data capture machines (updated as orders are scheduled
to
> production lines). 3) Production data from the data capture machines
will
> need to be sent in a timely manner because 4) Summary Production data
(totals
> by production order and material) is needed by the data capture machines
to
> prevent overruns.
> The current application we have uses a separate VB application to update
the
> stand alone and central database. Users are given a message when the
central
> database cannot be accesssed, however, the stand alone machines must
continue
> working.
> Would replication be a good design for this scenario? or is it overkill?
I
> have heard the replication is a resource hog, we will have only the
central
> server to work with.
> Other information:
> Production data on the stand alones need only be kept for a few days,
after
> that it can be purged.
> Production records (single table) can be created every 6 seconds, updates
to
> central database can be queued.
> Master data consist of 6 tables that are used for look up purposes by the
> application running on the stand alone systems.
> There are 2 production tables (Header and detail). Application displays
> summary production (for detail record) by current system and across all
> systems.
> Thanks in advance for you help

Is Replication the solution?

We have serveral office of which have there own SQL Databases running in
MSDE and SQL Server Enterprise running at the Corp HQ. At HQ there are
tables for say Office 1 (Ofc1) in a central database. The central database
has a shema for each office in the central database ie ofc1.tbl_contacts,
ofc2.tbl_contacts, etc... Not all of the fields residing on the office
servers will be replacated to the HQ central db. But
updates/additions/deletions need to be synched at least on a daily basis.
I have already writting some of my own jobs to handle this with stored
procedures. I have createdDate, modifiedDate(updated via trigger) fields
that are used to determine what data needs to be transferred. I
I was wondering if SQL Replication Services would be a help in this area?
Is there some other method that would be better suited for this task, or is
coding it by hand (which I already started) pretty much the better solution?
Thanks for the help,
--Micah
Replication is ideal for something like this. You don't really mention is
data is going both ways or only to the branch offices.
If it is one way you should be using transactional replication with filters;
if it is bi-directional you should be using merge replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Micah Miller" <micah.miller@.privacy.me> wrote in message
news:OQyEUPXtEHA.3884@.TK2MSFTNGP11.phx.gbl...
> We have serveral office of which have there own SQL Databases running in
> MSDE and SQL Server Enterprise running at the Corp HQ. At HQ there are
> tables for say Office 1 (Ofc1) in a central database. The central
database
> has a shema for each office in the central database ie ofc1.tbl_contacts,
> ofc2.tbl_contacts, etc... Not all of the fields residing on the office
> servers will be replacated to the HQ central db. But
> updates/additions/deletions need to be synched at least on a daily basis.
> I have already writting some of my own jobs to handle this with stored
> procedures. I have createdDate, modifiedDate(updated via trigger) fields
> that are used to determine what data needs to be transferred. I
> I was wondering if SQL Replication Services would be a help in this area?
> Is there some other method that would be better suited for this task, or
is
> coding it by hand (which I already started) pretty much the better
solution?
> Thanks for the help,
> --Micah
>
>

Is Replication the correct solution?

Hello,
Goal: Maintain a copy of two of our live databases from server A on server
B that are updated hourly. The databases on server B are intended to be read
from, not updated. Database 1 is about 500 MB, database 2 is about 3.8 GB.
Problems: 1) Transactional replication requires a primary key for each
table, not all the tables have primary keys. 2) When I try to set up
snapshot replication I get a warning message about the Identity Property will
not be transferred. I have at least 100 tables with the identity property.
3) Merge replication does not seem suited to my application.
Am I correct with what I am thinking as stated above? Can replication be
used to do what I am trying to do? If so, what would you recommend? I
appreciate any suggestions that can be offered.
IMHO replication is a good way for this.
Why don't you create PKs on the replication tables?
Thomas
On Wed, 9 Mar 2005 08:49:08 -0800, "Dave Johannson"
<DaveJohannson@.discussions.microsoft.com> wrote:

>Hello,
>Goal: Maintain a copy of two of our live databases from server A on server
>B that are updated hourly. The databases on server B are intended to be read
>from, not updated. Database 1 is about 500 MB, database 2 is about 3.8 GB.
>Problems: 1) Transactional replication requires a primary key for each
>table, not all the tables have primary keys. 2) When I try to set up
>snapshot replication I get a warning message about the Identity Property will
>not be transferred. I have at least 100 tables with the identity property.
>3) Merge replication does not seem suited to my application.
>Am I correct with what I am thinking as stated above? Can replication be
>used to do what I am trying to do? If so, what would you recommend? I
>appreciate any suggestions that can be offered.
|||I am new to replication, please explain a little about IMHO.
One of the databases in question is an ERP database, I am uncertain how the
schema update scripts from the vendor will behave if I change things on the
tables, also, I am not certain how the application will handle changes to the
tables, and lastly management is not comfortable with such modifications, so
I shy away from adding PKs to the tables that don't have them.
"Thomas Hase" wrote:

> IMHO replication is a good way for this.
> Why don't you create PKs on the replication tables?
> Thomas
>
> On Wed, 9 Mar 2005 08:49:08 -0800, "Dave Johannson"
> <DaveJohannson@.discussions.microsoft.com> wrote:
>
>
|||IMHO == In My Humble Opinion
Without PKs you can't use stanardreplication.
If you uncertain, ask your vendor about adding PKs.
If you don't add FKs to the PKs, there is IMHO no influence on
your application. It may be a little bit smaler performance.
Most databases have PKs and FKs to implement
referential integrity at database level.
On Wed, 9 Mar 2005 09:39:02 -0800, "Dave Johannson"
<DaveJohannson@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I am new to replication, please explain a little about IMHO.
>One of the databases in question is an ERP database, I am uncertain how the
>schema update scripts from the vendor will behave if I change things on the
>tables, also, I am not certain how the application will handle changes to the
>tables, and lastly management is not comfortable with such modifications, so
>I shy away from adding PKs to the tables that don't have them.
>"Thomas Hase" wrote:
|||Is there a way around the problem of the identity property not being
transferred when using snapshot replication? If it is possilbe to make
snapshot replication work then I would not have to add PKs to the tables.
"Thomas Hase" wrote:

> IMHO == In My Humble Opinion
> Without PKs you can't use stanardreplication.
> If you uncertain, ask your vendor about adding PKs.
> If you don't add FKs to the PKs, there is IMHO no influence on
> your application. It may be a little bit smaler performance.
> Most databases have PKs and FKs to implement
> referential integrity at database level.
>
>
> On Wed, 9 Mar 2005 09:39:02 -0800, "Dave Johannson"
> <DaveJohannson@.discussions.microsoft.com> wrote:
>
>
|||As you are interested in having the identity property at
the subscriber, I suspect this is some sort of didaster
recovery strategy. In that case, you might want to
consider log shipping, which would better satisfy your
requirements.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Is replication the best solution

We have a high volume OLTP database of about 300 g and growing at anywhere
from 5-20 g a week. We are running out of space and want to archive data
older than 45 days off of the database to an archive database that could be
used for reporting.
The question is would replication be the best method for moving data to the
Reporting/Archive database, in that data older than 45 would be deleted from
the OLTP but needs to remain in the Archive. We also do upgrades to our
internal applications about once a month, and sometimes the upgrades involve
database changes.
The suggestion has been made to simply run the OLTP and Reporting from the
same database and simply move it to a larger SAN (and a server that isn't
running Biztalk and housing its databases), since we don't seem to have any
processing or memory issues, unless backups are being done.
I think a separate reporting and OLTP solution would be better in that we
will be continuing to grow in transactions, possibly doubling it this year.
The reports would be for internal purposes and also for web reporting to the
payors and payees we handle. Actually a lot of the database bloat is in XML
overflow from the forms we receive in and that need to be reassembled for
some of the web clients.
Just to note we are using SQL Server 2000 EE active/passive cluster, on
Windows 2003 servers
Any insight or advice welcome.
Nancy
Sounds more like a job for DTS/SSIS. If you delete a replicated record on
the Publisher (OLTP) side, it deletes on the other side (Subscriber) as
well.
Write scripts/packages that move the data that is X days old or has been
changed since Y days to the other database. You will of course need some
way to determine when a record changed or was deleted/inserted...
Other opinions may vary
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:emsbntFTHHA.5016@.TK2MSFTNGP05.phx.gbl...
> We have a high volume OLTP database of about 300 g and growing at anywhere
> from 5-20 g a week. We are running out of space and want to archive data
> older than 45 days off of the database to an archive database that could
> be used for reporting.
> The question is would replication be the best method for moving data to
> the Reporting/Archive database, in that data older than 45 would be
> deleted from the OLTP but needs to remain in the Archive. We also do
> upgrades to our internal applications about once a month, and sometimes
> the upgrades involve database changes.
> The suggestion has been made to simply run the OLTP and Reporting from the
> same database and simply move it to a larger SAN (and a server that isn't
> running Biztalk and housing its databases), since we don't seem to have
> any processing or memory issues, unless backups are being done.
> I think a separate reporting and OLTP solution would be better in that we
> will be continuing to grow in transactions, possibly doubling it this
> year. The reports would be for internal purposes and also for web
> reporting to the payors and payees we handle. Actually a lot of the
> database bloat is in XML overflow from the forms we receive in and that
> need to be reassembled for some of the web clients.
> Just to note we are using SQL Server 2000 EE active/passive cluster, on
> Windows 2003 servers
> Any insight or advice welcome.
> Nancy
>
|||The problem is that both databases need to be insync,
with the Archive database holding all the data and the OLTP holding all the
"current" data. The only difference will be that whatever data is deleted
by the "Archiving process" will be deleted from OLTP but should not be
deleted from Archive/Rpt DB. That is the hard part to work around. Unless
you push all data to Archive and run any "non-Archive deletes" on Archive
separately, but that might be a maintenance nightmare.
But you've given me more to think about.
Thanks,
Nancy
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:%23F3F$%23FTHHA.3948@.TK2MSFTNGP05.phx.gbl...
> Sounds more like a job for DTS/SSIS. If you delete a replicated record on
> the Publisher (OLTP) side, it deletes on the other side (Subscriber) as
> well.
> Write scripts/packages that move the data that is X days old or has been
> changed since Y days to the other database. You will of course need some
> way to determine when a record changed or was deleted/inserted...
> Other opinions may vary
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:emsbntFTHHA.5016@.TK2MSFTNGP05.phx.gbl...
>
|||You can use replication for this, and just have deletes be effective is they
are on data recent than your archive interval. You will need a filter for
this.
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
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:emsbntFTHHA.5016@.TK2MSFTNGP05.phx.gbl...
> We have a high volume OLTP database of about 300 g and growing at anywhere
> from 5-20 g a week. We are running out of space and want to archive data
> older than 45 days off of the database to an archive database that could
> be used for reporting.
> The question is would replication be the best method for moving data to
> the Reporting/Archive database, in that data older than 45 would be
> deleted from the OLTP but needs to remain in the Archive. We also do
> upgrades to our internal applications about once a month, and sometimes
> the upgrades involve database changes.
> The suggestion has been made to simply run the OLTP and Reporting from the
> same database and simply move it to a larger SAN (and a server that isn't
> running Biztalk and housing its databases), since we don't seem to have
> any processing or memory issues, unless backups are being done.
> I think a separate reporting and OLTP solution would be better in that we
> will be continuing to grow in transactions, possibly doubling it this
> year. The reports would be for internal purposes and also for web
> reporting to the payors and payees we handle. Actually a lot of the
> database bloat is in XML overflow from the forms we receive in and that
> need to be reassembled for some of the web clients.
> Just to note we are using SQL Server 2000 EE active/passive cluster, on
> Windows 2003 servers
> Any insight or advice welcome.
> Nancy
>
|||So in essence we would be filtering out any deletes of data older than 45
days (or however they decide to do the Archiving)? How would you prevent
the deletes from being pushed over to the subscriber? I'm having a hard time
picture a filter to prevent deletes from being pushed.
Anyway, thank you for your time and answer.
Nancy Lytle
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eylT5YHTHHA.5016@.TK2MSFTNGP05.phx.gbl...
> You can use replication for this, and just have deletes be effective is
> they are on data recent than your archive interval. You will need a filter
> for this.
> --
> 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
>
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:emsbntFTHHA.5016@.TK2MSFTNGP05.phx.gbl...
>
|||There are several approaches to this. If there is a date column on each
table you are replicating you could include this logic in your proc.
Here is an example :
create procedure "sp_MSdel_dbotest"
@.pkc1 int
as
/*
begin
delete "dbo"."test"
where "pk" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
*/
delete "dbo"."test"
where "pk" = @.pkc1 and DateColumn>getdate()-25
You can also do your the delete statement which does your archive by
replicating the execution of a stored procedure and have the stored
procedure do nothing on the subscriber.
Another option is to put a dummy filter on the tables you are replicating,
and toggle it so it returns false when you are doing the archiving deletes.
In this situation all DML will not be replicated so it is not optimal but
can work for you.
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
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:eev7s4HTHHA.3592@.TK2MSFTNGP03.phx.gbl...
> So in essence we would be filtering out any deletes of data older than 45
> days (or however they decide to do the Archiving)? How would you prevent
> the deletes from being pushed over to the subscriber? I'm having a hard
> time picture a filter to prevent deletes from being pushed.
> Anyway, thank you for your time and answer.
> Nancy Lytle
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eylT5YHTHHA.5016@.TK2MSFTNGP05.phx.gbl...
>
|||Thank you very much for the explanation and options to work with.
I am sure replication can work in this situation, I think the developers
have just been wary of it best of problems we have had when they have run
update scripts on OLTP and Reporting, without mentioning schema changes,
adding/deleting columns, which we find out when the replication breaks. I
think that is one reason they are afraid to start Archiving with
replication.
But again, thank you so much for your advice and assistance.
Nancy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uKRpy0ITHHA.1600@.TK2MSFTNGP05.phx.gbl...
> There are several approaches to this. If there is a date column on each
> table you are replicating you could include this logic in your proc.
> Here is an example :
>
> create procedure "sp_MSdel_dbotest"
> @.pkc1 int
> as
> /*
> begin
> delete "dbo"."test"
> where "pk" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> */
> delete "dbo"."test"
> where "pk" = @.pkc1 and DateColumn>getdate()-25
> You can also do your the delete statement which does your archive by
> replicating the execution of a stored procedure and have the stored
> procedure do nothing on the subscriber.
> Another option is to put a dummy filter on the tables you are replicating,
> and toggle it so it returns false when you are doing the archiving
> deletes. In this situation all DML will not be replicated so it is not
> optimal but can work for you.
> --
> 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
>
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:eev7s4HTHHA.3592@.TK2MSFTNGP03.phx.gbl...
>

Is replication the answer?

Hi, I've never done replication before, so I don't know if this is what I
need. We have a server here (S1) and another server out of state (S2).
We have our ERP system/database in S2. We have database in S1 which has
views linked to the ERP database in S2. We use Access to generate reports and
the source of the reports are Access queries that query the views of the
database in S1 which linked to database in S2.
Before, we had both server at the same location so generating reports are
not a problem. But, since we moved S2 to out of state, users always get
time-out when running the report. I think it's because we need to run Access
queries based on the views in S1 which pull the data based on the views in
S2.
So, I was thinking, how about making a replication of the S2 database and
place it in S1? The database size is about 45 GB. Does anybody know if this
will solve the time-out problem? what steps should I take? How do I update it
if I decide to make the replication?
Thanks in advance for any suggestion. Sorry if my explanation is confusing.
Lisa
Hi
Your solution may depend on how up-to-date you require the second database
to be. If a 24 hour latency is ok, then you could do an automated
backup/restore process that will update the database overnight or during a
offline period, very similar to log shipping. Replication is also an option
especially if you only need a subset of the data from the primary database.
With SQL 2005 you also have the option of database mirroring combined with a
database snapshot.
HTH
John
"lwidjaya" wrote:

> Hi, I've never done replication before, so I don't know if this is what I
> need. We have a server here (S1) and another server out of state (S2).
> We have our ERP system/database in S2. We have database in S1 which has
> views linked to the ERP database in S2. We use Access to generate reports and
> the source of the reports are Access queries that query the views of the
> database in S1 which linked to database in S2.
> Before, we had both server at the same location so generating reports are
> not a problem. But, since we moved S2 to out of state, users always get
> time-out when running the report. I think it's because we need to run Access
> queries based on the views in S1 which pull the data based on the views in
> S2.
> So, I was thinking, how about making a replication of the S2 database and
> place it in S1? The database size is about 45 GB. Does anybody know if this
> will solve the time-out problem? what steps should I take? How do I update it
> if I decide to make the replication?
> Thanks in advance for any suggestion. Sorry if my explanation is confusing.
> Lisa
|||Hi John, thanks for your reply.
We have SQL 2000 on both servers. We have more than 2000 tables in the
remote server. I think there are only around 500 tables that are used.
If we chose backup/restore, how does it work? do we backup the remote server
database and restore it in the local server? how about the existing databse
in the local server? Should we delete it first?
Lisa
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Your solution may depend on how up-to-date you require the second database
> to be. If a 24 hour latency is ok, then you could do an automated
> backup/restore process that will update the database overnight or during a
> offline period, very similar to log shipping. Replication is also an option
> especially if you only need a subset of the data from the primary database.
> With SQL 2005 you also have the option of database mirroring combined with a
> database snapshot.
> HTH
> John
> "lwidjaya" wrote:
|||Hi Lisa
The back/restore process would backup the database on the remote server
(which you may already be doing!) and then the restore will be over the
existing database on the second server. You do not need to drop the database
on the second server first. You can backup/restore to a network drive (UNC
path), but you may find it better to copy the backup to the second server and
restore from there.
Depending on the size of your database you may want to keep it simple and
always do a full backup, although you could use differential backups and only
periodically restore a full backup.
After restoring the backup you may also want to add additional indexes to
the restored database to make the queries faster, you may also want to make
this database read-only.
HTH
John
"lwidjaya" wrote:
[vbcol=seagreen]
> Hi John, thanks for your reply.
> We have SQL 2000 on both servers. We have more than 2000 tables in the
> remote server. I think there are only around 500 tables that are used.
> If we chose backup/restore, how does it work? do we backup the remote server
> database and restore it in the local server? how about the existing databse
> in the local server? Should we delete it first?
> Lisa
> "John Bell" wrote:
|||Hi John,
thanks for your reply.
When you talked about backup/restore process, is that the process from the
SQL server or can I use the veritas backup exec?
The database size is approx. 45 GB, do you think it's gonna be a problem?
Thanks,
Lisa
"John Bell" wrote:
[vbcol=seagreen]
> Hi Lisa
> The back/restore process would backup the database on the remote server
> (which you may already be doing!) and then the restore will be over the
> existing database on the second server. You do not need to drop the database
> on the second server first. You can backup/restore to a network drive (UNC
> path), but you may find it better to copy the backup to the second server and
> restore from there.
> Depending on the size of your database you may want to keep it simple and
> always do a full backup, although you could use differential backups and only
> periodically restore a full backup.
> After restoring the backup you may also want to add additional indexes to
> the restored database to make the queries faster, you may also want to make
> this database read-only.
> HTH
> John
>
> "lwidjaya" wrote:
|||Hi Lisa
45GB is a bit too large for doing a full backup every night and restoring
it. How are you connected to the remote database and have you investigate the
performance of the connection and also the remote database (in general)?
The backup would be to disc using the T-SQL command BACKUP.
John
"lwidjaya" wrote:
[vbcol=seagreen]
> Hi John,
> thanks for your reply.
> When you talked about backup/restore process, is that the process from the
> SQL server or can I use the veritas backup exec?
> The database size is approx. 45 GB, do you think it's gonna be a problem?
> Thanks,
> Lisa
> "John Bell" wrote:
|||Hi John,
we just moved the database to out of state 2 weeks ago. After the move, I
linked the database/server to our local server here. Since then almost all
users here complain about the speed and the time out. When the database was
here, the database performance was fine.
Thanks,
Lisa
"John Bell" wrote:
[vbcol=seagreen]
> Hi Lisa
> 45GB is a bit too large for doing a full backup every night and restoring
> it. How are you connected to the remote database and have you investigate the
> performance of the connection and also the remote database (in general)?
> The backup would be to disc using the T-SQL command BACKUP.
> John
>
> "lwidjaya" wrote:
|||Hi Lisa
You should see the network guys and ask then what the performance should be,
i.e. if you have a very thin pipe between the sites or if there is some type
of throttling. Also make sure that the network is configured correctly
(tracetrt) and that you are not going around all the houses, you may need to
get a decicated vlan between the servers or some other network improvement.
If you can remote desktop into the other server it may give you some
indication of how the network is performing.
John
"lwidjaya" wrote:
[vbcol=seagreen]
> Hi John,
> we just moved the database to out of state 2 weeks ago. After the move, I
> linked the database/server to our local server here. Since then almost all
> users here complain about the speed and the time out. When the database was
> here, the database performance was fine.
> Thanks,
> Lisa
> "John Bell" wrote:
|||Hi Lisa
Database mirroring is available in SQL 2005, it is sometimes descripted as
real time log shipping and can be used as a failover copy of your database.
You can use a database snapshot of the mirror to get a read-only copy of the
database.
John
"lwidjaya" wrote:
[vbcol=seagreen]
> Hi John,
> I did a research on replication, and I found mirroring. Is it the same thing?
> Lisa
> "John Bell" wrote:
|||Hi John,
I did a research on replication, and I found mirroring. Is it the same thing?
Lisa
"John Bell" wrote:
[vbcol=seagreen]
> Hi Lisa
> You should see the network guys and ask then what the performance should be,
> i.e. if you have a very thin pipe between the sites or if there is some type
> of throttling. Also make sure that the network is configured correctly
> (tracetrt) and that you are not going around all the houses, you may need to
> get a decicated vlan between the servers or some other network improvement.
> If you can remote desktop into the other server it may give you some
> indication of how the network is performing.
> John
> "lwidjaya" wrote:

Is replication the answer?

Hi, I've never done replication before, so I don't know if this is what I
need. We have a server here (S1) and another server out of state (S2).
We have our ERP system/database in S2. We have database in S1 which has
views linked to the ERP database in S2. We use Access to generate reports an
d
the source of the reports are Access queries that query the views of the
database in S1 which linked to database in S2.
Before, we had both server at the same location so generating reports are
not a problem. But, since we moved S2 to out of state, users always get
time-out when running the report. I think it's because we need to run Access
queries based on the views in S1 which pull the data based on the views in
S2.
So, I was thinking, how about making a replication of the S2 database and
place it in S1? The database size is about 45 GB. Does anybody know if this
will solve the time-out problem? what steps should I take? How do I update i
t
if I decide to make the replication?
Thanks in advance for any suggestion. Sorry if my explanation is confusing.
LisaHi
Your solution may depend on how up-to-date you require the second database
to be. If a 24 hour latency is ok, then you could do an automated
backup/restore process that will update the database overnight or during a
offline period, very similar to log shipping. Replication is also an option
especially if you only need a subset of the data from the primary database.
With SQL 2005 you also have the option of database mirroring combined with a
database snapshot.
HTH
John
"lwidjaya" wrote:

> Hi, I've never done replication before, so I don't know if this is what I
> need. We have a server here (S1) and another server out of state (S2).
> We have our ERP system/database in S2. We have database in S1 which has
> views linked to the ERP database in S2. We use Access to generate reports
and
> the source of the reports are Access queries that query the views of the
> database in S1 which linked to database in S2.
> Before, we had both server at the same location so generating reports are
> not a problem. But, since we moved S2 to out of state, users always get
> time-out when running the report. I think it's because we need to run Acce
ss
> queries based on the views in S1 which pull the data based on the views in
> S2.
> So, I was thinking, how about making a replication of the S2 database and
> place it in S1? The database size is about 45 GB. Does anybody know if thi
s
> will solve the time-out problem? what steps should I take? How do I update
it
> if I decide to make the replication?
> Thanks in advance for any suggestion. Sorry if my explanation is confusing
.
> Lisa|||Hi John, thanks for your reply.
We have SQL 2000 on both servers. We have more than 2000 tables in the
remote server. I think there are only around 500 tables that are used.
If we chose backup/restore, how does it work? do we backup the remote server
database and restore it in the local server? how about the existing databse
in the local server? Should we delete it first?
Lisa
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Your solution may depend on how up-to-date you require the second database
> to be. If a 24 hour latency is ok, then you could do an automated
> backup/restore process that will update the database overnight or during a
> offline period, very similar to log shipping. Replication is also an optio
n
> especially if you only need a subset of the data from the primary database
.
> With SQL 2005 you also have the option of database mirroring combined with
a
> database snapshot.
> HTH
> John
> "lwidjaya" wrote:
>|||Hi Lisa
The back/restore process would backup the database on the remote server
(which you may already be doing!) and then the restore will be over the
existing database on the second server. You do not need to drop the database
on the second server first. You can backup/restore to a network drive (UNC
path), but you may find it better to copy the backup to the second server an
d
restore from there.
Depending on the size of your database you may want to keep it simple and
always do a full backup, although you could use differential backups and onl
y
periodically restore a full backup.
After restoring the backup you may also want to add additional indexes to
the restored database to make the queries faster, you may also want to make
this database read-only.
HTH
John
"lwidjaya" wrote:
[vbcol=seagreen]
> Hi John, thanks for your reply.
> We have SQL 2000 on both servers. We have more than 2000 tables in the
> remote server. I think there are only around 500 tables that are used.
> If we chose backup/restore, how does it work? do we backup the remote serv
er
> database and restore it in the local server? how about the existing databs
e
> in the local server? Should we delete it first?
> Lisa
> "John Bell" wrote:
>|||Hi John,
thanks for your reply.
When you talked about backup/restore process, is that the process from the
SQL server or can I use the veritas backup exec?
The database size is approx. 45 GB, do you think it's gonna be a problem?
Thanks,
Lisa
"John Bell" wrote:
[vbcol=seagreen]
> Hi Lisa
> The back/restore process would backup the database on the remote server
> (which you may already be doing!) and then the restore will be over the
> existing database on the second server. You do not need to drop the databa
se
> on the second server first. You can backup/restore to a network drive (UNC
> path), but you may find it better to copy the backup to the second server
and
> restore from there.
> Depending on the size of your database you may want to keep it simple and
> always do a full backup, although you could use differential backups and o
nly
> periodically restore a full backup.
> After restoring the backup you may also want to add additional indexes to
> the restored database to make the queries faster, you may also want to mak
e
> this database read-only.
> HTH
> John
>
> "lwidjaya" wrote:
>|||Hi Lisa
45GB is a bit too large for doing a full backup every night and restoring
it. How are you connected to the remote database and have you investigate th
e
performance of the connection and also the remote database (in general)?
The backup would be to disc using the T-SQL command BACKUP.
John
"lwidjaya" wrote:
[vbcol=seagreen]
> Hi John,
> thanks for your reply.
> When you talked about backup/restore process, is that the process from the
> SQL server or can I use the veritas backup exec?
> The database size is approx. 45 GB, do you think it's gonna be a problem?
> Thanks,
> Lisa
> "John Bell" wrote:
>|||Hi John,
we just moved the database to out of state 2 weeks ago. After the move, I
linked the database/server to our local server here. Since then almost all
users here complain about the speed and the time out. When the database was
here, the database performance was fine.
Thanks,
Lisa
"John Bell" wrote:
[vbcol=seagreen]
> Hi Lisa
> 45GB is a bit too large for doing a full backup every night and restoring
> it. How are you connected to the remote database and have you investigate
the
> performance of the connection and also the remote database (in general)?
> The backup would be to disc using the T-SQL command BACKUP.
> John
>
> "lwidjaya" wrote:
>|||Hi Lisa
You should see the network guys and ask then what the performance should be,
i.e. if you have a very thin pipe between the sites or if there is some type
of throttling. Also make sure that the network is configured correctly
(tracetrt) and that you are not going around all the houses, you may need to
get a decicated vlan between the servers or some other network improvement.
If you can remote desktop into the other server it may give you some
indication of how the network is performing.
John
"lwidjaya" wrote:
[vbcol=seagreen]
> Hi John,
> we just moved the database to out of state 2 weeks ago. After the move, I
> linked the database/server to our local server here. Since then almost all
> users here complain about the speed and the time out. When the database wa
s
> here, the database performance was fine.
> Thanks,
> Lisa
> "John Bell" wrote:
>|||Hi Lisa
Database mirroring is available in SQL 2005, it is sometimes descripted as
real time log shipping and can be used as a failover copy of your database.
You can use a database snapshot of the mirror to get a read-only copy of the
database.
John
"lwidjaya" wrote:
[vbcol=seagreen]
> Hi John,
> I did a research on replication, and I found mirroring. Is it the same thi
ng?
> Lisa
> "John Bell" wrote:
>|||Hi John,
I did a research on replication, and I found mirroring. Is it the same thing
?
Lisa
"John Bell" wrote:
[vbcol=seagreen]
> Hi Lisa
> You should see the network guys and ask then what the performance should b
e,
> i.e. if you have a very thin pipe between the sites or if there is some ty
pe
> of throttling. Also make sure that the network is configured correctly
> (tracetrt) and that you are not going around all the houses, you may need
to
> get a decicated vlan between the servers or some other network improvement
.
> If you can remote desktop into the other server it may give you some
> indication of how the network is performing.
> John
> "lwidjaya" wrote:
>

Is replication the answer?

Hi, I've never done replication before, so I don't know if this is what I
need. We have a server here (S1) and another server out of state (S2).
We have our ERP system/database in S2. We have database in S1 which has
views linked to the ERP database in S2. We use Access to generate reports and
the source of the reports are Access queries that query the views of the
database in S1 which linked to database in S2.
Before, we had both server at the same location so generating reports are
not a problem. But, since we moved S2 to out of state, users always get
time-out when running the report. I think it's because we need to run Access
queries based on the views in S1 which pull the data based on the views in
S2.
So, I was thinking, how about making a replication of the S2 database and
place it in S1? The database size is about 45 GB. Does anybody know if this
will solve the time-out problem? what steps should I take? How do I update it
if I decide to make the replication?
Thanks in advance for any suggestion. Sorry if my explanation is confusing.
LisaHi
Your solution may depend on how up-to-date you require the second database
to be. If a 24 hour latency is ok, then you could do an automated
backup/restore process that will update the database overnight or during a
offline period, very similar to log shipping. Replication is also an option
especially if you only need a subset of the data from the primary database.
With SQL 2005 you also have the option of database mirroring combined with a
database snapshot.
HTH
John
"lwidjaya" wrote:
> Hi, I've never done replication before, so I don't know if this is what I
> need. We have a server here (S1) and another server out of state (S2).
> We have our ERP system/database in S2. We have database in S1 which has
> views linked to the ERP database in S2. We use Access to generate reports and
> the source of the reports are Access queries that query the views of the
> database in S1 which linked to database in S2.
> Before, we had both server at the same location so generating reports are
> not a problem. But, since we moved S2 to out of state, users always get
> time-out when running the report. I think it's because we need to run Access
> queries based on the views in S1 which pull the data based on the views in
> S2.
> So, I was thinking, how about making a replication of the S2 database and
> place it in S1? The database size is about 45 GB. Does anybody know if this
> will solve the time-out problem? what steps should I take? How do I update it
> if I decide to make the replication?
> Thanks in advance for any suggestion. Sorry if my explanation is confusing.
> Lisa|||Hi John, thanks for your reply.
We have SQL 2000 on both servers. We have more than 2000 tables in the
remote server. I think there are only around 500 tables that are used.
If we chose backup/restore, how does it work? do we backup the remote server
database and restore it in the local server? how about the existing databse
in the local server? Should we delete it first?
Lisa
"John Bell" wrote:
> Hi
> Your solution may depend on how up-to-date you require the second database
> to be. If a 24 hour latency is ok, then you could do an automated
> backup/restore process that will update the database overnight or during a
> offline period, very similar to log shipping. Replication is also an option
> especially if you only need a subset of the data from the primary database.
> With SQL 2005 you also have the option of database mirroring combined with a
> database snapshot.
> HTH
> John
> "lwidjaya" wrote:
> > Hi, I've never done replication before, so I don't know if this is what I
> > need. We have a server here (S1) and another server out of state (S2).
> > We have our ERP system/database in S2. We have database in S1 which has
> > views linked to the ERP database in S2. We use Access to generate reports and
> > the source of the reports are Access queries that query the views of the
> > database in S1 which linked to database in S2.
> > Before, we had both server at the same location so generating reports are
> > not a problem. But, since we moved S2 to out of state, users always get
> > time-out when running the report. I think it's because we need to run Access
> > queries based on the views in S1 which pull the data based on the views in
> > S2.
> > So, I was thinking, how about making a replication of the S2 database and
> > place it in S1? The database size is about 45 GB. Does anybody know if this
> > will solve the time-out problem? what steps should I take? How do I update it
> > if I decide to make the replication?
> > Thanks in advance for any suggestion. Sorry if my explanation is confusing.
> > Lisa|||Hi Lisa
The back/restore process would backup the database on the remote server
(which you may already be doing!) and then the restore will be over the
existing database on the second server. You do not need to drop the database
on the second server first. You can backup/restore to a network drive (UNC
path), but you may find it better to copy the backup to the second server and
restore from there.
Depending on the size of your database you may want to keep it simple and
always do a full backup, although you could use differential backups and only
periodically restore a full backup.
After restoring the backup you may also want to add additional indexes to
the restored database to make the queries faster, you may also want to make
this database read-only.
HTH
John
"lwidjaya" wrote:
> Hi John, thanks for your reply.
> We have SQL 2000 on both servers. We have more than 2000 tables in the
> remote server. I think there are only around 500 tables that are used.
> If we chose backup/restore, how does it work? do we backup the remote server
> database and restore it in the local server? how about the existing databse
> in the local server? Should we delete it first?
> Lisa
> "John Bell" wrote:
> > Hi
> >
> > Your solution may depend on how up-to-date you require the second database
> > to be. If a 24 hour latency is ok, then you could do an automated
> > backup/restore process that will update the database overnight or during a
> > offline period, very similar to log shipping. Replication is also an option
> > especially if you only need a subset of the data from the primary database.
> > With SQL 2005 you also have the option of database mirroring combined with a
> > database snapshot.
> >
> > HTH
> >
> > John
> >
> > "lwidjaya" wrote:
> >
> > > Hi, I've never done replication before, so I don't know if this is what I
> > > need. We have a server here (S1) and another server out of state (S2).
> > > We have our ERP system/database in S2. We have database in S1 which has
> > > views linked to the ERP database in S2. We use Access to generate reports and
> > > the source of the reports are Access queries that query the views of the
> > > database in S1 which linked to database in S2.
> > > Before, we had both server at the same location so generating reports are
> > > not a problem. But, since we moved S2 to out of state, users always get
> > > time-out when running the report. I think it's because we need to run Access
> > > queries based on the views in S1 which pull the data based on the views in
> > > S2.
> > > So, I was thinking, how about making a replication of the S2 database and
> > > place it in S1? The database size is about 45 GB. Does anybody know if this
> > > will solve the time-out problem? what steps should I take? How do I update it
> > > if I decide to make the replication?
> > > Thanks in advance for any suggestion. Sorry if my explanation is confusing.
> > > Lisa|||Hi John,
thanks for your reply.
When you talked about backup/restore process, is that the process from the
SQL server or can I use the veritas backup exec?
The database size is approx. 45 GB, do you think it's gonna be a problem?
Thanks,
Lisa
"John Bell" wrote:
> Hi Lisa
> The back/restore process would backup the database on the remote server
> (which you may already be doing!) and then the restore will be over the
> existing database on the second server. You do not need to drop the database
> on the second server first. You can backup/restore to a network drive (UNC
> path), but you may find it better to copy the backup to the second server and
> restore from there.
> Depending on the size of your database you may want to keep it simple and
> always do a full backup, although you could use differential backups and only
> periodically restore a full backup.
> After restoring the backup you may also want to add additional indexes to
> the restored database to make the queries faster, you may also want to make
> this database read-only.
> HTH
> John
>
> "lwidjaya" wrote:
> > Hi John, thanks for your reply.
> > We have SQL 2000 on both servers. We have more than 2000 tables in the
> > remote server. I think there are only around 500 tables that are used.
> > If we chose backup/restore, how does it work? do we backup the remote server
> > database and restore it in the local server? how about the existing databse
> > in the local server? Should we delete it first?
> >
> > Lisa
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Your solution may depend on how up-to-date you require the second database
> > > to be. If a 24 hour latency is ok, then you could do an automated
> > > backup/restore process that will update the database overnight or during a
> > > offline period, very similar to log shipping. Replication is also an option
> > > especially if you only need a subset of the data from the primary database.
> > > With SQL 2005 you also have the option of database mirroring combined with a
> > > database snapshot.
> > >
> > > HTH
> > >
> > > John
> > >
> > > "lwidjaya" wrote:
> > >
> > > > Hi, I've never done replication before, so I don't know if this is what I
> > > > need. We have a server here (S1) and another server out of state (S2).
> > > > We have our ERP system/database in S2. We have database in S1 which has
> > > > views linked to the ERP database in S2. We use Access to generate reports and
> > > > the source of the reports are Access queries that query the views of the
> > > > database in S1 which linked to database in S2.
> > > > Before, we had both server at the same location so generating reports are
> > > > not a problem. But, since we moved S2 to out of state, users always get
> > > > time-out when running the report. I think it's because we need to run Access
> > > > queries based on the views in S1 which pull the data based on the views in
> > > > S2.
> > > > So, I was thinking, how about making a replication of the S2 database and
> > > > place it in S1? The database size is about 45 GB. Does anybody know if this
> > > > will solve the time-out problem? what steps should I take? How do I update it
> > > > if I decide to make the replication?
> > > > Thanks in advance for any suggestion. Sorry if my explanation is confusing.
> > > > Lisa|||Hi Lisa
45GB is a bit too large for doing a full backup every night and restoring
it. How are you connected to the remote database and have you investigate the
performance of the connection and also the remote database (in general)?
The backup would be to disc using the T-SQL command BACKUP.
John
"lwidjaya" wrote:
> Hi John,
> thanks for your reply.
> When you talked about backup/restore process, is that the process from the
> SQL server or can I use the veritas backup exec?
> The database size is approx. 45 GB, do you think it's gonna be a problem?
> Thanks,
> Lisa
> "John Bell" wrote:
> > Hi Lisa
> >
> > The back/restore process would backup the database on the remote server
> > (which you may already be doing!) and then the restore will be over the
> > existing database on the second server. You do not need to drop the database
> > on the second server first. You can backup/restore to a network drive (UNC
> > path), but you may find it better to copy the backup to the second server and
> > restore from there.
> >
> > Depending on the size of your database you may want to keep it simple and
> > always do a full backup, although you could use differential backups and only
> > periodically restore a full backup.
> >
> > After restoring the backup you may also want to add additional indexes to
> > the restored database to make the queries faster, you may also want to make
> > this database read-only.
> >
> > HTH
> >
> > John
> >
> >
> > "lwidjaya" wrote:
> >
> > > Hi John, thanks for your reply.
> > > We have SQL 2000 on both servers. We have more than 2000 tables in the
> > > remote server. I think there are only around 500 tables that are used.
> > > If we chose backup/restore, how does it work? do we backup the remote server
> > > database and restore it in the local server? how about the existing databse
> > > in the local server? Should we delete it first?
> > >
> > > Lisa
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > Your solution may depend on how up-to-date you require the second database
> > > > to be. If a 24 hour latency is ok, then you could do an automated
> > > > backup/restore process that will update the database overnight or during a
> > > > offline period, very similar to log shipping. Replication is also an option
> > > > especially if you only need a subset of the data from the primary database.
> > > > With SQL 2005 you also have the option of database mirroring combined with a
> > > > database snapshot.
> > > >
> > > > HTH
> > > >
> > > > John
> > > >
> > > > "lwidjaya" wrote:
> > > >
> > > > > Hi, I've never done replication before, so I don't know if this is what I
> > > > > need. We have a server here (S1) and another server out of state (S2).
> > > > > We have our ERP system/database in S2. We have database in S1 which has
> > > > > views linked to the ERP database in S2. We use Access to generate reports and
> > > > > the source of the reports are Access queries that query the views of the
> > > > > database in S1 which linked to database in S2.
> > > > > Before, we had both server at the same location so generating reports are
> > > > > not a problem. But, since we moved S2 to out of state, users always get
> > > > > time-out when running the report. I think it's because we need to run Access
> > > > > queries based on the views in S1 which pull the data based on the views in
> > > > > S2.
> > > > > So, I was thinking, how about making a replication of the S2 database and
> > > > > place it in S1? The database size is about 45 GB. Does anybody know if this
> > > > > will solve the time-out problem? what steps should I take? How do I update it
> > > > > if I decide to make the replication?
> > > > > Thanks in advance for any suggestion. Sorry if my explanation is confusing.
> > > > > Lisa|||Hi John,
we just moved the database to out of state 2 weeks ago. After the move, I
linked the database/server to our local server here. Since then almost all
users here complain about the speed and the time out. When the database was
here, the database performance was fine.
Thanks,
Lisa
"John Bell" wrote:
> Hi Lisa
> 45GB is a bit too large for doing a full backup every night and restoring
> it. How are you connected to the remote database and have you investigate the
> performance of the connection and also the remote database (in general)?
> The backup would be to disc using the T-SQL command BACKUP.
> John
>
> "lwidjaya" wrote:
> > Hi John,
> > thanks for your reply.
> > When you talked about backup/restore process, is that the process from the
> > SQL server or can I use the veritas backup exec?
> > The database size is approx. 45 GB, do you think it's gonna be a problem?
> >
> > Thanks,
> >
> > Lisa
> >
> > "John Bell" wrote:
> >
> > > Hi Lisa
> > >
> > > The back/restore process would backup the database on the remote server
> > > (which you may already be doing!) and then the restore will be over the
> > > existing database on the second server. You do not need to drop the database
> > > on the second server first. You can backup/restore to a network drive (UNC
> > > path), but you may find it better to copy the backup to the second server and
> > > restore from there.
> > >
> > > Depending on the size of your database you may want to keep it simple and
> > > always do a full backup, although you could use differential backups and only
> > > periodically restore a full backup.
> > >
> > > After restoring the backup you may also want to add additional indexes to
> > > the restored database to make the queries faster, you may also want to make
> > > this database read-only.
> > >
> > > HTH
> > >
> > > John
> > >
> > >
> > > "lwidjaya" wrote:
> > >
> > > > Hi John, thanks for your reply.
> > > > We have SQL 2000 on both servers. We have more than 2000 tables in the
> > > > remote server. I think there are only around 500 tables that are used.
> > > > If we chose backup/restore, how does it work? do we backup the remote server
> > > > database and restore it in the local server? how about the existing databse
> > > > in the local server? Should we delete it first?
> > > >
> > > > Lisa
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi
> > > > >
> > > > > Your solution may depend on how up-to-date you require the second database
> > > > > to be. If a 24 hour latency is ok, then you could do an automated
> > > > > backup/restore process that will update the database overnight or during a
> > > > > offline period, very similar to log shipping. Replication is also an option
> > > > > especially if you only need a subset of the data from the primary database.
> > > > > With SQL 2005 you also have the option of database mirroring combined with a
> > > > > database snapshot.
> > > > >
> > > > > HTH
> > > > >
> > > > > John
> > > > >
> > > > > "lwidjaya" wrote:
> > > > >
> > > > > > Hi, I've never done replication before, so I don't know if this is what I
> > > > > > need. We have a server here (S1) and another server out of state (S2).
> > > > > > We have our ERP system/database in S2. We have database in S1 which has
> > > > > > views linked to the ERP database in S2. We use Access to generate reports and
> > > > > > the source of the reports are Access queries that query the views of the
> > > > > > database in S1 which linked to database in S2.
> > > > > > Before, we had both server at the same location so generating reports are
> > > > > > not a problem. But, since we moved S2 to out of state, users always get
> > > > > > time-out when running the report. I think it's because we need to run Access
> > > > > > queries based on the views in S1 which pull the data based on the views in
> > > > > > S2.
> > > > > > So, I was thinking, how about making a replication of the S2 database and
> > > > > > place it in S1? The database size is about 45 GB. Does anybody know if this
> > > > > > will solve the time-out problem? what steps should I take? How do I update it
> > > > > > if I decide to make the replication?
> > > > > > Thanks in advance for any suggestion. Sorry if my explanation is confusing.
> > > > > > Lisa|||Hi Lisa
You should see the network guys and ask then what the performance should be,
i.e. if you have a very thin pipe between the sites or if there is some type
of throttling. Also make sure that the network is configured correctly
(tracetrt) and that you are not going around all the houses, you may need to
get a decicated vlan between the servers or some other network improvement.
If you can remote desktop into the other server it may give you some
indication of how the network is performing.
John
"lwidjaya" wrote:
> Hi John,
> we just moved the database to out of state 2 weeks ago. After the move, I
> linked the database/server to our local server here. Since then almost all
> users here complain about the speed and the time out. When the database was
> here, the database performance was fine.
> Thanks,
> Lisa
> "John Bell" wrote:
> > Hi Lisa
> >
> > 45GB is a bit too large for doing a full backup every night and restoring
> > it. How are you connected to the remote database and have you investigate the
> > performance of the connection and also the remote database (in general)?
> >
> > The backup would be to disc using the T-SQL command BACKUP.
> >
> > John
> >
> >
> >
> > "lwidjaya" wrote:
> >
> > > Hi John,
> > > thanks for your reply.
> > > When you talked about backup/restore process, is that the process from the
> > > SQL server or can I use the veritas backup exec?
> > > The database size is approx. 45 GB, do you think it's gonna be a problem?
> > >
> > > Thanks,
> > >
> > > Lisa
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Lisa
> > > >
> > > > The back/restore process would backup the database on the remote server
> > > > (which you may already be doing!) and then the restore will be over the
> > > > existing database on the second server. You do not need to drop the database
> > > > on the second server first. You can backup/restore to a network drive (UNC
> > > > path), but you may find it better to copy the backup to the second server and
> > > > restore from there.
> > > >
> > > > Depending on the size of your database you may want to keep it simple and
> > > > always do a full backup, although you could use differential backups and only
> > > > periodically restore a full backup.
> > > >
> > > > After restoring the backup you may also want to add additional indexes to
> > > > the restored database to make the queries faster, you may also want to make
> > > > this database read-only.
> > > >
> > > > HTH
> > > >
> > > > John
> > > >
> > > >
> > > > "lwidjaya" wrote:
> > > >
> > > > > Hi John, thanks for your reply.
> > > > > We have SQL 2000 on both servers. We have more than 2000 tables in the
> > > > > remote server. I think there are only around 500 tables that are used.
> > > > > If we chose backup/restore, how does it work? do we backup the remote server
> > > > > database and restore it in the local server? how about the existing databse
> > > > > in the local server? Should we delete it first?
> > > > >
> > > > > Lisa
> > > > >
> > > > > "John Bell" wrote:
> > > > >
> > > > > > Hi
> > > > > >
> > > > > > Your solution may depend on how up-to-date you require the second database
> > > > > > to be. If a 24 hour latency is ok, then you could do an automated
> > > > > > backup/restore process that will update the database overnight or during a
> > > > > > offline period, very similar to log shipping. Replication is also an option
> > > > > > especially if you only need a subset of the data from the primary database.
> > > > > > With SQL 2005 you also have the option of database mirroring combined with a
> > > > > > database snapshot.
> > > > > >
> > > > > > HTH
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "lwidjaya" wrote:
> > > > > >
> > > > > > > Hi, I've never done replication before, so I don't know if this is what I
> > > > > > > need. We have a server here (S1) and another server out of state (S2).
> > > > > > > We have our ERP system/database in S2. We have database in S1 which has
> > > > > > > views linked to the ERP database in S2. We use Access to generate reports and
> > > > > > > the source of the reports are Access queries that query the views of the
> > > > > > > database in S1 which linked to database in S2.
> > > > > > > Before, we had both server at the same location so generating reports are
> > > > > > > not a problem. But, since we moved S2 to out of state, users always get
> > > > > > > time-out when running the report. I think it's because we need to run Access
> > > > > > > queries based on the views in S1 which pull the data based on the views in
> > > > > > > S2.
> > > > > > > So, I was thinking, how about making a replication of the S2 database and
> > > > > > > place it in S1? The database size is about 45 GB. Does anybody know if this
> > > > > > > will solve the time-out problem? what steps should I take? How do I update it
> > > > > > > if I decide to make the replication?
> > > > > > > Thanks in advance for any suggestion. Sorry if my explanation is confusing.
> > > > > > > Lisa

is replication session a transaction?

I am curious if a "session" for merge replication is a transaction. If I
see 100 actions in the Agent History and the last one is "the process could
not enumerate changes at the subscriber" due to General Network Error
(dropped connection or whatever), did those 10000 data changes get
committed or were they all rolled back and the next agent run will start
that all over again.
Assuming the negative possibility, what profile parameter would I change to
reduce the number of rows before a commit? Our agents for low-bandwidth
subscribers are running for hours and hours and never seem to catch up
because they always eventually lose the connection.
Thanks for any insights.
They are committed as singletons. So a batch of 100 (by default or whatever
is in the Upload|DownloadWriteChangesPerBatch) is tried and if errors
occurred while applying a batch the error rows go into a retry look which is
tried after the batch completes.
Should an network error occur during a synchronization, the merge agent will
look at the last batch successfully applied and then start to apply the next
one again. There is a possiblility that should 99 of the rows in the last
batch make it through, all 99 will be tried again to push the last one
through which was not successful.
You will notice that the slow link profile drops these values from 50 to 1
(or 5 for UploadGenerationsPerBatch).
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
"Anachostic" <anachostic@.remove.700cb.net> wrote in message
news:Xns995CA07258BD9anachostic@.207.46.248.16...
>I am curious if a "session" for merge replication is a transaction. If I
> see 100 actions in the Agent History and the last one is "the process
> could
> not enumerate changes at the subscriber" due to General Network Error
> (dropped connection or whatever), did those 10000 data changes get
> committed or were they all rolled back and the next agent run will start
> that all over again.
> Assuming the negative possibility, what profile parameter would I change
> to
> reduce the number of rows before a commit? Our agents for low-bandwidth
> subscribers are running for hours and hours and never seem to catch up
> because they always eventually lose the connection.
> Thanks for any insights.