Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Log Shipping vs. Replication Expand / Collapse
Author
Message
Posted Friday, June 27, 2008 9:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:59 PM
Points: 11, Visits: 352
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?



Post #525098
Posted Friday, June 27, 2008 9:27 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:21 PM
Points: 5,975, Visits: 12,883
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.


---------------------------------------------------------------------

Post #525109
Posted Friday, June 27, 2008 9:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:59 PM
Points: 11, Visits: 352
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



Post #525114
Posted Friday, June 27, 2008 9:44 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:21 PM
Points: 5,975, Visits: 12,883
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.


---------------------------------------------------------------------

Post #525121
Posted Friday, June 27, 2008 11:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 8, 2012 10:54 AM
Points: 13, 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?
Post #525217
Posted Monday, June 30, 2008 8:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 5, 2013 12:19 PM
Points: 371, Visits: 797
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).
Post #525979
Posted Saturday, January 7, 2012 4:32 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, July 20, 2014 3:52 PM
Points: 54, Visits: 256
Has anyone worked on dealing with the schema changes made by replication (adding a rowguid column).
Post #1231982
Posted Saturday, January 7, 2012 4:59 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:21 PM
Points: 5,975, Visits: 12,883
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)

---------------------------------------------------------------------

Post #1231987
Posted Sunday, January 8, 2012 11:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, July 20, 2014 3:52 PM
Points: 54, Visits: 256
Yeah thanks. That makes sense.
Post #1232120
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse