Finding database on virtual servers

C, your cluster, is it active/active and your have
multiple SQL SERVER instances that you run concurrently
on BOTH boxes, active/active? or is only one node active
and the other is sitting there just in case
(active/passive)? I'm not clear on what you mean by
multiple instances? SQL instances I guess... Either
way, you'd have a virtual name that your application
would refer to. When the node failover, you still call
the virtual name and the cluster wil get you to the
active side of the cluster. Maybe I'm not getting your
question completly?!? Bruce

>--Original Message--
>I have a sql 2000 cluster (multiple instance) and I'm
going to move my
>databases over and spread them out over each instance.
Now when my web
>servers need to read/write info to the databases how
will they find which
>virtual server has the database it needs. Do I have to
build a connection
>string that says, first look here and if not then look
here. I be grateful
>for any ideas or suggestions.
>.
>
This is an active/active cluster. All instances of sql need to be running at
all times. I am hoping to spread the load (databases) evenly across the
servers. I have 150 databases on various sizes and transaction amounts, plus
a couple on control databases. I don't want to have the control databases in
one instance and the rest in another, because I will not be making efficient
use of my hardware.
"Bruce de Freitas" wrote:

> C, your cluster, is it active/active and your have
> multiple SQL SERVER instances that you run concurrently
> on BOTH boxes, active/active? or is only one node active
> and the other is sitting there just in case
> (active/passive)? I'm not clear on what you mean by
> multiple instances? SQL instances I guess... Either
> way, you'd have a virtual name that your application
> would refer to. When the node failover, you still call
> the virtual name and the cluster wil get you to the
> active side of the cluster. Maybe I'm not getting your
> question completly?!? Bruce
>
>
> going to move my
> Now when my web
> will they find which
> build a connection
> here. I be grateful
>
|||ok cool, active/active with multiple SQL instances...
I've done more active/passive with single instances, much
less complex then what you're trying to do. But, I
believe it works like this. Say you have two nodes and
you want to have 3 SQL instances active on NodeA (SQL1,
SQL2, SQL3) and 2 other SQL instances active on NodeB
(SQL4, SQL5). While you could play a game with switching
SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
it's much cleaner to have 5 instances on each node, so
the 2 on NodeA sit there as a failover for the 2 on NodeB
and 3 on NodeB sit there as failover for the 3 on NodeA.
You'd have 5 virtual names that your app refers to, but
10 SQL instances across the 2 boxes. If NodeB totally
crashed, then all 5 SQL instances on NodeA would be
active, until NodeB came back online. If you're trying
to do somethign along those lines, then I think you're ok
referring to each virtual name, and let the Cluster
Manager worry about directing SQL requests to the right
place. Bruce

>--Original Message--
>This is an active/active cluster. All instances of sql
need to be running at
>all times. I am hoping to spread the load (databases)
evenly across the
>servers. I have 150 databases on various sizes and
transaction amounts, plus
>a couple on control databases. I don't want to have the
control databases in
>one instance and the rest in another, because I will not
be making efficient[vbcol=seagreen]
>use of my hardware.
>"Bruce de Freitas" wrote:
concurrently[vbcol=seagreen]
active[vbcol=seagreen]
call[vbcol=seagreen]
your[vbcol=seagreen]
instance.[vbcol=seagreen]
to[vbcol=seagreen]
look
>.
>
|||You don't understand my question. The fail-over I'm not worried about and we
will be making called to virtual servers so it doesn't matter which box the
instances run on. My question is: Do my web servers "have" to know which
instance of SQL the database it needs is in or is there a way for the web
server to query and the instances to find where the database it need is?
"Bruce de Freitas" wrote:

> ok cool, active/active with multiple SQL instances...
> I've done more active/passive with single instances, much
> less complex then what you're trying to do. But, I
> believe it works like this. Say you have two nodes and
> you want to have 3 SQL instances active on NodeA (SQL1,
> SQL2, SQL3) and 2 other SQL instances active on NodeB
> (SQL4, SQL5). While you could play a game with switching
> SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
> it's much cleaner to have 5 instances on each node, so
> the 2 on NodeA sit there as a failover for the 2 on NodeB
> and 3 on NodeB sit there as failover for the 3 on NodeA.
> You'd have 5 virtual names that your app refers to, but
> 10 SQL instances across the 2 boxes. If NodeB totally
> crashed, then all 5 SQL instances on NodeA would be
> active, until NodeB came back online. If you're trying
> to do somethign along those lines, then I think you're ok
> referring to each virtual name, and let the Cluster
> Manager worry about directing SQL requests to the right
> place. Bruce
>
> need to be running at
> evenly across the
> transaction amounts, plus
> control databases in
> be making efficient
> concurrently
> active
> call
> your
> instance.
> to
> look
>
|||For the sake of simplicity, let's say you only have 2 virtual SS instances,
in an Active/Active configuration. Regardless of the current state of the
fail-over configuration--that is, whether or not the current host of the
virtual servers is the same or distributed across the physical nodes--you
would still need to have a connection to each virtual server.
Now, it would be possible to create a third SS virtual instance and make
this sort of the master, virtual server. If you created a control database
for your web server to always connect to, then you could establish linked
servers from this host to the other two. Then, if you always queried this
third server using four-part names, then you could establish queries from
the third sever but against either of the first two.
Also, on the third server, in your control database, you could create the
following VIEW:
CREATE dbo.VIEW ServerDatabases
AS
SELECT SERVER_NAME = 'SERVER1', DATABASE_NAME = CATALOG_NAME
FROM SERVER1.master.INFORMATION_SCHEMA.SCHEMATA
UNION ALL
SELECT SERVER_NAME = 'SERVER1', DATABASE_NAME = CATALOG_NAME
FROM SERVER2.master.INFORMATION_SCHEMA.SCHEMATA
GO
Then you could query this for a particular database to determine the server.
Then you would execute your query against SERVER3 but using the four-part
name against either SERVER1 or SERVER2 depending on where the database
lived.
SELECT SERVER_NAME
FROM SERVER3.controldatabase.dbo.ServerDatabases
WHERE DATABASE_NAME = 'MyDatabase'
Now, you do not necessarily need the third instance and could use either of
the first two to create this common control database. However, keep in mind
that if you run all of your queries through a common server, that one will
always be used for resources, even if the subsequent call is redirected to
either of the other two servers.
It is more complex, but you would be better off setting up a seperate
connection for each host you query.
Sincerely,
Anthony Thomas
"C-Ya" <CYa@.discussions.microsoft.com> wrote in message
news:8B94987E-51F0-4219-A9D4-DE351A9BD098@.microsoft.com...
You don't understand my question. The fail-over I'm not worried about and
we
will be making called to virtual servers so it doesn't matter which box the
instances run on. My question is: Do my web servers "have" to know which
instance of SQL the database it needs is in or is there a way for the web
server to query and the instances to find where the database it need is?
"Bruce de Freitas" wrote:

> ok cool, active/active with multiple SQL instances...
> I've done more active/passive with single instances, much
> less complex then what you're trying to do. But, I
> believe it works like this. Say you have two nodes and
> you want to have 3 SQL instances active on NodeA (SQL1,
> SQL2, SQL3) and 2 other SQL instances active on NodeB
> (SQL4, SQL5). While you could play a game with switching
> SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
> it's much cleaner to have 5 instances on each node, so
> the 2 on NodeA sit there as a failover for the 2 on NodeB
> and 3 on NodeB sit there as failover for the 3 on NodeA.
> You'd have 5 virtual names that your app refers to, but
> 10 SQL instances across the 2 boxes. If NodeB totally
> crashed, then all 5 SQL instances on NodeA would be
> active, until NodeB came back online. If you're trying
> to do somethign along those lines, then I think you're ok
> referring to each virtual name, and let the Cluster
> Manager worry about directing SQL requests to the right
> place. Bruce
>
> need to be running at
> evenly across the
> transaction amounts, plus
> control databases in
> be making efficient
> concurrently
> active
> call
> your
> instance.
> to
> look
>