Merge replication when new tables are created regularly

Hi all,
I am trying to replicate a database (sql server 2000) to a remote site. I
use merge replication and do almost continous replication.
My problem is I have one application which creates atleast 6 or 7 new tables
a day. Everytime it create a tables, snapshot agent restarts again and this
makes the entire server slow. Also when doing this snapshot agent fails most
often!
How can I get around this issue. Anybody with insight to this issue, plz
help me..
Regards,
Maani
The problem with the snapshot agent on merge publications is that it
snapshots the entire publication, even when only one article is added, and
there isn't an option of a concurrent snapshot unlike transactional. This is
probably causing the snapshot errors you are getting. It's not always very
practical, but you could potentially add the new tables to a new
publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Sounds to me like a design problem in the application. At that rate, you
would be adding approx. 2,500 tables a year! I'd hate to administer that
database.
David
"Maani" <Maani@.discussions.microsoft.com> wrote in message
news:709E0B34-622A-4E17-9996-5DCBC45D3C93@.microsoft.com...
> Hi all,
> I am trying to replicate a database (sql server 2000) to a remote site. I
> use merge replication and do almost continous replication.
> My problem is I have one application which creates atleast 6 or 7 new
> tables
> a day. Everytime it create a tables, snapshot agent restarts again and
> this
> makes the entire server slow. Also when doing this snapshot agent fails
> most
> often!
> How can I get around this issue. Anybody with insight to this issue, plz
> help me..
> Regards,
> Maani
|||So.. What should I do?
create a new publication periodically and and all newly created tables
should be added on to newest publication? How can i do that then? Somebody
plz help me with the scripts please as I am not professional DBA... :-)
"David" wrote:

> Sounds to me like a design problem in the application. At that rate, you
> would be adding approx. 2,500 tables a year! I'd hate to administer that
> database.
> David
> "Maani" <Maani@.discussions.microsoft.com> wrote in message
> news:709E0B34-622A-4E17-9996-5DCBC45D3C93@.microsoft.com...
>
>

Merge Replication w/ Web Synchronization Across Non-Trusted Domain

I have a requirement to replicate a portion of a 2005 database using merge
replication where the database server is in a workgroup at location A and the
web server is in an AD domain at location B. Both locations are connected
via a VPN.
Becuase of the disparate domains we are unable to push snapshots to a share
on the Web Server w/o using FTP. After specifying the FTP information in the
FTP Snapshot and Internet dialog, the following message is returned when
attempting to start the Snapshot Agent:
Message: The replication agent failed to create the directory
'\\172.27.1.187\unc\ftp\DAYMONJPSV02$TEST_CORE_APP RISCORE1\20071218021362\'.
Stack: at
Microsoft.SqlServer.Replication.Utilities.CreateDi rectoryWithExtendedErrorInformation(String directory)
at
Microsoft.SqlServer.Replication.Snapshot.SnapshotP rovider.CreateSnapshotFolders()
at
Microsoft.SqlServer.Replication.Snapshot.MergeSnap shotProvider.CreateSnapshotFolders()
at
Microsoft.SqlServer.Replication.Snapshot.SqlServer SnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGeneration Agent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQL_REPL,
Error number: MSSQL_REPL52026)
Get help: http://help/MSSQL_REPL52026
Source: mscorlib
Target Site: Void WinIOError(Int32, System.String)
Message: Message: Logon failure: unknown user name or bad password.
Stack: at System.IO.__Error.WinIOError(Int32 errorCode, String
maybeFullPath)
at System.IO.Directory.InternalCreateDirectory(String fullPath, String
path, DirectorySecurity dirSecurity)
at System.IO.Directory.CreateDirectory(String path, DirectorySecurity
directorySecurity)
at
Microsoft.SqlServer.Replication.Utilities.CreateDi rectoryWithExtendedErrorInformation(String directory) (Source: mscorlib, Error number: 0)
The user id and password are those of a domain user for the FTP server at
location B. Do I have to use a non-AD account?
You need to use a snapshot account which has rights to modify to
\\172.27.1.187\unc. You specify this account in sp_addpublication_snapshot
using the @.job_login and @.job_password parameters.
This account should exist on \\172.27.1.187 and your publisher.
http://www.zetainteractive.com - Shift Happens!
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
"parchk" <parchk@.discussions.microsoft.com> wrote in message
news:7575E064-365A-49B4-B479-AD3C7D4E14C8@.microsoft.com...
>I have a requirement to replicate a portion of a 2005 database using merge
> replication where the database server is in a workgroup at location A and
> the
> web server is in an AD domain at location B. Both locations are connected
> via a VPN.
> Becuase of the disparate domains we are unable to push snapshots to a
> share
> on the Web Server w/o using FTP. After specifying the FTP information in
> the
> FTP Snapshot and Internet dialog, the following message is returned when
> attempting to start the Snapshot Agent:
> Message: The replication agent failed to create the directory
> '\\172.27.1.187\unc\ftp\DAYMONJPSV02$TEST_CORE_APP RISCORE1\20071218021362\'.
> Stack: at
> Microsoft.SqlServer.Replication.Utilities.CreateDi rectoryWithExtendedErrorInformation(String
> directory)
> at
> Microsoft.SqlServer.Replication.Snapshot.SnapshotP rovider.CreateSnapshotFolders()
> at
> Microsoft.SqlServer.Replication.Snapshot.MergeSnap shotProvider.CreateSnapshotFolders()
> at
> Microsoft.SqlServer.Replication.Snapshot.SqlServer SnapshotProvider.GenerateSnapshot()
> at Microsoft.SqlServer.Replication.SnapshotGeneration Agent.InternalRun()
> at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQL_REPL,
> Error number: MSSQL_REPL52026)
> Get help: http://help/MSSQL_REPL52026
> Source: mscorlib
> Target Site: Void WinIOError(Int32, System.String)
> Message: Message: Logon failure: unknown user name or bad password.
> Stack: at System.IO.__Error.WinIOError(Int32 errorCode, String
> maybeFullPath)
> at System.IO.Directory.InternalCreateDirectory(String fullPath, String
> path, DirectorySecurity dirSecurity)
> at System.IO.Directory.CreateDirectory(String path, DirectorySecurity
> directorySecurity)
> at
> Microsoft.SqlServer.Replication.Utilities.CreateDi rectoryWithExtendedErrorInformation(String
> directory) (Source: mscorlib, Error number: 0)
> The user id and password are those of a domain user for the FTP server at
> location B. Do I have to use a non-AD account?
|||Thanks Hillary. I am assuming that becasue the servers are in two different
security domains that the account should be local on both servers? Also, if
the publication has already been created, can it be modified to modify the
job_login and job_password parameters? Thanks in advance.
"Hilary Cotter" wrote:

> You need to use a snapshot account which has rights to modify to
> \\172.27.1.187\unc. You specify this account in sp_addpublication_snapshot
> using the @.job_login and @.job_password parameters.
> This account should exist on \\172.27.1.187 and your publisher.
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "parchk" <parchk@.discussions.microsoft.com> wrote in message
> news:7575E064-365A-49B4-B479-AD3C7D4E14C8@.microsoft.com...
>
>
|||Exactly, it should be a local account on both servers.
You can modify the snapshot account by right clicking on the publication in
SSMS, selecting properties and clicking on the agent security tab.
http://www.zetainteractive.com - Shift Happens!
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
"parchk" <parchk@.discussions.microsoft.com> wrote in message
news:915C3515-3E0A-4755-88B7-DE72D927A0E2@.microsoft.com...[vbcol=seagreen]
> Thanks Hillary. I am assuming that becasue the servers are in two
> different
> security domains that the account should be local on both servers? Also,
> if
> the publication has already been created, can it be modified to modify the
> job_login and job_password parameters? Thanks in advance.
> "Hilary Cotter" wrote:

Merge replication very very slow

Trying to merge replicate a database, but it is very slow.
At the subscriber it inform: "Uploading data changes to the Publisher"
and then stuck for very long time.
After that it informs several lines of: "Processing article
'TableName'" and fail with "General network error".
Publisher, distributor and subscriber: SQL 2K SP3.
I am using the replmerg.exe to replicate, so I am getting all logs on
screen anyway and it looks like it fail in different phases each time.
I already set QueryTimeout to 600 and inactivity threshold to 120.
Thanks

Merge replication subscription error with proxy

Ok, perhaps I'm a moron.. believe me, I'd be glad to admit it if I
could just get this fixed.
We're trying to merge replicate data via https, we have installed the
CA certificate and can connect to the replisapi.dll through IE. I used
the following script to generate the subscription:
DECLARE @.publication AS sysname;
DECLARE @.publisher AS sysname;
DECLARE @.publicationDB AS sysname;
DECLARE @.websyncurl AS sysname;
DECLARE @.login AS sysname;
DECLARE @.password AS nvarchar(512);
SET @.publication = N'tables';
SET @.publisher = N'server';
SET @.publicationDB = N'database';
SET @.websyncurl = 'https://webserver/WebSync';
SET @.login = N'websql\user';
Set @.password = N'pass';
USE [LocalDB]
EXEC sp_addmergepullsubscription
@.publisher = @.publisher,
@.publication = @.publication,
@.publisher_db = @.publicationDB,
@.subscriber_type = N'anonymous';
EXEC sp_addmergepullsubscription_agent
@.publisher = @.publisher,
@.publisher_db = @.publicationDB,
@.publication = @.publication,
@.distributor = @.publisher,
@.job_login = @.login,
@.job_password = @.password,
@.use_web_sync = 1,
@.internet_url = @.websyncurl,
@.internet_login = @.login,
@.internet_password = @.password,
@.enabled_for_syncmgr = 'true';
GO
This creates the subscription locally but gives me the error
"websql\user not a valid Windows account". I tried changing the
job_login & job_password to a local account and the subscription was
added without errors. When I go to start the syncronization I get the
error: "The system cannot find the file specified." The underlying
error here is with the proxy. For some reason when it tries to connect
it isn't passing the proper credentials or something.
Anyway, any I'd appreciate any help or direction. If I find out what's
wrong I'll go ahead and post it.
JC
That's because it is creating the agent at the subscriber and when doing so,
you are specifying an account of websql\user which does not exist on the
subscriber. If you change this to a local account and then try to
synchronize, you will get security errors, because the SID of the local
account you set it to does not have a valid scope outside of the local
machine. The job login and password can be a local account and it dosn't
affect anything. However, take a look at the other credential. You are
setting the synchronization context to websql\user and that account, since
it doesn't exist on the local machine can't generate the file which is used
to synch via https. (The more accurate error message would be user doesn't
exist or access denied.)
I don't have a system setup to be able to configure https synchronization
right now. I've done this before and your issue is with the security
context. I just can't remember what I did before to fix this particular
error.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
<jbzcooper@.gmail.com> wrote in message
news:1138315588.689076.326990@.g14g2000cwa.googlegr oups.com...
> Ok, perhaps I'm a moron.. believe me, I'd be glad to admit it if I
> could just get this fixed.
> We're trying to merge replicate data via https, we have installed the
> CA certificate and can connect to the replisapi.dll through IE. I used
> the following script to generate the subscription:
> DECLARE @.publication AS sysname;
> DECLARE @.publisher AS sysname;
> DECLARE @.publicationDB AS sysname;
> DECLARE @.websyncurl AS sysname;
> DECLARE @.login AS sysname;
> DECLARE @.password AS nvarchar(512);
> SET @.publication = N'tables';
> SET @.publisher = N'server';
> SET @.publicationDB = N'database';
> SET @.websyncurl = 'https://webserver/WebSync';
> SET @.login = N'websql\user';
> Set @.password = N'pass';
> USE [LocalDB]
> EXEC sp_addmergepullsubscription
> @.publisher = @.publisher,
> @.publication = @.publication,
> @.publisher_db = @.publicationDB,
> @.subscriber_type = N'anonymous';
> EXEC sp_addmergepullsubscription_agent
> @.publisher = @.publisher,
> @.publisher_db = @.publicationDB,
> @.publication = @.publication,
> @.distributor = @.publisher,
> @.job_login = @.login,
> @.job_password = @.password,
> @.use_web_sync = 1,
> @.internet_url = @.websyncurl,
> @.internet_login = @.login,
> @.internet_password = @.password,
> @.enabled_for_syncmgr = 'true';
> GO
> This creates the subscription locally but gives me the error
> "websql\user not a valid Windows account". I tried changing the
> job_login & job_password to a local account and the subscription was
> added without errors. When I go to start the syncronization I get the
> error: "The system cannot find the file specified." The underlying
> error here is with the proxy. For some reason when it tries to connect
> it isn't passing the proper credentials or something.
> Anyway, any I'd appreciate any help or direction. If I find out what's
> wrong I'll go ahead and post it.
> JC
>
|||Mike,
Appreciate the reply and you are correct. I split the credentials into
two, one for the machine and one for the web. This brought up a second
error which I pondered for some time until a colleage suggested I look
at the security permissions for the login. I tried several things but
this is what I was doing when I got it to work:
0. Ensure that windows account SQL run under has proper authority to
execute job
1. Log into windows using the windows account SQL is using
2. Log into SQL using Windows Authentication
3. Execute the above script (after adding a second set of credentials
for the job)
4. Run replication
Some of the above steps may not be necessary.. it just worked for me.
HTH someone else.. man, I was in a bad spot.
JC

Merge replication scenario - how to have inventory working properly

Hi,
I'm using merge replication to replicate the Customers, Orders,
OrderDetails and Stock tables from server A to server B.
Everythings works as expected except the stockage level for a product.
Think about this scenario:
1) Initially the stockage level of product 1 is 20 units.
2) Server A creates a new order with 5 units of product 1.
Stock table in server A now has 15 units for product 1.
3) Server B creates another order with 3 units of product 1.
Stock table in server B now has 17 units for product 1.
4) Synchronization takes place, and there is an update conflict in the
stock table for product 1. Server A wants to save 15 and server B wants
to save 17.
Either value is incorrect because the stockage level shoud be 12.
Is there any way to have this working as expected? I have thought of
creating a custom resolver, but I think there isn't a way to get the
stockage level after the previous synchronization in the conflict
handler, substract that value from the current stockage level, do the
same with the data from the other server and combine the values to get
the proper result.
Thanks a lot!
Manu,
you could have a table which shows initial stock (20). After that the
remaining stock is a view which is initial stock - sum of orders and in this
case there won't be any conflicts.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Inventory is defined as the number of units in stock - the number of units
sold. The application maintains inventory in server a.
When server a and server b sync orders will have to move up from server b to
server a. A trigger off the orderdetails table can fire and update the
inventory table on server a and keep it in sync, this trigger can be
designed to only fire on actions originating from server b.
Then the problem becomes keeping the inventory table in sync in both
locations. This can be done as a download only article, but it will be
updated with the next sync.
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
"Manu" <manunews@.gmail.com> wrote in message
news:1169553887.302039.76620@.m58g2000cwm.googlegro ups.com...
> Hi,
> I'm using merge replication to replicate the Customers, Orders,
> OrderDetails and Stock tables from server A to server B.
> Everythings works as expected except the stockage level for a product.
> Think about this scenario:
> 1) Initially the stockage level of product 1 is 20 units.
> 2) Server A creates a new order with 5 units of product 1.
> Stock table in server A now has 15 units for product 1.
> 3) Server B creates another order with 3 units of product 1.
> Stock table in server B now has 17 units for product 1.
> 4) Synchronization takes place, and there is an update conflict in the
> stock table for product 1. Server A wants to save 15 and server B wants
> to save 17.
> Either value is incorrect because the stockage level shoud be 12.
> Is there any way to have this working as expected? I have thought of
> creating a custom resolver, but I think there isn't a way to get the
> stockage level after the previous synchronization in the conflict
> handler, substract that value from the current stockage level, do the
> same with the data from the other server and combine the values to get
> the proper result.
> Thanks a lot!
>
|||Thanks for the help.
On Jan 23, 2:10 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:[vbcol=seagreen]
> Inventory is defined as the number of units in stock - the number of units
> sold. The application maintains inventory in server a.
> When server a and server b sync orders will have to move up from server b to
> server a. A trigger off the orderdetails table can fire and update the
> inventory table on server a and keep it in sync, this trigger can be
> designed to only fire on actions originating from server b.
> Then the problem becomes keeping the inventory table in sync in both
> locations. This can be done as a download only article, but it will be
> updated with the next sync.
> --
> Hilary Cotter
> 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
> "Manu" <manun...@.gmail.com> wrote in messagenews:1169553887.302039.76620@.m58g2000cwm.go oglegroups.com...
>
>
>
>
>
>

Merge Replication Replicate Master and related tables

hi all,

Could we configure a merge replication such as replicate the master table and its all related tables (relation deep could be 1.)

We dont want to manually find master table relations and configure replication for the related tables.

please help for that configuration

kinds

No but why would you want to?|||No, replication wizard will not find and define all the FK dependencies for you, you have to do this yourself.|||Thanks StatisticsIO.com and Greg Y for your valuable help. |||In repl UI, there is capability to create a dynamic filter on the parent table and then automatically generate the join filters based on PK-FK, do try it out in the publication wizard.sql

Merge Replication Questions [SQL2k5 non express]

I can choose synchronization direction for articles: a) Bidirectional b) one way

1) Is that possible somehow to replicate the schema only of an article but no synchronization / zero direction :-)/

2) Same question about columns, I should replicate schema only for few columns, but without data synch. These columns are freely updateable at anywhere (publisher and subscribers), but the data changes shouldn't be replicated.

Thanks for the answers in advance

I guess that you want to keep same schema's at two or more machines?

I do not know whether you can do it using replication, actually I think that there is no way to do something like that.

What I would do is that I would script database, and make same copies at all locations. Later when you need some updates/changes to schema, you can script those also. Not only that, but you can build your own schema replication system, so everything could go, kind of, semi-automatic.

|||

Sorry, my initial question was not clear.

I would like to replicate all tables in the db, except 1-2 tables and 3-4 columns only.

Let's see the following example:

There are about 30 tables to replicate let's name those T1, T2, T3, ... T30 and the column names are T1C1, T1C2, ... T2C1, T2C2, .... etc

I would like to replicate all tables, except T15 and T16 tables (all columns) and 4 columns T8C4, T8C5, T9C2 and T9C3. But the schema should be the same at all places, so T15 and T16 should be exist at subscribers and publishers and the mentioned columns also, but data should not be replicated to-from that 2 table and from/to that 4 columns.

|||

Now it is much clearer to me.

As I said, you can copy your schema to be same on all databases, but you can filter out your publication so you just replicate tables T1-T14 and T17-T30, and to replicate all columns except T8C4, T8C5, T9C2 and T9C3.

If you then choose to initialize, you will loose tables/columns that are not in replication, but you can add them later.

Or you can make publication, then copy db schema using script to subscriber (so you have rowguid) and choose do not initialize.

Test it, play around with it. Make on your own sql server two tiny db's with two tables (one publisher and one subscriber) and play with it.

|||

THis can be done with replication. What you do is create a publication containing all the tables and columns you want. You can then script out table T15 and T16, put it in a file, and reference it in parameter @.post_snapshot_script for stored procedure sp_addpublication or sp_addmergepublication.

|||

Thanks for the answer. It took a bit longer, because I ran into a little problem, I got the following error message on one of my stored procedure in post-snapshot script:

"The query processor could not produce a query plan. For more information, contact Customer Support Services."

It was because i forgot to include the:

set QUOTED_IDENTIFIER ON

maybe this is a bug of sql2k5 SP1

btw, I moved the T8C4, T8C5, T9C2 and T9C3 columns to separate tables as well, and there are foreign keys pointing back to the original tables PKs

Thanks again for the solution.

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 Issue. Update not happening

Hi,
We have set up merge replication between a few boxes. I make changes on one
box, replicate, and check the tables on the other box. The inserts are being
replicated, and some updates.
It appears that the updates made out of a stored proc are not being
replicated. I can manually update the table, and it will be replicated. But
when updated from the stored proc, it is not coming accrossed.
Any ideas?
Steve
More info.
sp3, no conflicts reported, after further testing, it seems one column on
one table never gets updated. Other columns that didn't, did at other times.
It is of type bit. I can manually update it and replicate, but can't have
the stored procedure update it and replicate.
"SteveInBeloit" wrote:

> Hi,
> We have set up merge replication between a few boxes. I make changes on one
> box, replicate, and check the tables on the other box. The inserts are being
> replicated, and some updates.
> It appears that the updates made out of a stored proc are not being
> replicated. I can manually update the table, and it will be replicated. But
> when updated from the stored proc, it is not coming accrossed.
> Any ideas?
> Steve
>
|||Just went to sp4, that did not help. When I do a "validate" after the
replicate, the checksum for that table fails.
"SteveInBeloit" wrote:
[vbcol=seagreen]
> More info.
> sp3, no conflicts reported, after further testing, it seems one column on
> one table never gets updated. Other columns that didn't, did at other times.
> It is of type bit. I can manually update it and replicate, but can't have
> the stored procedure update it and replicate.
>
> "SteveInBeloit" wrote:
|||More info - it is a Pull initiated at the subscriber. If I run the same
application on the Publisher, and make the updates, then replicate, the
change gets to the subscriber, just not the other way around.
"SteveInBeloit" wrote:
[vbcol=seagreen]
> Just went to sp4, that did not help. When I do a "validate" after the
> replicate, the checksum for that table fails.
> "SteveInBeloit" wrote:
|||Solved:
It was actually a delete and an insert, not an update. A record had been
written to a ##TempTable. Processing was done on that record. Then the GUID
key of that row was used to delete the row out of the real table, then and
INSERT from the temp to the real table to put it back. On that insert, I
used the same GUID key instead of letting the table NEWID get a new one. It
confused replication, having known there was a DELETE and and INSERT, but the
GUIDs the same.
"SteveInBeloit" wrote:
[vbcol=seagreen]
> More info - it is a Pull initiated at the subscriber. If I run the same
> application on the Publisher, and make the updates, then replicate, the
> change gets to the subscriber, just not the other way around.
> "SteveInBeloit" wrote:

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
>

Merge Replication Error

In Merge Replication, I am not able to replicate one table having more than 5 GB data to the subscriber. If I have filter on the table I am able to do. I couldn't understand the problem? Any one having any idea on the same?

For your information.

I have 1.7GB free space in C drive. Is it a space problem in C drive? Actually my data base is storing in E drive. It has 30GB free space. But Sql Server installed in D drive it has 2 GB free space.

Thanks in advance

Kasi.

What is the error youre getting? Are you getting failure generating snapshot, or are you getting failure when applying the snapshot to the subscriber?

|||

Error Message:

Replication-Replication Merge Subsystem: agent TRPSQL3-ThomsonResearch-TR PUb-TRPSQL2-7 failed. The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history log

I have got the above error message

|||it's possible the merge agent is timing out before the jobis completed, you can try increasing the QUeryTImeout paramter value for the agent to something larger, like 600 or 900. 5 GB is a lot of data.

Merge Replication Error

In Merge Replication, I am not able to replicate one table having more than 5 GB data to the subscriber. If I have filter on the table I am able to do. I couldn't understand the problem? Any one having any idea on the same?

For your information.

I have 1.7GB free space in C drive. Is it a space problem in C drive? Actually my data base is storing in E drive. It has 30GB free space. But Sql Server installed in D drive it has 2 GB free space.

Thanks in advance

Kasi.

What is the error youre getting? Are you getting failure generating snapshot, or are you getting failure when applying the snapshot to the subscriber?

|||

Error Message:

Replication-Replication Merge Subsystem: agent TRPSQL3-ThomsonResearch-TR PUb-TRPSQL2-7 failed. The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history log

I have got the above error message

|||it's possible the merge agent is timing out before the jobis completed, you can try increasing the QUeryTImeout paramter value for the agent to something larger, like 600 or 900. 5 GB is a lot of data.

Merge Replication and Deletes

Hi there,
I have four databases set up with merge replication. I have a filter that
says only replicate a record if its timestamp is greater than one day ago (I
don't care about any data older than one day).
If I now go into Database #1 and delete a record that is older than one day,
I do not expect to see this record deleted from the other databases (in
other words, I expect that it would remain in the others). However, all the
deletes for records older than one day ARE being replicated to the other
databases. I'd really prefer that this didn't happen.
Does anyone know why this occurs?
Thanks,
Kevin
Because you told it to. You said that your filter says to only replicate if
the timestamp is greater than one day ago, and it's timestamp is greater than
1 day ago.
If you want it to not be replicated, then update the timestamp to the
current date first and then delete.
Robert.
"Kevin Thomas" wrote:

> Hi there,
> I have four databases set up with merge replication. I have a filter that
> says only replicate a record if its timestamp is greater than one day ago (I
> don't care about any data older than one day).
> If I now go into Database #1 and delete a record that is older than one day,
> I do not expect to see this record deleted from the other databases (in
> other words, I expect that it would remain in the others). However, all the
> deletes for records older than one day ARE being replicated to the other
> databases. I'd really prefer that this didn't happen.
> Does anyone know why this occurs?
> Thanks,
> Kevin
>
>
|||Perhaps I misspoke, I apologize. I only want to replicate data that is less
than a day old. I want to delete data that is greater than a day old. When
I delete the data that is older than one day, those deletes are replicated,
even though I don't expect that they would because of the filter.
Thanks for your help,
Kevin
"Robert Davis" <RobertDavis@.discussions.microsoft.com> wrote in message
news:E1D36A03-CF46-4EAB-BE05-31D048FDC59B@.microsoft.com...[vbcol=seagreen]
> Because you told it to. You said that your filter says to only replicate
> if
> the timestamp is greater than one day ago, and it's timestamp is greater
> than
> 1 day ago.
> If you want it to not be replicated, then update the timestamp to the
> current date first and then delete.
> Robert.
> "Kevin Thomas" wrote:

Merge Replication across the internet

Hi,
I require to replicate data 2 ways across the internet.
I think merge replication would be the best way to do this.
How secure is it across the internet ?
Is there anyway to create a file out of differences to be applied to
another database.
I am using SQL Server 2000
Srini,
yes this is possible with a few treaks.
To make it completely secure, most people use a VPN.
File-based replication is not possible.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Merge replication 2005 - 2005 Express

Hi.
I want to replicate SQL 2005 - SQL 2005 Express. MS SQL 2005 (DevEdition
beta 2) is configured as Publisher/Distributor. I want ti configure SQL 2005
Express (beta 2)as Subscriber (pull-subscription).
For this purposes used T-SQL command (at Subscriber)
sp_addmergepullsubscription (after it runs in subscriber database appears
some system tables for "replication managment") Next step is to run (at
Subscriber)sp_addmergepullsubscription_agent procedure. I use it in this way:
use [DB_FOR_REPLICATION]
EXEC sp_addmergepullsubscription_agent
@.name = ...,
@.publisher = ...,
@.publisher_db = ...,
@.publication = ...,
@.publisher_security_mode=0,
@.publisher_login = ...,
@.publisher_password = ...,
@.distributor_security_mode=0,
@.distributor = ...,
@.distributor_login = ...,
@.distributor_password = ...,
@.job_login = ...,
@.job_password = ...,
@.use_interactive_resolver='false',
@.enabled_for_syncmgr='true',
@.frequency_type=4,
@.frequency_subday=4,
@.frequency_subday_interval=1,
@.active_start_date= 20050101;
GO
As I understand its meant that: SQL server authentication used for both
Publisher/Distributor(login/pswrd i supplied), replication is sheduled to run
every minute since 2005 jan 01 and _subscription can be synchronized through
Windows Synchronization Manager_.
Questions:
1. Does Windows Synchronization Manager keep password i provided in SP
above? (every time i'm trying to run it asks me to enter 'em again).
2. Where i can find schedule mentioned in SP (every minute since...)?
3. What happens when @.enabled_for_syncmgr='false'? I cant syncronize DBs at
all. No agent tables in SubscriptionDB and no schedule in MSBD.
Thnx for answers
Best Regards Maxim Morzhov
Does anyone know?
Best regards
Morzhov M.
|||Since SQL Server 2005 Express Edition doesn't include SQL Agent, the
scheduling parameters will be ingored. After you create the subscription, you
must setup a schedule in Windows Synchronization Manager to run the agent on
a schedule.
If you don't enable Windows Syncronization Manager, you can still
synchronize the subscription by running the Merge Agent directly (C:\Program
Files\Microsoft SQL Server\90\COM\replmerge.exe) as shown in the SQL Server
2005 BOL topic "
How to: Synchronize a Pull Subscription (Replication Programming)." Or you
can write an application that uses RMO to synchronize the subscription.
Hope that helps,
Glenn Gailey [MS]
This posting is provided "AS IS" with no warranties, and confers no rights.
"MaxX_RUS" wrote:

> Hi.
> I want to replicate SQL 2005 - SQL 2005 Express. MS SQL 2005 (DevEdition
> beta 2) is configured as Publisher/Distributor. I want ti configure SQL 2005
> Express (beta 2)as Subscriber (pull-subscription).
> For this purposes used T-SQL command (at Subscriber)
> sp_addmergepullsubscription (after it runs in subscriber database appears
> some system tables for "replication managment") Next step is to run (at
> Subscriber)sp_addmergepullsubscription_agent procedure. I use it in this way:
> use [DB_FOR_REPLICATION]
> EXEC sp_addmergepullsubscription_agent
> @.name = ...,
> @.publisher = ...,
> @.publisher_db = ...,
> @.publication = ...,
> @.publisher_security_mode=0,
> @.publisher_login = ...,
> @.publisher_password = ...,
> @.distributor_security_mode=0,
> @.distributor = ...,
> @.distributor_login = ...,
> @.distributor_password = ...,
> @.job_login = ...,
> @.job_password = ...,
> @.use_interactive_resolver='false',
> @.enabled_for_syncmgr='true',
> @.frequency_type=4,
> @.frequency_subday=4,
> @.frequency_subday_interval=1,
> @.active_start_date= 20050101;
> GO
> As I understand its meant that: SQL server authentication used for both
> Publisher/Distributor(login/pswrd i supplied), replication is sheduled to run
> every minute since 2005 jan 01 and _subscription can be synchronized through
> Windows Synchronization Manager_.
> Questions:
> 1. Does Windows Synchronization Manager keep password i provided in SP
> above? (every time i'm trying to run it asks me to enter 'em again).
> 2. Where i can find schedule mentioned in SP (every minute since...)?
> 3. What happens when @.enabled_for_syncmgr='false'? I cant syncronize DBs at
> all. No agent tables in SubscriptionDB and no schedule in MSBD.
> Thnx for answers
> Best Regards Maxim Morzhov