Showing posts with label client. Show all posts
Showing posts with label client. Show all posts

Wednesday, March 28, 2012

Merge Replication Without Merging Client To Server

I want to SYNC the data on the server to the client, but never the client data to the server.

Is such a configuration possible with Merge Replication under SQL CE?

Thanks again!

Merge is designed to be bidirectional but RDA is intended for scenarios like yours.

Remote Data Access (RDA) with tracking turned OFF on your SQL Mobile tables is the way to solve this. You already have the IIS infrastructure in place since you're doing merge today. RDA pull the data to SQL Mobile on some schedule that supports the way your mobile users operate.

-Darren

|||

If you are talking about SQL Mobile 3.0 and SQL Server 9.0. YES :)

Choose the article type as "Download Only". This way you always get changes from SQL Server down to SQL Mobile. However, your changes in SQL Mobile will not be sent to SQL Server.

Useful MSDN URL: http://msdn2.microsoft.com/en-us/library/ms147295.aspx

Thanks,

LaxmI NRO, MSFT, SQL Mobile, Microsoft Corporation

Monday, March 26, 2012

Merge Replication Synchronization Manager

Hi,
after upgrading a client within a Merger Replication scenario from MSDE2000A
to SQL Express SP1 the subscription is no longer listed within the
synchronisation manager.
How to register the subscription within Synchronization Manager?
(I know it is possible within Management Studio but I need to automate this
process).
Thanks in advance,
Thomas
have you tried to pull it again in WSM?
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
"ThoHot00" <ThoHot00@.discussions.microsoft.com> wrote in message
news:F36273CF-38BA-4A7C-A373-CB96CCDE37E4@.microsoft.com...
> Hi,
> after upgrading a client within a Merger Replication scenario from
> MSDE2000A
> to SQL Express SP1 the subscription is no longer listed within the
> synchronisation manager.
> How to register the subscription within Synchronization Manager?
> (I know it is possible within Management Studio but I need to automate
> this
> process).
> Thanks in advance,
> Thomas
|||Hi Hilary,
I haven't tried to pull it again within WSM. I would need to reregister it
within WSM, but since this upgrade occurs within a software upgrade I do not
have access to all client machines and I need a solution that I can integrate
into an installer package.
The subscription registration can still be found under
HKLM\Software\Microsoft\Microsoft SQL Server\80\Replication\Subscriptions\...
If I copy this entry to HKLM\Software\Microsoft\Microsoft SQL
Server\90\Replication\Subscriptions\... the subscription shows up in WSM and
works fine. I'm a little bit worried about the fact that within this key is
entry called subid. When I regenerate this entry using "Management Studio"
the subid is different and there is one additional entry called WebSync.
WebSync is always 0 in our case but I don't have a clue where the changed
subid comes from. If I call sp_helpmergepullsubscription it still shows the
"old" subid.
Greetings,
Thomas
"Hilary Cotter" wrote:

> have you tried to pull it again in WSM?
> --
> 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
> "ThoHot00" <ThoHot00@.discussions.microsoft.com> wrote in message
> news:F36273CF-38BA-4A7C-A373-CB96CCDE37E4@.microsoft.com...
>
>

Friday, March 23, 2012

Merge replication real-time monitoring

Hi,
So far I used merge ActiveX control to start and monitor pull merge
subscription within C# client application. Due to fact that some subscriber
machines are rather slow and publication is relatively complex (lots of join
filters etc.), I want to offload processing to distributor. Some subscribers
often report "The process is running and is waiting for a response from one of
the backend connections".
Since I cannot control offloading with ActiveX (because it starts process on
local subscriber, or I am missing something here?), I would like to
use -Offload, start agent using msdb..sp_start_job on subscriber and monitor
replication progress by directly reading distributor's
distribution..MSmerge_history.
My questions are:
- is there better approach to accomplish same goal (e.g. .NET Remoting?)
- how to get "percentage complete" (agent outputs it if -OutputVerboseLevel 2
is used, but how to get it from subscriber?)
Links with more detailed info on replication would also be nice :-)
Thanks,
Tom
for percentage complete have a look at the status event.
The replication ActiveX controls do not support the full range of properties
that the SQL DMO replication objects support. IIRC the offloadagent
parameter is ignored in the merge ActiveX control.
"Tom" <tomislav.hrvoic.rimuv.dis@.inet.hr> wrote in message
news:%23ouVBHUJEHA.2660@.TK2MSFTNGP09.phx.gbl...
> Hi,
> So far I used merge ActiveX control to start and monitor pull merge
> subscription within C# client application. Due to fact that some
subscriber
> machines are rather slow and publication is relatively complex (lots of
join
> filters etc.), I want to offload processing to distributor. Some
subscribers
> often report "The process is running and is waiting for a response from
one of
> the backend connections".
> Since I cannot control offloading with ActiveX (because it starts process
on
> local subscriber, or I am missing something here?), I would like to
> use -Offload, start agent using msdb..sp_start_job on subscriber and
monitor
> replication progress by directly reading distributor's
> distribution..MSmerge_history.
> My questions are:
> - is there better approach to accomplish same goal (e.g. .NET Remoting?)
> - how to get "percentage complete" (agent outputs it
if -OutputVerboseLevel 2
> is used, but how to get it from subscriber?)
> Links with more detailed info on replication would also be nice :-)
> Thanks,
> Tom
>
sql

Monday, March 19, 2012

Merge replication over VPN

I currently have a client who needs to replicate a database between a server
in the office and a computer at home using a VPN connection.
Here is the currnet setup:
-Merge Replication
-Both the server and the client have SQLSERVERAgent logged in with the same
username and password for pass-through authentication
-Subscriber impersonates SqlServerAgent
-Anonymous subscription
The snapshot has been created successfully on the server and I am able to
register the client on the server's Enterprise Manager (so they can see each
other). The replication starts and runs for quite a while (most of the work
is done). Eventually, it fails with the message
//<servername>/<sharename>/<filname>.dri could not be propagated to the
subscriber. With no further details. I don't think it is security related,
because most of the work is already done.
Any ideas what I'm missing?
You might try, from the client, to see if you can browse this share. When I
have set it up, it required an entry in the HOSTS file to map the IP address
to the NETBIOS name.
HTH,
Paul Ibison
|||The share is visible. I fixed that with an entry in the HOSTS file in the
beginning.
"Paul Ibison" wrote:

> You might try, from the client, to see if you can browse this share. When I
> have set it up, it required an entry in the HOSTS file to map the IP address
> to the NETBIOS name.
> HTH,
> Paul Ibison
>
|||OK, if it is running, transferring the snapshot and fails after a while, you
might try increating the -QueryTimeout parameter of the merge agent's
profile.
HTH,
Paul Ibison
|||I did that already as well after having a timeout problem. The current
problem just says that the file could not be propagated to the subscriber.
"Paul Ibison" wrote:

> OK, if it is running, transferring the snapshot and fails after a while, you
> might try increating the -QueryTimeout parameter of the merge agent's
> profile.
> HTH,
> Paul Ibison
>
>
|||Problem Solved:
It turns out that there is no guarantee that the order that records are
inserted during replication will be the same as when they were inserted
originally. Therefore, it is possible that a chile record can be inserted
before its parent, causing a conflict with the foreign key constraint.
After setting all foreign key constraints to "not for replication", the
client took the snapshot without a problem.
"b1@.ckh013" wrote:

> I currently have a client who needs to replicate a database between a server
> in the office and a computer at home using a VPN connection.
> Here is the currnet setup:
> -Merge Replication
> -Both the server and the client have SQLSERVERAgent logged in with the same
> username and password for pass-through authentication
> -Subscriber impersonates SqlServerAgent
> -Anonymous subscription
> The snapshot has been created successfully on the server and I am able to
> register the client on the server's Enterprise Manager (so they can see each
> other). The replication starts and runs for quite a while (most of the work
> is done). Eventually, it fails with the message
> //<servername>/<sharename>/<filname>.dri could not be propagated to the
> subscriber. With no further details. I don't think it is security related,
> because most of the work is already done.
> Any ideas what I'm missing?

Merge replication on internet

How can we achive a SQL server merge replication over the internet when both the client and server are Windows Xp machines and using SQL Server 7? Can someone please explain in a clear step by step fashion?
Is this a static IP or not.
Basically what you have to do it configure the publisher and subscriber using client network utilty, in the alias tab. Use the current TCP/IP address and a plain text server name (ie, don't use the ip address as the alias).
Then in your hosts file ( found in cd %windir%\system32\etc edit the hosts file to include both these servernames and the ip addresses. If the addresses are dynamic you will have to modify this each time you get a new ip address.
Then you have to configure an ftp server on your publisher and configure your merge publication for ftp.
Then create a pull subscription using ftp on your subscriber.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Rammy" wrote:

> How can we achive a SQL server merge replication over the internet when both the client and server are Windows Xp machines and using SQL Server 7? Can someone please explain in a clear step by step fashion?
>
|||One more thing. When you configure your subscriber, do not use the impersonate the SQL Server Agent on the publisher, use SQL Server authentication.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" wrote:
[vbcol=seagreen]
> Is this a static IP or not.
> Basically what you have to do it configure the publisher and subscriber using client network utilty, in the alias tab. Use the current TCP/IP address and a plain text server name (ie, don't use the ip address as the alias).
> Then in your hosts file ( found in cd %windir%\system32\etc edit the hosts file to include both these servernames and the ip addresses. If the addresses are dynamic you will have to modify this each time you get a new ip address.
> Then you have to configure an ftp server on your publisher and configure your merge publication for ftp.
> Then create a pull subscription using ftp on your subscriber.
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "Rammy" wrote:

Monday, March 12, 2012

merge replication mess with identity fields

Our application makes extensive use of Identity fields as primary/foreign
keys. One client decided to use merge replication between two sites. They
brought in a consultant who set it up for them. The guy didn't know what
identity fields were. He thought our application filled them in from some
table he couldn't find.
When records turned up missing, the client called us back. Finding that new
records on both ends were getting the same identity, then SQL Server was
keeping the record on one side & throwing out the one of the same number on
the other side, we had them stop editing on the subscriber side until we got
the thing corrected. I thought if all edits were done on the publisher, it
could keep pushing the changes down to the subscriber without causing
problems.
Records are still getting deleted by replication, so the other consultant
has turn it off.
My job is to fix the mess. Identity ranges using NOT FOR REPLICATION are
the answer. But how do I get to that point from where I am? ALTER TABLE
won't work on these tables that are set up to be replicated.
Thanks.
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company
I don't think that the NFR switch will be the best option for you.
I think you need different seeds on both sides, ie on the publisher has a
seed of 1 and in increment of 2. On the subscriber have a seed of 2 and an
increment of 2.
Check out this link for more info on how to set the NFR property on identity
columns, however I think you are best to rip everything out and start again.
sp configure 'allow updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <>
0 and
colstat & 0x0008 =0
go
sp configure 'allow updates', 0
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:ObsGdn0yEHA.344@.TK2MSFTNGP10.phx.gbl...
> Our application makes extensive use of Identity fields as primary/foreign
> keys. One client decided to use merge replication between two sites. They
> brought in a consultant who set it up for them. The guy didn't know what
> identity fields were. He thought our application filled them in from some
> table he couldn't find.
> When records turned up missing, the client called us back. Finding that
> new
> records on both ends were getting the same identity, then SQL Server was
> keeping the record on one side & throwing out the one of the same number
> on
> the other side, we had them stop editing on the subscriber side until we
> got
> the thing corrected. I thought if all edits were done on the publisher, it
> could keep pushing the changes down to the subscriber without causing
> problems.
> Records are still getting deleted by replication, so the other consultant
> has turn it off.
> My job is to fix the mess. Identity ranges using NOT FOR REPLICATION are
> the answer. But how do I get to that point from where I am? ALTER TABLE
> won't work on these tables that are set up to be replicated.
> Thanks.
> --
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> http://www.Embtrak.com
> DVBrown Company
>
|||Different seeds sound like a good solution. Since I expect to have 2
subscribers, I think I Iike an increment of 3.
So ... how do I get to that? Even if I delete the existing publication, how
do I get different seeds on the subscriber(s) than on the publisher? The
default action when the initial snapshot is taken is to DROP and recreate
the table ... obviously according to the same schema as that on the
publisher. Am I even looking in the right area?
Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uIz8dn1yEHA.3908@.TK2MSFTNGP12.phx.gbl...
> I don't think that the NFR switch will be the best option for you.
> I think you need different seeds on both sides, ie on the publisher has a
> seed of 1 and in increment of 2. On the subscriber have a seed of 2 and an
> increment of 2.
> Check out this link for more info on how to set the NFR property on
identity
> columns, however I think you are best to rip everything out and start
again.[vbcol=seagreen]
> sp configure 'allow updates', 1
> go
> reconfigure with override
> go
> update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <>
> 0 and
> colstat & 0x0008 =0
> go
> sp configure 'allow updates', 0
>
>
> "Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
> news:ObsGdn0yEHA.344@.TK2MSFTNGP10.phx.gbl...
primary/foreign[vbcol=seagreen]
They[vbcol=seagreen]
what[vbcol=seagreen]
some[vbcol=seagreen]
it[vbcol=seagreen]
consultant[vbcol=seagreen]
are[vbcol=seagreen]
TABLE
>
|||Create the tables on the publisher the way you want them. Then do the same
on the subscriber. Then sync them up. One option is to use snapshot
replication making sure you use the article property keep the existing table
intact and delete all the data.
When you have finished this, manually change all the identity fields for the
seeds and increments you want. Then do a no sync subscription.
Don't use an increment of 3 as it will overlap with the identity values on
the publisher. Here is an example
Publisher Susbcriber
1 3 - will collide
2 6 - will collide
3
4
5
6
7
You could also try to back up the publication database, and then restore it
on the subscriber. Make the changes to the identity increments/seeds and
then do the nosync subscription.
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:u3Jyn81yEHA.1412@.tk2msftngp13.phx.gbl...
> Different seeds sound like a good solution. Since I expect to have 2
> subscribers, I think I Iike an increment of 3.
> So ... how do I get to that? Even if I delete the existing publication,
> how
> do I get different seeds on the subscriber(s) than on the publisher? The
> default action when the initial snapshot is taken is to DROP and recreate
> the table ... obviously according to the same schema as that on the
> publisher. Am I even looking in the right area?
>
> Thanks.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uIz8dn1yEHA.3908@.TK2MSFTNGP12.phx.gbl...
> identity
> again.
> primary/foreign
> They
> what
> some
> it
> consultant
> are
> TABLE
>
|||I think it's possible to do it with increment 3, of course if it is set not
only on subscriber but also on publisher. For example setup like that:
Publisher: Seed = 1 Increment = 3
Publisher identity values: 1, 4, 7, 10, 13...(1+n*3)
Subscriber1: Seed = 2 Increment = 3
Subscriber1 identity values: 2, 5, 8, 11, 14...(2+n*3)
Subscriber2: Seed = 3 Increment = 3
Subscriber2 identity values: 3, 6, 9, 12, 15...(3+n*3)
I agree with Hillary that probably the best option is to configure publisher
database (set NFR flags on identity columns, seed 1, increment 3), backup
it, compress, transfer to subscribers locations, restore, change identity
columns seed to 2 and 3, initialize replication with "no sync"
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u9s4cD2yEHA.2040@.tk2msftngp13.phx.gbl...
> Create the tables on the publisher the way you want them. Then do the same
> on the subscriber. Then sync them up. One option is to use snapshot
> replication making sure you use the article property keep the existing
table
> intact and delete all the data.
> When you have finished this, manually change all the identity fields for
the
> seeds and increments you want. Then do a no sync subscription.
> Don't use an increment of 3 as it will overlap with the identity values on
> the publisher. Here is an example
> Publisher Susbcriber
> 1 3 - will collide
> 2 6 - will collide
> 3
> 4
> 5
> 6
> 7
> You could also try to back up the publication database, and then restore
it[vbcol=seagreen]
> on the subscriber. Make the changes to the identity increments/seeds and
> then do the nosync subscription.
> "Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
> news:u3Jyn81yEHA.1412@.tk2msftngp13.phx.gbl...
recreate[vbcol=seagreen]
a[vbcol=seagreen]
that[vbcol=seagreen]
publisher,
>
|||This is an excellent solution again Kestutis, but are there 2 or 3 sites? I
thought there were only 2, a publisher and a single subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> wrote in message
news:uDVOQ%236yEHA.1412@.tk2msftngp13.phx.gbl...
> I think it's possible to do it with increment 3, of course if it is set
not
> only on subscriber but also on publisher. For example setup like that:
> Publisher: Seed = 1 Increment = 3
> Publisher identity values: 1, 4, 7, 10, 13...(1+n*3)
> Subscriber1: Seed = 2 Increment = 3
> Subscriber1 identity values: 2, 5, 8, 11, 14...(2+n*3)
> Subscriber2: Seed = 3 Increment = 3
> Subscriber2 identity values: 3, 6, 9, 12, 15...(3+n*3)
> I agree with Hillary that probably the best option is to configure
publisher[vbcol=seagreen]
> database (set NFR flags on identity columns, seed 1, increment 3), backup
> it, compress, transfer to subscribers locations, restore, change identity
> columns seed to 2 and 3, initialize replication with "no sync"
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u9s4cD2yEHA.2040@.tk2msftngp13.phx.gbl...
same[vbcol=seagreen]
> table
> the
on[vbcol=seagreen]
> it
and[vbcol=seagreen]
publication,[vbcol=seagreen]
The[vbcol=seagreen]
> recreate
has[vbcol=seagreen]
> a
and[vbcol=seagreen]
<>[vbcol=seagreen]
sites.[vbcol=seagreen]
know[vbcol=seagreen]
from[vbcol=seagreen]
> that
Server[vbcol=seagreen]
until[vbcol=seagreen]
> publisher,
causing[vbcol=seagreen]
REPLICATION
>
|||Hi , even I am facing the same problem . I have implemented below soluion of
set up of seeds.
Can u pls tell me how to set up replication with nosync . I know there is
one option of when we initiaze the subscription i.e when subscriber has its
own schema and data . But when i choose this option following error come in
merge agent : "Invalid column name 'rowguidcol'."
Pls help
Regards,
Swati
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> wrote in message
news:uDVOQ#6yEHA.1412@.tk2msftngp13.phx.gbl...
> I think it's possible to do it with increment 3, of course if it is set
not
> only on subscriber but also on publisher. For example setup like that:
> Publisher: Seed = 1 Increment = 3
> Publisher identity values: 1, 4, 7, 10, 13...(1+n*3)
> Subscriber1: Seed = 2 Increment = 3
> Subscriber1 identity values: 2, 5, 8, 11, 14...(2+n*3)
> Subscriber2: Seed = 3 Increment = 3
> Subscriber2 identity values: 3, 6, 9, 12, 15...(3+n*3)
> I agree with Hillary that probably the best option is to configure
publisher[vbcol=seagreen]
> database (set NFR flags on identity columns, seed 1, increment 3), backup
> it, compress, transfer to subscribers locations, restore, change identity
> columns seed to 2 and 3, initialize replication with "no sync"
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u9s4cD2yEHA.2040@.tk2msftngp13.phx.gbl...
same[vbcol=seagreen]
> table
> the
on[vbcol=seagreen]
> it
and[vbcol=seagreen]
publication,[vbcol=seagreen]
The[vbcol=seagreen]
> recreate
has[vbcol=seagreen]
> a
and[vbcol=seagreen]
<>[vbcol=seagreen]
sites.[vbcol=seagreen]
know[vbcol=seagreen]
from[vbcol=seagreen]
> that
Server[vbcol=seagreen]
until[vbcol=seagreen]
> publisher,
causing[vbcol=seagreen]
REPLICATION
>
|||Daniel Wilson wrote:
> Different seeds sound like a good solution. Since I expect to have 2
> subscribers, I think I Iike an increment of 3.
Daniel mentioned 2 subscribers, if I understood correctly from following
sentence.
So this approach of different identity seeds on every "end-point" of
replication topology and identity increment equal to number of "end-points"
of replication is OK for that case. The problems will come if some more
subscribers will have to be added.. Then all system will have to be
reinitialized with the increased number of "replication end-points".
Regards,
Kestas
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:etRzhI9yEHA.336@.TK2MSFTNGP10.phx.gbl...
> This is an excellent solution again Kestutis, but are there 2 or 3 sites?
I
> thought there were only 2, a publisher and a single subscriber.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
|||Hi , Do we have to explicitily restore the whole database . I have tried
import/export option . Rowguidcol and it's property is same for both
subscriber and publisher , so ideally it should not give this error . Pls
suggest .
Now I am trying option 1 : restoration of subscrition database.
Regards,
Swati
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> wrote in message
news:O0uYaY9yEHA.3844@.TK2MSFTNGP12.phx.gbl...
> When you create merge publication, ROWGUID column is automatically added
to
> all published tables (if there is no column with property ROWGUIDCOL). If
> your subscriber database is restored from a backup of "publisher DB before
> creation of merge publication", then these columns will be missing on
> subscriber DB. You have to make sure that those columns are there - do one
> of the following:
> 1. Backup publisher DB after creation of publication and then restore it
as[vbcol=seagreen]
> a subscriber
> or
> 2. Add ROWGUID columns to all replicated tables on subscriber (make sure
> that this column has all the same properties as on subscriber DB)
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:efTqeK9yEHA.1192@.tk2msftngp13.phx.gbl...
soluion[vbcol=seagreen]
> of
is[vbcol=seagreen]
> its
> in
set[vbcol=seagreen]
> backup
> identity
the[vbcol=seagreen]
existing[vbcol=seagreen]
> for
> values
> restore
have[vbcol=seagreen]
> 2
publisher?[vbcol=seagreen]
the[vbcol=seagreen]
you.[vbcol=seagreen]
publisher[vbcol=seagreen]
2[vbcol=seagreen]
on[vbcol=seagreen]
> start
0x0001[vbcol=seagreen]
> Finding
same
> ALTER
>
|||I'm not totally convinced, and I really hate to quibble with you as your
answers though infrequent are spectacular!
However, if more subscribers are coming into the mix Daniel will have to
implement some form of automatic identity range management.
This would be best deployed by configuring the publication for automatic
identity range management and then deploying the snapshot to each subscriber
on an ad hoq basis.
Granted if there is a known number of subscribers for the lifetime of the
project such a manual partitioning solution such as the one you have
recommended.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> wrote in message
news:%23bRhYb9yEHA.3836@.TK2MSFTNGP12.phx.gbl...
> Daniel Wilson wrote:
> Daniel mentioned 2 subscribers, if I understood correctly from following
> sentence.
> So this approach of different identity seeds on every "end-point" of
> replication topology and identity increment equal to number of
"end-points"[vbcol=seagreen]
> of replication is OK for that case. The problems will come if some more
> subscribers will have to be added.. Then all system will have to be
> reinitialized with the increased number of "replication end-points".
> --
> Regards,
> Kestas
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:etRzhI9yEHA.336@.TK2MSFTNGP10.phx.gbl...
sites?
> I
>

Merge Replication issue .. Please help (Replication does not work)

How is the network relationship set up? If this is a non-
trusted relationship, you'll need to register an alias in
the client network utility on the subscriber, and place
an entry in the hosts file there also.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Paul,
Thank you for your reply.
This is a non-trusted relationship.I registered a client network utility
with the alias pointing to my publisher/ distributor and added an entry in
the host file also.
But still I 'm getting this same message in the job history:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
What is wrong am I doing?
Sam
"Paul Ibison" wrote:

> How is the network relationship set up? If this is a non-
> trusted relationship, you'll need to register an alias in
> the client network utility on the subscriber, and place
> an entry in the hosts file there also.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Also I tried to push the subscription instead of pullin git from subscriber.
But in push subscription , the merge agent throws this error:
The remote server is not defined as a subscription server.
How do I set up the remote server as subscription server?
The merge agent does transfers lot of data and objects from publisher to
subscriber , it is sometime at the end of the process , it fails.
Please advice..
Sam
"Paul Ibison" wrote:

> How is the network relationship set up? If this is a non-
> trusted relationship, you'll need to register an alias in
> the client network utility on the subscriber, and place
> an entry in the hosts file there also.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Sam,
have you set up FTP for the initialization? Have a look at this article:
http://support.microsoft.com/?id=321822
Also, check that the correct port has been opened up on the firewall (port
1433 for default instance of SQL Server, by default).
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Friday, March 9, 2012

Merge Replication From Behind Corporate Firewall

I would like to replicate data between a central hosted server (which I
host) and a client server (hosted by my client). The client server is
behind a corporate firewall.
My understanding is that in order to perform Merge Replication both
servers would need to be able to see each other on port 1433, which
means both servers need to have a fixed IP address open to the accept
incoming/outgoing traffic from their respective IP addresses.

>From speaking with the client it's become apparent that they are not
going to provide a fixed IP address accessible to my server. Is there
any other way of doing this ie. replicate data from the client to my
hosted server?
BTW - the reason I'm doing this is that I have Reporting Services on my
hosted server so the idea was that the client could replicate data up
to my server, I maintain it on an ongoing basis and provide reports
through reporting services.
Any suggestions/ideas ?
Thanks for the help !
If your publisher has a fixed IP address this will work. You can pull the
snapshot using FTP, so they will have to open that port.
On your subscriber you will have to create an alias using client network
utility for the publisher using the fixed IP address.
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
<ciaran@.insero-technologies.com> wrote in message
news:1108055889.511697.180130@.o13g2000cwo.googlegr oups.com...
> I would like to replicate data between a central hosted server (which I
> host) and a client server (hosted by my client). The client server is
> behind a corporate firewall.
> My understanding is that in order to perform Merge Replication both
> servers would need to be able to see each other on port 1433, which
> means both servers need to have a fixed IP address open to the accept
> incoming/outgoing traffic from their respective IP addresses.
> going to provide a fixed IP address accessible to my server. Is there
> any other way of doing this ie. replicate data from the client to my
> hosted server?
> BTW - the reason I'm doing this is that I have Reporting Services on my
> hosted server so the idea was that the client could replicate data up
> to my server, I maintain it on an ongoing basis and provide reports
> through reporting services.
> Any suggestions/ideas ?
> Thanks for the help !
>
|||Hi Hilary,
Thanks for the response !
I've looked into pulling the snapshot using FTP, and while the BOL give
a good overview of setting this up from the Publication side, there is
very little covered from the subscriber side (any ideas on
references?).
I did set up the alias for my Publisher using a fixed IP address, but I
can't see where I would reference this setting up my subscription.
A Confused ..
Ciaran ;o)
Hilary Cotter wrote:
> If your publisher has a fixed IP address this will work. You can pull
the
> snapshot using FTP, so they will have to open that port.
> On your subscriber you will have to create an alias using client
network[vbcol=seagreen]
> utility for the publisher using the fixed IP address.
> --
> 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
> <ciaran@.insero-technologies.com> wrote in message
> news:1108055889.511697.180130@.o13g2000cwo.googlegr oups.com...
(which I[vbcol=seagreen]
is[vbcol=seagreen]
accept[vbcol=seagreen]
not[vbcol=seagreen]
there[vbcol=seagreen]
my[vbcol=seagreen]
on my[vbcol=seagreen]
up[vbcol=seagreen]
|||Once you setup the subscription you just have to change the "Snapshot file
location" to FTP in the subscription properties.
Jos.
<ciaran@.insero-technologies.com> wrote in message
news:1108066869.083530.26340@.z14g2000cwz.googlegro ups.com...
> Hi Hilary,
> Thanks for the response !
> I've looked into pulling the snapshot using FTP, and while the BOL give
> a good overview of setting this up from the Publication side, there is
> very little covered from the subscriber side (any ideas on
> references?).
> I did set up the alias for my Publisher using a fixed IP address, but I
> can't see where I would reference this setting up my subscription.
> A Confused ..
> Ciaran ;o)
> Hilary Cotter wrote:
> the
> network
> (which I
> is
> accept
> not
> there
> my
> on my
> up
>

Wednesday, March 7, 2012

merge replication error - could not find stored procedure ''.

Hi
I have a client which has a merge subscription - anonymous ftp
When synchronising we see the following error message at the publisher:
Error Message:
The merge process could not retrieve column information for table
'dbo.Transfer'.
(Source: Merge Replication Provider (Agent); Error number: -2147201016)
------
Could not find stored procedure ''.
(Source: LOUISEPC (Data source); Error number: 2812)
------
What does this mean ?
Thanks
Bruce
does this help?
http://support.microsoft.com/default...b;en-us;308743
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
"Bruce Baker" <bruceb@.ardex.com.au> wrote in message
news:%23DHMBbVrFHA.1168@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have a client which has a merge subscription - anonymous ftp
> When synchronising we see the following error message at the publisher:
> Error Message:
> The merge process could not retrieve column information for table
> 'dbo.Transfer'.
> (Source: Merge Replication Provider (Agent); Error number: -2147201016)
> ----
--
> Could not find stored procedure ''.
> (Source: LOUISEPC (Data source); Error number: 2812)
> ----
--
>
> What does this mean ?
> Thanks
> Bruce
>
>
|||Not really - it was working - for some reason the procs for the table are
missing from the sysmergearticles table.
Any bright ideas on repopulating that table ? Wonder what else is bung....
Thanks
Bruce
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%233tiAZprFHA.1252@.TK2MSFTNGP09.phx.gbl...
> does this help?
> http://support.microsoft.com/default...b;en-us;308743
> --
> 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
> "Bruce Baker" <bruceb@.ardex.com.au> wrote in message
> news:%23DHMBbVrFHA.1168@.TK2MSFTNGP10.phx.gbl...
> --
> --
>
|||You can manually populate these tables with the correct procs. It will be
somewhat difficult to figure out what these procs names should be. You will
have to open these procs to find out which underlying tables they populate
and go from there.
You are best in the long run to drop the subscribers, and then re deploy
them.
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
"Bruce Baker" <bruceb@.newsgroups.nospam> wrote in message
news:OiBu4NurFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Not really - it was working - for some reason the procs for the table are
> missing from the sysmergearticles table.
> Any bright ideas on repopulating that table ? Wonder what else is
bung....[vbcol=seagreen]
> Thanks
> Bruce
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%233tiAZprFHA.1252@.TK2MSFTNGP09.phx.gbl...
-[vbcol=seagreen]
-
>

Merge Replication Error - A dynamic snapshot is required

Any info on this error:
Scenario:
Replication Server: Sql Server 2005
Replication client: SQL Server Express Edition
I have setup a merge publication on SQL Server 2005, with a dynamic
filter (using HOST_NAME()) defined on one of the articles, also I have
defined a logical record.
(To be precise it is a logical record without join filter, I have done
this using 'sp_addmergefilter' and specifying the @.filter_type = 2
value which configures it just as logical record without a join filter)
Subscription (am using push) to this Publication is successful, but
when I see Replication Monitor, I get the following error there:
A dynamic snapshot is required because the publication 'XYZ' contains
both dynamic filters and logical record relationships.
How do i configure dynamic snapshot? Any info this error would be great.
I tried the following stored procedure to add a dynamic snapshot:
exec sp_adddynamicsnapshot_job
@.publication = 'XYZ',
@.suser_sname = NULL,
@.host_name = sysname,
@.frequency_type= 2
but am still getting the same error "A dynamic snapshot is required
because the publication 'XYZ' contains
both dynamic filters and logical record relationships. "
On Jan 25, 4:25 pm, narasimha...@.gmail.com wrote:
> Any info on this error:
> Scenario:ReplicationServer: Sql Server 2005Replicationclient: SQL Server Express Edition
> I have setup amergepublication on SQL Server 2005, with a dynamic
> filter (using HOST_NAME()) defined on one of the articles, also I have
> defined a logical record.
> (To be precise it is a logical record without join filter, I have done
> this using 'sp_addmergefilter' and specifying the @.filter_type = 2
> value which configures it just as logical record without a join filter)
> Subscription (am using push) to this Publication is successful, but
> when I seeReplicationMonitor, I get the following error there:
> Adynamic snapshotis required because the publication 'XYZ' contains
> both dynamic filters and logical record relationships.
> How do i configuredynamic snapshot? Any info this error would be great.

Saturday, February 25, 2012

Merge Replication and SQL 2005, help!

I'm working on developing a software solution using Mobile Client Software Factory. Let me start off by saying that this package is such a dream come true, and I can't thank the guys who put this together enough. The problem is, we're having some trouble getting the components setup for the Framework to work, in particular with SQL Server 2005.

We have SQL Server 2005 running on a machine, and we have our entire database setup what I believe to be correctly. Last night, we tried for hours to get the PPC Emulator running our version of the Framework to connect to the SQL Server (On another machine on the same LAN) to no avail.

We published just one table to test with, we have the snapshot created, the web service is running, and yet it won't let us connect. The most common error we're getting from the webservice log is: Hr=80004005 ERR:OpenDB failed getting pub version 28627.

I was just wondering if someone out there had a guide for correctly setting up SQL Server 2005 for merge replication so that remote clients can access the server through the webservice over the internet. If there is anything specifically special that needs to be done to work with the Mobile Client Software Factory, that would be great to know too.

I'd really appreciate some help, and thanks in advance to anyone who can lend some advice.

Cheers!

Andrew

I don't know what Mobile Client Software Factory is, but if you have merge subscriptions on a disconnected network, but has access to the internet, you can use merge web synchronization, which is synchronizing over the internet. There's more information in books online, search for replication web synchronization.

Monday, February 20, 2012

Merge Replication and Dynamic filters

Hi,

I would like to setup a merge replication from a main database (publisher), to around 100 client databases (subscribers), that contain only subset information for each distinct client: each client has then its own database to view and modify its own data (Filtered on rows and columns). The client databases will initially be hosted on the same SQL server Instance.

I don't want to setup manually 100 publications with static filters, and more may come in the future: a pain to setup and maintain because I have to configure the tables, the columns and the row filters (and joins) each time.

I would like then to setup one publication with a dynamic filter, to filter in a way or another on a specific client. Creating subscribers becomes then a piece of cake. The dynamic filter would apply based on a property specific to the client, but... HOW?

Filtering on HOST_NAME() will not work because several subscribers are on the same server.

Filtering on SUSER_SNAME() will not work because merge agent will always use the same user name for connecting to the publisher (using push subcription, all merge agents are on the same server), and I have not find out how it can be configured by merge agent: even if the merge agent jobs have different owner, it is always the SQL Server Agent login that is used to connect to the publisher (I am using windows authentication).

I was thinking about using DB_NAME(), and have specific db name for each client DB, but DB_NAME() provides the name of the publisher DB, not the subscriber DB. etc ...

What could I use in this case to dynamically filter on client data without having to fall into heavy replication administration and setup.

Thanks for your help,

Best regardsThink about using pull subscriptions - you'll have more flexibility, including accounts for every agent.|||I have found the way to filter on each subscriber by forcing a different host name for each subscriber:
In the job that launch the merge agent, I add a parameter -HostName [CustomizedName]
My publisher filters then on a different host name for each subscriber.

Merge replication and dynamic filters

Hi,
I would like to setup a merge replication from a main database (publisher), to around 100 client databases (subscribers), that contain only subset information for each distinct client: each client has then its own database to view and modify its own data (Filtered on rows and columns). The client databases will initially be hosted on the same SQL server Instance.
I don't want to setup manually 100 publications with static filters, and more may come in the future: a pain to setup and maintain because I have to configure the tables, the columns and the row filters (and joins) each time.
I would like then to setup one publication with a dynamic filter, to filter in a way or another on a specific client. Creating subscribers becomes then a piece of cake. The dynamic filter would apply based on a property specific to the client, but... HOW?
Filtering on HOST_NAME() will not work because several subscribers are on the same server.
Filtering on SUSER_SNAME() will not work because merge agent will always use the same user name for connecting to the publisher (using push subcription, all merge agents are on the same server), and I have not find out how it can be configured by merge agent: even if the merge agent jobs have different owner, it is always the SQL Server Agent login that is used to connect to the publisher (I am using windows authentication).
I was thinking about using DB_NAME(), and have specific db name for each client DB, but DB_NAME() provides the name of the publisher DB, not the subscriber DB. etc ...
What could I use in this case to dynamically filter on client data without having to fall into heavy replication administration and setup.
Thanks for your help,
Best regards

Quote:

Originally posted by jj.lequeux
Hi,
I would like to setup a merge replication from a main database (publisher), to around 100 client databases (subscribers), that contain only subset information for each distinct client: each client has then its own database to view and modify its own data (Filtered on rows and columns). The client databases will initially be hosted on the same SQL server Instance.
I don't want to setup manually 100 publications with static filters, and more may come in the future: a pain to setup and maintain because I have to configure the tables, the columns and the row filters (and joins) each time.
I would like then to setup one publication with a dynamic filter, to filter in a way or another on a specific client. Creating subscribers becomes then a piece of cake. The dynamic filter would apply based on a property specific to the client, but... HOW?
Filtering on HOST_NAME() will not work because several subscribers are on the same server.
Filtering on SUSER_SNAME() will not work because merge agent will always use the same user name for connecting to the publisher (using push subcription, all merge agents are on the same server), and I have not find out how it can be configured by merge agent: even if the merge agent jobs have different owner, it is always the SQL Server Agent login that is used to connect to the publisher (I am using windows authentication).
I was thinking about using DB_NAME(), and have specific db name for each client DB, but DB_NAME() provides the name of the publisher DB, not the subscriber DB. etc ...
What could I use in this case to dynamically filter on client data without having to fall into heavy replication administration and setup.
Thanks for your help,
Best regards

I have found the way to filter on each subscriber by forcing a different host name for each subscriber:
In the job that launch the merge agent, I add a parameter -HostName [CustomizedName]
My publisher filters then on a different host name for each subscriber.