Archive for the ‘Technology’ Category

Excel Tip: Undo Shortcuts October 8th, 2013

Vinod Kumar

One of the most used productivity software is Excel. It has always been that way if you are a Data person. Whenever I talk with customers who use reporting or are part of finance operations, their first choice for data manipulation defaults to Excel. There is familiarity of use, predictable and known UI, ease of formula use and many more. So in this blog, let me try to see how much we know of our tool that we use day-in-day-out a.k.a Excel. I have been writing about various Excel tips over my blog and a lot of times people ask some interesting trivia’s that I get to learn from. This post here is inspired by one such question that did come my way and I am happy to share with you.

If I were to ask – “What is the most commonly used shortcut when you are working with Office?”. What is your answer to this question? There are a number of shortcuts, but the survival of all the computer professionals will go for a toss if today’s keyboards missed “CTRL+C” and “CTRL+V”. This is one such shortcut without which our kid’s homework will be complete, our college assignments would become incomplete, and the code that we write would be incomplete and so on. Hands-down this is one of the most commonly used shortcut we would have used in almost any Office product suite – let alone Excel. Lesser known are thousands more shortcuts like these that we can use. In exploration to one such shortcut is our quiz used in later part of this post.

Undo Functionality

So if that was not the quiz, what is the quiz or tip all about anyways? The question now to you is, what are the ways you Undo the operations that you have done inside Excel. There are multiple answers:

Option 1: CTRL+Z

The most widely used shortcut to undo the series of operations is the CTRL+Z key. So we have given you one of the common answers.

Option 2: Using Quick Access Toolbar

This is one of those hidden or must say the second most used method of performing the Undo operation. Very powerful because the dropdown in that arrow shows a history and we can undo to a point in time of our document. This is more than powerful than you think.

Quiz Question

The quiz question is simple. Can you get me a 3rd Option to do the Undo operation inside Excel? This needs to be repeatable just like our CTRL+Z and I must be able to use this shortcut / operation / click and achieve the same result?

Continue reading...


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


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:




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


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


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


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.


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


Identifies the file that receives output from sqlcmd.


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.


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


Value will be used to give a query timeout value.


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.


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


Find information about SQL Server 2012 AlwaysOn FCI September 4th, 2013

Vinod Kumar

When we were writing our AlwaysOn book, there were so much more to write that we couldn’t add a lot of content which we wished. Having said that, it still made to a 500+ pages which was something we didn’t realize. As a DBA, you need to administer several SQL Server 2012 instances at a given point in time. Before you can start your administration of SQL Server instances, I highly recommend we be clear on certain things:

  1. How many nodes are there in the system?
  2. Are these SQL Server instances Clustered currently?
  3. Which is the current active node the instance is currently running?
  4. Who are the active or possible owners available?
  5. Which are the shared disks we are accessing?
  6. The various settings for SQL Server Cluster resource? etc

Even though these look simple, ask your DBA and you will be surprised with their expression :) – trust me. In this blog post, let me call out some of the important information we need to collect ahead of time. This is not exhaustive, but I am sure this gives you a good start.

First connect to SQL Server Management Studio, connect to Server and open a Query window. Make sure you document these in each of your environments.

1. Version and Edition


SELECT SERVERPROPERTY(‘ProductVersion’) AS ProductVersion,

SERVERPROPERTY(‘ProductLevel’) AS ProductLevel,

SERVERPROPERTY(‘Edition’) AS Edition

2. Find SQL Server Instance is Clustered


We will get a value of 0 if the instance is not clustered, else we will get 1.

3. Which instance are we actively running

SELECT SERVERPROPERTY(‘ComputerNamePhysicalNetbios’)

4. Which nodes instance is running

SELECT NodeName, Status, status_description, is_current_owner

FROM sys.dm_os_cluster_nodes;

5. How to identify Shared Disks the instance can access

SELECT DriveName FROM sys.dm_io_cluster_shared_drives;

6. Current SQL Server Cluster resource settings

SELECT VerboseLogging,

SqlDumperDumpTimeOut, FailureConditionLevel, HealthCheckTimeout

FROM sys.dm_os_cluster_properties;

Some of these properties are used to set the values that affect failure detection, failure response times etc. Knowing our environment is important and as a professional DBA, it is critical for us to have the datapoints handy.

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) is now available.

Continue reading...