The big drawback to ColumnStore Indexes is their read-only status, which makes them useful only for queries in data warehouses with huge databases. OLTP databases and ColumnStore Indexes are, by their nature and almost by definition, mutually exclusive.
Even in a data warehouse milieu, frequently loading new data into read-only tables can be quite a hassle. Microsoft describes a workaround for the read-only problem by having you switch out table partitions in your data warehouse tables. If you are desperate for better performance, the workaround might be acceptable. Alternatively, you might opt to use SQL Server 2012's read-only secondary feature to manage the database copies you use for analysis and reporting.
Speaking of indexes - SQL Server 2012's improvements in online re-indexing are a welcome relief to administrators who from time to time have to re-index a database. SQL Server 2005 touted an online re-indexing feature, but the earlier version's fine print mentioned that the indexing didn't work for all data types (the problem types were varchar(max), nvarchar(max), varbinary(max) and XML). SQL Server 2012 removes the restriction so that administrators can have true online index maintenance for applications that are supposed to be online and available 24/7.
We don't want to appear excessively greedy, but next we'd like to see in SQL Server an ability to re-index individual table partitions online. We have a few other issues, as well. Missing from SQL Server 2012 is any significant use of PowerShell, which helps customers automate tasks through the use of commandlets. Other than a few commandlets for AlwaysOn and some backup/restore functions, SQL Server 2012 has no reliance on PowerShell. With the emphasis Microsoft is putting on PowerShell, we found the omission disappointing.
Ironically, the SQL Server 2012 installation process uses PowerShell. As with virtually every other current version of a Microsoft server product, Windows PowerShell 2.0 is a requirement for deploying SQL Server 2012.
We were also disappointed by the lack of improvements to SQL Server Management Studio (SSMS). Yes, Microsoft has given SSMS a Visual Studio 2010 makeover, which means you get better snippet management as well as integration with Team Foundation Server, but SQL Server 2012 offers no new DBA management tools. For instance, we would have liked to have seen better multi-server management and reporting features, as well as some use of PowerShell in SSMS.
SQL Server 2012's many new features (some of which, like programming language enhancements, we haven't even touched on) are a good reason to upgrade. There's something to like for nearly everyone. Just be aware that the new version costs more, will likely increase administrator workloads and might use quite a bit more bandwidth than earlier SQL Server versions.
Nance runs Network Testing Labs and is the author of Introduction to Networking, 4th Edition and Client/Server LAN Programming. His e-mail address is email@example.com.
Read more about software in Network World's Software section.
- Enable secure remote access to 3D data without sacrificing visual perfomance Design and manufacturing companies must adapt quickly to the demands of an increasingly global and competitive economy. To speed time to market for...
- Simplifying Product Design In A Complex World Product design engineering has moved far beyond the confines of ever-more powerful workstations. Companies can't afford to restrict projects to using only local...
- A Reference Architecture for the Internet of Things The aim of this is to provide Architects and Developers of IoT projects with an effective starting point that covers the major requirements...
- How to Reduce Hardware & Infrastructure Costs Through Data In this paper, we take a look at how organizations are revisiting their network and server architecture in a bid to address the...
- NSS Labs & Cisco Present: Evaluating Leading Breach Detection Systems Today's constantly evolving advanced malware and APTs can evade point-in-time defenses to penetrate networks. Security professionals must evolve their strategy in lockstep to...
- Will the Real Endpoint Threat Detection and Response Please Stand Up? This webinar explores new technologies & process for protecting endpoints from advanced attackers as well as the innovations that are pushing the envelope... All Hardware White Papers | Webcasts
Our new weekly Consumerization of IT newsletter covers a wide range of trends including BYOD, smartphones, tablets, MDM, cloud, social and what it all means for IT. Subscribe now and stay up to date!