August 11, 2006 at 4:48 pm
As someone else noted the article was a bit old!! Replication is always difficult for GUI users but if you script then making changes to objects is a doddle. I'm not sure your article really mentioned the issues you get with making changes to a database which is replicated, e.g. in prod systems with controlled software releases you'll find index changes and such can be problematic. I've log shipped since 6.0 and it's a cool way to provide recovery, I've found generally 5 mins is about the minimum log ship, especially if you have many databases on a server - which is of course the problem with all such solutions - ideal for one of two databases but when you have 20 servers and a couple of hundred databases that pipe can get mightly narrow and the distance to your DR site can increase latency.
That said sql 2005 has some really cool slants you can apply so a follow up for 2005 might be good.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 27, 2008 at 12:36 am
Just thought I'd my ten cents worth. A case for Log shipping as a reporting solution.
Replication is obviously the preferred method of ensuring Data is in Sync. But here is an example of where log shipping works as providing the reporting server. We are currently using a particular MS application, supposed Enterprise financial application whos name would seem to engender a sage like quality. Unfortunately runs like a 'hairy goat'. Normal operation of posting invoices, entering timesheets would grind to a halt whenever someone ran a report, user satisfaction of zip. I had to separate the reporting from the running system. As the application has never been 'Certified for Replication', management are wary of me offering replication as a soloution. Hence the solution. I set up a log ship sequence to an other server this occurs every six hours, which the Business at the moment deemed an acceptable lag in data. If there was any body running a report the restoration job would fail - normal. This is where a little magic comes in. I set the re-try to once every 1 minute for 1 hour. In the restoration job I check if there are any connections that have their last batch time less than the longer than the average of the longest running report. If there are the I force the restore job to fail (select 1 from XXX) - and wait 1 minute and try again. As soon as all connections are older than the expected average then the SPID's are killed. If after one hour there are still connections still active -- All are killed, the restore is done. Log shipping complete. I know this is not optimal but the refresh times are gazzetted and I have found most people have accepted this process. No, not all are satisfied and these are managed by exception, the rest are happy campers.
Code On 😛
June 27, 2008 at 6:17 am
Hi Paul,
Great article. If schema changes are not copied during log shipping, what is the best method to maintain updates to scripts and tables with large databases where full backups may take hours to copy to a DRP site, even with a log compression solution.
thanks,
Andrew
June 27, 2008 at 9:07 am
wavesmash (6/27/2008)
Hi Paul,Great article. If schema changes are not copied during log shipping, what is the best method to maintain updates to scripts and tables with large databases where full backups may take hours to copy to a DRP site, even with a log compression solution.
thanks,
Andrew
wavesmash, schema changes are copied over by log-shipping, its one of its big advantages.
Paul, good article. As a firm believer in log shipping I am glad to see you recommend it over replication for failover. I would have been even more forceful in this recommendation, I see replication as more for scale-out, and log shipping as much the better failover\DR solution. Only circumstance I personally would ever recommend replication for failover is if absolutely no data can afford to be lost. even then if you get the tail of the log, log shipping won't lose you any data, and if there is a delay before the restore on the standby could give you a way of backing out incorrect amendments to the data.
---------------------------------------------------------------------
June 27, 2008 at 9:10 am
Is it possible to ship logs from a database that is a replication subscriber? I have tried this and although the log shipping jobs complete successfully, I am unable to update a record on the subscriber and see the changes on the standby server after a restore of the transaction log.
:unsure:
June 27, 2008 at 9:19 am
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?
June 27, 2008 at 9:27 am
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.
---------------------------------------------------------------------
June 27, 2008 at 9:30 am
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
June 27, 2008 at 9:44 am
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.
---------------------------------------------------------------------
June 27, 2008 at 11:26 am
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?
June 30, 2008 at 8:29 am
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).
January 7, 2012 at 4:32 pm
Has anyone worked on dealing with the schema changes made by replication (adding a rowguid column).
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
January 7, 2012 at 4:59 pm
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)
---------------------------------------------------------------------
January 8, 2012 at 11:08 am
Yeah thanks. That makes sense.:-)
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply