Where Does SQL Server Express 2005 Work Best? - dummies

By Robert D. Schneider

SQL Server 2005 Express brings a lot to the table, but when does it make sense to choose it as your data storage platform? Some insight follows. . . .

Small office/Home office

SQL Server 2005 Express strikes a nice balance between simplicity and power, while holding the cost as low as you can possibly get. Plus, because many small businesses one day find themselves morphed (or acquired) into larger enterprises, making this database server the cornerstone of your data storage architecture means that you never outgrow your database: You can easily upgrade to SQL Server 2005 Enterprise.

On top of that, the fact that SQL Server 2005 Express is a true relational database management platform means that you can store and track just about anything in your database. Some particularly good applications include

  • Inventory details
  • Sales statistics
  • Financial metrics

When you have this information safely ensconced in your SQL Server 2005 Express database, you’re free to use all sorts of tools and technologies to help make sense of your data. For example, you might store details about all the individual transactions that your organization performs, and then use business intelligence or other data analysis tools to help identify trends from your sales results.

While you’re free to store anything you like in your SQL Server 2005 Express database, be aware that any individual database has a maximum storage limit of 4GB for your information. If you find yourself approaching that ceiling, you need to archive some of your older data to make room for newer knowledge.

Distributed enterprise

With the rise of low cost, high-speed Internet connections, many more organizations are realizing benefits from distributed computing. For the distributed enterprise of any size, SQL Server 2005 Express offers a good balance between the low maintenance requirements of an entry-level database like Microsoft Access, and the capabilities of a robust, server-based database.

In addition to these features, you can administer multiple remote SQL Server 2005 Express sites from one location via the SQL Server Management Studio Express.

Another useful capability of SQL Server 2005 Express is its ability to participate as a client in a replication architecture. This means that you could have a single, high-end edition of SQL Server distributing its data among numerous SQL Server 2005 Express clients. These clients could, in turn, support read-only applications like reporting or business intelligence. This type of architecture spreads the processing load across multiple machines, and helps eliminate bottlenecks.

Finally, another good illustration of distributed computing is to use SQL Server 2005 Express as a local database and then aggregate its information to a central server for safekeeping and analysis. For example, suppose that you’re building a retail application that will support dozens of locations, none of which will have a database administrator. You could install a traditional, low-end database in each store, but you need to take advantage of a true relational database management system’s features, such as advanced security, stored procedures, or triggers. You also need to gather and consolidate this data for reporting purposes. This is a good use for SQL Server 2005 Express: It offers enough power for enterprise-class applications without demanding teams of highly trained administrators for its daily care and feeding.

Independent Systems Vendor/Original Equipment Manufacturer (ISV/OEM)

ISVs and OEMs have first-hand experience that the cost of embedded technology can eat into profits. That’s not a problem with SQL Server 2005 Express: Free generally doesn’t have much of an impact on margins. However, don’t be fooled by the price: This is a full-featured database, built on the SQL Server platform.

If you base your applications and solutions around SQL Server 2005 Express, you’re not locked in: If your customers need extra horsepower and capabilities, they can quickly and painlessly upgrade to a more powerful edition of SQL Server.

The beauty of basing your solutions on a single relational database management product family is that you can do your development on the more advanced editions of SQL Server, using robust tools like Visual Studio, and then easily deploy your application onto the lightweight SQL Server 2005 Express.

As an added benefit to OEMs and ISVs, SQL Server 2005 Express lets you store your data in a single, easily transferred file. Known as an .mdf file, this structure combines the simplicity of a low-end flat file with the power of a true relational database management system. You should note, however, that you have to deploy the SQL Server Express 2005 server, even if you only intend to deploy .mdf files with your solution.