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 !

My 1st SQL in the City Experience !


I attended my 1st #SQLinthecity event hosted by RedGate yesterday. This is one of those events which I wanted to attend for a long time, and I’m glad I could make it this year.

IMG_3194

Attendees registering for the event

This whole day event was packed with some cool sessions, and I was able learn something new from each and every session I attended. Yay,  win !

High quality training delivered by RedGate and its all free ! Yes, you heard that correct.

It was a busy day for me and attending back to back sessions are always fun. Here is a list of all the sessions I attended and a short description on what I liked in each session:

 

1st session :  You did what to my transaction log?  

Speaker : Gail Shaw/Tony Davis.

This was a fun session where Gail Shaw and Tony Davis demonstrated what all possible wrong things people do to their transaction log when it’s full.

This session reminds us that Google/Bing is good, but it contains lot of bad info too!

IMG_3197

Gail and Tony talking about transaction log mistakes!

 

What I liked about this session:

It was interactive and had some demos in it. Interactive sessions are always welcome, and that’s the way to go.

Both Tony and Gail did a good job and there was never a dull moment during the whole 1 hr window.

 

 

2nd session : 101 stupid things your colleagues do when setting up SQLServer.

Speaker : Matt Slocum

I should say Matt did a great job in this talk. This was a quick session on what should be the right way of setting up SQLServer. What is recommended vs What’s not.

IMG_3199

Matt talking about SQLServer setup mistakes !

 

What I liked about this session :

Solid content, Ability to answer to the point etc.

 

 

 

 

3rd session : SQLServer 2014 new features.

Speaker : Kevin Boles

Title name is self-explanatory, and this session was all about the new features in SQL2014. Kevin Boles did a good job here and he ensured that there was never dull moment in this session.

IMG_3200

 

What I liked about this session :

Good content and Kevin talked about lot of features which are kind of hidden within SQL2014, and I like it.

 

 

 

4th session : SQLServer tips and tricks.

Speaker : Ike Ellis

The abstract for this session was interesting and it was like : ” This is a presentation for the YouTube generation. Come discover a series of 20 tips on a wide variety of topics.

What I liked about this session :

I really had a good time attending this session and learned lot of new tips and tricks. Ike did a good job and the session was very interactive. Each tip (Wide variety of topics : Includes SSMS,Profiler, SSRS etc) was explained within 3-5 minutes and demos were nice.

Conclusion :

I should really appreciate RedGate for what they are doing for the SQLServer Community/Family.Events like these are classic examples that they care for their product and us.

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

SQLPASS Summit 2014 is here, The largest SQLServer Conference !


November is not the right time to visit Seattle (Blame the rain!), but I kinda like November because that’s when #SQLPASS Summit happens.

SQLPASS Summit is the largest SQLServer conference in the world, and this year the number seems pretty huge with over 5000 attendees !

#Summit14 will be my 2nd Summit, and I’m really looking forward to it. My 1st was Summit12, and I missed the one which happened in Charlotte, NC (Summit13).

This year is very special as SQLServer 2014 was released this April, and there are tons of SQL2014 related sessions for this Summit.

If you are looking for deep dive sessions on In Memory OLTP,Buffer Pool Extension, Cardinality Estimator, Deep Azure Integration, then Summit14 is what you need to attend. Register here !

I normally check-in for the event little early, and PASS does a great job by opening the registration counters by Sunday evening.

I completed my registration today and here are some pictures for you :

IMG_3176

IMG_3186

IMG_3181

I will be blogging LIVE for Day1 keynote, and will be writing about my summit experience in the next couple of days.

Sleepless in Seattle !

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