Installing SQL Server 2005 Management Studio Express

If you didn't install SQL Server 2005 Management Studio Express with SQL Server 2005 Express, it can be downloaded individually or as part of the SQL Server 2005 Express Edition Toolkit, which includes Business Intelligence Development Studio (BIDS). BIDS will let you create and manage SQL Server Reporting Services (SSRS), which provides reports on SQL Server operations, as well as allowing you to generate reports from your database. We won't be using BIDS in our project, so we only need Management Studio Express, but you can install either version to work through this book.

When you download Management Studio Express, you will have a Microsoft installation file, or MSI, called SQLServer2005_SSMSEE.msi in your download location. In a Windows system, an MSI file can be run as an executable, so simply double-click the filename to begin the installation. Agree to the licensing and accept all the defaults, unless you need to change the installation location. When the installation finishes, you should find SQL Server Management Studio Express in the SQL Server 2005 program group on your start menu.

Running SQL Server 2005 Management Studio Express

When you start Management Studio Express, you will be presented with a login dialog for the SQL Server. It will have your server name and use Windows Authentication for the connection. This works fine on your local system for development, but if you intend to use Management Studio Express on a hosted web site, you will likely be required to use SQL Authentication for access. If that is the case, your hosting service should be able to provide you with the name of the SQL Server, as well as the login details.

Clicking Connect will connect Management Studio Express to your SQL Server. If you expand the Databases folder, and then expand System Databases, you will see four default databases required for SQL Server—master, model, msdb, and tempdb.

These four databases are used by SQL Server to store information for SQL Server itself, and you should never need to touch them. The database we create for our application will be separate from these and that will be the only database we use. SQL Server 2005 Express allows the creation of multiple databases, but for our application we will only create one, both because it makes our application simpler to manage and because many web hosting services charge extra for hosting additional databases.

Management Studio Express allows us to perform some important tasks on our database. If you right-click on the master database and choose Tasks, you will see the important Backup and Restore tasks, as well as a Shrink task that allows us to compress the space used by our database. On a web host, this is important because extra space often costs extra. However, in any SQL Server installation, a smaller database will be a better performer. Shrinking the database is a double-edged sword though, since it can lead to fragmentation of the database. It can also be useless, since a database needs some extra space to function and will need to grow larger as it needs the space. Shrinking is most useful in development or updating a database, especially after a table is altered or dropped and large amounts of space may end up empty.