SQL Server 2005 Express: Beyond the basics

It might be a scaled down version of SQL Server, but SQL Server 2005 Express Edition delivers advanced performance. Windows expert Serdar Yegulalp explains how, including using it in custom applications as a data engine, securing an instance of SQL Express, and moving from SQL Express to a full instance of SQL Server.

In my previous article about SQL Server 2005 Express Edition, I talked a bit about what the Express Edition was designed to address and how it eclipsed the SQL Server Desktop Engine as a lightweight way to make use of SQL Server power. This time around, I'm going to talk about some of the more advanced things that can be done with SQL Express 2005: using it in custom applications as a data engine, securing an instance of SQL Express, and moving from SQL Express to a full instance of SQL Server.

SQL Express 2005 is a fine choice if the full version of SQL Server is too much, or if you're looking to get your feet wet in a way that doesn't involve using a time-limited version of the product. Though it's a scaled down version of SQL Server, using SQL Express 2005 can offer database administrators advanced performance.

Packaging the engine to go

One of the many things SQL Server 2005 Express Edition was designed to do was serve as a data engine for third-party applications. SQL Express can be used in an application you write yourself and repackaged with it. That way someone else who wants to use the program can simply install the whole thing and get an instance of SQL Express along with it.

One of the things Microsoft recommends, whenever possible, is re-using a given instance of SQL Express. If you have two programs using SQL Express (even if they're by different authors!), it makes sense whenever possible to re-use a given instance of SQL Express, so there aren't multiple editions of the product installed on the same machine.

Microsoft provides a function called user instances, which allows an existing SQL Express installation to be recycled, so that the database for your project can be attached to a previous running SQL Express installation. It can be addressed independently of any other database. To that end, when you create the installer for your application, you should check to see if an instance of SQL Express is already installed and give the user the option to attach the application's database to it. If there is no instance of SQL Express on that machine, then give them the option to install it then and there. (This also helps avoid problems where the namespace for one instance collides with another.)

Microsoft talks about how to accomplish all of this in detail in the document Embedding SQL Server Express into Custom Applications. Another thing that Microsoft describes in this document is how to use the .NET Framework 2.0 "ClickOnce" technology to create an application that can live on an application server — whether on the LAN or on the Web — and does not need to be downloaded and run on the client machine. (Note that this requires the .NET Framework 2.0 and an instance of Server Express to be running on the local machine to work.)

Don't forget about security for SQL 2005 Express Edition

An administrator working with a full version of SQL Server obviously has to be concerned about security, but what about SQL Express? The answer, amazingly, is yes: you do need to give at least some thought to security issues with SQL Express, although obviously not in the same the scope and depth as SQL Server.

The first issue to think about is the SA account and password — the master account for any given instance of SQL Express. The easiest solution to this is to simply configure SQL Express to use Windows Authentication Mode, so the user has to supply admin

More on SQLServer 2005:
  • Developing with SQL Server 2005 Express

  • SQL Server .VHD trial editions that impress

  • SQL Server 2005 Enterprise Edition vs. Standard Edition
  • credentials on that system to gain admin access over the database. This doesn't mean everyone who uses the database — for instance, someone using an application that accesses the database — has to be an administrator. Make your SA password as secure as possible. It always has to be created for a given instance of SQL Express, and any rules about password strength provided by the domain you're using SQL Express in will be enforced on the SA password.

    Another security issue to consider is which network protocols to enable when setting up connectivity to the database. If you're not using the database anywhere except on your own machine, for instance, you can safely disable the use of any protocol except Named Pipes. If you insist on using TCP/IP as the connection protocol, make sure that system won't be accessible, except by authorized clients.

    Those are two of the biggest issues, but Microsoft has some more material on securing SQL Server 2005 Express Edition, aimed both at application developers and people using SQL Express as a database product.

    Migrating from SQL Server Express Edition to SQL Server 2005

    At some point you might decide that SQL Express, as convenient as it is, just isn't a substitute for the full version of SQL Server. Maybe you need SQL Server's advanced analysis and reporting tools. Maybe the 2GB database limit is cramping your style. Either way, you want to make the jump to the full version of SQL Server, and you want to do it without losing anything you're currently working on in SQL Express.

    The good news is that you can transition from SQL Express to the full SQL Server quite transparently. There's a couple of ways to go about doing this, but the easiest is to back up the databases in question on SQL Express, install the full version of SQL Server, and restore the databases there. Both programs "speak" the same database backup format, and the two can live side-by-side in the same machine quite responsibly. However, bear in mind that both instances of SQL Server — Express and the full edition — should be configured with the same collation.

    Note also that if you have issues where you have mixed collations in different columns, you should resolve those at this point before putting the database back into production. The exact choice of collation is of course entirely up to you and your needs; what matters most is that it's consistent, or you may find yourself running into problems down the line (query errors, etc.).

    Serdar Yegulalp has been writing about Windows and related technologies for more than 10 years and is a regular contributor to various sections of TechTarget as well as other publications. He hosts the Web site WindowsInsider.com, where he posts regularly about Windows and has an ongoing feature guide to Vista for emigrants from Windows XP.
    Copyright 2007 TechTarget

    Dig Deeper on Microsoft SQL Server 2005

    Start the conversation

    Send me notifications when other members comment.

    Please create a username to comment.