Getting Down to Business with SQL Server 2005 Express - dummies

Getting Down to Business with SQL Server 2005 Express

By Robert D. Schneider

If you’re wondering how you can get started using the product, that’s what this article is all about.

Planning your database

The first thing to keep in mind as you contemplate what to keep in your SQL Server 2005 Express database is that this is not a stripped-down, feature-limited, stand-alone product. It’s true that Express does have significant limitations to the amount of information you can store in the database, and that certain key high-volume features are not present.

Before you get heavily into using this edition, you should make sure that none of these limitations are showstoppers for you. Remember that the majority of these constraints shouldn’t impact you during development; they’re only an issue at runtime.

Even though this edition has feature restrictions, SQL Server 2005 Express works with all the same types of information as its more fully featured siblings, and you can easily migrate to a more powerful edition. It also supports all the same application programming interfaces (APIs), as well as the same stored procedure and trigger capabilities.

This means that as a designer, you can build your SQL Server 2005 Express database with confidence, knowing that you aren’t painted into a corner by missing capabilities, nor the solution you design is forever consigned to this entry-level database. This works the other way as well: You can design your solution on a more powerful edition of SQL Server 2005, and then deploy it onto SQL Server 2005 Express, as long as it doesn’t require any of the features that are only found in the more expensive editions of the product.

One feature that’s particularly attractive for distributed application developers and vendors is the Xcopy deployment capability of SQL Server 2005 Express. This lets you easily bundle your application and database (.mdf) file and then copy them to another machine. Because everything is already pre-packaged, you don’t need to manually configure these other platforms, as long as they have a running instance of SQL Server 2005 Express. When your application launches, the database server automatically attaches the .mdf file to the local instance. This architecture results in an easily implemented portability strategy.

Building SQL Server 2005 Express applications

You have a wide variety of electives when building a solution that stores its information in this database. To begin, Microsoft is making the Visual Studio Express products affordable and easy to use to create database-driven applications. If you’re new to application development, checking out these products is definitely worth your while.

This tight integration can go a long way toward boosting your productivity. In effect, with these products, Microsoft has broken down the traditional barrier between application and database tools.

If you’re more inclined to use heavier-weight, more powerful tools, Visual Studio 2005 makes a lot of sense to employ for application development. It’s an extremely potent tool that contains a tremendous amount of functionality. It’s also very well integrated with all SQL Server 2005 products, not only the Express edition. Naturally, all this power comes with additional complexity and a somewhat steeper learning curve, so you should decide if your application requires all these capabilities.

Of course, Microsoft isn’t the only game in town when it comes to application development technologies. You can use several third-party tools (including open source and shareware) to construct a SQL Server-based solution.

Configuring, managing, and monitoring SQL Server 2005 Express

With your database and application built, it’s natural to turn your attention to setting configuration parameters and then monitoring the database. Here again, you have no shortage of options.

To begin, SQL Server 2005 Express snaps into the standard Microsoft Computer Management console. This lets you configure and run your database services.

For further configuring and managing your SQL Server 2005 Express server, you can choose between graphical and character-based tools. As you might expect, graphical tools provide more intuitive, easy-to-understand information about your server.

When it comes to graphical tools, it’s hard to beat Microsoft’s free, excellent graphical management environment known as SQL Server Management Studio Express. SQL Server Management Studio Express can do much more than simply show you tables and run queries. One handy tool is the activity monitor, which opens a window onto all database-related activity for your server. You can monitor a tremendous amount of information using this tool.

For those of you who prefer a character-based configuration and management utility, Microsoft continues to ship the SQLCMD utility, which allows for direct entry of SQL statements. Because you find much of the administrative capabilities for SQL Server embedded in stored procedures, you can run just about any management operation from the rather bland SQLCMD interface.