SQL Server 2008 R2 on production -- is it worth to upgrade to SP1/SP2 One of my company's mission-critical database is running SQL Server 2008 R2. SELECT serverproperty('ProductVersion') returns 10.50.1600.1. So, yeah, RTM.

Now, long story short, this database is not yet migrated to our SAN Storage, so we can't have easy snapshot of the system. If we want to make a reversible change, we'll have to backup the database's instance willy-nilly. And of course, this might need some downtime/service interruption, and is thus a hassle.

Currently there seems to be no significant problems, except some slowdowns which we attribute to suboptimal queries. And our Dev/DBA sorely misses OPTION (RECOMPILE), which Microsoft took out from SQL Server 2008 R2 RTM.

So, I would like to know:


Upgrading the server to SP1 or SP2: will it be worth the hassle?
I have been searching for something clear-cut on the benefits of SP1 over RTM, or SP2 over SP1 over RTM.
Should we decide to apply SP1/SP2, will there be critical hotfixes to apply afterwards?
(E.g., something similar to the regression with SQL Server 2012 SP2 could lead to data loss).


Thanks for any help/explanation.
1.Upgrading the server to SP1 or SP2: will it be worth the hassle?
  I have been searching for something clear-cut on the benefits of SP1 over RTM, or SP2 over SP1 over RTM


Yes very much and believe me its not a hassle it will save you from lot of hassles. As already pointed you are running on unsupported version of SQL Server. Let me tell you personal experience, suppose you face any issue which comes eventually as a MS bug and you plan to ask MS to pay you for your loss you cannot because you are running unsupported version. The legal agrrements are little more complicated and I will not talk about it. Also even if you raise a case with MS for some support related activities MS engineere will straight away say its unsupported version first please apply SP2, YES not even SP1 SP1 is also not supported, then only they will proceed further, after you have applied SP2.

There was a critical bug fixed in SQL Server 2012 SP1. 


Memory leak for SQLOPTIMIZER
Unexpected start for SQL Server.


List of bugs fixed in SQL Server 2008 R2 Sp1

List of bugs fixed in SQL Server 2008 R2 SP2

I guess above two reasons and links are enough. Are you waiting to face the issue and then apply SP2 ? Please dont. Also the option recompile bug was fixed in later CU and eventually in SP1.


  2.Should we decide to apply SP1/SP2, will there be critical hotfixes to apply afterwards?


As already pointed SQL Server 2008 R2 SP2 is not fully supported. As a general rule after launch of new service pack within one year previous SP becomes unsupported. Following is difference between mainstarem support and extended support taken from here

Think of Mainstream support as “normal”. In other words, mainstream support means Microsoft supports a product with its full offerings including paid incident support, hotfix support, security updates, etc.

When a product enters the Extended Support “phase”, the game changes:

1) We still provide security updates at no charge to all customers

2) You can still call CSS or create a case online per the normal support offerings (pay per incident, Premier, etc)

But…you cannot obtain a non-security hotfix from Microsoft for no charge. In order to obtain a non-security hotfix, you must purchase an Extended Hotfix Support Agreement. Extended Hotfix Support Agreements are available for Premier customers. Contact your Technical Account Manager for more information.

The extended support might involves more cost than normal support and is not available to everyone. If your firm has premier support tie up with MS then you can get extended support eaisly otherwise its costly and bit difficult. This is as per my region MS support/licensing is complex and you should speak to licesning expert to get more information about extended support.

MS says that CU should be only applied if user is facing bug which is fixed in that CU. If not Microsoft recommends not to apply it. SP's are more throughly tested and more reliable as compared to CU. So I would also say unless you face issue dont go for CU.If you want help in deploying SP please refer to This Link(its for SS 2012 but process/steps remains same)