The Evolution of the Microsoft SQL Server with New Features..

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.

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.

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)

Regular usage scripts sql server DBA

1. On which drive the  database files(mdf & ldf) are located?

use master
select *from sysaltfiles where filename like 'd%'

                      (OR)
select *from master..sysaltfiles where filename like 'd%'

2.How to shrinklog file if it is full?

backup log dbname with no_log
dbcc shrinkfile(2)

3. how to see open transations in a database?

  dbcc opentran


4. HOW TO see if perticlular process is blocked?
Use master
select * from sysprocessess where blocked>0 and spid=57

5. why buffer cache hit ratio <90?

 if the bottlenecks are there
 then the number of pages read in to the cache will vary
 and the ratio of finding the data by the server in the cache will decrease

6 how to find attribute of perticula file?

  Execute spFileDetails 'c:\autoexec.bat'

7. how to see the status of database?

  dbcc showfilestats

8.How to see the  Cpu usage?(if buffer cache hit raio<90)

 select *from sys.sysprocesses order by cpu asc

9.How to find out the info of a specific table on a database?

 select * from sysobjects where name like '%table_name%'

10. What is Undo File? Why it is required?

 Undo file is needed in standby state because while restoring the log backup, uncommited transactions will be recoreded to the undo file and only commited transactions will be written to disk there by making users to read the database. When you restore next tlog backup SQL server will fetch the uncommited transactions from undo file and check with the new tlog backup whether the same is commited or not. If its commited the transactions will be written to disk else it will be stored in undo file until it gets commited or rolledback.

11.How to get the data the from linked server?

 Select * from LinkedServer.DBName.SchemaName.TableName

12.How to find the service pack version?

SELECT SERVERPROPERTY('PRODUCTLEVEL')

13.How to see the collation setting for server level?

SP_HELPSORT

14.How to see log usage info in a database?

select * from  sys.databases where name like '%log_reuse_wait_desc%'

15.How to see the connection info by using DMV'S?

select * from  sys.dm_exec_sessions

16.How to see the transaction isolation level?

DBCC USEROPTIONS

17.SQL Server 2000 database compatible level to SQL Server 2005

EXEC sp_dbcmptlevel AdventureWorks, 90;

18.How to see the memory ocupation of eache query running under Query Analyzer

select * from sys.dm_exec_query_memory_grants

19.How to clear the data from buffer cashe

DBCC DROPCLEANBUFFERS

20.How to see how much space tempdb is allocated for perticulas task

Select * from sys.dm_db_session_space_usage 
                   
                  OR
sys.dm_db_task_space_usage 

21.The following DMV query can be used to get useful information about the index usage for all objects in all databases

Select * from from sys.dm_db_index_usage_stats 
order by object_id, index_id

22.The following query to list all the schedulers and look at the number of runnable tasks.

 Select * from from 
    sys.dm_os_schedulers
    where 
    scheduler_id < 255

23.Which Query is taking more cpu time 

select * from sys.dm_exec_query_stats 

24.How to find whether any active requests are running in parallel for a given session by using the following query.

select * from 
    sys.dm_exec_requests 

25. if sql server installation failed in sql2000 whrere can i see tha failed info?

 sqlstp.log

26. How to see the lock info?

 select * from sys.dm_tran_locks

27. How to open cluadmin from windows 2008 server?

   cluadmin.msc

Contained Databases in SQL Server 2012

Contained Databases have been an oft requested feature which has finally arrived in SQL Server 2012.
In prior SQL Server versions, database were not 100% portable. In case you need to move a database from one server to another using backup and restore, the data in the
database will of course be preserved. However, to work with the database, you need several more objects such
as logins etc. However, these objects are outside the database and so would not be included in any backup.

With Contained databases, there is only a small functional
dependency on SQL Server Instance or in other words, the database is
self-contained and all the objects will reside in the database itself.

 There are three Contained types.

Contained Type	Description
NONE	Default contained type is what we are used to have in SQL Server 2008 R2 and prior versions.
PARTIAL	Partially Contained Databases provides some isolation from the instance of SQL Server but not full containment.
FULL	(This is currently not available in SQL Server 2012 but in FULL mode, users will not be not permitted to cross the database)

Configuring Contained Databases

Firstly, you will need to enable the Contained Database feature at the
aerver level either from the user interface or from a script.

sp_configure 'show advanced options',1

 GO

 RECONFIGURE WITH OVERRIDE

 GO

 sp_configure 'contained database authentication', 1

 GO

 RECONFIGURE WITH OVERRIDE

 GO

Alternatively you can use the SSMS user interface:

Right click the server, select Properties > Advanced and set Enabled Contained Databases to True.

Next you will need to configure Contained Database at the database level, to do this from using code:

CREATE DATABASE [ContainedDatabase]

 CONTAINMENT = PARTIAL

 ON PRIMARY

 ( NAME = N'ContainedDatabase', FILENAME = N'C:\myPath\ContainedDatabase.mdf')

 LOG ON

 ( NAME = N'ContainedDatabase_log', FILENAME = N'C:\myPath\ContainedDatabase_log.ldf')

 GO

As you can see in the above script, the CREATE DATABASE syntax
is tweaked to enable the contained database feature.

You can use the SSMS user interface
to enable contained database at the database level:

In SQL Server 2012, you will see only two options i.e. None
and Partial however we may see the Full contained type in future releases
of SQL Server 2012.

The new Contained Databases feature will be reflected in the sys.databases but not in sys.sysdatabases:

SELECT Name,containment

FROM sys.databases

As you can see in the above image, the new column is included to
identify whether the database is contained database or not.

You can change the containment to PARTIAL or to NONE by
executing following script.

USE [master]

GO

ALTER DATABASE [ContainedDatabase]

SET CONTAINMENT = PARTIAL WITH NO_WAIT

GO

Let us see how user login works with Contained Databases.

If you are creating a user in any of SQL Server 2012 prior
versions, this is the syntax you need to use.

USE [master]

GO

CREATE LOGIN [None_Contained_Login]

WITH PASSWORD=N'Qwerty123',

DEFAULT_DATABASE=[master]

GO

USE [AdventureWorks2008R2]

GO

CREATE USER [None_Contained_Login]

FOR LOGIN [None_Contained_Login]

GO

This script will create a login with s password and then
create a user by adding the previously created login.

In Contained databases this is how you create a user.

USE [ContainedDatabase]

GO

CREATE USER [Contained_User]

WITH PASSWORD=N'Qwerty123'

GO

Note the two two differences:

·         Unlike in the standard (pre SQL Server 2012) user creation, you have only one create
statement. In this, you will have only a CREATE USER statement and no CREATE LOGIN
statement.

·         In SQL server 2012 you assign the password to the USER and not to the LOGIN unlike in the standard
user creation.

The above query cannot be run on ‘None’ contained or standard
databases. If you attempt to run this statement you will trigger the following error.

Msg 33233, Level 16, State 1, Line 1

You can only create a user with a password in a contained database.

However, you can create a standard user in contained
databases since you have used PARTIAL contained database type (Note that in future this
will fail in the FULL contained database type).

For UI lovers, right click the users under the Security node in
the Contained database and select New User…  where you will be taken to
the following screen.

In the above screen, you will see that the user type is a SQL user
with password which is what contained database type requires.

Difference Between Snapshot, Transactional and Merge Replication in SQL Server

In SQL Server, replication is a set of applications that helps users in copying and distributing data and database objects among different databases. It also helps in maintaining synchronization between databases in order maintain consistency. However there are types of replication possible in SQL Server such as snapshot, transactional, merge, etc. Thus, in order to have a better understanding and selecting right replication technique, we have differentiated between a snapshot, transactional and merge replication in detail here.

●     Snapshot Replication

It is pretty much clear from the name itself that it take snapshots of the published database. After that, it exports it to a subscriber database. Whenever it drop the table and recreate it again, the primary aim of the snapshot replication is to properly overwrites the data or transactions in the subscriber database

When is it Beneficial?

A user can use snapshot replication in its best way when the subscriber requires data on certain interval rather than on frequent basis or when the frequency of data is low. It means snapshot replication is advantageous in a case where the list of the item is to be updated that change only on certain interval or periodically for example, at the end of business day. Apart from all this, snapshot replication is a little bit slow when compared with transactional replication. It moves multiple records suppose millions of records, from the publisher (one end) to the subscriber (another end).

●     Transactional Replication

Transactional replication works slightly different from snapshot replication. It replicates each and every transaction for the table/article being published from a publisher to the subscriber. In the beginning, it also takes a snapshot of a publisher database and overwrites it to subscriber database to synchronize the data. As everybody knows the main aim of replication is to maintain synchronization of data between publisher and subscriber database.

When is it Beneficial?

A user can use Transactional Replication where real time data is required for example bank-specific transaction and online trading in order to keep a backup of live data of each credit or debit transaction. A log reader agent first reads transactions from the transaction log and after that write it to the distribution database and at last update it to the subscriber database. Log reader agent is must in each database if transactional replication is used. Moreover, they transfer transactions from the publisher to distributor. The main reason it keeps the track of data changed at publisher level is because transactional replication is applied only to article/ table that has a primary key.

●     Merge Replication

As snapshot replication, it is also clear from its name that merge replication joins the publisher and subscriber database. It is the complex replication if compared to other and helps users to maintain data consistent among multiple ends. The best part about merge replication is that it work in an integrated way by taking publisher and subscriber together. The main aim of merge agent is to trace the each and every change that has occurred at both publisher and subscriber end. After that, they have to send that modified transactions to distributor database for further propagation. The Merge Agent execute either at subscription end for pull subscription or distributor end for a push subscription.

When is it Beneficial?

After having a complete understanding of the Merge replication, it is very much clear that it works really well in the retail markets such as Lifestyle, Pantaloons, BigBazar and much more. At these places, it is really helpful in synchronizing records among multiple stores as stock increased or decreased

Difference B/W Snapshot, Transactional And Merge Replication in a Tabular Form

S.no	Snapshot Replication	Transactional Replication	Merge Replication
1	It is unidirectional	It is unidirectional	It is bidirectional
2	It does not require table structure	It requires table that must have primary keys	It requires table that has rowguid to column
3	High latency is acceptable	Application does not accept high latency at all	Application accept both high or low latency
4	It is used in server-to-server environment	It is used in server-to-server environment	It is used in server-to-client environment

Conclusion

Replication is very important process from SQL Server database point of view. However, it assists SQL users in maintaining consistency between database and allows them to distribute database object from one database to another. As there are various replication techniques available so, it becomes difficult for a user to select one of them. Thus, after understanding the requirement of the user, we have discussed the difference between snapshot, transactional and merge replication. After clearly understanding the difference between the three, a user can choose among three accordingly.