SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log Shipping vs. Replication


Log Shipping vs. Replication

Author
Message
Andrew Sears
Andrew Sears
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 358
Thanks for the clarification. Alarm bells went off when I thought log shipping wasn't syncing schema changes.

Is it possible to replicate schema changes & stored procedure changes and what is the best method if so?



george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23342 Visits: 13698
wavesmash (6/27/2008)
Thanks for the clarification. Alarm bells went off when I thought log shipping wasn't syncing schema changes.

Is it possible to replicate schema changes & stored procedure changes and what is the best method if so?


for log shipping think of it this way...........

ANYTHING that gets recorded in the transaction log of the log shipped database will get copied to the other side. that included schema changes, stored proc changes, new users, updates,deletes, inserts to ANY table (user or system)

what you wont get is changes not in the log shipped database, i.e those at server level such as logins, DTS or SSIS packages, SQLAgent jobs.

Replication will ONLY replicate inserts, updates and deletes to the user tables included in the publication.

---------------------------------------------------------------------
Andrew Sears
Andrew Sears
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 358
So there is no way to automatically sync table schema or stored proc schema changes during replication without using a 3rd-party tool?

With database mirroring, is a shared drive (SAN) a requirement?

thanks,
Andrew



george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23342 Visits: 13698
wavesmash (6/27/2008)
So there is no way to automatically sync table schema or stored proc schema changes during replication without using a 3rd-party tool?

With database mirroring, is a shared drive (SAN) a requirement?

thanks,
Andrew


not a replication expert, those sort of changes need to be done by replication stored procedures provided for the purpose (or via gui) I believe, so require manual amendments to replication set up when changes are made.

Mirroring does not care whether databases are on local disks or SAN, as long as the servers can communicate.

---------------------------------------------------------------------
mike.williams-791609
mike.williams-791609
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 21
this being my first read of the article, the following caught my attention:

"...generally I recommend log-shipping for fail-over, followed by transactional replication with queued updating subscribers. The order is reversed if there is a possibility of needing to go back to the production server ..."

The question came to mind whether or not Paul was indicating that the two different methods could be used in combination somehow, or was he simply stating that his first preference is log shipping and his second preference is transactional replication.

Can anyone clarify this for me?

If he was indicating his preferences in order, I wonder, subsequently, if it is possible to combine the methods for a sort of poor-man's version (e.g. no 3rd party utilities) of the best of both worlds? Or would this be the SQL equivalent to smoking crack?
Glen Sidelnikov
Glen Sidelnikov
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1707 Visits: 804
Sorry to add some confusion to the discussion, but...

It is absolutely unclear for me what is it that author was trying to achieve in his comparison?
Reading the second sentence I have found that "This article outlines the differences between them, for the purposes of maintaining a standby server"

It might be my misunderstanding, but I am not familiar with such a business requirement? I was always thinking about a high availability which is in order a part of a wider DR policy which could be a business requirement and standby server as a very narrow methodology which is a part of suggested high availability solution?

The very next sentence is adding to my personal confusion:
"To put this in context,log-shipping and replication are two methods which are often compared to clustering, however clustering is really a technology created to solve a different business requirement - that of automatic fail-over."

Automatic fail-over is not a business requirement as well. It is a part of functionality that could be listed as a partial requirement in the same HA policy. By itself automatic fail over means nothing... More than that I can implement clustering without fail over at all.

MS suggesting following HA solutions that includes (bolded for SQL 2005):

Database Engine

* SQL Server Database – Failover Clustering, Mirroring, Log Shipping, Replication
* SQL Server Analysis Services – Failover Clustering
* SQL Server Notification Services – Failover Clustering

Replication is the lowest choice in the tree of possible HA solutions. If functional replication requirements were included in the original design of the database, it is possible to recommend it as a partial HA solution.
Excluding clustering from the list of possible solutions for SQL 2000 is a mistake. Based on the author's words, one can think that clustering is implemented for automatic fail over only. At the same time a comparison of a top level functionality of clustering and replication is showing that cluster implementation will require less resources and what is most significant less administrative overhead (especially on complex SQL server installation), AND provide a MORE full HA solution than replication.
Personally I am not a fan of clustering solution. However, when making my recommendations to the customer, I am trying to keep in mind that this is just a tool. :-)
In my understanding a picture of comparison of HA tools will not be finished without bringing mirroring and ESPECIALLY third party tools (like XOSoft, for example).
Kenneth Igiri
Kenneth Igiri
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1049 Visits: 499
Has anyone worked on dealing with the schema changes made by replication (adding a rowguid column).

Br. Kenneth Igiriwww.scribblingsage.comwww.igiribooks.comAll nations come to my light, all kings to the brightness of my risingSmooooth
george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23342 Visits: 13698
note : 7 year old thread last posted to 3 and a half years ago - I highly recommend you post your question in a new thread (i.e add a new topic vis the forum tab)

---------------------------------------------------------------------
Kenneth Igiri
Kenneth Igiri
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1049 Visits: 499
Yeah thanks. That makes sense.:-)

Br. Kenneth Igiriwww.scribblingsage.comwww.igiribooks.comAll nations come to my light, all kings to the brightness of my risingSmooooth
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search