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:

  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.

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

  • You understand whether you apply SP1 or SP2 that you are still running an unsupported version in production? – user507 Sep 2 '14 at 4:58
  • @ShawnMelton yup. The application relying on the database is not compatible with any newer SQL Server version. Not sure why, but that's not my domain so I can do natch. – pepoluan Sep 2 '14 at 5:00
  • As a DBA I can tell you that is within our domain because I would drop any plan to patch a server that has been at RTM this long. I would engage all application owners to inform them their application is in an unsupported state in production. I have done this multiple times with clients for them to find out the application version itself was out of support as well, mission critical. Just my opinion... – user507 Sep 2 '14 at 5:10
  • @ShawnMelton well, Microsoft said that 2008 R2 will still be in 'Extended Support' until 2019. So I can't just declare that 2008 R2 is "no longer supported"... – pepoluan Sep 2 '14 at 5:33
  • 1
    You can if you fully understand what extended support really means – user507 Sep 2 '14 at 6:16

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.

  1. Memory leak for SQLOPTIMIZER
  2. 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)

  • hey, thanks! This is a great write-up, appreciate you taking the time to write it. I'll push for patching to 'those in power' (and also push for upgrade to at least 2012). – pepoluan Sep 4 '14 at 3:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.