Typically the features that you don't hear about from marketing are the ones that don't bring in all the money (e.g. pushed as "enterprise feaures" in order to sell Enterprise Edition). I answered a similar question here that provided a list of my favorite new features in 2012, that also aren't limited to Enterprise Edition:
What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?
(I could list them here but I don't think duplication makes sense. And there are some other good answers on that page as well.)
The only difference would be you could add to the list the things that have been added from 2008 on: date/time data types, data compression (Enterprise), backup compression (Standard+), some syntax improvements (multi-row values, inline declare + assign, +=/-=, etc). These are things you would get, though, whether you went 2005 -> 2008 R2 or 2005 -> 2012. Guess I knee-jerked listing those because you won't get them if you stay where you are.
In addition, moving to 2012 now instead of 2008 R2 buys you a longer lease toward end-of-life / end-of-mainstream support. However if you are using CAL licensing you might consider 2008 R2, because this option isn't available anymore in 2012 Enterprise. And core licensing can be more expensive if your CPUs have more than 4 cores per socket. They recently updated the "How to Buy" part of the site; you should stay on top of that and keep in good touch with a licensing rep. They're not perfect (and we can have a different discussion about this) but they're the only ones who can give you plausible deniability should anything they sell you end up putting you out of compliance, as long as you haven't lied to them. :-)
If you're going to take advantage of CPU-intensive things like compression, then having faster, fewer cores may be tolerable overall especially since it will make your SQL licensing (quite likely the most expensive part of your solution) that much cheaper. More, slower cores will be more expensive in terms of licensing, and spreading the work over more cores won't necessarily do any better for an OLTP-type workload - since many tasks cannot be parallel anyway. Also, AMD cores are subject to a core factor, which means the pricing is adjusted. This means you pay less per AMD core than per Intel core, but this is probably reflected in the performance, as well (at least if my conversations with Glenn Berry are any indication). No discount like this is in effect for 2008 R2 (at least AFAIK) since you're paying by the socket. Personally, I would rather spend the money on the better cores, but at 48x + 1.33x for licensing I'm not sure I could sell the bean-counters on it. :-)
But there's no way anyone here can tell you which way to go. If you can get some spec hardware in both configurations that would be ideal - you really should test your full workload cycle to see if fewer, faster processors are better for your workload - or at least close enough that they are justified by the licensing savings. On modern hardware I'd expect that to be a better option but I am not going to sign anything that guarantees it. :-)
See my comment above. I used Process Monitor to trace the registry access and found it was getting an Access Denied while trying to write:
3:16:40.8405491 PM sqlservr.exe 8756 RegCreateKey HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer ACCESS DENIED Desired Access: Write
I got around this by opening the Registry Editor and giving Everyone full access to HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer
. Then I changed the default location and removed the Everyone access once it was done. This made the following extra keys that weren't there before:
DefaultData
DefaultLog
BackupDirectory
Best Answer
...
Not really that much difference, to be honest. While
User Instances
is no longer a factor, it is still very easy for a developer to create a bunch of SqlLocalDb instances and lose track of which one holds the "true" version of the truth.What LocalDb does is eliminate the need to set up a full service-based instance of SQL Server (Express or otherwise), and reduces the complexity of the security model. It is still up to the developer or the team to implement sound development practices with regard to source control and promotion. As expressed in the comments, there are several ways to promote code from a local instance to production (hopefully through some kind of QA/test systems first):
(I've intentionally left off detach / attach because backup / restore is much safer. With detach if something happens to the .mdf file during or after detaching, you now have zero copies of your database. If a backup goes wrong, you still have the source.)
Whether any of these are more or less prone to the same kinds of problems as today, depends more on team discipline and established procedures than whether they use Express or LocalDb for local development. IMHO.
The nice thing LocalDb provides over user instances is that if you connect to a single instance of LocalDb you don't end up with the specific case where every time you use
AttachDbFileName
you get a new copy of the database. The most problematic part there is you change a table in one instance, then get an error from your application because it's connected to a copy that doesn't have your table change. As you've pointed out, this has led to a never-ending stream of confusion and similar questions on SO. In fact SqlLocalDb still supportsAttachDbFileName
, but I think it's going to be very uncommonly used, if ever.With LocalDb it is less likely that you run into these problems, but they still exist. It's a different tool with some advantages but it can still be used inappropriately.