Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Replication / March 2007

Tip: Looking for answers? Try searching our database.

Any problems when table isn't recreated during initialization?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Oskar - 06 Mar 2007 18:44 GMT
Hi
I've configured push Merge replication between MS SQL Server 2005 Standard
and MS SQL Server 2005 Express. What kind of difficulties will I experience
if I don't recreate replicated tables during the reinitialization of
subscribers?

-- Thanks, Oskar.
Paul Ibison - 06 Mar 2007 18:56 GMT
Oskar - nosync initializations are fairly standard. However are you saying
that there is non-convergence of data? If so, then potentialy you'll be
plagued with errors.
Cheers,
            Paul Ibison SQL Server MVP, www.replicationanswers.com
Oskar - 06 Mar 2007 19:26 GMT
Thanks Paul. I don't really know what do you mean by non-convergence of data?
Could you please shed some light on that for me? By the way I use
non-overlapping partitions.

> Oskar - nosync initializations are fairly standard. However are you saying
> that there is non-convergence of data? If so, then potentialy you'll be
> plagued with errors.
>  Cheers,
>              Paul Ibison SQL Server MVP, www.replicationanswers.com 
Paul Ibison - 06 Mar 2007 20:04 GMT
What I'm thinking of is the possibility that someone on the publisher or
subscriber could change the data after the last synchronization and before
the reinitialization. You could prevent this with securite etc and use
RedGate's DataCompare or TableDiff to check if this is a problem.
 Cheers,
             Paul Ibison SQL Server MVP, www.replicationanswers.com
Oskar - 11 Mar 2007 16:46 GMT
Thanks for making that clear. But wait, isn't the "upload pending changes
before reinitialization" option supposed to solve that? Actually I've tried
that and it doesn't seem to be uploading any pending cahanges from my
subscriber. Is that a feature or a bug?

-- Thanks, Oskar.

> What I'm thinking of is the possibility that someone on the publisher or
> subscriber could change the data after the last synchronization and before
> the reinitialization. You could prevent this with securite etc and use
> RedGate's DataCompare or TableDiff to check if this is a problem.
>   Cheers,
>               Paul Ibison SQL Server MVP, www.replicationanswers.com
Paul Ibison - 11 Mar 2007 17:11 GMT
If the publication is still active you are 100% correct. I have taken
advantage of this option before and it worked fine - can you explain a
little more about your setup - if the subscriber changes aren't getting
uploaded there must be something particular about the publication (eg is it
filtered statically or dynamically).
  Cheers,
              Paul Ibison SQL Server MVP, www.replicationanswers.com
Oskar - 11 Mar 2007 18:12 GMT
Yes, I have parameterized (dynamic) filters for articles and non-overlapping
partitions. Is that a known limitation?

-- Thanks, Oskar

> If the publication is still active you are 100% correct. I have taken
> advantage of this option before and it worked fine - can you explain a
[quoted text clipped - 3 lines]
>    Cheers,
>                Paul Ibison SQL Server MVP, www.replicationanswers.com
Oskar - 11 Mar 2007 18:34 GMT
By the way I didn't reinitialize the subscription because of adding,
dropping, or changing a parameterized filter in case you suspected that.

> Yes, I have parameterized (dynamic) filters for articles and non-overlapping
> partitions. Is that a known limitation?
[quoted text clipped - 8 lines]
> >    Cheers,
> >                Paul Ibison SQL Server MVP, www.replicationanswers.com
Paul Ibison - 11 Mar 2007 19:45 GMT
OK - I'll try to repro tomorrow. Just so I can do it exactly the same way as
you are, the changes on the subscriber which don't get uploaded - are they
'standard' changes or are they changes which make a record change
partitions?
   Cheers,
               Paul Ibison SQL Server MVP, www.replicationanswers.com
Oskar - 11 Mar 2007 22:22 GMT
No, there are no out-of-partition rows. Few more details about the setup I
have:

- filtering is done by "fake" HOST_NAME();
- 2 push subscriptions on MS SQL Server 2005 Express machines
(9.00.2047.00), 1 publisher on MS SQL Server 2005 Standard (9.00.1399.06);
- rows are only inserted;
- 2 MS Active Directory users in Users group: one for snapshot and the other
for all merge agents, both sysadmins on the publisher and one of them
sysadmin on the subscriber;
- non-overlapping partitions;
- tables are either dropped & recreated or kept unchanged during
initialization (I've tried both of these options);
- automatic identity range management;
- subscriptions never expire;
- other publication options more or less at their defaults;

I did an experiment. Stop a merge agent, add some data on a subscriber,
start the merge agent, all added data appears at the publisher. Then I did
another one. Stop a merge agent, add some data on a subscriber, reinitialize
the subscription with the "upload_first" option and generate a new snapshot,
start the merge agent. Depeneding on the "pre_creation_command" publication
option, which in my case was either "drop" or "none", added data is lost or
retained at the subscriber but doesn't appear at the publisher.

Thanks for taking a close look at this.

-- Oskar

> OK - I'll try to repro tomorrow. Just so I can do it exactly the same way as
> you are, the changes on the subscriber which don't get uploaded - are they
> 'standard' changes or are they changes which make a record change
> partitions?
>     Cheers,
>                 Paul Ibison SQL Server MVP, www.replicationanswers.com
Oskar - 31 Mar 2007 18:18 GMT
Paul,
Seems that I've solved the problem by installing SP2 to the publisher
machine. Thanks for your effort.

-- Oskar

> No, there are no out-of-partition rows. Few more details about the setup I
> have:
[quoted text clipped - 31 lines]
> >     Cheers,
> >                 Paul Ibison SQL Server MVP, www.replicationanswers.com
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.