In-Memory OLTP or online-transaction processing is a database engine that is memory-optimized specifically for OLTP operations and is part of SQL Server’s engine. Cloud backup, manual or automatic, has been simplified with point in time restore at instance level, and measures databases usage patterns to set backup frequency. Moreover there is a fast disaster recovery with a low Recovery Time Objective, which is easy to deploy and manage.
In SQL 2012, if your primary drops offline or the cluster loses quorum, the readable replica databases drop offline. (This is hilarious because it’s right when you really want to be able to query the secondaries.) No way to control it – it’s totally automatic. In SQL 14, the secondaries remain online and readable when the primaries aren’t available. However, keep in mind that typical AlwaysOn AG connections sql server 2014 new features for dba go through the AG listener name, and then fetch the list of readable replicas from the primary. This just means that in order to keep your report queries online, you can’t use the AG listener – you have to connect directly to the replica’s server name. I like using a separate set of DNS records for readable replicas, like readonly.mydomainname.com, and have my report servers point at those.
Related blog articles
Even new Dynamic Management Views have been added to increase ease of troubleshooting. You can read about them on my previous tip “Understanding Dynamic Management Views in SQL Server 2014”. As I told you before, this version of SQL Server was conceived as a platform for
a Hybrid Cloud.
Another interesting new feature is the introduction of an XML compression option
in the CREATE TABLE and CREATE INDEX commands. This option allows us to compress
off-row XML data for both XML columns and indexes. Another interesting new feature of Query Store is the possibility to set up query
hints for the queries in the Query Store using the sp_query_store_set_hints
and sp_query_store_clear_hints stored procedures. SQL Server 2022 added support to create certificates from PFX files. Additionally,
you can back up and restore certificates and symmetric keys from Azure BLOB storage. Another improvement is that system-generated certificates now use RSA-3072 by default.
SQL Server 2014 Key Features and Enhancements
The most talked about and most awaited feature of SQL Server 2014 is most undoubtedly the new In-Memory OLTP engine, internally called Hekaton. It was announced at PASS 2012 and was warmly welcomed by the industry.
- In-Memory OLTP or online-transaction processing is a database engine that is memory-optimized specifically for OLTP operations and is part of SQL Server’s engine.
- Because the clustered columnstore index can be updated, then the workload can perform more delete, update and insert operations.
- Because ssd has much better IO performance than normal disk we get the benefits of a very large buffer pool without adding more memory.
- The setting MAX_OUTSTANDING_IO_PER_VOLUME which is available through ALTER RESOURCE GOVERNOR will set the maximum number of IO operations per disk volume.
The new estimator should provide more consistent and predictable query performance, as well as more accurate cardinality estimates so better query plans can be chosen. SQL Server 2014 will now allow you to extend the memory SQL Server uses by using nonvolatile random access memory (aka Solid State Drives) to greatly improve I/O throughput. This feature is designed to help offload the small random I/Os that SQL Server has traditionally done to mechanical disks, to Solid State Drives. This feature is currently targeted for only being available to those that purchase Enterprise 64 bit version of SQL Server 2014. SQL Server 2014 substantially improved the component of the engine that creates and optimizes query plans.
Getting started with Greenplum – 2 – Initializing and bringing up the cluster
Check out this summary of new features and things to investigate. In which Phil illustrates an old trick using STUFF to intert a number of substrings from a table into a string, and explains why the technique might speed up your code… I’ve grown up reading Tom Clancy and probably most of you have at least seen Red October, so this book caught my eye when browsing used books for a recent trip. It’s a fairly human look at what’s involved in sailing on a Trident missile submarine… Below is a listing of the SQL Server 2014 new features and where you can find more information on them.
In SQL Server 2014 single partitions can be rebuilt and additional partition switching and index rebuild operations can be performed while
the table is online. The Resource Governor lets you limit the amount of CPU and memory that a given workload can consume. SQL Server 2014 extends the reach of the Resource Governor to manage storage I/O usage as well. The SQL Server 2014 Resource Governor can limit the physical I/Os issued for user threads in a given resource pool. The new columnstore index gives you the possibility to update the underlying table without dropping the columnstore index like you were forced to do in previous versions. Also, the columnstore index in SQL Server 2014 has to use all columns from the table and cannot be combined with another index.
0 σχόλια