Windows Server 2016 and SQL Server 2016 – A match made in heaven!


For all these years Windows Server 2016 is the Windows version I was waiting for, and this blog post will explain why I’m super excited about this!

With Windows Server 2016, you can now create a Windows Cluster with nodes in multiple domains. Or even without a domain (Workgroup).

With Windows Server 2016 and SQL Server 2016, you can now create an Always On Availability Group configuration with nodes in multiple domain, and this is exactly why I’m super excited as it gives more flexibility when architecting complex/robust systems.

Think about the below scenario (Simplified):

win1

In the above picture, there are 3 separate domains, and before Windows Server 2016 and SQL Server 2016, there was no way to create an Always On Availability Group configuration with these 3 nodes participating.

With Windows Server 2016/SQL Server 2016 it’s possible now to add nodes from different domains to a cluster and configure an AG on top of it:

win2

 

There are some other nice benefits of Windows Server 2016, and they are:

Seamless Upgrades:

Upgrading to Win Server 2016 is going to be easy and less painful going forward. If you have an existing Win 2012 R2 cluster, then you can opt to do a rolling upgrade. Assume that it’s a 3 node cluster, then you can evict a node and install Win 2016 on it and add it back to the cluster.

If you don’t want to go through the hassle of evicting the node, upgrading and adding it back, then you can also opt for an in-place upgrade of an existing node. Cluster will work in mixed mode.

Cluster Site Awareness:

We can now set policies to allow a SQL instance to failover to a node within the same site before failing over to a node in a different site. More control and more flexibility.

Quarantine nodes:

Let’s assume that in a cluster you have a node which is in a flapping state (not stable and it’s joining in and out frequently). With Win 2016, this node will be Quarantined. Flapping nodes can impact the whole cluster negatively and quarantine will avoid this. The quarantined nodes are not allowed to join the cluster for 2 hours.

Scaling:

Win 2016/SQL 2016 supports 24 TB of memory. Yes! Let’s cache all those databases out there! (No more IO operations).

Win 2016/SQL 2016 supports 640 cores!  <Cough> license per core $ amount! </Cough>

Storage Spaces Direct:

Win 2016 support storage spaces direct and we can now run SQL FCI and AG on storage spaces direct. Storage Spaces Direct is nothing but DAS storage replicated across all nodes.

Conclusion:

If I’m running SQL 2016, then I would definitely want to run it on Win 2016. The features and capabilities are too good to ignore.

Thanks for reading, and please watch this space for more!

Advertisements

AlwaysOn Availability Group Database Level Health Detection – SQL Server 2016


SQL Server 2016 AlwaysOn AG got many improvements, and I’m pretty impressed with the log throughput improvements and redo improvements based on some of my test cases(A detailed blog post on that will follow soon). In this blog post we will look at another key improvement, ie Database Level Health Detection.

SQL Server 2016 enhances the AlwaysOn health diagnostics with database health detection. If AlwaysOn availability group database health detection has been selected for your availability group, and an availability database transitions out of the ONLINE state (sys.databases.state_desc), then entire availability group will failover automatically.

Let’s look at a demo to demonstrate the new behaviour –

I have enabled Database Level Health Detection for my AG as shown below –

AG20161Now, in theory if one of the disks which is hosting my database tpcc is gone(both data/log file), then it should trigger a failover.

Currently node 001 is the primary replica. Let’s see, if theory matches with reality.

I went ahead and inserted data continuously to one of the tables in the database, and took the disk(which hosts both data/log files) offline via Disk Management –

AG20162

That caused the database to go into Recovery Pending State –

AG20163

Note – This controlled failure simulation was not a straightforward process. Initially the database was not going to a degraded state. After my interaction with some of the top SQL Server Experts out there in our community, I was able to force the DB go into a degraded state.

Ref this twitter thread for more details – https://twitter.com/AnupWarrier/status/748364014682578949

This immediately caused a failover, and now 002 is the primary replica.

AG20164

Conclusion – 

This proves that Database level health detection works seamlessly and a failover is triggered when the state of a database changes from Online to other degraded states.

Thanks for reading, and keep watching this space for more!

Why you should think about upgrading to SQL Server 2016!


SQL Server 2005 is out of support and you might be (Or rather, you should be) planning an enterprise wide upgrade of SQL Server. Are you thinking of upgrading to SQL Server 2014? If yes, then wait! SQL Server 2016 is packed with great features and mainly, there is plenty of enhancements. Here is a quick walk-through of my 6 favorite features, and I will explain why SQL Server 2016 is a great choice for your next platform upgrade(When it’s released(RTM)):

1. In Memory OLTP Improvements

As you already know In Memory OLTP, also known as ‘Hekaton’ and ‘In-Memory Optimization’, is Microsoft’s latest in-memory processing technology. In-Memory OLTP is optimized for Online Transaction Processing (OLTP). It is integrated into SQL Server’s Database Engine and can be used in the exact same manner as any other Database Engine component.

In-Memory OLTP originally shipped with SQL Server 2014 and it mainly features two new data structures which are Memory-Optimized Tables, and Natively-Compiled Stored Procedures.

SQL Server 2016 provides an improved In Memory OLTP engine, and it overrides many restrictions which existed in SQL 2014(Phew! I was waiting for this to happen).

In Memory OLTP SQL 2016 Improvements

Feature/Limit SQL Server 2014 SQL Server 2016
Maximum size of durable table 256 GB 2 TB
LOB (varbinary(max), [n]varchar(max)) Not supported Supported
Transparent Data Encryption (TDE) Not supported Supported
ALTER PROCEDURE / sp_recompile Not supported Supported (fully online)
ALTER TABLE Not supported
(DROP / re-CREATE)
Partially supported*
DML triggers Not supported Partially supported
(AFTER, natively compiled)
Feature/Limit SQL Server 2014 SQL Server 2016
Indexes on NULLable columns Not supported Supported
Foreign Keys Not supported Supported
Check/Unique Constraints Not supported Supported
Parallelism Not supported Supported
OUTER JOIN, OR, NOT, UNION [ALL], DISTINCT, EXISTS, IN Not supported Supported
SSMS Table Designer Not supported Supported

The above 2 tables should help you understand why SQL Server 2016 should be the platform of your choice if you are planning to deploy In Memory OLTP for your critical workloads.  I would still say In Memory should be your last resort to fix issues like latch contention or heavy blocking as there are other techniques like Hash partitioning with a computed column(to reduce contention) or use Read Committed Snapshot Isolation (RCSI)(to avoid blocking). There are other overheads associated with these two, and you should evaluate all the available options and pick the right solution.

2. Column Store Indexes

SQL Server 2016 introduces real-time operational analytics, the ability to run both analytics and OLTP workloads on the same database tables at the same time (Yes, to an extend and we are not talking about replacing SSAS cubes).

Benefits of Column Store Indexes

A columnstore index can provide a very high level of data compression, typically 10x, to reduce your data warehouse storage cost significantly. Plus, for analytics they offer an order of magnitude better performance than a btree index. They are the preferred data storage format for data warehousing and analytics workloads. Starting with SQL Server 2016 ,you can use columnstore indexes for real-time analytics on your operational workload.

Recommended use cases:

  • Use a clustered columnstore index to store fact tables and large dimension tables for data warehousing workloads. This improves query performance and data compression by up to 10x.
  • Use a nonclustered columnstore index to perform analysis in real-time on a OLTP workload.

You might have many analytics queries today which you will never run directly against a OLTP database mainly because it might impact the OLTP workload performance, but with Operational Analytics you have an opportunity to test and ensure that those analytics queries can indeed be run against a OLTP database or even consider leveraging Always On AG secondary replicas powered by nonclustered columnstore to offload your analytics workloads .

3. Enhanced AlwaysOn Availability Groups

The AlwaysOn Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012, AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations.

AlwaysOn AG SQL 2016 Improvements

Failover on database health
Distributed Transaction Coordinator support
3 Synchronous replicas
Optimized log transport
Load balancing across readable secondary replicas

The first enhancement is self-explanatory,now a failover can be triggered according to the state of the database health. That can come handy in some situations.

DTC support is promising. In the past I have worked with some 3rd party apps which relied heavily on DTC and it’s good to see that SQL 2016 is supporting it.

Optimized log transport is my favorite enhancement. Normally on a high concurrent OLTP system, there is always a chance for the secondaries to stay behind and impact the RTO/RPO service level agreements. With optimum log transport and parallel redo threads, this overhead will be reduced.

4. Native JSON Support

SQL Server 2016 natively supports JSON. With native support you have the ability to format and export data as JSON string. You also have the ability to load JSON text in tables, extract values from JSON text, index properties in JSON text stored in columns, etc.

First thing we should be aware is that built-in JSON support is not the same as the native JSON type. In SQL Server 2016, JSON will be represented as NVARCHAR type.

JSON Use cases

  • You can accept JSON, easily parse and store it as relational
  • You can export relational easily as JSON
  • You can correlate relational and non-relational

5. Always Encrypted

Always Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national identification numbers (e.g. U.S. social security numbers), stored in SQL Server databases. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server). As a result, Always Encrypted provides a separation between those who own the data (and can view it) and those who manage the data (but should have no access).

Always Encrypted makes encryption transparent to applications. An Always Encrypted-enabled driver installed on the client computer achieves this by automatically encrypting and decrypting sensitive data in the client application. The driver encrypts the data in sensitive columns before passing the data to the Database Engine, and automatically rewrites queries so that the semantics to the application are preserved. Similarly, the driver transparently decrypts data, stored in encrypted database columns, contained in query results.

Data is vulnerable when it’s in rest and in motion. Features like TDE(Transparent Data Encryption) protects the data which is at rest, but till SQL 2016, there was no way to protect the data when it’s in motion. Attacks like man in the middle attack can compromise the data which is in motion, however with SQL 2016 and Always Encrypted we now have a solution to this problem.

6. Temporal tables

Temporal tables, also named system-versioned tables, allow SQL Server to automatically keep history of the data in the table. Temporal tables were introduced in the ANSI SQL 2011 standard and is now available in SQL Server 2016.

A system-versioned table allows you to query updated and deleted data, while a normal table can only return the current data. For example, if you update a column value from 5 to 10, you can only retrieve the value 10 in a normal table. A temporal table also allows you to retrieve the old value 5. This is accomplished by keeping a history table. This history table stores the old data together with a start and end data to indicate when the record was active.

A classic use case for temporal table is for Audit purposes. With temporal tables you can find out what values a specific entity has had over its entire lifetime.

Another use case is a quick recovery when you or someone deletes a row from a table (Yes, the same old delete without a clause!). With temporal tables, you can retrieve the deleted row details from the history table and insert it back into the main table.

So, you might be curious about this. What is the difference between Temporal tables and CDC (Change Data Capture). Here is the explanation:

CDC is usually useful to keep changes for a short period of time to feed those to external consumers such as an ETL process.Temporal tables can keep historic data for very long periods. Usually used for auditing/legal purposes and time travel queries.

Conclusion:

These 6 features should convince you on why you should consider upgrading to SQL 2016 when it’s released. There are many other exciting features like Polybase, Completely overhauled SSRS etc and we will talk more on that during the upcoming posts.

Thanks for reading and keep watching this space for more!

SQL Server 2016 CTP 3 Setup – UI Improvements


There are some improvements in the setup UI for SQL Server 2016 CTP3, and its great to see Microsoft making these changes as they receive feedback from multiple customers and early adopters.

My earlier post on setup UI changes for CTP 2 is available here.

The 1st change is around granting the SQL Engine Service account necessary privileges to enable INF(Instant File Initialization) during the setup itself.

This was a surprise when I saw it for the 1st time :

New Setup

You can notice the check box for Grant Perform Volume Maintenance Task privilege.

There is an interesting post by Nic Cain(B/T) on this topic and you can read it here.

The 2nd change is around Tempdb configuration and here are the new options :

New Setup2

The wizard will now check the no of cores on the server and will come with a pre-populated value for the no of temp db data files.

It also provides options to define the initial size and the auto growth size.

This might not be something which the power users care,(When I said power users,I meant people who automate lot of SQL server deployments and are already doing this with custom scripts) however this is really a value add for people who do manual SQL installation.

Going forward we also don’t need to turn on Trace Flags 1117 and 1118 as they are taken care by default.

Conclusion:

I’m really glad that Microsoft is giving options for users to follow some of the long-standing best practices during the install process itself and this is a welcoming change.

Thanks for reading and keep watching this space for more!

Installing SQL Server 2016 CTP2 – UI Changes


SQL Server 2016 CTP2 public build was released by Microsoft yesterday, and if you haven’t downloaded it yet, then drop everything(No, not a Production DB !) and go and get it from here.

This post is based on the UI changes/additions which I observed while I installed this specific build. Its going to a very short post as there are no major changes.

First addition or change was around the Feature Selection page –

2016CTP2

 

You can notice a new instance feature called PolyBase Query Service for External Data. 

This feature enables truly integrated querying across Hadoop data and SQL Server data using standard T-SQL statements.

Second change or addition was around the Database Engine Configuration page.

2016CTP22

This one is beautiful, I was pretty much excited when I saw this for the first time. As the description says the default is 8 or the number of cores, whichever is lower.

My test machine had 2 cores and it configured 2 files for me.

2016CTP23

However, the growth is still 10% for these files ! You may want to change that.

2016CTP24Conclusion –

Those are the quick UI changes which I could notice during the install process.

Thanks for reading and keep watching this space for more ! (Hint : Lot of good SQL Server 2016 stuff coming your way)

SQL Server 2016 Announced, and it stretches to the Cloud!


Last week during Ignite, Microsoft CEO Satya Nadella announced the next version(Till then it was called as SQL vNext) of SQL Server, and its SQL Server 2016.

Satya started the Ignite keynote, and he looked really pumped when he announced SQL 2016.

“SQL 2016 perhaps is the biggest breakthrough you have ever seen in database technology”

2016_1

 

He was focusing on Stretch Database capabilities, and he called it can infinite database where you have the capability to pick a single table and stretch it to Azure without any code changes.

The keyword here is “without any code changes”.

SQL Server 2016 is packed with some real exciting features, and below is my top favorite list(Yes, ranked from 1 to 3) –

  1. Operational Analytics – In Microsoft SQL Server 2016, users can run analytic queries on in-memory and disk-basedOLTP tables with minimal impact on business criticalOLTP workloads, requiring no application changes.The concept is based on leveraging In Memory OLTP + In Memory Column Store.This indeed is game changing. The idea of running analytic queries on OLTP tables might be a strict ‘No’ now, however as SQL Server evolve we can see changes in thought process.
  2. Always Encrypted –  

We had Transparent Data Encryption(TDE) all these years,and its been doing a great job. Then, why there is a need for something like Always Encrypted ?

TDE encrypts data at rest. As soon as the data is in motion, its vulnerable. Classic example is man-in-the-middle-attacks.

Always Encrypted protects data in rest and in motion. This feature lets application encrypt and decrypt critical data automatically, and it happens seamlessly in ADO.NET.

 The master-key resides in the application and there are no application changes which are required.

3.   Stretch Database – 

We all have hot and cold data in our databases, and in majority of the cases this data will be in the same database, same table without any specific partitions.

Years old data which might come useful only during audit purposes will be utilizing costly storage space and will add extra maintenance overhead.

SQL Server 2016 will bring in a new concept called stretch database, where in you have the ability to pick a specific table and stretch it to Azure.

If an application queries for the historical data, it will be retrieved from Azure.

Storage in Azure is cheap and the price will always go down as long as the “Cloud War” is happening between Microsoft, Amazon and Google.

With concepts like Always Encrypted, the data which resides or in motion to Azure is secure.

Apart from these 3 features, I’m really looking forward for Query Store and the Round Robin Load Balancing capabilities for secondaries in AlwaysOn Availability Groups.

There will be plenty of In Memory OLTP enhancements and it will cover larger T-SQL surface area. More on this will follow shortly.

Conclusion – 

Yes, SQL Server 2016 is a good release with lot of nice features and enhancements.

I’m pretty happy with the vision of the Data Platform Group within Microsoft, and I’m really looking forward to write more about these features in the upcoming months.

Thanks for reading and keep watching this space for more !