Sunday, January 18, 2009

Installing Microsoft SQL Server Express 2008

It requires some efforts to get Microsoft SQL Server Express 2008 to get running on a Vista SP1 machine with Microsoft SQL Server Express 2005 installed.

First, my machine wasn't always synchronize with latest windows updates and hot fixes.  I have Vista SP1 installed.  My machine has SQL Server Express 2005 installed.  I still need it in for my daily development.  I need both 2005 and 2008 services running at same time.

After download the SQL Server Express 2008, it will perform a pre-requisite validation to check if you have extra tools installed.  Here are a list of software that I have to install first before I can pass the SQL Server Express 2008 pre-requisite validation:

  1. .NET Framework 3.5 SP1
  2. Windows PowerShell 1.0 Installation Package for Windows Vista (KB928439)
  3. Windows Installer 4.5 Redistributable
  4. Uninstall SQL Server Management Studio for SQL Server 2005

After all the above tools installed, the SQL Server 2008 Express installation process is smooth.  However, there is one more step to get it right before both 2005 and 2008 services can co-exist in same machine.

By default, the default service instance name for both SQL Server 2005 and 2008  is same: "SQLEXPRESS".  You have to give distinct name for both service name.  For example, I name SQL Server 2005 and 2008 instance name as "SQLEXPRESS2005" AND "SQLEXPRESS2008" respectively.  After that, you may start both services in same machine at the same time.

SQL Server communicate on few network protocols. We may use "SQL Server Configuration Manager" to configure it.  Among them are:

  1. Shared Memory
  2. Named Pipes
  3. TCP/IP
  4. VIA

The TCP/IP protocol is using dynamic port by default.  We may continue using that provided we enable "SQL Server Browser".  We may use a connection string like "<ip-address>\<instance-name>" to make a connection to a SQL Server service bind to dynamic port.  For example: "\SQLEXPRESS2008".

One excellent feature with SQL Server 2008 I learned is the PowerShell.  It allows us to perform task in batch on the database entities.  For example, to delete all tables at once, we may issue "rm *".  A very good entry point for Unix/Linux users.

There is also SQL Server Compact that target on standalone or embedded desktop application.  It seems that SQL Server is getting more scalable for different application.

No comments: