Author: way0utwest

Data Preservation

Maintaining data across time isn’t something many of us think about. We work with data in the here and now, and in the database world, we typically only need to recover or restore data from a short window. Like most of you, I would usually plan on recovering data that’s only a few days old. Being forced to restore a database from two weeks on any of my systems would make me cringe. It certainly would be embarrassing for me personally if it were my fault I couldn’t restore to a point in time that was more recent than that.

In planning to recover our systems, we typically know the versions of software we have to recover from, and we can easily re-download copies of SQL Server or the patches we need. Most of us are dealing with SQL Server 2000 or later, which is good since those are the only versions still documented on MSDN. If you need SQL Server 7.0 or SQL Server v6.5 documentation, I hope you have copies. The same goes for the media. You can still download SQL Server v6.5, and SP5, but if you needed SP3, it isn’t easily available. I ran into that situation about 10 years ago, and we had to make a special request through our TAP manager to get someone in Redmond to dig up a copy.

In some ways it might not be important to worry about long term storage. Most of us will end up transferring our data to newer systems (and formats) over time. As we upgrade SQL Server, our databases move along to newer formats, or we abandon them because they are no longer needed. That’s fine for some data, but not all.

Long term archival and storage is a challenge, as you can see in this short look at how old films are maintained. It just touches the edges of what’s being done, and doesn’t address costs. Plenty of old films have been lost forever, and perhaps that doesn’t matter, but it does concern me. I have thousands, maybe tens of thousands of digital images. While I love the ease with which I can share them with family, and make extra copies, I am worried that perhaps the lack of a physical copy means my great-grandchildren will struggle to find evidence of my generation if there is a catastrophe or storage formats change.

This is one area of our industry in which we have a lot of maturing to do, and I hope that we can come up with some new ideas for maintaining our data for the long term, across not months or years, but decades or centuries.

Steve Jones

Video and Audio versions

Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo atwww.everydayjones.com.

Follow Steve Jones on Twitter to find links and database related items and announcements.

Windows Media Video ( 18.9MB)

MP4 iPod Video ( 22.0MB)

MP3 Audio ( 4.5MB)

Feeds are available at iTunes and Mevio

To submit an article, rant or editorial,
log in to the Contribution Center

December 3, 2013

SQL in the City and SQL Saturday–Washington DC

Actually, Maryland, but close enough. I’ll be in Chevy Chase this weekend, first for our SQL in the City Seminar on Deployment Friday, and then SQL Saturday #233 on Saturday. Both events are free, so if you can come to one or both this weekend, register and come. The registration is mostly so we can plan on the number of people, so please register or cancel if you can, or can’t, come.

Both events are free. This is a great chance to learn something new. Friday is a look at database deployment, building from Version Control to Automated Build and Testing your database, to deployment. We’re presenting some of the things we’ve learned at Red Gate from our own internal work and our customers, and people this year have really enjoyed these sessions and learned a lot.

Saturday is a chance to learn about all sorts of technologies. From BIML to Columnstore to Indexing. There are a ton of sessions. My session on Saturday is about branding yourself, and if you want a new job, a better job, or more money, come see me at 2:00pm.

This trip closes out my year, and I’m glad for that. It’s been an up and down year for travel, but overall, I think this was the most travel I’ve ever done in one year. I managed well, but there were a few spots when I was definitely worn out.

I would love to say I won’t do it next year, but I’m not sure that’s the case. I really enjoy many of these events, and I’ll probably do the same number, or perhaps more, in 2014, but I’ll try to spread them out a bit more.

December 3, 2013
Accidently Kicking a Database into the Restoring State
I learn new things all the time. This was one that actually stunned me. Huge props to Gail Shaw for posting a note about this in a thread.

Run this code:
```
CREATE DATABASE MyRestoreTest
;
GO
USE MyRestoreTest
go
BACKUP DATABASE MyRestoreTest TO DISK ='myrestoretest.bak';
GO
CREATE TABLE mytable( id INT)
;
GO
USE master
go
BACKUP LOG myrestoretest TO DISK = 'myrestoretest_log.trn' WITH norecovery
```
You’ll see this in your Object Explorer

Ugh.

I haven’t started a restore. I’ve run a backup. Apparently this causes problems, as noted by Gail in the thread. Sure enough, it’s documented in the BACKUP command, in the Log-specific Options.

I had never scheduled backups with this type of option, but you might have a job that does this if you were preparing for a failover. Having a script ready it a good idea, but if it executes unexpectedly, this could happen.

The lesson: make sure you know the options when you run a command. Always test, and if something strange happens, search or ask what might have happened.
December 2, 2013

Is Your Data Relational?

When should you use MongoDB? I’ve asked that question a few times, and I haven’t gotten a good answer I understand. I’ve read about a few of the NoSQL, document databases, and I think they do have a place in the world. There are domains of problems that they solve better than relational databases. I’m sure that streaming databases, graph databases, columnar databases, and more have places where we they shine, but I still believe that many, perhaps most, problems are best solved by relational databases.

I saw this piece about MongoDB being a poor choice by the Diaspora developers because they hadn’t modeled their data well. It’s a decent analysis of a real situation, and I think it’s one that has probably been repeated many times by many developers that were intimidated, frustrated, or otherwise turned off by relational platforms.

There is a cost to using a relational platform, and it can require expensive developer time to map objects to relational structures. However the solution isn’t to abandon the relational platform. The better solution is likely to train developers and hire a few DBAs that can help with the mapping and queries to solve problems.

Steve Jones

Video and Audio versions

Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

Follow Steve Jones on Twitter to find links and database related items and announcements.

Windows Media Video ( MB)

MP4 iPod Video ( 17.0MB)

Feeds are available at iTunes and Mevio

To submit an article, rant or editorial,
log in to the Contribution Center

December 2, 2013