There’s now a replacement for MSDE that has lots of advantages over its predecessor. SQL Server 2005 Express Edition is a workload-limited version of SQL Server 2005, but the performance limits are much less restrictive than those imposed by MSDE. You can also get a management interface from which you can create databases and carry out administrative tasks. The limits on the new version are that it will only make use of a single CPU, even on multiple CPU servers. It will also only make use of a maximum of 1GB of memory, and the biggest database you can have is 4GB. OK, this does impose restrictions that may force you to move to ‘full’ SQL Server in the longer term, but you can certainly get a long way past the starting post before you hit the barriers.
The more cynical among you are undoubtedly wondering why Microsoft is offering a ‘free’ database that appears to offer everything necessary for you to create and run reasonably large databases. The rise in importance of MySQL and similar open source databases is undoubtedly a factor. Another reason is that once you’ve put work into writing an application that uses SQL Server Express, you’re hardly going to ditch it all and move to Oracle if you need further functionality, are you? Instead, you (or your users) are going to pay for a full version of SQL Server.
You may be thinking that your databases aren’t going to get bigger than 4GB, so there will be no need for you to ‘move up’. If that’s the case, you need to bear in mind that while you get the identical database engine to SQL Server 2005 (when it finally appears), you don’t get the additional analysis bits. SQL Server Express doesn’t come with Reporting Services, or any parts of the Business Intelligence Platform such as Analysis Services. You don’t get SQL Agent for handling scheduled tasks, nor do you get full text search. You’re also missing support for data mirroring and clustered databases.
Most of the configuration aspects of SQL Server Express are dealt with using wizards
Running replication tasks defined by administrators
If you were cruel, you could describe SQL Server Express as a Trojan horse of a product. As the developer, you write the application based on the server engine. Then when your business users come to you asking for ad-hoc reports, data mining, and business intelligence facilities, you point them in the direction of the Microsoft salesman. If you feel this is sneaky, you have to remember that in the meantime, you’re getting the benefit of using a product that has taken a lot of people and an awful lot of money to develop.The current version of SQL Server Express is beta 2, and while the software itself is free, the kit you need to run it isn’t. It needs Windows Server, either 2000 or 2003, and is very picky about required components including the right service packs, and the absolute latest release of the .NET Framework – the Visual Studio 2005 version of the .NET Framework, in fact, is a prerequisite for the installation of SQL Server Express, and the setup produces an error if the correct version is not found on the machine.
Once you’ve managed to install all the correct elements, SQL Server Express is a great improvement on MSDE. You work with databases using the Microsoft SQL Server 2005 Express Manager, which you download separately as a free utility that can also be used to administer MSDE and SQL Server 2000.
SQL Server Express Manager gives you a basic interface where you can work with your databases. While it is designed to shield you from as many choices as possible, it’s still not 100% point and click. For example, the first thing you see is a screen where you’re asked which server instance you want to connect to – no drop down lists or hints. If you don’t know the name of your server then you will get no help with the problem. You’re also expected to know whether to use Windows or SQL Server Authentication. Only database server aficionados would really describe it as being user friendly!
It lets you carry out most administrative tasks, providing a query editor that lets you work with multiple result sets and an object explorer with which you can view and manage schema objects. The manager is set up to minimise the number of questions you’re asked, working on the assumption that you’re happy with the default choices for most things. So you can create a new database by specifying only its name and location. If you’re sufficiently experienced with SQL Server to know your way around T-SQL, you may prefer to stick with using this, as you do get more control over your choices than you would using the graphical user interface.
One improvement to SQL Server Express over MSDE is the new visual Query Editor. This looks good, with keyword colour-coding and a results pane that returns results in a data grid. You also see any error messages that you’ve managed to cause in the results pane.
The query manager offers colour coding and a graphical interface
One area that is more friendly is the Microsoft Management Console (MMC) snap-in called the SQL Computer Manager. This is part of SQL Server 2005, and replaces previous editions tools such as the Service Manager, Server Network Utility and Client Network Utility. In addition to SQL Computer Manager you get SQL Command (a replacement for OSQL), and an updated BCP for bulk copying of data. The similarities with full SQL Server 2005 run throughout the product. So, for example, you get the same native and managed providers as the rest of SQL Server 2005. This should ensure that applications that are written based on SQL Server Express will work without changes if you move to the full product.
A major reason why SQL Server Express is of interest to developers is that you get it with all the 32-bit editions of Visual Studio, so you may well already have it on your system. The idea is that you will find SQL Server Express as closely integrated as the Jet database system used to be in the past, and that you will be able to write not only standard client applications, but also ASP.NET apps for your Web servers.
The way you use SQL Server Express in your applications is via the database object. This is a template for a SQL Server database, and it appears when you choose the option to Add New Object. If you add a database object, the connection management is done automatically. However, if you want to alter settings, you can do so by editing a central Web.config file.
Once the database has been added, you can then view the objects (tables, views, stored procedures etc) within the database by clicking on the database object. You can drag and drop tables onto your forms, and if you’re using Visual Web Developer, this automatically creates GridView and SqlDataSource objects for you to use. You can also drag and drop to bind controls on your forms to database columns.
SQL Server Express is free; it uses the same technology as SQL Server 2005, so you know you’re future-proofing your investment in any applications that use it. For a beta release, it seems to work pretty well. There’s no denying that it’s picky about the environment it runs in, but that will also be true of SQL Server 2005, so there’s a certain argument that says you might as well get the annoyances over with. The only real drawback (if that’s the word for it), is that by choosing to develop on SQL Server Express, you are probably making a decision to buy SQL Server 2005 at some point in the future, whether you are consciously doing so or not.
Kay Ewbank is editor of the magazine Server Management. She is a highly experienced database analyst who has followed the development of database technology from dbase through to today’s SQL servers.
Comments