Got questions about the new features in Microsoft SQL Server 2008 R2? Can’t wait for R2’s release date? Here’s the answers I’ve been giving out most often to database administrators wondering about the changes in SQL Server 2008 R2 vs SQL Server 2008.
What will be the SQL Server 2008 R2 release date?
SQL Server 2008 R2 will be released in May 2010. Despite it being released in the year 2010, it will not be called SQL Server 2010.
What’s the cost to upgrade to SQL 2008 R2?
If you’ve got Software Assurance for your SQL Server 2008 licensing, then you get all R2 updates included for free. If you don’t have SA, then you’ll need to decide whether the new features in SQL Server 2008 R2 vs SQL Server 2008 will be worth the cost of upgrading, because you won’t get SQL 2008 R2 for free. R2 is not considered a service pack.
What’s new, and what are the new features in SQL Server 2008 vs SQL Server 2008 R2?
From a very high level, here’s the new features in R2 that weren’t in 2008:
- Database Engine – not much new here. There’s some infrastructure introductions to support Data-Tier Applications in the future, but they’re not too useful as of the August CTP. I blogged about it in a 3-part series starting with this article about How SQL 2008 R2 is Like Virtualization for Databases. If you’ve heard the terms Utility Control Point, Utility Explorer, or SQL Server Utility, I’ve got your answers in that series.
- Business Intelligence – there’s a ton of new functionality for self-service BI in SQL Server Analysis Services 2008 R2. Excel 2010 will act as a client for SSAS. You can learn more about it in this screenshot-packed review of what’s new in Project Gemini and this pivot table tutorial for Project Gemini.
- Scale-Out Servers – if you need to scale a data warehouse beyond a single server, you’ll want to keep an eye on Project Madison. It’s the result of Microsoft’s acquisition of DATAllegro.
- T-SQL Enhancements – got nothin’ for you here. No new commands, no new data types, no new stored procedure goodies.
Will I need Visual Studio 2010 to edit DAC Packs?
Data-tier application projects, new with SQL 2008 R2, can’t be edited with SQL Server Management Studio. You’ll need the upcoming release of Visual Studio 2010 to create and edit .dacpac files.
Where can I download the SQL Server 2008 R2 CTP?
MSDN and Technet subscribers can download 2008 R2 Enterprise Edition now for free. Check in the SQL Server 2008 section of the download site, or click here to download SQL Server 2008 R2 for free. Keep in mind that this is a preview build, not a feature-compete beta. It should not be used in production. When databases are attached to a SQL 2008 R2 server, their version number is upgraded to 660, and these databases cannot be reattached to an older (SQL 2008) server.
How will SQL Server 2008 R2 licensing work for virtual servers?
R2’s licensing has an ugly change for shops who use virtualization. Right now, if you buy SQL Server 2008 Enterprise Edition now by the CPU, you get unlimited virtualization rights. If you’ve got a 4-socket virtual host and you buy 4 sockets of Enterprise Edition, you can run as many SQL Servers on that host as you want. From Microsoft’s SQL Server 2008 Licensing Guide:
“For enterprise edition there is an added option: if all physical processors in a machine have been licensed, then you may run unlimited instances of SQL server 2008 in one physical and an unlimited number of virtual operating environments on that same machine.”
You may not be running SQL Server widely in virtualization environments yet, but ask yourself how many SQL Server 2000 and 2005 instances you’re running today. SQL Server doesn’t just go away – the instances you install today will still be in production for years to come. They might not be virtualized today, but they’re gonna be virtual years from now, and today’s licensing saves you a fortune.
Microsoft sees that coming, and they’re changing it in R2. Review the SQL Server 2008 R2 Editions PDF and you’ll find that R2 Enterprise Edition doesn’t come with unlimited virtualization. To get that feature, you have to spring for the new Datacenter Edition, which costs around $60k per CPU socket.
If I was a DBA with a budget to buy SQL Server licenses this year, I’d make that purchase now. In May, Enterprise Edition’s price is going up, and it will have less licensed features. I’d buy it with Software Assurance anyway, so I’ll get R2’s new features if I want them. If I didn’t want those new features, I’d still have the flexibility of running unlimited SQL Server 2008 instances in virtualization.
Why isn’t the next version called SQL Server 2010?
Microsoft is following the precedent set with Windows Server 2003 R2, which added some features but wasn’t a groundbreaking change. SQL 2008 R2 does include some pretty cool stuff, but it doesn’t include major earth-shaking changes in the database engine itself.
Where can I read more about the new features and changes?
Here’s what I’ve written so far:
38 Comments. Leave new
Great Post Brent.
My only beef with MS’ precedent of calling things R2 is that we end up with things like SQL Server 2008 R2 SP1. (Though I’m not sure why I’m mentioning that here – other than to complain of course 😉 )
And SQL 2008 R2 SP1 CUP2…
Hi,
Is sql server 2008 R2 having more different to sql server 2008 R2. or its a little bit change?
Good one..
The database engine itself isn’t a big change, but there are a couple of areas outside of the core storage engine that have big changes. Search my site for PowerPivot, Parallel Data Warehouse, and for DAC, and you’ll find articles about those changes.
There are a few more changes to the Database Engine in SQL Server 2008 R2:
– Improvements to the hashing algorithm to calculate the lock hash value (has a potential to improve concurrency)
– Support for Unicode Compression
– Support for more than 64 processor cores
– Some changes in Service Broker related to poison-message handling behavior
– Some changes to Filestream (supports both Snapshot & Read-committed Snapshot isolation levels)
Thanks,
Varsham Papikian
In R2 SQL Utility, I would lke to know how collecter agents transmit data to sysutility_mdw database from managed instances.I didnt see any linked server.
Thanks
My biggest question is what will be dropped/no-longer-supported compared to SQL Server 2008? Were things that were initially deprecated in 2008 be dropped in 2008 R2, or not? The biggest issue we have is supporting multiple versions of SQL Server among our customers for our product, and we have to therefore code to a “lowest common denominator”. We are just now dropping support for SQL Server 2000, so we’re supporting 2005 and 2008. Can we treat 2008 R2 as just “2008” for the purpose of compatability? I am hoping so.
Great question! SQL Server 2008 R2 is a minor release, so I don’t think anything’s been deprecated since 2008. There’s a long list of things that will be deprecated soon, but that usually means the next major version.
More enhancements are in the Reporting Services (part of the BI enhancements that were announced).
Those include:
• Grab and Go reporting ”
Report Part gallery, Shared Datasets
Allows users to break down reports to smaller parts, publish them to the server and have other users assemble them in their reports in easy way – Enables users to quickly assemble their own reports in self service manner, no need to rebuild reports from scratch.
• Reports as oData sources:
One can now programmatically access Reports and query for the report data via Atom data Feeds (Odata standard). Used by PowerPivot to use Reports as a data source for analysis.
•New Data Source support
Sharepoint Lists, SQL Azure are new data sources you can use for reporting.
• Data Visualization enrichment
Maps, Sparklines, KPIs , Data Bars
• VS 2010 and Netfx 4.0
Ajax Report Viewer, compatibility with other non IE browsers.
All in all its a fairly large release, much more then people might think although, its true that the enhancements in the core relational engine were limited by design. That is why it was called R2 but anything outside the engine, is by no means an R2 kind of release.
Ariel – thanks for the feedback on SSRS. I agree that R2 is an awesome release for the BI community. I’m really impressed at what’s in the box there. I think we’re seeing a lot of things that have long-term implications in a great way.
Hi Brent!
I was curious as to if Microsoft intends to license SQL Server 2008 R2 SSAS (old school OLAP version) and SQL Server 2008 R2 SSAS (Sharepoint Integration for PowerPivot) differently…
Thanks,
Drake Teran
Hmmm – I haven’t heard anything about that, so I’m guessing no.
This may be a stupid question (and one I’d figure out on my own once I install it, which will happen in a few weeks), but I thought I’d ask it anyway:
Can SQL Server Management Studio 2008 connect to a SQL Server 2008 R2 instance? Or do the SQL Server 2008 R2 tools (including SQL Server Management Studio) need to be installed to successfully connect to SQL Server 2008 R2?
Likewise, on a fresh SQL Server 2008 R2 install, with a newly created database, what is the returned compatability level? 105? Or the same as SQL Server 2008? I know the product version number is 10.5.x …
Thanks!
Paul – oooo, good question about SSMS 2008 connecting to R2. I haven’t tried that and I don’t have any 2008 instances left in my lab, so I don’t have a quick answer.
The compatibility level is still the same, but the version number is higher. You can’t attach a database to R2 and then attach it back to 2008, so just be forewarned there.
With SQL Server 2008 R2 being a minor release versus a major release, your old 2008 SSMS will connect to any SQL Server 2008 R2 installation. However the minor release will overwrite your shared install files.
-Drake
SQL server is getting more expensive. What are the cheaper options of the SQL Sever
There’s several versions – Express Edition is completely free, so you might consider checking that out. They raised the database size limit in R2 too. SQL Azure is another interesting option since you don’t even have to buy hardware. And then of course there’s open source databases. The world is your oyster here, but you’ll need to define your requirements in order to pick the right solution for you among the many options.
I have bought SQL Server 2008 with software assurance and licensing but it expired on April 30, 2010. May I use the SQL 2008 R2 version or how I can download this.
Akber – unfortunately, your SA expired before R2 came out.
Microsoft broke its own versioning rules with SQL 2008 R2. There is a major change in the file structure that makes 2008 R2 .mdf files completely unreadable by 2008.
One of Microsoft’s own rules on versioning is that if files produced or used by the new release are incompatible with the previous release, then this is a MAJOR version number upgrade.
Example– I am developing with 2008 R2 in the development environment, and our production server is 2008 (not R2). I just detached the data and log files from my development box, and copied them to the production box, then tried to attach them.
Error message:
The database ‘dbNameHere’ cannot be opened because it is version 661. This server supports version 655 and earlier. A downgrade path is not supported.
Could not open new database ‘dbNameHere’. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948).
By Microsoft’s own versioning rules, “2008 R2” should be “2010.” The version number should not be 10.50.xxxx, but 11.00.xxxx, because 2008 R2 data and log files are incompatible with 2008.
This is a breaking change that was not documented anywhere that I can find. It is not apparent to developers and administrators that there is a file incompatibility, until you try to do it. Normally, any developer would assume that since it is still “version 2008 (version 10.x),” then files should be compatible between them.
David – yep, it’s the database version number, and it’s been blogged about a few times:
http://www.sqlskills.com/BLOGS/PAUL/post/SQL-Server-2008-R2-bumps-the-database-version.aspx
Hope that helps!
Brent
Actually, no… It doesn’t help, and you miss my whole point. I solved my example by scripting the database with the data. Only 456,000 lines of SQL script, but it worked.
My complaint is that it should not have been necessary, because we should never have been “trapped” into running 2008 R2 in development (from our MSDN subscriptions) when we have 2008 installed in production.
The blog posts you referenced are all talking about trying to attach a 2005 database to a 2000 server. Or a 2008 database to a 2005 server.
To me, the appropriate answer to those is, “Well, duh! You wouldn’t try to Open a Word 2007 file with Word 2003 (without a converter), would you?”
But I’m talking about SQL 2008 to SQL 2008. The “R2” does NOT make it clear that the database files are incompatible. Even worse, copy a database from a production 2008 server to a development box with 2008 R2. Make changes, then try to attach it back. It’s now broken for the server it originally came from.
Though the referenced blogs explain the internals about why the files are incompatible (I already knew that.), it does not adequately explain why Microsoft decided not to make this a “Primary” or “Major” software version release. Your original post tells us “… added some features but wasn’t a groundbreaking change.” Well, I’d say that an incompatible file structure IS a “groundbreaking change.” These are the practices that make it appear to the public that Microsoft decided to force users to spend more money to upgrade or to buy SA’s.
It’s like if Microsoft decided to change the .docx file structure for Word 2010, and release it as “Word 2010 R2” with no way to convert the files back. Word 2010 could not open “R2” files, and businesses that had a few new PC’s with “Office 2010 R2” would be up in arms.
I maintain that if you can’t attach a database file from “2008 R2” to a “2008” server, then it should have been a “Primary” or “Major” release (SQL 2010 and version 11.x). After all, it’s been two years after initial release, and there is no forward compatibility. I have not yet found a single case where developers and DBA’s did not learn of this by surprise. If Microsoft is destroying forward compatibility within “2008” releases (within “10.x” versions), there should at least be a Service Pack for 2008 so it can handle the new file format.
David – Paul’s blog post specifically covers 2008 R2.
I guess my main complaint is that blogs and forums are NOT the right place for developers and other IT professionals to find out about this sort of issue. Microsoft should have had this information very prominently displayed on the SQL Server web site, in the download areas, and in MSDN. I still can find NO introductions to R2, anywhere, that warns us. ONLY in blogs and forums.
Microsoft should be VERY UP FRONT about this sort of change. While it’s not technically a “breaking” change, yet it has a similar impact. Developing in 2008 R2, then needing to deploy to 2008, caused me to lose a full day of work. I had to script out the database to deploy it. Then I had to uninstall 2008 R2 and install 2008 in the development environment, a process that takes hours.
Microsoft knows good and well that companies will not just roll over all their servers to the next version. But developers may be expected to push the envelope with then newest products. (Such is the case where I work.)
The natural assumption is that it’s still “2008” and it’s still “version 10.x.” And I’m not alone. A simple Internet search turns up thousands who have fallen into this trap.
It could all have been avoided had Microsoft simply called it SQL 2010, and made it version 11.x.
Hi there,
I created a publication using sql 2008 server. the publication was deleted. I want to know if there is a way to discover the computer that connected to the server and deleted this publication. Any Help?
Tony – your best bet is to post this kind of question on a Q&A site like http://serverfault.com.
I suffered the same problem that David mentioned, I had to connect to a R2 database remotely and the client didnt even know it was R2 – they specified 2008.
Yo Brent, when is SP1 coming out for SQL 2008R2?
Thx,
Matt
Matt – well, anyone who tells you they know the release date of a future Service Pack is either lying or breaking their NDA. 🙂
argh!!!!. I guess I will not be installing R2 Thanks Brent.
Looking at this info, I don’t see any reason to upgrade from SQL Server 2008 to R2.
Thanks for the summary without the MSFT marketing spin. It helps.
I would recommend doing it for the company if we had Software Assurance. But since we do not, I cannot justify it. We will likely wait for Denali before we upgrade our systems.
i’m looking to purchase SQL Server 2008 R2 in 64bit, but i only see a 32bit version. Does the SQL Server 2008 R2 product come with both versions (32 and 64 bit on the same dvd) even though this link shows 32 bit
http://www.amazon.com/Server-Standard-32-bit-Client-Licenses/dp/B003KWWIB4/ref=sr_1_4?ie=UTF8&qid=1307579825&sr=8-4
Sorry, I’m not good with licensing – your best bet is probably to pick up the phone and call a reseller like CDW or Insight.