Cadzow Knowledgebase

Normal view

SQL Server 2005 Express Edition

What is SQL Server 2005 Express Edition?

Microsoft SQL Server is Microsoft's premium data storage platform, available for single servers or large “clustered” arrays of servers for very large databases. Microsoft also developed a cut-down version of SQL Server called MSDE (Microsoft Data Engine) that was based on the same technology but limited in its capacity. However, MSDE is difficult to use, difficult to update and is too badly cut-down to be really useful.

In recognition of this, Microsoft has released a free version called SQL Server 2005 Express Edition which is identical to full versions of SQL Server with the following exceptions:

  • It is limited to 1 physical CPU;

  • It is limited to 1Gb RAM;

  • It is limited to databases of 4Gb (2005), 10Gb (2008 & later);

  • It does not support Analysis Services, Reporting Services, Data Transformation Services or Notification Services;

  • It does not support server-side replication.

For many SQL Server installations, these limitations will be too great. But for many situations, small-scale databases currently running under Jet (.MDB) or MSDE can be upgraded to SQL Server 2005 Express to gain the reliability and power of SQL Server for free.

Installing SQL Server 2005 Express Edition

NB. SQL Server 2005 Express is not supported on Windows 8 and later. Use Microsoft SQL Server 2008 R2 Express Edition or Microsoft SQL Server 2012 Express Edition instead.

  1. Download and install .NET Framework 2.0.

  2. Download and install SQL Server 2005 Express Edition Service Pack 4 from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=26435597-b28e-4568-9d16-017bdf47abdc.

    Follow the setup process by accepting the defaults, apart from enabling Add user to the SQL Server Administrator role if installing on Microsoft Windows Vista.

  3. Download and install SQL Server Management Studio Express Service Pack 4 from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=76ea83da-4797-4f11-a834-456ee9dc3764.

    Follow the setup process by accepting the defaults.

Assigning Permissions in Windows Vista

Windows Vista treats administrative users differently from the classic model used in previous versions of Windows, and this requires some intervention.

If installing SQL Express 2005 Service Pack 2 or greater, enable Add user to the SQL Server Administrator role during setup.

If installing other versions, or if this option was not selected during setup:

  1. Open SQL Server Management Studio Express from StartProgramsMicrosoft SQL Server 2005 using the “Run as administrator” option.

  2. In the left-hand pane, choose SecurityLogins.

  3. Right-click in the right-hand pane and choose New Login…

  4. Click Search to choose the appropriate Windows account, and choose Windows Authentication.

  5. Under Server Roles, enable sysadmin.

Configuring SQL Server 2005 Express Edition

Run the SQL Server Configuration Manager from StartProgramsMicrosoft SQL Server 2005Configuration Tools:

  1. In the left pane, click SQL Server 2005 Network Configuration.

  2. In the right pane, double-click Protocols for SQLEXPRESS.

  3. Right-click TCP/IP and choose Enable.

    You will receive a message that changes are saved but will not take effect until the service is restarted.

  4. Double-click TCP/IP.

  5. Click the IP Addresses tab.

  6. Under IPAll, set TCP Dynamic Ports to blank and TCP Port to 1433.

    Alternatively, if you have a special requirement, set the parameters as desired.

  7. Click OK.

    You will receive a message that changes are saved but will not take effect until the service is restarted.

  8. In the left pane, click SQL Server 2005 Services.

  9. In the right pane, right-click SQL Server (SQLEXPRESS) and choose Restart.

Restoring a Database Backup

  1. Copy the database backup file to a local drive. (SQL Server cannot restore databases from network drives and usually not from removable drives.)

  2. Open SQL Server Management Studio Express from StartProgramsMicrosoft SQL Server 2005.

  3. Enter <COMPUTERNAME>\SQLEXPRESS as the Server Name and click Connect.

  4. In the left pane, right-click Databases and choose Restore Database.

  5. Choose From Device and click the browser button […].

  6. In the Specify Backup dialog, click Add and choose the database backup file.

  7. Click OK.

    The database backup details will be shown.

  8. Place a tick next to the backup by clicking the box in Restore column.

  9. In To Database, choose the name of the database to restore into from the drop-down list, or enter a new name (eg. CadzowDB).

  10. If you wish to put the database files into a non-default location, click Options in the left pane, and change the filenames for the database and the log file.

  11. Click OK.

    The database file will be restored.

Configuring Cadzow 2000

By default SQL Server 2005 Express Edition is installed as an “instance”, so the format for the Cadzow 2000 GO.BAT script is:

SET CADZOWDATA=SQL;CadzowDB;Server\SQLEXPRESS;1433

Substitute your database name, server name (or address) and port number as required.



Copyright © 1996-2023 Cadzow TECH Pty. Ltd. All rights reserved.
Information and prices contained in this website may change without notice. Terms of use.

Question/comment about this page? Please email webguru@cadzow.com.au