Here's a tidbit for those who might have SQL server in their environments, maybe without knowing all the nitty gritty low-down: if you try to use file system replication (robocopy, xcopy, repli-whatever) to maintain a DR server from your production SQL Server, you might be in for a nasty surprise.

I recently had to troubleshoot a scenario like this: the app owner came to me and said, "the DB on our disaster recovery system is in suspect mode, what should we do? (and what happened?)" I immediately went to disk corruption, etc., but it turns out the issue was really simpler: it turns out there was nothing wrong with the machine other than copying files onto it.

Huh?

Yes, copying files had corrupted the database. Here's how:

The (well meaning) group involved had established replication of files from a production server to this failover server, thinking about disaster recovery. For almost all "vanilla" files, this works great. In practically all other cases outside SQL Server, each file is a separate entity, working alone. If the file is unlocked at any point in time, then it's pretty safe to copy it off to another server.

SQL Server databases, however, are always composed of at least two separate files that work together: the data file and the log file. In addition, those two files are typically locked whenever the SQL Server service is running and has them open. Two issues with file replication, then: the database files are always locked, so they probably won't get replicated very often, but more importantly, you have to copy both of them at the same time to end up with a working database at the other end of the process. If the log and data files log sequence numbers (LSN's) don't line up, the recovery process doesn't work, and the database will not function.

What I think happened with my app owner is this: the source system and destination went offline for a brief period (probably patching), so the original MDF and LDF files were unlocked. The replication thingamagig managed to pick up and copy the small log file but not the big data file to the its target server. When the target server's SQL Server service fired up, it found a mismatched log and data file, and so, of course, failed recovery and would not bring the DB online.

The takeaway - SQL Server has great technology for disaster recovery, including the simple/basic log shipping method. Copying raw files from disk, however, won't give you DR!

Mac++ | C:\macOSX > bliss

It’s been about eight years (!) since my wife and I bought a computer for personal use. Last week we took delivery of a new MacBook Pro 13 2.26. And it’s fantastic! Sometimes if I work on a machine that’s slightly newer than the previous -- at work, for example -- there’s a little bump but the novelty wears off pretty quick. But get this: we went directly from a much loved Power Mac G4 Cube built about 2001 to a brand new Intel-based MacBook. Bam!

Bye Bye Cube

Still runs! Picture of the Cube, taken with the MacBook’s iSight camera. Note the old iSight camera on top of the monitor…

The old machine has a 450 mhz one core PowerPC processor, and shipped with 64 MB RAM and an 18 GB hard drive. We got one of the early Apple LCD displays, an Apple Studio 17. I upgraded the memory a few years ago to a whopping 320 MB, and got a 60 GB external firewire drive. I also replaced the DVD ROM with a burner and bought a Microsoft Arc mouse. The thing is, other than those updates, the Cube has run flawlessly all this time. It was expensive when we bought it, but we got eight years of good use out of it. It upgraded without issue all the way from the original OS to Mac OS 10.3. It’s also one of the few computer models in the industrial design collection of the New York MOMA, and industrial design is a weakness of mine. I have a real, personal attachment to it, and I’ll be sad to give it up. In November we finally hit a wall in terms of software upgrades with a deprecated version of iPhoto. C’est la vie. I’ll want to keep it with all my relic Macs as a keepsake; I’m sure my wife will want to be rid of it :-). [Edit: she’s sitting right here and says “No,” she wants to keep the Cube and continue to use it as a second machine!]

Hello Mac OS X 10.6 + Windows 7

Picture of the new MBP from the old Cube’s iSight

The upgrade experience has been really exceptional. I can remember (dating myself here) when you had to worry, especially with Windows, about things like interrupts and drivers and autoexec.bat. Macs were better, but they had issues too (remember Extensions? MultiFinder? Classic?). I am not really an Apple fanboy these days, having developed a fondness for, and then a career around SQL Server -- though about 1995 I’m sure I was. On the other hand, we’ve always had Macs at home, from my first $4,700 Mac II in 1988 right up to the present. Today I try to be OS agnostic, so the prospect of running Windows 7 and OS X integrated together on Apple hardware is almost too good to be true. I am giddy. Here’s what we did, in case anyone else is headed down this road. There are other, equally good methods, but this was great:

1. Mirrored the Cube’s original hard drive to our external Firewire disk drive using the shareware SuperDuper! (No joke – I’ve had this for years as our backup solution. It makes a complete, bootable image of the original hard drive.)
2. Ejected and unplugged the external drive from the Cube and connected its USB interface to the new MacBook.
3. On the MacBook, ran the Apple Migration Assistant to bring all the data, user profile information and vintage apps across. Here I picked the option to make renamed user profiles on the new machine from the old profiles, while keeping a “clean” native user profile on the MacBook. I just like to start from scratch, especially when I am imagining eight-year-old bits.
4. Some old apps we have are PowerPC binaries, which won’t be as fast on the new hardware, but I installed Apple’s Rosetta to run them anyway. They still work.
5. Copied a pile of other files such as music and pictures from another partition on the external disk to the MacBook’s internal drive.
6. I then downloaded vmware Fusion and installed it.
7. Atop that, I bought Windows 7, downloaded and installed it from .ISO.
8. Virus Protection for the Windows side, Windows Update patches
9. Installed apps from our CDs on both OS’s
10. Hooked adapters up from Apple to continue using our vintage Studio 17, driven by the MacBook

What’s incredible and satisfying: it all works. First time, no hassle. Two operating systems, one integrated desktop/UI. Wireless, multi-touch trackpad, everything. Amazing. The very next day my wife took the MacBook on a trip, taking with her real work requirements that had to be accomplished in both OS’s. She came back happy and reporting 100% success. Wow.

I wonder if because I have been at this a while (30 years fooling with computers, but who’s counting?) I have a greater appreciation for the engineering that made this possible. All these components, hardware, software libraries, chips, interfaces and standards converge to make something elegant, powerful – amazing. Here I sit writing in Windows Live Writer, on my Mac, connected to sqlblog.com – with no issues. High five to Intel, Apple, vmware, Microsoft, Adobe. Wow.

So, I am just writing because I am excited about some new toys we're setting up at work: I've got three new Nehalem-based SQL servers connected to our first storage array with solid state disks (it actually has three tiers of storage: SSD, FC and SATA). Looking forward to screaming fast reads for our data warehouse/BI setup, as well as consolidation of a sprawly old collection of SQL Servers onto a nice, consolidated set of new clusters.

Technically the new storage array will support a cross-platform mix of HP/UX Itanium servers running Oracle and MS Windows clusters running SQL Server, side by side on the SSD and fiber channel disks. The design work has given me the chance to work closely with one of our Oracle DBAs and the SAN admins (all friendly people! Amazing!), and I'm learning a lot.

All the metal bits are in place in the data center, lights a-blinking, and we're on to configuring the array and perf testing in the next two weeks. Sweet!

This has bugged (pun?) me for some time, and I wonder if I am just nuts: I hesitate to log in to a production server with Remote Desktop and run SSMS there, preferring to do all I can with SSMS running on my local workstation instead. However, in both SSMS 2005 and 2008 there's this strange behavior when setting up an Integration Services package for SQL Server Agent: the GUI demands the package be specified using a local disk instead of a disk from the server, which of course is impossible:

1. Open SSMS and connect to a remote server
2. Create a new SQL Server Agent job
3. Create a step with type SQL Server Integration Services Package
4. Choose the File System as the source (we deploy SSIS packages as files)
5. Click the Ellipsis (...) button
6. I then get the rather unhelpful My Documents folder on my C: drive.

Most other server-related dialogs give you server filesystems (backup or restore, for example.) Is this just a long-overlooked bug, or am I crazy?

Partitioning for Fun and Profit

Over the past year or so I’ve been playing with a toy SQL monitoring application, just to sharpen my dev skills a bit. Part of the app is a SQL database to store historical performance data, in order to do trending. Fooling with the design of the performance database allowed me to look at EAV (Entity Attribute Value) versus other patterns and do some real performance measurements.

I went into this design with some preconceptions about EAV:

1. I figured EAV could be a good fit for this problem, given that one would not know which of the hundreds of available performance counters the app would track until run time. At design time, all I know is that the database has to store some unknown, user-defined collection of performance counters. It seems like the classic EAV problem of run time, user-defined attributes.
2. I predicted that EAV would be more convenient to implement than a dynamic / multiple table design because the schema would not change at run time. The question is, how much more convenient? Conversely, how much effort and risk is there in extending the schema at run time?
3. I thought there would be tradeoffs, where EAV is bigger and slower than having tables built out, especially when it comes to reporting queries. Generally it's possible to boost database performance when you can partition sub-sets of rows into physically separate structures, rather than having them interleaved together in one huge table. The questions here are, how much bigger, and specifically which kinds of queries are slower? Are there concrete rules that can indicate when EAV will work well or not?

To set the stage for this, I am planning to store the performance data in a way that aligns with how Windows performance counters are structured: there’s a tree of counters labeled performance objects / counters / instances in perfmon or counter categories / counters / instance names in C#. It’s pretty easy to dump out a tabular list of available counters with a simple C# or PowerShell snippet:

The output is a tab-delimited list of most available counters:

First the EAV schema

The EAV pattern essentially says that since we don’t know what attributes (what specific performance counters, in this case) need to be stored, we store the data in labeled rows, instead of in columns – rows being really easy to change at run time, while columns are not. The simplest implementation of this is with two tables, one small table of monitored servers, and one huge table of labeled performance counter values. If a “sample” is defined as a set of specific counter values at one point in time, then this big table will have a set of rows for each server, for each time point, where each row is labeled per performance counter:

Inserting the counter data into this table is trivial:

Next, EAV Sample Data

In order to see how this will perform at scale, we need to populate it with a lot of sample data, certainly more than will fit in memory. I’ve got a script that can do this by just creating random values for a reasonable set of different counters, in bulk:

Sample counter values (note: this makes a decent  quantity of data, four weeks of one minute sample intervals for 194 counters on five imaginary servers 4 * 24 * 60 * 5 * 194 ~= 39,000,000 rows)

Index rebuild at the end, as the table is almost certainly highly fragged.

This design does function, but there are some challenges:

1. The CounterSamples table is obviously huge, and highly repetitive because of the row labels.
2. A query is most likely to “want” the data from one counter or another, as separate sets, but practically never from a mixture of different counters. Directly aggregating the “total % processor used” values with, for example, the “percent log used” values makes no sense, because the counters measure two unrelated metrics. So it seems like we have rows mixed together that most queries will want to separate.

Counterpoint: Dynamic Table Solution

Another possible schema is driven by the notion that at query time this data “wants” to be partitioned by counter, so all the values for each specific counter are in one place. Since we don’t know the counter list until run time, it means we have to code around the fact that tables need to be added dynamically to the schema. This does add to the complexity of the code, while possibly paying dividends in storage requirement and performance.

First, we can use the same Servers table as in the first example, so assume we have that one. Next, we will need to be able to dynamically generate a table to store counter samples for any specific performance counter.  A template for such a table might look like this:

But, code has to be written to take a performance counter and generate a table like this at run time. In a “real” scenario, it probably makes sense to do that client side, but to mock this up I am going to make a fake, dynamic SQL driven stored procedure that will generate these tables:

Similarly, client-side code would have to be written to insert counter samples into the database by intelligently using the correct table for each counter. Technically this isn’t very difficult, as one could put a check for a matching table into the constructor of a client-side object, which would validate that an appropriate table exists (or create one), and then use that table from that point on. I can also mock this in T-SQL with a dynamic SQL stored procedure, though I would not implement it this way on a real system:

Dynamic Tables Sample Data

So, in a real app the tables would be created on demand. In order to load sample data, though, it’s simpler just  to initialize the database with the whole sample set of tables:

Then generate the same quantity of sample data as in the first example:

First Impressions

The first thing I note when looking at the sample data is the size discrepancy: the first model requires 5.5 GB to store the counter data, where the second fits in 1.2 GB. Most of that difference has to do with the fact that the EAV “labels” for this case are quite wide, so there’s a lot of repeated text in the EAV model.

The second thing of note is the interleave of different types of perf counters in the one large table of samples, which is what we’re going to examine for performance. This depends some on the order of the cluster key, but essentially it looks like this:

Rows that I imagine one query “cares” about (green) are dispersed through the table, with a lot of other rows in between (gray).

So, tune in for the next post where I’ll have report query performance information, and a look at the buffer cache contents when reading from these two designs.

In the last installment I pleaded for the equal treatment of the database schema and object model when implementing an application with a database, on the grounds that the failure of either means the failure of the whole system. I think it follows that a decent application ought to have both a good quality database schema and a fabulous code base. If the database is an embarrassment, on relational grounds, then it compromises the whole, just as if the app code is an embarrassment on OO grounds.

(I am working from the assumption that we do try to create good quality software; there's the whole "sales driven design" notion that says we can't ever create anything of value because it's too costly; I reject that notion as ... well, boring. There would not be much to write here if we simply give up trying to make anything good.)

So, for the sake of argument let's assume we've picked an O/R Mapper to help create the app by automating much of the plumbing of repetitive SQL queries, and moving data to and fro. I am going to invoke NHibernate, because it seems decent, and mature, and so on. I think Entity Framework has the potential to turn into something great, if built out correctly in upcoming versions, yet it seems so far to be over-inflated with a lot of marketing noise, while it catches up to other solutions. Then again, I am not really an expert with ORM, and others could to a better job of comparing. Assume an appropriate level of abstraction is maintained between the database and the application by some implementation of either views, functions and stored procs, or by a nice, clean data access layer, which you can do with a mapper if you implement it correctly.

The most trivial bits of O/R mapping aren't that interesting - a simple atomic object corresponds to a simple row in a simple table using a surrogate key. A collection like List<T> in the app is a collection of such objects representing a subset of the rows from a table. "Product Object" implies "select ProductID, Name, Price from Products where ProductID = n." That's all quite basic, and something one would probably create manually using traditional coding techniques. The place where things get interesting and hairy is in the more complicated cases where the object model and the relational model don't match up so neatly. I'll cut straight to the chase: polymorphism is where things get ugly, and that's where we should start to be careful. And if you look up polymorphism related to popular ORM's, an issue rears its ugly head immediately: TPH or Table Per Heirarchy persistence model for polymorphism. Consider these descriptions:

NHibernate Inheritance Mapping docs

https://www.hibernate.org/hib_docs/nhibernate/html/inheritance.html

Oren Eini (Ayende Rahien)'s very concise blog description:

http://ayende.com/Blog/archive/2009/04/10/nhibernate-mapping-ndash-inheritance.aspx

Alex James excellent version pertaining to Entity Framework

http://blogs.msdn.com/alexj/archive/2009/04/15/tip-12-choosing-an-inheritance-strategy.aspx

I don't have anything against the smart, hard-working folks that put this stuff together, nor am I anti-ORM in any way. Still, amazingly, all these documents and blogs about both NHibernate and Entity Framework generally give about equal billing to three methods for representing polymorphic objects in a relational schema -- even though one, TPH (which, horribly, seems always to come first in every such list) violates the most basic best practice for relational database design. It's not normalized even in a trivial sense, it makes it impossible to enforce integrity on the data, and what's most "awesome:" it is virtually guaranteed to perform badly at a large scale for any non-trivial set of data.

Luckily, there is a workable implementation in most of these tools, Table per Type. What's missing from the docs and recommendations around TPH is some big, bold print that says,

"Never do TPH, because it's a horrible nightmare in the relational world, a violation of 30 years of best practice in database design, and probably makes a DBA somewhere cry inside. Always do TPT. TPH is presented here only as a twisted curiosity, and is to be avoided."

Lack of that warning about TPH means people might be tempted to actually use it, which is unfortunate.

I imagine all DBA's will hate TPH. Some Devs I am certain will complain that the DBA's are being obstinate or unreasonable, but here's the thing: TPH is a bad idea, and the DBA's know that. If the shoes and feet were reversed, so to speak, where the DBA's were demanding that Developers violate some fundamental programming principles in the design of their code -- without even really understanding it -- they might be a bit miffed as well, and would have valid reasons. The sad thing is that blunders like TPH can sour DBAs on the entire notion of ORM, based simply on the notion that ORM tools can seem to encourage bad database design. It really ought to be the other way around. But let's set the cultural argument aside and look at technical reasons why this method is to be avoided.

I worked with an ISV-provided piece of software for years that, by coincidence, had important, busy tables designed on the TPH principle. Ironically it didn't use anything as elaborate as an O/R mapper; the developers had just adopted the same notion for polymoprhism. In this case the application has an accounting function, among other things. Each financial transaction in the accounting part of the system, of every type (charges, payments, checks, invoices, etc.) is stored in one massive Transactions table. The table is 120 columns wide and practically every column is nullable, and might or might not have a value depending on what type of transaction was indicated - you guessed it - in the "discriminator" column. There are 16 types of transactions, if I recall. This massive table also stored both posted and un-posted transactions (hey, they're all "transactions," right?). It also contained the header records for batches of transactions that were entered at the same time. In order to "post" un-posted transactions, an elaborate process would change the discriminator/record type and set various dates and flags in the table, through a ... erm, "complex" ... procedure. A massive amount of this huge, largely empty table (nulls take space, even without values) had to be kept in cache on the SQL Server all the time. It was basically filling the RAM on the server with blank space, and the odd value here or there at random. Ironically, this design provided no advantage whatsoever. It's wasn't like there was a trade off of this advantage for that disadvantage. It was simply, purely horrible.

The application did not perform well, at all, at scale, and was just a constant source of expense and pain for us. Not because of some purist ideal of normalization, but because all the financial data was mixed into a great disorganized pile. The frustrating thing about it was that had it been based on a reasonable schema, there would have been no performance issue at all, and it would easily have worked for hundreds or a few thousands of end users on fairly modest hardware.

Of the docs and blog posts I have been able to find on this topic, most give only a cursory overview, along the lines of "you might want to do this, or that, it's not that important." Alex James does more in the way of explanation about how they work and why to pick one. Interestingly, there's a table on his blog page ranking them by different criteria, and the TPT, relational-friendly option he marks "winner" in every category except performance. I had the privilege of attending a session at the PASS Summit that he gave, which was excellent, and he is a very sharp guy with an understanding of the database side of this issue. Yet I do have to wonder about the "performance" argument, as it's not been my experience at all. Maybe I am jumping to conclusions, but I wonder if it's the old myth about "avoid joins to enhance performance" coming back around.

I suspect this: for a trivial case, where you have five or six object types that are quite similar, then stuffing them into one table might avoid some joins - as long as the cost of the interleaving of rows of different types, which reduces the efficacy of indexes and takes more memory and slows many types of queries down, doesn't cancel the intended optimization. However for real world cases with large sets of data, design-by-join-avoidance isn't really a good strategy. Better performance is gained, in most cases, by the partitioning of data into smaller, more highly sorted and organized sets. Yes, the server has to perform joins, but the cost of joins is much less than the cost of brute-force searching through a high volume of interleaved, mixed, sparse rows of different "types" in one massive table.

Perhaps a clearer analogy: suppose we had one uber-table, called "Object." Everything inherits from Object, so why not one massive, ultimate Table Per Heirarchy with Object as the root. We just put a discriminator that indicates what type of object each row represents, and a nullable column for every property of every object. Simple! The issue is that everything, in this case literally everything, is mixed together. Should perform swimmingly, because there are no joins!

But wait. There are other issues over and above joins. How does the server locate data in this table? How much cache would be required? What does an index look like? Clearly the server would perform better if the data were partitioned sensibly, into smaller groups, so that it could find related information. The answer, of course, is tables. Having multiple tables not just convenient, it's a performance optimization. In fact, there are many performance issues that can be solved by adding tables, not removing tables - most of the time if unrelated rows can be partitioned into separate sets (tables), it makes it easier and faster for the server to locate them.

So if performance is the one argument for TPH, the one that Alex says "trumps these other concerns," then I have to say I'm not quite buying it. In fact, I can't see why anyone would ever want to do TPH for anything other than a small toy of a system. In fact, can we turn that thing off in any of these O/R mappers? Where's the OFF button?

Back to the larger point, which is, "what are the major design issues for connecting apps to data?" I think the points I want to make are: a. the database schema still deserves careful attention and design, even in 2010, and b. the use of modern methodologies (agile, ORM, etc.) can be aligned with best practices but you have to be intentional about it. You have to be able to say things like, "TPH sounds neat-o, but it violates normalization fundamentals. What's up with that? That seems like a bad idea, even according to my olde-time gray-haired DBA over here. Why do that? What are my other options? Which ones don't make the database suck?" The end goal should be "and" - great database AND great application, no compromises.

Mercy, I think I made it. Today was the post-con for the PASS Summit, and I was in a great session hosted by Louis Davidson and Paul Nielsen. I say "hosted" because it was really more a guided, interactive discussion than a lecture, which is great for PASS, and especially great for the end of PASS when everyone's eyes are a little glazed over. It was great to get quality time with these two powerful brains.

I also got to meet a couple more great folks - Aaron Bertrand and John Paul Cook, among others.

The facility folks were breaking down all the banners and booths during the day, which is always a little bittersweet. I was powerfully reminded of this feeling I used to have when I was a kid, watching TV, the credits would roll at the end of Dukes of Hazzard and Waylon Jennings would start in singing the theme song. I'd looked forward to the show all week, and it was awesome, and yet over too soon, which made me a little sad.

[If you are not familiar with the Dukes of Hazzard, it was one of the worst and most successful television programs in the U.S. in the early '80s. It appealed, completely transparently and without even attempting sophistication, to the American boy's ideal of muscle-car-driving southern Robinhood/outlaw. Plus, they jumped a Dodge Charger over something in every episode. I was eleven, OK? :-) ]

The summmit is rolling into it's last day (post-conference sessions aside). I got to see some amazing stuff yesterday. Hillarity at the Quest breakfast -- picture five DBAs, in a clown car -- but great content interleaved with the jokes. The MVP Deep Dives book seems to be a huge success, and the booksigning was very cool.

I got to meet more of my heros, like:

Erland Sommarskog (http://www.sommarskog.se/) who is interesting not only because he's Swedish (most good things come from Sweden) but because he was basically blogging about SQL Server before blogging was cool, perhaps even before "blog" was a word. Erland wrote some indispensable articles on error handling patterns and dynamic SQL that are a must-read for every DBA and database dev. If you don't know those articles, stop reading right here and open a tab. Seriously.

Brad McGehee -- this is a guy who can distill the best performance practices, no matter how complex, into coherent, understandable language for any DBA to fully understand. Also lives in Hawaii, lucky guy. I'd had some email correspondence with Brad, but it was nice to meet him face to face.

Brent Ozar, all around mover and shaker. How does he DO that?

Gail Shaw. Period. Awesome. Wonderwoman of the core SQL engine, indexes, you name it.

Buck Woody, Allen White, Kevin Kline -- too many to list them all.

In line at the book signing, by pure luck, I also bumped into Dave Fackler, whose SSIS session, from his description, sounds like it will be fantastic, and exactly what I need for a project at work.

Other high points - enjoyed Craig Utley's session on SSAS best practices, which was a great precursor for Chris Webb's Aggregation Design talk. Also Red Gate booth small talk. Love those folks. Project Madison at the HP booth was very cool. Got a great demo of SQL Sentry (unsolicited plug here) which looks terrific.