Search results matching tags 'Best Practices' and 'Backup'

The All-New 'Database Lifecycle Management" is available on MSDN

KKline — Fri, 05 Apr 2013 10:51:00 GMT

The initial release of Database Lifecycle Management is now available on MSDN.

The site is something called "curated content". This means it's a single consolidated location to look up lots of disparate articles and content, all in one easy to search location. This “curated content view” contains the best content, video, and community-centric information from Microsoft, including topics like:

SQL Server Data Tools

· Get started with sample projects, code samples

· Video demos by Gert Drapers (blog)

· Script common data portability tasks using Sqlpackage.exe

· Link to the SSDT team blog

SQL Server Management Studio

· Manage SQL Database using SSMS

· Backup and restore w/ SQL Azure

· Migrate local databases to Azure

· Video demo of hybrid scenarios by Gert Drapers (blog)

Windows Azure SQL Database

· SQL Database backup and restore

· Import/export SQL Database

· Windows Azure training kit

· Connection management and troubleshooting connections

Enjoy,

-Kevin

-Follow me on Twitter!
- Google Author

Quick Tip - Speed a Slow Restore from the Transaction Log

KKline — Wed, 14 Nov 2012 15:59:00 GMT

Here's a quick tip for you:

During some restore operations on Microsoft SQL Server, the transaction log redo step might be taking an unusually long time. Depending somewhat on the version and edition of SQL Server you've installed, you may be able to increase performance by tinkering with the readahead performance for the redo operations. To do this, you should use the MAXTRANSFERSIZE parameter of the RESTORE statement. For example, if you set MAXTRANSFERSIZE=1048576, it'll use 1MB buffers.

If you change the MAXTRANSFERSIZE, keep an eye on the PerfMon objects for Buffer Manager and Readahead IO. You may also wish to keep an eye on LOGBUFFER wait stats.

I'd love to hear your feedback. Have you tried this technique? Did it work as advertised? Did it require some changes to work on a specific version or edition?

Many thanks,

-Kev

-Follow me on Twitter!

Must-Have Resources - SQL Server Backup & Recovery

KKline — Thu, 08 Dec 2011 20:36:00 GMT

One of the things that drives me crazy as I'm getting older is that my brain is losing the capacity to differentiate version numbers. As I speak travel around speaking with customers and at conferences, I find my self saying things like "I can't recall if this problem was fixed in SQL Server 2000 or 2005. But you don't have to worry about that any more." Or things like "That feature was added in SQL Server 2008 R2, eh, or was that version 6.5. DOH!" followed by a loud slapping sound as I whap my palm into my forehead. The Internet doesn't forget either. Recommendations that were once helpful, if not outright essential, now are neutral or even downright bad. So now, whenever I put together new presentations, I always spend a lot of time in research, reassessing my knowledge on the topic. (That doesn't mean that I'll extemporaneously say it wrong, because I speak in an off-the-cuff and rapid style. But at least my notes are usually correct).

Case In Point

Take backup and recovery (B&R), for example. I've been writing about and performing B&R for years. I'm even part of the team that builds the most popular B&R tool in the SQL Server space. It's an extremely important part of what I do. And yet, even after spending a couple days re-researching topics (yet again), I still manage to get a few of the more specific details wrong because of changes over the years in the feature set. How so? During an Expert's Perspective webcast last week (and available on-demand) covering the top backup and recovery mistakes on Microsoft SQL Server, I mis-explained the details concerning how SQL Server performs a differential backup. A very clever SC on our team who was also attending the webcast pointed out to me after the session that I'd described differentials working at an 8k page-level when, in fact, they work at the extend-level (that's a block of eight 8k pages). #FACEPALM! When describing the differential backup I said that a bit was flipped on each page header and SQL Server would only back up those pages. Instead, whenever a page is changed a bit is recorded on the differential change map (1bit per extent), the backup process then queries these map pages and backs up those extents that have been marked as changed.

Watch the on-demand video of Performance Tuning SQL Server for Backups and Restores, with me, Iain Kick, and Brent Ozar (blog | twitter). Free, but registration required.
Watch the on-demand video of Ten Incredibly Painful SQL Server Backup and Recovery Mistakes, with me and David Gugick.

The Research Reveals

The cool revelation here is not that I can admit my mistakes. The take-away from this blog post are all the great articles I read writing my latest slide deck. These blogs and articles were so good that I needed to spread the word. Add these to your must read list:

Virtual Log Files must be tamed!

Performance impact: a large number of virtual log files Part I and Part II by one of my favorite not-so-prolific bloggers, Linchi Shea (blog). The thing I love about Linchi's content is its rich benchmarking information. Few SQL Server experts out there routinely test the behavior and performance of specific SQL Server features like Linchi does.
Slow recovery times and slow performance due to large numbers of Virtual Log Files, by Graham Kent (blog) of Microsoft Sweden, shows that VLFs impact not only the processing time of transactions but also B&R processes.
Lots of VLFs are Bad, by Tony Rogerson (blog | twitter), was the article that first got me interested in this behavior.

Backup and Recovery Myth Busting

Breaking the Backup Chain - Redux (Or 'Eating Crow'), by Michael K. Campbell (blog), shows that out-of-band database backups break differentials not transaction log backups.
Backup with COPY_ONLY, How to Avoid Breaking the Backup Chain, by the eminent Paul Randal (blog | twitter), uses clear writing and first-class demos to illustrate the principle of breaking backup chains in Microsoft SQL Server. Other great blogs of a related nature from Paul include:

Other Good Sources

And just in case you need a refreshing in the basics of SQL Server backup and recovery, check these out:

Best Practice: Backups

Denis Gobo — Sat, 14 Jul 2007 13:40:00 GMT

What if I told you to take your latest production backup, restore it on a different machine and try using the database? Are you comfortable with that task? Do you think it will work? When was the last time you tested your backups?

Do you even have a backup?

Why am I asking all these things? Because your data is as good as your last good backup. Is your data backed up regularly? You will say “Of course it is we use [Insert expensive backup solution here] for all our enterprise backups”. Prove it, go to work on Monday and ask them to give you the latest backup. I bet out of a 100 people who ask this question to their backup team there will be several people without a backup file.

Here is another problem: three years ago the backups were taking about 1 hour. The backup started at 12 it would be done at 1, at 1:30 a job from another machine would ftp the file down. Two years later the backup takes 2 hours to complete, you didn’t realize this. Can you guess what will happen if you try to restore once of those backup that were moved by FTP? I will tell you it won’t work. What if there is no backup and you do a FTP? Oh yes the 0kb file will be created.

Where do you keep your backups?

Are you backups in the same building? If you would say yes then you have a big problem. Let me tell you a little story. I worked for a company in New York City between 2001 and 2005. This company had their office in WTC tower one. To be safe they kept their backups in WTC tower two. Well I don’t have to tell you what happened with the backup. If you do store your backup offsite (and why wouldn’t you?) make sure it is at least 100 miles away. If you don’t want to go that far from your current location then pick a location which is safe from floods, fires and not worthy to attack.

Where is your Source Code?

Do you backup your source code? Most people will say they keep it in Subversion or Visual Source Safe. But does that get backed up? What happens if your building goes up in flames? What we do is we have a full source code backup every day. In addition to that we also have differential backups every n revisions. We have jobs that create these backups and then FTP them to 3 different locations. If you have 20 developers and you lose 6 hours of work then you have lost 120 * $$ (you do the math). This is the best case scenarios. If the backup was in the building together with all the workstations then you got a lot bigger problem to deal with.

SQL developers are notorious for not using source control. They will tell you that the database backup is their source control. A source control system does not have to be expensive; we use Subversion (which is free and better than VSS). You can either use Tortoise or the plugin for Visual Studio to do your check ins.