SQL Server Pro Magazine April 2014
Written by Ian Stirk   
Tuesday, 08 April 2014

Discover all the new and exciting features of the just released SQL Server 2014, inside this month’s SQL Server Pro Magazine

SQL Server Pro is an online subscription-based monthly magazine that has news and articles from across the SQL Server world, much of which is also freely accessible on its website. The items in bold below correspond to the name of the article in the current issue.

New Features in SQL Server 2014 has to be one of the most eagerly awaited SQL Server articles of recent times. The article provides a concise summary of the major new features in the just released SQL Server 2014. The primary feature must be the new In-Memory OLTP Engine, which allows certain tables to reside in memory, and selected stored procedure to be compiled – both these things can dramatically improve performance. You can expect a ten-time improvement in performance, maybe more! (Also see:SQL Server 2014 Released.)

There’s enhanced Windows Server 2012 integration, with the ability to scale-up to 640 logical processors and 4TB of memory in a physical environment. Additionally, Solid State Disks (SSDs) can be integrated to extend SQL Server 2014’s buffer pool. Resource Governor now allows physical I/O usage to be capped, providing another filter to query prioritisation.

There are enhancements to AlwaysOn Availability Groups, with the number of secondary replicas increasing from 4 to 8. Replicas are great for offloading read-only activity (e.g. reporting, extraction, backups) from the primary server. Additionally, asynchronously available secondaries can be created in Windows Azure, very useful for Disaster Recovery purposes.
Several lesser advertised features are discussed, including:

  • Backups have been enhanced to incorporate encryption
  • Columnstore Indexes are now updatable
  • Improved SQL Server Data Tools for Business Intelligence
  • Power BI for Office 365 Integration

Not all subsystems have been enhanced, for example, there are no changes to replication, SSIS and SSRS. Links are included to show features that have been deprecated and discontinued.  
This is an exciting release, the promise of significant performance improvements surely is a tempting incentive to upgrade to SQL Server 2014.

With the lure of improved performance there will be a lot of interest in taking advantage of SQL Server 2014’s In-Memory OLTP engine. The SQL Server 2014’s Analysis, Migrate, and Report (AMR) Tool promises to make this optimization process much easier, and eliminate the guesswork from what to migrate. The aim of the AMR tool is to identify tables and stored procedures that will provide the best performance gains after migration to In-Memory OLTP.

The article provides step-by-step guidance of how to run the AMR tool, discussing the options available, and the meaning of the various outputs produced. There are plenty of screenshots included to assist you in your migration to better performance.

The database corruption series ends appropriately with Responding to Corruption. Having identified corruption there are certain things you should and shouldn’t do, in summary these are:

  • Don’t reboot your server  
  • Don’t detach then re-attach your databases
  • Know the nature and scope of corruption before attempting repairs
  • Consider rerunning DBCC CHECKDB if problems are minor
  • Size up corruption and remediation options before doing anything
  • Validate any repair options in test environment first
  • Run DBCC CHECKDB with the REPAIR_BUILD option
  • Execute page-level restore operations if possible
  • Execute a full recovery
  • Use DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS as a last resort

This has been a most welcomed series, both extensive in its range and considered in its opinion. If you’ve been bitten by database corruption in the past, you’ll know the importance of this series. If you haven’t experienced corruption, consider yourself lucky – and prepare for it now, by reading this instructive series of articles.

T-SQL Interval Graphs Challenge continues the series of articles on intervals and counts. This installment provides both cursor and set-based solutions to the real-world interval packing problem. There’s plenty of useful code provided. If you like T-SQL puzzles, you’ll certainly enjoy the solutions provided here.

The editorial discusses Documenting Code: A Necessary Evil, or Just Plain Wrong? The premise being that with meaningful method names and variable names the code becomes self-documenting. The author convincingly argues that with a diverse range of developer ability, together having to support legacy code, there is definitely a need for comments. To me, the code says how something is done, the comments tell me what’s being done and why, often providing a higher level overview, helping me understand more quickly what is happening. I suspect the current comment-less code vogue is a passing fashion (like wearing shoes without socks?).

The main point made in Visual Studio Online FAQs is that this new release isn't related to the Visual Studio development tool, rather it is an online version of Team Foundation Server (think source code repository and project management tool) – so quite a misleading title from Microsoft. The article doesn’t make any explicit reference to SQL Server, and I’m not sure the article belongs in this magazine.

I thoroughly enjoyed this month’s issue, with its emphasis on the just released SQL Server 2014, discussing all the major new and exciting features, perfect timing! Now all I need to do is download it and experiment…


More Information

SQL Server Pro April 2014

Related Articles

SQL Server 2014 Released

Looking Forward to SQL Server 2014 (September issue)

In-Memory OLTP In Depth

To be informed about new articles on I Programmer, install the I Programmer Toolbar, subscribe to the RSS feed, follow us on, Twitter, FacebookGoogle+ or Linkedin,  or sign up for our weekly newsletter.



Meet Ambidex and Rookie From NAVER

Robots may not be ready yet for the great takeover of jobs that some people fear, but in South Korea, where there are 1,000 robots per 10,000 workers, service robots are finding a role. 

Kotlin 2 Released With Multiplatform K2 Compiler

Kotlin 2.0 has been released. The new version is a major update with improvements including a stable K2 compiler, which is multiplatform from the ground up, and according to JetBrains understands your [ ... ]

More News


raspberry pi books



or email your comment to: comments@i-programmer.info

Last Updated ( Tuesday, 08 April 2014 )