What is an instance

Tony Girgenti (tony(nospam)@lakesideos.com) writes:
> I'm trying to understand what an SQL Server 2008 instance is. The
> documentation for SQL Server 2008 Books Online says "A copy of SQL Server
> running on a computer".
>
> From that it sounds like you only need one instance of SQL running on a
> computer to use many databases.
>
> Why would you need more than one instance of SQL?

There can be many reasons. For instance, at my client there is another
group that has a product that users multiple databases. All references to
stored procedures and tables reportedly include the database name. This
means that if they need a new test environment, they need to set up a
different instance.

Another reason could be security. Maybe you are hosting different
applications, and while you can set up so that users can only access
some databases, you get one more level of separate with different instance.

It could also be that you want to run different versions of SQL Server. That
could be both for testing purposes or in production. (One vendor requires
SQL 2008 for their product, while another vendor do not support it.)

> If thats the case then why does a program like Microsoft Office Accounting
> 2007 create it's own instance of SQL Server(MSSMLBIZ) on a computer that
> already has a instance of SQL Server(SQLEXPRESS)?

I don't use MOA, so I cannot tell. But maybe they think that SQLEXPRESS
could be used by other applications, they don't want clashes. Or they need
to have full control over the instance to guarantee that things would work.


--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: What is an instance