Windows Family vs Windows Server Family

We've been using Microsoft Windows since our childhood and we know there are several kinds of editions and versions in the Windows family.

The operating systems of Windows family are classified into two types,

• Client Operating Systems.
• Network Operating Systems.

Client Operating Systems

The following operating systems come under Client Operating Systems,

• Disk Operating System (DOS)
• Windows 95
• Windows 98
• Windows 2000
• Windows XP (includes XP1, XP2, XP3)
• Windows Vista
• Windows 7 (includes Home Basic, Home Premium, Professional, Ultimate etc.)
• Windows 8 (includes Windows 8.1)

These are designed for delivering standard consumer experience. They can be installed on a standalone computer as well as in a network infrastructure. They can work only as clients and cannot offer any services to other clients if they are installed in a network infrastructure. They are referred as lightweight operating systems and can run even with a minimal hardware configuration. Their license costs are less when compared to that of Network Operating Systems. They are designed with more graphics for the end users.

Network Operating Systems

The following Windows operating systems come under Network Operating Systems,

• Windows Server 2000
• Windows Server 2003
• Windows Server 2003 R2
• Windows Server 2008
• Windows Server 2008 R2
• Windows Server 2012
• Windows Server 2012 R2

These are designed only to be installed in a network infrastructure. Once installed in a network they can run and provide services to the clients. The difference between Network and Client operating systems are, Active Directory Services can be installed on network operating systems. The Active Directory Services authenticate all the clients in a Windows Domain Network and provide services to them. Through Active Directory, they define security policies and other authorities to all the clients present in that domain. These operating systems need high end hardware configuration like more number of processors, high amount of physical memory. Unlike Client Operating Systems they have minimal graphics in their user interface. These operating systems concentrate on background process mostly to ensure better services to the clients. Their license costs are more than that of client operating systems. These are used in professional production environments.

Delete Connection History in SSMS 2012

Connection History in SSMS 2012 is stored as a .bin file in the following location,

%appdata%\Microsoft\SQL Server Management Studio\11.0

Copy and paste the above path in RUN dialog box and open it. There you can find a file with name SqlStudio and of format .bin. Just delete that file and then open SSMS. Then you will find no connection history.

Case Sensitive Search in SQL Server

We all know that SQL is a case insensitive language. Yes, it is true. But we think that even in searching the data in database is also case insensitive. But that's not true. There lies a feature called COLLATION which contradicts our assumption.

Collation comes into action when we use ORDER BY clause. There are several collations which define language and sorting according to the alphabet of that language. The default collation we see in the English version of SQL Server on the English version of Microsoft Windows is Latin collation. As English is derived from Latin, the sorting of the English records in the database is performed according to the English alphabet. This Latin collation of two types, case sensitive and case insensitive. The default collation taken by SQL Server during its installation is Case Insensitive Latin collation. If case sensitive Latin collation is selected then that doesn't allow case insensitive searching of the records.

Let's see it practically...

USE AdventureWorks2012

GO

CREATE TABLE SENSITIVE_SEARCH (NAME VARCHAR(100))

GO

INSERT INTO SENSITIVE_SEARCH VALUES ('YASHWANTH'),('yashwanth'),('Yashwanth'),('yasHwanth')

GO

SELECT * FROM SENSITIVE_SEARCH

Now, I've created a table and inserted the same name in different styles.

Now let's do a search with a predicate,

SELECT * FROM SENSITIVE_SEARCH WHERE NAME = 'YASHWANTH'

See the result. It's same as above. Though you change the style of the predicate column value the result lies same.

To achieve case sensitive search, you need to change the collation. Collation can be changed at instance level, database level and column level. In this case, only for this table we need to achieve case sensitive search. So, changing the column's collation is enough.

Before changing the collation, let's know how to know collation at different levels...

1) To know the collation of the server through Object Explorer, right click on the instance name and select Properties.

Through T-SQL, collation can be known as,

SELECT SERVERPROPERTY('collation')

2) To know the collation of the database through Object Explorer, right click on the database and select Properties. Under Maintenance section, collation appears.

Through T-SQL, it can be known as,

SELECT DATABASEPROPERTYEX('AdventureWorks2012','collation')

or connect to the database and run this command

SELECT collation_name FROM sys.databases WHERE name = 'AdventureWorks2012'

3) To know the table collation through Object Explorer, right click on the Table and select Properties. Under Extended Properties, collation appears.

4) Collation of a column can be known through Object Explorer by right clicking on the column and selecting Properties.

Through T-SQL, it can be known as,

SELECT collation_name FROM sys.columns WHERE object_id = OBJECT_ID('SENSITIVE_SEARCH') AND name = 'NAME'

Now we need to change the collation of the column NAME in the table SENSITIVE_SEARCH. To facilitate case sensitive search, we need to choose a suitable collation. To know the available collations supported by SQL Server, run the following query.

SELECT * FROM sys.fn_helpcollations() WHERE name LIKE 'SQL%'

There are some characteristics to be noted while selecting a collation,

_CS - Case Sensitive

_CI - Case Insensitive

_AS - Accent Sensitive

_AI - Accent Insensitive

_KS - Kana Sensitive

_WS - Width Sensitive

In our present case, we require the following collation,

SQL_Latin1_General_CP1_CS_AS

To change the collation of our table's column to the above collation run the following command,

ALTER TABLE SENSITIVE_SEARCH ALTER COLUMN NAME VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS

Now case sensitive search is enabled for the column NAME.

SELECT * FROM SENSITIVE_SEARCH WHERE NAME = 'YASHWANTH'

GO

SELECT * FROM SENSITIVE_SEARCH WHERE NAME = 'Yashwanth'

To know more about collations in SQL Server, follow the official documentation of Microsoft.

http://msdn.microsoft.com/en-us/library/ms143726.aspx

Database Mirroring in SQL Server - Post Mirroring Configuration, Monitoring & Troubleshooting

This post is the sequel of my post Database Mirroring in SQL Server - Configuration. In this post, we'll see about what to do after Database Mirroring is configured, how to monitor a mirroring session and troubleshooting the mirroring configuration.

Post Mirroring Configuration

After Database Mirroring is configured, the following steps have to be taken to ensure a correct service to clients,

• The logins, jobs, SSIS packages etc that are created in Principal Instance must be created in Mirror Instance manually as they are not automatically replicated when mirroring is configured.
• Any new changes made on Principal such as adding new logins, mapping new users to databases should be reflected manually in the Mirror instance too.

Monitoring the Mirroring Session

After mirroring is configured, it can be checked using the following query,

SELECT * FROM sys.database_mirroring

Run the above query in Principal and Mirror to know the status of instance, role etc. The first column of this view is database_id. For first records the values of this view is NULL because database_ids 1,2,3 and 4 are of system databases Master, Model, Msdb and Tempdb respectively. System databases cannot be mirrored, so they have NULL values.

For more information about this view, go here,

http://msdn.microsoft.com/en-IN/library/ms178655.aspx

Database Mirroring Session can be monitored by using a built-in tool named Database Mirroring Monitor. 

Go to Object Explorer, right click the Principal or Mirror Database. Select Tasks and then click Launch Database Mirroring Monitor.

The databases that are configured for Mirroring are registered automatically with this tool. If not, they can be registered manually by clicking Register Mirrored Database on Home screen. From there, Principal and Mirror can be connected and registered. After registering, Database Mirroring Monitor shows the status of Principal and Mirror and their connection status with Witness. It is refreshed automatically for every 30 seconds and shows any unsent log including its size, estimated time to send it from Principal to Mirror etc. There lies a button History on clicking which the synchronization history appears. The history results can be filtered according to the given options.

In the Warnings tab, warnings can be configured by setting desired threshold values for different kinds of warnings. When a configured threshold is exceeded, an event is logged to the Application Even Log. This can be configured as an email alert.

Removing Database Mirroring

Configuring something may be difficult sometimes but removing something is very easy. So is the case with Mirroring. Mirroring can be removed with just one mouse click.

In Object Explorer, right click on the Principal database and click Properties. Under Mirroring tab, click on Remove Mirroring button. You will be asked for a confirmation. Confirm if mirroring has to be removed and it will be removed. Now you've removed Mirroring but you're not done with it completely. In the same Database Properties window, you can still see a Server Network Address for your principal instance. This is because there are endpoints configured and they have to be removed. For this, connect to Principal, Mirror and Witness (if any) and run the following SQL statement.

DROP ENDPOINT <Endpoint-Name>;

With this command, mirroring is removed completely including its endpoints. The Mirror database remains in a RESTORING state as it was created WITH NO RECOVERY option. To bring it into normal state, restore its backup WITH RECOVERY option or just run the following command,

RESTORE DATABASE <Database_Name> WITH RECOVERY

Troubleshooting Database Mirroring Configuration

While configuring Database Mirroring on a single computer with three instances, an error rises with Error Message 1418. This message indicates that the server network address cannot be reached or does not exist. It asks you to verify the server network address and re-issue the command.

For this, there is a simple solution. Go to SQL Server Configuration Manager and right click on SQL Server service and click on Properties. Under Log On tab, select This Account. In the Account Name field, provide your local system Account Name or click on Browse and search your account name by using Check Name button. Provide and confirm your account password and click Apply. It asks for restarting the service, click Yes and the service will be restarted. Do the same procedure for Principal, Mirror and Witness instances.

Now go to Database Properties window and click on Start Mirroring button which starts your mirroring session.

Database Mirroring in SQL Server - Configuration

Configuring Database Mirroring becomes easier when one understands completely about what is mirroring. Please read my previous posts about introduction of Database Mirroring here,

Database Mirroring in SQL Server - Introduction (Part - 1)

Database Mirroring in SQL Server - Introduction (Part - 2)

Mirroring can be configured by using SQL Server Management Studio or Transact - SQL statements. Before configuring mirroring session, ensure the following conditions are met,

• The recovery model of Principal database is FULL.
• Create a mirror database on Mirror server by restoring the latest full backup of Principal Database WITH NO RECOVERY which allows inserting log records into it.
• Take a log backup of Principal database and restore it on Mirror database WITH NO RECOVERY option.
• To setup Database Mirroring, the used login should be of sysadmin fixed role.

The communication between instances that are participated in Mirroring is performed over TCP endpoints. Each instance should have its own Endpoint that listens over a unique TCP/IP port.

Configuring Database Mirroring using SQL Server Management Studio

In the Object Explorer, right click on the Principal Database and select Properties.

Go to Mirroring Page and click the Configure Security button which launches Configure Database Mirroring Wizard. Click Next.

Then comes Include Witness Server page which asks whether you need a Witness instance to be configured. If you want you Mirroring Session, select NO otherwise YES. Right now, I'm selecting YES.

Click Next which takes you to Choose Servers to Configure. Ensure that Witness Server Instance is checked to configure Witness.

Click Next to go to Principal Server Instance configuration. There the Principal Server Instance is already selected. As said earlier in this post, communication is done over TCP Endpoints. Each endpoint has its own TCP/IP port. By default there is a port 5022. If you're setting up Mirroring in your production environment, use another port for security reasons. The name of endpoint is Mirroring by default. You can rename it. There is also one more option for encrypting data that is sent through that endpoint, checked by default. If you want your data not to be encrypted, uncheck that check box.

Click Next to configure Mirror Server Instance. Select an instance that has to be used as Mirror and connect to it. The above specified things imply here also. Make sure that the listener port of Mirror should not be same if both Principal and Mirror are two separate instances on a single machine.

Click Next to configure Witness Server Instance. Even here, the above specified conditions imply.

Click Next to go to Service Accounts page. If the three instances belong to same domain account then no need to specify service accounts, leave the text boxes empty. If not, specify service accounts. This creates logins for each account and grants CONNECT permission on the endpoints. If the three instances are in a Workgroup then also no need to specify any Service Account.

Click Next which takes you to Complete the Wizard page. Verify all the options selected till now and click Finish if everything is OK. Now the endpoints are created for each instance.

Click Close button which takes you to Database Properties dialog box. In this dialog box, the properties of Principal, Mirror and Witness are displayed. SQL Server asks whether to start mirroring or not. If you want it to start mirroring right now, click on Start Mirroring. To start the session later, select Do Not Start Mirroring.

If you click Start Mirroring, it takes a few seconds to start the session and gives you the following screen.

At the bottom of above screen, you can see the current status of your Mirroring Session. You have options to Pause or Remove Mirroring and Failover (this is for Manual Failover).

You can also see your databases in Object Explorer by connecting to your Principal and Mirror Instances.

Now Principal and Mirror are synchronized with each other. The Mirror database will be in Restoring state as it was created WITH NO RECOVERY option. It cannot be connected now. Only Principal can be connected.

To test synchronizing, do some transactions on Principal and refresh the Object Explorer. You can see Principal and Mirror getting synchronized.

To test Automatic Failover, go to SQL Server Configuration Manager and shutdown Principal instance. Refresh the object explorer and you can find Mirror has become Principal. This is done by Witness.

Database Mirroring in SQL Server - Introduction (Part - 2)

In my previous post, I've introduced Database Mirroring with its requirements and operating modes. As said, in this post I'll discuss about the importance and impact of configuring Witness in Mirroring.

Impact of Witness in Database Mirroring

The main job of Witness is monitoring the partners that are participating in Mirroring session. It checks frequently whether both Principal and Mirror are in working state. A single witness can be used to monitor more than one mirroring session.

In High Safety Mode

If a Witness is configured in a Mirroring Session then Quorum is needed. Quorum is a relationship that exists when two or more instances in a Database Mirroring Session are connected to each other. If any instance is not connected then that particular instance loses quorum. If no instance is connected then the whole session loses quorum hence the databases become unavailable.

There are three types of Quorum,

Full Quorum - Consists of both the partners and witness.

Witness to Partner Quorum - Consists of any one partner and Witness.

Partner to Partner Quorum - Consists of only partners.

These quorums are supported in High Safety Mode. When there is a Full Quorum, all the instances act according to their roles until a manual failover is performed.

Suppose Principal is down and loses connection with Witness and Mirror and if Witness and Mirror remain connected then they both form a quorum and an automatic failover occurs. Now Mirror becomes Principal and starts servicing the clients.

Suppose Witness becomes unavailable and steps out of Quorum, the partners remain connected to each other, this is called Partner to Partner Quorum. In this case, a manual failover is possible.

If all the server instances become disconnected from each other, the session is said to have lost quorum. As server instances reconnect to each other, they regain quorum with each other.

• If the principal server reconnects with either of the other server instances, the database becomes available.
• If the principal server remains disconnected, but the mirror and witness reconnect to each other, automatic failover cannot occur because data loss might occur. Therefore, the database remains unavailable, until the principal server rejoins the session.
• When all three server instances have reconnected, full quorum is regained, and the session resumes its regular operation.

To have a detailed explanation of how a Quorum affects database availability, follow the official documentation of Microsoft,

http://msdn.microsoft.com/en-IN/library/ms189902.aspx

In High Performance Mode

While using High Performance Mode, it is recommended to set the Witness to OFF because a witness can be set in this mode but it serves no purpose. If a witness is configured in this mode, it requires a quorum which should consist of at least two instances connected to each other.

If the witness is disconnected from the quorum when any of the partners goes down then the database becomes unavailable. When mirror goes down, the principal should be connected to witness otherwise the principal's database goes offline until mirror or witness rejoins the session. As said earlier, in this mode only forced failover is possible. When a witness is configured, if principal is down then it requires mirror and witness to be connected to each other to perform a forced failover.

For a detailed explanation of Database Mirroring Operating Modes, read Microsoft's documentation,

http://msdn.microsoft.com/en-us/library/dd207006.aspx

How a Witness performs Automatic Failover

When the partners get disconnected from each other, witness ensures whether one of them is available for the clients. In High Safety Mode, if a Mirror loses the connection with Principal after getting synchronized with it completely but still remains connected with Witness then no failover occurs and Principal keeps on serving its database to clients. In this case, the Principal keeps on accumulating log records that have to be sent to mirror once it joins mirroring session.

If Principal gets disconnected from Witness then Mirror server knows that principal is down and immediately initiates an automatic failover. If mirror is disconnected from principal and witness then no failover occurs and the state of Principal is not considered in this case.

To know more about a Witness, follow the following link,

http://msdn.microsoft.com/en-us/library/ms175191.aspx

Database Mirroring in SQL Server - Introduction (Part - 1)

The most important feature for any application is Availability. An end user gets satisfied completely when the application is available at any time. This availability is achieved from the database itself. SQL Server offers many such availability mechanisms. One of such mechanisms is Database Mirroring. I'm going to produce a series of articles on Database Mirroring.

Database Mirroring was first introduced in SQL Server 2005 and it is available in Enterprise, Developer, Business Intelligence and Standard Editions. The main concept of Database Mirroring is maintaining a standby copy for your database and keeping both the databases in a sync so that the copy database serves the clients when the main database faces any kind of problem.

There are three instances participated in Database Mirroring,

Principal - This is where the main copy of database resides.

Mirror - This is where the standby copy of database resides.

Witness - This is an optional instance which initiates automatic failover from Principal to Mirror and is set only in High Safety Operating Mode which I'll discuss later in this post.

When database mirroring is set up, Principal sends the transaction log records to the Mirror over network and Mirrors gets synchronized with Principal. When Principal becomes unavailable due to any problem, Mirror becomes the new Principal and serves the clients. This process is called Failover. When a Witness is configured, it initiates this failover automatically.

In SQL Server, Database Mirroring allows only one mirror for one principal. To have more than one mirror, another availability solution namely AlwaysOn High Availability should be used which supports up to four replicas for one principal. In SQL Server 2012 Books Online it is specified that Database Mirroring will be deprecated. This means in the next release (SQL Server 2014), it is supported but will not be available in a later release of SQL Server. So, Microsoft recommends to use AlwaysOn High Availability.

Requirements for Database Mirroring

Before setting up database mirroring, the following conditions should be satisfied:

• The Principal and Mirror (collectively called Partners) should be of same version and edition of SQL Server.
• The Witness should be of same version and can be of any edition. Even an SQL Express can serve as Witness as it is just a monitoring one.
• Principal, Mirror and Witness should be configured on three different instances.
• The partner databases should be in Full Recovery Model.
• There should be enough disk space on Mirror server for Mirror database.
• The latest backup of Principal database should be restored on Mirror Server WITH NO RECOVERY because this allows inserting transaction log records into the database.
• The log backup of Principal database should be taken and restored to the newly restored Mirror database WITH NO RECOVERY.
• The Principal and Mirror databases should be with same name.

Failovers in Database Mirroring

There are three types of failovers in Mirroring,

Automatic - When Principal is down, the mirror becomes Principal automatically. This is done by Witness.

Manual - When Witness is not configured and Principal is down, Database Administrator has to make the mirror as Principal manually.

Forced - When Principal is down, the mirror is forcibly made the Principal with some data loss.

Automatic and Manual Failovers do not incur data loss and are supported in Synchronous Database Mirroring whereas Forced Failover causes data loss and is supported in Asynchronous Database Mirroring.

Operating Modes of Database Mirroring

There are two operating modes of Mirroring which impacts the application safety and performance. 

• High Safety Mode (Synchronous Database Mirroring)
• High Performance Mode (Asynchronous Database Mirroring)

Let us know what these modes are:

High Safety Mode (Synchronous Database Mirroring)

As said earlier, Principal sends transaction log records to the Mirror constantly. In High Safety Mode, when a transaction is made on Principal, it doesn't commit the transaction immediately. It writes the log to its disk and sends it to the mirror database. Now principal waits until mirror writes the log to its disk and sends an acknowledgement to Principal. After receiving the acknowledgement, principal commits the transaction to the client.

This mode assures full safety for data and keeps both principal and mirror databases in a synchronized state but increases transaction time. This mode requires an optional instance configured, called Witness. When witness is configured, it performs automatic failover from principal to mirror in case of any failure in principal. Even manual failure can be done when witness is present. If a witness is not configured then the database administrator has to perform manual failover in case of any failure in principal.

If Witness is not configured and Principal is down then the Mirror is suspended. In such cases, a Forced Failover can be done to the Mirror with some data loss.

High Performance Mode (Asynchronous Database Mirroring)

In High Performance Mode, when a transaction is made on Principal, it sends the log record to Mirror and commits it immediately without waiting for any acknowledgment from Mirror. There may exist a lag between principal and mirror in writing log records to their disks. When there are a lot of transactions made on principal over a time, it may increase traffic over the netwrok between principal and mirror.

When there is any failure in principal, a forced manual failover should be done to mirror. As said above, if there is a delay in mirror writing all the log records received from principal then failover incurs the loss of that data and brings the mirror in available state. In this mode, Witness should not be configured as there will be issues regarding quorum. 

I'll write about impact of witness in both the modes in later posts of this series.

For introduction about Database Mirroring, read the official documentation of Microsoft,

http://msdn.microsoft.com/en-us/library/ms189852.aspx

The next post of this series is available here,

http://techie-friendly.blogspot.in/2014/08/database-mirroring-in-sql-server_17.html

Identity Jump in SQL Server 2012 and its resolution

Sometimes a new feature added in a product may affect an existing feature in it. One of such scenarios is Identity Jump in SQL Server 2012. This is affected because Microsoft introduced Sequences in SQL Server 2012 upon the request of so many users. I didn't know about this until one of my cousins pointed it out. Then I went on surfing about this in various blogs.

Let's have a look about this...

Create a table and insert some values in it like below,

USE AdventureWorks2012

GO

CREATE TABLE TEST_IDENTITY(ID INT PRIMARY KEY IDENTITY(1,1),NAME VARCHAR(100))

GO

INSERT TEST_IDENTITY SELECT 'A'

INSERT TEST_IDENTITY SELECT 'B'

INSERT TEST_IDENTITY SELECT 'C'

Now run a SELECT query and see the data in it.

SELECT * FROM TEST_IDENTITY

Now, go to SQL Server Configuration Manager and restart the SQL Server service. After it is restarted, insert one more row in the above table and run the SELECT query,

INSERT TEST_IDENTITY SELECT 'D'

GO

SELECT * FROM TEST_IDENTITY

You can observe there is a long jump in the value of identity... From 3 to 1002!!

Here the data type of Identity Column is INT, so it jumped in thousands. If BIGINT is used then it jumps more long. This is a known bug for Microsoft. I tried installing all the updates of SQL Server 2012 released till now, thinking it might be resolved. There was a recent release to SQL Server 2012 named SQL Server Service Pack 2 in June, 2014 but this bug wasn't resolved. Instead some blogs claimed it as a feature. It cannot be considered as a feature because in production environments this may result in a dissatisfaction as services need to be restarted often.

There are two remedies for this issue. One is using the trace flag -T272 as a start up parameter and the other is using Sequences instead of Identity Columns. We'll see how both work...

Using Trace Flag -T272

Add a trace flag as a start up parameter of SQL Server service. Go to SQL Server Configuration Manager and right click on SQL Server Service. Click on Properties and go to the tab Startup Parameters. Specify -T272 and click Add and click on Apply. Now restart the SQL Server service in order to have it effected.

Now insert some rows in the table as below

INSERT TEST_IDENTITY SELECT 'E'

INSERT TEST_IDENTITY SELECT 'F'

INSERT TEST_IDENTITY SELECT 'G'

INSERT TEST_IDENTITY SELECT 'H'

You can see that there is no jump in Identity if you run SELECT query against it...

To test it again, restart the service and insert one more row and run SELECT query against it...

INSERT TEST_IDENTITY SELECT 'I'

GO

SELECT * FROM TEST_IDENTITY

If you add the trace flag to your production server, you have to bear the jumps occurred till now but no more jumps will be occurred. When new tables are created, everything will be fine.

Using Sequences

Sequences are added in 2012 version of SQL Server. It is a database level object whereas identity is not an object and sticks only to the particular table. If a sequence is created then it can be used for any table across the database. Now, let's create a sequence for a table...

CREATE SEQUENCE dbo.SEQUENCE1

AS INT

START WITH 1

INCREMENT BY 1

Now create a table that uses values generated by this sequence...

CREATE TABLE TEST_SEQUENCE(ID INT PRIMARY KEY DEFAULT NEXT VALUE FOR dbo.SEQUENCE1,NAME VARCHAR(100))

Here in the place of Identity, we used the value generated for sequence as default value. Now insert some rows into this table and see how data is inserted,

INSERT TEST_SEQUENCE(NAME) SELECT 'A'

INSERT TEST_SEQUENCE(NAME) SELECT 'B'

INSERT TEST_SEQUENCE(NAME) SELECT 'C'

INSERT TEST_SEQUENCE(NAME) SELECT 'D'

GO

SELECT * FROM TEST_SEQUENCE

The data is inserted into the table as it gets inserted when there is an identity column. But there lies a disadvantage with Sequences. In the above table, we used the next value of the sequence as default value of the column. But you can know the next value of the sequence by running the below query...

SELECT NEXT VALUE FOR dbo.SEQUENCE1

After knowing the next value, insert one more row into the above table and see its data...

INSERT TEST_SEQUENCE(NAME) SELECT 'E'

GO

SELECT * FROM TEST_SEQUENCE

You can see that there is a value 6 is inserted because 5 had already been generated. This can't be done with Identity Columns if they work fine without any issue.

To know the basics of Sequences, read the official documentation of Microsoft.

http://msdn.microsoft.com/en-IN/library/ff878091.aspx

Conclusion

According to me, in the production environments, Sequences can be used but they are not permanent substitution or remedy for Identity Jump Issue. Preferably, adding Trace Flag helps you depending on your requirement.

Hope Microsoft resolves this bug in the future!! :)