Any problems when table isn't recreated during initialization?

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.
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
|||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.
"Paul Ibison" wrote:

> 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
>
>
|||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
|||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.
"Paul Ibison" wrote:

> 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
>
>
>
|||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
|||Yes, I have parameterized (dynamic) filters for articles and non-overlapping
partitions. Is that a known limitation?
-- Thanks, Oskar
"Paul Ibison" wrote:

> 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
>
>
|||By the way I didn't reinitialize the subscription because of adding,
dropping, or changing a parameterized filter in case you suspected that.
"Oskar" wrote:
[vbcol=seagreen]
> Yes, I have parameterized (dynamic) filters for articles and non-overlapping
> partitions. Is that a known limitation?
> -- Thanks, Oskar
> "Paul Ibison" wrote:
|||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
|||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
"Paul Ibison" wrote:

> 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
>
>

ANSI's stance on SmallInt vs. Int

I decided to push this out to it's own thread. Here's the question, if you a
re
deciding to abide purely by ANSI SQL 92 standards and are only thinking in t
erms
of the logical model, how does the modeler decide when to use a SmallInt vs.
an
Integer if the allowed range is considered a physical implementation?
A secondary question relates to boolean values. Is the official ANSI solutio
n to
storing boolean values (0 or 1) to use either a full blown Int (of course, s
ince
we are only talking about ANSI, we have no idea how big that is ;-> ) or a
Char(1)?
ThomasANSI / ISO SQL doesn't define absolute numeric precision so the
decisions on which datatype to use can only be made in the context of
an actual implementation. Those physical details are just outside the
scope of the standard. Does it matter?
SQL99 defines a Boolean datatype but SQL Server doesn't support it.
David Portas
SQL Server MVP
--|||> ANSI / ISO SQL doesn't define absolute numeric precision so the
> decisions on which datatype to use can only be made in the context of
> an actual implementation. Those physical details are just outside the
> scope of the standard. Does it matter?
By specifying the concept of a SmallInt but providing no information that wo
uld
help the modeler choose one over the other, it sounds like the ANSI team mad
e an
outright error in including SmallInt in the first place. Said another way, s
ince
designers are already required to accommodate the given DBMS for things like
the
range of values for a SmallInt, where is the problem in using DBMS specific
features like TinyInt? After all, if portability is the holy grail of standa
rds
use, it is already the case that porting a database from one DBMS to another
might break because one vendor used 2-bytes for their SmallInt while another
used 1-byte for their SmallInt. Thus, in terms of data types certainly, it m
akes
sense to use things like TinyInt to accomplish you goal.
In case you were wondering, I bring this up becuase of a comment Mr. Celko m
ade
about not using SQL's TinyInt datatype.

> SQL99 defines a Boolean datatype but SQL Server doesn't support it.
Is that true of SQL 2005 as well?
Thomas