April 11, 2010 at 8:28 pm
Comments posted to this topic are about the item The Log Shipping Standard
April 12, 2010 at 1:16 am
I'm a fan of log shipping. It's a hardened bit of technology that works very well.
Having said that, isn't it time to improve on the file-based transaction log? Maybe something in-memory with a delayed write to disk? My data-mart ETL would love this. C'mon Microsoft, you can do it!!
James Stover, McDBA
April 12, 2010 at 6:00 am
James Stover (4/12/2010)
I'm a fan of log shipping. It's a hardened bit of technology that works very well.Having said that, isn't it time to improve on the file-based transaction log? Maybe something in-memory with a delayed write to disk? My data-mart ETL would love this. C'mon Microsoft, you can do it!!
Great idea! A CTRL-Z for us buffoons that occasionally forget that WHERE clause.
April 12, 2010 at 6:14 am
I agree that the ability to inject some latency is valuable, probably used less often than it should be!
April 12, 2010 at 7:04 am
Hi Steve
A great suggestion and good use for log-shipping. As an ex-DBA I was often called upon to fix an "oops" and one does need to fix a way of rapidly correcting a problem rapidly.
It does seem though that much of the SQL community seems to be moving toward mirroring as a high-availability option and less log-shipping and only seems to be retaining log shipping for purposes of multiple recipients etc..
With a product like SQL Virtual Database (http://www.idera.com/Products/SQL-toolbox/SQL-virtual-database/) a DBA can take the previous backup together with any number of offloaded logs and create a virtual database back to any point in time. All of this without having to actually create a physical database (with all of the space problems etc.) The virtual database just uses the backups and logs as its "source" but to a SQL Server instance the virtual database looks exactly like a real database and TSQL can be run to, for example join the table as it was to as it is now the determine changes to be inserted/updated without even having to take the production database offline. As an example: INSERT INTO TableA WHERE SELECT * FROM backup.TableA where NOT EXISTS (SELECT TableA WHERE…)
Brett Hawton
Idera Product Design
April 12, 2010 at 7:53 am
Tom Garth (4/12/2010)
James Stover (4/12/2010)
I'm a fan of log shipping. It's a hardened bit of technology that works very well.Having said that, isn't it time to improve on the file-based transaction log? Maybe something in-memory with a delayed write to disk? My data-mart ETL would love this. C'mon Microsoft, you can do it!!
Great idea! A CTRL-Z for us buffoons that occasionally forget that WHERE clause.
Any time you're running a script manually against a production database, you should ALWAYS wrap it in a transaction, and not issue the commit till you've verified the data is correct.
My usual practice is wrap and roll back, with verification scripts before the rollback. That way I can see what will be modified, without holding locks on tables/rows (which is what happens if you start a transaction and don't roll it back or commit it). Then, when I'm certain the script will do exactly what I need, I change the rollback to a commit, and run it.
If you're in that habit, a missing Where clause won't bite you.
- 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 12, 2010 at 9:57 am
We have been using that for the past 5 years with the FAT FINGER case in mind!
* Noel
April 12, 2010 at 10:10 am
I like the injection of latency into log-shipping. We could have used that in our implementation.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 13, 2010 at 11:31 am
GSquared's comment is a good one, but I'm still waiting for Microsoft or someone else to write a plug-in that warns you if you try to execute a query without a WHERE clause. GSquared's method of being able to roll back is nice, but it still puts a lot of stress on the server if, for example, the table you were working on has 10 million rows and you forgot the WHERE clause.
April 14, 2010 at 6:12 am
kevin77 (4/13/2010)
GSquared's comment is a good one, but I'm still waiting for Microsoft or someone else to write a plug-in that warns you if you try to execute a query without a WHERE clause. GSquared's method of being able to roll back is nice, but it still puts a lot of stress on the server if, for example, the table you were working on has 10 million rows and you forgot the WHERE clause.
In those extreme cases, you should be able to notice that the query is taking too long and cancel it.
- 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 14, 2010 at 8:09 pm
GSquared (4/14/2010)
kevin77 (4/13/2010)
GSquared's comment is a good one, but I'm still waiting for Microsoft or someone else to write a plug-in that warns you if you try to execute a query without a WHERE clause. GSquared's method of being able to roll back is nice, but it still puts a lot of stress on the server if, for example, the table you were working on has 10 million rows and you forgot the WHERE clause.In those extreme cases, you should be able to notice that the query is taking too long and cancel it.
In SSMS (2005), there is an option named "SET IMPLICIT_TRANSACTIONS" under Tools --> Options --> Query Execution --> SQL Server --> ANSI. The upside to using this is that your DDL's and DML's are rolled into a transaction without having to think about it. The downside is that you are likely to forget that it's enabled and wreak havoc on your database (that's never happened to me ;-)). I seem to recall this being a question on the MCDBA exam back in the day.
James Stover, McDBA
April 15, 2010 at 6:22 am
James Stover (4/14/2010)
GSquared (4/14/2010)
kevin77 (4/13/2010)
GSquared's comment is a good one, but I'm still waiting for Microsoft or someone else to write a plug-in that warns you if you try to execute a query without a WHERE clause. GSquared's method of being able to roll back is nice, but it still puts a lot of stress on the server if, for example, the table you were working on has 10 million rows and you forgot the WHERE clause.In those extreme cases, you should be able to notice that the query is taking too long and cancel it.
In SSMS (2005), there is an option named "SET IMPLICIT_TRANSACTIONS" under Tools --> Options --> Query Execution --> SQL Server --> ANSI. The upside to using this is that your DDL's and DML's are rolled into a transaction without having to think about it. The downside is that you are likely to forget that it's enabled and wreak havoc on your database (that's never happened to me ;-)). I seem to recall this being a question on the MCDBA exam back in the day.
Implicit transactions have the same drawbacks. If you're likely to forget "begin transaction" when implicit transactions are off, you're just as likely to forget "commit" when they are on. I'm not sure whether transactions that shouldn't be committed being committed is worse than ones that should be committed being rolled back, or locks being held indefinitely while you're at lunch with an open transaction you forgot.
- 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 15, 2010 at 9:11 am
GSquared (4/12/2010)
My usual practice is wrap and roll back, with verification scripts before the rollback. That way I can see what will be modified, without holding locks on tables/rows (which is what happens if you start a transaction and don't roll it back or commit it). Then, when I'm certain the script will do exactly what I need, I change the rollback to a commit, and run it.If you're in that habit, a missing Where clause won't bite you.
Yes, that's a really good technique. Including the rollback in the script is particularly important if you are using an unreliable internet connection (such things exist - whether because a particular location doesn't have decent networks or because the wrong people are managing the network configuration at the far end) because when the connection drops it may take hours to get connected again, and that makes the lock holding problem into a lock holding disaster. I use the technique a lot, but don't really have the habit (ie I sometimes don't use it) although I've been trying to make it an automatic reflex for a while.
Tom
April 15, 2010 at 9:25 am
Interesting article, and an interesting use for log shipping.
For a very long time I lived with SQLS 2000 Standard Edition, because the cost of Enterprise Edition was such that requiring out customers to have it would probably have priced us out of the market. So log shipping was out - that edition didn't support it. But (provided disc space is available) it is easy to use normal backups (provided you have full recovery model) to do the same thing - effectively recover as a new database to a point in time, and then replace the damaged data in the real database with good data from the new one (I've done that more times than I like to remember - a couple of them for my errors, far more for other people's - why is it that some app developers are so brilliant at writing SQL that scrambles or deletes the data the app needs?). I guess the new Virtual DB thing means you don't have to worry about backups any more, but with log shipping in Standard Edition for the newer versions of SQLS log shipping with enough latency may be a better approach.
One worry about log shipping with latency: if the prime purpose of log shipping is to provide a hot (or even warm) standby, too much latency is a bad thing.
Tom
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply