Search results matching tag 'Disaster Recovery'

SSMS 2012 Restore GUI Gotcha

merrillaldrich — Fri, 15 Mar 2013 16:30:07 GMT

Today I want to bring to your attention an issue in the SQL Server Management Studio 2012 restore GUI. In many ways the new restore dialog is nicer than the old one, with new features and added convenience – but, as is always the Achilles heel of GUI tools like this, if you don’t know what’s really going on it can bite you. I’m not sure what to call this issue, maybe just a UI design flaw. Technically it works as designed, and there’s nothing really wrong with it, so it’s not a bug. But I can imagine it really causing someone pain who is careless or doesn’t know what’s happening behind the scenes.

Restoring a copy of a database make take the original down.

Among the new features is some management of “Tail-Log Backups,” which is a wonderful idea. Taking a backup of the tail of the log will take down the database and put the whole end of the log into a backup file, preserving basically all modifications to the DB from the log. This is ideal, for example, at the last step of migrating a database using log shipping, because all the activity is preserved and the source database is locked against further modifications. The log chain is preserved in moving to the new copy of the database, or in a DR scenario, to your other server.

The problem is, I know a lot of people don’t know this. Often its people who use the GUI a lot, such as “accidental DBAs.” I think the GUI is really important despite those who rarely use it, or frown on it.

Here’s the issue: Suppose I have a production database that has some user-created problem (like a user accidentally updated some rows.) It may be reasonable to restore a copy of the database to a point before the issue and investigate whether it’s possible to merge the data back in. The original database isn’t damaged from a technical point of view – there’s no corruption, for example.

Right-click the original source database and choose Tasks > Restore > Database … from the context menu.
The resulting dialog will go out and discover the backup files for that database from MSDB, which is very helpful.
Change the name of the target database, in order to restore it as a copy.

There’s a note at the top of the dialog that, while accurate, may not help some in this scenario. It says, “A tail-log backup of the source database will be taken.” Sounds innocuous if you don’t know what that implies. In past versions, restoring a copy of a database would never affect the original – or not by default, anyway – so I cringe as I imagine people merrily clicking past this warning.

The script you get with these settings is indeed composed with a tail-log backup of the source database:

That will, as indicated, take a tail-log backup – and thereby take the original database down. Nice if you wanted that, but a mean surprise if you didn’t.

If you act on the warning and click the Options tab, and then uncheck the offending setting, you do get the expected behavior (the original database is unaffected because the tail-log backup is not included at the top of the script):

So, be careful out there!

Paul Randal’s Disaster Recovery Step-by-Step Poster

Argenis — Thu, 07 Feb 2013 18:09:00 GMT

If you were looking for Paul Randal’s [Blog|Twitter] DR poster and couldn’t find it anywhere on the net, I have good news for you: I saved a copy on my PC before it was taken down, and (with Paul’s permission) wanted to make it available here.

So, without further ado, enjoy the poster.

Microsoft Windows Azure Disaster Recovery Options for On-Premises SQL Server

BuckWoody — Tue, 08 Jan 2013 14:40:00 GMT

One of the use-cases for a cloud solution is to serve as a Disaster Recovery option for your on-premises servers. I’ll explain one particular use-case in this entry, specifically using Windows Azure “IaaS” or Virtual Machines as a Recovery Solution for SQL Server (more detail here: http://www.windowsazure.com/en-us/home/features/virtual-machines/). In future installments I’ll explain options for other workloads such as Linux and Windows Servers, SharePoint and other solutions. Some architectures also allow for using Windows Azure SQL Database (Formerly SQL Azure) in recovery scenarios; I’ll cover that separately.

Using Azure as a Disaster Recovery site gives you a range of options, uses world-wide datacenters that you can pick from, and does not require traditional licensing and maintenance paths. You can also integrate the offsite data into other uses, such as reporting (in some cases) or to leverage within other applications. However, the cost-model is different, so make sure you do your homework to ensure that it makes sense to use a cloud provider for safety. You may find that it is cheaper, more expensive, or that you require a mix of technologies and options to get the best solution.

NOTE: The Microsoft Windows Azure platform evolves constantly. That means new features and capabilities, as well as security, optimizations and more improve on a frequent basis. As with any cloud provider, ensure that you check the date of this post to ensure you are within six months or so. If the date is longer than that, then check each of the “Details” links to ensure you are working with the latest information.

The options you have range from simple off-site storage for database backups to systems that your users can access when your primary options are offline. To select which options to use, evaluate the databases you want to protect, and then create your Recovery Point Objectives (RPO) and Recovery Time Objectives (RTO) for each workload. Those two vectors will provide the starting point for each choice you make.

NOTE: If you’re not familiar with RPO and RTO on a database system, learn those terms carefully before designing a recovery solution – on any platform. RPO and RTO are business/technology terms, and are not vendor or platform-specific. http://wikibon.org/wiki/v/Recovery_point_objective_-_recovery_time_objective_strategy

The range of protection you have is very similar to the on-premises options for SQL Server (on-premises details here: http://msdn.microsoft.com/en-us/library/ms190202.aspx), with the primary limitation being bandwidth. While Microsoft has the largest connections we can get into our datacenters, depending on where your systems are and their connection to the Internet, you will need to consider how much data you transfer, and how often. For backup files, a single, larger transfer is acceptable, using Log Shipping or Database Mirroring, smaller, more frequent transfers are preferable.

Another limitation is controlling the hardware on the Windows Azure Virtual Machine. That means hardware-based clustering isn’t possible, as of this writing. You’re also limited to the size of the Virtual Machines that Windows Azure (or any other cloud provider) offers. It’s important to keep in mind that you’re building a Disaster Recovery solution, not necessarily a full Highly-Available system. The difference is that in this case DR provides a means to recover and operate at a more limited fashion than a full on-premises HA (with matching hardware and licenses) involves. Storage, however, isn’t as affected. You can mount large amounts of storage on a Windows Azure Virtual Machine, so it’s more memory and CPU that you need to consider for your solution.

The final consideration is security. There are two aspects in security that you need to consider: data security and authentication and access. For the first consideration, the Windows Azure system does hold multiple certifications and attestations that you can find here: . In some cases those certifications are agreements on the part of security each party will hold liability for; so it’s important to carefully read and understand what the agreement states. There are also methods of encrypting data (such as the backups) using your own certificates or hardware devices and then storing them externally. This means no one can easily un-encrypt your data.

For the authentication portion, you can create a secure “tunnel” between your network and Windows Azure. This involves a certificate that is installed on your hardware firewall at your facility, and an agent that is enabled with the same certificate on Windows Azure. This gives you a “point to point” connection, encrypted but over a public connection. From there you can use Active Directory to connect the authentication for the systems involved in the DR solution.

Backups

The First and most simple DR solution using Windows Azure is to store your backup files (*.bak) in Windows Azure storage. Windows Azure Storage is triple-redundant across multiple fault-domains within a single datacenter, and then all three copies are replicated to a geographically separate (although data-sovereignty same) location. That translates to six copies of data stored remotely. In case of a disaster, you connect to storage, download the images, and restore them to a new server. The server can have the same name or different, and unless you’re using contained databases, you’ll need to re-create and re-authorize the security accounts needed for the database.

Note that you also have the option of using an “appliance”, which is a piece of hardware you install at your facility which will act as a backup device or share location (or both). The device handles the encryption, de-duplication and compression for the files, and then stores those files on Windows Azure. More information on that option is here: http://www.storsimple.com/

RPO: As of last backup

RTO: (Time of transfer from Windows Azure + Time of Restore to New System + Bringing System Online with User Accounts) - Time of Backup

References:

More detail on storing files on Windows Azure: http://sqlblog.com/blogs/sqlos_team/archive/2013/01/24/backup-and-restore-to-cloud-simplified-in-sql-server-2012-sp1-cu2.aspx

Free Client: http://azurestorageexplorer.codeplex.com/

Database Mirroring

Database Mirroring is a deprecated feature in SQL Server, which means it will be removed in a future release. It is, however, still supported in SQL Server 2012, and it can be used between on-premises SQL Server Instances and Windows Azure VM’s. Using connection strings and .NET languages, clients can actually point to the partner server automatically.

The granularity of this solution is at the individual database level. Machines can retain their individual identities. You can use certificates to connect the systems, or you can use the point-to-point solution and Active Directory.

There are limitations, however. You won’t use a Listener in this configuration, and you’ll be using Asynchronous mode. If you are not running in the same Active Directory, you’ll also need to factor in the time to re-create and tie out those accounts when calculating the RTO value.

RPO: As of last good synchronization

RTO: (Time of failure + Time of client redirect to New System ) - Time of last good synchronization

References:

A complete tutorial on setting up this configuration is here: http://msdn.microsoft.com/en-us/library/jj870964.aspx

Log Shipping

Another feature available for DR in a Hybrid fashion is using Log Shipping, which also protects your system at a database level. Log shipping involves an automated log backup of your database, and the log is copied and then applied at the secondary server. Because the log file is copied to a Windows share, this solution requires both networking access and an Active Directory integration.

RPO: As of last good log backup application to the secondary system

RTO: (Time of failure + Time of manual client redirect to New System + Time of Manual Failover ) - Time of last good log backup

References:

Log Shipping information is here: http://technet.microsoft.com/en-us/library/ms187103.aspx

AlwaysOn Availability Groups

SQL Server 2012 introduces a new set of features called “AlwaysOn” that encompass many of the HA/DR features in previous releases. One feature within that set is called “Availability Groups”, and with certain caveats that feature is available for a Hybrid on-premises to Windows Azure VM solution.

AlwaysOn requires a Windows Cluster (WFSC), which is where the caveats come into play. You’re able to set up a multi-subnet WSFC cluster, but you won’t have access to the Availability Group Listener function, so you need to consider the client reconnection.

RPO: As of last good synchronization

RTO: (Time of failure + Time of manual client redirect to New System + Time of Manual Failover ) - Time of last good log backup

References:

A complete tutorial on setting up this configuration is here: http://msdn.microsoft.com/en-us/library/jj870959.aspx

High-Availability White Papers and Resources for SQL Server

KKline — Thu, 26 Jul 2012 15:00:00 GMT

In foreground, attendee makes dreaded "shoot myself" hand sign to the speaker.

I was just telling the good people of Charlotte about how they (and how YOU) need to read all things by Paul Randal (blog | twitter), except for all of his cheesy romance novels like Caress and Conquer written under the nom de plum of Connie Mason.

There's lots more good stuff from Paul, just not romantic.

This is a 'so-last-version' whitepaper describing five common high-availability and disaster-recovery architectures deployed by customers, along with a case study of each. Although the white paper is specific to SQL Server 2008 R2 and isn't updated for AlwaysOn features, it's still really, really good. It covers:

Failover Clustering for High Availability with Database Mirroring for Disaster Recovery
Database Mirroring for High Availability and Disaster Recovery
Geo-Clustering for High Availability and Disaster Recovery
Failover Clustering for High Availability Combined with SAN-Based Replication for Disaster Recovery
Peer-to-Peer Replication for High Availability and Disaster Recovery

You can get it from this link. Not everything is transferable to new AlwaysOn technologies, but then again AlwaysOn is an Enterprise Edition feature. So the database mirroring recommendation can be upsized, in many if not all cases, to SQL Server 2012, while the SAN and peer-to-peer recommendations continue to hold fast.

In addition, I encourage you to get up to speed on AlwaysOn. There are two great AlwaysOn FAQs that I recommend. The first is Microsoft’s official AlwaysOn FAQ at http://msdn.microsoft.com/en-us/sqlserver/gg508768.aspx. The second comes from my buddy and high-availability expert Allan Hirt (blog | twitter) at http://www.sqlha.com/2012/04/13/allans-alwayson-availability-groups-faq/.

To get started with AlwaysOn, check out http://msdn.microsoft.com/en-us/library/cc645581.aspx.

Enjoy,

-Kev

Bug-Out Bags and Cloud Architecture Considerations

BuckWoody — Fri, 20 Jan 2012 17:00:58 GMT

I served in the U.S. Military for a while, and as part of my training we had to maintain a “Bug-Out Bag”, which was a large duffle-bag full of certain items that we could live on/fight with in an emergency. I’ve carried the spirit of that idea forward with me into civilian life, in Florida and especially here in the Pacific Northwest.

In Florida we dealt with the threat of hurricanes - I went through four of those in one year that hit my area. You’re without power, it floods quickly, and it gets wicked hot. You roof might be gone, whatever. Here in the Pacific Northwest, I live near one of the largest volcano's in the world, we have flooding, and recently we were hit with an ice-storm. Now I’ve lived all over the world, from Alaska to North Dakota and even near the Kamchatka Peninsula in Russia, and I can handle the snow. But ice - that’s a toughie no matter where you live. We had so much that it split my little pine tree in front of the house in half.

We lost power - although I think the folks at Puget Sound Energy did an amazing job at getting us back up in less than 24 hours, but we weren’t worried anyway. That bug-out bag mentality carried forward to a “second pantry” we keep in the garage.

We have a large plastic box (that will fit in the back of the Subaru) with dried goods like pasta, and canned goods and even a little cook stove. We have 25 gallons of clean water in Jerry-Cans. We have batteries, candles and matches. And we have flashlights around every door. We use supplies from the “pantry” to fill our house pantry, and then refill the emergency one from the grocery store. That way everything is fresh, rotated, and we can “bug-out” here at home or on the road.

So what does this have to do with Distributed Computing Architectures?

It’s the thought process. In both the military and civilian life, I’ve done a few things:

Sat down and thought carefully about exactly what I need. Did I include a can-opener? A small shovel to dig out of whatever I got stuck in? Then I weed out what I *really* don’t need.
Put those things into a small, manageable container.
Tried them - even when (especially when) I didn’t have an emergency
Tweaked the process to see what I could do better.

Have you done this when you moved an app to the “cloud”? Each of these has a computing parallel - do you know what you would do if you couldn’t access the Distributed Computing Environment?

I’ve found these thoughts are actually a great place to start - keeps the process simplified from the start, and gives you a sense of assurance when you’re asked if you can recover from an emergency.

T-SQL Tuesday #19: Blind Spots

merrillaldrich — Tue, 14 Jun 2011 05:31:59 GMT

A while ago I wrote a post, Visualize Disaster, prompted by a real incident we had at my office. Fortunately we came through it OK from a business point of view, but I took away an important lesson: it’s very easy, whether your organization and your team is savvy about disaster recovery or not, to have significant blind spots with regard to recovery in the face of some large, unexpected outage. We have very clear direction and decent budgets to work with, and the safety and recoverability of applications and data is a real, primary objective at my workplace – and still this was a take-your-breath-away, eye opening kind of experience. Here are some common places I have seen such blind spots in my past work. Perhaps you can have a look around you and see if you see these wherever you are today, and maybe some ways to combat them:

Invincibility Blind Spot

I think most of us have worked at a place where the leadership is just oblivious to the idea that anything damaging could happen to their business from an IT failure. I did some consulting at a place where one of the owners flat-out told me, “I’m not sure all this technology really helps us. It’s certainly not essential. We could go back to pencil and paper and be just fine.” I knew just from watching their operation for a short time that if they lost their technology, they would probably go straight out of business. Their server was the type you see pictures of as jokes on the web – in an un-conditioned room, with an oscillating fan aimed at it, on a rickety shelf, shared password, etc.

This can be hard to combat. A typical organization like this would look at ideas like backup or disaster recovery, and immediately balk because it “sounds expensive” or “there isn’t time for that, because it’s not real work.” And they aren’t always small companies – one place I did work for had 1500 employees and essentially no DR strategy other than some half-hearted tape backups.

The only way I have gotten traction with these cases is to do two things:

Make sure the leadership hears the argument for DR from someone they trust and that has credibility with them. In some cases, that was me, once I built trust working with them. In other cases the argument, right or wrong, had to come from someone else – perhaps another business person, not even in I.T. – for it to carry any weight.
Once you have that voice that carries real weight, walk the leaders of that organization through a visualization of what could really happen if they lost their infrastructure: the sending people home, the loss of credibility with customers, the real, no-hand-waving, no-magic amount of time it would take to recreate a functional system, the work lost. It has to be real, and it has to burst that imaginary bubble that can surround computer technology and make it seem like it’ll just somehow keep working. A building fire is usually a good scenario, because you don’t have to be in IT to relate.

Ego Blind Spot

The Ego blind spot is somewhat trickier. The place this can lurk is with capable IT staff who do have a mandate to make DR work, but whenever they are approached about discussing DR or testing their systems, may become defensive or make excuses. There can be an undercurrent in the conversation that insinuating that DR isn’t “covered” by their systems is some sort of an insult. Often that undercurrent actually comes from insecurity – there may really be gaps in their systems that they privately worry over, but don’t want to crack open and solve because either a. it’s embarrassing or b. they don’t relish the extra work and risk it could take to reconfigure a running system. These folks generally have the best intentions, but getting at the gaps in the technology can be a real problem, just because of personalities.

Here the only remedy I know of is sociological – the business continuity leader (or the IT team lead, if it’s the same person) has to have the leadership skills to win these folks over. The technical staff have to be in a position where finding the DR gaps and improving their systems is something they perceive will provide an opportunity to demonstrate, and not threaten, their skills. It has to feel like a worthwhile project. It’s almost impossible to get at the underlying problems any other way. The leader in this scenario will need their technical expertise, their on-the-ground view of how systems really work to even locate the issues, and for that, grudging cooperation will not do. Working DR has to become a real part of the staff’s fully owned, personal priorities. If the person who knows the low-level detail about how a system works is armed with DR know-how, and committed to making DR work, the gaps will disappear. If, on the other hand, the technical people don’t want to see the gaps, and the leadership isn’t capable of seeing the gaps, the gaps will remain until some incident exposes them.

Magic System Blind Spot

This is an interesting one – the Magic System blind spot is essentially a blind faith that some of the latest gee-whiz tech is the silver bullet that will save everything. “We have DR covered because we virtualized.” “We have DR covered because we replicate.” “We have DR covered because we load balance.” “Disaster can’t touch us – we have a SAN!”

“Cloud.”

I’ve seen naïve, young people succumb, I’ve seen leadership (the ones out of touch with the technology, generally) succumb, but surprisingly I have also seen savvy people I would never have expected succumb to this.

The remedy here looks simple to a staffer, but maybe difficult to a leader: no matter what a vendor claims or advertises, what we imagine a magical system can do, you must have someone available who, impartially, knows how that technology works enough to dispel the magic. All this stuff works for a reason. Using dedupe? Make sure someone on staff understands how that really works. Snapshots? How. Relying on virtualization for DR? Exactly how does that work? Only by unpacking how these systems do what they do can you be sure they will work at crunch time.

Devil in the Details Blind Spot

Lastly, we have the blind spot that is the nemesis of us all. The one present in every organization, extremely difficult to stamp out, “When we fail over, when the data center goes down, will it work?” This is a simple question, but here’s why it is so difficult: every system has so many moving parts, each of which perhaps requires specialized knowledge, and a seemingly small detail that nobody thought of can absolutely wreck the DR process when you have a real incident. It’s very easy to have a scenario where practically everything works except that one tiny thing that prevents it all working – the database is there, the web servers are up, we have network connectivity and name resolution but everyone forgot that the encryption key to the whoozit has to be loaded into the whatchacallit. It’s really easy to miss something. And because the something that was missed is small, maybe nobody took it very seriously.

Remedies for this are more difficult. For some organizations with the finances, it might be possible to actually run multiple data centers and, in fact, fail production systems between them. That would ensure the design is sound. Most of us, though, have to use test systems and then just try our darnedest to be really careful.

If you can’t test with production systems, the next best thing would be to have a pre-prod or staging system that is comparable to production where you can do rehearsals. Such a rehearsal can be a drill around some imaginary scenario, say “It’s 5:00 am and Data Center A is on fire. (This is a drill.) Go.”

Failing that, the only recourse – and it’s much less accurate – is a careful and detailed tabletop visualization. Visualizations like this are great, and valuable, if they are run well. Vital ingredients:

Effective leadership that can persuade people to check egos at the door and take it seriously. Without buy-in, you never get to the details that matter.
A facilitator that can ask relevant but probing questions, in order to eliminate the inevitable hand-waving that masks gaps in the system. Example: “At this point we would load the logins into the DR SQL Server.” The facilitator should not say “OK.” She should say “From where? How? Who?”
Detail. Everything in a reasonably sane organization works at a high level. It’s only by diving into the details and making a visualization real that you uncover those small, system-breaking gaps.
Note takers. In every tabletop I’ve attended, a huge number of issues were uncovered, and in order to get the most value, it’s important to capture them all right then, in the room. Otherwise they escape!

Do you see one of these four blind spots in your organization? Others? Any tips or processes for stamping them out? I’d love to hear.

SQL Azure Use Case: Shared Storage Application

BuckWoody — Tue, 26 Apr 2011 13:33:50 GMT

This is one in a series of posts on when and where to use a distributed architecture design in your organization's computing needs. You can find the main post here: http://blogs.msdn.com/b/buckwoody/archive/2011/01/18/windows-azure-and-sql-azure-use-cases.aspx

Description:

On-premise data will be a part of computing for quite some time – perhaps permanently. Bandwidth requirements, security, or even financial considerations for large data sets often dictate that relational (on non-relational) systems will be maintained locally in many organizations, especially in enterprise computing.

But distributed data systems are useful in many situations. Organizations may wish to store a portion of data off-site, either for sharing the data with other applications (including web-based applications) or as a supplement to a High-Availability and Disaster Recovery (HADR) strategy.

Implementation:

SQL Azure can be used to add an additional option to an HADR strategy by copying off portions (or all) of an on-premise database system.

In this arrangement, on-premise systems remain as they are. Data is replicated using many technologies, such as SQL Server Integration Services (SSIS), scripts, or Microsoft’s Sync Framework to a SQL Azure database. This data can be kept “cold”, meaning that a manual process is required to bring the data back, or as a “warm” standby using connection string management in the application.

Recently we architected a solution where a company kept a rolling two-week window of data replicated to SQL Azure using the Sync Framework. The application, a compiled EXE running on user’s systems, had a “switch connections” button, that allowed the users to take a laptop to another location, select that option, and continue working from anywhere they had Internet connectivity. This required forethought and planning, and did not replace their primary HADR systems, but it did allow them to continue operations in the case of a severe outage at multiple sites. Since they are an emergency services provider, this gave them the highest redundancy.

Another option is to amalgamate data from disparate sources.

In this arrangement, two or more data services (one of which is SQL Azure) are accessed by a single program. The program queries each system independently, and using LINQ a single query can work across all of the data, assuming there is some sort of natural or artificial “key” that can join the data sets together. The user programs simply view this single data set as a single data source, unaware of the underlying data sets. This allows great flexibility and agility in the downstream program. The upstream data sources can change as long as the elements are kept consistent.

There are performance and security implications to amalgamated data systems, but if architected carefully they provide multiple benefits. A few of of these are that other systems can access the individual data sources, reporting is simplified and standardized, and multiple copies of data are eliminated.

Resources:

You can read more about the Sync Framework and SQL Azure here: http://social.technet.microsoft.com/wiki/contents/articles/sync-framework-sql-server-to-sql-azure-synchronization.aspx

If you are new to LINQ, you can find more resources on it here: http://msdn.microsoft.com/en-us/library/bb308959.aspx

Scandalous II: Shh! I am De-duplicating Compressed Backups

merrillaldrich — Sat, 23 Apr 2011 05:01:50 GMT

This is part II of two Scandalous posts. Watch, mouth agape, as I run with scissors, right up against prevailing wisdom! Unfollow me now, before it’s too late!

Here’s the thing. There are two really outstanding posts out there on the ‘tubez that explain in vivid detail the problems with sending compressed data into a de-duplicating appliance. And these guys are both absolutely right. Everything in their posts is correct, and I would ask that, if you haven’t, you please read them before mine:

First, Brent Ozar:

http://www.brentozar.com/archive/2009/11/why-dedupe-is-a-bad-idea-for-sql-server-backups/

(And, may I say, well done on the Numero Uno Google result for that post. Very nice!)

Next Denny Cherry:

http://itknowledgeexchange.techtarget.com/sql-server/sql-backup-compression-and-backup-dedup-are-mortal-enemies/

(A very respectable #3 on the Google-ometer.)

Now, I’m not kidding. These guys know their stuff, and they are right. Stop reading right now.

Still here? Ok, now come closer.

Closer.

Shh.

I studied this whole thing very carefully, and I do it anyway.

While it’s true that de-duplication works poorly with compressed data, and if you compare the de-dupe ratios for “usual” uncompressed files with the de-dupe ratios for compressed files, the compressed data looks very, very bad. But there’s even more to this story, so much more that we decided to, in a limited way, stuff the compressed files into our DDR anyway.

Here’s why:

Both SQL Server backups and file compression are a deterministic process. If you back up the same database twice, and it has the same data pages in it, and those pages are largely unchanged, then the backup files will be substantially the same. This is true if you compress both files with the same algorithm and settings, too – the data in the compressed files will be largely identical. It will not be like any OTHER files on your network, but the two files will be similar to one another.

If you change a small percentage of the data pages in the data file, that will still be true: a compressed backup of the database on, say, Monday will be mostly the same as a compressed backup of the same database, with modest changes, on Tuesday.

What that means is that if I have a 1 TB database, which I do, that produces a 250 GB compressed backup file, and that database receives mainly incremental changes from day to day or week to week, then each successive backup will be similar to the previous one. And if I copy them into a de-duplicating store (at least the one I have to work with) then, while the first file will be basically 100% net new data, the second will de-dupe against the first. It’s not as effective as other types of files, but it does help. Let’s say, for argument, that I get 75% de-duplication of only the two files, instead of the normal 85%+ across many instances of other files, I am still getting 75% de-duplication, and that can be very useful.

Useful how? Well, we have SAN replication married to our de-duplicating store for offsite backup and disaster recovery. That means that each night I have to transmit a LOT of SQL backup data across a WAN to another site. What’s a lot? For me, that just means the pipe is small and the data is much bigger. And that process would go a lot faster if, somehow, by magic, a whole lot of the data were already at the other end of the pipe before I start.

See where I’m going with this? With de-duplicated files, as days and weeks pass, each time we replicate new files from one site to the other, a whole lot of the data is already there at the other site. We only have to transmit the net new data. Even if that’s only 50% (a very poor performance number for de-duplicated storage in most people’s minds) that’s still cutting the data in half. Which is pretty good. Plus it’s compressed, which helps every other aspect of the backup story.

So we have what I think is a good compromise, born out by internal testing:

Keeping compressed SQL Backups in de-duplicated storage indefinitely, as a replacement for tapes, is impractical. It’s just too expensive. So we keep the SQL Backups in there only for the purpose of DR, and we have a pretty aggressive purge schedule to be rid of old files. The sweet spot seems to be to keep only a week or two.
We use tapes too, for archival purposes, and they have longer retention.
We back up to local (DAS or SAN) disks first at the SQL Server and then copy into the de-duplicating store, so that the backup process performs well and isn’t bottlenecked at the network or at the speed the appliance can receive the files. So backups go to disk, then get copied into the de-dupe store, cancel against whatever is in there, and then it replicates them off site.

This is not a cheap setup, but it works great. I love it. That 250 GB file I mentioned is available at my other site in a couple of hours, because it’s always mostly there already. Your mileage may vary depending on all the specifics of the technology you have, and, as I said, Brent and Denny are right.

* Professional driver on a closed course; don’t try this at home; no animals were de-duped in the production of this post.

Visualize Disaster

merrillaldrich — Tue, 08 Mar 2011 05:21:00 GMT

Or, How Mirroring Off-Site Saved my #Bacon

My company does most things right. Our management is very supportive, listens and generally funds the technology that makes sense for the best interest of the organization. We have good redundancy, HA and disaster recovery in place that fit our objectives. Still, as they say, bad things can happen to good people. This weekend we did have an outage despite our best efforts, and that’s the reason for this post. It went pretty well for my team, all things considered, but we’d put in a lot of preparation.

It’s a Matter of When and How, not If.

If you work in this industry it’s very likely that you will be hit with something like this at some point. It’ll come when you don’t expect it, in a way you probably didn’t imagine. What separates the women from the girls, so to speak, is how ready you are for the unexpected when it happens. In sports there’s a technique called “positive visualization” or “mental rehearsal” where a skier, or other athlete goes through his or her whole run beforehand, imagining each gate and how they will successfully navigate the course. As far as I have seen it’s quite effective for creating the right mindset to have a successful run, and it’s something I use in planning for failover or disaster recovery. I thought I’d take a minute, fresh from my recent hair-graying experience, to remind everyone to go through this exercise.

Imagine Your Datacenter is Gone

For this to be effective, you have to really go through it in detail, like the skier imagining every gate. You get a phone call, and find out every server in your primary datacenter is offline, and no one knows exactly why. (I am altering the scenario here a little to respect the privacy of my company, but in reality the reason doesn’t matter.) Let’s say the UPS caught fire, or the sprinklers went off, or someone forgot to put diesel in the generator, or there was an earthquake, or a meteor. Fact is, you had a highly redundant infrastructure with failover clusters and backup power and RAID and dual power supplies and redundant everything and still your whole site went down.

It happens. A lot.

First, are you in a company that has not invested in a DR strategy and is totally unprepared? If so, then I would strongly suggest going through this exercise, at a high level, with your boss. An outage like this can and does put whole companies out of business, and if you have a CIO, he or she might not be the CIO afterward.

Really Do this Detailed Thought Experiment

Let’s say, for the sake of argument, that your datacenter will be partially offline for four days (I am improvising here, and you can too, but make it realistic). Run through these visualizations, and imagine how you handle it in your environment:

Do you have your data? How much data is lost? The servers are offline, and you’re not getting anything from their local drives. Not full backups, not log backups. Nada. Tape drive is there, but has no power. Is there an up-to-date copy at another site? Or recent backups at another site? If so, what’s the recovery point (ie. how old is it) and what would that mean for your business?
But don’t stop with the data -
If you have another server at another site, can all your applications and/or users actually connect to it? Connection strings in place? How do you repoint everything so that the failover server can be used? It’s easy to gloss over this issue, but think it through in detail: will you have to have staff go and change individual machines to connect to another server? This can be a really sticky problem.
Is there enough bandwidth between your users and your DR site to sustain the normal workload?
So you have data, and clients that can point to it. Can they authenticate? Are all the logins in place from the old server? Remember, you can’t reach the old box, can’t query it – there is no way to take a look at this point and see what logins were there. How do you recover authentication? If you are using SQL auth, what about all those passwords? Even if you use mirroring or log shipping, the login issue can still take your system down.
Now, hopefully, you have connections and users authenticating. What about those SQL Agent jobs? Are the jobs all present on your failover machine? How will you know?
Use SSIS in your environment? Where are the packages? Do those fail over to your DR site too? How are the DR copies kept current?
Keep going like this down every subsystem you use – replication, ETL processes, reporting, etc.

The answers to all these questions will be different for you, but the importing thing is that there should be real, working answers. In the middle of the night, sitting at the keyboard, you can’t be figuring this stuff out.

I am pretty happy with what we have built at my office – though I am careful not to get complacent. We use a combination of mirrored failover clusters (that is, failover clusters in two datacenters, with database mirroring synchronizing them over our WAN) and de-duplicated backup files that are SAN-replicated offsite to provide two channels of offsite backup. For some older systems we are still on log shipping, but plan to advance those to mirroring as soon as our ISVs and budgets permit. We use some custom SQL Agent/PowerShell jobs to propagate logins and jobs between the sites – which jobs we had to actually use this weekend for the first time. I am happy to report, they worked. Whew.

On the other hand, I came from a company that was much more reckless, and effectively had no failover strategy whatsoever. They were frankly just too cheap to do it. Looking back, it terrifies me, because I know how simple it could be for them to lose their whole business.

Edit 3/11/2011

With the earthquake in Japan, this is in the forefront of many people's minds today. Please have a look at these other posts, esp. Paul Randal's survey:

http://www.sqlskills.com/BLOGS/PAUL/post/Surveys-DR-plan-testing-and-considering-human-nature.aspx

http://www.sqlha.com/blog/

Could be a good time to approach the boss about this important topic.

Pop Quiz: Restore a Database to the Point in Time when a Full Backup Started?

merrillaldrich — Mon, 01 Nov 2010 18:26:00 GMT

Recently we've had to interview some SQL Server DBA candidates for our team, and we were looking for the type of open-ended technical questions that would draw out interviewees and let us get a sense of their thought process. A surprisingly simple question came up that seems to work well - it makes the candidate think through and explain some internals of SQL Server in a way that I think reveals whether or not they have a handle on fundamentals about the transaction log and backups. The question is simple:

You have a full backup that kicked at 7:00 pm last night. You have to restore the data in the database to 7:00 pm last night. Can you do it with that full backup file? If so, why does it work? If not, why not? And how do you get it back to that 7:00 pm state?

The "why" bit is the most interesting part. I like a candidate that gets the answer wrong, but with the right reasoning about the log, better than one who get's it right but can't explain why. And the worst answer is obviously, "Sure - a full backup just has all the data as of the time it started!"

What's your favorite question?

Search results matching tag 'Disaster Recovery'

SSMS 2012 Restore GUI Gotcha

Paul Randal’s Disaster Recovery Step-by-Step Poster

Microsoft Windows Azure Disaster Recovery Options for On-Premises SQL Server

Backups

Database Mirroring

Log Shipping

AlwaysOn Availability Groups

Other Solution Options

High-Availability White Papers and Resources for SQL Server

Bug-Out Bags and Cloud Architecture Considerations

T-SQL Tuesday #19: Blind Spots

Invincibility Blind Spot

Ego Blind Spot

Magic System Blind Spot

Devil in the Details Blind Spot

SQL Azure Use Case: Shared Storage Application

Scandalous II: Shh! I am De-duplicating Compressed Backups

Visualize Disaster

It’s a Matter of When and How, not If.

Imagine Your Datacenter is Gone

Really Do this Detailed Thought Experiment

Pop Quiz: Restore a Database to the Point in Time when a Full Backup Started?