Encryption is a tough subject, but it’s one that’s fascinated me for years. I downloaded PGP when it came out nearly two decades ago, and learned about the intricacies of symmetric and asymmetric keys. I thought it was fun, even when I couldn’t convince many people to consider using it.
Last year at PASS I ran into Michael Coles, who I’ve known for a few years and had just finished tech reviewing a book for. He was planning on a new book and asked me if I’d be interested in doing another one, this one on encryption. I jumped at the chance, and sometime this past spring started getting chapters.
I knew a lot, but had to learn a lot as well. I spent a lot of time Googling various terms and double checking historical references as well as algorithms and terms. I think I did a good job, and Michael and his co-author, Rodney Landrum, did a fantastic job.
I found a package on my desk this morning, and opened it to find my copy of the book. It’s now out, and available in bookstores, including Amazon.
So check it out, and learn a bit about this very important topic for DBAs. Not a lot of companies use encryption, but the ease of administration has grown with SQL Server 2008 and more companies are using it every day. I expect to see it start coming up in interviews soon.
The more I think about this, the more I think it should be built into SQL Server as a default. Why not ask for the information to create an operator and email at install and then create a few nice default alerts, like one for which log is full.
It's such a common problem, why not get people to enable DBMail and enter their email address during installation? For the people that really need to lock down their servers, they could skip it, but for the average person, it might save some headaches if they knew when their log was 80% full.
If you want the general steps for creating an alert, it's on MSDN.
We've had a few articles on how you move databases, but it seems that there are always new people using SQL Server and they end up posting a question because they aren't sure how to do something.
The other day I ran across someone trying to copy their database to a new server. They'd tried to "Copy Database Wizard" and the Copy Objects" in DTS (SQL 2000), but weren't getting default. Likely that's a bug in those processes, which I believe are the same code.
I mentioned detaching and attaching your database, which the poster wasn't aware of. Surprised me, but I'm sure many people don't know how. I decide (another) blog on the topic couldn't hurt with some simple instructions.
Now you have your data moved, all your code, defaults, rules, contraints, etc. Now logins need to be handled. There are a couple ways to do this.
1. If you care about passwords (keeping them the same), use sp_help_revlogin to script out the logins from the old server.
2. run sp_change_users_login to sync up the logins from #1 with your users, or to just create new logins on the new server if you didn't want to mess with the scripting.
With my hard drive failing in the laptop recently, I scrambled to do something. I was on the road, and didn't have a lot of choices, especially as I was in a weird place. I was on vacation, and I didn't have super immediate needs, but I did have a commitment to speak on behalf of SQLServerCentral at the Richmond SQL Server and .NET Users Groups.
So I did a few things. I grabbed a netbook, which would give me short term help, and also I tried another solution I'd read about. I got a Black Widow, external SATA dock and a copy of Acronis True Image Home version along with a new SATA drive. I managed to get my laptop to boot, and then copied off the latest versions of my documents to an external drive.
NOTE and WARNING: Your hard drive will fail at some point. Make sure you back things up. A cheap, 1TB external USB drive costs about US$200. Get one, and periodically copy things off. I'd done this before my trip, but I was 4 days late in changes to a few things. I was lucky I could get the latest versions of things.
Once I was sure things were working, I took a deep breath. At this point I didn't see the external USB drive because it was completely blank. The hardware showed up, but no guarantees there was a drive there. I probably could have tested it somehow, but I was pressed for time with family calling for things. So I inserted the Acronis CD in the drive and booted to it.
When it starts, it's a GUI, and it's slow. It's loading off CD, and that gives you new appreciation for how well things work off a disk drive.
It's mainly a backup program and gives you lots of options. I had to move down to the clone disk option. The guy at Best Buy said I'd want to walk through the advanced selections to choose the new size of the partition. My old drive was 160GB and the new one was 320GB. I could live with two partitions on the new drive, but I've prefer one.
So I walked through, choosing the source disk and target disk, setting the partition to be the whole disc. Read carefully as the options were slightly confusing to me. I managed to get this right, and things are confirmed in your settings.
My screen didn't look like this one above, as I'd set one large partition for the target drive.
Once I started it, I had a couple graphs on the screen showing progress. The estimated time on my Qosmio F40, dual core 2.2GHz machine with SATA drives, was about 3 hours. So I moved on with vacation, slightly worried about it. I returned late that night, but was afraid to make the switch at that time. I rebooted the laptop, and it was working, so I left things alone. Well, that and I didn't have a screwdriver to work on the laptop. I packed up the new drive in it's plastic casing and went to bed. I had an early 100 mile drive down to Richmond the next day.
When I got time in the afternoon to try things, I removed the drive from the laptop with it's case and then put the new drive in the casing. I slipped it back in the slot at the front of my laptop and rebooted to find ...
The Acronis CD program starting.
I was confused, but then I realized that it was booting from CD. I switched the drives back, which was a slow process. And the same result, which didn't surprise me as the drive was failing. So, I left things alone, grabbed the netbook and a copy of the presentation on flash drive, and went to talk. I'm glad I did that as I had issues with the netbook as well.
When I returned, I opened the laptop and looked at it awhile. I mentally went through the steps and realized I'd inserted the drives upside down. I quickly reversed the original drive, praying that it hadn't damaged the connectors, and it worked. The SATA connection looks fairly symmetrical, so that's good. I then removed the drive, changed out the old for the new in the case and re-inserted it, and I had my laptop back, quicker, and with an extra 150GB of space.
Since then I've been using the new drive and it has performed flawlessly. All of my stuff works, the settings, the configuration, security, everything is as it was. I was skeptical that this would work, thinking there might be some system partitions or things, but the Acronis software has run very well. If you need a backup of your hard drive, want to put in a larger one, or have a drive failing, I highly recommend this process. My cost:
For a total of US $190. That's much cheaper than a new laptop, and it's certainly worth the time it saved me in rebuilding a new drive and installing things. I'm actually tempted to make an image of this hard drive periodically rather than back things up to ensure that I can recover if something happens.
It seems that I see more and more posts about people trying to shrink their transaction logs. It's getting to be close to the time when I need a cut-and-paste snippet stored in a file I can pull out for my standard "it is recommended that you do not shrink the data or log files" post response.
The vast majority of the people that want to shrink their logs do so because they're running out of disk space and they realize that their log file is 25x the size of their data file. I'll see a 500MB data file and a 12GB log file all too often because they've set up full backups (good move) and not log backups (bad move). I won't go through all the scenarios or the advice that should be given, mostly because I think I've typed it all a few times in the last few weeks.
The more interesting question is how do we try and prevent this from happening?
The default recovery model is full, which means log backups are needed. Should SQL set up backups by default? I am starting to lean towards this being a default as a part of creating a database. Something should ask for, or perhaps set up, a backup scheme that makes sense. The backup folder should be set at installation, and a warning popped up if it's on the same drive letter as the data files. Then a standard nightly full, every 4 hour log backup should be set up. Those are some guesses I'd made, and they could be changed to something that might be better suited to most databases.
And we'd need an option to disable this default for those people that have automated processes in place to handle backups.
That, of course, isn't so easy to set up for Microsoft. You need Agent running, you are now making it hard to create a database, etc. Maybe a pop-up at the end of a database creation? Maybe a standard "to do" or reminder that comes up in SSMS whenever a database has 2 full backups wtih no log backups?
There are any number of ways to handle this, but I think that the educational aspect of the need is the biggest problem. It seems that without some heavy education, or some IN-YOUR-FACE dialog, maybe in Visual Studio, the issue isn't reaching enough Accidental DBAs and developers.
I've been tech editing a book about SQL Server encryption and this series of blog posts looks at some interesting things I've found when going through the book.
The Service Master Key (SMK) in SQL Server is the root of all encryption operations. It's the key that's used to encrypt all other keys below it, or at least encrypt those keys that are used to encrypt something else.
Surprisingly, there's no CREATE SERVICE MASTER KEY command. There is an ALTER SERVICE MASTER KEY command, which is used to work with this key.
Instead, the Service Master key is automatically created when you enable certain operations. For example, if you create a Database Master Key (DMK), if there is no SMK, it is automatically generated. It is then used to encrypt the DMK on the instance. If you enable Transparent Data Encryption (TDE), then an SMK is created if it does not exist. If you create a linked server, an SMK is needed to encrypt the password.
There are a few other operations as well that create the SMK, but the important thing for you to understand is that this key is at the root of all encryption, and needs to be protected. That means backup of this key, and secure storage. There is a Backup Service Master Key command, and you need to make sure you use this and store a copy of this somewhere that you can get to it in a DR situation. Putting it on the root of the instance host might not be a secure place, and honestly I think you need some secure place that you centralize all of your keys so that they are available along with, but separate from, the backups.
Inside the instance, or on the host, the SMK is protected by the Windows DPAPI mechanism, which should be secure. If it's not, then likely nothing is secure in the computer.
Other posts:
We're DBAs, right? We're concerned with data, with stability, and perhaps most importantly, backing up and restoring our data. We expect to be able to do this, and at a fairly granular level. I can back up a table or a view (through bcp), a stored procedure (scripting), a file group, or a database.
As I was checking facts in the encryption book I stumbled upon this caveat. You can't back up an asymmetric key!
There's not even a backup asymmetric key command in the T-SQL reference!
I was stunned and looked all around to try and figure this out. After all, I can create an asymmetric key from a file. You use some other program to create the key files, like sn.exe from the Visual Studio resource kit, but you apparently can't get this key back out of SQL Server once it's in there.
It is in a database backup and restore, so that's good, but if I wanted to use this key in another database, or perhaps more importantly, I wanted to put the public key in one database, say an order entry system where I take credit cards in and encrypt them with this key. I don't have the private key, but I move that key to a second system, which the order entry people don't have access to. I then transfer the encrypted data between systems and can decrypt it with the private key on the second system.
If I've created my asymmetric keys in SQL Server, I can't easily do this. Can't do it for DR if I need to get data decrypted, just have to be extra sure I have a good database backup and I can use it to decrypt the data.
There's a backup certificate command, a backup master key command, and even a backup service master key command (though strangely no create service master key command), but no backup asymmetric key command.
I think this is an oversight in SQL Server encryption (as is the lack of CREATE SERVICE MASTER KEY) and ought to be corrected. Hopefully it will in the future. In the meantime, I would not recommend you creating asymmetric keys in SQL Server. Instead, create them in Visual Studio and then protect the files.
I’ve been managing backups of my video files using USB hard drives for a year, and I’m fed up. I currently have 3 drives, with video, and pictures, spread across all three, none backing each other up, and files off my various machines. To add more hassles, I also have issues with my XP desktop not allowing people to log in remotely and access files. My wife used to back up onto my desktop, or grab photos off there. I’m sure that’s not something that’s too hard to fix, but I don’t want to spend a few hours digging in there.
I also needed an external drive for my VM work, wanting to put SQL Server on one of my disks, but they’re all almost full. So that spurred me to look at solving both problems at once.
Last year I asked Cali Lewis, of Geekbrief, what they did for backup. They shoot a daily podcast, generate more formats and storage than I do, and I was curious. Since Drobo is a sponsor of hers, I should have figured it out, but obviously they use a Drobo.
This morning I tweeted my followers asking what they thought. I mentioned I was considering a Drobo and wondered what other people did. I got a couple of USB drive responses, even with NAS add-ons, but those don’t really help. What I really wanted was:
I was considering a Drobo with 4 drives, since it looks like a nice device. I can add drives as I need them, multiple sizes, and allow the Drobo OS to balance the data across these drives and provide protection.
The base Drobo only has Firewise and USB ports, but I can add network connectivity if needed. However the Ethernet addition is $200, on top of a $500 device, so I’m at $700.
That’s not too expensive for protecting my data, but it’s a bit more than I’d like.
I also get a basic backup device that I need to use the Windows backup to schedule data moving from my machines or the iMac to the device. There are some apps that will stream media off this device, so it would be a nice central solution for the family.
However one thing that concerns me is that if the Drobo goes down, those drives aren’t readable anywhere else. I’d need another Drobo to access the data.
Someone tweeted back, actually a few people, that I should look at the MediaSmart server from HP. My first reaction was really, a Microsoft Home Server? I haven’t heard great things about them, but to be fair I haven’t heard much at all.
So I Google’d and asked a couple questions on Twitter. I found a few reviews, but this was one someone sent me from SlashGear.
As I read it, it seemed like they really had done a good job with this product, and it supports MAC OS as well.
It’s a 64-bit CPU, albeit Celeron, and it has 2GB of RAM. At $500, the price compares favorably with other servers I’ve priced, and this one has lots of features. I verified that it saved sata on multiple drives, and that you can add drives of different sizes. I also l like that it has Amazon S3 backup integration. I’m not sure if I want that, but it’s a good option.
It also has a lot of options for streaming data from other computers, especially media (pictures, video) and sharing it. I really like that since that’s a lot of what I deal with these days. All my writing can be backed up on a USB key since the text is small, but the video and pictures, especially pictures, are important and too big to manage effectively.
My other choice, is NAS of some sort. I had this D-Link 2 bay NAS enclosure recommended as well. It has a network interface that allows FTP and SMB access (file shares) and can handle RAID 1. It has a USB printer port as well, which is very interesting. We could use some printer help, especially since ours wants a new network interface ever time the power cycles.
This device is $157 from Amazon, but without drives. I could add 2 1TB drives for
about $200, so this is basically a $350 solution. The other thing is that this is a completely manual solution. I’d have to script the backups, and manage them going to and from the machine. I’m not sure about streaming stuff, though I suspect that I could let each machine access it as a file share and pull data off it.
The MediaSmart server is $500, but that’s with one drive. Adding a second for protection gives me $600, or $250 more, but I get a lot more, management of files to prevent duplicates, streaming access for more devices, including iTunes, and it automatically looks for new media files. That’s an interesting plus.
It’s a business cost to me, and $200 doesn’t seem like a lot. I’m really leaning towards a Windows Home Server, both for the testing and the convenience. I’ll do more research, but that’s my first thought.
I write an editorial about why I thought tape still had a place in backups. There were some interesting responses that said disk works well for them and they wouldn’t go back to tape.
I used to think the density was a big deal, along with cost, but maybe that’s changing. It’s interesting to think about disk only backups remotely, using a pull technique of some sort.
There are likely good ways to do this, but I still struggle with the offsite aspect of this. How do you get backups offsite in a timely manner. You can burn a lot of bandwidth with disks. If you physically move disks, even those large portable ones, isn’t that more cumbersome than tape? Can you have a service like an Iron Mountain still handle backups?
SQL Server does a great job of handling backups and restores, but you have to be able to mange the files well. Tape is less durable over time, though the same might be said of disk. In most cases, you never use either set of backups, so beyond a few days, does it matter? The case I wrote about shows it can.
I’m not convinced that disk is better, but I can at least see some places where it can work. Not tremendously large amounts of data, the need for a hot standby somewhere, and perhaps many cases where you would never want to go back more than a few versions.
I've always told DBAs and developers working for me that backups don't really matter. After all, 99.99% of the time we make a backup of a database, we never go back to it. It sits there, on some media, until it's deleted, erased, destroyed, or perhaps, it just lives on.
Restores, however, are critical.
No one ever got fired for not doing a backup that wasn't needed. However not being able to complete a restore has damaged more than a few careers. Just like being able to do that restore can boost your status as a DBA.
Last year, Greg Linwood posted a blog about SQL Server helping the environment and a suggestion on Connect to remove nonclustered indexes from backups. His thoughts were that we could
We'd also get corresponding quicker restore times, though you would then have to rebuild all your indexes. However your data would be available, and you would be able to build those indexes used heavily first, speeding up systems, and then backfill other indexes as you had time.
The other great situation here is that you could quickly move over other copies of the database for testing, development, reporting, etc., that might have much different indexing strategies.
In large systems, indexes can easily take up to 50% of the space of the data. Perhaps more if you heavily index. Shrinking these sizes would definitely help conserve resources, especially time. There would potential issues for people restoring that might need to set other expectations and manage their restore process more closely, but those could be handled.
The suggestion was closed by MS with a note that it was a complex problem, they'd have to redo backup, possibly slow it down, and it wasn't worth it. I'm not sure I understand that, since you would just grab the same pages, however not sending through pages that were indexes. Aren't these segregated out anyway?
Jonathan Kehayias had a great suggestion to have SQL Server automatically separate out NC indexes from data in filegroups. That would preserve the existing structure, and I think that's great. Sure you can do that today, but we want SQL Server to make things easier to manage, not more complex. Having the server separate out NS indexes by default, and then not back them up, would be a great way to improve the operation of the system.
I saw a question today on restoring a SQL Server 7 backup to SQL Server 2005, and I wasn't sure this was supported. So I went browsing through Books online and sure enough, it's supported. I ended up in the SQL Server 2008 books online first and it's not supported there, so it appears that you get to restore from the 2 previous versions at least for now.
That's good and it means that upgrades are possible through backup and recovery, which is a nice, safe way to move things around as far as I'm concerned. I always prefer having a fallback, in this case the original server instance.
Should MS support more versions? I'm not sure they should. That's a lot of backwards compatibility for them to maintain. I know it would seem simpler since they have the 70 mode in the database, but I think interpreting comments is much different from converting the file format.
However they have the code to convert 70 databases to 80. And from 80 to 90. Why not include a utility on the media that converts old backups to a newer format?
