Basics of Dedicated Administrator Connection (DAC) in SQL Server

SQLServerF1

What is Dedicated Administrator Connection (DAC) in SQL Server?
Microsoft SQL Server gives a Dedicated Admin Connection (DAC). The DAC permits a head to get to a running case of SQL Server Database Engine to investigate issues on the server—notwithstanding when the server is lethargic to other customer associations. The DAC is accessible through the sqlcmd utility and SQL Server Management Studio. The association is just permitted from a customer running on the server. No system associations are allowed.

To utilize SQL Server Management Studio with the DAC, unite with a case of the SQL Server Database Engine with Query Editor by writing ADMIN: before the server name. Object Explorer can’t join utilizing the DA

How to Enable Dedicated Administrator Connection (DAC) for SQL Server instance?
To enable DAC, Connect to the instance and open a new query window, run the following command

sp_configure ‘remote admin connections’, 1;
GO
RECONFIGURE;
GO

How to verify if Dedicated Administrator Connection (DAC) is enabled on a SQL Server Instance?
We can verify or confirm that Dedicated Administrator Connection (DAC) is working by connecting to SQL Server instance using the following in the “Instance” field of SSMS login window

ADMIN:

How many Dedicated Administrator Connections (DAC) can be opened at a time for a particular SQL Server instance?
Only one connection can be made using Dedicated Administrator Connections (DAC).

When is the Dedicated Administrator Connections (DAC) to be used?
Dedicated Administrator Connections (DAC) can be used in cases where SQL Server is running but not responding to any regular connections due to resource usage. DAC connection has its own memory and scheduler and dedicated thread, which allows connection through DAC to run queries even when the SQL Server is not responding. It is not advisable to run a large or complex queries with DAC, rather it is to be used to identify the resource consuming queries causing SQL Server to hang or to not respond and kill any such offending sessions.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

SQL Server Instance Level Objects Backups

SQLServerF1

One of the important and common responsibility of a SQL Server DBA is to make sure a perfect backup/recovery plan is in place and that the plan can be implemented as per the SLA’s. Periodic testing is important to ensure that the plan works in case of unexpected disaster. In most of the environments, the backups of SQL Server databases are performed which include system and user databases, however there can be many scenarios where in one of the database may be corrupted and has to be recovered, in which case the restore of the database backups works great, but in some cases it is also a possibility that certain server level objects have to be recovered and restoring system databases is not an option and a time taking option. In such cases have backup of server level objects separately will serve greatly.

Thus it becomes important for a DBA to also consider performing backups of instance level objects periodically, so that the level of restores or control on the objects which required to be restored will be available. Also this will act as a secondary option in case the primary option of system database backups does not work in some situations. Below are the important SQL Server instance level objects which are to be backed up regularly and can be used later when required.

– SQL Server Instance Configuration properties: these setting can be backed up by saving the output of sp_configure values or the values of select * from sys.sysconfigurations.
– Database properties: The database properties gets backed up by the database backups, however saving these settings in a text document will be useful to keep track of settings and can be verified or compared if the values are changed by someone and we had to identify what were the values DBA team planned for.
– DDL triggers: There are server level triggers which should be backed up and will be useful if someone deletes the triggers.
– Backup Devices: During a backup operation on a SQL Server database, the backed up data (the backup) is written to a physical backup device. This physical backup device is initialized when the first backup in a media set is written to it. The backups on a set of one or more backup devices compose a single media set.

– Endpoints: SQL Server endpoint is the point of entry into SQL Server. It is implemented as a database object that defines the ways and means in which SQL Server may communicate over the network. SQL Server routes all interactions with the network via endpoints and each endpoint supports a specific type of communication.
– SQL Server Agent Properties: These settings are important and the backup of these settings are useful in cases where these values are changed by someone and we want to know what are the correct ones.
– SQL Server Agent Jobs: Very important to have backup of the jobs, so that we can recreate them easily without having to restore the MSDB database. It is important to keep in mind that some jobs will run DTS/SSIS packages which have be backed up separately where the packages have to be saved separately.
Linked Servers: Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel.
SQL Server Logins: Logins allow access to users to connect to SQL Server, so important to keep a backup, so that we can recreate the required logins if they are accidentally deleted.

Other SQL Server instance level objects which can be backed up include SQL Server Agent operators, SQL Server Agent alerts, SQL Server Server Roles, SQL Server Audits, etc

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Reviewing Security Configuration of SQL Server

SQLServerF1

Security is one of the most important aspects for any technology. As SQL Server is an RDBMS product which stores users data which is important and critical, so it is common to see many attempts been made to compromise and get access to the important user or business related data. This brings responsibility of the DBAs, developers, management to plan and implement best security practices to avoid any such attacks from succeeding. It is important to periodically review the security settings to make sure that there are no gaps or possibilities of security breach. Below are list of items or objects which a DBA team may want to periodically review and verify to make sure that the security configuration is as per best practices.

Database Owners: By default, any users who creates the databases becomes the owner of the database. Mostly DBAs who create the databases become the owner of that database, so it is important to immediately change the database owner to SA or other standard account. Periodically review the database owners to make sure there are no user specific domain accounts being the database owners.
Windows or SQL Server Logins: Periodically review all the windows logins, SQL Server logins and windows groups which has access to the SQL Server instance. Remove any logins which does not require access anymore. It happens that some user leaves the company but the logins still remain. Sometimes access is given on temporary basis for a project work and would not removed after the completion of the project. Periodic review of the logins should resolve this issue of unauthorized logins being present at server level or at SQL Server level.

Server Level Permissions: There are different kinds of server level and database level roles provided by SQL Server. Make sure that only the required permissions are granted to the users. Providing more permissions than what is required will cause serious problems. It is important to periodically review and revoke any additional accesses granted to any users.
SQL Agent Jobs Owned by Windows Domain Accounts: The DBA who creates a SQL Server Agent job by default becomes owner of that job, so make sure to change it to SA
.SQL Server Client Protocols: By default SQL Server allows communication through ports(TCP/IP, Shared Memory, Named Pipes, VIA). Mostly it is not required for all these protocols to be enabled, some of them can be disabled when application does not use them, so that any one trying to gain access through that protocol would be blocked.

SQL Server TCP/IP port: Default instance of SQL Server uses port 1433 by default, so it is advisable to change to this some other port other than 1433 as per best practices. But making this change may cause applications to fail connecting to SQL instance, so care has to be taken to make changes to application connection string to use the port number used by SQL Server or to enable SQL Browser service. SQL Server Named instances can be configured to use either permamnent statis port or a dynamic port which may change after SQL or server restart. It is advisable to configure static port for SQL Server Named instances.

The above checks or recommendations are high level and there are mode advanced check that are to be performed for more critical servers. There are compliances like SOX HIPAA which put rules to be followed for critical servers. Also, care should be taken to implement the recommendations and proper testing has to be done prior to any implementation to make sure applications does not break due to the changes.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

New SQL Server 2014 Hybrid Cloud

Most applications have some key difficulties, for example, high productivity, business esteem, complex equipment arrangements, monstrous crests on interest, and agreeing to industry and corporate regulations. Considering every one of these components and building an undertaking evaluation innovation can be extremely difficult. Microsoft Hybrid Cloud Strategy gives backing to customary, private cloud, open cloud, and cross breed cloud situations to beat these key difficulties.

At the point when your business obliges an adaptable IT foundation that can scale on interest, you can assemble a private cloud in your server farm or an open cloud in Windows Azure worldwide server farms. When you extend your server farm to meet general society cloud, you construct a mixture cloud model.

By utilizing Microsoft advancements, you can run code both on-premises and in the cloud, run in the cloud utilizing on-premises information, or run totally in the cloud utilizing more than one server farm. In this way, you can move your applications to the cloud at your own particular pace while protecting the benefit of existing legacy IT speculations.

There are half breed cloud situations that compass from on-premises SQL Server to Windows Azure open cloud offerings: SQL Server in Windows Azure Virtual Machines and Windows Azure Storage. Particularly which are

Backup and Restore Databases to/from Windows Azure Storage – A standout amongst the most major chairman assignments is going down and restoring databases. With SQL Server and Windows Azure, you can securely reinforcement your databases in the cloud.
Maintain Database Replicas on Windows Azure Virtual Machines – Having a stable catastrophe recuperation answer for your databases is fundamental for your business’ prosperity. Most clients need to design a catastrophe recuperation site and buy extra equipment for database copies. With SQL Server and Windows Azure, you can keep up one or more copies of your databases in the cloud.
Store SQL Server Data Files in Windows Azure Storage – Putting away on-premises SQL Server information documents in Windows Azure Storage gives an adaptable, solid, and boundless off-site stockpiling for your databases. Beginning with SQL Server 2014, you can utilize another highlight, SQL Server Data Files in Windows Azure, to store SQL Server database records in Windows Azure Storage. With this highlight, you can move information and log documents from on-premises database into Windows Azure Storage, while keeping the register hub of SQL Server running on-premises. This highlight empowers you to have boundless capacity limit in Windows Azure Storage.
Migrate existing SQL Server databases to Windows Azure Virtual Machines – The distributed computing conveys some key advantages to endeavors, for example, boundless virtualized assets are accessible for you on a pay-every utilization premise, you can influence openly accessible cloud server farms instead of building out and oversee server farms all alone, and consequently you can lower IT and equipment costs.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2014

Thanks,

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

List of SQL Server 2014 Enterprise Edition Features

There are some new features introduced with SQL Server 2014 and some are being changed to support advanced functions. Below are some of the top SQL Server 2014 features supported by SQL Server 2014 Enterprise Edition, Evaluation Edition, Developer Edition, Business Intelligence Edition.

Server Core support
Log Shipping
Database mirroring
Backup compression
Database snapshot
Alwayson Failover Cluster Instances
AlwaysOn Availability Groups
Connection Director
Online page and file restore
Online indexing
Online schema change

Fast recovery
Mirrored backups
Hot Add Memory and CPU
Database Recovery Advisor
Encrypted Backup
Smart Backup
Multi-instance support
Table and index partitioning
Data compression
Resource Governor
Partition Table Parallelism
Multiple Filestream containers
NUMA Aware Large Page Memory and Buffer Array Allocation
Buffer Pool Extension
IO Resource Governance
In-Memory OLTP
Delayed durability
Basic Auditing
Fine Grained Auditing
Transparent database encryption
Extensible Key Management
User-Defined Roles
Contained Databases
Encryption for Backups
SQL Server change tracking
Merge replication
Transactional replication
Snapshot replication
Heterogeneous subscribers
Oracle publishing
Peer to Peer transactional replication
Policy automation
Performance data collector
Standard performance reports
Plan guides and plan freezing for plan guides
Direct query of indexed views
Automatic indexed view maintenance
Distributed partitioned views
Parallel indexed operations
Automatic use of indexed view by query optimizer
Parallel consistency check
Change Data Capture Service for Oracle by Attunity
Change Data Capture Designer for Oracle by Attunity
High performance Oracle destination
High performance Teradata destination
SAP BW source and destination
Data mining model training destination adapter
Dimension processing destination adapter
Partition processing destination adapter
Change Data Capture components by Attunity
Connector for Open Database Connectivity
Create cubes without a database
Auto-generate staging and data warehouse schema
xVelocity memory optimized columnstore indexes

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2014

Thanks,

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Changes to Editions and Components of SQL Server 2014

Microsoft SQL Server 2014 software Editions are categorized into different sets based on the features offered mentioned as below.
Principal Edition(Enterprise, Business Intelligence, Standard Edition) – The premium offering, SQL Server 2014 Enterprise version conveys extensive top of the line datacenter abilities with bursting quick execution, boundless virtualization, and end-to-end business insight.
Specialized editions(Web Edition) – SQL Server 2014 Business Intelligence version conveys extensive stage enabling associations to manufacture and convey secure, adaptable and sensible BI arrangements.
Breadth editions(Developer, Express Edition) – SQL Server 2014 Standard version conveys essential information administration and business brainpower database for offices and little associations to run their applications and backings basic advancement devices for on-reason and cloud

On an Internet server, for example, a server that is running Internet Information Services (IIS), you will normally introduce the SQL Server customer instruments. Customer apparatuses incorporate the customer network segments utilized by an application uniting with an example of SQL Server. You can introduce simply the SQL Server customer segments on a PC that is running customer/server applications that associate specifically to a case of SQL Server. A customer segments establishment is likewise a decent choice in the event that you direct an example of SQL Server on a database server, or on the off chance that you plan to create SQL Server applications.

Below are the SQL Server Components which can be installed with the SQL Server media.
SQL Server Database Engine
Analysis Services
Reporting Services
Integration Services
Master Data Services

Along with SQL Server services, there are also additional tools that can be installed to management and development purposes. Below are some of the components that can be installed on SQL Server box or on application servers or remote servers.
SQL Server Management Studio
SQL Server Configuration Manager
SQL Server Profiler
Database Engine Tuning Advisor
Data Quality Client
SQL Server Data Tools
Connectivity Components
SQL Server Books Online

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2012
SQL Server 2014

Thanks,

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Number of CPU Limits for SQL Server 2014

SQLServerF1

Below are the number of CPUs supported by the different editions of SQL Server 2014.

Enterprise Edition: Core-based Licensing1 – Operating system maximum
Developer Edition – Operating system maximum
Evaluation Edition – Operating system maximum
Business Intelligence Edition – Limited to lesser of 4 Sockets or 16 cores
Standard Edition – Limited to lesser of 4 Sockets or 16 cores
Web Edition – Limited to lesser of 4 Sockets or 16 cores
Express Edition – Limited to lesser of 1 Socket or 4 cores
Express with Tools – Limited to lesser of 1 Socket or 4 cores
Express with Advanced Services – Limited to lesser of 1 Socket or 4 cores

Enterprise Edition with Server + Client Access License (CAL) based authorizing (not accessible for new understandings) is constrained to a greatest of 20 cores every SQL Server case. There are no restrictions under the Core-based Server Licensing model.

In a virtualized domain, the process limit utmost is taking into account the quantity of intelligent processors, not centers, on the grounds that the processor structural engineering is not unmistakable to the visitor applications. Case in point, a server with four attachments populated with quad-center processors and the capacity to empower two hyperthreads every center contains 32 legitimate processors with hyperthreading empowered yet just 16 intelligent processors with hyperthreading incapacitated. These sensible processors can be mapped to virtual machines on the server with the virtual machines’ register stack on that legitimate processor mapped into a string of execution on the physical processor in the host server.

You may need to incapacitate hyperthreading when the execution every virtual processor is essential. One can empower or handicap hyperthreading utilizing a BIOS setting for the processor amid the BIOS setup, however it is regularly a server perused operation that will affect all workloads running on the server. This may propose dividing workloads that will run in virtualized situations from those that would advantage from the hyperthreading execution support in a physical working framework environment.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2014

Thanks,

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

TOP KNOWN ISSUES WITH MICROSOFT SQL SERVER 2014 CU4

SQLServerF1

You may receive below error on a table that contains non-unique indexes and when you run a query to update the table by using the bitmap index that contains a partition key
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object <table name> with unique index <index name>. The duplicate key value is (0, <the value>).
The issue has been fixed in the cumulative update 4 (CU4) of SQL Server 2014.
https://support.microsoft.com/kb/2999197

You may receive below kind of deadlock error when parallelism is used at server or query level.
(Process ID 85) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
The issue was fixed in the cumulative update 4 (CU4) of SQL Server 2014.
https://support.microsoft.com/kb/2999197

Incorrect results may be returned when you use Option(Recompile) for queries inside a procedure and involves join to other tables and the joined table is updated from another transaction in SQL Server 2014
The issue was fixed in the cumulative update 4 (CU4) of SQL Server 2014.
https://support.microsoft.com/kb/2999197

When using SQL Server 2012 service broker as an initiator to send high number of messages to a remote target, a race condition might occur leading to an access violation and deadlocked schedulers and may cause service broker to stop working.
The issue was fixed in the cumulative update 4 (CU4) of SQL Server 2014.
https://support.microsoft.com/kb/2999197

When a query is run which contains common language runtime (CLR) functions in the WHERE clause against an indexed view, may cause an fatal exception and that session may get disconnected.
The issue was fixed in the cumulative update 4 (CU4) of SQL Server 2014.
https://support.microsoft.com/kb/2999197

When manual failover is performed on a mirrored database, you may notice that database takes a long time to recover and to come online on the new Principal server or the database stays in “In Recovery” status for a long time and does not assume the role of the mirror database on the new mirror server and at the same time, the new Principal database may also get into “Principal, Disconnected” state.
The issue was fixed in the cumulative update 4 (CU4) of SQL Server 2014.
https://support.microsoft.com/kb/2999197

In Merge replication when a custom stored procedure resolver is used to resolve conflicts, there may be additional characters added to the character type data after conflict resolution. Also the merge agent may fail and following error may be logged in the replmerg.log file
ERROR: ErrNo = 0x8004565a, ErrSrc = <null>, ErrType = 8, ErrStr = The Merge Agent failed because the schema of the article at the Publisher does not match the schema of the article at the Subscriber. This can occur when there are pending DDL changes waiting to be applied at the Subscriber. Restart the Merge Agent to apply the DDL changes and synchronize the subscription.
The issue was fixed in the cumulative update 4 (CU4) of SQL Server 2014.
https://support.microsoft.com/kb/2999197

With release of each new version and new service packs of SQL Server, new issues or bugs will surface and Microsoft will work on fixing high priority bugs. Above are some of the top known issues which were identified and fixed in Microsoft SQL Server 2014 CU4.

This is applicable on below versions of SQL Server

SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Top Known Issues with Microsoft SQL Server 2014 CU3

SQLServerF1

With release of each new version and new service packs of SQL Server, new issues or bugs will surface and Microsoft will work on fixing high priority bugs. Below are some of the top known issues which were identified and fixed in Microsoft SQL Server 2014 CU3.

– In Microsoft SQL Server 2014, if you are running a query which involves multiple table joins, and if any of the tables referenced in the query has Clustered Columnstore Index, then you may notice significant poor performance with the query because of missing Clustered Column Store Index segment elimination.

o Micorsoft has identified this issue and has released a fix for the same.
http://support.microsoft.com/kb/2984923/en-us
o Either Apply this fix if this is business critical issue, else apply latest available Cumulative Update or Service Pack.

– If you have defined a temporary variable and populated huge number of row into it, and then if you are using this temp variable in queries where you join this with other tables, then you may notice slow performance problems with the query.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– You may receive above error when you try to open memory-optimized table template from SQL Server Management Studio (SSMS) from a remote client machine and using SQL Server Authentication to connect to SSMS.
Failed to connect to Server ""
The target principal Name is incorrect. Cannot generate SSPI context

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– If a database which is part of AlwaysOn Availability Group and if this database consists of an In-Memory Table in new SQL Server and while you try to insert or update which affects multiple rows in one transaction on this table may lead to an “non-yielding scheduler” error. This will then result in the failover of the AlwaysOn Availability Group because the dump file generation takes a long time to complete.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– Access Violation exception and memory stack dump will be generated in SQL Server 2014, when you create a user-defined table and add some extended properties at the column level and then tried to query sys.extended_properties system table.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– You will receive below error when you have a FileTable or FILESTREAM in SQL Server 2014, and you have defined the MAXSIZE of FILESTREAM data to UNLIMITED and when the used size of FileTable when the FILESTREAM container size has reached 32 terabytes (TB) and then you tried to insert more data.
error 1105 Could not allocate space for object ‘‘ in database ‘’ because the ‘‘ filegroup is full.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– Log Reader Agent crashes by causing an access violation exception and memory stack dump during the initialization of the log reader agent in Transactional Replication.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– In SQL Server 2014, if you have a table that is partitioned and also has a clustered columnstore index and when you try to create an indexed view on this table, you will receive the error message: Internal Query Processor Error: The query processor could not obtain access to a required interface.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– By default “Auto Create Statistics” databases option is enabled on all databases. In SQL Server 2014, if you try to create a natively compiled stored procedure and if internally triggers the automatic creation of missing statistics, then a self-deadlock can occur which can significantly increases the compilation time.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– In SQL Server 2014, if you created a memory-optimized table variables outside of the natively compiled stored procedures, then a LCK_M_SCH_M (schema modify lock) is taken on the corresponding memory-optimized table, during destructing of the table variable, this any concurrent declaration of memory-optimized table variables outside of natively compiled stored procedures are blocked.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

– In SQL Server 2014, If “UPDATE STATISTICS” is performed on a computed column that contains the “ISNULL” function then it will cause an access violation exception and generated a memory stack dump.

o Microsoft has identified this issue and has released a fix for the same in CU3 of SQL Server 2014.
http://support2.microsoft.com/kb/2952444

This is applicable on below versions of SQL Server

SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Top New Microsoft SQL Server 2014 Features

Microsoft released new SQL Server 2014 version on 1st of April 2014. SQL Server 2014 was released with on two years of release of SQL Server 2012. Most famous and talked feature of SQL Server 2014 is In-Memory OLTP (In-Memory Optimization). In SQL Server 2012, most famous feature was AlwaysON Availability Groups. There are many other new features introduced in SQL Server 2014 along with the In-Memory OLTP.

Below is list of some of the top features released with SQL Server 2014.

In-Memory OLTP (In-Memory Optimization) – As the name says, it is designed to greatly improve the performance on Online Transactional Processing (OLTP) applications, by keeping all the data in memory thus reducing the physical IO, which was the cause of slowness for most of the applications. In-Memory OLTP engine has been developed and has been integrated with the SQL Server Database Engine as one component, rather than having to install as a separate component.

In order to use SQL Server 2014 In-Memory OLTP, we need to identify highly transactional tables and convert them as memory optimized tables. Memory-optimized-tables supports all the ACID properties which are Atomicity, Consistency, Isolation, and Durability. In-Memory Tables can be accessed using regular Transact-SQL in the similar way as we access the disk-based tables. In one query we can perform DML operations on both In-memory optimized tables and disk-based tables as well. Regular Stored procedures can access In-Memory tables and regular tables. Natively compiled stored procedures introduced on SQL Server 2014 is used specifically to work with In-Memory optimized tables only, which provide additional performance benefits. The In-Memory OLTP engine is designed to handle extremely high session concurrency for OLTP type of transactions driven system. It achieves this by using latch-free data structures and optimistic, multi-version concurrency control, which results in predictable, very fast, low latency, high throughput for OLTP transactions. There are other factors too which affect the performance gains. Microsoft in their official documentation mentioned that commonly we see 5-to-20 times performance improvements and can be increased further by following recommended In-Memory query optimization techniques. More information can be found in Microsoft official website.

Enhancements to AlwaysOn Feature – SQL Server 2014 contains many new enhancements for AlwaysOn Failover Cluster Instances and AlwaysOn Availability Groups compare to SQL Server 2012.

– Maximum number of secondary replicas has been increased from 4 to 8.
– When secondary replicas are disconnected with primary replica or when Secondary replicas is not part of the Quorum, readable secondary replicas still remain available for read only workloads.
– Cluster Shared Volumes (CSVs) whose support to SQL Server was introduced with SQL Server 2014 can now be used in Failover cluster instances (FCIs) in SQL Server 2014 as cluster disks.
– Existing AlwaysON Availability Group DMV’s are enhanced and new DMV’s and functions are introduced as well for better monitoring and troubleshooting.
– Using “Add Azure Replica”, we can use Azure server as a secondary replica and add Azure databases as the secondary AlwaysON Availability Group databases for Read_Only purposes in hybrid IT environment.

Enhancements to Resource Governor – Previous versions of the Resource Governor allowed us to specify the limits for the amount of CPU and memory for a particular application request. With SQL Server 2014, now we can also control the I/O limits for a particular application request. MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings are provided to control the physical I/O’s for a particular user threads.

Enhancement to Statistics – Statistics is the main resource and used to calculate the cost for a query and to create best execute plan. From SQL Server 2014, CREATE STATISTICS and statistic related T-SQL command now allows statistics to be created per partition by using the INCREMENTAL option.

Buffer Pool Extension – With SQL Server 2014 Buffer Pool Extension we can use solid-state drives (SSD) as Non-Volatile RAM which extends the BufferPool region allowing to pageout small workloads to SSD’s, as I/O operations between RAM and SSD’s are fast enough, the SSD’s will server as a backup memory. This enhancement significantly improves the I/O throughput for Read heavy workloads.

Enhancements to Columnstore Indexes – Allows DML operations as well on ColumnStore indees, which was not allowed before. This was possible as now we can create clustered columnstore index which improves data compression and query performance for data warehousing workloads. Since the clustered columnstore index is updateable, the workload can perform DML operations like insert, update, and delete. Another enhancement include, allowing SHOWPLAN to display the information related to ColumnStore Indexes as well in the query plan.

Enhancements to ONLINE Rebuild Index Operations – Starting SQL Server 2014, we can specify whether SPID or Session which is performing ONLINE rebuild index operation can now run at a lower priority compared to application SPID’s or sessions, thus reducing the blocking of application sessions. We can also configure online rebuild operation to kill itself, if it is blocking for more than certain duration. WAIT_AT_LOW_PRIORITY option allows us to specify how long the rebuild process should wait for the necessary locks and if it does not get the locks by that time, then to terminate rebuild session.

Delayed Durability – Delayed Durabile is also known as lazy commit. By default, SQL Server uses “fully durable transaction commits” which are synchronous and notifies a commit transaction as successful and return control to the client only when the associated log records for the transaction is written to disk. In “Delayed durable transaction”, commits are asynchronous and notifies a commit transaction as successful even before the log records for the transaction are written to disk, thus reducing any latency to wait for the log record to be flushed to the physical disk. Writing the transaction log entries to disk is mandatory for a transaction to be durable, so even in Delayed durable the transactions only become durable later when transaction log of that transaction are flushed to physical disk. This is to be only used for some applications where loss of some transactions is acceptable.

Enhancements to Cardinality Estimation – Cardinality Estimation logic has been redesigned in SQL Server 2014 which improved in generating quality query plans, thus improving query performance.

Enhancements to Backup and Restore – There are few enhancements to Backup and Restore process in SQL Server 2014 which include, adding GUI feature of performing a backup to a URL, thus allowing us to backup to or restore from Windows Azure Blob storage service. Another enhancement is the ability to encrypt the data while creating a backup, resulting in an encrypted backup file.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 
1 2 3 4