Showing posts with label merge. Show all posts
Showing posts with label merge. Show all posts

Friday, March 30, 2012

No-Sync Replication and Future Schema changes

I have the responsibility of setting up SQL2000 (merge and
transactional) replication of a 30GB database between 3 physical
locations (within 100 miles), over 256KB pipes.
I have done this with no-sync before (backup publisher to USB HD and
restore at subscriber), but the downside of that is that future table
schema changes (using sp_repladdcolumn etc) will not propagate to the
subscriber.
Question: Is there a way that I can setup no-sync replication in such
a way that I can also propagate future schema changes "over the wire"?
Not with merge replication. With transactional replication it is possible to
trick the publisher into thinking that it is dealing with a "automatic"
subscriber.
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
"Steve Campbell" <dukeytoo@.gmail.com> wrote in message
news:1183993715.504073.91200@.c77g2000hse.googlegro ups.com...
>I have the responsibility of setting up SQL2000 (merge and
> transactional) replication of a 30GB database between 3 physical
> locations (within 100 miles), over 256KB pipes.
> I have done this with no-sync before (backup publisher to USB HD and
> restore at subscriber), but the downside of that is that future table
> schema changes (using sp_repladdcolumn etc) will not propagate to the
> subscriber.
> Question: Is there a way that I can setup no-sync replication in such
> a way that I can also propagate future schema changes "over the wire"?
>
|||On Jul 9, 11:31 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Not with merge replication. With transactional replication it is possible to
> trick the publisher into thinking that it is dealing with a "automatic"
> subscriber.
Any idea how?
|||Hi Hilary,
I too am interested in knowing the answer to this question (see my recent
thread in this NG title Add additional Subscribers) using a similar approach
(backup existing Subscriber DB, and restore it to a new Subscriber using
nosync) but it appears that would require the future schema changes to be
applied to the subscriber(s) through scripting (sp_addsubscription), and no
longer possible through the Publication Properties, Articles tab ?
I'm using Transactional under SQL 2000 SP4, 1 Publisher/Distributor and 3
PUSH Subscribers; and I need to ADD 2 more PUSH Subscribers to the topology
in the same well connected environment.
If the answer is nestled away in your book somewhere, I do own a copy...just
point me in the right direction.
Many thanks !
Regards,
Frank
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u%23PkYakwHHA.600@.TK2MSFTNGP05.phx.gbl...
> Not with merge replication. With transactional replication it is possible
> to trick the publisher into thinking that it is dealing with a "automatic"
> subscriber.
> --
> 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
> "Steve Campbell" <dukeytoo@.gmail.com> wrote in message
> news:1183993715.504073.91200@.c77g2000hse.googlegro ups.com...
>
|||This is how I do it.
select * from syssubscriptions where sync_type=2--locate your no sync
subscription.
update syssubscriptions set sync_type=1 where dest_db='nosyncdatabase'
sp_refreshsubscriptions 'your publication name'
Put it back when you are done.
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
"Steve Campbell" <dukeytoo@.gmail.com> wrote in message
news:1184005859.811318.34520@.n60g2000hse.googlegro ups.com...
> On Jul 9, 11:31 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Any idea how?
>
sql

Tuesday, March 20, 2012

Nonconvergence issue after upgrading MSDE to SQL 2005 Express

I'm experiencing an intermittent issue with merge replication after
upgrading one of the subscribers to SQL 2005 Express from MSDE 2000.
Here are a few details about the environment before I describe the
issue
1. Originally was SQL 2000 Standard Publisher with MSDE 2000
Subscribers all SP4
2. Upgraded (in place) Publisher to SQL 2005 Standard SP1 - no issues
3. Upgraded 1 of 3 subscribers to SQL 2005 Express SP1 encountered the
following issue only on the upgraded Express installation
4. Merge replication is running continuously as a pull subscription
with column-level tracking
5. Replication is running as a scheduled task on the subscriber (pull)
by executing replmerg.exe
Issue:
Background: A contacts table has addresses stored in a separate table
with the address_id in the contacts table referencing the address_id
in the addresses table
1. Contact created on subscriber without an address (null address_id)
2. Replication runs (contact uploaded to publisher)
3. Contact updated at subscriber to have an address (address_id
populated)
4. Replication uploads changes to publisher
4a. The address is uploaded to the publisher
4b. The address_id update to the contact record appears to be uploaded
to the publisher (confirmed by running SQL profiler)
4c. After the replication session is complete the address_id update to
the contact record appears to have not been made or is rolled back
5. Replication downloads changes to the subscriber and determines that
the address is not part of the partition for the subscriber (because
the address_id is not present in the contact record) and removes it
from the subscriber (the address is still present on publisher)
If I were to bet on a theory about why the address_id update to the
contact record is getting lost I would put my money on a deadlock (or
similar issue) and rollback but I would be interested to hear if
anyone has run into similar issues. I'd also be interested to know why
SQL 2005 Express would behave differently when replicating than MSDE
2000 (this setup has been running for almost 2 years on the MSDE with
no issues). If it is a deadlock/rollback then does the MSDE subscriber
handle the rollback differently than the Express subscriber? Is there
a re-try step that is not running in Express that does run in MSDE?
Have you logged to an output file or run profiler to determine exactly what
is happening?
If it is a deadlock the merge agent is transactional in nature and will put
the error in a retry loop at the end of each batch.
So this doesn't really explain what you are seeing.
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
"Brian Fell" <brianfell@.gmail.com> wrote in message
news:1182807685.623799.115710@.u2g2000hsc.googlegro ups.com...
> I'm experiencing an intermittent issue with merge replication after
> upgrading one of the subscribers to SQL 2005 Express from MSDE 2000.
> Here are a few details about the environment before I describe the
> issue
> 1. Originally was SQL 2000 Standard Publisher with MSDE 2000
> Subscribers all SP4
> 2. Upgraded (in place) Publisher to SQL 2005 Standard SP1 - no issues
> 3. Upgraded 1 of 3 subscribers to SQL 2005 Express SP1 encountered the
> following issue only on the upgraded Express installation
> 4. Merge replication is running continuously as a pull subscription
> with column-level tracking
> 5. Replication is running as a scheduled task on the subscriber (pull)
> by executing replmerg.exe
>
> Issue:
> Background: A contacts table has addresses stored in a separate table
> with the address_id in the contacts table referencing the address_id
> in the addresses table
> 1. Contact created on subscriber without an address (null address_id)
> 2. Replication runs (contact uploaded to publisher)
> 3. Contact updated at subscriber to have an address (address_id
> populated)
> 4. Replication uploads changes to publisher
> 4a. The address is uploaded to the publisher
> 4b. The address_id update to the contact record appears to be uploaded
> to the publisher (confirmed by running SQL profiler)
> 4c. After the replication session is complete the address_id update to
> the contact record appears to have not been made or is rolled back
> 5. Replication downloads changes to the subscriber and determines that
> the address is not part of the partition for the subscriber (because
> the address_id is not present in the contact record) and removes it
> from the subscriber (the address is still present on publisher)
>
> If I were to bet on a theory about why the address_id update to the
> contact record is getting lost I would put my money on a deadlock (or
> similar issue) and rollback but I would be interested to hear if
> anyone has run into similar issues. I'd also be interested to know why
> SQL 2005 Express would behave differently when replicating than MSDE
> 2000 (this setup has been running for almost 2 years on the MSDE with
> no issues). If it is a deadlock/rollback then does the MSDE subscriber
> handle the rollback differently than the Express subscriber? Is there
> a re-try step that is not running in Express that does run in MSDE?
>
|||On Jun 25, 10:06 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Have you logged to an output file or run profiler to determine exactly what
> is happening?
> If it is a deadlock the merge agent is transactional in nature and will put
> the error in a retry loop at the end of each batch.
> So this doesn't really explain what you are seeing.
> --
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com"Brian Fell" <brianf...@.gmail.com> wrote in message
> news:1182807685.623799.115710@.u2g2000hsc.googlegro ups.com...
>
>
>
>
> - Show quoted text -
I ran profiler and saw no evidence of any problem however I do see the
update with the address_id in it (and an entry that indicated that
replication thought the upload was successful (Uploaded 2 change(s) in
''contacts'' (1 insert, 1 update) - one of those for the address entry
as well). Unfortunately, it took me three weeks to catch this thing
with profiler and I didn't have the replication logging to a file when
I started it up that day (was switching back and forth between
watching the console and reviewing a log). Any ideas about something
to look at would be greatly appreciated.
Thanks,
Brian Fell
|||You will probably have to try to repro the problem using the specific set of
commands you outline in your original post.
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
"Brian Fell" <brianfell@.gmail.com> wrote in message
news:1182824624.031896.140300@.o61g2000hsh.googlegr oups.com...
> On Jun 25, 10:06 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> I ran profiler and saw no evidence of any problem however I do see the
> update with the address_id in it (and an entry that indicated that
> replication thought the upload was successful (Uploaded 2 change(s) in
> ''contacts'' (1 insert, 1 update) - one of those for the address entry
> as well). Unfortunately, it took me three weeks to catch this thing
> with profiler and I didn't have the replication logging to a file when
> I started it up that day (was switching back and forth between
> watching the console and reviewing a log). Any ideas about something
> to look at would be greatly appreciated.
> Thanks,
> Brian Fell
>

non-conflict errors

in merge replication using sql server 2005 and sql server compact, what happens when rows or columns cannot be inserted/updated/deleted because of some error other than a conflict? Are they handled differently tan change conflicts?

thanks,

bryan

The rows are retried and are still logged into the conflict table.|||

what happens to the conflict table? does it just stay on the server for logging purposes? does it go back to the client? if so, what does the client do with it? is the client notified that entries are in the conflict table? can it be queried programmically and the conflict fixed and resubmitted?

thanks,

bryan

Monday, March 12, 2012

Non standard port - non default instance

Hi all,
I'm trying to create a subscription for a merge replication on a sql
server mobile 2005 based application.
The publisher is on a intranet, listening on port 9091 and with a named
instance.
The merge agent is in a DMZ with port 9091 open for inbound/outbound TCP
traffic to the intranet.
Problem:
when I try to synchronize I receive the "SQL Server does not exists" error.
My subscription has the following setup:
Publisher = "ServerName\InstanceName";
PublisherNetwork = NetworkType.TcpIpSockets;
PublisherAddress = "10.xxx.xxx.xxx,9091";
PublisherDatabase = "DatabaseName";
PublisherSecurityMode = SecurityType.DBAuthentication;
PublisherLogin = "name";
PublisherPassword = "pwd";
name/pwd user has correct priviledges non publisher database.
I can connect via SQL Client to the database from the machine in DMZ.
In my (many) tries, I also created an alias on the DMZ client and used
that for the Publisher property at the subscriber.
Nothing worked.
I'm finishing options...
Cl@. ha scritto:

> Problem:
> when I try to synchronize I receive the "SQL Server does not exists" error.
I solved opening 1434 udp port.
Question is: why sql client can connect without 1434 port open and the
merge agent can't?

Monday, February 20, 2012

No tables in merge replication

Hi, I have setup a publication that my program on my CE device will
synchronize to. It connects and downloads the database fine, and the database
file shows that data has been downloaded. But when I try to run SQL
statements against the database, it says that the tables are not there. I
also used the Query Analyzer that is installed on the machine when using
vs.net, and it does not show any tables, either. I was wondering if there was
something that I was doing wrong when setting up the publication.
Thanks
Daniel Robinson
This is highly abnormal. Can you possibly post your code here?
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
"Daniel Robinson" <DanielRobinson@.discussions.microsoft.com> wrote in
message news:72C63D6C-6F68-416E-AC16-8BE0A8B94CD9@.microsoft.com...
> Hi, I have setup a publication that my program on my CE device will
> synchronize to. It connects and downloads the database fine, and the
database
> file shows that data has been downloaded. But when I try to run SQL
> statements against the database, it says that the tables are not there. I
> also used the Query Analyzer that is installed on the machine when using
> vs.net, and it does not show any tables, either. I was wondering if there
was
> something that I was doing wrong when setting up the publication.
> Thanks
> Daniel Robinson
|||I don't think it's a problem with my code. We had a publication setup for
another database, and that was working. All I did in the code was change the
publication name.
Daniel
"Hilary Cotter" wrote:

> This is highly abnormal. Can you possibly post your code here?
> --
> 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
> "Daniel Robinson" <DanielRobinson@.discussions.microsoft.com> wrote in
> message news:72C63D6C-6F68-416E-AC16-8BE0A8B94CD9@.microsoft.com...
> database
> was
>
>
|||Here is the SQL that I used to create the publication:
EXEC sp_replicationdboption @.dbname='daniel', @.optname='merge publish',
@.value='true'
EXEC sp_addmergepublication @.publication='uadd_AMSSchool_Daniel',
@.allow_pull='true', @.allow_push='false',
@.allow_anonymous='true', @.enabled_for_internet='true',
@.snapshot_in_defaultfolder='false', @.alt_snapshot_folder='C:\SqlPublication',
@.compress_snapshot='false', @.sync_mode='character', @.retention=60,
@.conflict_retention=60, @.allow_synctoalternate='true',
@.keep_partition_changes='true'
EXEC sp_addpublication_snapshot @.publication='uadd_AMSSchool_Daniel',
@.frequency_type=8, @.frequency_interval=1,
@.frequency_subday=1, @.frequency_subday_interval=1,
@.frequency_recurrence_factor=1
EXEC sp_grant_publication_access @.publication='uadd_AMSSchool_Daniel',
@.login='sa'
EXEC sp_addmergearticle @.publication='uadd_AMSSchool_Daniel',
@.article='mytable', @.source_object='mytable', @.source_owner='dbo',
@.type='table', @.destination_owner='dbo', @.column_tracking='true',
@.force_invalidate_snapshot=1, @.vertical_partition='true'
EXEC sp_mergearticlecolumn @.publication='uadd_AMSSchool_Daniel',
@.article='mytable', @.column='myname', @.operation='add',
@.schema_replication='true', @.force_invalidate_snapshot=1,
@.force_reinit_subscription=1
EXEC sp_mergearticlecolumn @.publication='uadd_AMSSchool_Daniel',
@.article='mytable', @.column='myint', @.operation='add',
@.schema_replication='true', @.force_invalidate_snapshot=1,
@.force_reinit_subscription=1
I do get a warning that only Subscribers running SQL Server 2000 can
synchronize with the publication...and I am running sqlCE. You think this
might be causing a problem?
Thanks
Daniel
"Hilary Cotter" wrote:

> This is highly abnormal. Can you possibly post your code here?
> --
> 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
> "Daniel Robinson" <DanielRobinson@.discussions.microsoft.com> wrote in
> message news:72C63D6C-6F68-416E-AC16-8BE0A8B94CD9@.microsoft.com...
> database
> was
>
>