Posts Tagged ‘Management’

SQL Server AlwaysOn–Connection Strings October 7th, 2013

Vinod Kumar

In my interactions with customers, a lot of them are trying to move from SQL Server 2008 / R2 to SQL Server 2012 and they keep asking what is the changes one need to do when it comes to utilizing SQL Server 2012 AlwaysOn from an application point of view. I always say it is as simple as making a connection string change. We have extensively called this out in our book (Paperback, Kindle) under Chapter 8, “Connection Strings Parameters” section.

AlwaysOn uses listener to access DBs, connection string is as following:

Server=AGListener; Database=AGTestDB; Integrated Security=SSPI

For connecting to readable secondary replica a new keyword gets added: ApplicationIntent and Read-Only like:

Server=tcp:AGListener,1433; Database=Db1; Integrated Security=SSPI; ApplicationIntent=ReadOnly;

Some of the interesting points we discussed as part of our book on this topic are:

  1. To use ApplicationIntent connection string feature we need to update .NET Framework to 4.0.2.
  2. We can still use DB mirroring connect string to connect AlwaysOn group DBs, there is no need to create AG listener.

The primary reason is to offload your secondary read-only workloads from your primary replica, which conserves its resources for your mission critical workloads. If you have mission critical read-workload or the workload that cannot tolerate latency, you should run it on the primary.

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...


 

Picture Post: Competition Perception September 26th, 2013

Vinod Kumar

If you want to find the real competition, just look in the mirror. After a while you’ll see your rivals scrambling for second place ― Criss Jami

In this world of enterprise organizations, every single person seems to have a competition of a different kind. Competition is good, but if you are always thinking what competition is going to do to you – well you are starting on a wrong foot. Competition is out there to keep your foot in the ground. If you get carried away and close your eyes to what happens around you – you are surely going to be grounded for ever.

Never compete with someone who has nothing to lose ― Baltasar Gracián

Competition brings out the best in products and the worst in people ― David Sarnoff

I am not sure how many can understand this concept. If you ever got a chance to watch the movie Cars, you will get the picture I show above. It is always not about winning, it is about knowing the strengths of your competition, it is about playing to your strengths, it is about planning a strategy to your execution and not dancing to the tune of what the competition does.

While most are dreaming of success, winners wake-up and work hard to achieve it.

Do let me know if this makes sense. Are there other anecdotes that comes to your mind and if possible can you share some?

Continue reading...


 

SQL Server AlwaysOn – RPO and RTO discussion September 25th, 2013

Vinod Kumar

Every single day I meet a number of customers who want to implement SQL Server AlwaysOn as part of their migration to SQL Server 2012. Though most of the discussions and architecture are easy to understand, most of the DBA’s ask a simple yet important question of what is the latency for Synchronous replica’s? What is the RTO and RPO for secondary servers? We have explained the same in Chapter 1 of our book. Let me bring out some of these concepts that I discuss in this blog for your reference.

Recovery Point Objective (RPO)

Recovery Point Objective (RPO) is defined as the amount of acceptable data loss or the point in time up to which the data can be recovered. Important metrics that can be used to monitor RPO when working with SQL Server AlwaysOn are:

The log send queue size at any point will give us an indication approximately how much log has not been sent in KB. This is the amount of log secondary doesn’t have at the time of failover and the amount of data loss customers will experience.

sys.dm_hadr_database_replica_states.last_commit_time column in DMV on both primary and secondary and look at the time difference. On the secondary the time will be the same as on the primary. The secondary rows on the primary will show the time reported back from each secondary. The difference of the time between the primary row and secondary row approximately represents the RPO assuming the redo is caught up and the progress is reported back.

Note: If there are more than one secondary replica, when making the decision to failover, analyze these values for all secondary replicas, as failing over to a secondary replica with more favorable last commit time may minimize data loss.

Recovery Time Objective (RTO)

Recovery Time Objective (RTO) is defined as the time taken to restore normal operations after a failure or planned failover. Important metrics for determining RTO when working with SQL Server AlwaysOn is:

The redo queue size divided by the redo rate should give the DBA some understanding of what this overhead time is before the database will be online and available for the primary role. So, Redo Catch up time = redo_queue_size(KB)\redo_rate (KB\sec). The values are reported in sys.dm_hadr_database_replica_states.

Note: If there are more than one secondary replica, when making the decision to failover, analyze these values for all secondary replicas, as failing over to a secondary replica with more favorable last commit time may minimize downtime.

This blog 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).

Would love to also learn from you how you design and work on your RTO and RPO in your production environments with SQL Server AlwaysOn.

Continue reading...


 

SQLCMD Command line options September 18th, 2013

Vinod Kumar

Recently I was reading the blog from Pinal around Execute Operating System Commands in sqlcmd and was wondering do we know all the command line parameters available with SQLCMD? Personally for me this is a very handy utility and I have used it in the past like anything.

The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt. This utility replaces the osql command-line tool with many scripting enhancements. Some of the common command-line options are:

Option

Description

-A

Logs in to SQL Server with a Dedicated Administrator Connection (DAC).

-d

Issues a USE  db_name statement when you start sqlcmd. This option sets the sqlcmd scripting variable SQLCMDDBNAME.

-E

Uses a trusted connection instead of using a user name and password to log on to SQL Server.

-i

Identifies the file that contains a batch of SQL statements or stored procedures. Multiple files may be specified that will be read and processed in order.

-N

This switch is used by the client to request an encrypted connection.

-o

Identifies the file that receives output from sqlcmd.

-P

Is a user-specified password. Passwords are case sensitive.

-q "cmd"

Executes a query when sqlcmd starts, but does not exit sqlcmd when the query has finished running. Multiple-semicolon-delimited queries can be executed.

-Q

Executes a query when sqlcmd starts and then immediately exits sqlcmd.

-t

Value will be used to give a query timeout value.

-U

Is the user login ID. If neither the -U option nor the -P option is specified, sqlcmd tries to connect by using Windows Authentication mode.

-Z

Changes Password and exits sqlcmd

This is not an exhaustive list of parameters that you can find but these are the most commonly used.

As I wrap up, wanted to call out one specific parameter which is –K with sqlcmd. When I was writing the book on AlwaysOn Availability Groups” (Paperback, Kindle), we wanted to show quickly how client redirection works with ApplicationIntent=ReadOnly and this handy parameter helps you achieve the same from sqlcmd.

Continue reading...


 

Life is about stopping September 11th, 2013

Vinod Kumar

I don’t regret the things I’ve done, I regret the things I didn’t do when I had the chance.

Earlier this week I was writing about happiness and some of the thoughts around that. In order to have a happy life, it is strange that we need to change a lot of things in our own self. This blog post is about calling out some of the things we need to STOP doing. Here is a quick list, not exhaustive though.

Don’t be afraid to stand for what you believe in, even if that means standing alone.

  1. Stop running from your problems
  2. Stop thinking you’re not ready
  3. Stop living in the past
  4. Stop being negative in your thoughts
  5. Stop trying to hold onto the past
  6. Stop lying to yourself
  7. Stop being idle
  8. Stop procrastinating
  9. Stop yourself from being distracted
  10. Stop spending time with wrong people
  11. Stop fear of failure
  12. Stop doubting
  13. Stop trying to be someone else
  14. Stop being scared to make mistakes
  15. Stop cursing yourself for old mistakes – move on
  16. Stop doing the same thing while expecting different results
  17. Stop making excuses
  18. Stop from saying “Yes” always
  19. Stop your anger before it becomes a danger
  20. Stop trying to buy happiness
  21. Stop expecting people to be perfect
  22. Stop exclusively looking to others for happiness
  23. Stop trying to compete against everyone else
  24. Stop being jealous of others
  25. Stop blaming yourself for doing the right thing
  26. Stop buying things which you will throw anyways
  27. Stop overloading your schedule
  28. Stop holding grudges
  29. Stop being defensive
  30. Stop being materialistic
  31. Stop holding back your reason to praise someone
  32. Stop letting others bring you down to their level
  33. Stop wasting time explaining yourself to others
  34. Stop trying to impress others
  35. Stop being lazy
  36. Stop getting away with “good enough” attitude
  37. Stop overlooking the beauty of small moments
  38. Stop trying to make things perfect
  39. Stop following the path of least resistance
  40. Stop spending mindlessly
  41. Stop blaming others for your troubles
  42. Stop worrying so much
  43. Stop focusing on what you don’t want to happen
  44. Stop being ungrateful

Being happy doesn’t mean that everything is perfect. it means that you’ve decided to look beyond the imperfections.

I am sure you will agree with some of these if not all. Do let me know if you agree with the above. What would be your top 3 which you liked and found striking?

Life is short, live it. Love is rare, grab it. Anger is bad, dump it. Fear is awful, face it. Memories are sweet, cherish it.

Continue reading...