The Evolution of the Microsoft SQL Server with New Features..
SQL Server 2016
New
Features/Enhancement
– Stretch Database : The idea behind this feature is certainly interesting.
The upcoming stretch database feature will allow you to dynamically stretch
your on-premise database to Azure. This would enable your frequently accessed
or hot data to stay on-premise and your infrequently accessed cold data to be
moved to the cloud.
– Always Encrypted : Always Encrypted is designed to protect data at rest or in motion. With Always Encrypted, SQL Server can perform operations on encrypted data and the encryption key can reside with the application. Encryption and decryption of data happens transparently inside the application.
– Enhancements to AlwaysOn : SQL Server 2016 will also continue to advance high availability and disaster recovery with several enhancements to AlwaysOn. The upcoming SQL Server 2016 release will enhance AlwaysOn with the ability to have up to three synchronous replicas. Additionally, it will include DTC (Distributed Transaction Coordinator) support as well as support for round-robin load balancing of the secondary replicas. There will also be support for automatic failover based on database health.
– Enhanced In-Memory OLTP : First introduced with SQL Server 2014, In-Memory OLTP will continue to mature in SQL Server 2016. Microsoft will enhance In-Memory OLTP by extending the functionality to more applications while also enhancing concurrency. This means they will be expanding the T-SQL surface area, increasing the total amount of memory supported into the terabyte range as well as supporting a greater number of parallel CPUs.
– Always Encrypted : Always Encrypted is designed to protect data at rest or in motion. With Always Encrypted, SQL Server can perform operations on encrypted data and the encryption key can reside with the application. Encryption and decryption of data happens transparently inside the application.
– Enhancements to AlwaysOn : SQL Server 2016 will also continue to advance high availability and disaster recovery with several enhancements to AlwaysOn. The upcoming SQL Server 2016 release will enhance AlwaysOn with the ability to have up to three synchronous replicas. Additionally, it will include DTC (Distributed Transaction Coordinator) support as well as support for round-robin load balancing of the secondary replicas. There will also be support for automatic failover based on database health.
– Enhanced In-Memory OLTP : First introduced with SQL Server 2014, In-Memory OLTP will continue to mature in SQL Server 2016. Microsoft will enhance In-Memory OLTP by extending the functionality to more applications while also enhancing concurrency. This means they will be expanding the T-SQL surface area, increasing the total amount of memory supported into the terabyte range as well as supporting a greater number of parallel CPUs.
BI Features
– Revamped SQL Server Data Tools :
Another welcome change in SQL Server 2016 is the re-consolidation of SQL Server
Data Tools (SSDT). As Microsoft worked to supplant the popular and useful
Business Development Studio (BIDS) with SQL Server Data Tools they wound up
confusing almost everyone by creating not one but two versions of SQL Server
Data Tools both of which needed to be downloaded separately from installing SQL
Server itself. With the SQL Server 2016 release Microsoft has indicated that
they intend to re-consolidation SQL Server Data Tools.
SQL Server 2014
New
Features/Enhancement
– In-Memory OLTP Engine : SQL
Server 2014 enables memory optimization of selected tables and stored
procedures. The In-Memory OLTP engine is designed for high concurrency and uses
a new optimistic concurrency control mechanism to eliminate locking delays.
Microsoft states that customers can expect performance to be up to 20 times
better than with SQL Server 2012 when using this new feature.
– Clustered ColumnStore Index : When Microsoft introduced the columnstore index in SQL Server 2012, it 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. SQL Server 2014 eliminates this restriction with the new updateable Columnstore Index. The SQL Server 2014 Columnstore Index must use all the columns in the table and can’t be combined with other indexes.
– Backup to Windows Azure : SQL Server 2014’s native backup supports Windows Azure integration. Although I’m not entirely convinced that I would want to depend on an Internet connection to restore my backups, on-premises SQL Server 2014 and Windows Azure virtual machine (VM) instances support backing up to Windows Azure storage. The Windows Azure backup integration is also fully built into SQL Server Management Studio (SSMS).
– AlwaysOn Enhancements : 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.
– Buffer Pool Extension : SQL Server 2014 provides a new solid state disk (SSD) integration capability that lets you use SSDs to expand the SQL Server 2014 Buffer Pool as nonvolatile RAM (NvRAM). With the new Buffer Pool Extensions feature, you can use SSD drives to expand the buffer pool in systems that have maxed out their memory. Buffer Pool Extensions can provide performance gains for read-heavy OLTP workloads.
– Backup Encryption : One welcome addition to SQL Server 2014 is the ability to encrypt database backups for at-rest data protection. SQL Server 2014 supports several encryption algorithms, including Advanced Encryption Standard (AES) 128, AES 192, AES 256, and Triple DES. You must use a certificate or an asymmetric key to perform encryption for SQL Server 2014 backups.
– Clustered ColumnStore Index : When Microsoft introduced the columnstore index in SQL Server 2012, it 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. SQL Server 2014 eliminates this restriction with the new updateable Columnstore Index. The SQL Server 2014 Columnstore Index must use all the columns in the table and can’t be combined with other indexes.
– Backup to Windows Azure : SQL Server 2014’s native backup supports Windows Azure integration. Although I’m not entirely convinced that I would want to depend on an Internet connection to restore my backups, on-premises SQL Server 2014 and Windows Azure virtual machine (VM) instances support backing up to Windows Azure storage. The Windows Azure backup integration is also fully built into SQL Server Management Studio (SSMS).
– AlwaysOn Enhancements : 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.
– Buffer Pool Extension : SQL Server 2014 provides a new solid state disk (SSD) integration capability that lets you use SSDs to expand the SQL Server 2014 Buffer Pool as nonvolatile RAM (NvRAM). With the new Buffer Pool Extensions feature, you can use SSD drives to expand the buffer pool in systems that have maxed out their memory. Buffer Pool Extensions can provide performance gains for read-heavy OLTP workloads.
– Backup Encryption : One welcome addition to SQL Server 2014 is the ability to encrypt database backups for at-rest data protection. SQL Server 2014 supports several encryption algorithms, including Advanced Encryption Standard (AES) 128, AES 192, AES 256, and Triple DES. You must use a certificate or an asymmetric key to perform encryption for SQL Server 2014 backups.
BI Features
– Power View for Multidimensional
Models : Power View used to be limited to tabular data. However, with
SQL Server 2014, Power View can now be used with multidimensional models (OLAP
cubes) and can create a variety of data visualizations including tables,
matrices, bubble charts, and geographical maps. Power View multidimensional
models also support queries using Data Analysis Expressions (DAX). – SQL
Server Data Tools : The new SQL Server Data Tools for BI (SSDT-BI) is
used to create SQL Server Analysis Services (SSAS) models, SSRS reports, and
SSIS packages. The new SSDT-BI supports SSAS and SSRS for SQL Server 2014 and
earlier, but SSIS projects are limited to SQL Server 2014.
SQL Server 2012
New
Features/Enhancement
– AlwaysOn : SQL Server
AlwaysOn provides a high-availability and Disaster-recovery solution for SQL
Server 2012. Click here for more details
– Non-Clustered ColumnStore Index : non-Updateable, click here to get more details
– Contained database : This is a great feature for people who have to go through pain of SQL Server database migration again and again. One of the biggest pains in migrating databases is user accounts. SQL Server user resides either in windows ADS or at SQL Server level as SQL Server users. So when we migrate SQL Server database from one server to other server these users have to be recreated again.
– User Defined Server roles : In SQL Server 2008 R2 we had the ability to create roles at database level. So you create customized roles at the database level and then assign them to users. But at the server level or instance level we did not have options of creating server roles. So if you right click on the “Server roles” you will not find any options for adding new server roles.
– Non-Clustered ColumnStore Index : non-Updateable, click here to get more details
– Contained database : This is a great feature for people who have to go through pain of SQL Server database migration again and again. One of the biggest pains in migrating databases is user accounts. SQL Server user resides either in windows ADS or at SQL Server level as SQL Server users. So when we migrate SQL Server database from one server to other server these users have to be recreated again.
– User Defined Server roles : In SQL Server 2008 R2 we had the ability to create roles at database level. So you create customized roles at the database level and then assign them to users. But at the server level or instance level we did not have options of creating server roles. So if you right click on the “Server roles” you will not find any options for adding new server roles.
BI Features
– Tabular Model (SSAS) – Data
Quality Service (DQS) :
– Power View :
– Cloud :
– Power View :
– Cloud :
SQL Server 2008/R2
New
Features/Enhancement
– Master Data Services :
Master Data Services might be the most underrated feature in SQL Server 2008
R2. It provides a platform that lets you create a master definition for all the
disparate data sources in your organization. Almost all large businesses have a
variety of databases that are used by different applications and business
units. These databases have different schema and different data meanings for
what’s often the same data.
– Multiserver Management : SQL Server Utility Control Point
– Backup Encryption : Executed at backup time to prevent tampering.
– Data Compression : Fact Table size reduction and improved performance.
– File Stream : New data type VarBinary(Max) FileStream for managing binary data.
– Full Text Search : Native Indexes, thesaurus as metadata, and backup ability.
– Change Data Capture : For requirements to save or monitor historical information on changed data. Using SQL 2008 we can implement a non-invasive detection of changed records.
– Resource Governor : Very cool. Throttle the resources of users based on Memory or Processor
– Multiserver Management : SQL Server Utility Control Point
– Backup Encryption : Executed at backup time to prevent tampering.
– Data Compression : Fact Table size reduction and improved performance.
– File Stream : New data type VarBinary(Max) FileStream for managing binary data.
– Full Text Search : Native Indexes, thesaurus as metadata, and backup ability.
– Change Data Capture : For requirements to save or monitor historical information on changed data. Using SQL 2008 we can implement a non-invasive detection of changed records.
– Resource Governor : Very cool. Throttle the resources of users based on Memory or Processor
BI Features
– SQL Server Integration Service :
Improved multiprocessor support and faster lookups.
– PowerPoint :
– Sharepoint Integration :
– PowerPoint :
– Sharepoint Integration :
SQL Server 2005
New
Features/Enhancement
– T-SQL (Transaction SQL) enhancements :
new features including error handling via the TRY and CATCH paradigm, Common Table
Expressions (CTEs), which return a record set in a statement, and the ability
to shift columns to rows and vice versa with the PIVOT and UNPIVOT commands.
– Service Broker : The Service Broker handles messaging between a sender and receiver in a loosely coupled manner. A message is sent, processed and responded to, completing the transaction.
– Data encryption : SQL Server 2000 had no documented or publicly supported functions to encrypt data in a table natively. Organizations had to rely on third-party products to address this need. SQL Server 2005 has native capabilities to support encryption of data stored in user-defined databases.
– SMTP mail : Sending mail directly from SQL Server 2000 is possible, but challenging. With SQL Server 2005, Microsoft incorporates SMTP mail to improve the native mail capabilities.
– Multiple Active Result Sets (MARS) : MARS allow a persistent database connection from a single client to have more than one active request per connection. This should be a major performance improvement, allowing developers to give users new capabilities when working with SQL Server. For example, it allows multiple searches, or a search and data entry. The bottom line is that one client connection can have multiple active processes simultaneously.
– Dedicated administrator connection (DAC) : If all else fails, stop the SQL Server service or push the power button. That mentality is finished with the dedicated administrator connection. This functionality will allow a DBA to make a single diagnostic connection to SQL Server even if the server is having an issue.
– Database Mirroring : Database mirroring is an extension of the native high-availability capabilities.
– Service Broker : The Service Broker handles messaging between a sender and receiver in a loosely coupled manner. A message is sent, processed and responded to, completing the transaction.
– Data encryption : SQL Server 2000 had no documented or publicly supported functions to encrypt data in a table natively. Organizations had to rely on third-party products to address this need. SQL Server 2005 has native capabilities to support encryption of data stored in user-defined databases.
– SMTP mail : Sending mail directly from SQL Server 2000 is possible, but challenging. With SQL Server 2005, Microsoft incorporates SMTP mail to improve the native mail capabilities.
– Multiple Active Result Sets (MARS) : MARS allow a persistent database connection from a single client to have more than one active request per connection. This should be a major performance improvement, allowing developers to give users new capabilities when working with SQL Server. For example, it allows multiple searches, or a search and data entry. The bottom line is that one client connection can have multiple active processes simultaneously.
– Dedicated administrator connection (DAC) : If all else fails, stop the SQL Server service or push the power button. That mentality is finished with the dedicated administrator connection. This functionality will allow a DBA to make a single diagnostic connection to SQL Server even if the server is having an issue.
– Database Mirroring : Database mirroring is an extension of the native high-availability capabilities.
BI Features
– SQL Server Integration Services
(SSIS) : SSIS has replaced DTS (Data Transformation Services) as the
primary ETL (Extraction, Transformation and Loading) tool and ships with SQL
Server free of charge.
– Analysis Services (SSAS) and Reporting Services (SSRS)
– Analysis Services (SSAS) and Reporting Services (SSRS)
SQL Server 2000
New
Features/Enhancement
– Log Shipping
– Replication
– Clustering
– Replication
– Clustering
BI Features
– Data Transformation Services (DTS)
It's so nice article thank you for sharing a valuable content. SQL server dba Online Training
ReplyDelete