Microsoft made some important new changes in SQL Server 2008 Express R2. One of the biggest changes is that the maximum database size has been bumped up from 4GB to 10GB. But SQL Server 2008 R2 express is still limited to 1GB of RAM and a single CPU.
The new SQL Server 2008 R2 Express product is packaged differently than previous versions of SQL Server Express. Instead of being labeled as a separate edition, each different SQL Server 2008 R2 Express package is now presented as an installation option. You can download the different SQL Server 2008 R2 Express editions at Microsoft’s website. Below, I guide you through the new options of SQL Server 2008 R2 Express so you’ll have a better idea of what each offers.
1. SQL SERVER 2008 R2 EXPRESS DATABASE ONLY
Tipping the scales at 71MB for the x86 version and 74MB for the x64 version, Database Only is the smallest and simplest installation option for SQL Server 2008 R2 Express. This option is suitable for including in ISV packages and as a database for custom applications. This includes the core SQL Server 2008 R2 relational database engine but not the business intelligence (BI) subsystems like Analysis Services, Integration Services, or Reporting Services. This minimal installation option also doesn’t include any built-in management tools. However, most of the SQL Server core features are included such as support for the CLR and support for all data types, including the new geospatial and FILESTREAM data types.
2. SQL SERVER 2008 R2 EXPRESS MANAGEMENT TOOLS
It’s important to know that SQL Server 2008 R2 Express Management Tools doesn’t include the relational database engine. This installation option includes only SQL Server Management Studio Express (SSMSE). The Management Tools are 154MB for the x86 version and 163MB for the x64 version. (Doesn’t it seem somehow wrong that SSMSE by itself is almost twice the size of the relational database engine?) Anyway, you would use this option to manage previously installed instances of SQL Server Express.
3. SQL SERVER 2008 R2 EXPRESS DATABASE WITH MANAGEMENT TOOLS
This version, SQL Server 2008 R2 Express Database with Management Tools, is probably the best version for low cost database development for use with small business and departmental database needs. It has all the relational database features plus it also has SSMSE, which you can use for basic database management. This version weighs in at 235MB for the x86 version and 247MB for the x64 version.
4. SQL SERVER 2008 R2 EXPRESS DATABASE WITH ADVANCED SERVICES
The SQL Server 2008 R2 Express Database with Advanced Services option is the most full-featured SQL Server 2008 R2 Express edition. It comes in at a hefty 727MB for the x86 version and 795MB for the x64 version. This installation option includes the basic SQL Server relational database engine, as well as SSMSE, support for full-text search, and support for Reporting Services. This Express version of Reporting Services works only with the local SQL Server 2008 R2 Express instance. This also supports Database Mirroring (Witness Only) and Snapshot, Transactional & Merge Replication (Subscriber Only).
Step By Step Guide for SQL Server 2008 R2 Express Edition Installation
Below I show the steps to install SQL Server 2008 R2 Express.
1. Launch the package, you will then see the contents extracted to a temporary location.
2. SQL Server Setup checks to determine pre-reqs are installed. If not, you will be prompted to install.
3. The Installation Center will then launch. Click on “New installation or add feature to an existing installation” to start the Installation Wizard.
4. Accept the license terms.
5. Review the features to installed and where SQL Server will be installed. Make any changes that are necessary for your environment.
6. Review the Instance Name and change if necessary.
7. Review the credentials that will be used for the services that will be created.
8. Review the Authentication Mode and the administrators that will be added to the Database Engine.
9. Review the Error Reporting settings. We encourage you to keep this option checked, as we review the product feedback on an ongoing basic. This helps us to continually improve SQL Server.
10. That’s it!
11. Check the SQL Server Express Version and Edition by executing the below TSQL query
SERVERPROPERTY(‘ProductVersion’) AS ProductVersion,
SERVERPROPERTY(‘ProductLevel’) AS ProductLevel,
SERVERPROPERTY(‘Edition’) AS Edition