Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.

Dismiss

multiple instances

3 views

Skip to first unread message

Adam Embrey

unread,

May 21, 2003, 11:59:21 AM5/21/03

Does anyone have any information of what kind of overhead I would be running
into with 5 instances of SQL 2000 on a 2 node a/p cluster?

Bill Cheng [MSFT]

unread,

May 22, 2003, 8:41:40 AM5/22/03

Hi Adam,

I found the following information from SQL Server Books Online, which may
be helpful.
Multiple Instances of SQL Server on a Failover Cluster
You can run only one instance of SQL Server on each virtual server of a SQL
Server failover cluster, although you can install up to 16 virtual servers
on a failover cluster. The instance can be either a default instance or a
named instance. The virtual server looks like a single computer to
applications connecting to that instance of SQL Server. When applications
connect to the virtual server, they use the same convention as when
connecting to any instance of SQL Server; they specify the virtual server
name of the cluster and the optional instance name (only needed for named
instances): virtualservername\instancename. For more information about
clustering, see Failover Clustering Architecture.

Failover clustering also supports multiple instances. Multiple instance
support makes it easier to build, install, and configure virtual servers in
a failover cluster. Applications can connect to each instance on a single
computer in much the same way as they connect to instances of SQL Server
running on multiple computers. For more information about virtual servers,
see Creating a Failover Cluster.

With multiple instance support, you can isolate work environments (for
example, testing from production) or volatile application environments and
provide different system administrators for each instance of SQL Server on
the same computer. For more information, see Multiple Instances of SQL
Server.

When you install multiple instances, each instance gets a unique set of:

System and user databases.

The SQL Server and SQL Server Agent services. For default instances, the
names of the services remain MSSQLServer and SQLServerAgent. For named
instances, the names of the services are changed to MSSQL$instancename and
SQLAgent$instancename, allowing them to be started and stopped
independently of the other instances on the server. The database engines
for the different instances are started and stopped using the associated
SQL Server service. The SQL Server Agent services manage scheduled events
for the associated instances of the database engine.

The registry keys associated with the database engine and the SQL Server
and SQL Server Agent services.

Network connection addresses so that applications can connect to specific
instances.

SQL Server 2000 depends on distinct registry keys and service names within
the failover cluster so that operations will continue correctly after a
failover. Therefore, the name you provide for the instance of SQL Server
2000, including the default instance, must be unique across all nodes in
the failover cluster, as well as across all virtual servers within the
failover cluster. For example, if all instances failed over to a single
server, their service names and registry keys would conflict. If INST1 is a
named instance on virtual server VIRTSRV1, there cannot be a named instance
INST1 on any node in the failover cluster, either as part of a failover
cluster configuration or as a stand-alone installation.

Additionally, you must use the VIRTUAL_SERVER\Instance-name string to
connect to a clustered instance of SQL Server 2000 running on a virtual
server. You cannot access the instance of SQL Server 2000 by using the
computer name that the clustered instance happens to reside on at any given
time. SQL Server 2000 does not listen on the IP address of the local
servers. It listens only on the clustered IP addresses created during the
setup of a virtual server for SQL Server 2000.

This posting is provided "AS IS" with no warranties, and confers no rights.

Regards,

Bill Cheng
Microsoft Support Engineer
--------------------
| From: "Adam Embrey" <aem...@aviagen.com>
| Subject: multiple instances
| Date: Wed, 21 May 2003 10:59:21 -0500
| Lines: 4
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2720.3000
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
| Message-ID: <eNkeyH7H...@TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.clustering
| NNTP-Posting-Host: na.aviagen.com 12.150.240.66
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.clustering:9981
| X-Tomcat-NG: microsoft.public.sqlserver.clustering

Bill Cheng [MSFT]

unread,

May 28, 2003, 1:44:11 AM5/28/03

Hi Adam,

Here is some more information on running multiple instances of SQL Server
2000:
1. BOL Topic: Managing AWE Memory

Running Multiple Instances

If you are running multiple instances of SQL Server 2000 on the same
computer, and each instance uses AWE memory, you must ensure the following:

- Each instance has a max server memory setting.

- The sum of the max server memory values for all the instances is less
than the amount of physical memory in the computer.

If the sum of the settings exceeds the physical memory on the computer,
some of the instances either will not start or will have less memory than
is specified in max server memory. For example, suppose a computer has 16
GB of physical RAM and has three instances of SQL Server 2000 running on
it. Furthermore, max server memory is set to 8 GB for each instance. If you
stop and restart all three instances:

- The first instance will start with the full amount of 8 GB of memory.

- The second instance will start, but with slightly less than 8 GB of
memory (up to 128 MB less).

- The third instance will start in dynamic memory mode and will have 128
MB or less memory available to it.

2. BOL Topic: Multiple Instance Recommendations

Before installing multiple instances of Microsoft SQL Server 2000 on the
computer, you should be aware of the resources each instance will be using.
Each instance acts like an individual server and yields resources only to
the operating system and not to other instances. For example, if instance1
needs more memory to run a query, it will not ask instance2 to yield but
will request a memory grant from the operating system.

If you have multiple instances installed on a single-CPU computer, with
both instances actively processing queries, expect a slowdown in the
queries because both instances will compete for CPU resources. In that
environment, a query that is resource intensive, such as one containing
JOIN with GROUP BY or ORDER BY clauses, may take twice as much time to run
as the same query on a single instance installed on a single-CPU computer.
This information is based on comparing the query execution on a single-CPU
computer with one instance to two instances on the same computer, with both
instances running the same CPU intensive operation simultaneously.

Installing multiple instances on a computer with low RAM leads to slower
query execution. For example, installing three instances on a server with
64MB of RAM will slow your queries significantly. You can expect that about
15 percent more time will be required to run the same query.

3. Other BOL topics of interest:

Working with Named and Multiple Instances of SQL Server 2000

4. Support Webcast on multi-instancing:

Support WebCast: Multi-instancing in Microsoft SQL Server 2000
<http://support.microsoft.com/default.aspx?scid=%2fservicedesks%2fwebcasts%2
fwc032201%2fwcblurb032201.asp>

This posting is provided "AS IS" with no warranties, and confers no rights.

Regards,

Bill Cheng
Microsoft Support Engineer
--------------------

0 new messages