Whats New with SQL Server 2014

As you’d expect with SQL Server,
each new release has brought many new features over the years. A complete listing can be found
in the links in the additional information section. You can encrypt your data while you are performing a backup, independently of the destination – whether internal or external. You have to specify the encryption algorithm and the encryptor when creating the backup.

This release also includes the possibility to
Backup and
Restore to/from
a URL directly with SQL Server Management Studio. When you first hear about it, SQL Server’s In-Memory OLTP sounds like a new feature that’s hard to implement, requires a lot of schema changes, and takes planning and downtime. Here Brent Ozar tells you how it’s simply not true, and you can do it in just 3 steps. There have also been some changes in terms of security permissions for SQL Server 2014. Just as the title says, you are able to deploy a SQL Server database to a Virtual Machine that resides in Azure. You can find more information and guidelines about this, here.

In-Memory OLTP

In-Memory OLTP is a strong point of SQL Server 2014 and it promises to be a big boost in database application performance, which is why you should really give it a good thought. This option should be used for archival or other cases in which you require a small storage size for your data and you cannot afford to add more time for storage and retrieval. In terms of OS’s which support this feature, you can use either Windows Server 2012 or Windows Server 2012 R2 and also Windows Server 2008 R2 SP2. Also, you will have to use either one of the Enterprise, Developer, or Evaluation editions of SQL Server 2014 in order to have access to In-Memory OLTP. This new version of SQL Server introduces an implementation of blockchain.

Do you need a quick estimated distinct count on that enormous billion-row table? Always Encrypted with Secure Enclaves may be just the solution you’re looking for. When Microsoft introduced the columnstore index in SQL Server 2012, it sql server 2014 new features for dba provided improved performance for data warehousing queries. For some queries, the columnstore indexes provided a tenfold performance improvement. However, to utilize the columnstore index, the underlying table had to be read-only.

(Almost) Everything You Need to Know About SQL Server 2014

You can find more detailed information about this feature, here. The new estimation logic which was designed for the cardinality estimator has improved the quality of the query execution plans and thus has a direct impact on the performance of queries. This feature will give you the possibility to create a SQL Server database that is running either on-premises or inside a virtual machine in Azure, with dedicated storage in Azure Blob Storage. Allows logins to perform SELECT Statements in all databases that the login has CONNECT permission. With the SQL Server 2014 release, we can set constraints on the physical IO operations.

• Need to use scalar UDFs but were afraid of the performance hit?
• You can now move the tempdb’s system tables into memory-optimized tables taking the pressure off your disks.
• With this new release of SQL Server 2014 there are a number of new features that Microsoft has included.
• SQL Server 2014’s native backup supports Windows Azure integration.
• I like using a separate set of DNS records for readable replicas, like readonly.mydomainname.com, and have my report servers point at those.

We’ll help you sort through the good and bad, and find some hidden gems along the way. I teach SQL Server training classes, or if you haven’t got time for the pain, I’m available for consulting too. If you really need to scale out your reads, SQL 14 gives you up to 8 secondaries (up from 4). Of course, you’ll be paying for Enterprise Edition licensing on these, but if you were already going to replicate data out to various reporting or BI servers, now your life is easier. This feature enables SQL Server to be configured to use a SSD disk as an extension for the Database Buffer Pool in order to reduce latency.

Higher Availability

You can use also audit to find out who accessed your confidential data. A lot of the differences in each edition are hardware/performance based. That said there are some functional differences between editions as well, so you
need to be careful when selecting an edition. Microsoft has increased the performance of the SELECT INTO statement. They have done this by allowing the SELECT INTO to perform parallel operations when inserting rows into a table. With SQL Server 2014 the development team built a new cardinality estimator.

• The new estimator has incorporated algorithms and assumptions which work with modern OLTP and data warehouse workloads.
• Azure Arc-enabled servers allow you
  to manage Windows and Linux physical and virtual servers hosted outside of Azure.
• Learn almost everything you need to know about SQL Server 2014 in Brent’s rundown of new features in SQL Server 2014.
• This article highlights a few key enhancements that are built into the product.

You can now specify how long an index rebuild should wait for the locks required. This option also allows us to selectively terminate blocking processes caused by online index rebuilds. Microsoft has enhanced AlwaysOn integration by expanding the maximum number of secondary replicas from four to eight. Readable secondary replicas are now also available for read workloads, even when the primary replica is unavailable. In addition, SQL Server 2014 provides the new Add Azure Replica Wizard, which helps you create asynchronous secondary replicas in Windows Azure. Microsoft introduced some significant enhancements in SQL Server 2014—especially with In-Memory OLTP.