Friday, March 02, 2007

Sql Server 2005 - A look at Installation : Instances

About Sql Server 2005 Instances:
  • More than one Sql Server 2005 can be installed on the same server, each installation is an instance,
  • On the same machine you can have both Sql server 2000 and also Sql Server 2005 running side by side (like i do on my laptop),
  • When you install Sql Server 2005 you are asked whether you want it to be installed as a default instance (no name for the instance) or you want to give it a name.
  • During installation you can look at already existing instances as shown in the figure,

Why we need multiple instances?

  • If you are planning to have a test environment and a production environment i would recommend installing Sql Server Developer edition on another pc and use it as a test environment, i do not like having test and production on the same OS box. Then why do we need multiple instance support? which is a great question, my answer is below,
  • When you need to have multiple versions of sql server like 2000 & 2005 each run in their own instances,
  • When you need to give admin or sa access to two different administrators to maintain their own sql servers,
  • When you want to have another instance on the same OS where you can first test the service packs and ensure it works fine on the production OS and then do it to the other live instance,
  • When you install other software like Vault or Sharepoint Portal Server 2007 or Visual Studio IDE 2005 they install their own instances of sql server 2005 or Sql Server 2005 Express editions.

SqlServer 2005 - About Sql 2005 & various editions

Sql Server 2005 is a robust database which is a version upgrade after 5+ years, the previous one was Sql Server 2000 and the one before was Sql Server 7.0

Microsoft spent 5 years to come up with this next release which has a lot of cool features,

  • Improvements in performance,
  • More analysis tools,
  • Integrated development with cool dot net IDE and management studio,
  • CLR capabilities,
  • Http end points,
  • Support for service broker and SOA,
  • Better Sql jobs control with the new Sql Agent,
  • Database Snapshots feature,
  • Great database mirroring,
  • Log shipping feature,
  • Replication improvements,
  • and lots n lots of T-Sql and Sql improvements.

I have been working with Sql 2000 for the past 5+ years, and it is one of my favourite databases, i had previous experience working with My Sql and i used to wonder why such a robust database like Sql did not have some good features which mysql had at that time. Example: it had paging at the backend level, you can specify in a query itself how many rows you want, even oracle had pagenum but sql lacked it in 2000 sql server.

The new sql 2005 is very promising and has made up for all the lacking features.

Sql Server 2005 is shipped free with VS Dot net IDE as sql express edition, it comes with sharepoint portal server 2007 and a bunch of other dot net applications. Any developer should learn the new features of Sql server 2005 and my entries would be helpful in that regard.

Sql Server comes in 5 editions:

  • Sql Server 2005 Enterprise edition,
  • Sql Server 2005 Standard edition,
  • Sql Server 2005 Work group edition,
  • Sql Server 2005 Developer edition (free for development and testing, exactly like EE),
  • Sql Server 2005 Express edition (free like former MSDE)

Best ways to learn sql server 2005:

1) Books online free download -

2) Free E-learning courses from Microsoft - (for a limited time it is free soon to end)

3) Official site -

4) Cost in $$ for Sql server 2005 -

5) Made up your mind try it for free (180 day evaluation) -

6) Edition comparisons -

7) Post a comment on my blog, if i know the answer will let you know :)