What's New in SQL Server 2014 - Overview


What are the new features in SQL Server 2014?  


In this post I will be covering briefly new and enhanced feature of SQL Server 2014. This post is intended to give visibility into new and enhanced features but I will not be covering technical detail (process and procedure) of these features.

Below are the new or enhanced features of SQL Server 2014

1.       Memory Optimized Objects

In SQL Server 2014, memory-optimized Engine is basically integrated in SQL Server engine. You can define SQL Server objects such as Tables and Store procedures to run in the memory. In previous versions, almost all the operations on sql server objects contained I/O process that means it involved disk back and forth during sql server operations such as select, insert, update and delete. With Memory Optimized Engine, these operations will take place in memory instead of SQL Server going back and forth to Disk drives.

Memory Optimized OLTP is completely transactional based, all the processing take place in the form of transactions, however it is being done in memory if you declare those objects to take advantage of memory-optimized feature. There are some prerequisites that need to be considered before you can use this feature. I will be covering all that in a separate post.

2.       SQL Server Data Files in Windows Azure

This features is mainly to move or have your database files on windows Azure Blob storage, Azure Blob storage basically is huge storage system that’s chunked in 1TB of Blobs, you can use this storage to keep your database files, it provides benefits of migrating database from one location to another, replicating database files from different location within the Azure cluster or you can copy it back to on-premises system in order to restore the database using attach/detach method.

3.       Hosting SQL Server in Windows VM

This feature enables you to deploy your existing on-premises database to Windows Azure SQL Server VM, in other words you can migrate your existing on-premises SQL Server to Azure using migration Wizard.

4.       Backup and Restore

If you are familiar with SQL Server 2012 SP1, There is new option introduced in 2012 SP1 was to backup SQL Server Database to URL, however this option can only be utilized using either T-SQL , PowerShell and SMO. But in SQL Server 2014, you can use management studio to accomplish backing and restoring your databases to URL.

Keep in mind when scheduling SQL Server Database backups, the only URL supported in this operation is Windows Azure URL, you can’t use your existing URL’s i.e. Sharepoint file system URL. May be Microsoft next releases of SQL Server 2014 include this feature to use other URLs than Windows Azure.

5.       Redesign of Query Plans and Performance boost

Query Plans use cardinality estimator to provide query execution plans in SQL Server, in SQL Server 2014; estimator is redesigned to improve accuracy as well as quality of query plans which helps to improve the performance of your query. Again this is just an enhancement to provide a quality query plans. Personally, I didn’t see much of a change using it, there might be some queries which can take advantage of this enhancement while others remain the same.

6.       Reduced Latency – Delayed Durability

I found this feature quite beneficial, however it totally depends on organizational structure for example, Finance oriented organization, it is very important to NOT lose any data, while other organizations, it is tolerable to lose some data as long as they can recover most of the data. So in SQL Server 2014, there is a concept introduced called delayed durability, which actually means that client application will not wait from SQL Server that its transactions are written on the disk, transaction on sql server side will be stored in memory and will be written in batches so it will reduce lots of I/O operations hence reduce the latency. But again you have to be very careful making your transactions delayed durable or not.

7.       Always On – Availability Group (AG)

a)      Always on concept was introduced in SQL Server 2012. Purpose of this concept was high availability of the database and isolates some of the heavy read such as reporting from the database. Always on was accomplished using feature called Availability group, basically you can provide replica of your production database as read only database onto another SQL Server instance so that reporting or other heavy read of database can run on secondary replica without causing lots of load on actual production database. In SQL Server 2012, secondary copy of your database were limited to 4 replicas, however in SQL Server 2014, it is increased to 8 secondary replicas.

b)      If primary replica goes down, secondary replica will remain online for read operations in SQL Server 2014.

c)       Failover Clustered instances can share clustered shared volumes as clustered shared disks

8.       Table Partitioning and Indexing

In SQL Server 2014, you can rebuild individual partition of a partitioned table

9.       Columnstore Indexes

This feature is one of my favorite, columnstore indexes were introduced in 2012, however in 2012, you can only create non-clustered columns store indexes, but in SQL Server 2014, you can create Clustered Columnstore indexes and they are updatable. It greatly enhanced the data warehouse performance.

10.   Buffer Pool Extension

Buffer Pool Extension is a great feature of SQL Server 2014, it enables buffer pool to extend buffer pool size between RAM and SSD (Solid State Drive). Just to give you a little background about buffer pool if you already don’t know it, buffer pool is primary memory allocation source of SQL Server. It is a key component in achieving efficiency. It serves two purposes.

1. Access and update database pages

2. Buffer pool to reduce I/O
             This feature helps SQL Server Buffer pool to store larger worksets in the pool, hence make   
             SQL Server 2014 more efficient in its operations.



11.   Resource Governor Enhancement
       Resource Governor helps to control your resources such as CPU, IO etc. In SQL Server 2014 you can assign physical I/O resource of a particular resource pool, depending upon priority and urgency of the operation, this value can be set to manipulate the resources in SQL Server 2014

12.   Incremental Statistics
       This feature will allow you to create or update statistics of a particular partition if your table is partitioned. Before that, you had to update statistics of the whole table

13.   Online Index Operation
       Online index operation progress can be monitored real time as partitionID and PartitionNumber column is added in SQL Server 2014 and you can query the progress in order to get real time status of online index rebuild Job

14.   Database compatibility Level 

      Compatibility 90 (SQL Server 2005) is not valid in SQL Server 2014  
 
 
 

11 comments:

  1. I find your posts cool and refreshing. I am handling a blog network right now and will definitely seek your advice as the network matures. Stock Market Courses in Delhi

    ReplyDelete
  2. I always like to read a quality content having accurate information regarding the subject and the same thing I found in this post. Nice work
    Website Kalyan Matka Results

    ReplyDelete
  3. Sometimes, if you stand on the bottom rail of a bridge and payforessay lean over to watch the river slipping slowly away beneath you, you will suddenly know everything there is to be known

    ReplyDelete
  4. In addition to my personal life and studies https://www.topwritersreview.com/reviews/trustessaywriting/

    ReplyDelete
  5. In addition to my personal life and studies topwritersreview

    ReplyDelete
  6. Looking for all the latest styles of jackets heath ledger joker costume and coats for men? Shop the best collection of men's jackets and coats. Free shipping available.

    ReplyDelete
  7. Oracle Financials Cloud training will provide an in-depth understanding of your applications, including Accounting Hub, General Ledger, Payables, Receivables, and Revenue Management, and teach you how to set up, administer and use your Financials Cloud applications.

    There are various categories of Oracle Financials Cloud training which is mentioned below:

    oracle hfm training
    Oracle Accounting Hub Cloud Training
    Oracle General Ledger Cloud Training
    Oracle Payables Cloud Training
    Oracle Receivables and Collection Cloud Training
    Oracle Revenue Management Cloud Training
    Oracle Financial Reporting Cloud Training
    Complete Customization of Oracle Financials Cloud training’s course content is possible for Individual students and for Corporate. Oracle Financials Cloud online training is available for individuals and for corporate we may arrange the classroom as well. For more information on Oracle Financials Cloud, training does connect us.
    Email: contact@maxmunus.com
    Call / WhatsApp: +919035888988

    ReplyDelete
  8. Great I really like your post. Such amazing information, all the best for more updates. Thanks for sharing. It is such a very amazing post...
    Click here: Utility kilt

    ReplyDelete
  9. In a fast-paced world, prioritizing our health is paramount. Harnessing the ancient wisdom of Ayurveda, you can now seamlessly enhance your well-being with the convenience of modern technology. Buy Ayurvedic Medicine Online and embark on a journey towards holistic wellness.

    ReplyDelete
  10. A corrugated box is a type of packaging made from corrugated fiberboard, which consists of a fluted corrugated sheet sandwiched between one or two flat linerboards. These boxes are widely used for shipping, transporting, and storing goods due to their strength, durability, and versatility.

    Corrugated boxes come in various sizes, shapes, and strengths to accommodate different products and shipping needs. They are commonly used in industries such as e-commerce, retail, food and beverage, electronics, and manufacturing.

    The corrugated structure of these boxes provides cushioning and protection to the contents, making them ideal for shipping fragile or delicate items. They are also lightweight, which helps reduce shipping costs, and they can be easily customized with printing for branding or product information.

    Overall, corrugated boxes are a popular choice for packaging and shipping due to their cost-effectiveness, recyclability, and ability to provide adequate protection to the items they contain.

    Buy Packaging Material Online. High-quality corrugated boxes, and courier packaging bags at best prices. Fastest shipping in India through Avon Packaging.

    Address:- 48 KM Miles Stone, Delhi-Mathura Road, Prithla, Sector 10, Palwal , Haryana, India, Pin:121102

    Contact:- 8950869007

    ReplyDelete