Time to move on from MSDE to SQL Server 2005 Express (you do want to run on Windows Vista – don’t you?)


[Updated 22nd July to expand on MSDE on Vista]


MSDE is the free lightweight version of SQL Server 2000 which many ISV use as part of their overall architecture – typically for roaming users or “edge” data synch. Its limitations are that it is purely a database engine with no management tools, it has a maximum size of 2GB and it has a workload governor that seriously inhibits throughput. Thankfully we have a much better alternative – SQL Server 2005 Express:



  • Increased DB Size to 4GB
  • Removal of workload governor
  • Better integration with Visual Studio
  • Free integrated Management Tool; SQL Server Management Studio Express
  • SQL Server 2005 Reporting Services

If you are still not convinced then perhaps there is another reason you should look at migrating. Microsoft is working hard to help customers achieve the highest levels of security. As part of this push we have made the decision to not support MSDE on Windows Vista as we believe the combination of SQL Server 2005 Express and Vista is a better choice. We therefore recommend that you begin migration planning to SQL Server 2005 Express for any applications developed using MSDE that you believe will need to run on Windows Vista. Thankfully for most applications this will be relatively straightforward and we have resources to help. This is not a decision taken lightly  – but reflects the emphasis we place on making our products as secure as possible.


Find out why you should be upgrading here and Download SQL Server 2005 Express here!


Also, as this has proven to be such a “popular” topic, can I also draw your attention to this post on Vista Application Compatibility http://blogs.msdn.com/ericnel/archive/2006/06/07/620389.aspx. If you are developing solutions, then please do take the time to check these resources out and test your application installs on Vista sooner rather than later.


 

Comments (30)

  1. Dennis says:

    Sorry, but your post really is frustrating, unless you were being sarcastic. “we have a much better alternative …” – no one is denying that SQL Server 2005 Express isn’t better but there is a *huge* body of legacy applications that utilize MSDE (and SQL Server 2000 which is hard to buy today) that will never be upgraded to the 2005 versions.

    ANSWER(eric): Having worked with ISVs using MSDE and spoken with them recently on this I am comfortable that for the vast majority of cases the work done to get an application “ported” from MSDE to SQL Express is small and well understood. I do not underestimate the regression testing – but I run our UK SQL compat labs and I know that in a day of solid testing with tool support we could get a high degree of confidence in a SQL Server 2000 to 2005 migration.  It is always tough when MS drops support for technologies – but all vendors have to do it at some stage to ensure they can keep advancing the technology and keep resources deployed where they can do the most good.

  2. shahnhogan says:

    Will Small Business Accounting 2006 be updated to support SQL Server Express 2005 or can it run with SQL Express 2005 installed instead of MSDE?

    ANSWER(eric): I’m not sure about SBA – I will see what I can find out.
    UPDATE(eric): SBA 2007 will use SQL Express. No info on SBA 2006 yet … still working on that…

  3. davidacoder says:

    Not supporting MSDE on Vista is a terrible decision. It essentially means that there will be a whole set of software that is just a little older than a year when Vista comes out that won’t run (SQL Express 2005 was released in Oct 2005, so that was the earliest time point when ISVs could use it, anything older than that used the light SQL stuff will have used MSDE). It at the same time sends a very strong signal to ISVs: Don’t ever use MSDE or SQL Express if you don’t want to be in terrible upgrade trouble with new versions of Windows, because Microsoft is not even going to support a version of it that is something like 1 1/2 years out of date. Crazy, really….

    ANSWER(eric): I think a little “enforced” change from time to time can be a good thing if there are reasons to justify it. SQL Express is more performant (in most cases) and more secure (in most cases). We learnt a lot from Slammer and the use of merge modules in MSDE – and those learnings went into SQL Server 2005 Express. Merge modules created a horrible issue – only the ISV who installed MSDE could ever patch an MSDE instance installed via a merge module. That meant that even though we had a fix for slammer we could not patch affected users in any way. SQL Express does not use merge modules. I think Vista gives us an opportunity to encourage ISVs to look again at their use of MSDE and in moving to SQL Express get away from merge modules forever. By the way – this is just my thoughts on this…

  4. Kevin Dente says:

    Can you clarify what “won’t be supported” means? Won’t install at all? Won’t run at all? Or won’t be able to get support from Microsoft if there are problems encountered?

    ANSWER(eric): I don’t have anything “official” to share – but I just tried it. On Beta 2 of Vista, MSDE install is detected by Vistas Program Compatibility Assistant as “has known compatibility issues” – but it also detected it incorrectly as SQL Server 2005 without SP1 and recommends SP1 as the resolution. Clearly not correct behavior and I would hope/expect this to change by RTM.

    You can then continue to run the setup. Setup did install MSDE for me and I was able to connect and run a few simple queries.

  5. In my previous post I explained that MSDE would not be supported on Windows Vista. I have answered a…

  6. Mike says:

    I was wanting some clarification on “won’t be supported” too.  We have a shrinkwrapped app that uses Installshield installs where we have added .NET 1.1 framework and MSDE as prerequisites.  

    I just ran an install test of the app on B2 and, It detected that we were not installeding .NET 1.1 SP1 and stated the incompatability prompt there, (but let me continue).   Then went right into the MSDE install.. Didn’t give any prompts at all, it installed fine and the app seems to be working as expected.

    The question is.. Will this behavior continue in the RTM or what?  I guess I too am looking for some “real” clarification as to what supported/not supported means.

    ANSWER(eric): Thanks for sharing. I am waiting on further details from my US colleagues.

  7. Chris Miller says:

    While SQL Server 2005 Express has a lot of things that MSDE 2000, the MSDE still has the SQL Agent service.  That doesn’t come with Express.  If you have a small app that had scheduled SQL jobs running via SQL Agent, then you don’t have a trivial upgrade to SQL Server 2005 Express.

    ANSWER(eric): Spot on. There are a couple of other things that MSDE offered that are not part of SQL Express. The upgrade guide I link to in my other post  does talk about these areas and offer alternatives – e.g. upgrading to SQL Server 2005 Workgroup. I would be the first to admit that I would have preferred Express to have been an absolute superset of MSDE “good stuff” – but unfortunately it is not.

  8. davidacoder says:

    I just read in Euan Garden’s blog (linked to as my URL) that Office includes MSDE. Does that mean that pre-2007 versions of Office will not install fully on Vista?

    ANSWER(eric): I believe Office 2003 included MSDE primarily as an alternative database store to Jet (which is the default) for Access 2003. You can install all of what most folks think of as Office 2003 without installing MSDE. Word 2003, Excel 2003 etc have all installed fine on Vista for me. I have not tried Access 2003 – but I don’t think by default that included MSDE either (anyone confirm?). The other area would be Sharepoint which could run on MSDE instead of SQL Server – but Sharepoint is a server side install so not a candidate for running on Vista.

  9. davidacoder says:

    Quite frankley, I don’t believe this is true. No one at MS can be crazy enough to break Access 2003 solutions in Vista, even if it is only the subset that uses MSDE (that is probably still a number in the millions, given the number of Office installations around). Not supporting files authored with the now current version of Office in Vista would simply be unacceptable in terms of backward compatability.

    ANSWER(eric): Thanks again for the taking the time to respond. I agree this does feel unusual – but the decision has been made based on our commitment to give users the highest levels of security moving forward with Vista – and that means SQL Server 2005 Express instead of MSDE on Vista. I have asked for more detail on this – and I am relaying your feedback. This is in line with other work we are doing – many ISV applications already “break” (thankfully they are often easy to fix) on Windows Vista primarily through security and stability improvements. I am working with UK ISVs to help them become 100% compatible with Vista.

    And finally, remember my earlier comments – moving MSDE to SQL Express is a well understood and in most cases straight forward process.

  10. Mike McIntyre says:

    This is a very bad decision, a real blow to the many legacy ISV solutions which are not easy to migrate.

    Mike McIntyre [MVP]
    Partner
    aZ Software Developers LLP

    ANSWER(eric): Thanks Mike for your thoughts. Just to repeat – my managed ISVs in the UK are not expressing concern to me about this decision. Most already went through compatibility work for SQL 2000 to 2005 and hence feel they are already ready.

  11. Chris says:

    Actually, no, I do NOT want to run Vista. I would like to, but after playing with B2, all I have found is a gimicky interface and a system resource hog. Why the hell do I need such a powerful CPU, graphics card, and tons of RAM JUST to run the OS? That’s just plain stupid.

    And despite the claimed increase in security, it won’t be long before people discover a whole slew of vulnerabilities. It will be fun watching M$ race to get the “fixes” out fast enough– sorta like what happened when XP rolled out.

    ANSWER(eric): It can be tempting to think of Vista as XP with a snazzy UI.- but there is so much more goodness to it than that. We have lots of detail in the product guide for Beta 2. As for resources – it seems to run just fine on a £300 basic Dell PC I bought for the kids a couple of years back. I don’t agree that it is a resource hog – but yep, Beta 2 likes its memory and this basic Dell happens to have 1GB of it so all is well. As for security – I expect Vista will come under a lot of scrutiny from hackers – but Vista does have fundamental deep seated improvements over Windows XP which are most welcome and will further hinder attackers, limit the impact they can make and make it easier to revert to a stable and safe build.

  12. davidacoder says:

    Of course your managed ISVs are not the problem. These are the top notch guys that have a working relationship with MS, probably release fairly frequently, don’t mind if they have to sell an update, whatever. But customers of not so snappy ISVs are really screwed. What if you bought an app that works perfectly fine and the vendor went bankrupt? What if you bought a really expensive app and the vendor tells you that upgrading it to Vista (by porting it to SQL 2005) costs $X, but you simply don’t have that in your budget? What if you have an app developed inhouse that uses MSDE, but the programmer who build it has left, and no one has bothered looking into it, since the app works just fine?

    Managed ISVs are a tiny, tiny, tiny subset of people affected by this decision.

    ANSWER(eric):  We care about all developers – and for us getting application running great on Vista is a priority. Vista by design breaks apps. This is a bigger topic than just MSDE. We have thankfully pulled together resources to help at http://www.devreadiness.org – check out my post http://blogs.msdn.com/ericnel/archive/2006/06/07/620389.aspx

  13. Dave A says:

    Hi.  You keep saying “but the decision has been made based on our commitment to give users the highest levels of security moving forward with Vista”  This implies that the MSDE is unsecure. How? Why have we not be told before? What happens if the application is simply running on desktops within a secure environment? In this case the environment provides the security.

    Why is that not a service pack available to secure it?  How is SQL Server 2005 Express more secure?

    ANSWER(eric): I have learnt over many years that the only truly 100% secure PC is a PC with zero connectivity (no wired/wireless/usb/pcmcia) and no keyboard. I have also learnt that Microsoft is constantly improving the security of its products overtime – as we better understand the threats and have better tools and processes to address those threats. Therefore why should you be surprised to hear that in the 5 years since SQL 2000 we have learnt a lot more about security and are acting on those learnings – and hence SQL Server 2005 Express is better in this area than MSDE. That is not to say that you can’t create an acceptably secure application around MSDE – but I know it is a dang site easier to do it with SQL Express. Check out a top ten list or our SQL Security site.

  14. Kelsey Thornton says:

    I have read the blurb about “SQL Server 2005 Express Edition with Advanced Services”, and it seems that one of the “Advanced Services” is the ability to no longer support ODBC connections at
    all.
    (In SQL Server Express this is turned OFF by default, but I still can’t connect via ODBC even after enabling it!)

    ANSWER(eric): You need to a little more than that – but ODBC just works fine. Check out http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

  15. I think it’s the right move.  SQL Express is SO much better than MSDE.  Gives people a chance to learn and leverage some of the new features like CTE and managed code.  All in the name of progress.

    Good move.

  16. Not to support MSDE is certainly a wrong decision for MS Access Applications. MS doesn’t recommend to use current MS Access(2003 or earlier) with SQL Server 2005.

    ANSWER(eric): Would like to see a link to that as that was news to me. Can you post – thanks.

  17. Jim says:

    SQL Express 2005’s dependency on the .NET Framework is tough for us to handle and is one of the main reson’s we haven’t moved yet.

  18. Vlado says:

    We have migrated to SQL Express 2005 and then back to MSDE 2000 Result: 1 week of work wasted * 3 developers = calculate yourself

    Reason: terrible performance, no sql agent and few other minor things

    It seems that workload governor is much less of a limitation than 1cpu and sql agent is simply unbelievable.

    and now you’re forcing us to move to SQL Express?

    ANSWER(eric): I have said elsewhere – its a shame that SQL Express is not an absolute superset of MSDE. Instead it offers a huge (and I do mean huge) number of new features and capabilities but does miss out a few things that a subset of folks relied on in MSDE. Net gain – but awkward if like your app you infact did need the extra CPU. In our upgrade guidelines we discuss situations where the upgrade path should be MSDE to SQL Server 2005 Workgroup Edition – yours appears to be a candidate for that. Workgroup Edition includes 2 CPU support and SqlAgent (and other stuff). Take a look at http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.

  19. davidacoder says:

    Reading all this, I can really only draw one conclusion: ISVs think twice before you use MSDE or SQL Express and rather have a good look at other embedded database products. If you absolutely need the SQL Server feature set, fine. But if all you really need is some fast, local database without all the fancy stuff (and honestly, that is probably the majority of apps that use MSDE), then you would have been much better off had you used a different database, because your app would probably run without any change on Vista. And what do we know what will happen when the next version of Windows comes out? The same story again? This is just not a stable platform I would bet on, after this story…

    ANSWER(eric): hmmmm…. got to disagree there :) How about thinking of it like this. OTTOMH…

    MSDE 1.0 came out with SQL Server 7.0 in 1999. The next version of MSDE came out with SQL Server 2000 in 2000 – and was known as SQL Desktop Engine. It added some useful stuff. The next version of MSDE came out with SQL Server 2005 in 2005 and was known as SQL Server 2005 Express. It added some useful stuff. In all cases there was a big brother(s) to MSDE – just in case you needed to scale your application up. So as an ISV I might well think “hey – Microsoft are really committed to MSDE. They keep bringing out even better versions and are doing a great job of giving us an upsizing path if we so desire. The latest MSDE (Express) has lots of great new features and hence I know as the requirements of my app advance, MSDE (Express) will be able to handle it. Ah…. turns out Microsoft have been listening to the security concerns of my customers and with Windows Vista and this latest version of MSDE they have created the most secure OS and DB combination from Microsoft yet. Turns out they care so much about security these days that Vista will break some apps by design to help with security and they will not support MSDE on Vista. Thankfully they have given detailed guidance on how to move to the latest version of MSDE (Express). Seems responsible. I like these guys – dang it – I might even send them a Christmas card this year”.

    Now – hold on two seconds before you hit reply :-) Just to be 100% clear – I do understand the concerns expressed on this thread and continue to welcome them. I am listening. I am passing them on – but as my old Nan used to say “Dont throw out the baby with the bath water”.

  20. I’m sure you will hear this many times in the coming days.  If you are a software developer writing…

  21. I know this is going to upset quite a few people and it will cause a bit of pain. Eric Has posted this…

  22. There have been a few blog postings (most notably Eric Nelson’s post on July 12) about the fact that…

  23. Just to follow up on my post MSDE nearing End of Life – No MSDE Support on Vista and from Eric’s post….