The Visual Studio 2005 Express products are cool - SQL Server 2005 Express is really cool
You have probably heard already about the Visual Studio 2005 Express family of products that were publicly revealed at TechEd Europe. This is a set of 6 products (VB, C#, J#, C++, Web Developer, SQL Server) that will be available for very low cost. More important, however, is the fact that each of these versions goes out of its way to make it easier for the inexperienced .NET developer to quickly be productive. It starts with the fact that the download size is significantly smaller, setup time is fast, and the IDE is designed to make the first experience(s) as smooth as possible. Another cool feature are the Starter Kits that are included. The VS team is taking a page out of the ASP.NET playbook and providing their own starter kits. These are fully functional sample applications that come with tutorials, along with the source code. The Beta 1 version of VB Express already comes with the My Movie Collection starter kit. Of course, the idea is not only to learn from the starter kit, but also to take it further and extend/modify it to meet your own needs.
But what I really want to talk about is SQL Server Express :-)
This is the replacement/next generation of MSDE. If you're not familiar with this, it is a free and redistributable version of the SQL Server engine (SQL 2000 in the case of MSDE and SQL 2005 in the case of SQL Express), without the tools and additional services, and a few limitations. I have always been a big fan and proponent of MSDE, and have pushed my clients to use this instead of an MDB, except for the simplest of applications.
On the SQL Express page you can see the long list of features of this product - I 've also pasted it here for you.
A robust database for building dynamic applications
- Based on the core SQL Server 2005 Database Engine, including an advanced query optimizer and the new snapshot isolation level
- Supports the complete SQL Server programming model including T-SQL and CLR integration
- Standard SQL Server objects such as stored procedures, views, triggers, and cursors
- Client (Subscription-only) replication and support for the new Service Broker features
Strong XML support
- Native XML data type
- Supports structured and semi-structured data
- XQuery support
- XML Schema support
Tools and features to enhance management and ease of use
- Automated tuning of database parameters based on usage characteristics
- Express Manager (coming soon) will provide wizards for many common tasks
- Comes with Computer Manager for starting and stopping the SQL Server Express Windows service
- Automated servicing and patching (as part of Microsoft Update)
- XCopy deployment with administrator privileges (non-admin support in Beta 3)
- Simplified documentation helps you easily build dynamic applications using relational databases
Easy installation and setup
- Fast download
- Simplified user interface for setup
- Silent install for embedded usage
Scalability and performance
- Supports 1 CPU, but can be installed on a server with any number of processors
- Maximum 1 GB addressable RAM
- Maximum 4 GB database size
Deep integration with Visual Studio 2005
- Installed with Visual Studio 2005
- Takes advantage of Visual Studio Data Explorer for designing schema, adding data, and querying local databases
- CLR integration
- Supports user-defined types and aggregates
- In-process data access with ADO.NET 2.0
- Integration of SQL Server and CLR security
- Integrated debugging
Robust security
- Secure by default settings
- Fine-grained administration rights
- Three levels of code access security: Safe, External Access (verifiable), and Unsafe
- Supports leveraging stored procedures as an abstraction layer
- Support for Windows authentication
If you look carefully, you'll notice that almost everything is not specific to SQL Express, but rather, a feature of SQL Server 2005 (this is a good thing!). However, I think that there are 3 areas that deserve special notice – I have highlighted these in red for you. These are, IMHO, the 3 biggest difficulties in getting people to move from MDB to MSDE today - and have all been addressed in SQL Express:
(1) Difficult deployment - having to learn at least the basics of administering SQL Server
(2) No tools for manipulating/querying the database once deployed - even though you can use Access or the tools in VS during development
(3) Performance limitations - mainly concerns about the performance governor - an artificial limitation on performance when there are more than eight active concurrent operations.
These issues are addressed in SQL Express in the following manner:
(1) XCOPY deployment. This means that to deploy a database on a new machine (or different location) all you need to do is copy the MDF file and modify (maybe) the connection string. This is exactly what you need to do with an MDB – and no more. No need to create or attach a MDF to create a logical database. No need to open any SQL administration tool.
(2) There will be an easy to use administration tool for SQL Express – called XM (Express Manager) that will also be free and redistributable.
(3) Although there are still a few, IMO very reasonable, limitations that affect performance, the workload governor has been eliminated! This eliminates much of the mystery and guesswork surrounding performance.
For many applications, at least most of the ones for which I do recommend the use of MSDE today, the performance issue is more a matter of fact than reality. In most applications that should be considering using MSDE, the performance hit will be negligible, if anything. Of course, there are many applications, if not most production database applications, that do need to go beyond MSDE/SQL Express to the full-fledged SQL Sever product. If not, Microsoft would not be investing massive amounts of money and resources into this product…But until your application truly requires the advanced performance/features, get started on the right foot for free, with MSDE or SQL Express. Or have both a high-end and a low-end (or trial) version of your product.
About a year ago, I was able to convince a client of mine of all of the benefits of MSDE and that they should use that instead of an MDB in their current system. He was convinced of everything - except for performance. We agreed that they would try to simulate their current workload and then do some performance and load testing. Needless to say, they are now happily using MSDE in their application.
With SQL Server 2005 Express, you not only get the features of SQL Server 2005 (
Talk about a no-brainer…