Merge replication problem

I have one corporate server that acts as both publisher and distributor.
Remote locations all have SQL server loaded and act as subscribers. This
setup has worked great until yesterday. Somehow, and it is still under
investigation, the corporate server "lost" all subscriptions and became
disconnected from the remote locations.
Each location is on a 5 minute schedule for the merge agent to run.
After recreating all the subscriptions and "pushing" them down to the remote
locations, there has been information lost during the down time. The
snapshot agent had to be run in order to recreate the subscriptions. I am
trying to figure out how I could have reconfigured the setup so that when
the subscription was made all the data in the remote locations would have
been preserved and "Merged" with the data from the server.
Thank you in advance for you input.
WB
Should this situation happen again I suggest:
1. Prevent further updates on remote stations until replication is re-setup
OR choose a time for the resync that least interferes with operations - the
longer you leave it the worse your problem becomes
2. Remove publication(s) and subscriptions
3. Use a tool like Red Gates "Data Compare" to see what data the publisher
is missing - it will generate the sql inserts for you, this sql may need to
be manually tweaked
4. Apply update scripts
5. Recreate publication(s) and subscriptions
6. Find the person who caused the problem and apply thumbscrews
Jim.
"WB" wrote:

> I have one corporate server that acts as both publisher and distributor.
> Remote locations all have SQL server loaded and act as subscribers. This
> setup has worked great until yesterday. Somehow, and it is still under
> investigation, the corporate server "lost" all subscriptions and became
> disconnected from the remote locations.
> Each location is on a 5 minute schedule for the merge agent to run.
> After recreating all the subscriptions and "pushing" them down to the remote
> locations, there has been information lost during the down time. The
> snapshot agent had to be run in order to recreate the subscriptions. I am
> trying to figure out how I could have reconfigured the setup so that when
> the subscription was made all the data in the remote locations would have
> been preserved and "Merged" with the data from the server.
> Thank you in advance for you input.
> WB
>
>
|||I don't know how successful I will be at applying the thumbscrews to myself,
as it appears to have happened on my watch. It appears that while trying to
create a new remote server (from the remote location) and then send down the
snapshot and merged replication data, that the SQL server at the main office
removed the subscriptions of all the remote locations. Not sure how the
subscriptions were removed or why SQL thought it needed to do that, but I
may never know....
I will definitely look into the data compare tool; that will probably come
in handy in the future.
"Jim Breffni" <JimBreffni@.discussions.microsoft.com> wrote in message
news:C3E8DA76-F717-4353-8E0B-FACDA058F3DB@.microsoft.com...
> Should this situation happen again I suggest:
> 1. Prevent further updates on remote stations until replication is
re-setup
> OR choose a time for the resync that least interferes with operations -
the
> longer you leave it the worse your problem becomes
> 2. Remove publication(s) and subscriptions
> 3. Use a tool like Red Gates "Data Compare" to see what data the
publisher
> is missing - it will generate the sql inserts for you, this sql may need
to[vbcol=seagreen]
> be manually tweaked
> 4. Apply update scripts
> 5. Recreate publication(s) and subscriptions
> 6. Find the person who caused the problem and apply thumbscrews
>
> Jim.
>
> "WB" wrote:
This[vbcol=seagreen]
remote[vbcol=seagreen]
am[vbcol=seagreen]
when[vbcol=seagreen]
have[vbcol=seagreen]

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
>