SQL Server 2012 keeps raising the game
Microsoft has made available release candidate 0 of SQL Server 2012. We decided to take a look at its most compelling new features.
15 December 2011
If there's one single piece of software which continually impresses us, it's SQL Server. It's solid, developed at a steady pace that keeps up with the demand for new features and general rate of technology change.
And it has to. The need to store, manage and process data is exploding at an exponential rate - according to Gartner, at least 59% annually and up to 44 times over the next decade, according to IDC.
Meeting today's data needs
Organisations and IT departments are facing new technical challenges. Globalisation, larger organisations, processing millions of customer records - all of these issues and many more are pushing hardware and software to its limits. At the heart of everything is the database, so when Microsoft pushes out a new version of SQL Server - the database many organisations rely on - it's met with great interest.
Due for release this year, the latest version of SQL Server (previously code-named 'Denali' - named after the highest peak in North America) has been given a public preview in the form of the first release candidate.
Although there are many hundreds of improvements, Microsoft's focus for many of these is in three key areas:
- Mission critical performance
- Better insights into your data
- Cloud solutions
All of the 9s
Microsoft is pitching SQL Server as being always on - delivering what it calls the 'required 9s' for business. This refers to the uptime goal of 99.999% availability, ie: it pretty much never fails and it's always on.
This is an ambitious goal, but it's one which businesses quite simply demand. Well, if you're going to put a database at the heart of your 24/7 business then it had better be available 24/7.
Recovery from failure
Of course, in reality, things fail - the trick is to make sure that within the data centre, various instances of SQL Server continually work together to provide resilience, share loads and reduce risks. So, SQL Server's 'always on' mentality provides increased availability with Groups, Failover Cluster Instances, Active Secondaries and AutoStat (getting statistics from a database via another). For example, Always On Availability Groups give database mirroring a real boost, ensuring that multiple application databases remain available at all times.
Should you wish to restore a previous instance of a database, the Database Recovery Advisor provides a visual timeline to enable a faster and more accurate review of available restore points.
Pushing performance
As new features are added to SQL Server, the worry is that it will grow slower, weighed down by more and more code - so Microsoft has to work progressively harder to ensure that the opposite happens: performance is actually improved over previous versions. Microsoft has introduced many new features to make this a reality, including Column Store - the first true column store in a major database engine; this delivers one hundred-fold improvements in star join and similar queries. Further, table partitioning now scales to 15,000 partitions - a real benefit for large sets of data.
The performance of full text search has been given a total overhaul, something which Microsoft describes as delivering 'magnitudes of performance and scale advantages'.
Of course, storing data is one thing - to get value from it, it needs to be interrogated - and results pulled out in a meaningful way. This is what Microsoft calls 'insights' - meaning that the data (hopefully) can change the way you run your organisation.
Reporting for everyone
Project 'Crescent' adds to SQL Server 'reporting services for everyone' - not just SQL experts. Creating a report is now easier than ever and can be undertaken by business people. As the data unfolds in a fluid animation, clicking on the 'shape' of the data allows it to be explored and interrogated in a pretty intuitive and interactive fashion. It's a great achievement - and one which will save the need for developers to code endless 'fancy' front-end reporting panels.
PowerPivot has been enhanced so that it's far easier to create reports using the tools most people have on their desks - such as Excel and SharePoint. Particularly impressive is that this visual reporting takes place at a lightning-fast rate, with very little lag at all. Reports can now be exported in the newer Microsoft Office formats which were first introduced in Office 2007 - delivering data right to the desktop in a format anyone can use.
Data Quality Services is another totally new SQL Service - it essentially provides tools which allow those who manage the data to easily maintain the data's quality and integrity.
Going to the cloud
The buzzword of the last couple of years is definitely 'cloud' - and SQL Server doesn't disappoint in this department. SQL Server databases can be readily deployed on a public or private cloud, delivering solutions which can be easily scaled to almost any size - and, better still, quickly moved around to other cloud providers. It's what Microsoft calls 'the cloud on your terms'.
SQL Server's Developer Tools (code name 'Juneau') provide a single environment for database development - whether the data will be on the premises or in the cloud. SQL Azure and SQL Server provide support for moving a data-tier application across server, cloud or SQL Azure.
As we said at the start, there are improvements aplenty - for example, PowerShell 2 support, FileTable (to bring Win32 namespace support/compatibility) and greater interoperability between SQL Server and SQL Azure applications and other industry standard APIs. At the other end of the scale, even SQL Server Express has had a facelift: the new version LocalDB is even more lightweight that can be installed quickly and need no configuration.
Yet again, this version of SQL Server is a winner. Upgrading a SQL infrastructure is definitely not a trivial task, so many organisations will rightly wait until they are both sure of the technology and able to afford the leap - but the leap is well and truly worth the effort.
