Skip the navigation

Microsoft raises the bar with SQL Server 2012

By Barry Nance
July 16, 2012 12:40 AM ET

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.

Conclusion

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 barryn@erols.com.

Read more about software in Network World's Software section.

Originally published on www.networkworld.com. Click here to read the original story.
Reprinted with permission from NetworkWorld.com. Story copyright 2012 Network World, Inc. All rights reserved.
Our Commenting Policies
Consumerization of IT: Be in the know
consumer tech

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!