replication technique

Hello,
I read serveral articles about the mechanism of replication from a sql
server 2000 database on
desktop with an mobile device running windows mobile.
I want to remark my understanding fo the replication mechanism and wanted to
ask you if you
could give me feedback if I understand it in a correct way.
-----
1) First of all I create a publication - which is a collection of articles -
rows. It is
possible to define certain tables and rows for publication.
2) After the publication a windows ce-based application can call the methods
of the SQL Server
Ce Replication object to subscribe to the publication. When the mobile
application has executed
the subscription the initial snapshot of the published table is stored on
the mobile device and
so the database file is created on the device.
3) The subscription database on the device can then be updated by
synchronzing it with the desktop
database. For that the synchronisation is always intiated by the device. For
that the SQL Server CE
Client Agent extracts all modified rows from the subscription database put
these rows in a message
file and sends it over HTTP to the SQL Server CE Server Agent. The SQL
Server CE Server Agent
creates a new message file on the desktop where the IIS is running and
writes in that file the
modified rows which are sent by the SQL Server CE Client Agent.Then the SQL
Server CE Server Agent
initiates the SQL Server Reconciler process. The SQL Server Reconciler
Process loads the SQL Server CE
Replication Provider which reads the message file which was created by the
SQL Server CE Server Agent
and apply the changes to the publisher database. The SQL Server CE
Replication Provider informs the SQL Server
Reconciler about the changes which were made in the subscription
database.The SQL Server Reconciler
also solves conflicts if more than one subscriber or publisher updates the
same record.
On the other hand the SQL Server Reconciler informs the SQL Server CE
Replication Provider of
changes which occured at the publisher database. The SQL Server CE
Replication writes this
information - especially the modified rows - to an output message file.
After the Reconciler
process is completed the SQL Server CE Server Agent locates the the output
message file reads
the information and transfer it to the SQL Server CE Client Agent which
apply the changes from
the output message file to the subscription databse on the windows ce device.
-----
a) Are these steps correct or have I forgotten anything important or have I
understand something
wrong?
b) What I have to imagine behind the SQL Server CE Client Agent, the SQL
Server CE Server Agent,
SQL Server Reconciler and the SQL Server CE Replication Provider? What are
these terms exactly?
c) When I publish a database, especially tables in the enterprise manager
the tables get automatically
a row guid. What part plays these row guid's in my above mentioned
replication process?
d) The Replication Mechanism uses static and dynamic filtering. Is it right
that the dynamic filter
could not use on the application layer? - means that it is not possiblt to
replicate for example
the information according to the user which is currently logged in the
application? - I think it
is only possible to synchronize according to a certain device especially
certain host, is that right?
I know this is a extensive posting but I would appreciate it very much if
anybody find time to
answer me my questions. :-/
Regards.
patrick
answers inline
--
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
"pat" <pat@.discussions.microsoft.com> wrote in message
news:273513F0-8240-4E1A-9598-9ED04BC448CE@.microsoft.com...
> Hello,
> I read serveral articles about the mechanism of replication from a sql
> server 2000 database on
> desktop with an mobile device running windows mobile.
> I want to remark my understanding fo the replication mechanism and wanted
> to
> ask you if you
> could give me feedback if I understand it in a correct way.
> -----
> 1) First of all I create a publication - which is a collection of
> articles -
> rows. It is
> possible to define certain tables and rows for publication.
Yes, you will need to set up a merge publication. When you say rows for
publication you mean horizontal filtering.
> 2) After the publication a windows ce-based application can call the
> methods
> of the SQL Server
> Ce Replication object to subscribe to the publication. When the mobile
> application has executed
> the subscription the initial snapshot of the published table is stored on
> the mobile device and
> so the database file is created on the device.
Yes, you will code something using vb or vc embedded or the .Net compact
framework to pull a merge subscription. This will use a SQL CE database on
your PocketPC.
> 3) The subscription database on the device can then be updated by
> synchronzing it with the desktop
> database. For that the synchronisation is always intiated by the device.
> For
> that the SQL Server CE
> Client Agent extracts all modified rows from the subscription database put
> these rows in a message
> file and sends it over HTTP to the SQL Server CE Server Agent. The SQL
> Server CE Server Agent
> creates a new message file on the desktop where the IIS is running and
> writes in that file the
> modified rows which are sent by the SQL Server CE Client Agent.Then the
> SQL
> Server CE Server Agent
> initiates the SQL Server Reconciler process. The SQL Server Reconciler
> Process loads the SQL Server CE
> Replication Provider which reads the message file which was created by the
> SQL Server CE Server Agent
> and apply the changes to the publisher database. The SQL Server CE
> Replication Provider informs the SQL Server
> Reconciler about the changes which were made in the subscription
> database.The SQL Server Reconciler
> also solves conflicts if more than one subscriber or publisher updates the
> same record.
> On the other hand the SQL Server Reconciler informs the SQL Server CE
> Replication Provider of
> changes which occured at the publisher database. The SQL Server CE
> Replication writes this
> information - especially the modified rows - to an output message file.
> After the Reconciler
> process is completed the SQL Server CE Server Agent locates the the output
> message file reads
> the information and transfer it to the SQL Server CE Client Agent which
> apply the changes from
> the output message file to the subscription databse on the windows ce
> device.
>
This is basically it.
> -----
> a) Are these steps correct or have I forgotten anything important or have
> I
> understand something
> wrong?
that's basically it

> b) What I have to imagine behind the SQL Server CE Client Agent, the SQL
> Server CE Server Agent,
> SQL Server Reconciler and the SQL Server CE Replication Provider? What are
> these terms exactly?
> c) When I publish a database, especially tables in the enterprise manager
> the tables get automatically
> a row guid. What part plays these row guid's in my above mentioned
> replication process?
it uniquely identifys each row, so SQL Server can figure out which row has
changed or needs changing.

> d) The Replication Mechanism uses static and dynamic filtering. Is it
> right
> that the dynamic filter
> could not use on the application layer? - means that it is not possiblt to
> replicate for example
> the information according to the user which is currently logged in the
> application? - I think it
> is only possible to synchronize according to a certain device especially
> certain host, is that right?
>
No, you define your filter on the publication. I don't believe you can
filter your subscribers.

> I know this is a extensive posting but I would appreciate it very much if
> anybody find time to
> answer me my questions. :-/
> Regards.
> patrick
>
|||Hello,
Thank you very much for your reply but few things are still unclear:
1) Why do I need the row guid? - the changed rows are saved in a file and
are sent to the SQL Server CE Server Agent, so he knows which rows were
modified when he looks into the message file. Why does he need then a row
guid?
2) The SQL Server CE Client Agent, the SQL, Server CE Server Agent, SQL
Server Reconciler and the SQL Server CE Replication Provider - are these
processes or components or parts of the os? what I have to imagine behind
these terms?
3) Referring to dynamic filtering I thought it is possible to synchronize
after a certain host on the mobile device? - What can I then realize with
dynamic filterin?
(static filtering is when I define some rows or columns or tables which I
can then synchronize or?)
Hopefully you could answer me once more.
regards
patrick
3)
|||Hi,
Thank you very much for your reply but unfortunately few things are unclear:
1)Why do I need this row guid's, because the SQL Server CE Server Agent
reads the modified rows from the message file which is sent by the SQL
Server CE Client Agent and so the SQL Server CE Replication Provider knows
what rows he has to modify. So why do I neet this row guids?
2)Tthe SQL Server CE Client Agent, the SQL Server CE Server Agent, SQL
Server Reconciler and the SQL Server CE Replication Provider - what do I have
to imagene behind these terms? Are these processes or components or part of
the os or anyting else?
3)Referring to the filtering. So it is not possible to define a filter
according to a certain host on a mobile device? - The static filtering allows
to define some rows or columns or tables which can be modified, but then for
what is the dynamic filtering?
regards
patrick
|||hi,
is it possible to answer me the last time on my three posted questions?
regards
patrick
sql

Replication Synchronization Error

I have a default full install of the JULY CTP on X86 Windows Server 2003 (fully up to date on the patches). I used the Merge Replication Sample to create the Publisher/Publication. SInce the code sample to create the subscriber doesn't work (apparently some major changes in the assemblies) I had to create the Subscription using the wizard. Both databases are on the same machine (adventureworks and adventureworksreplicated). When I try and sychronize the database the first time I get the following error.

Error loading custom assembly "C:\Program Files\Microsoft SQL Server\100\COM\BusinessLogic.dll", Error : "Could not load file or assembly 'C:\\Program Files\\Microsoft SQL Server\\100\\COM\\BusinessLogic.dll' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)".

I did confirm that the assembly is there, but do not know what else to look for. The account the synch is running under is the local Administrator (not a good idea I know but just want it to work).

Any ideas?

Thanks

Steve Muise

Neudesic LLC

Two threads down is a workaround for what looks like might be your problem, can you give it a shot? If it doesn't work, then let us know.

Replication Sync on Windows Vista

On windows XP there is a Synchronize program in Accessories. When I run our replication scripts on a SQL Server Express on the XP machine and start the Synchronize program there are items there that allow me to replicate as well as set a schedule, etc.

On Windows Vista there is a Sync Center, but replication doesn't show up here.

I've searched high and low on Google and am not coming up with anything useful, which leads me to believe this is something very simple that I'm overlooking...

Does anyone know how to make your replication show up on the Sync Center in vista, or is there another application that I should download?

Thanks,

Brian

I could be wrong, but I do not believe those are the same. I think Sync Manager in Vista is a built-in tool for synchronizing PDA's and thing like that. However, so I do not mis-speak, I am testing that now because we are also at the point of testing synchronization with Vista.

I will let you know what i find.

Scott

|||To add files to Sync Center, on the (Vista) machine you want to have the offline copy on, open explorer and navigate to the network share the original file or directory is on. Right-click and on the context menu select "Always Available Online." The SyncCenter icon will be superimposed on the icon to show it is being copied.

Replication Sync on Windows Vista

On windows XP there is a Synchronize program in Accessories. When I run our replication scripts on a SQL Server Express on the XP machine and start the Synchronize program there are items there that allow me to replicate as well as set a schedule, etc.

On Windows Vista there is a Sync Center, but replication doesn't show up here.

I've searched high and low on Google and am not coming up with anything useful, which leads me to believe this is something very simple that I'm overlooking...

Does anyone know how to make your replication show up on the Sync Center in vista, or is there another application that I should download?

Thanks,

Brian

I could be wrong, but I do not believe those are the same. I think Sync Manager in Vista is a built-in tool for synchronizing PDA's and thing like that. However, so I do not mis-speak, I am testing that now because we are also at the point of testing synchronization with Vista.

I will let you know what i find.

Scott

|||To add files to Sync Center, on the (Vista) machine you want to have the offline copy on, open explorer and navigate to the network share the original file or directory is on. Right-click and on the context menu select "Always Available Online." The SyncCenter icon will be superimposed on the icon to show it is being copied.

Replication problems

Hi group
I have the following setup:
A primary sqlserver running windows 2000 server OS
a secondary sqlserver running windows 2003 server OS
Both sqlservers run sqlserver 2000 software, and both logon with the
local administrator account. Both administrator account have the same
password, so accessing \\<servername>\c$\....\repldata shouldnt be a
problem.
the primary sqlserver has been configured as the publication server,
publicating a database called repli_test, as snapshot
The secondary sqlserver has a pull subscription to the repli_test
database.
When I try to pull the database, I get the following error:
The job failed. Unable to determine if the owner
(SQL-BACKUP\Administrator) of job
SQLSERVER-repli_test-repli_test-SQL-BACKUP-repli_test-9DA1AF79-158C-49BD-89B6-64D8F9EF2C21
has server access
(reason: Could not obtain information about Windows NT group/user
'SQL-BACKUP\Administrator'. [SQLSTATE 42000] (Error 8198)).
any suggestions to a solution?
TIA
Kaare
Try changing the job owner to sa.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you very much for your reply. It really did the trick.
Unfortunally Im stock on another problem. Replication doesnt seem to
work, if a table contains more than 255 columns. Is there any workaround
on this issue?
TIA
Kaare
|||The main advised workaround is to partition the table and then replicate as
two articles in the same publication. From the point of view of the system,
this could be made transparent as you could have a view which unions the
data from the 2 tables, and call the view the same as the curent table. This
would be taking advantage of non-distributed partitioned views.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication problem with SQL Mobile

Dear ppl,

I have a windows mobile 5.0 pocket PC application that performs replication between a Publihser (PC, SQL SERVER 2005), and subscriber (SQL MOBILE) on the pocket PC. The problem I am having is as follows

I created a bidirectional publication (SQL SERVER 2005). Then the pocket pc application subscribe to it (SQL Mobile). Pocket PC app add some records and then synchronise the data. Lets suppose the Pocket PC app adds 2 records on SQL Mobile in a table with primary key 2000 and 2001, which also reflected on the server after syncronisation.

Now for some reason I had to delete the publication and recreate it. My pocket PC resubscribed to the new publication, synchronise the data and get records 2000 and 2001 in SQL Mobile db. Now when the pocket Pc app tries to add a new record to it, it comes up with the error something like "duplicate values not allowed". What it is trying to do is, it is trying to add a new record with id 2000, which is already there in the table and therefore ending up throwing exception.

Can anyone please tell me what is the solution to this problem. Am i missing anything? Do i need to do something with configuring publication while recreating it after deleting it.

Regards
Nabeel Farid

Is this PK column an identity column on the table?

Or is the app generating the value 2000?

Can you subscriber to this publication on a different mobile database and try inserting there?

Replication problem

Hi.

I am using SQL Server 2005 at the back-end, with a database published for merge replication; Windows Mobile-based handheld devices running a .NET 2.0 CF application, and SQL Mobile 2005.

Intermittently, when initialising the subscription on a device, the app cycles through the tables multiple times, or even endlessly.

Can anyone shed some light on why this is happening?

Try to check the Replication Monitor (server side) for status, including number of records submitted. There are some issues with merge replication in the SQL Server 2005 RTM, that have been fixed in SQL Server 2005 SP2.|||

Thanks for the response.

I checked the replication monitor as a matter of course - no problems there, everything looks fine.

Interesting what you say about SP2 - the box in question is running RTM, so maybe updating it will help. Is there anywhere that goes into detail on what these issues that you mention were, and if/how they have been addressed in SP2?

|||

There are various merge/SQL Compact fixes, all documented at: http://support.microsoft.com/default.aspx?scid=kb;en-us;921896
Amongst others:

919949 (http://support.microsoft.com/kb/919949/) FIX: The sp_MSinitDynamicSubscriber stored procedure is called more than one time by using the same parameters in SQL Server 2005

|||

Erik,

Thanks for that. I am recommending to my client that they upgrade to SP2, and will post back here with the results.

|||

My client is hoping to install SP2 in a couple of weeks. In the meantime, they applied the hotfix, but the problem still exists.

They applied the hotfix to the server running the instance of SQL 2005, but not to the machine with the SQL Mobile server tools (and SQL 2005 client components). Is this correct?

I am hoping that, despite the failure of the hotfix to resolve the issue, the full SP2 update will make a difference...

|||

Erik,

SP2 has now been applied to both servers, and the problem is now resolved.

Thanks very much for your help.

|||

We are facing the same problem... so suggested client to apply for Sp2.... still the problem pesrsists,,,,

Any idea as how to resolve it... please let me know if anything need to be changed after applying Sp2...

Waiting for an answer..

|||It is important to apply SP2 on all servers, including IIS (web) servers (replication components)|||

How to apply SQL server 2005 Service pack 2 on IIS web servers...

I really didnt understand this..can u please telll me what is it

|||If you are running IIS on a separate server, you need to update the replciation components to SP2, see this for info http://msdn2.microsoft.com/en-us/library/ms172937.aspx|||

I will update the replication componets on IIS servers, but what abt the "SQLCSA30.dll" contained in the virtual directory... HOw to update this dll with Service pack 2....

Please correct me if i am using the wrong concepts.. I am new to these concepts..

|||

I will update the replication componets on IIS servers, but what abt the "SQLCESA30.dll" contained in the virtual directory... HOw to update this dll with Service pack 2....

Please correct me if i am using the wrong concepts.. I am new to these concepts..

|||

Thank You Erik...

My Problem solved after applying SP2

GOKI

sql

Replication problem

Hi.

I am using SQL Server 2005 at the back-end, with a database published for merge replication; Windows Mobile-based handheld devices running a .NET 2.0 CF application, and SQL Mobile 2005.

Intermittently, when initialising the subscription on a device, the app cycles through the tables multiple times, or even endlessly.

Can anyone shed some light on why this is happening?

Try to check the Replication Monitor (server side) for status, including number of records submitted. There are some issues with merge replication in the SQL Server 2005 RTM, that have been fixed in SQL Server 2005 SP2.|||

Thanks for the response.

I checked the replication monitor as a matter of course - no problems there, everything looks fine.

Interesting what you say about SP2 - the box in question is running RTM, so maybe updating it will help. Is there anywhere that goes into detail on what these issues that you mention were, and if/how they have been addressed in SP2?

|||

There are various merge/SQL Compact fixes, all documented at: http://support.microsoft.com/default.aspx?scid=kb;en-us;921896
Amongst others:

919949 (http://support.microsoft.com/kb/919949/) FIX: The sp_MSinitDynamicSubscriber stored procedure is called more than one time by using the same parameters in SQL Server 2005

|||

Erik,

Thanks for that. I am recommending to my client that they upgrade to SP2, and will post back here with the results.

|||

My client is hoping to install SP2 in a couple of weeks. In the meantime, they applied the hotfix, but the problem still exists.

They applied the hotfix to the server running the instance of SQL 2005, but not to the machine with the SQL Mobile server tools (and SQL 2005 client components). Is this correct?

I am hoping that, despite the failure of the hotfix to resolve the issue, the full SP2 update will make a difference...

|||

Erik,

SP2 has now been applied to both servers, and the problem is now resolved.

Thanks very much for your help.

|||

We are facing the same problem... so suggested client to apply for Sp2.... still the problem pesrsists,,,,

Any idea as how to resolve it... please let me know if anything need to be changed after applying Sp2...

Waiting for an answer..

|||It is important to apply SP2 on all servers, including IIS (web) servers (replication components)|||

How to apply SQL server 2005 Service pack 2 on IIS web servers...

I really didnt understand this..can u please telll me what is it

|||If you are running IIS on a separate server, you need to update the replciation components to SP2, see this for info http://msdn2.microsoft.com/en-us/library/ms172937.aspx|||

I will update the replication componets on IIS servers, but what abt the "SQLCSA30.dll" contained in the virtual directory... HOw to update this dll with Service pack 2....

Please correct me if i am using the wrong concepts.. I am new to these concepts..

|||

I will update the replication componets on IIS servers, but what abt the "SQLCESA30.dll" contained in the virtual directory... HOw to update this dll with Service pack 2....

Please correct me if i am using the wrong concepts.. I am new to these concepts..

|||

Thank You Erik...

My Problem solved after applying SP2

GOKI

Replication problem

Hi.

I am using SQL Server 2005 at the back-end, with a database published for merge replication; Windows Mobile-based handheld devices running a .NET 2.0 CF application, and SQL Mobile 2005.

Intermittently, when initialising the subscription on a device, the app cycles through the tables multiple times, or even endlessly.

Can anyone shed some light on why this is happening?

Try to check the Replication Monitor (server side) for status, including number of records submitted. There are some issues with merge replication in the SQL Server 2005 RTM, that have been fixed in SQL Server 2005 SP2.|||

Thanks for the response.

I checked the replication monitor as a matter of course - no problems there, everything looks fine.

Interesting what you say about SP2 - the box in question is running RTM, so maybe updating it will help. Is there anywhere that goes into detail on what these issues that you mention were, and if/how they have been addressed in SP2?

|||

There are various merge/SQL Compact fixes, all documented at: http://support.microsoft.com/default.aspx?scid=kb;en-us;921896
Amongst others:

919949 (http://support.microsoft.com/kb/919949/)

FIX: The sp_MSinitDynamicSubscriber stored procedure is called more than one time by using the same parameters in SQL Server 2005

|||

Erik,

Thanks for that. I am recommending to my client that they upgrade to SP2, and will post back here with the results.

|||

My client is hoping to install SP2 in a couple of weeks. In the meantime, they applied the hotfix, but the problem still exists.

They applied the hotfix to the server running the instance of SQL 2005, but not to the machine with the SQL Mobile server tools (and SQL 2005 client components). Is this correct?

I am hoping that, despite the failure of the hotfix to resolve the issue, the full SP2 update will make a difference...

|||

Erik,

SP2 has now been applied to both servers, and the problem is now resolved.

Thanks very much for your help.

|||

We are facing the same problem... so suggested client to apply for Sp2.... still the problem pesrsists,,,,

Any idea as how to resolve it... please let me know if anything need to be changed after applying Sp2...

Waiting for an answer..

|||It is important to apply SP2 on all servers, including IIS (web) servers (replication components)|||

How to apply SQL server 2005 Service pack 2 on IIS web servers...

I really didnt understand this..can u please telll me what is it

|||If you are running IIS on a separate server, you need to update the replciation components to SP2, see this for info http://msdn2.microsoft.com/en-us/library/ms172937.aspx|||

I will update the replication componets on IIS servers, but what abt the "SQLCSA30.dll" contained in the virtual directory... HOw to update this dll with Service pack 2....

Please correct me if i am using the wrong concepts.. I am new to these concepts..

|||

I will update the replication componets on IIS servers, but what abt the "SQLCESA30.dll" contained in the virtual directory... HOw to update this dll with Service pack 2....

Please correct me if i am using the wrong concepts.. I am new to these concepts..

|||

Thank You Erik...

My Problem solved after applying SP2

GOKI

replication problem

Hello:

We are using SQL Server 2000 Standard Edition on Windows 2000. If we want to set up a DR site, we have 2 options:

a. transactional replication
b. log shipping

However since we do not have Enterprise Edition, maybe the best option we have is transactional replication.

My problem with transactional replication is when we set it up, we do not seem to have control over initial snapshot. When the database is huge, we prefer to manually take the snapshot and restore it at DR site, is it possible ?

rgds,
eric ngYes, you have a choice between initializing the database to subscriber or not. If you say no, then sql would assume you already have the same schema and data in the subscriber.|||Originally posted by joejcheng
Yes, you have a choice between initializing the database to subscriber or not. If you say no, then sql would assume you already have the same schema and data in the subscriber.

Thank you. The manual does not seem to have step-by-step guidelines on how to manually send the snapshot over to subscriber server before transactional replication starts continous replication.

Btw, I come across a lot of people discouraging people using transactional replication as DR method.

Is it true that transactional replication is very taxing to the production server ?

Is custom log shipping worth trying (if we are using standard edition) ?

Is third-party software eg DoubleTake would be much better in our context ?

rgds,
eric ng|||The other way around that is to disable the snapshot right after you setup the replication. You can disable it in the Replication Monitor or you can disable it as a scheduled task.

Transactional Replication can be very slow if you have large batches coming accross the network. But it provides you more concurrent processing than Log Shipping. Remember, Log Shipping is simply a backup/restore process, and every time you restore the database you would have disallow or kill all the user access. Just imagine this is done hourly, the users will be interrupted every hour. On the other hand, Log Shipping is easier to manage. And if you can restore only once a day in the evening, that could be your choice.

Replication performance - how to measure it ?

Hello,

could you please advice on how to measure replication performance
in Oracle, DB2 & MS SQL Server RDBMS installed in Windows servers ?
I've got two servers with databases installed and configured,
I prepared set of data using DBGEN from TPC and I already imported them
into databases.Also, I configured the replication.
Now I have to do a test with a few kind of replications method
implemented in these RDMBS, but I don't know which tool or reports or
"v$iews" should I use to measure replication performance.
The replication is configured only between the same RDBMS, I mean
Oracle <-Oracle, DB2 <-DB2 and MSSQL<-MSSQL.
Most of applications are great for checking performance of local DB,
not for replicated/distributed.
I've found description of CA Unicenter Database Performance Management
for distributed RDMBS, and I think it could be the right one, but I
can't find any demo or trial version :(
Could you please advice any place to download it, or other application,
script, description, just whatever.
Perhaps just any other idea how to check the replication mechanism
efficiency ?

Regards,
Markmjan@.interia.pl wrote:

Quote:

Originally Posted by

Hello,
>
could you please advice on how to measure replication performance
in Oracle, DB2 & MS SQL Server RDBMS installed in Windows servers ?
I've got two servers with databases installed and configured,
I prepared set of data using DBGEN from TPC and I already imported them
into databases.Also, I configured the replication.
Now I have to do a test with a few kind of replications method
implemented in these RDMBS, but I don't know which tool or reports or
"v$iews" should I use to measure replication performance.
The replication is configured only between the same RDBMS, I mean
Oracle <-Oracle, DB2 <-DB2 and MSSQL<-MSSQL.
Most of applications are great for checking performance of local DB,
not for replicated/distributed.
I've found description of CA Unicenter Database Performance Management
for distributed RDMBS, and I think it could be the right one, but I
can't find any demo or trial version :(
Could you please advice any place to download it, or other application,
script, description, just whatever.
Perhaps just any other idea how to check the replication mechanism
efficiency ?
>
Regards,
Mark

Any answer you might receive regarding Oracle depends upon which
replication mechanism you choose and which release of Oracle you're
using. Absent such information it's difficult, if not impossible, to
supply an answer.

Since you can't, or won't, supply the above mentioned information take
a trip to http://tahiti.oracle.com and start reading the documentation
for the replication mechanism and release you're using. Monitoring
instructions are usually included in these docs. I could provide
information for Streams on 9.2.0, (and possibly 10.2) but the dearth of
information in your post, as well as having my own work to complete,
causes me to send you to the manuals. Should you decide to provide
more detail I, or someone else in this newsgroup, could possibly supply
a better response to your question.

As ye sow, so shall ye reap. Provide to us, we can provide to you.
It's that simple.

David Fitzjarrell|||mjan@.interia.pl wrote:

Quote:

Originally Posted by

Hello,
>
could you please advice on how to measure replication performance
in Oracle, DB2 & MS SQL Server RDBMS installed in Windows servers ?
I've got two servers with databases installed and configured,
I prepared set of data using DBGEN from TPC and I already imported them
into databases.Also, I configured the replication.
Now I have to do a test with a few kind of replications method
implemented in these RDMBS, but I don't know which tool or reports or
"v$iews" should I use to measure replication performance.
The replication is configured only between the same RDBMS, I mean
Oracle <-Oracle, DB2 <-DB2 and MSSQL<-MSSQL.
Most of applications are great for checking performance of local DB,
not for replicated/distributed.
I've found description of CA Unicenter Database Performance Management
for distributed RDMBS, and I think it could be the right one, but I
can't find any demo or trial version :(
Could you please advice any place to download it, or other application,
script, description, just whatever.
Perhaps just any other idea how to check the replication mechanism
efficiency ?
>
Regards,
Mark

Your question, as stated, is impossible to answer with respect to
Oracle.

You have not provided a version number and replication might be
accomplished in a wide variety of ways depending on your requirements.
Among the possibilities:

Advanced Replication (many variations)
Streams (many variations)
Change Data Capture (3 different modes: Synchronous or Asynchronous?
Hotlog or Autolog?)
Data Guard (3 different modes: Maximum Protection, Maximum Availability,
and Maximum Performance)

You will find that depending on the Oracle option you choose you may
well find that neither SQL Server or DB2 has an equivalent technology.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org|||On 24 Aug 2006 06:17:40 -0700, mjan@.interia.pl wrote:

Quote:

Originally Posted by

>Hello,
>
>could you please advice on how to measure replication performance
>in Oracle, DB2 & MS SQL Server RDBMS installed in Windows servers ?

Hi Mark,

For MS SQL Server, check out the subject "Monitoring Replication" in SQL
Server 2005 Books Online. Here's a link (copy and paste into the URL
field in Books Online to be taken to the subject directly):
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/f182f43a-6af8-45bc-a708-08d5f7a6984a.htm

And if you don't have Books Online installed, here's a link to the
internet copy: http://msdn2.microsoft.com/en-us/library/ms152751.aspx
--
Hugo Kornelis, SQL Server MVP|||And if you don't have Books Online installed, here's a link to the

Quote:

Originally Posted by

internet copy: http://msdn2.microsoft.com/en-us/library/ms152751.aspx
>
--
Hugo Kornelis, SQL Server MVP

Thanks for fast reply, guys.
Hugo, thanks for the link about MS SQL Server monitoring :)

DA Morgan&fitzjarrell, you're right. I'm sorry, my mistake.
Oracle version is 10gR2 10.2.0.1.0, IBM DB2 v8.1.7
and Microsoft product will be probably MS SQL Server 2005

At present I've got configured replication only for DB2,
as concerns Oracle - I'm working on it.
Actually, I'm going to test almost all kinds of replication method
offered by these RDBMS,
so I need the most uniwersal benchmark&monitoring tool for this.
I know that it's hard to advice, but believe me that since a few days
I'm just searching&looking for information about a software or
procedure to evaluate replication performance.
On usenet, ftp's, WWW, P2P, DBA forums, etc. well, just everywhere.
But it seems, that not a lot of admins need to make a replication
tests.
Thats why I decided to ask for help here .

Cheers,
Mark|||diesel wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>And if you don't have Books Online installed, here's a link to the
>internet copy: http://msdn2.microsoft.com/en-us/library/ms152751.aspx
>>
>--
>Hugo Kornelis, SQL Server MVP

>
Thanks for fast reply, guys.
Hugo, thanks for the link about MS SQL Server monitoring :)
>
DA Morgan&fitzjarrell, you're right. I'm sorry, my mistake.
Oracle version is 10gR2 10.2.0.1.0, IBM DB2 v8.1.7
and Microsoft product will be probably MS SQL Server 2005
>
At present I've got configured replication only for DB2,
as concerns Oracle - I'm working on it.
Actually, I'm going to test almost all kinds of replication method
offered by these RDBMS,
so I need the most uniwersal benchmark&monitoring tool for this.

No such thing in the Oracle world. Different tools for different
requirements and different modes depending on your priority.

The faster the replication the more risk of data being lost. Here's
something to consider. Is the transaction completed when it is committed
on the primary box or is it completed when it is committed both locally
and at the replication site? What happens if the link between the two
goes down? Do you continue local processing and hope to catch up later
or do you stop all transactions? These are all very important questions.

To just be testing speed alone is sort of like the airplane pilot who
cuts the engines at 35,000 feet and at 1,500 feet announces to the
passengers that fuel economy is really improved.

Quote:

Originally Posted by

I know that it's hard to advice, but believe me that since a few days
I'm just searching&looking for information about a software or
procedure to evaluate replication performance.

And what I am cautioning you about is that replication performance is
a meaningless metric unless you are comparing technologies that produce
the same outcome. Not just when all is perfect but in real world
situations. I can make anything really fast if I don't care what happens
later when there is a network glitch.

Quote:

Originally Posted by

On usenet, ftp's, WWW, P2P, DBA forums, etc. well, just everywhere.
But it seems, that not a lot of admins need to make a replication
tests. Thats why I decided to ask for help here .
>
Cheers,
Mark

I hope you do help ... but I see no evidence that you are really trying
to compare apples with apples. If you don't your will produce numbers.
Meaningless numbers ... but numbers. I suspect that is not your goal.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Replication on cluster Problem (SQL 2000)

Config :
2 database servers SQL 2000, running with OS Windows 2000 Advanced Server. Set in a cluster.

Prob :
I want to replicate some data from my database for reporting issues.
Now when i go trough "Configure Publishing and Distribution Wizard"
and use the "defaults", i always seem to have this error :
"Error 18483 : Could not connect to server XXX because 'distributor_admin' is not defined as a remote login at the server"

Cause this doesnt work, i tried to "set the distribution properties ...", in the "Configure Publishing and Distribution Wizard". At the Screen with "Provide Distribution Database Information", i have this nice line on the bottem :
"The paths must refer to disks that are local to 'XXX' and begin with a local drive letter and colon (for example, C:). Mapped drive letters and network paths are invalid."

Now since i am using a cluster, and the whole database (all .mdf, ...) are on the network drives, this is a big problem.

Is there any way arround this ?Do you register the server with the exact server name NOT the IP address in the Enterprise Manager Console?
If you didn't , the problem was encountered.
You should register with the servername.
Use the following query:
Select @.@.SERVERNAME
to get the Servername.
I'm sure you can miss it!

Originally posted by Sublimo
Config :
2 database servers SQL 2000, running with OS Windows 2000 Advanced Server. Set in a cluster.

Prob :
I want to replicate some data from my database for reporting issues.
Now when i go trough "Configure Publishing and Distribution Wizard"
and use the "defaults", i always seem to have this error :
"Error 18483 : Could not connect to server XXX because 'distributor_admin' is not defined as a remote login at the server"

Cause this doesnt work, i tried to "set the distribution properties ...", in the "Configure Publishing and Distribution Wizard". At the Screen with "Provide Distribution Database Information", i have this nice line on the bottem :
"The paths must refer to disks that are local to 'XXX' and begin with a local drive letter and colon (for example, C:). Mapped drive letters and network paths are invalid."

Now since i am using a cluster, and the whole database (all .mdf, ...) are on the network drives, this is a big problem.

Is there any way arround this ?|||i have the real servername

SO it isnt that. Main prob imo, is that i want to set the replication database on the clustered disk. And SQL-Server is being a bitch about it.

Sub

replication of sql server within a WIndows 2003 cluster

We have two server setup in a Windows 2003 cluster with SQL 2005 loaded on
both servers. A database was loaded on one server and and we noticed that the
database seem to replicate over to the ohter sql server without a database.
Since then the database on server 2(active) has grown in size and the
database on server1(passive)has remained the original size.
Should i setup relicaion or another options so that the databases are both
showing the same size files?
I'm not sure if I understand your question. By default in an active/passive
(single instance) topology, upon failover all databases will be transferred
to the second node. If by chance you are talking about an active/active
(multi-instance) topology, you need replication installed and configured to
replicate. Drill down on the replication folder for local publications to
see if there is a publication for your database.
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
"toma" <toma@.discussions.microsoft.com> wrote in message
news:556CE8FF-45BD-462D-9FF9-0C838C4A864F@.microsoft.com...
> We have two server setup in a Windows 2003 cluster with SQL 2005 loaded on
> both servers. A database was loaded on one server and and we noticed that
> the
> database seem to replicate over to the ohter sql server without a
> database.
> Since then the database on server 2(active) has grown in size and the
> database on server1(passive)has remained the original size.
> Should i setup relicaion or another options so that the databases are both
> showing the same size files?
|||Thank you for your response.
we have an active/passive enviroment for failover.
server1 is passive and server2 is active.
The database on server2(active) is 35mbytes. The same file on the
server1(passive) is 19 mbytes.
I am concerned that when server2 fails over to server1 that some of the
database functionality will be lost since the files are of different size.
Do you know if the larger database file on the active server will be copied
to the passive server or should I run replicator to insure that the databse
files are the same on each server?
"Hilary Cotter" wrote:

> I'm not sure if I understand your question. By default in an active/passive
> (single instance) topology, upon failover all databases will be transferred
> to the second node. If by chance you are talking about an active/active
> (multi-instance) topology, you need replication installed and configured to
> replicate. Drill down on the replication folder for local publications to
> see if there is a publication for your database.
> --
> 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
>
> "toma" <toma@.discussions.microsoft.com> wrote in message
> news:556CE8FF-45BD-462D-9FF9-0C838C4A864F@.microsoft.com...
>
>

Replication Job Failed. Unable to determine the owner

A former employee at my organization created a replication job to back up our database. The developer has since left and his windows account was deleted. I'm now receiving this error:

SQL Server Scheduled Job 'Replication agents checkup' (0xFC17DD4EF8E5694E8B786EE54CF8AD0D) - Status: Failed - Invoked on: 2007-06-05 15:40:00 - Message: The job failed. Unable to determine if the owner (username) of job Replication agents checkup has server access (reason: Could not obtain information about Windows NT group/user 'username', error code 0x534. [SQLSTATE 42000] (Error 15404) The statement has been terminated. [SQLSTATE 01000] (Error 3621)).

How can I fix this? I looked under the Replication folder but didn't see anything.

In Object Explorer, look at the job in SQL Server Agent to see who owns it, and verify the account is valid.

|||That's for the help. I changed the owner but am now getting this error:

SQL Server Scheduled Job 'Replication agents checkup' (0xFC17DD4EF8E5694E8B786EE54CF8AD0D) - Status: Failed - Invoked on: 2007-06-07 01:00:00 - Message: The job failed. The Job was invoked by Schedule 175 (Replication agent schedule.). The last step to run was step 1 (Run agent.).

When I executed the maintenance plan manually it worked, but it doesn't seem to work on the schedule. I've never created a maintenance plans so I'm going to look up some more information to see if I can figure out what is going to wrong.

Replication Job Failed. Unable to determine the owner

A former employee at my organization created a replication job to back up our database. The developer has since left and his windows account was deleted. I'm now receiving this error:

SQL Server Scheduled Job 'Replication agents checkup' (0xFC17DD4EF8E5694E8B786EE54CF8AD0D) - Status: Failed - Invoked on: 2007-06-05 15:40:00 - Message: The job failed. Unable to determine if the owner (username) of job Replication agents checkup has server access (reason: Could not obtain information about Windows NT group/user 'username', error code 0x534. [SQLSTATE 42000] (Error 15404) The statement has been terminated. [SQLSTATE 01000] (Error 3621)).

How can I fix this? I looked under the Replication folder but didn't see anything.

In Object Explorer, look at the job in SQL Server Agent to see who owns it, and verify the account is valid.

|||That's for the help. I changed the owner but am now getting this error:

SQL Server Scheduled Job 'Replication agents checkup' (0xFC17DD4EF8E5694E8B786EE54CF8AD0D) - Status: Failed - Invoked on: 2007-06-07 01:00:00 - Message: The job failed. The Job was invoked by Schedule 175 (Replication agent schedule.). The last step to run was step 1 (Run agent.).

When I executed the maintenance plan manually it worked, but it doesn't seem to work on the schedule. I've never created a maintenance plans so I'm going to look up some more information to see if I can figure out what is going to wrong.

Replication Issues

Hi,

I have created a publication in Microsoft SQL server 2005 and the publication credentials are used by an application targeted for a windows mobile 5.0 which using RDA pulls the data from the Master data file and creates a local symbol data file, the first part is working fine, i.e RDA pull from Sql Server 2005 to the SQL CE,

Now i am trying to add features where in the changes made by the application in the sdf in the mobile device are to be pushed back to the Master data file, for that i tried to use the RDA push command and for using the push command the primary constraint was the table shoule be tracked on when the data is pulled initially(i.e in the first RDA pull. tracking mode should be seton) but that has a setback for me, for when the tracking is enabled i am getting these columns

_sysIG bigint
_sysCG bigint
_sysCD datetime
_sysP1 varbinary(8000)
_sysMC varbinary(8000)
_sysMCS varbinary(8000)
_sysSR varbinary(8000)

as additional columns in my sdf file and these increase the size of my sdf file, is there any way by which i can stop them from replicating in the symbol data file without affecting the tracking option, for without the tracking option the columns cannot be pushed back to the MDF, can anyone give me insights on this?

Regards,

Venkatesh.M

These columns are required for the tracking mechanism to work. Please see this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=509110&SiteID=1|||

ErikEJ wrote:

These columns are required for the tracking mechanism to work. Please see this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=509110&SiteID=1

Thanks for the insight Erik.. i have solved the issue.. thanks

Replication Issues

Hi,

I have created a publication in Microsoft SQL server 2005 and the publication credentials are used by an application targeted for a windows mobile 5.0 which using RDA pulls the data from the Master data file and creates a local symbol data file, the first part is working fine, i.e RDA pull from Sql Server 2005 to the SQL CE,

Now i am trying to add features where in the changes made by the application in the sdf in the mobile device are to be pushed back to the Master data file, for that i tried to use the RDA push command and for using the push command the primary constraint was the table shoule be tracked on when the data is pulled initially(i.e in the first RDA pull. tracking mode should be seton) but that has a setback for me, for when the tracking is enabled i am getting these columns

_sysIG bigint
_sysCG bigint
_sysCD datetime
_sysP1 varbinary(8000)
_sysMC varbinary(8000)
_sysMCS varbinary(8000)
_sysSR varbinary(8000)

as additional columns in my sdf file and these increase the size of my sdf file, is there any way by which i can stop them from replicating in the symbol data file without affecting the tracking option, for without the tracking option the columns cannot be pushed back to the MDF, can anyone give me insights on this?

Regards,

Venkatesh.M

These columns are required for the tracking mechanism to work. Please see this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=509110&SiteID=1|||

ErikEJ wrote:

These columns are required for the tracking mechanism to work. Please see this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=509110&SiteID=1

Thanks for the insight Erik.. i have solved the issue.. thanks

Replication Issue Syntax error or access violation

I am replication on a SQL Server 2000 env on Windows Server 2003. I randomly
get an access violation error stating "Syntax error or access violation" ODBC
error 37000. After further investigation using sp_browsereplcmds I see that
a blank field in the db is sending the following repeated about 30 times.
''+{fn CHAR(0)}+''+{fn CHAR(0)}
I am using sql server generated procs. I also have 4 other publishers using
the same table without an errors. I am looking for a solution rather than a
work around. Any ideas?
Thanks,
TK
This looks like a bug. Can you call PSS on it.
If you are looking for a work around, study the transaction which caused
this sequence of commands. Sometimes if you set columns to "", you can get
some weird occurences. Most of these problems have been patched however.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"TK" <TK@.discussions.microsoft.com> wrote in message
news:7DAEE7FE-2CA7-495B-8E8D-4B65EB40BA30@.microsoft.com...
>I am replication on a SQL Server 2000 env on Windows Server 2003. I
>randomly
> get an access violation error stating "Syntax error or access violation"
> ODBC
> error 37000. After further investigation using sp_browsereplcmds I see
> that
> a blank field in the db is sending the following repeated about 30 times.
> ''+{fn CHAR(0)}+''+{fn CHAR(0)}
> I am using sql server generated procs. I also have 4 other publishers
> using
> the same table without an errors. I am looking for a solution rather than
> a
> work around. Any ideas?
> --
> Thanks,
> TK

Replication Issue

Hi All,

I have made Merge Replication using SQL Server 2000.Windows 2000 Server as Publisher and Windows 2000 Professional as Subscriber.

I am using Pull Subscription for that.

MSmerge_contents table is created on both publisher and subscriber database witch contains the information about the rows that are updated or inserted on any side.

Now we come to the problem.

tblDefProductPrice that contains the prices of products. I change the price of a single product so that when synchronization is done subscriber will get the updated price

Changing on row means only one row is updated so MSmerge_contents table should have only one row to propagate to subscriber but it is not when I saw the contents of MSmerge_contents table its showing the whole table with (8,000) rows.

I did not want because its extra work for publisher to publish 8,000 rows instead of propagating single row. In future it may increase to 80,000 that is loss of time and money.

Thanks for your kind help

FNKHi
I think you are misunderstanding the system table.
The merge agent will only replicate rows that have changed since the last synchronization - it doesnt, on each sync, replicate the whole MS_MergeContents table

des|||Thank you DesmondX for your kind response,

Yes you are right The merge agent only replicate rows that have changed since the last synchronization.

I have observed the contents of table MSmerge_contents last synchronized rows are present there but problem here is that it adds 8,000 more rows when I made changes in single row.

This problem is not faced with all tables in DB but only tblDefProductPrice table is behaving like this.

FNK|||strange...are there any foreign keys enforced on table, for replication?
if you do a select on matches on the rows rowguid, are their more than one match?
are there user triggers on the table?
when synchronization happens, does the agent report that it is delivering those 8000 changes?
des|||Dear DesmondX

Thanks for taking time to give solution to my problem.

I think i am not able to communicate the problem clearly. I will try again.

I have a table "tblProductPrice" in my DB. I insert some of the tuples, say 25, through SQL statement from VB code. When i do replication then the result shows "8000" updates, along with 25 inserts. This is equivalent to the total tuples present in that table. This table has got one foreign key. I have removed that contraint but the result is same.

There is another scenario. There is table "tblProductSeasonFitness". This has got two foreign keys but table has no direct relationship with above mentioned table of price. When i "Insert or Update" few tuples in this table (currently were are updating only two tuples at a time), then after replication there are "8000" updates in "Price" table mentioned above.

I have tried deleting the foreign key constraints but same result
I have deleted the price table, made new table, inserted the data again but same result

I am just wondering what may be the reason.

FNK|||Hi FNK
Guess we have to assume then that all the rows, at least in SQLs opinin, are being updated. Not sure if you are using .NET, if so, do you grab all the data as a dataset? Maybe when it returns the data it is mistakenly marking it as updated?
Are you sure the query you are sending is only affecting the necessary rows, as I have never had a problem like that in replciation (not that that writes it off!)?
If you have a test environment, try setting it up without replication and maybe use TIMESTAMP columns in the records to test which rows have been updated, after doing the exact same thing as you are doing that causes the problem.
Has your replication subscriber been properly initialized with the schema and data? If not it will regard everthing as a needed repl generation.
Also, test doing the update on the rows straight from Query Analyzer, to see if the prob lies in the App or SQL.
Hopefully by this you can isolate the area of the problem...
Good luck
des