SQL Server Evolution from 2000 to 2016

The Evolution of the Microsoft SQL Server

---

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.

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.
- Clusteredr ColumnStore Index : When Microsoft introduced the columnstore index in QL 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.

BI Features

- Tabular Model (SSAS) - Data Quality Service (DQS) :
- 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

BI Features

- SQL Server Integration Service : Improved multiprocessor support and faster lookups.
- 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.

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)

---

SQL Server 2000

Refer the msdn Article for more details Click here

New Features/Enhancement

- Log Shipping
- Replication
- Clustering

BI Features

- Data Transformation Services (DTS)

---

SQL Server Database Mirroring Ques & Ans

Database Mirroring Interview Questions & Answers

Q. What is default port of Database Mirroring Endpoint?
A: 5022

Q. Database Mirroring comes with which edition?
A: SQL Server 2005 SP1. Or SQL Server 2005 RTM with trace flag 1400

Q. How to enable mirroring by Script ?
A: – Specify the partner from the mirror server
ALTER DATABASE [AdventureWorks] SET PARTNER = N‘TCP://MirrorServer.mycompany.com:5022';

– Specify the partner from the principal server
ALTER DATABASE [AdventureWorks] SET PARTNER = N‘TCP://PrincipalServer.mycompany.com:5022';


Q. How to disable mirroring by script?
A: ALTER DATABASE [AdventureWorks] SET PARTNER OFF

Q.How to do manual failover to Mirror when principle is working fine?
A: ALTER DATABASE SET PARTNER FAILOVER

Q. You are getting the below error message while configuring database mirroring? Msg 1416, Level 16, State 31, Line 3. Database is not configured for database mirroring
A: You need to restore the Full backup from principal server using With NoRecovery option and also one transactional log backup from principal server using With NoRecovery option and then start configuring mirroring

Q. Can we configure mirroring between Standard Edition & Enterprise Edition or Vice Versa?
A: Nope its not possible, both principal and mirror should have same edition.
Refer the KB article

Q. How to bring mirror DB online if Principle is down?
A: ALTER DATABASE SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
If you try to do failover like normal situation when principle is online [ALTER DATABASE SET PARTNER FAILOVER] then you will receive below error.
ALTER DATABASE SET PARTNER FAILOVER
Msg 1404, Level 16, State 10, Line 1
The command failed because the database mirror is busy. Reissue the command later.

Q. System Store Procedure to monitor Mirroring?
A: MSDB.SYS.SP_DBMMONITORRESULTS

Q. In Database mirroring, Let suppose Primary DB log file is not getting truncated even after taking log backup. What is the issue?
A: In this scenario it seems there is some communication issue between the Primary & Mirror database, due to following reasons:
- Network issue between Primary & Mirror Servers.
- Endpoints are not able to communicate, or could be port issue.
- Mirroring got paused or got interrupted due to unexpected reboot.


Q. In Database mirroring, Let suppose Primary DB log file is not getting truncated even after taking log backup and Mirror DB is showing as Suspended state. What action will you take to re-sync the mirroring?
A: To fix this issue, will identify cause by checking the following things:
- Check for the Network connectivity.
- Check both DB Server endpoints are working successfully.
- Check for the Mirror DB status and if it is suspended then have to re-configure the mirroring.

Q. In Database mirroring, If we Add new FileGroup and create new file under the FileGroup. Will that change also reflect on Mirror database?
A: Yes it will reflect the changes to mirror database as well. Just have to make sure the file path and drive of the new file we are creating, same path should be available on the mirror DB Server as well.

SQL Server Saving Changes Is Not Permitted

Issue
SQL Server Issue while modifying table through SQL Server Management Studio (SSMS).
When we alter the table definition, table design and try to save through SSMS, we get an error message saying "Saving Changes is not permitted."

Solution
1. Click on Tools Tab>> Options

2. Click on Designers
Un-check option "Prevent saving changes that requires re-creation."

That's it!!

Note: If you disable this option, you are not warned when you save the table that the changes that you made have changed the metadata structure of the table. In this case, data loss may occur when you save the table.

SQL Server : Data Compression

Today's Post is all about Data Compression.
SQL Server’s data compression doesn't automatically compress the data in the database; instead, data compression can only be used for these types of database objects:

- A table stored as a heap
- A table stored as a clustered index
- A non-clustered indexes.
- An indexed view.
- Partitioned tables and indexes.

There are two different types of Data Compressions:
Row‐Level Data Compression
Row-level compression is essentially turning fixed length data types into variable length data types, freeing up empty space. It also has the ability to ignore zero and null values, saving additional space. In turn, more rows can fit into a single data page.

This is the simplest method of data compression is row-level compression, works by:
- Reducing the amount of metadata used to store a row.
- Storing fixed length numeric data types as if they were variable-length data types. For example, if you store the value 1 in a bigint data type, storage will only take 1 byte, not 8 bytes, which the bigint data types normally takes.
- Storing CHAR data types as variable-length data types. For example, if you have a CHAR (100) data type, and only store 10 characters in it, blank characters are not stored, thus reducing the space needed to the store data.
- Not storing NULL or 0 values.
Row-level data compression offers less compression than page-level data compression, but it also incurs less overhead, reducing the amount of CPU resources required to implement it.

Page Level Data Compression
Page Level Compression includes three additional features:
- Row Level Compression.
- Prefix Compression.
- Dictionary Compression.
Its starts with row-level data compression, then other two additional compression features: prefix and dictionary compression. We'll get more detail about these features later in this article. As you can imagine, page-level compression offers better data compression in compare to row-level compression.

Page-level data compression offers greater compression, but at the expense of greater CPU utilization. It works using these techniques:
- It starts out by using row-level data compression to get as many rows as it can on a single page.
- Next, prefix compression is run. Essentially, repeating patterns of data at the beginning of the values of a given column are removed and substituted with an abbreviated reference that is stored in the compression information (CI) structure that immediately follows the page header of a data page.
- And last, dictionary compression is used. Dictionary compression searches for repeated values anywhere on a page and stores them in the CI. One of the major differences between prefix and dictionary compression is that prefix compression is restricted to one column, while dictionary compression works anywhere on a data page.

The amount of compression provided by page-level data compression is highly dependent on the data stored in a table or index. If a lot of the data repeats itself, then compression is more efficient. If the data is more random, then little benefits can be gained using page-level compression.

Advantages
- Reducing the amount of physical disk space required to store data in result less number of reads and writes. - Reduces the amount of Buffer Cache memory needed to store data in the Buffer Cache. This in turn allows more data to be stored in the Buffer Cache, reducing the need for SQL Server to access the disk to get data, as the data is now more likely to be in memory than disk, further reducing disk I/O.

Disadvantages
- Using compression uses up additional CPU cycles. If your server has plenty to spare, then you have no problem. But if your server is already experiencing a CPU bottleneck, then perhaps compression is better left turned off.

Steps to enable Compression on a table or index refer MSDN Article

Here are the T-SQL syntax with example of compression:
To enable compression on a table:

USE AdventureWorks2012;
GO
EXEC sp_estimate_data_compression_savings 'Production', 'TransactionHistory', NULL, NULL, 'ROW' ;

ALTER TABLE Production.TransactionHistory REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW); 
GO

To enable compression on an index:

USE AdventureWorks2012; 
GO
SELECT name, index_id
FROM sys.indexes
WHERE OBJECT_NAME (object_id) = N'TransactionHistory';

EXEC sp_estimate_data_compression_savings 
    @schema_name = 'Production', 
    @object_name = 'TransactionHistory',
    @index_id = 2, 
    @partition_number = NULL, 
    @data_compression = 'PAGE' ; 

ALTER INDEX IX_TransactionHistory_ProductID ON Production.TransactionHistory REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = PAGE);
GO

---

Customize Script to get the compression details of all the tables in a database with estimated saving size:

CREATE PROCEDURE usp_CompressTbl_Report (@compress_type char(4))
AS 
SET NOCOUNT ON
BEGIN
DECLARE @schema_name sysname, @table_name sysname
CREATE TABLE #CompressTbl_Report 
(ObjName sysname,
schemaName sysname,
indx_ID int,
partit_number int,
size_with_current_compression_setting bigint,
size_with_requested_compression_setting bigint,
sample_size_with_current_compression_setting bigint,
sample_size_with_requested_compression_setting bigint)
DECLARE c_sch_tb_crs cursor for 
SELECT TABLE_SCHEMA,TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE LIKE 'BASE%' 
AND TABLE_CATALOG = upper(db_name())
OPEN c_sch_tb_crs
FETCH NEXT FROM c_sch_tb_crs INTO @schema_name, @table_name
WHILE @@Fetch_Status = 0 
BEGIN
INSERT INTO #CompressTbl_Report
EXEC sp_estimate_data_compression_savings
@schema_name = @schema_name,
@object_name = @table_name,
@index_id = NULL,
@partition_number = NULL,
@data_compression = @compress_type
FETCH NEXT FROM c_sch_tb_crs INTO @schema_name, @table_name
END
CLOSE c_sch_tb_crs 
DEALLOCATE c_sch_tb_crs
SELECT schemaName AS [schema_name]
, ObjName AS [table_name]
, avg(size_with_current_compression_setting) as avg_size_with_current_compression_setting
, avg(size_with_requested_compression_setting) as avg_size_with_requested_compression_setting
, avg(size_with_current_compression_setting - size_with_requested_compression_setting) AS avg_size_saving
FROM #CompressTbl_Report
GROUP BY schemaName,ObjName
ORDER BY schemaName ASC, avg_size_saving DESC 
DROP TABLE #CompressTbl_Report
END
SET NOCOUNT OFF
GO

Here is a sample execution and result set:

USE AdventureWorks2012 
GO
EXEC usp_CompressTbl_Report @compress_type = 'ROW'
GO

SQL Server : Backup Compression

Today's will talk about Compress Backup feature? how to take compress backup and enable backup compression feature in sql server.
Backup compression is a powerful feature offered by SQL Server (versions 2008 and above) that allows take the backup in compressed file which reduce the amount of storage space used by the backup files while at the same time increasing the speed of recovery which goes hand in hand with lower CPU usage when restoring the database from your backup.

Recommendations
When you are creating an individual backup, configuring a log shipping configuration, or creating a maintenance plan, you can override the server-level default.
Also, backup compression is supported for both disk backup devices and also for tape backup devices.

Two ways to configure
1.) Through SSMS
1.a. In the Object Explorer window, right-click the database server for which you want compressed backups to be created and select Properties.
1.b. Click the Database Settings page in the left side menu
1.c. Under Backup and restore, the Compress backup tickbox shows the current setting for this option.

- If the Checkbox is unchecked, new backups are created uncompressed by default.
- If the Checkbox is checked, new backups are compressed by default.

Note: You need Syadmin or Serveradmin fixed server role, Then only can change the default setting by checking the check box.

2.) Using TSQL
2.a. Start by connecting to the Database Engine and pressing the New Query button to create the script file.
2.b. To configure the backup compression default option at server-level so that backups are created compressed by default, run the following query:

EXEC sp_configure 'backup compression default', 1;
        RECONFIGURE WITH OVERRIDE;
        GO

These settings take effect immediately so no server restart is required.
If you would just like to see the status of backup compression without using SSMS interface, you can run the following T-SQL query:

SELECT value
FROM sys.configurations
WHERE NAME = 'backup compression default';

There is also the option of overriding the server backup-compression default when creating a backup with T-SQL by using either WITH NO_COMPRESSION or WITH COMPRESSION option in your BACKUP statement.
An example statement of this can be seen below:

BACKUP DATABASE TestDB TO DISK = 'D:\MSSQL\Backup\TestDB.bak'
WITH FORMAT, COMPRESSION;