Replication stategy advice

Hi,
I would like some advice to choose the correct replication stategy. I've got
an Oracle database which is copied accross to SQL server every morning. This
is done via SSIS delete/ insert. This process copies approx 130K rows from
oracle into SQL Server every morning.
Now the SQL server database is replicated to a Disaster Recovery server. At
the moment this is done via Snapshot replication every hour.
I would like to know what's the best strategy. Can I use transactional
replication instead of snapshot? Personally I don't see a big issue with 120K
rows inserted every morning, transactional should handle it. Then once this
is done, there aren't many updates during the day. Is Log shipping a better
option? What's the best strategy if you assume that the initial sync may
double or triple is size to 400K rows? If I use log shipping are there any
extra contrains I need to be aware of, ie don't allow truncate statements etc
on the database?
Thank you for your help.
Panos
By the way, this is on 2005. Can I also consider database mirroring in this
case? Thanks.
"Panos Stavroulis." wrote:

> Hi,
> I would like some advice to choose the correct replication stategy. I've got
> an Oracle database which is copied accross to SQL server every morning. This
> is done via SSIS delete/ insert. This process copies approx 130K rows from
> oracle into SQL Server every morning.
> Now the SQL server database is replicated to a Disaster Recovery server. At
> the moment this is done via Snapshot replication every hour.
> I would like to know what's the best strategy. Can I use transactional
> replication instead of snapshot? Personally I don't see a big issue with 120K
> rows inserted every morning, transactional should handle it. Then once this
> is done, there aren't many updates during the day. Is Log shipping a better
> option? What's the best strategy if you assume that the initial sync may
> double or triple is size to 400K rows? If I use log shipping are there any
> extra contrains I need to be aware of, ie don't allow truncate statements etc
> on the database?
> Thank you for your help.
> Panos
|||Panos,
please take a look at this article:
http://www.replicationanswers.com/Standby.asp which should help with your
questions.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Panos - yes, database mirroring is an option. However in your case I
wouldn't expect that the latency advantage will be enough of a reason to use
it. Also, you'll have to set up database snapshots to make it available
which can't be backed up and can't have FTI - I'll do a proper (article)
comparison some time soon.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks for this Paul very useful. A final question, if you are doing truncate
table statements on the publisher database, can you use this database for
logshipping/ mirroring?
The fact that Truncates don't register into the log doesn't affect the
Mirroring stategy? or if it is affected what can be done to sink Publisher &
subscriber? We would like to keep using truncate. My idea is when you
truncate the table in the publisher you can also truncate it in the
subscriber DB as it only happens by a scheduled job. Thanks.
Panos.
"Paul Ibison" wrote:

> Panos,
> please take a look at this article:
> http://www.replicationanswers.com/Standby.asp which should help with your
> questions.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||I think the answer is here:
http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?topicid=1&faqid=4
So the DBA at my last job wasn't very up to date?
Panos.
"Panos Stavroulis." wrote:
[vbcol=seagreen]
> Thanks for this Paul very useful. A final question, if you are doing truncate
> table statements on the publisher database, can you use this database for
> logshipping/ mirroring?
> The fact that Truncates don't register into the log doesn't affect the
> Mirroring stategy? or if it is affected what can be done to sink Publisher &
> subscriber? We would like to keep using truncate. My idea is when you
> truncate the table in the publisher you can also truncate it in the
> subscriber DB as it only happens by a scheduled job. Thanks.
> Panos.
> "Paul Ibison" wrote:
|||Panos - this is correct. From the point of view, truncate will be OK.
However, you won't be able to do a truncate if there is a FK declared on the
table, which usually precludes this operation for me.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

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 in SQL server2005

Hi there i need an advice on replication in sql2005

i've heared that i can't make replication between two cluster servers installed on them sql2005 and they are in different domains so please can you tell me is that true and if is what can we do to solve this because they are in different places and we can't give them the same domains of IPs

thanks

I"m going to move this thread to the replication group.|||I've never heard of that restriction. You can replicate between machines, regardless of whether they are even in a domain. You simply have to specify login credentials for each of the replication agents.