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 !

Exploring AlwaysOn Availability Group Template in Azure


This is one of those posts which is due for a long time. I was really excited when Microsoft announced the release of an AlwaysOn AG template for the Azure Portal back in August 2014.

I had decided to explore this AG template soon after the launch, and this post will explain how easy it is to deploy a 2 node AlwaysOn AG deployment in Azure. Trust me, it’s just a matter of few clicks.

Sit back and relax,we are now going to deploy AG in Azure.Here are the steps :

After you login to the Azure portal, you can access the Market Place

AGTemplate1

You will be surprised to see the number of options which are available in market place now.

AGTemplate2

Search for AlwaysOn ( I really hope MSFT will correct this, it should be rather be AlwaysOn AG) in the search box.

AGTemplate3

 

Choose SQL Server 2014 AlwaysOn and proceed by clicking Create.

AGTemplate4

 

Enter details for the required fields and also select the details needed for SQL Server settings. For the purpose of this post, I will be choosing default settings for the domain controller,virtual network,storage etc.

AG6

Note : Ignore the warning messages in the picture above.Warning for Resource Group is shown as I already have a RG with the same name.

That’s it, and you click create.

It takes around 40 – 45 minutes for the whole process to complete and once it was completed, I had the below set of virtual machines.

AG5

 

2 machines for the domain controllers, 1 for the file share witness and 2 for the AG nodes.

Here is a view of the Resource Group and its resources :

AG4

If you login to one of the SQL nodes and open up SSMS, you can see that AG is fully configured and its ready for use.

AG1

Here is the dashboard view and the quorum info :

AG2

AGQ

Conclusion :

Templates are interesting, and this is an excellent way of deploying AGs in large-scale. I’m pretty sure that MSFT will be working more to optimize this, and will add more and features to it.Looking forward to it.

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

This one enhancement makes AlwaysOn Availability Group better in SQL Server 2014 !


I keep getting questions on what is improved other than the increased number of secondaries(8 now) with AlwaysOn Availability Group(AG) in SQL 2014. In this blog post  I would like to talk about one major enhancement around the increased readable secondaries availability in SQL Server 2014.

In SQL Server 2012 there is a good chance that your read workloads can be killed during network failures. Typically this happens in a multi subnet environment where you have AG nodes in 2 or more data centers.If network failure results a communication failure between primary and secondary replicas, then the secondaries will go into resolving state.In resolving state, the read workloads are basically killed. (Yes, yet another reason why you need to test your AG deployments)

With SQL Server 2014, there is good news. Read workloads are not impacted during network failures, primary replica down or cluster quorum issues.

Yes, it stays up all the time, and will be serving your read workloads. The replica will be in resolving state, however the databases are still available for your read connections/workloads.

Keep in mind : Read-only routing is not yet supported for this, and you cannot use listener for your reporting workloads.Brent Ozar calls out this in his blog post here.

Lets now walk though this enhancement :

I will be using a 2 node AG setup and the data transfer mode(Availability Mode) is asynchronous.

If I query the DMV sys.dm_hadr_availability_replica_states I can see the replica states.

SELECT role_desc,connected_state_desc  FROM sys.dm_hadr_availability_replica_states

role_desc               connected_state_desc
PRIMARY              CONNECTED
SECONDARY        CONNECTED

Lets double-check if the data transfer is set as asynchronous

SELECT  is_primary_replica, synchronization_state_desc FROM sys.dm_hadr_database_replica_states

is_primary_replica       synchronization_state_desc
0                                  SYNCHRONIZING
1                                  SYNCHRONIZED

Everything looks good, and now lets take down the primary replica.

Note : Dont try this at home ! (I mean production)

Okay, As this is a test instance I really don’t care and what I did was shutdown the SQL Services to take the primary down.

If we query the DMV again, we can see

SELECT role_desc,connected_state_desc  FROM sys.dm_hadr_availability_replica_states

role_desc                connected_state_desc
RESOLVING           DISCONNECTED

And note, there is no entry for primary as we I have taken it down.

Even though the secondary replica is in resolving state, read connections just works.

USE DemoAG
GO
SELECT COUNT (*) AS [TotalRowCount] FROM [dbo].[InsertCount]

TotalRowCount
6886

The secondary database is in NOT SYNCHRONIZING, NOT_HEALTHY state, however its ONLINE.

SELECT synchronization_state_desc,synchronization_health_desc,database_state_desc FROM sys.dm_hadr_database_replica_states

synchronization_state_desc         synchronization_health_desc       database_state_desc
NOT SYNCHRONIZING               NOT_HEALTHY                           ONLINE

Here is a view from Management Studio :

AG Resolving

Conclusion:

Yes, this is a very good enhancement which will make sure your readable secondaries are highly available and I’m pretty sure that this is one of those enhancement which will force you to deploy AGs in SQL 2014 rather than opting SQL 2012.

Thanks for reading and keep watching this space for more !

SELECT INTO Improvements in SQL Server 2014 !


SQL Server 2014 is a great release with high performance features like In Memory OLTP, Updatable Column Store Indexes etc.

These features still remains as the top favorite and the most compelling reasons for organizations to do an upgrade, However SQL Server 2014 also includes very small but very relevant and useful enhancements, and this post talks about one of those enhancement:

SELECT INTO now runs Parallel !

MP900386077

When I first heard about this feature my first impression was: What…was it not running parallel before ?

No, SELECT INTO was single threaded all these years and with SQL Server 2014 this will now use multiple threads.

Let’s do a quick walk-through of this enhancement.

In this walk-through we will use a table named MasterData which has around 12 million rows.

--Select Count
 SELECT COUNT(*) AS Row_Count FROM [dbo].[MasterData]

Row_Count
11,999,880

Data from this table will be copied over to a different table named MasterData_Copy using SELECT INTO operation.

--Turn on Actual Execution Plan
 SET STATISTICS TIME ON
 GO
 SELECT * INTO [dbo].[MasterData_Copy] FROM [dbo].[MasterData]

This operation was completed in 6 seconds (Desktop class machine) and if we examine the execution plan we can observe parallelism.

SELECTINTO1

And the distribution of row’s among multiple threads:

SELECTINTO2

Now let’s make this little more interesting. We will change the database compatibility level to SQL2012(110) and see what impact that brings to the SELECT INTO operation.

--Change Database Compatibility
 ALTER DATABASE SELECTINTO_Demo SET COMPATIBILITY_LEVEL = 110
--Turn on Actual Execution Plan
 SET STATISTICS TIME ON
 GO
 SELECT * INTO [dbo].[MasterData_Copy] FROM [dbo].[MasterData]

This operation was completed in 11 seconds and it generated a serial plan.

SELECTINTO3

And here is the row distribution.

SELECTINTO4

<Update 1/13/2015 2 PM> 

I received a comment from Toni which says “It might be a good idea to set MAXDOP, most likely having 16 threads isn’t the best option”.

I absolutely agree with this and a well tested MAXDOP setting will definitely bring better results and this is something which you have to keep in mind when queries goes parallel.

</Update>

Conclusion:

If you are using SELECT INTO a lot in your environment, then I would highly recommend you to compare and see what improvements you can again out of SQL Server 2014.

SQLCMD and SET QUOTED_IDENTIFIER


Let’s talk about something simple today. You might already be knowing this, but if you haven’t seen/noted this before then this post will definitely help you.

I was working on requirement wherein I had to simulate a workload which will do massive inserts to a table which had computed column. I tested my T-SQL script to create the workload via SSMS and it worked like a gem.

Yay, all good and that’s the end of this post!  No, not really.

Things started little interesting when I used SQLCMD as a test case to call the same T-SQL. I got an error which said:

Msg 1934, Level 16, State 1, Server <Name>, Line 4
INSERT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

sqlcmd

 

That’s interesting because my commands worked just great when I used SSMS earlier! What’s wrong with SQLCMD now?

I quickly realized that this has to something to do with query execution options, and cross checked SSMS options.

sqlcmd2

 

So, SET QUOTED_IDENTIFIER is ON for SSMS connections, cool and that’s the reason why my commands where running cool earlier.

I quickly checked books online article for sqlcmd and found this. [Well, this is a very good article which is written for sqlcmd by those technical writers at Microsoft]

sqlcmd3

So, adding -I to sqlcmd as a query execution option fixed my problem.

sqlcmd4

Conclusion: 

Little tips and tricks like these always surprises me when I’m dealing with SQLServer, and it helps me learn something or the other daily.

Thanks for reading and keep watching this space for more. Yep, lot of SQL2014 goodness coming in next few days.

#SQLPASS #Summit14 Day 2 Keynote Highlights !


This was one of those days I was waiting to happen. You might be wondering whats special? Yes, this day is very special because Rimma Nehme will be giving a keynote on  Cloud Databases 101.

Rimma Nehme is one of those super smart people who works for Microsoft Gray Systems Lab along with the very special Dr David Dewit.

Day 2 started with a talk by Adam Jorgenson, PASS Exec Vice President of Finance.
Adam started talking on how PASS operate, from where money comes and how it’s used? The key thing to note is that PASS is in good financial state with good amount of rainy day fund.
IMG_3247

Adam on stage !

Thomas LaRock was back on stage on day 2, and he started talking by thanking outgoing board member Sri Sridharan. Kudos to Sri for all his efforts. He also introduced the new board member Grant Fritchey.

Next up was Denise McInerney,VP Marketing and she talked about her experience of PASS and the career changing decisions she took. Denise mentioned how PASS changes people’s career.

She thanked the volunteers for their hard work, and the winner of PASSion award was announced and the winner was Andrey Korshikov.

Denise confirmed that Summit will be back in Seattle Oct 27-30 2015, and that’s great news !!!

Crowd was eagerly waiting for the keynote to start and Denise requested Rimma to take over the stage.
Rimma talked little about her and cracked some jokes about Dr. David Dewit. She added that she is a great fan of SQLServer/PASS community.
IMG_3249

Rimma Nehme on stage giving Day 2 Keynote on Cloud Database 101

The agenda of the talk was based on :
  1. Why Cloud? 
  2. What is a Cloud DB?
  3.  How they are built?
  4.  What is my role as a DBA?
When people like Dr. David Dewit or Rimma talk, they come with great simple slides with lot of information on them. They also tend to use real life analogy to explain things, which is really nice.
Here is an abstract of what Rimma talked:
Why cloud computing?
Why cloud? (Also known as 5th utility! )
Cloud is special because of its characteristics:
  • On demand self-servicing.
  • Location transparent resource pooling.
  • Ubiquitous network access.
  • Rapid elasticity.
  • Measured service pay per use.
History of cloud :
1960’s (Super computers/Mainframe) ,1990(Sales force) ,2002(AWS), 2006(EC2), 2008 – present (Azure, Google Apps).
Where cloud lives: Data Centers(Of course !), and the move to the Next generation Data Centers.
Data center efficiency is measured in terms with PUE (Power Usage Effectiveness) :
 IMG_3253
PUE =
Total Facility Power /  IT Equipment power.
Traditional PUE 2.0 vs Modular PUE 1.15(cooling 0%).
Microsoft Data-centers :

Over 100 DC in more than 40 countries, More than 1 Million Servers.

IMG_3254
What is a Cloud DB
 
Platform As a Service, Infrastructure As a Service ,Software As a Service.
Pizza analogy of cloud:
IMG_3256
What kind of cloud databases are out there: We have Managed RDBMS, Managed No SQL, Cloud Only DBaaS. Analytics as a Service, Object Store.
IMG_3257
How they are build
 
Why Virtualization : Put under-utilized resources in use. With a drawback that direct access to resources will be lost.
Multi Tenancy : 4 common approaches. Private OS,Private Process/DB, Private Schema,Shared Schema.
IMG_3261
Inside Azure SQL DB :
The concept is each account as 1 or more servers, each server has one or more databases, each database has one or more objects.
IMG_3264
Everything designed with HA in mind.
Layers of abstraction : Client layer, services layer,platform later, infra layer.
Network topology :
IMG_3265
MY role as a DBA
 
Last but not least Rimma talked about the role of DBA for Cloud.
IMG_3267

Rimma wrapped up Cloud 101 talk by requesting Dr.Dewitt to join her on stage, and there was a standing ovation by the crowd.

IMG_3269

 Conclusion: 

We #SQLFamily is expecting Rimma to be back next year to give a more in-depth talk on Cloud and we are looking forward to it.

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

#SQLPASS #Summit14 Day 1 Keynote Highlights !


IMG_3214

PASS President Thomas LaRock on stage !

PASS President Thomas LaRock kicked off the summit keynote.

 

Tom talked about the history of PASS and its growth curve. Positive numbers all over the place !

One of the key things to note is that PASS has delivered 1.3 million hours of training which is pretty impressive.

 

 

Next up was Microsoft Corp Vice President TK Ranga Rengarajan talking about Evolving Microsoft Data Platform.

Ranga talked about the explosion of data/data sources and its positive trend.He stressed on the evolving Data culture and how data professionals can lead this change.Microsoft data platform is capable enough to manage all the data requirements.

IMG_3218

Ranga Rengarajan on stage !

Goal is do more,achieve more via :

Capture Diverse Data

AzureDocDB, AzureHD Insight, Analytics Platform System, Azure Search.

There was a demo by Pier1 Imports on Azure Search, and the demo explained the value of Azure Search capabilities
and its power.

Pier 1 gave another demo of their Promotions app/page which uses AzureDoc DB.

They also achieve scaling using SQLDatabase sharding capabilities.

Pier 1 is also leveraging Geo Replication for its premium SQLAzure Database.

Achieve Elastic Scale

SQL2014 with Win 2012 R2, SQLServer in Azure VM’s, Azure SQLDatabase.

Max Performance

Azure SQLDatabase, SQL2014.

Simplify with Cloud

Hybrid scenarios with SQL2014, Azure as a data lake.

Ranga also talked about various businesses which are leveraging SQLServer to achieve their data/scaling requirements.

Some great numbers for SQLServer this year :

1.2 Million downloads of SQL2014. 1M Azure SQL DB’s deployed.

Major Announcements :

Azure SQL Database Major update : Leap in TSQL compatibility, Larger index handling, Parallel queries, Extended events, In memory Col Store.

Technology Sneak Peak : Pier1 Imports

Pier1 Demo was focused on In memory OLTP/ Col store on Azure SQLDatabase. 13,000 transactions/second were fired up and at the same time a reporting workload was ran and performance was awesome .

They also demonstrated the ability to stretch On Premises database to the cloud.

Next up was Joseph Sirosh, Microsoft CVP Machine Learning & Information Management.

Joseph talked about Azure Machine learning and other capabilities.

IMG_3231

Joseph making sure the crowd is engaged !

 

Again the goal is Do more,achieve more, Put data to work  :

Understand the past
Azure Data Factory

Demo from Pier1 demo on trend/stream analysis using Kinect/Azure Data Factory. Data from Kinect sensors are streamed
to azure and data is analyzed real-time.

Analyze the present
Azure Stream Analytics

Predict whats next
Azure Machine Learning

Demo by Pier1 Imports on machine learning. Pier1 app allows customer to login to the app and the customers
shopping trend/taste data is checked from Azure and results are shown.

Next up was James Phillips , Microsoft GM Data Experiences

James talked about bringing insights to more people – faster.

IMG_3237

James talking about Power BI and Machine Learning !

 

Goal again is Do more ,achieve more :

Simplify data discovery 
     
Deliver faster time to insight
PowerBI,QA

Connect to on-premises
Data Refresh, Interactive Query

Enable data culture
Live Dashboards, Drill through

James gave a cool demo of PowerBI Dashboard.

Thanks for reading and keep watching this space for more !