One of the changes coming in SQL Server v11 (not sure what the name will actually be), also known as “Denali”, is the addition of more High Availability/Disaster Recovery Options. When I first heard about this, I was excited, but the public CTP had limited support for it. I’m hoping the next CTP will substantially improve the feature and this makes it to the RTM.
In the meantime, here’s a Channel 9 video from Justin Erickson that gives a short explanation of how this works and how it compares with the current Mirroring/Log Shipping/Clustering options.
I can avoid this kind of logging, but not SQL Server's kind.
As I have been studying more about SQL Server internals this year, one of the myths that keeps being debunked is the idea there are operations in SQL Server that are not logged. That’s not true as everything you do in SQL Server is logged to the transaction log in order to ensure that SQL Server remains ACID compliant and cannot end up in an unrecoverable state.
I’m not sure where this myth started, but I suspect that some version of the telephone game is at fault. I constantly see people asking for a way to insert or delete data from SQL Server without affecting the transaction log. One of these wish list requests likely got transformed into an imaginary feature that many people think has to exist in the product. Surely there is the capability to not log things if they are deemed unimportant.
There isn’t, and you can’t, and I’m not sure that I’d even want this feature. Logging introduces overhead, and it can slow down processing, but it also provides a tremendous amount of safety and security. Computers will crash, power will get turned off, and hardware will fail. Mr. Murphy will ensure that one of these will occur while a large import of some sort is taking place. I don’t know how the rest of you feel, but personally I would prefer that when my database is restarted, I can be sure that my data is in a consistent state. Transaction log logging ensures this is the case in SQL Server.
We often make decisions based on the data sets we have, and for the most part we want to be sure that all the data we added, updated, or removed from the system is in a known state. We do not want to accept partial transactions being recorded. We need to be sure that every debit into an account is matched up with a credit out of another account. Too many people seem to think that either their systems will never crash, or that this reliability can be guaranteed without logging.
Accept the overhead of logging, and be glad that it’s unavoidable. At some point in your career, I’m sure you’ll be grateful.
This presentation is designed to cover some of the basic mistakes that I find people making quite often when working with SQL Server. It is a mix of development and administrative items, designed to help beginners get a grounding in those skills that often cause the most problems in SQL Server.
One of the things that is important for developing a high availability or disaster recovery is knowing what commitments you need to honor for your clients. After all, if you don’t know how quickly you need to be back up, or what protections for data are required, how can you plan to recover systems?
Yes, I know everyone always wants 100% uptime and 100% data recovery, but those things are very expensive, and sometimes not possible on you systems, so doesn’t it make sense to have some idea of the relative importance of data in each system?
For this Friday, I wanted to ask the DBAs out there about the contracts that should exist between the technology infrastructure and Its business clients. The question this week is:
Do you know what your SLA is?
Do you actually have a written contract for various systems with the clients that spells out specifically the acceptable uptime and data loss parameters? If so, do you know what those numbers are?
You do need to have some idea of what is acceptable, even if it’s not a guarantee in a contract. It’s necessary in order to efficiently plan what level of resources will be devoted to your systems. Without this, you might be making investments on the wrong systems, leaving those systems that are more important from the business perspective, exposed to more data loss than can be tolerated.
Voice of the DBA 