Big problem

I have two databases that replicate with merge replication. A couple of days, the replication process failed while trying to do a re-initialization (with upload changes to publisher option enabled) and it seemed that some of the records in the subscriber
were missing. So I did a restore of the subscriber and then I then kicked off another re-initialization (with upload changes to publisher option enabled) and it has completed succesfully. However, now it seems that some of the older data that was created
in the subscriber are no longer in the subscriber but they are in the publisher. In addition to this, some of the records that were created in the subscriber did not upload to the publisher.
Is there any way to manually flag records to force them to replicate (for inserts)?
You could try doing a dummy update on the relevant rows (or all rows if necessary). Something like
Update MyTable set fname = fname
should do it.
HTH,
Paul Ibison
|||Yes, I tried this, but the problem is that I need to know which column has changed for each row. Is there any way to get it to replicate the whole row?
|||In your first post you explain that there are some rows on the publisher which don't exist on the subscriber and vice-versa. For some reason the merge triggers didn't fire and MSmerge_contents doesn't contain the the row value, so it is not replicated. Up
dating these rows - any column - will force an entry to this system table, and this should then be replicated. However your second post explains you need to know which column has changed for each row, which is what confuses me . Why is this required - d
o you also have rows which which exist on both publisher and subscriber but are not synchronized and contain different data?
Regards,
Paul Ibison
|||to force a row to be replicated again use sp_mergedummyupdate.
When the update is done on the other end SQL Server constructs the parameters for the stored procedure so that only the changed column is updated. So, it is stored somewhere. Figuring out where is not for mere mortals such as I - it is for the likes of su
ch as Mr. Ibison
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Paul,
Yes, sorry, I was not clear in my first description of the problem. There are some instances where there is already a recod there but it is an older version, and there are some instances where the records are not there at all. I was mixing the two problem
s up.
Thank you (and Hilary) for your answers. they have been very helpfull.

Bidirectional Synchronization for SQL Server Mobile?

I create a distributed database for mobile application. I replicate a table that distribute on mobile device. I follow instruction how to create distributor, publication, replication, web synchronization, and subscriber database. I have done fine for synchronization between mobile database into desktop database (in this case SQL Server 2005 Standard Edition). But the problem is how can setup publication so it can bidirectional, not only from mobile database into desktop database, but also from desktop database into mobile database. So in the mobile database can have same data with desktop database even on mobile database lost some old data.

Its like data exchange between both engine. Desktop and mobile have same data. For filtering I can put filter on the desktop server for replicated table, so don't worry how I split the data.

Thanks a lot.

Look into Merge replication, or transactional replication with updatable subscriptions.

Martin

Bidirectional Replication

Hello,
I am trying to perform 2 way replication in SQL 2000.
SERVERA = OrdersTable -> I want data to replicate to this
server from SERVERB on UPDATE only.
SERVERB = OrdersTable -> I want data to replicate to this
server from SERVERA on INSERT only.
This is what I have done.
I created a publication on SERVERA and in the article
properties, I checked only INSERT.
Then I created a push subscription to SERVERB.
I then created a publication on SERVERB and in the article
properties, I checked only UPDATE. Then I created a push
subscription to SERVERA.
Is this the correct way to achieve my goal?
Thanks in advance.
I don't think so. First are you using merge replication? It sounds like you are. I think you are talking about the merging
changes tab options on the articles properties tab.
This verifies that a merge agent has rights to insert, update or delete. So you can have 1 pull subscriber which can do all three depending on his/her rights, and another that can't, or can have a subset or rights.
bi-directional transactional replication might be the way to go with this as you can write custom stored procedures to incorporate your business logic.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Bi Directional Transactional Replication

Okay I posted this yesterday I believe:
“Let's say TableA on a 2000 ServerA.DatabaseA has an identity key and I want
to transactionally replicate that TableA to a ServerB.DatabaseA (that is also
2000). But here's the catch: in the case of a significant outage on ServerA
I want to be able to repoint the web to ServerB.DatabaseA and have everything
function successfully.
A previous responder suggested (I think) that I could simultaneously set up
transactional replication from ServerA.DatabaseA to ServerB.DatabaseA and
from ServerB.DatabaseA to ServerA.DatabaseA and have one database have the
identity on serverA with a seed of B using odds and the identity on serverB
using a seed of evens for example.
Here are my questions:
1) Will Sql Server 2000 let you set up this kind of quasi-two-way
transactional replication?
2) If the answer to #1 is 'yes', then how can you have identities set up on
both tables? Won't replication throw up if you're copying from an identity
column on ServerA to its sister table on ServerB that also has an identity
column?
3) Would this setup be much easier to implement than standard merge
replication?
Any tips on how I might do this would be much appreciated.”
And Hilary very kindly posted this:
“1) Yes, but you have to create the objects in advance on both sides and use
the not for replication switch on the identity colummns. You must also set
the identity increments and seeds. Then you must run dbcc
checkident(tablename, reseed, correctvalue) to fix everything.
2) replication doesn't throw up per se. Basically when you create your
publication and get to the specify articles dialog box, click on the browse
button to the right of the table, and in the snapshot section, select keep
existing table unchanged. Run the checkident to ensure that you are getting
odds on the correct sides, and evens on the other.
3) Standard merge is simpler to set up. It have typically 1 to 2 minutes of
latency whereas with bi-directional transactional on a system under load you
are looking at about 10-20s. Merge replication is harder to troubleshoot and
recover from.”
So I tried to do what Hilary said:
I set up Not for Replication, Seed=1 and Increment=2 on the identity field
on ServerA.TableA after setting up transactional replication from
ServerA.TableA to ServerB.TableA. I then manually ran the snapshot and it
sent over the table to ServerB.TableA. It created a non-identity column on
ServerB.TableA, however, so I had to go in and manually change ServerB.TableA
to Not for Replication, Seed=2 and Increment=2.
Now here’s the problem:
When I insert a record into ServerA.TableA, it does not migrate over to
ServerB.TableA.
What am I doing wrong?
This type of setup is possible. Replication performs identity inserts as you
set up the columns with 'Identity (not for replication)'.
Is it easier than merge? Probably I'd say a little more difficult although
neither are really difficult. The issue is that you have to do the setup
manually whereas merge can be done with the wizard. It'll perform much
better than merge, but merge will cope with schema changes better. Anyway,
here's a couple of articles to help you out:
http://support.microsoft.com/default...b;en-us;820675
http://msdn.microsoft.com/library/de...lsamp_3ve6.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Best way to replicate large DB

I would like to replicate a large database to a reporting server on the same
network. I have very good bandwidth between the two servers
The DB in questio is ~200GB and has a transaction rate of ~3000 per minute.
There are 10 tables that are > 10 GB's.
Does anyone have any advice for me? or a document to review?
Thanks in advance!
Mark,
there are no articles specific to large volume replication that I am aware
of. There is an article on performance tuning and optimization
(http://www.microsoft.com/technet/pro.../tranrepl.mspx)
that should be of some help in your situation. Pull subscribers and a
separate distributor would probably be the first step. Teckie issues like
Raid 0+1, #procs, RAM and high bandwidth are not specific to replication,
but in your case the setup will probably prove crucial. These are also
partially covered in the article.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Best way to replicate Laptop users

Hi,
I have a central server witch fetches information from other local
servers. I setup merge replications so that all changes made at local
server would be replicated at central server in the night.
Now, I want all the information on central server (that is, the
combined information from all the local servers) to be dumped on the
laptop users so that they can work offline. Because of the amount of
the data I have to transfer, I can't no longer just delete all the
objects and copy over all tables any more (takes way too long.)
However, I can not setup another merge replication because the central
server is already setuped in the local to central merge replication.
The only way I could think of is to setup merge replication from local
servers to the laptop user and probably setup a job to setup the first
snapshot dump (takes around 45 minutes) in the middle of the night so
that it won't affect local server during business hour.
I am just wondering if there are better ways to do this?
Conditions:
1 - I prefer not to touch the local servers since they are vital to our
operation during regular hours (9am ~ 9pm, 7 days / week).
2 - I prefer to replication with the central server for it is a faster
server and it is not linked to any real-time operation machines (except
that it replicates with the other local servers in the middle of the
night)
3 - Snapshot replication or DTS is out of the question for the sales
table contains 2 million+ rows and growing
Any good suggestions?
Thank you in advance
Charlie Chang
[charlies224@.hotmail.com]
This might be a good case for using dynamic snapshots so the laptop users
get only the data they require. You will need a column on the tables you are
replicating to partition your data with.
I would use WSM (Windows Synchronization Manager) to manage the pull.
Depending on your data flow you might be able to publish from the local
servers.
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
<charlies224@.hotmail.com> wrote in message
news:1109629058.527708.180340@.l41g2000cwc.googlegr oups.com...
> Hi,
> I have a central server witch fetches information from other local
> servers. I setup merge replications so that all changes made at local
> server would be replicated at central server in the night.
> Now, I want all the information on central server (that is, the
> combined information from all the local servers) to be dumped on the
> laptop users so that they can work offline. Because of the amount of
> the data I have to transfer, I can't no longer just delete all the
> objects and copy over all tables any more (takes way too long.)
> However, I can not setup another merge replication because the central
> server is already setuped in the local to central merge replication.
> The only way I could think of is to setup merge replication from local
> servers to the laptop user and probably setup a job to setup the first
> snapshot dump (takes around 45 minutes) in the middle of the night so
> that it won't affect local server during business hour.
> I am just wondering if there are better ways to do this?
> Conditions:
> 1 - I prefer not to touch the local servers since they are vital to our
> operation during regular hours (9am ~ 9pm, 7 days / week).
> 2 - I prefer to replication with the central server for it is a faster
> server and it is not linked to any real-time operation machines (except
> that it replicates with the other local servers in the middle of the
> night)
> 3 - Snapshot replication or DTS is out of the question for the sales
> table contains 2 million+ rows and growing
>
> Any good suggestions?
> Thank you in advance
> Charlie Chang
> [charlies224@.hotmail.com]
>

Best way to replicate huge transactions

We do nightly processing for our warehouse data on Server A and we replicate
the data after all the processing to Server B.
What is the fastest way to push this data across ? Right now it seems like
it takes more than an hour to push all the changes across.i.e an hour
latency. We are using trans replication. Any parameters we can tweak to push
the data faster or are there any other techniques.
Please advice.. Thanks
Hassan,
this article quantifies the effects of using most of the available
parameters:
http://www.microsoft.com/technet/pro.../tranrepl.mspx
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||replicate the execution of stored procedures if
1) your stored procedures modify more than one row at a time
2) the bulk of your batch operations use procs
consider using merge replication if
1) your batch operations modify the same row many times, ie a stock market
application.
2) use download only exchangetype
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