Here was an interesting problem we ran into recently. We use SQL Server database snapshots to help load our data warehouse. To do this, we have an automated process which drops and re-creates snapshots as needed. Not too long ago, we started running into a problem in which the process failed to run, giving me two separate and contradictory errors. The step to create a new snapshot failed because “Database ‘[my database snapshot name]‘ already exists. Choose a different database name”
This seems pretty clear and straightforward; however, when I tried to drop the snapshot, I got an error message indicating that the database did not exist. I checked sys.databases and no reference of that snapshot was found. Nevertheless, the snapshot file itself existed on the Windows server hosting this SQL Server instance. When I tried to delete that file, I got an error message stating that the file is in use. And, even stranger, we were able to run USE [my database snapshot name] and perform queries and DBCC CHECKDB against the snapshot itself. So this snapshot existed in some land between existence and non-existence. This problem matches this post from a few years back, which had no resolution.
The resolution we came up with is as follows: take the primary database (the one upon which the snapshot was built) offline. Once you do that, you should be able to rename the snapshot file in Windows. After doing that, bring the database back online and you should be able to drop the database snapshot. There must have been some kind of error during the snapshot drop process which prevented the snapshot from being dropped cleanly, but going through these steps fixed our problem.
NoSQLMap now exists. It took a while for developers to figure out that SQL injection is a bad thing (and sadly, there are still quite a few who still don’t get it). Many of the developers who have gone overboard on the NoSQL movement will need to re-learn the same lesson, probably as painfully as the last time—and given the attitude of NoSQL product developers toward security (specifically, that end users should handle that burden), they might find it even worse than they did in the relational world.
Nic Cain on
Hekaton In-Memory OLTP. One additional limitation that Nic doesn’t bring up is the lack of foreign key and check constraints.
I’m pretty optimistic about the future of in-memory databases (pun not intended), but this is a V1 product with a lot of limitations. My initial primary use case would be for staging tables in an ETL environment. I’d have a very hard time justifying this in a production OLTP environment in which I actually cared about the data. And given that my job is to be the guy who cares about and protects the data, this might be a tool which doesn’t make it out of the lab for a couple of years.
Don’t worry—I won’t be doing this regularly.
The first real day after PASS (I’m not counting the weekend, as that was mostly decompressing), I decided to install BIDS Helper and start learning BIML through tutorials at BIMLScript. This is going to be a side project for the moment, but I hope to nail down some of the basic technique before figuring out how precisely to apply it to my work.
PASS Summit just ended and it was exactly what I needed. I’ve decided to make a few resolutions for the next year. In particular, my plan is to
- Learn BIML. This is a key technology and the next step in becoming a great SSIS developer.
- Get back on Twitter. SQL Server has one of the best Twitter communities and my having been out of the loop for more than three years has cost me.
- Re-write my ETL processes, making them more robust and higher-quality code. There were a few talks which showed me just what I need to do, so it’s time to put all of that into action.
- Go back to PASS Summit net year. It will be in Seattle, so I’d have to fly rather than drive, but even if I pay for everything out of pocket again, it’ll be totally worth it. This time, I’ll register before December 7th to get the early bird deal and offset the cost of a ticket.
I’m off to Charlotte for a few days to take part in PASS Summit. As usual, I’ll have a few blog posts queued up and shall endeavor to write a summary upon my return.
Tribal SQL is now for sale.
Also, if you’re going to PASS Summit this year, there will be a meet & greet at the Red Gate booth at 10:45 AM on Friday, October 18th.
I took my final MCSA exam today, this one on implementing a data warehouse in SQL Server 2012. I needed a 700 to pass and got exactly 700. The economist in me is pleased that I spent exactly the amount of time and effort necessary studying in order to pass this examination.
Tribal SQL will be published in October, (presumably?) in time for PASS Summit. This means that, within the next month, I’ll officially be published.
Ayende Rahien has a series of posts on rolling your own database platform (based on a talk in which he says don’t do that): post 1, post 2, post 3, post 4, post 5, post 6, post 7.
It’s a very interesting look at some of the design decisions necessary for a database, even if it doesn’t follow the relational model.