Hey. I've logshipping implemented right now. I'm confused as to why should I
not be using Replication instead. Are there any benefits of using Logshipping
in place of replication. I understand how replication works and I know how
Logshipping works but is one better than the other. Also, what should decide

Hey. Thank you for the prompt reply. I understand the differences you have
mentioned but are there any significant changes between these two? Let me ask
you this. I've logshipping setup going from Server A to Server B. On this db,
the average trans backup file every 10 minutes is 400mb. So, our secondary
server does do a lot of restores for a long time and then I defrag all tables
on this db every other day. This creates log backups of about 30gb, 15gb etc.
But the total backup size during this reaches more than 60gb. And after this,
our secondary server stays out of sync for about 10 hours at least. Will
replicaiton be any better than this??? The amount of data being sent to the
subscriber and the time for the subscriber to catch up to the publisher.

Also, our db is about 60gb, obviously in a full recovery model. What would
be a good option to chose if we wanted to somehoe do defrags(can't reindex)
on this db. We can't have a downtime. It's also being logshipped. What would
be the suggested options in this case?

Thank you,

Tejas

Log shipping "replicates" the entire database including system objects. In
SQL 2000 it will replicate ddl (schema changes).

For log shipping you are really limited by the number of dumps you do. This
can be as low as each minute, but in practice is every 5 minutes. Your
destination server must be read only and is not available as your logs are
applied. There are certain events which can break the log shipping chain.

For replication - especially transactional replication the subscriber is not
read only. You need a pk on each table and your latency is smaller than with
log shipping. You can't replicate system objects easily, but you can
replicate subsets of the data and change the data on the fly.


--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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



[quoted text, click to view]

Well, THAT was a completely confusing mixture of terminology.

Log shipping doesn't "replicate" anything. Log shipping works with
transaction log backups. Of particular note is that everything that hits
the transaction log will be moved with log shipping. This includes not only
your user transactions, but also maintenance such as reindexing. Reindexing
is an ongoing issue with log shipping, because it introduces large
transaction log backups and increased latency during periods of the day.
Log shipping is also an all or nothing. You get everything in the database
including users and permissions. You can get log shipping to run stably on
an interval as low as 2 minutes which is pretty common. That introduces a
latency and potential data loss than will generally average ~4 minutes.
When you are using log shipping for availability, the secondary is always
configured with norecovery, so it is not usable by anyone else.

Replication uses various techniques to duplicate transactions between
databases. The big difference is that it only sends DML and possibly DDL
statements between publisher and subscriber. So things like reindexing have
zero impact on the volume of data being exchanged. The other big difference
is that replication will not pick up security settings and it can be
configured to only send a portion of the database (subset of tables, subset
of columns in a table, subset of rows in a table). Replication can also be
configured to allow changes to occur on the subscriber and get pushed back
into the publisher whereas log shipping is a one way only operation.

Placing the database in simple recovery model will break log shipping, it
has no effect on replication. Deleting a transaction log backup before it
is applied to the secondary will also break log shipping, but has no effect
on replication.

The other big difference? System-wide impact. Log shipping leaves the
primary and secondary completely disconnected. Something happening on the
secondary can not cause issues on the primary. The worst thing that could
possibly happen is that your transaction log backups accumulate in a
directory. When you implement replication, you can binding multiple
databases into a system. Things going wrong on the subscriber can impact
the publisher. A case in point is implementing transactional replication.
If the subscriber is offline, the transaction will continue to accumulate in
the distribution database. If this occurs for long enough, you can blow out
of disk space causing the distribution database to go offline. That
prevents the log reader from writing to the distribution database, so your
transaction log continues to grow and can not be cleared out causing a
cascading error. Is this issue easy to produce? No. Very few people would
ever have a system hit that type of state. But, I have seen it triggered at
a couple of customers who had massive transaction volumes while also being
crunched for disk space. In one case, we blew through several hundred GB of
disk space in less than an hour causing the entire environment to die.

--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.


[quoted text, click to view]

If you switch your process from dbreindex to indexdefrag (assuming SQL
Server 2000 here), you can minimize the impact on log shipping during your
index maintenance. That is because dbreindex will create massive
transactions in the log. Indexdefrag is accomplished via a large number of
"micro-transactions". Every row movement in an index is a separate
transaction. This allows your transaction logs to be smaller. You will
still send the same overall amount of data, but each log can be smaller.
This keeps the secondary a bit more up to date than if you were using
dbreindex, which will decrease (but not eliminate) the latency.

--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.


[quoted text, click to view]

It seems that replication will work very well for you on the basis of what
you describe. The problems you will face are

1) replicating schema changes. These can be done through sp_repladdcolumn
and sp_repldropcolumn, however it will be a lot of work for you and there
will be some schema changes you will be unable to do through
sp_repladdcolumn and sp_repldropcolumn. You may be able to kick all your
users off the publisher, drop all subscribers, script the publication, drop
the publication, make the schema changes on both sides, recreate the
publication, recreate the replication stored procedures, apply them on the
subscriber(s), and then do a no-sync.

2) all tables will need a primary key (assuming you are doing transactional
replication of course, which is a best fit for what you are trying to do).

With logs of the size you are talking about log shipping can be difficult,
and another problem with it is that if you break your log shipping chain you
will have to start from a new backup.

I would run to SQL 2005 if I were you as this allows you to replicate schema
changes.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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



[quoted text, click to view]

Tejas,
as well as Hilary's answer, please take a look at this article:
http://www.replicationanswers.com/Standby.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)