NNTP-Posting-Host: 61.95.221.55
Path: TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.connect:42289 microsoft.public.sqlserver.dts:47716 microsoft.public.sqlserver.programming:440288 microsoft.public.sqlserver.replication:50654 microsoft.public.sqlserver.server:339057 microsoft.public.s
qlserver.xml:23307 microsoft.public.sqlserver.clients:28442
Hi,
I want to use SQL Server replication in a scenario where the Publisher and
multiple subscribers are connected over a very low bandwidth.
Is that possible to write down some scripts/programs which does the
following
1. Create a file containing changes made locally during that day on each of
the subscribers. (This size of this file should be as small as possible)
2. Compress these files and Upload to the Publisher throught FTP or by
emails.
3. Publisher after receiving files from all the subscribers will merge the
changes from each of the subscribers.
4. Publisher then makes a fresh file containing changes for each of the
subscriber. This would contain collective changes from all the subscribers
which are releavant to each of the subscribers.
5. These files are then downloaded by each of the subscribers and the
changes are merged into their copy.
I'd prefer to use it in a scenario where data is partially replicated at
each of the subscribers however even a full replication is better than not
having anything.
Thanks in Advance,
Puneet
Puneet,
you require updating subscribers who have autonomy. In replication topologies, this limits the choice to merge replication or transactional replication with queued subscribers. In either case you can't take the changes and email them - they must be read
by the appropriate agent. You could implement what you want using DTS to export/import CSV files. Obviously it would be a highly customised solution, but I have done similar things this way.
HTH,
Paul Ibison
|||Hi Paul,
I understand it requires an agent which is capable of understanding the
format in which the changes have been recorded and put into the file. The
point I'm trying to make is the requirement that the agent should be able to
do its work using one server at a time. The problem area is access to both
the servers together is not possible, or if it is possible, it is over a
very slow connection.
Using DTS would be a huge task. I strongly feel that it is possible to
develop a standard solution for requirements like mine and hope one already
exists.
Thanks,
Puneet
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:C156E629-4BDE-4EF4-B338-95F1888CA619@.microsoft.com...
> Puneet,
> you require updating subscribers who have autonomy. In replication
topologies, this limits the choice to merge replication or transactional
replication with queued subscribers. In either case you can't take the
changes and email them - they must be read by the appropriate agent. You
could implement what you want using DTS to export/import CSV files.
Obviously it would be a highly customised solution, but I have done similar
things this way.
> HTH,
> Paul Ibison
|||Puneet,
such a solution is not one that I know of in straightforward replication. I
suppose you could cheat a little and use snapshot replication (although this
ends up doing the same as the DTS suggestion). You'd need to flag new
records and reset the flag after replication. If data was partitioned
according to server, then each server could be designated as a publisher,
and records appended to each subscriber. This will only work if the
partitions are strict and data cannot be changed in more than one place.
HTH,
Paul Ibison
Showing posts with label public. Show all posts
Showing posts with label public. Show all posts
Monday, March 26, 2012
Wednesday, March 21, 2012
Merge Replication Problem (Private,Public Data - Security Issue)
Hello,
We plan to establish a merge replication topology in which several servers
(subscribers) have "public" data – data that have to be propagated to the
other servers, and private data - data reside only at original servers.
Private and public data are marked with one table column (1/0). Those servers
are at local branches in different countries. We have one server on the
Internet, which can be publisher and hold all public data, besides its own
private data. Subscriptions will be anonymous pull subscriptions. Subscribers
would periodicaly make dialup connections and merge data. Article filter
would be something like "Public=1". The problem is that subscribers' private
data will go to the publisher in merge replication. As far as we understand,
filter is only checked at the publisher and not at the subscriber. Only
public data goes from publisher to the subscribers, but all data goes from
subscriber to the publisher.
Our client is to happy about the fact that data marked as private is
available on the Internet server.
Is there any possibility to prevent private data going to the publisher?
Many thanks in advance.
Mirza
The easiest solution is to partition the data - have 2 tables, one for
private and one for public. This way, the data is protected and can be
selectively replicated. To show the data to the user on teh subscriber you
could use partitioned views, or standard views with an instead-of trigger.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
We plan to establish a merge replication topology in which several servers
(subscribers) have "public" data – data that have to be propagated to the
other servers, and private data - data reside only at original servers.
Private and public data are marked with one table column (1/0). Those servers
are at local branches in different countries. We have one server on the
Internet, which can be publisher and hold all public data, besides its own
private data. Subscriptions will be anonymous pull subscriptions. Subscribers
would periodicaly make dialup connections and merge data. Article filter
would be something like "Public=1". The problem is that subscribers' private
data will go to the publisher in merge replication. As far as we understand,
filter is only checked at the publisher and not at the subscriber. Only
public data goes from publisher to the subscribers, but all data goes from
subscriber to the publisher.
Our client is to happy about the fact that data marked as private is
available on the Internet server.
Is there any possibility to prevent private data going to the publisher?
Many thanks in advance.
Mirza
The easiest solution is to partition the data - have 2 tables, one for
private and one for public. This way, the data is protected and can be
selectively replicated. To show the data to the user on teh subscriber you
could use partitioned views, or standard views with an instead-of trigger.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Saturday, February 25, 2012
merge replication causing cpu spikes?
Hi ... having a problem with an application that relies on merge replication
among three SQL Servers. Two of the servers are public, the other's behind a
firewall. Periodically the CPUs on the public servers spikes, and
sqlserver.exe is consuming the whole thing. Running "select * from
master.sysprocesses order by cpu desc " revealed a lot of "lastwaittype"
records pointing to NETWORKIO as the source. It appears port 1433 is open
between these servers, but none of the Microsoft networking ports seem to be.
Replication has been suggested as the prime suspect, but sqlagent.exe doesn't
seem to be consuming many CPU cycles by comparison.
Aside from the NIC and basic SQL Server counters, what performance counters
should I monitor to get to the source of the problem? Any other suggestions
to determine exactly why this is happening to SQL?
Thanks in advance,
Dan
This is a tough one. First of all don't get too excited by CPU spikes. It
just means that your processor is busy. A while loop is able to throw your
CPU into a tizzy. for example open up a large log file and do a global
search and replace.
CPU spikes become problematic when they last a long time. How long does the
cpu spike last for?
What happens when you shut down SQL Server Agent? Do you still get these
spikes?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Dan Maniotis" <Dan Maniotis@.discussions.microsoft.com> wrote in message
news:F3117348-73DF-4179-A810-0783BB24EC23@.microsoft.com...
> Hi ... having a problem with an application that relies on merge
replication
> among three SQL Servers. Two of the servers are public, the other's behind
a
> firewall. Periodically the CPUs on the public servers spikes, and
> sqlserver.exe is consuming the whole thing. Running "select * from
> master.sysprocesses order by cpu desc " revealed a lot of "lastwaittype"
> records pointing to NETWORKIO as the source. It appears port 1433 is open
> between these servers, but none of the Microsoft networking ports seem to
be.
> Replication has been suggested as the prime suspect, but sqlagent.exe
doesn't
> seem to be consuming many CPU cycles by comparison.
> Aside from the NIC and basic SQL Server counters, what performance
counters
> should I monitor to get to the source of the problem? Any other
suggestions
> to determine exactly why this is happening to SQL?
> Thanks in advance,
> Dan
|||I should have been more specific ... these spikes are pretty devastating,
with the application that relies on the database becoming completely
unresponsive ... forcing us to restart the application.
We didn't shut down SQLAgent, but we did shut down the application that
makes database changes, so the agent had a lot less work to do ... the
assumption is that it catches up on its backlogged replication duties by the
time the app is brought back online (usually within a half hour or so). We're
putting some performance counters in place to prove/disprove that theory.
Is shutting down the agent relatively safe with merge replication? I've done
that using transactional replication with no negative results, but I wanted
to err on the side of caution.
Thanks *very* much for your help.
Best Regards,
Dan
"Hilary Cotter" wrote:
> This is a tough one. First of all don't get too excited by CPU spikes. It
> just means that your processor is busy. A while loop is able to throw your
> CPU into a tizzy. for example open up a large log file and do a global
> search and replace.
> CPU spikes become problematic when they last a long time. How long does the
> cpu spike last for?
> What happens when you shut down SQL Server Agent? Do you still get these
> spikes?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Dan Maniotis" <Dan Maniotis@.discussions.microsoft.com> wrote in message
> news:F3117348-73DF-4179-A810-0783BB24EC23@.microsoft.com...
> replication
> a
> be.
> doesn't
> counters
> suggestions
>
>
|||Yes, shutting down the merge agent is safe to do and has little impact on a
server. The longer you have the merge agent shut down for the more impact it
will have on the system.
The reason I say this is because every time there is some update activity on
the published tables, this transaction is logged in msmerge_contents and
msmerge_tombstone. As these tables get large, the logging takes longer and
performance is degraded, although normally not signficantly.
Ideally you want your merge agent to run frequently so it can process small
chunks of data, as opposed to run hourly and process large chunks of data.
Perhaps change your polling interval to every 10 seconds, and set your merge
agent to run every 5 minutes and see if this has an impact on performance.
One more thing - are you using filtering?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Dan Maniotis" <DanManiotis@.discussions.microsoft.com> wrote in message
news:8A030C31-E60B-452D-B943-E9B3AE1B883F@.microsoft.com...
> I should have been more specific ... these spikes are pretty devastating,
> with the application that relies on the database becoming completely
> unresponsive ... forcing us to restart the application.
> We didn't shut down SQLAgent, but we did shut down the application that
> makes database changes, so the agent had a lot less work to do ... the
> assumption is that it catches up on its backlogged replication duties by
the
> time the app is brought back online (usually within a half hour or so).
We're
> putting some performance counters in place to prove/disprove that theory.
> Is shutting down the agent relatively safe with merge replication? I've
done
> that using transactional replication with no negative results, but I
wanted[vbcol=seagreen]
> to err on the side of caution.
> Thanks *very* much for your help.
> Best Regards,
> Dan
> "Hilary Cotter" wrote:
t[vbcol=seagreen]
your[vbcol=seagreen]
the[vbcol=seagreen]
behind[vbcol=seagreen]
"lastwaittype"[vbcol=seagreen]
open[vbcol=seagreen]
to[vbcol=seagreen]
|||We're not using any filtering at all, and the merge agent is set to run every
minute.
Learned something more about the network setup: *all* ports are blocked
upstream from the subscribers to the publisher: the publisher's behind a
firewall and the subscribers are in front of it. The public subscribers' data
is updated much more frequently than the publisher's, but it's incumbent on
the publisher to initiate replication.
My uninformed impression was that merge replication would require two-way
communication, but at least one of our admins believes the replication
connections are initiated by the publisher, so this is a non-issue.
And just to make things really interesting, sqlagent isn't logging on
the publisher (no sqlagent.out to check for errors). Gah.
Thanks again,
Dan
"Hilary Cotter" wrote:
> Yes, shutting down the merge agent is safe to do and has little impact on a
> server. The longer you have the merge agent shut down for the more impact it
> will have on the system.
> The reason I say this is because every time there is some update activity on
> the published tables, this transaction is logged in msmerge_contents and
> msmerge_tombstone. As these tables get large, the logging takes longer and
> performance is degraded, although normally not signficantly.
> Ideally you want your merge agent to run frequently so it can process small
> chunks of data, as opposed to run hourly and process large chunks of data.
> Perhaps change your polling interval to every 10 seconds, and set your merge
> agent to run every 5 minutes and see if this has an impact on performance.
> One more thing - are you using filtering?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Dan Maniotis" <DanManiotis@.discussions.microsoft.com> wrote in message
> news:8A030C31-E60B-452D-B943-E9B3AE1B883F@.microsoft.com...
> the
> We're
> done
> wanted
> t
> your
> the
> behind
> "lastwaittype"
> open
> to
>
>
among three SQL Servers. Two of the servers are public, the other's behind a
firewall. Periodically the CPUs on the public servers spikes, and
sqlserver.exe is consuming the whole thing. Running "select * from
master.sysprocesses order by cpu desc " revealed a lot of "lastwaittype"
records pointing to NETWORKIO as the source. It appears port 1433 is open
between these servers, but none of the Microsoft networking ports seem to be.
Replication has been suggested as the prime suspect, but sqlagent.exe doesn't
seem to be consuming many CPU cycles by comparison.
Aside from the NIC and basic SQL Server counters, what performance counters
should I monitor to get to the source of the problem? Any other suggestions
to determine exactly why this is happening to SQL?
Thanks in advance,
Dan
This is a tough one. First of all don't get too excited by CPU spikes. It
just means that your processor is busy. A while loop is able to throw your
CPU into a tizzy. for example open up a large log file and do a global
search and replace.
CPU spikes become problematic when they last a long time. How long does the
cpu spike last for?
What happens when you shut down SQL Server Agent? Do you still get these
spikes?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Dan Maniotis" <Dan Maniotis@.discussions.microsoft.com> wrote in message
news:F3117348-73DF-4179-A810-0783BB24EC23@.microsoft.com...
> Hi ... having a problem with an application that relies on merge
replication
> among three SQL Servers. Two of the servers are public, the other's behind
a
> firewall. Periodically the CPUs on the public servers spikes, and
> sqlserver.exe is consuming the whole thing. Running "select * from
> master.sysprocesses order by cpu desc " revealed a lot of "lastwaittype"
> records pointing to NETWORKIO as the source. It appears port 1433 is open
> between these servers, but none of the Microsoft networking ports seem to
be.
> Replication has been suggested as the prime suspect, but sqlagent.exe
doesn't
> seem to be consuming many CPU cycles by comparison.
> Aside from the NIC and basic SQL Server counters, what performance
counters
> should I monitor to get to the source of the problem? Any other
suggestions
> to determine exactly why this is happening to SQL?
> Thanks in advance,
> Dan
|||I should have been more specific ... these spikes are pretty devastating,
with the application that relies on the database becoming completely
unresponsive ... forcing us to restart the application.
We didn't shut down SQLAgent, but we did shut down the application that
makes database changes, so the agent had a lot less work to do ... the
assumption is that it catches up on its backlogged replication duties by the
time the app is brought back online (usually within a half hour or so). We're
putting some performance counters in place to prove/disprove that theory.
Is shutting down the agent relatively safe with merge replication? I've done
that using transactional replication with no negative results, but I wanted
to err on the side of caution.
Thanks *very* much for your help.
Best Regards,
Dan
"Hilary Cotter" wrote:
> This is a tough one. First of all don't get too excited by CPU spikes. It
> just means that your processor is busy. A while loop is able to throw your
> CPU into a tizzy. for example open up a large log file and do a global
> search and replace.
> CPU spikes become problematic when they last a long time. How long does the
> cpu spike last for?
> What happens when you shut down SQL Server Agent? Do you still get these
> spikes?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Dan Maniotis" <Dan Maniotis@.discussions.microsoft.com> wrote in message
> news:F3117348-73DF-4179-A810-0783BB24EC23@.microsoft.com...
> replication
> a
> be.
> doesn't
> counters
> suggestions
>
>
|||Yes, shutting down the merge agent is safe to do and has little impact on a
server. The longer you have the merge agent shut down for the more impact it
will have on the system.
The reason I say this is because every time there is some update activity on
the published tables, this transaction is logged in msmerge_contents and
msmerge_tombstone. As these tables get large, the logging takes longer and
performance is degraded, although normally not signficantly.
Ideally you want your merge agent to run frequently so it can process small
chunks of data, as opposed to run hourly and process large chunks of data.
Perhaps change your polling interval to every 10 seconds, and set your merge
agent to run every 5 minutes and see if this has an impact on performance.
One more thing - are you using filtering?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Dan Maniotis" <DanManiotis@.discussions.microsoft.com> wrote in message
news:8A030C31-E60B-452D-B943-E9B3AE1B883F@.microsoft.com...
> I should have been more specific ... these spikes are pretty devastating,
> with the application that relies on the database becoming completely
> unresponsive ... forcing us to restart the application.
> We didn't shut down SQLAgent, but we did shut down the application that
> makes database changes, so the agent had a lot less work to do ... the
> assumption is that it catches up on its backlogged replication duties by
the
> time the app is brought back online (usually within a half hour or so).
We're
> putting some performance counters in place to prove/disprove that theory.
> Is shutting down the agent relatively safe with merge replication? I've
done
> that using transactional replication with no negative results, but I
wanted[vbcol=seagreen]
> to err on the side of caution.
> Thanks *very* much for your help.
> Best Regards,
> Dan
> "Hilary Cotter" wrote:
t[vbcol=seagreen]
your[vbcol=seagreen]
the[vbcol=seagreen]
behind[vbcol=seagreen]
"lastwaittype"[vbcol=seagreen]
open[vbcol=seagreen]
to[vbcol=seagreen]
|||We're not using any filtering at all, and the merge agent is set to run every
minute.
Learned something more about the network setup: *all* ports are blocked
upstream from the subscribers to the publisher: the publisher's behind a
firewall and the subscribers are in front of it. The public subscribers' data
is updated much more frequently than the publisher's, but it's incumbent on
the publisher to initiate replication.
My uninformed impression was that merge replication would require two-way
communication, but at least one of our admins believes the replication
connections are initiated by the publisher, so this is a non-issue.
And just to make things really interesting, sqlagent isn't logging on
the publisher (no sqlagent.out to check for errors). Gah.
Thanks again,
Dan
"Hilary Cotter" wrote:
> Yes, shutting down the merge agent is safe to do and has little impact on a
> server. The longer you have the merge agent shut down for the more impact it
> will have on the system.
> The reason I say this is because every time there is some update activity on
> the published tables, this transaction is logged in msmerge_contents and
> msmerge_tombstone. As these tables get large, the logging takes longer and
> performance is degraded, although normally not signficantly.
> Ideally you want your merge agent to run frequently so it can process small
> chunks of data, as opposed to run hourly and process large chunks of data.
> Perhaps change your polling interval to every 10 seconds, and set your merge
> agent to run every 5 minutes and see if this has an impact on performance.
> One more thing - are you using filtering?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Dan Maniotis" <DanManiotis@.discussions.microsoft.com> wrote in message
> news:8A030C31-E60B-452D-B943-E9B3AE1B883F@.microsoft.com...
> the
> We're
> done
> wanted
> t
> your
> the
> behind
> "lastwaittype"
> open
> to
>
>
Monday, February 20, 2012
Merge Replication and FK constraints
This question is also posted in public.sqlserver.ce, Sorry!
Scenario:
SQL 2000 with 3 tables A, B ans C related to each other with FK constraints.
B has a relation to A and C has a relation to B
In the FK columns the option 'Enforec relationship for replication' is
unchecked
Merge replication to SQL CE
I would like to take some rows from A set up a filter to include related
rows from B and ALL rows from C
In my mind this should work. Despite the fact that there will be some rows i
C with no parent row i B the replication should execute whitout errors.
I got the following confusing error message:
@."Exception 1: Error Code: 80004005 Message : Run Minor Err.: 28557 Source
: Microsoft SQL Server 2000 Windows CE Edition Err. Par. : data
source=\Program Files\VPJ\VPJ.sdf;Exception 2: Error Code: 80040E14 Message
: The OLE DB Execute method failed. The SQL statement is not valid. [,,,SQL
statement,,] Minor Err.: 28560 Source : Microsoft SQL Server 2000 Windows
CE EditionException 3: Error Code: 80040E14 Message : There was an error
parsing the query. [Token line number,Token line offset,,Token in error,,]
Minor Err.: 25501 Source : Microsoft SQL Server 2000 Windows CE Edition
Num. Par. : 1 Num. Par. : 145 Err. Par. : NOT"
Anyone who have succeeded to replicate between SQL 2000 and SQL CE in a
similar scenario
/Thomas B
We do not support the NOT FOR REPLICATION option with SQL Server CE. Make
sure that the box is checked
thanks
gopal
Scenario:
SQL 2000 with 3 tables A, B ans C related to each other with FK constraints.
B has a relation to A and C has a relation to B
In the FK columns the option 'Enforec relationship for replication' is
unchecked
Merge replication to SQL CE
I would like to take some rows from A set up a filter to include related
rows from B and ALL rows from C
In my mind this should work. Despite the fact that there will be some rows i
C with no parent row i B the replication should execute whitout errors.
I got the following confusing error message:
@."Exception 1: Error Code: 80004005 Message : Run Minor Err.: 28557 Source
: Microsoft SQL Server 2000 Windows CE Edition Err. Par. : data
source=\Program Files\VPJ\VPJ.sdf;Exception 2: Error Code: 80040E14 Message
: The OLE DB Execute method failed. The SQL statement is not valid. [,,,SQL
statement,,] Minor Err.: 28560 Source : Microsoft SQL Server 2000 Windows
CE EditionException 3: Error Code: 80040E14 Message : There was an error
parsing the query. [Token line number,Token line offset,,Token in error,,]
Minor Err.: 25501 Source : Microsoft SQL Server 2000 Windows CE Edition
Num. Par. : 1 Num. Par. : 145 Err. Par. : NOT"
Anyone who have succeeded to replicate between SQL 2000 and SQL CE in a
similar scenario
/Thomas B
We do not support the NOT FOR REPLICATION option with SQL Server CE. Make
sure that the box is checked
thanks
gopal
Subscribe to:
Posts (Atom)