Archive for the ‘Book’ Category

SQL Server AlwaysOn–SQL 2014 Readable Secondary changes March 20th, 2014

Vinod Kumar

This is in continuation to the post around SQL Server AlwaysOn-Readable Secondary Setting which I posted earlier. With every new release there are multiple enhancements and I know by the time we published our book on AlwaysOn many of these features were not quite available and we missed writing about them.

One of the main addition with SQL Server 2014 for AlwaysOn is the capability to have 8 secondary replica. This is has been discussed exclusively in the book and the number of synchronous nodes etc remain as in SQL Server 2012. Nothing much changes. It is important to understand that commits don’t wait for async replica’s hardening, so there will be not much of an impact wrt to deployments are concerned.

Enhanced Readable Secondary with SQL Server 2014

So what are the new scenario’s that we are talking about from a readability point of view? In SQL Server 2012, when the availability on the secondary databases was either NOT SYNCHRONIZED (Data movement SUSPENDED) or in RESOLVING state we were not able to connect to the secondary node for ReadOnly workloads.

Scenario 1: In SQL Server 2012 if we take the Availability group to Offline mode, the secondary databases get into a RESOLVING state and we get the following error:

Unable to access database ‘AdventureWorks2012’ because its replica role is RESOLVING which does not allow connections. Try the operation again later. (Microsoft SQL Server, Error: 983)

From SQL Server 2014, existing connections made to the secondary replica directly can still be served and new connections will also be allowed.

Scenario 2: In SQL Server 2012, if data movement gets suspended, the availability database gets into a NOT SYNCHRONIZED state. In such scenarios the connections are refused and we get the following error:

The target database, ‘AdventureWorks2012’, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.

Or we might get a read access error on the secondary. Either way, with SQL Server 2014, this is no longer a problem as read directly to the secondary is possible even when the synchronization is suspended.

Note: Irrespective of the scenario mentioned above, the read operation cannot be performed using the Listener and routing list. In either of the case of RESOLVING or NOT SYNCHRONIZED state if you use the Availability Group Listener name/IP we will get the below error.

Unable to access the ‘AdventureWorks2012’ database because no online secondary replicas are enabled for read-only access. Check the availability group configuration to verify that at least one secondary replica is configured for read-only access. Wait for an enabled replica to come online, and retry your read-only operation.

Thought this is a critical hidden enhancement with AlwaysOn, it can surely come in handy for Hybrid scenarios, or when quorum is lost scenarios where we still get the ability to read from our secondary directly as a last resort and worst case operation.

This post is inspired from Book content: SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” (Paperback, Kindle).

Continue reading...


SQL Server AlwaysOn-how far behind is Secondary November 4th, 2013

Vinod Kumar

If you ever wondered why I wrote a simple post like SQL Server AlwaysOn-Find my Primary, this post is really for you. There are a lot of times customers ask me how much time is my secondary behind my primary? Such a simple question can be answered easily using some good DMVs. Here is the script I use extensively when working with AlwaysOn customers to identify what is doing behind the scene’s in their setup.

SELECT                       AS AGGroupName,
AR.replica_server_name         AS InstanceName,
Db_name(DRS.database_id)       AS DBName,
AR.availability_mode_desc      AS SyncMode,
DRS.synchronization_state_desc AS SyncState,
FROM   sys.dm_hadr_database_replica_states DRS
LEFT JOIN sys.availability_replicas AR
ON DRS.replica_id = AR.replica_id
LEFT JOIN sys.availability_groups AGS
ON AR.group_id = AGS.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
AND AR.replica_id = HARS.replica_id

Remember to run the above query on your Primary server. I have placed the output in two images for your reference.

The above columns give us an indication to our setup. What is the availability group, what are the servers, their roles, DB involved, their modes and what is their current state.

The second image which is above is the critical and the most important columns include when was the last_hardened_time and the last_redone_time. This shows how far away is my secondary. Also if the end_of_log_lsn of my Primary is different from the last_redone_lsn you know our secondary is behind my primary. For the above setup I have stopped to data sync to get this effect. Having said that, you get the drift how powerful this DMV can be.

This post is inspired from Book content: SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” (Paperback, Kindle).

Continue reading...


SQL Server 2012 AlwaysOn Book Released August 5th, 2013

Vinod Kumar

Finally the work that has been going on for close to an year now gets published – the book is now available: “SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” (Paperback, Kindle). We have been working on this project with so much heart for an year now. Writing a book is an unique experience and to see it on the bookshelves is yet another feeling. We are excited beyond imagination on the release of this book. A special thanks to Balmukund Lakhani (Blog | Twitter) to author with me in this adventure.

Why AlwaysOn?

This story can be really interesting. We turned a corridor conversation into an informational product. The task for us was to write around 100 pages of book on this niche topic. As we started writing, a real scope creep did happen to our surprise. We kept on contemplating, let us add this topic and that and so on. Actually a 6*9 dimension book spanned for more than 530 pages, so we had to shrink this mega book. Instead of compromising the content, we converted the book to a bigger 10*7 dimension to bring the number of pages to 422 :). We are excited with the content and the initial technical review from a MCM has been really promising.

The journey to write about AlwaysOn was really simple. SQL Server 2012 release has been exciting times but to our surprise there is no single book out there in the market that can give structured learning to AlwaysOn Availability Groups. So we planned to write from the basics for a DBA who has no clue how to work with SQL Server 2012 AlwaysOn Availability Groups. We have detailed and brought every single nuances of AlwaysOn Availability Group technologies in this book. We are confident we can add another 100 pages with so much more to share. So stay tuned as we will bring the second edition with more content based on your feedbacks.

Print Book: Amazon             e-Book: Kindle

PS: At this moment we don’t have any other channel to publish this book in India. Our publisher is in constant pursuit to look for alternatives, till then Amazon will the only channel.

18 Chapters at glance:

Chapter 1. High Availability and Disaster Recovery Concepts

Chapter 2. Existing High Availability and Disaster Recovery Options

Chapter 3. What is AlwaysOn?

Chapter 4. Understanding Quorum Models

Chapter 5. AlwaysOn Availability Groups

Chapter 6. Availability Group (AG) Actions on the Secondary

Chapter 7. Deploying AlwaysOn Availability Groups

Chapter 8. Features of AlwaysOn Availability Groups

Chapter 9. AlwaysOn Monitoring and Troubleshooting

Chapter 10. AlwaysOn Diagnostics

Chapter 11. AlwaysOn Advanced Monitoring

Chapter 12. Deployment Variations of AlwaysOn

Chapter 13. AlwaysOn Common Issues

Chapter 14. Availability Group Failover

Chapter 15. Migrating from Previous High Availability Scenarios

Chapter 16. AlwaysOn Availability Group Maintenance Activities

Chapter 17. AlwaysOn – FAQs

Chapter 18. SQL Server 2014 – AlwaysOn Enhancements Teaser

This is a long list and we will keep reading the content over and over again to make sure this meets the quality bar we set to ourselves. If there are any suggestions, improvements and additions to content – please drop a line to me. We will be more than happy to include the same as we expand the book in its second edition including content for SQL Server 2014 specifics.

As I sign off, I want to specifically call out two people from my team who have backed me in this adventure of learning and sharing via this book – Govind Kanshi (Technical Director – MTC) and Aniruddha Deswandikar (Director MTC). It would have been impossible to deliver this book without their unconditional support.

Print Book: Amazon             e-Book: Kindle

Thanks again to all my readers who have inspired me in writing content. I am honored from your kind words every single day and I am sure you will find this book useful as you learn SQL Server 2012 or SQL Server 2014 AlwaysOn.

Continue reading...


The Beautiful Mind: Habits that hold us back May 3rd, 2013

Vinod Kumar

Motivation is what gets you started. Habit is what keeps you going – Jim Rohn

Our mind is actually an interesting beast. It can be really powerful and make us achieve the unthinkable. Watching at some of the world records acts that involve our physical body to go to the next level, it surely reassures how powerful our minds can be. A positive thinking can make a world of difference to what we can achieve in life.

Though I talk about being positive, there are tons of these habits that we carry as baggage with us which can easily cripple us as an individual. We know we have it within us but fail or ignore it being there. It is important for us to recognize such habits and to have a happier, easier and a positive life, we need to get rid of them.

Below are my take on few dangerous habits (in-my-opinion) that I feel we get controlled via our beautiful mind. Don’t underestimate the power of your mind, it can take you places or it can make you fall to places you never wanted to venture !!!

1. Be ambiguous (if needed)

See life like a mirror and accept what it gives you. Seeing things always as black-or-white can always cloud your mind away from accepting exceptions. It is not always that you are right and the other person is wrong or one method is good while other bad or it needed not be this way or that way … There can be areas of gray which we must learn to experiment with.

Such mindset create tremendous problem for you and people around. In these situations, you are so critical about what you do and you put unfair stress onto yourself. Make a genuine effort to understand the other side of the story – there is lots to learn from people around you.

Never permit failure to become a habit – William Frederick

2. Don’t get cozy !!!

Our mind is always apprehensive of uncertainties and it always places us in the safest of zones. The mind is scary of the unknown and change is uncomfortable for many of us to even think. How many of you out there have got comfortable with the company you work for? Are you scared of change? This is fundamentally a mind game.

As we talk about getting out of comfort zone, what really hold us back is the fear for change. The best way to overcome this is to challenge your comfort zone and stretch beyond what you are doing, stretch beyond what you are capable off, stretch beyond what others perceive is possible by you – your mind is what controls you, high time you start controlling it: Period :).

We first make our habits, and then our habits make us – Dryden, John

In career, just like what got you to where you are – doesn’t assure you where you would in the future. So break the shackles and take a positive step to do things outside your comfort zone.

3. Don’t let your mind dominate you

Good habits, once established are just as hard to break as are bad habits – Robert Puller

This is a tough one. Most of the times our instincts about what we feel dictates what we do. Simple example is – I don’t feel great as the day starts and I tend to skip a gym workout. Remember, you can hardly predict what you feel maybe in an hours time :). Life is about dominating your mind – not the other way.

As we dissect our mind, it always tries to select the path of lowest resistance. In other words, it is our laziness that is playing games :).

4. begrudge is poisonous

When you envy someone, let it not poison your thoughts – it can be a devil whispering in your ears and throwing you all over the place. If you don’t have an appetite for this, keep away – as it can be really distracting and irritating from time-to-time.

I always say this to many, focusing on yourself can take you a longer distance than looking at the other person. Always realize that you don’t control what the other person does but on the contrary you control what you can achieve. So focus on yourself now. Get a life and be grateful to what you have got. The future presents infinite possibilities, so don’t question your ability to achieve it.

Quality is not an act, it is a habit – Aristotle

5. Be a student

In the IT industry, I personally feel this is again a mind game. We don’t know everything and don’t assume you know everything. Be open to learning the same subject from your peers, subordinate, leader and others.

Have an open mind and don’t close your thoughts to learn something new from what you hear. Our mind always tries to play games in comparing with what we already know and our instinct is to find faults all the time. In such situations, you are bound to miss out the smallest of learning that was possible. If you want to learn something, cut your ego and become a student again.

Final thoughts

The final thought as I sign away is – don’t be the over-thinker. As we discussed before, let the gray areas exist, don’t analyze too much, have a macro plan and execute the micro plans as they happen. Be ready for change and train your mind.

So what are your thoughts on this subject. Do you agree, disagree or have experience to share? Please feel free to drop me a note.

Continue reading...


As a Leader, it is always your fault !!! April 16th, 2013

Vinod Kumar

I have written couple of posts on Leadership and management before over the blogs. In this blog post, let me take a different dimension to leadership and how I have seen people mistakes passing the buck when things go wrong. All I have to say to them is, STAND UP and OWN IT – aren’t you the much touted leader of your team?

As I said before, Leaders is someone who owns and *leads* the way for others to follow. Whenever you see someone taking a backseat and saying you did something wrong, well you surely have an attitude to deal with.

Never say, "oops."  Always say, "Ah, interesting."

IT is your fault – IF …

  1. … your team doesn’t get along with you.
  2. … your team misses the sales target.
  3. … people always take advantage of you.
  4. … you cannot control / moderate the conversations within the team.
  5. … your teams culture needs improvement or below par.
  6. … your team is not motivated enough to work harder.
  7. … employees do what they are directed and fail to execute out-of-box.
  8. … you are not respected. This needs to be earned.
  9. … your subordinates fail to understand you and don’t listen to you.
  10. … people need to know about you in the industry being a leader.
  11. … your self-confidence is low.
  12. … you don’t believe your team can succeed.
  13. … you cannot influence the leadership team to defend your team.
  14. … people give up even before giving their 100%.
  15. … you are not there when the team needs you.

The greatest mistake you can make in life is to be continually fearing you will make one – Elbert Hubbard

Parting words

It takes courage and confidence to stand up and accept failures. No one succeeded sitting on the sidelines. So as a leader, you need to show and guide your team in reaching the desired goal. Make new paths and don’t get stereotyped in your approach by what others have done.

Final words, look into what you could have done better rather than just finding faults with people around you. You are likely to have greater control to the situation which involves you. First, accept it was your fault in first place :). Be a leader, earn your respect.

Continue reading...