A Real Version Upgrade at Last
Whereas SQL Server 2008R2 was just a service pack that we had to pay for, SQL 2012 brings important changes, not all of them good.
Microsoft has been juggling functionality around and has come up with a new edition of SQL server that you might like and licensing changes that you probably won’t like. On the plus side there are some truly useful new additions to the feature set. On the minus side, these features are mostly reserved for the Enterprise Edition.
If you are still on SQL Server 2000 there is no upgrade path to SQL Server 2012. You will first have to migrate your databases to SQL Server 2005 or 2008. Among other problems, SQL 2000 DTS packages cannot be imported into SQL Server 2012. They must be rewritten as Integration Services packages. This is true even for DTS packages that have already imported into SQL Server 2005 or 2008.
The Party’s Over
I knew it couldn’t last, but with this release Microsoft has abandoned the per-CPU licensing model and has adopted the per-core model. In versions up to 2012, you needed one license per physical processor no matter how many cpu cores it had. If you picked your hardware carefully you could get 8 cores for the cost of one license and save enough in licensing fees to pay for the new hardware. With the same hardware, in SQL 2012 you will need 8 core licenses. It is true that the core licenses are much less expensive than the former per-socket pricing, but when you do the math you will find that the marketing hotshots in Redmond have slipped in a substantial price increase.
There is no room here to go into detail about the enormously complicated new licensing model but here is a link to a site where you can download PDF files explaining the new model and also the new pricing: SQL Server 2012 Pricing, etc.
A New Edition for BI
The new Business Intelligence edition is focused on middle tier internal data warehouses. To enforce the internal part, the only form of licensing supported by this edition is the per-user (CAL) model. If your BI warehouse is outward facing you have to buy Enterprise Edition and use per-core licensing.
You can look at AlwaysOn as database mirroring on steroids. To me, it seems to be the most important new feature. It can insure a level of availability that previously has been unreachable without spending a fortune.
AlwaysOn allows you more than one replica of your database. You can keep one locally for fast failover, one at a remote site. Unlike Database Mirroring, the replicas can be made readable, so you can separate the reporting load from your oltp processes without having to put up with the limitations of reporting from snapshots.
AlwaysOn also includes the ability to group multiple databases. If your application depends on more than one database, this feature allows you to failover all the databases as a synchronized set. If you have multiple groups, you can fail the groups over to separate servers. Much less expensive servers can be used as failover secondaries since each will only receive part of the load of the production server.
AlwaysOn also adds some features to failover clustering, including the ability to customize the conditions that trigger a failover.
I haven’t had a chance to actually test this feature but it sounds awfully good.
A contained database is one that includes the database and all dependent objects. The purpose is to make database migration simpler. Normally when you move a database to a different sql server, there are a number of related tasks that must be done before the database is usable. For example you have to synchronize the user accounts in the database with the server logins on the new server. You might also need to deal with collation problems, compatibility levels, scheduled jobs, etc. SQL Server 2012 implements a partially contained database in that the logins can be stored at the database level rather than at the Instance level. There is no need to migrate the logins separately.
This technology comes straight out of SQL Azure where you only have a database, the SQL Server instance is invisible to you. This and other features in SQL 2012 point to the fact that Microsoft is adopting cloud concepts to local databases so that the migration to the cloud becomes simpler.
ColumnStore indexes show great potential for optimizing data warehouse queries but they are essentially useless in an OLTP database.
ColumnStore indexes are very, very fast, but in this incarnation they seem to have simply too many limitations to be widely adopted any time soon. To me, the show-stopper is that they can only be used on a read-only table. Even in a data warehouse you frequently have to load new data into the tables and this is difficult to do if your tables are read-only.
Microsoft describes a workaround for the read-only problem by switching out table partitions in your data warehouse tables. I suppose if you were desperate for better performance, the workaround might be acceptable.
There are many more new features in SQL Server 2012 which might be useful to you but the majority of the important new features are only available in the Enterprise Edition. The new licensing model is probably going to cost more than you paid for previous versions.
There are many more features in the new version of SQL Server than I have covered. If you are thinking about implementing SQL Server 2012 you should not rely on this short article to give you a complete picture of the pros and cons of the new version. With the roll-out just a few weeks away, there should be no shortage of available information and opinions about SQL Server 2012 for you to read.