Archive for May, 2012

Drop the Excuses–Be Positive May 31st, 2012

Vinod Kumar

Excuses are tools of the incompetent, and those who specialize in them seldom go far.

I have been fortunate enough to meet a lot of people and many ask me – “How do you do so many things?”, “How do you have time for this?”, “When do you learn?”, “When do you spend time with family?” etc. These are some of the typical questions and most of the times I tell them, it is about priority and most importantly it is about making time rather than giving any level of excuse. The world around us is filled with so much to do that we can actually make merry of each and every moment we have. Make sure everyone who deserve to get your time, gets that slice without fail. That is how you juggle time. Now in this post, it is not about how to manage time, it is about what are the thoughts that make you certain that you are not going to make it – the Excuses we have.

Pointers of typical Excuse

It is better to offer no excuse than a bad one – George Washington

Though these are my observations when working with people, don’t try to get crossed with yourself if you have done some of these in the past. Every day is a new beginning and it is within us to make the change happen. So here are my top 20 thoughts that confirm that you are not going to make it through –

  1. It is impossible and not realistic.
  2. I don’t have enough time.
  3. It is difficult and am not motivated enough.
  4. I am stressed out.
  5. It is never going to happen.
  6. No one believes I can do it.
  7. I can’t focus and am too lazy.
  8. I am afraid it can never work.
  9. I cant go any further, its just too much for me.
  10. I am not disciplined enough to get it done.
  11. I don’t know how to get it done, I can’t figure out.
  12. I already did everything I could.
  13. I can’t take chances and I have too much to lose.
  14. Am not good at completing tasks.
  15. I am not smart enough. I don’t have the energy.
  16. I am too scared and tired to take the plunge.
  17. I am too young or I am too old.
  18. Nothing ever goes my way.
  19. I don’t want to see me fail.
  20. I have EMI pay and responsibilities – no risks. :)

The best job goes to the person who can get it done without passing the buck or coming back with excuses – Napoleon Hill

All the above are some form of an excuse we are making up in our minds. It finally boils down to positive thinking.

All that we are is the result of what we have thought. The mind is everything. What we think we become ~Buddha

Take it Positively

It is critical for us to have a positive outlook towards life. Being realistic and balanced is the most important factor. You surely are a better judge of your limits but to stretch little beyond that limit is what sets us to next level for the potential hidden inside us. A positive attitude brings strength, energy and initiative.

Do not let circumstances influence your thoughts and moods. By rising over them mentally, you will eventually rise over them materially.

  1. Carry a positive outlook on everything that you do.
  2. Exercise and have a good posture that drives positive energy.
  3. Be thankful for the little things that make your life. Express gratitude to people.
  4. Anticipate the best and be optimistic – say positive affirmations when talking.
  5. Imagine a great future and find the silver lining that will change your life.
  6. Think of the people around you who are going through more difficult times than you are right now. This resets your mind to get positive out of the situation.
  7. Play with kids or see kids playing. Since kids live the moment and take life as it comes, it surely uplifts your mood too.
  8. Believe that you can make it and don’t leave it till the last moment.
  9. Share jokes and watch movies that make you smile. See the humor and irony in the situations around you.
  10. Practice thinking that you got lucky !!!

The difference between can and cannot are only three letters. Three letters that determine your life’s direction.


Please don’t get me wrong with the fact that you need to accept everything that comes your way. But look out for these excuses that your mind pushes and be prepared to over the same. I am a firm believer that if you can dream, it can become real. It is essential that we work positively towards it and make these dreams become reality. Remember that saying NO is not an excuse – to beat around the bush is making excuses. If you commit, be prepared to deliver and not give excuses.

Please pass me your comments and I will be more than happy to learn from your experience too.

Continue reading...


I am not a Manager, I am a Leader May 29th, 2012

Vinod Kumar

When I wrote my last post around – “Being diffident, how can you lead?” a lot of comments had come around the topic of Manager and Leader and thought it will be nice to write some of my thoughts around the same. The fundamental differences are – management focuses on getting work done on time, on budget and on target, while leadership focuses on change, vision and innovation.

Leadership begins where management ends.

For many years, management was the more inclusive term and included leadership — along with motivating, planning, communicating, organizing etc. But the concept of leadership got singled out and given a separate mention with attention. I truly value the need to motivate your employees and you need someone who has a broader vision and passion to rally around and hence a true leader is a must. The point is "Leader VS Manager" is a myth. They are not mutually exclusive. In reality people always have shades of both. Just like in history all great men MK Gandhi, Mandela were all great leaders but they had very few things in common; except that they inspired people.

The manager must lead to be productive and the leader must manage to be effective.

If someone is a good leader does not mean that the person is bad manager. You can’t just put innovation in leader’s basket, and if manager comes out with a really innovative idea then it is not a fluke. Though I am just describing some of the traits as differences, these are not the black-and-white statements.

When we are on this subject – let me also point you to the other post I wrote around – Traits seen in bad managers. That will also be a good read if you haven’t read it yet.

Leader VS Manager

How can I define in simple terms the fundamental differences? Here is my take (yet not complete) –

  1. Leaders lead people. Managers manage people. – the simple definition.
  2. The manager has an eye always on the bottom line, the leader’s eye is on the horizon.
  3. Leaders set destinations via vision. Managers navigate the roads to get there.
  4. Leaders cultivate change and creates culture. Managers cultivate creating stability – hence enacts culture.
  5. The manager does things right, the leader does the right thing.
  6. The manager acts as boss, the leader acts as coach and facilitator.
  7. Leaders appeals to the heart. Managers appeal to the head.
  8. Managers are about reaching goals while Leaders have vision to inspire.
  9. The manager relies on control on people, the leader inspires trust on people.
  10. Leaders have followers. Managers have subordinates. (BTW, twitter and FB followers don’t count you to be a leader :))
  11. Leaders take ownership. Managers take responsibility.
  12. The manager accepts the status quo for work, the leader challenges you.
  13. Leaders are proactive. Managers are reactive.
  14. The manager creates boundaries, the leader plays as if there were no boundaries.
  15. Leaders catalyze leaps of improvement, managers improve things only incrementally, by tweaking existing processes and systems.
  16. Leaders accomplish achievements. Managers accomplish compliance.
  17. Leaders engages conflicts for ideas and solutions while Managers avoid/manage conflict.
  18. Leaders are concerned what is right and managers are concerned about being right.

Leaders are ordinary human being with extraordinary qualities. These extra ordinary qualities make them leaders. Other distinction between leaders and managers is that “Leaders create hopes and dreams and engage, inspire and align managers to achieve those hopes and dreams they have set.”

Organizations need both of these traits of people to function properly. I know there are many more traits you wish and please drop me a line on your thoughts on what you feel is a great trait of an effective leader and manager.

Continue reading...


SQL Server 2012: Using SEQUENCE May 24th, 2012

Vinod Kumar

There have been multiple options of using running numbers and as developers we have used options of IDENTITY in the past. With SQL Server 2012, there is an interesting option to utilize called as SEQUENCE. I am not sure how many of you are using this new feature in your applications already. In this post wanted to quickly give you some ways to use this nice little addition into SQL Server.


The syntax for SEQUENCE can be got from the documentation mentioned above. Let us create a simple table and Sequence to start with for our experiment.


In the above script the important part is the last command where we initialize the SEQUENCE. What we created is:

Data type: BIGINT
Starts at 1 which is greater than minvalue and less than maxvalue
MaxValue is max value of the type i.e. 9223372036854775807
MinValue is min value of the type i.e. -9223372036854775808
Increment is 1 – can also be negative.
No cycle – means it will error after maxvalue
Cache is used, database engine will select a size and selection might not be consistent.

‘CREATE SEQUENCE’ does not allow specifying the database name as a prefix to the object name.

Only word of caution is wrt cache. Using of cache can minimize some IO at generation time, but at the same time if any abrupt crash or power outage can mean the cache values can be lost and there can be breakage in the sequence values.

Get the SEQUENCE Metadata from inside SQL Server. Use the sys.sequence DMV for this task.


This confirms our initial understanding of the values and how they can be queried from the system. This DMV can be used to see what SEQUENCEs have been defined inside a Database.

Generating SEQUENCE

Generating sequence numbers is a simple step using the “next value” construct.


Since we are using the GO 10, there are 10 sequence numbers that get generated.

Quick Quiz: What will be the output from the below query if I run them now.


Will the values be:

  1. 10,11
  2. 11,12
  3. 11,11
  4. 12,12
  5. None of the above

Please post the answer over comments and would love to see your explanation for the same too. For correct answers, we will send you Pluralsight Codes to watch our courses.

Let us next, alter an existing SEQUENCE number to different values:


Only catch here is: Data type can’t be changed by alter statement. And “START WITH” cannot be used in an ALTER SEQUENCE command.

Given this constrain, decide on if you want to go with smallint, int, bigint or decimals/numeric (with 0 scale). I would suggest the choice would be between int and bigint – not to forget the storage or memory needs from a cache. Given these days few MB’s is fine from an overhead when the world is moving to TB’s of data :).

Using SEQUENCE Inside Transactions

The next aspect is to use the SEQUENCE values inside a transaction and see how they get affected. Use the below status check query to keep a tap on the progress every step.


Let us start by using an implicit transaction inside SSMS and insert one value. The output of the same is:


This seems to be one of the easiest of steps. Now if we use an Explicit transaction and if we rollback, how would the SEQUENCE Status look like?


Now you can see that the values as part of INSERT have been rolled back, yet there is a small difference in the status check – the current value is 11. This confirms that the SEQUENCE has jumped to the next value and doesn’t participate as part of transaction rollback. This is the same / similar behavior to how IDENTITY works inside SQL too. Hence if we insert a new row, it would have skipped a value of 11 as we gave a increment of 10 in the last alter statement.

It is sort of different from IDENTITY because even the IDENTITY can get reset using the TRUNCATE command, but SEQUENCE has to be reinitialized using the ALTER for the same.


Getting a range of values

Now that we are talking about sequences, there have been requirements to get a range of values. The same can be achieved using a stored procedure – sp_sequence_get_range. The idea here would be an application might need some range of values it can use from the sequence generated from the backend. In case of pessimistic locking lot of people ask me is it possible to know the IDENTITY before we insert so that they can send the parent and child (with foreign key populated) in one shot into SQL Server. Here is a typical way to achieve that.


Let me add the command text used above for easy access.

DECLARE @sequence_name nvarchar(100) = ‘SequenceID’,  @range_size int = 10, 
    @range_first_value sql_variant,  @range_last_value sql_variant,
    @range_cycle_count int,  @sequence_increment sql_variant, 
    @sequence_min_value sql_variant,  @sequence_max_value sql_variant;

EXEC sp_sequence_get_range  @sequence_name = @sequence_name, @range_size = @range_size,
@range_first_value = @range_first_value output,  @range_last_value = @range_last_value output, 
@range_cycle_count = @range_cycle_count output,  @sequence_increment = @sequence_increment output, 
@sequence_min_value = @sequence_min_value output,  @sequence_max_value = @sequence_max_value output;

SELECT RangeSize= @range_size,FirstValue=@range_first_value,LastValue= @range_last_value,CycleCount=@range_cycle_count,
  Increment=@sequence_increment,MinValue= @sequence_min_value,MaxValue= @sequence_max_value;

The documentation is pretty exhaustive and can be helpful for reference– MSDN/BOL.


There are a number of ways to use SEQUENCE inside SQL Server. We have already seen how one might use the same inside a SELECT (Option 1) or INSERT Statement (Option 2) above. Let me give you some other options of how one might use SEQUENCE.

Option 3 – Pre-generation and Batching

Get the next value before inserting. If you have not taken the quiz / trivia above – do that. You will realize that the solution for that question is by doing a batch operation like one written below. Unlike in the previous case, all expressions that appear in the same logical query processing phase are evaluated as if at the same point in time hence giving it a batch window eliminates that problem.


Option 4 – OVER and Order by

This will be one of the common methods people might start using the SEQUENCEs. Your requirement is to get some sequence or order while you get the resultset and this can be yet another way to use. You can use the overall ranking functions also to get two numbered set one as a master and other as a subset ranking set. A typical example is:


Option 5 – Using inside Update statement

There can be a requirement you want to create some running sequence for an already existing values inside SQL Server as part of migrating it to SQL Server 2012. Also you don’t want any breaks in that sequence (like Invoice etc). You can use this technique to generate such running numbers and then reset / reseed if you are using the identity columns. If you get the drift then you can easily build the script using another column and achieve the same. Though I did this for one of the ISV’s keep in mind the Foreign key pitfalls as part of the activity.


Option 6 – Use it via DEFAULT Column value

This option is a great way to use in comparison to IDENTITY values. Some of the benefits of cache can surely eliminate the contention on the last page because of IDENTITY value generation. And I see this as a new way to maintain and create unique numbers inside SQL Server. In the example below, the default constraint makes sure the next value has been entered and see the entry of last 8th row automatically.


Why SEQUENCE when IDENTITY is there?

UPDATE: A lot of people are asking when to use sequence and when IDENTITY. The scenario is simple, if you need a unique sequence number to be generated across a single database then sequence can be of great help. IDENTITY can be used to generate a unique number for a given table and not across tables. That is the fundamental difference between both.

Though SEQUENCE can also be used to replace IDENTITY in the future, given the caching and faster retrieval, this is an option to explore and use. I would say, do a simple performance testing to know which one performs better. You will surely surprised :).


Your question in mind is – is it a replacement for IDENTITY? Well, it can be as one of the options. Do a small performance test to see which of these methods fair well for use before doing the replacement. I have seen acceptable performance with SEQUENCE usage on a decent workload – but you are better judge of the final results in your environments.

As time permits, feel free to pass me your comments.

Continue reading...


Being diffident, how can you lead? May 22nd, 2012

Vinod Kumar

There are tons of personal anecdotes to this topic and hence thought of sharing some of the simple thoughts that are in my mind. Are you too shy to lead? Do you feel you are an innovator, team player, strategic thinker and yet you are being introvert and ignored? Does it hurt you that this is one quality that the management has not seen of you? I know quite a lot of us who are reading this post have some feeling within our minds. We just don’t know how to overcome the same. Let me take a step back and give you a scenario that I had been into and seen people struggle.

As much as I talk about individuals, there are also characteristics that one should see as a manager in employees. In one of my previous companies, I used to see a person be very silent in her tasks, work etc. In team meetings, she would be silent and when it comes to giving suggestions – she would be a silent sitting duck but might talk to the peer (good friend) for a brief moment. Suddenly in few moments you will see the other person giving some fantastic suggestions and most importantly taking the limelight based on the idea of someone else. Our management saw these happen but even though someone is truly talented in the team – they asked “Can this person become the team lead?”. At this juncture, this is a question more than a decision. This is fundamentally because you failed to open up in front of your own team.

My questions to you, did this person loose out on a potential career of being a great team lead? Is her promotion getting hampered with her behavior? I would argue – NO. Leaders are not born, they are made.

Leadership: the art of getting someone else to do something you want done because he wants to do it. – Dwight D. Eisenhower

For many of you who have known me for years think I am a natural speaker – that is a wrong. I have trained myself to be like this from my college 2nd year. I used to sweat to get up on stage, now you will argue there is no sign of it atleast today after close to 15 years of practice :).

Environment makes who you are

I wanted to do some research on this topic to see if there is any pattern. Interestingly, I found this nice paper done from the University of Wisconsin. Here the University picked some 77 toddlers from close to 368 pool and tried to monitor them for any behavior patterns, gene pattern to conclude if any of these affected the shyness, impulsive etc sort of behavior. The conclusion is interesting – the kids kept changing between shy to impulsive to extrovert to introvert as they kept transitioning from 3-9 years.

Hence this confirmed the fact that once the parents exposed the kids to explore taking calculated risks like getting new playmate, new schools, new ways to explore etc – the kid may feel a little afraid at first, but if he/she go ahead and engage despite the fear, they have a great time and learning. This took away the shyness in the individual and they start to move freely with all.

I suppose leadership at one time meant muscles; but today it means getting along with people. – Mohandas K. Gandhi

I think this experiment even with adults will yield the same result. Many of us out there don’t change the job because of the apprehensions of the new place, new job, new challenges, new friends and more. And we don’t open up at a new place easily and it is something we need to train consciously. How many of us walk around to find people and make friends in a party? That is the current acid test for you to asses yourself.

Simple steps

Someone a bit timid in groups at work can overcome their shyness with sustained effort. It has to be a conscious effort and you need to keep working on it till it becomes your second nature and natural. Let me give you a 5 step simple approach to this task in hand.

  1. Are you serious about this, do you really want to work on it? Then the first step is to get motivated in taking that unknown step forward. Be prepared mentally.
  2. Ask yourself, Where do I need to improve? No one out there is perfect, there is always scope for improvement. Find your mentor / trusted person who is there to give you candid feedbacks for you to improve. This person must know you for a while and must know your strengths and weakness and open on giving these feedbacks. Listen and give it a genuine try.
  3. After the feedbacks, think about the feed-forward. What should be the top 2 things that you need to start doing. It is like giving an exam, make a plan and some milestones for yourself. You are the best judge of your actions and you need to assess your progress from time to time. Keep a realistic and measurable goal.
  4. Find a partner in crime, this is someone other than your mentor. This person can help, guide and rehash if you are not able to articulate or make the point. And in every situation, think through what kept you quiet and what you might do next time to have the confidence to speak up.
  5. Finally, make merry of every opportunity you have that comes your way at work or outside work to make sure you break your shell.

Doing these you are just rewiring your brain from the instinct of just “being quite”. You will open new opportunities and you will become a braver soul. There is nothing wrong in airing your opinion, even if it were wrong. Atleast it is giving you an opportunity to learn.

If your actions inspire others to dream more, learn more, do more and become more, you are a leader. – John Quincy Adams

Final words

Do tell me if you plan to try any of these methods. See if you can be the person who oozes self-confidence and positive energy. Speak up, stand tall and be confident. I would love to get your feedbacks if this worked for you.

Continue reading...


Upgrading to SQL Server 2012 May 17th, 2012

Vinod Kumar

As new versions of software get released, companies and products also get into this natural transition of upgrading and supporting the latest and greatest. And in the same space, SQL Server is no exception -almost as an predictable cycle, every 3 years we can expect a version of SQL Server getting released. This has been a trend since the SQL Server 2005 version for sure. As part of my job in meeting customers, I have to give recommendations and best practices to move to the latest versions. There are so many nuances to keep in mind as we make a successful upgrade. Let me take a moment to give you some of the suggestions I give to customers. Hope these will be helpful for you too.

Bible to start

You need to always start with preparation phase. This phase involves multiple steps and here are some of them for you to start.

  1. Keep the SQL Server 2012 Upgrade Technical Guide handy with you always. This is a very long document to read but it is worth the reference. I am outlining this as the FIRST reference because I will refrain from what this document covers as content. It is exhaustive and as I said with ~450 pages has a lot of material on how to upgrade. Also for reference SQL Server 2005 to 2008 Upgrade Whitepaper.
  2. If you still want to know WHY UPGRADE? I am attaching a link to some of the new features document for your reference. Knowing the new features is an important step before you upgrade. Later, you must be looking at how you can start maximizing the new features introduced with the new version.

Just upgrading by keeping the compatibility level at a older version is not real upgrade. Evaluate at using some of the new features to increase productivity, performance, maintainability and scalability.

As management takes the decisions for upgrade, DBA’s and developers need to also get themselves set for this task. Any upgrade process cannot be a simple overnight activity but more of a calculated informed step as line-of-business applications are at stake and it can bring down the efficiency of the teams working inside the organizations.

  1. Take a BACKUP of your databases. This is the first step irrespective of what you do next. A backup is required to make sure we have a fallback mechanism if things go wrong. Also make sure if you are using any specific system databases and objects in those DB, these are appropriately backed-up or scripted out.
    1. Take FULL Backups
    2. Transaction Log Backups
    3. Backup Database Master Keys
    4. Backup Service Master Keys
    5. Backup any external resource used (CLR DLLs, Extended SP) and anything that is required for the application outside the database for proper functioning.
  2. Before the backup process, just run a DBCC CHECKDB across the databases to make sure there is no corruption before the backup and they are consistent. This step can reduce any heartburns at a later point in time post upgrade :).
  3. As the upgrade talks happens, get started with tracking if any deprecated features are being used. There are multiple ways to do this – I highly recommend using the “SQL Server: Deprecated Features” Perfmon counter to track it like a background task. As your application runs, this gives you a rough idea of how many deprecated features you might be using inside your application that might have an impact to your upgrade process. There is also a Trace Event for capturing Deprecated features with SQL Server Profiler which you can use but that will be to nail down which code is causing that perfmon spike.
  4. Before initializing the upgrade process, please start by running the Upgrade Advisor as a first step. This surely guides you of potential problems upfront and is one of the essential step too for any SQL Server upgrade process.
  5. Keep track of the Supported Versions and Editions as you plan for Upgrades. With changes in licensing plans, LPIM (lock pages in memory), AWE gone etc. It is critical to keep track of how we upgrade between environments.
  6. Take a typical workload using profiler and keep it handy for a later test. Create a separate environment, bring the databases via a backup / restore operation to the new edition. Look for warnings, errors, changes in functionality and performance bottlenecks. Make sure to replay the workload collected before to see if anything unusual behaviors or errors. Document and then make sure these are mitigates during the actual run.
  7. Also, review the KB article that talks about “How to move databases between computers running SQL Server”. Especially, when we move between servers during upgrades we might need to change the location of files and hence have to account for the same. Also on a new setup, make sure the drivers for SAN or other hardware components are done properly and are the latest as suggested by the vendor.
    1. There are interesting ways to install as we move forward with SQL Server 2012 like, you can install SQL Server with SMB Fileshares as storage options.
    2. Another, is to install it on a Windows Server Core. This is interesting because now you don’t have an UI to manage SQL Server and it also reduces the surface area of attack and patch management is easy too.
    3. Considerations for SysPrep install for SQL Server – read documentation.
  8. Also, since this is an upgrade to a new box, make sure you are having enough space allocated for atleast 2-3 years of growth in mind for the database locations. Now is a good time for you to reassess some of the values defined for the auto-growth settings. Ideally, I suggest never allow SQL Server to grow but you pre-allocate it as part of your administration window cycle by properly monitoring SQL Server Data file free space.
  9. Before you install the Database on a new server, make a note of the Collation requirements for your applications. This is an important step and you don’t want get into a server which is Case-Sensitive while the application is built assuming case-insensitive.
  10. Don’t forget some of the critical addendum functions that you might have configured like replication, mirroring, clustering, service broker, maintenance plans, local user accounts, SQL Server Logins, service accounts, Linked servers, common backup path locations, configuration settings, DR strategies etc. All these need to be accounted and have to be changed accordingly.
  11. Whatever step you take, document them meticulously and run the document through a junior tester on a fresh SQL Server environment. This will test your upgrade process documentation and at the same time make sure that you are not missing any step to avoid last minute glitches. Document every single error encountered and the solution for the same – it is better to be prepared than to fail not being prepared.
  12. When I call out documentation, look out for hidden gems in your process like – Startup Processes, SQL Server Trace Flags that were enabled, Error Log settings, External DLL / CLR assemblies used, powershell scripts, any application Windows services that have been installed, external third party backup tools etc. Also make sure the server is secure by default and make sure any unnecessary services (SQL Browser, IIS, virus scanners, spooler etc) if running have been stopped.
  13. If you are planning to move into Virtualized environment, know what does it take to run SQL Server in Hyper-V Environment (though little outdated, is a worthy read). Also, read about running SQL Server with Dynamic Memory in HyperV Environments. I saw a session video in NA TechEd on this very topic which can be a good watch: Microsoft SQL Server Consolidation and Virtualization: Myths and Realities.
  14. More documents worthy for reading on the Virtualization subject are: Hyper-V is the Best Virtualization Solution for SQL Server, High Performance SQL Server Workloads on Hyper-V, Planning, Implementing, and Supporting SQL Server Virtualization with Windows Server 2008 R2 Hyper-V and Live Migration and Support Policy for SQL Server Virtualization.
  15. What are your plans for upgrade: in-place upgrade, side-by-side on the same box or side-by-side on a different box? Irrespective of the method used Keep in mind Point 1 :).
  16. Have a small test-stub workload where you are aware of the parameters and the expected results from the server. Make sure you run this pre and then post the upgrade and debug any anomalies based on the output. If you have automated test scripts, these are the best to be run against the server for a quick test with expected values.
  17. Have a failsafe strategy if things don’t go as per the plan. Your backup can come handy here and be careful especially if you plan to do a in-place upgrade as there is not much choice to come back other than using the backup route.

Know the fine prints

Please make sure you are aware of the specific call-outs mentioned in the Upgrade Technical reference mentioned above. During upgrade there can be specific features or settings that might need special attention. I am calling out some of them as I have seen at multiple customer upgrade process.

  1. The default setting for max worker threads in SQL Server 2000 is 255. When you upgrade from an instance of SQL Server 2000, the database engine keeps setting the value of max worker threads. However, it is recommended you change the value of max worker threads to 0 before the migration, to allow the Database Engine calculates the optimal number of threads.
  2. Changing the Compatibility Level while users are connected to the database can generate incorrect results for sets of active queries. To avoid inaccurate results, it is recommended to change the Compatibility Level with the database in order to a SINGLE_USER mode and then back to MULTI_USER. Do this post the upgrade activity.
  3. Look at server side settings from the older version, as you plan to move to new hardware’s some of these old values doesn’t make sense. Keep special note to MAXDOP settings, number of TempDB files etc.
  4. Just like the recommendation on Server Core, I highly recommend not to install any new components that you may not be planning to use in the immediate future like – Data Quality Services, PowerPivot, Master Data Services, Analysis Services etc.
  5. Look at the Security considerations and the protocols used for communication as part of upgrade. Each new version there are some changes and analyze the ports you have as standards open at the new environment. Security considerations during Install is documented and worth reading for every version upgrade.
  6. In earlier versions of SQL Server, the values ​​for the table and index row counts and page may be incorrect. Thus, databases that were created in versions prior to SQL Server 2005 may contain incorrect counts. After you upgrade a database from SQL Server 2000, it is recommended that you perform the DBCC UPDATEUSAGE to correct any invalid counts. This DBCC statement corrects the count of rows, used pages, reserved pages, leaf pages and data for each partition in a table or index.
  7. Update statistics for all Indexes after the Upgrade process. This is one of the most important step. Keep in mind also the Plan guides and hints that you have used in your application. Test these sections for any potential performance problems. Given the upgrade, SQL Server engine would have also changed and hence testing them matters. In case of Full-text catalogs, make sure these are also updated.
  8. As a sanity check, make sure to run a DBCC CHECKDB on each of the databases which have been part of the upgrade process.
  9. Over and above this, I also recommend checking each of the database properties which are critical. Check the recovery models, Auto-Close, Auto-Create-Statistics, Auto-Shrink, Auto-Update-Statistics and Auto-Update-statistics-Asynchronously. Also in this list, I highly recommend setting value of CHECKSUM for the Page Verify property at the DB properties level.
  10. Know and document the changes that need to be done on the application to start pointing to the new upgraded server. This might sometimes involve Connection String changes in Web Server deployment. Also, track if any reporting server and Sharepoint installations are pointing to the old servers as data sources. Even these need to be changed as part of movement.
  11. After all this, don’t forget to take a Backup of your system. You don’t want to do all the hard work and find out the HDD crashed the next day :). Backup and secure on a different media and location.

I think this very much sums-up what I had in mind based on various implementations that I have seen. These steps are very much towards migration of the SQL Server Relational engine but doesn’t talk about the SSAS, SSIS, SSRS workloads. I would again reiterate to look at the Upgrade Reference Guide mentioned above as a good guide to start.

A successful upgrade uses a cycle of: Plan, Document process, Test, Refine process, Test, Plan upgrade window, execute, verify upgrade and then opens for business.

Now that we have upgraded into the next version of SQL Server, now start looking actively at how we can start using the new capabilities available inside the next new release. I am just calling out some of them that are worth mentioning here for SQL Server 2012 release:

SQL Server: AlwaysOn Technologies

xVelocity in-memory technologies of PowerView and PowerPivot

BI Sematic Model

File Tables

SQL Server 2012: Express LocalDB

SQL Server ColumnStore Index

Data Quality Services

Security Enhancements – Contained Databases

Big Data Analytics and Hadoop Connectors

Microsoft SQL Server JDBC Driver 3.0

Microsoft Drivers 3.0 for SQL Server for PHP

Links worth keeping track as you upgrade:

Deprecated features:

Discontinued features:

Breaking changes in 2012:

Behavior changes in 2012:

Full-Text Search Backward Compatibility in SQL 2012:

Hope these links and resources will be of help for you as you plan your upgrade and movement to yet another version of SQL Server. I would love to get your inputs and understand if there are any glaring steps that I would have missed mentioning as part of the checklist mentioned above.

Continue reading...