This week saw the CTP release of SQL Server 2008 R2 SP1 which combines all fixes to date for SQL 2008 R2 up to Cumulative Update 6 (CU6), and fixes to issues that have been reported through customer feedback platforms. These include supportability enhancements and issues that have been reported through Windows Error Reporting (WER) – why it’s good to participate! 
As posted by Aaron Bertrand, note that CU7 is *NOT* included in this service pack; so, if you are relying on any of those fixes, you should hold out until after SP1 is released *and* after the first subsequent cumulative update is released, as that is when the branch will most likely be caught up.
Anyway, as we have a SQL R2 RTM box badly in need of some patching I thought to hell with the warnings and let’s give it a whirl… The machine in question just happens to be our production dB backend for SharePoint 2010, SCE2010, Business Contact Manager, plus some dev/test SharePoint dB’s..
Firstly you’ll need to download the bits for your platform (ours is Intel x64) from Download details: Microsoft® SQL Server® 2008 R2 Service Pack 1 Community Technology Preview
Also download the SQL Server 2008 R2 SP1 Feature Pack CTP1 which is a collection of stand-alone packages which provide additional value for SQL Server. It includes the latest versions of:
the bits of interest to us from the Feature Pack are:
ReportBuilder3_x86.msi (This will be important for the RS crowd regardless of the SP)
sqlua.msi (The Upgrade Advisor – later discounted as for pre R2 upgrade)
In terms if overall process, I’d start with backups and whilst they are running get your reading head on, there is plenty to keep you occupied!
1) Backup, Backup and verify!
O/S level e.g. Windows Server Backup and application level e.g. SQL Backup, and perhaps even the app behind it, in our case SharePoint 2010 and System Center Essentials
In Windows Server 2008 onwards if you have Windows Server Backup command line tools installed you can easily check the status of the last backup with wbadmin get versions, e.g.
wbadmin get versions
Backup time: 14/05/2011 00:00
Backup location: Network Share labeled thefullcircle.localBackups
Version identifier: 05/13/2011-23:00
Can recover: Volume(s), File(s), Application(s), Bare Metal Recovery, System State
2) Read the Release Notes and the Readme, read the MS TechNet SQL blog post
3) Note the warnings..
Please note: This Customer Technical Preview (CTP) release is not supported by Microsoft Customer Support Services. Please submit feedback using the Microsoft SQL Server Connect Feedback Center. In addition, some of the fixes documented in this CTP release may not be included in the final release. There may also be fixes included in this CTP release that are not documented in the master KB article.
then ignore it 
4) Don’t Analyze Your Upgrade with Upgrade Advisor – already on SQL 2008 R2!!
In the readme it mentions to Analyze Your Upgrade with Upgrade Advisor although clearly if we are installing SP1 for R2 we are already on R2!?! 
– I’ll skip this step me thinks!
– I’ll skip this step me thinks!5) Do check your Edition, Version and Installed SQL Server features
Clearly you can see your version number in SSMS, e.g.
but are you absolutely sure of your edition? (only recently The Full Circle performed a SQL Cluster Edition downgrade for a major London Financial Index, the main one.. due to a mistaken edition installation that would have proved VERY costly to license – like £4K per processor)
due to a mistaken edition installation that would have proved VERY costly to license – like £4K per processor)Use the SQL query:
SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)
to retrieve the version and edition e.g.
10.50.1600.1 RTM Enterprise Edition (64-bit)
Next double check what features you have installed, you’ll need to run Setup from your SQL R2 media, go to Tool, and select Installed SQL Server features discovery report e.g.
I’m not going to paste the whole report here, but just for one of our instances:
Microsoft SQL Server 2008 R2 Setup Discovery Report
|
Product
|
Instance
|
Instance ID
|
Feature
|
Language
|
Edition
|
Version
|
Clustered
|
|
Microsoft SQL Server 2008 R2
|
MSSQLSERVER
|
MSSQL10_50.MSSQLSERVER
|
Database Engine Services
|
1033
|
Enterprise Edition
|
10.50.1600.1
|
No
|
|
Microsoft SQL Server 2008 R2
|
MSSQLSERVER
|
MSSQL10_50.MSSQLSERVER
|
SQL Server Replication
|
1033
|
Enterprise Edition
|
10.50.1600.1
|
No
|
|
Microsoft SQL Server 2008 R2
|
MSSQLSERVER
|
MSSQL10_50.MSSQLSERVER
|
Full-Text Search
|
1033
|
Enterprise Edition
|
10.50.1600.1
|
No
|
|
Microsoft SQL Server 2008 R2
|
MSSQLSERVER
|
MSAS10_50.MSSQLSERVER
|
Analysis Services
|
1033
|
Enterprise Edition
|
10.50.1600.1
|
No
|
|
Microsoft SQL Server 2008 R2
|
MSSQLSERVER
|
MSRS10_50.MSSQLSERVER
|
Reporting Services
|
1033
|
Enterprise Edition
|
10.50.1600.1
|
No
|
6) Run the big one!
In our case it’s the x64 version – SQLServer2008R2SP1-KB2463333-x64-ENU (314MB’s worth although will expand out to c.525MB and in our case the SP consumed c.2GB on the C: drive which is where our instance binaries reside (data & logs on another volume of course).
I’m not going to paste screen shot after screen shot, but the steps are:
6.1) A normal SQL upgrade/update process
6.2) Accept the License terms and do check the box to send feature usage data to Microsoft – it really does help steer the product!
6.3) Select your Features (okay another screen grab!) – let the tool select, and you check/confirm
6.4) Check for files in use (not you!, the tool does this)
6.5) Let the update go and make tea, sweep the deck, tidy your desk, etc. ours took c.25 minutes
6.6) If all goes well you should get a screen per below advising to Restart your engines!
Checking the version number should reveal 10.50.2425.0 SP1
Also checking SSMS for version info, in our case gave:
Microsoft SQL Server Management Studio 10.50.2425.0
Microsoft Analysis Services Client Tools 10.50.2425.0
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 8.0.7601.17514
Microsoft .NET Framework 2.0.50727.5444
Operating System 6.1.7601
Microsoft Analysis Services Client Tools 10.50.2425.0
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 8.0.7601.17514
Microsoft .NET Framework 2.0.50727.5444
Operating System 6.1.7601
And if it hasn’t gone well…? you’ve got the rest of the day to interrogate SQL setup logs in the usual place (C:Program FilesMicrosoft SQL Server100Setup BootstrapLog) – we’ve 80MB and 340 files in the last entry alone (of 4 today!), and at worst case start thinking about your restore process!
7) If you are a developer, do some more reading!
There are a lot of enhancements, fixes, and new features and that’s a different topic all together, however by the best cover I’ve seen thus far is Aaron Bertrand’s most excellent blog, and specifically the following posts: