Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts

Thursday, March 29, 2012

Filter in Replication

Hello,
I have a table JobDescription with the columns:
JobId
Email
StartTime
EndTime
Priority
etc.
I want the Filter in that way that only the rows of the JobDescription of a
certain Email Adress will be synchronized.
Can anybody tell me how this work?
thx
mathon
But which rows from which email would be displayed is only clear when the
user logs in the application with his email-adress. So I think the static
filter is not the right. Should I use dynamic filter? - How do I have to
quote it in that way in the Publication Process and then in my code? -
Unfortunately I haven't found a good sample for that.
mathon
|||Yes - dynamic filters is the way to go. If this is per
user, you can have a column for the loginname (the data
returned from select suser_sname()) and use that in the
filter. The dynamic filter option is a subset of the
horizontal filters part of the wizard and will appear if
you have selected the advanced options (first checkbox in
the wizard). Your dynamic filter will look like this:
SELECT <published_columns> FROM [dbo].[JobDescription]
WHERE JobDescription.SuserSName = SUSer_SName().
If you don't want another column in the table, and want
to map logins to email addresses, you can publish the
mapping table and apply the dynamic filter to that table,
or alternatively encapsulate the whole thing in a UDF to
be used in the where clause.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
|||Dear Mr. Ibison!
Let me reflect once to know If I'll be right:
The method in the Login-mask selects the the Email of the User (Engineer) ->
SELECT Email FROM Engineer WHERE UserId=? AND password=?.
So in the Publication Wizard:
1) First I check "Horizontally, by filtering the rows published data"
2) Then I check "Yes, enable dynamic filters"
3) Then I select the Table to filter - [dbo].[JobDescription]
4) Then the Select Statement looks as follows:
SELECT <published_columns> FROM [dbo].[JobDescription] WHERE
[dbo].[JobDescription].EmailEngineer = EngineerLogin()
But when I want to go further the following error occurs:
"The WHERE clause 'JobDescription.EmailEngineer = EngineerLogin()' could not
be executed on table '[dbo].[JobDescription]'.
Error 195. 'EngineerLogin() is not a recognized function name.
Do you know what went wrong? (The function exists in my class-file.)
Regards
mathon
"Paul Ibison" wrote:

> Yes - dynamic filters is the way to go. If this is per
> user, you can have a column for the loginname (the data
> returned from select suser_sname()) and use that in the
> filter. The dynamic filter option is a subset of the
> horizontal filters part of the wizard and will appear if
> you have selected the advanced options (first checkbox in
> the wizard). Your dynamic filter will look like this:
> SELECT <published_columns> FROM [dbo].[JobDescription]
> WHERE JobDescription.SuserSName = SUSer_SName().
> If you don't want another column in the table, and want
> to map logins to email addresses, you can publish the
> mapping table and apply the dynamic filter to that table,
> or alternatively encapsulate the whole thing in a UDF to
> be used in the where clause.
> HTH,
> Paul Ibison (SQL Server MVP)
>
>
|||Mathon,
firstly, I must clarify that the ability to filter data
on a per subscriber basis is part of merge replication
only (out of the box), so that is the scenario we are
looking at.
Your login mask may or may not be an issue. Is the
username and password used to log into sql server - ie is
this a SQL Server login? If it is, then filtering will be
simpler. In the wizard, ensure the advanced options are
enabled, and after selecting horizontal filters, select
the dynamic option. Your EngineerLogin() function as far
as I can see is a function you've created in your VB
code, so is not accessible to SQL Server (CLR integration
is a posibility in SQL 2005 but not SQL 2000). So, to map
the login name to the email address, you'll need a user-
defined function to be created in SQL Server. This
function will take suser_sname() as a parameter and will
return the email address as a return value.
HTH,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Mr. Ibison!
The password and userId is the log-in information for my mobile application
and not the login for the sql server. Is this more complicated?
regards
mat
"Paul Ibison" wrote:

> Mathon,
> firstly, I must clarify that the ability to filter data
> on a per subscriber basis is part of merge replication
> only (out of the box), so that is the scenario we are
> looking at.
> Your login mask may or may not be an issue. Is the
> username and password used to log into sql server - ie is
> this a SQL Server login? If it is, then filtering will be
> simpler. In the wizard, ensure the advanced options are
> enabled, and after selecting horizontal filters, select
> the dynamic option. Your EngineerLogin() function as far
> as I can see is a function you've created in your VB
> code, so is not accessible to SQL Server (CLR integration
> is a posibility in SQL 2005 but not SQL 2000). So, to map
> the login name to the email address, you'll need a user-
> defined function to be created in SQL Server. This
> function will take suser_sname() as a parameter and will
> return the email address as a return value.
> HTH,
> Paul Ibison (SQL Server MVP)
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||OK - in that case there might be issues. Do you use the
same login to SQL Server for everyone? Is the mobile
device used by an individual user or multiple users?
In the easiest scenario, the device is used by the same
user each time. Typically you'll be using a sql server
login which doesn't differentiate between users, so we
can't rely on this in the filter. If this is true, you
could set the filter to use HOST_NAME(). In the merge
agent's job step for this subscriber, add -HOSTNAME
[emailaddress] to filter the data (replace emailaddress
with the individuals email address). The filter will be
as follows:
SELECT <published_columns> FROM [dbo].[JobDescription]
WHERE JobDescription.email = HOST_NAME().
HTH,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Mr. Ibison!
Yes the device is always used by the same user and everybody has the same
login and password (sa,sa) to the SQL Server database. But i do not exactly
know what you mean with merge agents job step? Where should I add
-HOSTNAME[emailaddress]? And is it then still dynamically when I am not able
to filter according to the login a user makes in my mobile application?
regards
mat
"Paul Ibison" wrote:

> OK - in that case there might be issues. Do you use the
> same login to SQL Server for everyone? Is the mobile
> device used by an individual user or multiple users?
> In the easiest scenario, the device is used by the same
> user each time. Typically you'll be using a sql server
> login which doesn't differentiate between users, so we
> can't rely on this in the filter. If this is true, you
> could set the filter to use HOST_NAME(). In the merge
> agent's job step for this subscriber, add -HOSTNAME
> [emailaddress] to filter the data (replace emailaddress
> with the individuals email address). The filter will be
> as follows:
> SELECT <published_columns> FROM [dbo].[JobDescription]
> WHERE JobDescription.email = HOST_NAME().
> HTH,
> Paul Ibison (SQL Server MVP)
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Mathon,
the hostname will be dynamic in the sense of each
subscription will be able to get different data. This
means that each mobile device can have different data. It
doesn't mean that if a different user logs onto the
device he'll see different data - the data is partitioned
according to device. If there is a 1:1 relationship
between users and devices then this is fine.
The merge agent is a job. The middle step runs the merge
executable. In this step there will be a number of
parameters , and at the end you can add -HOSTNAME with a
value. If you are doing this programatically, the
HOSTNAME is a property of the merge object.
I recommend that you set it up and have a play around
with the dynamic filtering in a test environment to see
if it meets your requirements.
HTH,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Mr. Ibison!
Unfortunately I am not so familiar with the Replication Mechanism. In my
application I realized the replication only in that way that all my tables
would be replicated and no filters were set. I do this in the Publication
Wizard in the SQL Server Enterprise Manager. In the code I only quote then
the properties for the Replication like login, publisher, etc. (The
Replication works, but as said I want to filter the one table
JobDescription.)
So I am not sure how I should execute your quoted steps to reach my aim. Can
you probably give me a simpler and/or more detailed explanation?:-/ sorry..
mathon

filter in merge publication

Hi There,
I would like to filter first or latest 100 row while performing the
publication for merge replication. Is there any information how i can do
this in the case when table does not exist date fiels.
Thanks for help.
Indra.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Indra,
Provided you can relate 100 rows to a particular key value, you could use
static filters:
In SQL Server Enterprise Manager, expand the Replication and Publications
folders, right-click the publication for which you want to add a row filter,
and then click Properties. On the Filter Rows tab, click the Filter Clause
(...) button next to the article you want to filter, and then in the Specify
Filter dialog box, complete the WHERE clause with a condition for the filter
(BOL). - further details are in BOL: "merge replication, row filters".
HTH,
Paul Ibison
sql

Filter delete rows on subscriber

How does SQL server replication delete rows on the client that no longer
matches the filter? I need this to decide if I want to add a new table into
replication.
TIA.
Rajesh
There is a table with a large number of rows (5-6 million), with only a few
rows (2000-3000) replicated to each client. Each client needs to get a
different set of rows.
I want to know whether:
1. The filter is applied on the subscriber to delete the rows?
2. Or the IDs to be deleted is sent to the subscriber by the publisher?
3. Or the IDs on the subscriber is sent to the publisher, for which the
publisher responds with the IDs that should no longer be available in the
subscriber.
Due to the large number of rows, I'm just worried that #2 and #3 will lead
to a lot of inefficeincy. And if that is the case, I would rather sync this
data outside of replication.
Rajesh
"Rajesh" wrote:

> How does SQL server replication delete rows on the client that no longer
> matches the filter? I need this to decide if I want to add a new table into
> replication.
> TIA.
> Rajesh
sql

Filter data back to publisher

Hi all,

We have merge replication set up between 2 instances of SQL 2005 through web synchronization. I was wondering is it possible if the subscriber adds data at their end can we selectively edit what data gets uploaded back to the publisher? Will this work if I add a filter to the appropriate article at the publisher or will that only limit what goes down to the subscriber rather than vice-versa?

Also, could we set up web sychronization if the publisher had no direct access to the IIS server (ie. was not on the same network, available only through port 80, 443)?

Thanks,

Iain

Hey lain,

You can't selectively edit what data gets uploaded back to the publisher. There's a parameter that you can use in sp_addmergearticle @.subscriber_upload_options which you can set to (0,1,2) NO restrictions, changes are allowed at sub but not uploaded to the publisher , and last option doesn't allow any changed at the subscriber respectively. So you can either upload all the changes or none of the changes, but you can't filter out the data to be uploaded.

There's a workaround but that's not recommended. You can enable the insert triggers on the data you want to upload to the publisher, and then disable the insert triggers on the data you want to filter out. By doing that merge agent won’t detect those changes and they won't be uploaded to the publisher.

hope that helps

bishoyy

|||

Ok, thanks. Looks like if they want to implement their requirement they will just have to become the publisher. I think messing the the triggers will just lead to all sorts of issues and problems that I could well do without!

Thanks for the help.

Filter data back to publisher

Hi all,

We have merge replication set up between 2 instances of SQL 2005 through web synchronization. I was wondering is it possible if the subscriber adds data at their end can we selectively edit what data gets uploaded back to the publisher? Will this work if I add a filter to the appropriate article at the publisher or will that only limit what goes down to the subscriber rather than vice-versa?

Also, could we set up web sychronization if the publisher had no direct access to the IIS server (ie. was not on the same network, available only through port 80, 443)?

Thanks,

Iain

Hey lain,

You can't selectively edit what data gets uploaded back to the publisher. There's a parameter that you can use in sp_addmergearticle @.subscriber_upload_options which you can set to (0,1,2) NO restrictions, changes are allowed at sub but not uploaded to the publisher , and last option doesn't allow any changed at the subscriber respectively. So you can either upload all the changes or none of the changes, but you can't filter out the data to be uploaded.

There's a workaround but that's not recommended. You can enable the insert triggers on the data you want to upload to the publisher, and then disable the insert triggers on the data you want to filter out. By doing that merge agent won’t detect those changes and they won't be uploaded to the publisher.

hope that helps

bishoyy

|||

Ok, thanks. Looks like if they want to implement their requirement they will just have to become the publisher. I think messing the the triggers will just lead to all sorts of issues and problems that I could well do without!

Thanks for the help.

Filter clause

We are moving to SQL 2005 Standard Edition are use numerouse filters
for replication. In SQL 2000, we had a lot of the filters setup using
an OR statement in the filterclause (ie. a.company = b.company or
a.subcompany = b.company), and had no problems with adding the filters.
In SQL 2005, creating these filters takes forever, if created at all.
Has anyone out there seen a problem like this?
Any help would be appreciated.
Thanks,
Amy Marshall
Can you define "takes forever" as well as "if created at all"? Are they not
being created? Are you receiving errors? What is happening?
As far as creating these en mass. if you are using the GUI and are stuck in
that world, then plan on spending a few days clicking through and setting
this stuff up. Instead, you can very easily setup the base replication
configuration, generate a script, and then add all of the filters into the
script, in a fraction of the time it takes to use the GUI.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
<marshallae@.bowater.com> wrote in message
news:1135868828.946674.289840@.g14g2000cwa.googlegr oups.com...
> We are moving to SQL 2005 Standard Edition are use numerouse filters
> for replication. In SQL 2000, we had a lot of the filters setup using
> an OR statement in the filterclause (ie. a.company = b.company or
> a.subcompany = b.company), and had no problems with adding the filters.
> In SQL 2005, creating these filters takes forever, if created at all.
> Has anyone out there seen a problem like this?
> Any help would be appreciated.
> Thanks,
> Amy Marshall
>
|||How many tables? Did you select the option to automatically generate
filters? This is very lengthy for large related tables in both SQL 2000 and
SQL 2005.
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
<marshallae@.bowater.com> wrote in message
news:1135868828.946674.289840@.g14g2000cwa.googlegr oups.com...
> We are moving to SQL 2005 Standard Edition are use numerouse filters
> for replication. In SQL 2000, we had a lot of the filters setup using
> an OR statement in the filterclause (ie. a.company = b.company or
> a.subcompany = b.company), and had no problems with adding the filters.
> In SQL 2005, creating these filters takes forever, if created at all.
> Has anyone out there seen a problem like this?
> Any help would be appreciated.
> Thanks,
> Amy Marshall
>
|||I have about 10 tables that use the 'OR' filter that links to one
table. What I did was script the package from SQL 2000 and ran it in
Query Analyzer on SQL 2005. If I take out the 'OR' statement, then the
filter will be created in less than a second. With the 'OR' statement,
I usually end up cancelling it after 5-10 minutes for each table with
that filter. (We were testing with the CTP Sept. version, and did not
have this issue...Could it be Standard vs. Enterprise?)
Thanks,
Amy
|||No, the edition doesn't matter. I can't reproduce this on the RTM bits.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
<marshallae@.bowater.com> wrote in message
news:1136292153.538075.219420@.g14g2000cwa.googlegr oups.com...
>I have about 10 tables that use the 'OR' filter that links to one
> table. What I did was script the package from SQL 2000 and ran it in
> Query Analyzer on SQL 2005. If I take out the 'OR' statement, then the
> filter will be created in less than a second. With the 'OR' statement,
> I usually end up cancelling it after 5-10 minutes for each table with
> that filter. (We were testing with the CTP Sept. version, and did not
> have this issue...Could it be Standard vs. Enterprise?)
> Thanks,
> Amy
>

Tuesday, March 27, 2012

filter

We have salesmen with laptops and want to use merge replication that
they will start via a synchronize button (I will use the ActiveX merge
object). Each salesman should only have data for their own customers.
Can the filtering be done at the subscriber during the pull
subscription, or do I have to create different publications on the
server? If the subscriber can filter, where can I find the filter
information in the activex module?
TIA
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Darin,
there is a HOSTNAME property of the merge object which can be used for the
dynamic filter, or alternatively it could just pick up the NETBIOS name by
default if this is suitable for your data (ie if subscriber computer names
appear in the publication data). In the publication, the filter needs to use
the HOST_NAME() function in order for this to work.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'm not real sure on this filtering for pull subscriptions.
My original publication is setup on the server via SQLDMO, and the
laptop will use ActiveX to pull the subscription.
Would I specify on the server while creating the merge article the
filter (salesman=HOSTNAME() where hostname is the name of the laptop) OR
would I do some filter on the laptop side?
Kinda confused.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||Darin,
yes - on the publisher specify salesman=HOST_NAME()
The function HOST_NAME() returns the subscriber NETBIOS name. If you don't
want the NETBIOS name, the property HOSTNAME of the merge object can be used
to override it with something else.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Ok, I am getting an error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near 'dbo'.
[microsoft][odbc sql server driver][sql server] A column used in filter
clause 'ccod_customer IN dbo.fRSCustomer(RTRIM(HOST_NAME()))' either
does not exist in the table 'ARContact' or cannot be exlcuded from the
current partition.
ccod_customer is in my table ARContact. I created the funcion
rRSCustomer to return a table which is a list of customer #'s this
salesman is tied to.
Any ideas?
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||Here is my code that creates the articles for publication (VB.NET)
objMergeArticle = New MergeArticle2
With objMergeArticle
.Name = junk
.SourceObjectName = junk
.SourceObjectOwner = "dbo"
.CreationScriptOptions =
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_Pr imaryObject + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_DR I_Defaults + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_DR I_ForeignKeys + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_Cl usteredIndexes + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_DR I_Checks + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_PK UKAsConstraints + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_UD DTsToBaseTypes + _
SQLDMO_CREATIONSCRIPT_TYPE.SQLDMOCreationScript_No nClusteredIndexes
.SubsetFilterClause = "ccod_customer IN
dbo.fRSCustomer(RTRIM(HOST_NAME()))"
End If
End With
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||Darin,
can you try 'ARContact.ccod_customer IN
dbo.fRSCustomer(RTRIM(HOST_NAME()))'.
If this doesn't work, I'll have a go myself. I haven't used a table-level
function like you're doing here, but have used a join to a lookup table to
achieve the same aim. Anyway, if my suggestion doesn't work, I'll have a
look at your method (although I vaguely remember another poster also having
problems with table-level functions ).
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I am still having the same problems. In addition, I am having another
issue which I posted in another thread (ActiveX Pull error).
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||Darin,
do you have access to EM on the laptop to confirm you can set it all up
manually - as a sort of proof-of-concept? My guess is that you are using
MSDE and this is not possible, but if it is, it might help with both these
issues.
Rgds,
Paul Ibison
|||The first error is occuring on the server while creating the filters.
The second is on the laptop. I will try to setup the pull subscription
directly on the laptop to see if that works.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
sql

Monday, March 26, 2012

Fillfactor

we have 2 servers with similar tables and columns and indexes with fillfactor
0. My replication is very slow during daytime and comes to normal at night
times.when i check the reads and writes both are more that 90 % high but when
i check the page splits it shows nothing.what do i need to do to fasten my
replication.
Do i need to add fillfactor?
i didn't see any page splits though
Thanks
Pardhi
Message posted via http://www.droptable.com
If you have no page splits, then I'd start monitoring for blocking issues
during synchronization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Paul i didn't see any blocking either
Paul Ibison wrote:
>If you have no page splits, then I'd start monitoring for blocking issues
>during synchronization.
>Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
Message posted via http://www.droptable.com
|||Then perhaps it's just one of the the usual processor/memory/disk access
issues. You could use profiler to monitor these counters and see if this is
the issue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Monday, March 12, 2012

Filegroup Bkp

Hi,

I have a VLDB database replicated to two different servers; one replication is a merge and the other is Transactional. It is a real time data which has a very high growth rate. At present we take full backup daily. The users during the pick time complain for the performance of this database. I am planning to implement a file group backups on this db and seperate tables and indexes on each file group. I would like expert opinion that will this implementation affect on my replication. I am worried for if i do this, my current replication will not be disturbed or stop working? Please advise? Also please suggest how frequent should i take backups?

Thanks

You are reconfiguring the database as well as the table structures. So, yes, it will impact replication when you reconfigure everything. Once moved, replication does not care. It issues insert, update, and delete statements and does not care what file the data is being written to.

The frequency of your backup is based on your data loss requirements. There isn't a specific answer on the frequency.

|||

First of all try to figure it out what is cuasing performance problem by running the trace for high duration, cpu and reads queries/procedures then proceed based on the analysis...

Are you running full backups during peak hours?

In the market there are some third party backup tools make your backups faster.

|||Yes you can test the filegroup backup method in thsi case, but ensure you have tested them in order to ensure it works in the case of an disaster recovery, fyi http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlwriter.mspx link.|||

There are no utilities to make backups "faster". Everything still calls the SQL Server backup engine and that engine runs as fast as the devices on the other side of it can take data. Because these utilities compress a backup, you are writing less to disk. Since you are writing less to disk, the backup runs in a shorter interval. It does not run any faster.

There is also a WIDE variance in times with these utilities. I've tested SQL Litespeed and Red Gate's SQL Backup. After testing more than 100 different databases containing a variety of data, Litespeed gets the job done in about 1/3 of the time that SQL Backup takes (default settings for both out of the box). The only case where I've gotten equivalent performance with the default settings was when I used a database that was almost entirely constructed of large text columns.

|||Does that mean, it will disturb the existing replication if i create a new file group on my db and i will have to rebuild replication?|||Does that mean, it will disturb the existing replication if i create a new file group on my db and i will have to rebuild replication?
|||No. You can add filegroups all you want without any issues. The issue comes when you want to move the tables to those filegroups. That is going to require dropping and recreating the clustered index which is normally the primary key. If you have enabled schema changes, then you will get an error when you try to alter the schema on the subscriber. I'd have to do some testing, I've never tried to move tables around to different filegroups on the subscriber while also keeping replication in place and running.

Sunday, February 26, 2012

File replication via sattelite

Will data be changing at both Publisher and subscriber?
>--Original Message--
>Hello.
>I need to do replication via sattelite communication.
This means: Very high cost (7$/minute), low datarate (64
kbit), not reliable and long delay (up to 4 seconds from a
datapackage is sent until an ACK is returned.
>I would prefere to do this offline- that is by
transfering a file with replication data.
>The datavolume shuld be kept to the lowest possible.
>A high degree of data partioning is possible in the
application.
>High degree of autonomy is necessary- only replication
once a day.
>This suggests merge replication.
>But is it possible to that filebased: That is, transfer a
file (via e-mail) and then do the merge at the subscriber
totally offline?
>I would appriciate if anyone could lead me in the correct
direction on this. (Or stop me before I reach the edge..)
>.
>
Yes, data will change both sides. But the application will only allow Inserts both sides, Deletes and Modifies one side. (Hopefully)
"Kevin3NF" wrote:

> Will data be changing at both Publisher and subscriber?
> This means: Very high cost (7$/minute), low datarate (64
> kbit), not reliable and long delay (up to 4 seconds from a
> datapackage is sent until an ACK is returned.
> transfering a file with replication data.
> application.
> once a day.
> file (via e-mail) and then do the merge at the subscriber
> totally offline?
> direction on this. (Or stop me before I reach the edge..)
>

File replication via sattelite

Hello.
I need to do replication via sattelite communication. This means: Very high cost (7$/minute), low datarate (64 kbit), not reliable and long delay (up to 4 seconds from a datapackage is sent until an ACK is returned.
I would prefere to do this offline- that is by transfering a file with replication data.
The datavolume shuld be kept to the lowest possible.
A high degree of data partioning is possible in the application.
High degree of autonomy is necessary- only replication once a day.
This suggests merge replication.
But is it possible to that filebased: That is, transfer a file (via e-mail) and then do the merge at the subscriber totally offline?
I would appriciate if anyone could lead me in the correct direction on this. (Or stop me before I reach the edge..)
The short answer is no. All replication types use rpc or port 1433 to
communicate with each other. Going to something as synchronous as a file
transfer will not work.
Note that Exchange essentially does what you are looking for. Exchange
servers send email (IIRC) to each other telling them what to do.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"otto b" <otto b@.discussions.microsoft.com> wrote in message
news:88E097FC-6CB1-4E37-B0B0-A92260E6FA75@.microsoft.com...
> Hello.
> I need to do replication via sattelite communication. This means: Very
high cost (7$/minute), low datarate (64 kbit), not reliable and long delay
(up to 4 seconds from a datapackage is sent until an ACK is returned.
> I would prefere to do this offline- that is by transfering a file with
replication data.
> The datavolume shuld be kept to the lowest possible.
> A high degree of data partioning is possible in the application.
> High degree of autonomy is necessary- only replication once a day.
> This suggests merge replication.
> But is it possible to that filebased: That is, transfer a file (via
e-mail) and then do the merge at the subscriber totally offline?
> I would appriciate if anyone could lead me in the correct direction on
this. (Or stop me before I reach the edge..)
|||Partitioning of data according to subscriber is most easily achieved using
merge and the -HOSTNAME parameter. It is also possible in transactional
replication but more difficult to maintain: you use Transformable
Subscriptions and define a different DTS package for each subscriber.
There is no filebased method of transferring the replicated data, apart from
the initial snapshot files of course, so using email won't work.
Minimizing the data volume is more difficult to determine. Mostly
transactional is less data, but if you had 1000 updates of a single record,
that would be 1000 transactions in transactional replication and just a
single record in merge, so recommending the method 'depends'. You could
measure network traffic in a test environment and compare transactional and
merge for your scenario.
HTH,
Paul Ibison