April 10, 2008 at 5:26 am
hi friends,
can you help me in log shipping and transactional replication?
what is the difference between log shipping and transactional replication?
thanks,
Shikha Srivastava
April 10, 2008 at 7:18 am
Per Books Online:
"Transactional replication typically starts with a snapshot of the publication database objects and data. As soon as the initial snapshot is taken, subsequent data changes and schema modifications made at the Publisher are usually delivered to the Subscriber as they occur (in near real time). The data changes are applied to the Subscriber in the same order and within the same transaction boundaries as they occurred at the Publisher; therefore, within a publication, transactional consistency is guaranteed."
"Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled."
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 10, 2008 at 10:37 pm
hi,
thanks for your ans but i want pinpoint difference between Log Shipping and Transactional replication.
you have given me thhe defenition of these two so please help me for the same.
Thanks & Regards,
Shikha
April 11, 2008 at 10:38 am
the log shipping will restore a backup on the secondary server while the replication does kind of a real time replication of the changes on your primary server.
April 11, 2008 at 11:51 am
Those definitions are the differences.
Transactional Replication copies on a transaction-by-transaction basis. It's near real-time (mainly depending on network latency).
Log shipping sends a backup of the log over and restores it. It's only as up-to-date as the most recent log that was shipped over.
Those are the differences, exactly as per the definition.
Is there something more specific you need to know about?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 3:56 pm
I always think of replication as 'scale-out' and log shipping as scale-up.
i.e if you want to a copy of the database so more people can use the data from multiple locations use replication, if you want it for high availability (resiliience) use log shipping.
With log shipping the destination database is not really available for use (at best read only and out of use with every restore of a log), With replication the database is always available and can be updateable.
with replication you have the limiting factor of replicated tables requiring a primary key. With third party apps this may not always be the case.
In 2005 you now have the third option of mirroring, which is scale-up, so like log shipping, but it is on a transaction basis, rather than transaction log basis.
---------------------------------------------------------------------
April 11, 2008 at 6:05 pm
Transaction Replication :
Creates new database in system databases : Distribution Database
All replicated table should have Primary Key
Need to deal with Identity Columns/Triggers
Creates new SPs ( 3 Sps of one table)
Latency should be in sec (Depending upon connection)
Sends Transactions
Users can connect to destination Database
Log Shipping
No New database
No need to change any table
Creates Transaction Logs, Copy transaction log to destination and restore them.
Destination database would be in Standby mode or NoRecovery mode
User would not be able to connect during restore
April 12, 2008 at 9:39 am
And of course one more major difference -
In log shipping everything that happens within the primary database which is logged is transferred to the secondary, so adding users, tables, dropping the same, changes in system tables are replicated across
in transactional replication the above is not true, only DML changes to the tables you have published in the replication will be replicated. You cannot publish system tables so things like adding users will never be replicated
---------------------------------------------------------------------
August 5, 2008 at 10:36 am
Broadly Speaking,
Transactional Relication is Logical Standby Database.
Where as Logshiipping simulate Physical Standby Database.
So, In Transactional Replication you have to add articals (ex.as new Tables is created )..
April 8, 2009 at 9:49 pm
Here are some differences:
Log-Shipping Replication
What is the latency? >1min Potentially as low as a few seconds
Is the schema altered at the publisher? No No Except Updating subscribers
or merge
Is the schema altered at the subscriber? No It is Possibly
Are there schema requirements? None Primary keys required
Can we select individual articles? No Yes
Is the subscriber/standby database 'protected'? Yes No
Is system data transferred? Mostly No
Is the subscriber/standby server useable
as reporting server? Unlikely Yes
November 16, 2010 at 2:38 am
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply