Posts Tagged ‘Management’

Managed Databases on Cloud July 18th, 2014

Vinod Kumar

Recently my good friend and colleague Govind wrote about this topic on what are customers looking forward to when it comes to Cloud and working with Azure. The fundamental tenants that customers look at for cloud be it PaaS, SaaS or IaaS has been around:

  1. Reduced Maintenance headaches
  2. SLA backed for HA/DR
  3. Performance
  4. Synchronization with on-prem
  5. Security
  6. Backups
  7. No worry about hardware

and a few more. But for most parts the above fits the quizzing we get into. In a recent conversation, I had to outline some of the options when it comes to backup requirements with the customer which I thought is worth a share here. I am looking at this from an Azure standpoint:

For IaaS:

  1. You will need to use SQL Server Agent and build your maintenance plans that can automated. This can be scripted (powershell, TSQL or others) and done for all workloads.
  2. For SQL Server 2008 R2 CU2 onwards, we can use Backup to URL option wherein backups from Azure VM – SQL box we can point backups to a blob storage. I wrote about this a while back and you can try the same –
  3. SQL Server 2014 also supports Encrypted backups to Blob and the same article shows the same.
  4. Also from SQL Server 2014 we have option to use Managed Automated backups configured. This will take backups automatically to  blob on a predefined time or based on workload pattern. Documentation for this can be found at:

For PaaS:

  1. Since we already make sure of consistency in the Azure world, we dont have to worry on this.
  2. For Basic, Standard and Premium editions there are SLA for Point-In-Time recovery which is 7, 14 and 35 days respectively. You can read more about this at: . I highly recommend to use Powershell scripts to automate this, if you plan to use the them.
  3. In the past, I have also seen customers use Database Copy functionality to keep a copy of their database in a ready to use state every couple of days. This gives them an opportunity to go back to that version immediately without any problems. This is also an viable option if you like to use. – Since point-in-time restores are available, I am more inclined to use that for cold standby and restores. Having said that, we can still use that feature for creating a copy for Dev, Test environments from our prod servers for testing.

These are my customer notes and I plan to start publishing these customer notes from time to time here in my blog. Since we are talking about Azure, I am sure some more additional capabilities and SLA’s can change over a period of time. So please keep an eye on the documentation for the latest values.

Continue reading...


Excel Tip: Month Name Sorting in Excel July 14th, 2014

Vinod Kumar

In my previous article Excel Tip: Month Name Sorting with PowerPivot / PowerView, I wrote about sorting of month name inside PowerPivot. I got a number of ping about the same functionality inside standard Excel sheets. I thought this would be the easiest and known to many – but to my surprise, not all know about the capability of Excel. So in this post, let me take you through the same process for Excel tables.

So let me prep you with the data first. I have two columns Month Name and Sales. When I try to sort by the Month Name, you can see how the sorting happens for A-Z and for Z-A. It is nowhere near to what one would expect to sort as month.

That bring us to an interesting option. It has been there all along, select the “Sort by Color” –> “Custom Sort…”.

This comes up with a small dialog which I am sure most of you would have used. There right at the order dropdown, is a hidden gem called as “Custom List…”. Select this to see the magic.

This bring a standard set of list or feel free to define your own New List for sorting and build the table.

Click on “OK” and see. This is awesome because now the list is sorted automatically back in your table. Now if we sort A-Z and Z-A to see the difference. So how easy and cool is it? Have you ever used this option before, let me know.

Continue reading...


Things that Cellphones changed … July 1st, 2014

Vinod Kumar

The era of cellphones and smartphones have hit us by storm for sure. Each manufacturer is surely trying to put something more every time with every release that they make. Having said that, I come from an era where I feel these cell phones have gone lengths to be more than just a calling machine. In this blog post let me call out my top 10 things we lost at our homes with the advent of cellphones/smartphones.

1 & 2: Watches and Alarm clocks


What can I say here. This I am sure a lot of you can relate to. Nowadays time is never seen using wrist watches but using the phones more often. I don’t recollect when was the last time you even bought a watch? Do you?

3. Calculators

From Minimal to scientific / advanced calculation, one can use their phones to perform all calculations and this nice little gadget is slowing getting replaced in front of our eyes.

I can say with confidence that my engineering was never complete without that Casio Black Calculator. Do you have one at home still?

4. Memorizing Phone Numbers

This is also no brainer – When was the last time you dialled a number of your cousin, aunt or grandparents without consulting your phones? The days of smart phones are to make you less smarter :).

Today, I see ads saying you must teach your children your phone numbers etc. Remembering numbers was always fun and it sharpened ones memory power in my opinion.

5. Encyclopedias

My childhood was filled with these books and there used to be a refresh that I got every other year.

Encyclopedia and Dictionary were household items which are getting replaced by search engines and Wikipedias. And with smartphones, all this at your finger tips.


6. Reading books

I was not an avid reader in my childhood days, but caught onto it very late. Having said that, I love the smell and the feel of reading printed books quite a lot. Even today I order a number of books every other month.

With the concepts of eReaders and larger screen sizes, now smartphones seem to take that spot too. Reading on the go !!!



7. Selfie or Celfie’s

I don’t need to explain much here. Gone are the days where on used to carry a still-camera to take pictures. I do have one at home to show my daughter once she grows up that we used to take pictures using that :).

8. Navigating using maps

It used to be fun to go around the city using a piece of paper and I for sure used to love this experience. I find it difficult for people to now tell directions without the help of their GPS devices – a.k.a phones. More so that they even cant tell which side is East or West :)

9. Fountain Pens

I have always loved the personalization of a hand writing brings. It showed a lot of care for the other person.

Today one can access Facebook on the go and the days of sending a personal note to your friend via the traditional cards is rare.

I am still from an old school, but I love this personalization even today.


10. Distracted life

I saved the best for the last, our morning walks are distracted, our driving are getting distracted, our lunches are distracted, we cant just sit calmly at airport and watch enjoy the weather. Our smartphones have also taken over our life where every minute is watching something on our phones or playing a game.

As I sign off, wanted to share this video– it is about social life. I personally thought this was worth a share.

Continue reading...


Excel Tip: Month Name Sorting with PowerPivot / PowerView June 25th, 2014

Vinod Kumar

I do a lot of presentation on Excel related topics to customers as part of my BI conversations at MTC, Bangalore. Recently one of the developer when looking at the PowerView demo said, he had an unique problem. He said that the PowerView was not ordering the month names in the correct order of Jan, Feb, Mar and so on but was sorting based on Alphabets. I repeatedly asked him if they had enabled the proper sort order. He was little clueless and wanted to see it in action. Here is the 30 seconds demo that I showed how this can be achieved.

In our PowerPivot dataset we have the following. It is a list of MonthNames and sales marked against them.

When I use this dataset and create a PowerView report, I select MonthName and Sales. This comes up with an report like this irrespective of the sort I do. This is the problem that the customer was mentioning.

The solution is simple. Get back to PowerPivot Tab, under Home –> Sort by Column –> Select “Sort by Column”.

We will be presented by the following dialog. Here I am mentioning, whenever the “Month Name” column is selected, use the “Month” integer column to sort behind the scenes. That is it !!

One the above step is complete, our PowerView will show a dialog of change in Data Model, select OK.

And the change can be seen now. The months seems to be in correct order.

Have you encountered this requirement in your report generation anytime? Do let me know.

Continue reading...


SQL Server: Columnstore Index on Temp Tables June 20th, 2014

Vinod Kumar

Recently I was talking to a customer during performance testing and they had an interesting twist to using Columnstore Index. Since they were on SQL Server 2012, I had suggested them to use Non-Clustered Columnstore index for their workload. He started to quiz me with a number of questions and I thought it would be appropriate to point them to some resources I had already written. So I took the liberty to send them the two articles:

  1. SQL Server 2012: ColumnStore Characteristics
  2. SQL Server 2012: ColumnStore Part II

The fundamentals and concepts have not changed but where worth a mention and read. Having said that, the counter question after about an hour of their research and reading the contents. “Will it work with Temp Tables? We are doing a reporting workload and are using Temp tables extensively.”

I thought, wow, interestingly I have not written on it till date and is worth a mention though. The easy answer is “YES”, it is quite possible. Why didnt you try was my answer :)

Columnstore Index on Temp Tables

To drive home the concept, in the below example I have created a simple temp table called as #Temp_Account. When I query the table, the execution plan shows as “RowStore” under storage as shown below.

The next logical step is to create our Non-Clustered Columnstore Index. I am using the standard syntax as shown below.

If we try to insert into this temp table, it is still readonly because non-clustered columnstore index has been defined in the table.

Msg 35330, Level 15, State 1, Line 19
INSERT statement failed because data cannot be updated in a table that has a nonclustered columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, and then rebuilding the columnstore index after INSERT has completed.

After index creation, if we make a simple query we can see the change in Execution Plan. The storage is now showing as “ColumnStore” as shown in the execution plan below.

The above query is simple in nature and hence the execution mode is “Row”. In future posts I will show how this can be changed to Batch mode.

Continue reading...