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
>

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 a
t
all times. I am hoping to spread the load (databases) evenly across the
servers. I have 150 databases on various sizes and transaction amounts, plu
s
a couple on control databases. I don't want to have the control databases i
n
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
>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[vbcol=seagreen]
>.
>|||You don't understand my question. The fail-over I'm not worried about and w
e
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
>sql

finding active users from sysprocesses

I am trying to find a select on sysprocesses that would list all the active
logins. An active login is a login that has a TSQL statment being executed
on the server,

This didnt work to well! Any ideas. Thanks in advance.

select sp.loginame,
-- more columns
from master..sysprocesses sp
where sp.status not in ('sleeping','background' )
order by 1You can use the system stored procedure sp_who & sp_who2 for these purposes.

--
- Anith
( Please reply to newsgroups only )|||kr (zzb26 (at) email.com) writes:
> I am trying to find a select on sysprocesses that would list all the
> active logins. An active login is a login that has a TSQL statment being
> executed on the server,
> This didnt work to well! Any ideas. Thanks in advance.
> select sp.loginame,
> -- more columns
> from master..sysprocesses sp
> where sp.status not in ('sleeping','background' )
> order by 1

In which way did it not work? In any case, you definitely want to add
"or opentrn > 0". A process which has a open transaction is active,
even if it is sleeping.

I have a lock-monitoring routine, and the condition I use is

upper(p.cmd) <> 'AWAITING COMMAND'

I also check whether sysprocesses.blocked > 0.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

find out active Sql statements

Hi,
Where does Sql server store the most current active T-SQL
statements? Is there a system table or stored procedure(documented or
undocumented)that will show you the active sql statements being
executed. For eg: in oracle you can query v$sql to see all the sql
statements executed by each session.
I know that you can use Enterprise manager, but was wondering if you
can run a query via query analyzer to look at it.
Any help is appreciated.

Thanks
Geetha<gdabbara@.brownshoe.com> wrote in message
news:1106264397.579113.143730@.c13g2000cwb.googlegr oups.com...
> Hi,
> Where does Sql server store the most current active T-SQL
> statements? Is there a system table or stored procedure(documented or
> undocumented)that will show you the active sql statements being
> executed. For eg: in oracle you can query v$sql to see all the sql
> statements executed by each session.
> I know that you can use Enterprise manager, but was wondering if you
> can run a query via query analyzer to look at it.
> Any help is appreciated.
> Thanks
> Geetha

Check out fn_get_sql() in Books Online - if it isn't there, you'll need to
download the latest BOL version from Microsoft:

http://www.microsoft.com/sql/techin.../2000/books.asp

DBCC INPUTBUFFER is another option, but it can only display the first 255
characters of whatever batch is being executed.

Simon|||[posted and mailed]

(gdabbara@.brownshoe.com) writes:
> Where does Sql server store the most current active T-SQL
> statements? Is there a system table or stored procedure(documented or
> undocumented)that will show you the active sql statements being
> executed. For eg: in oracle you can query v$sql to see all the sql
> statements executed by each session.
> I know that you can use Enterprise manager, but was wondering if you
> can run a query via query analyzer to look at it.

Simon pointed you to the basics. To get it all nicely packaged, I
have stored procedure aba_lockinfo that will give you the information.
Have a look at http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks you all for the information. It was very helpful.