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…