Hello y'all,
I am trying to find out if there is code to find out all the MS Servers installed over a Network.
ThanksDownload SQLScan.
http://www.microsoft.com/downloads/details.aspx?FamilyID=9552d43b-04eb-4af9-9e24-6cde4d933600&displaylang=ensql
Showing posts with label servers. Show all posts
Showing posts with label servers. Show all posts
Thursday, March 29, 2012
Finding installed MS Server in PCs over Network
Hello y'all,
I am trying to find out if there is code to find out all the MS Servers installed over a Network.
ThanksPlease don't double post.|||There are a number of ways to do it, all with their own pluses and minuses. Are you thinking in terms of AD (and if so one domain or many), polling, or something else? LAN, WAN, or other? Approximately how many servers, and how are they managed? Will you be able to administer them from the PC where you want to enumerate them, or is this a "drive by" poll?
If you are looking for the "shoot low boys, they're riding shetland ponies" approach, check out SQLDMO (http://support.microsoft.com/default.aspx?scid=kb;en-us;287737).
-PatP
I am trying to find out if there is code to find out all the MS Servers installed over a Network.
ThanksPlease don't double post.|||There are a number of ways to do it, all with their own pluses and minuses. Are you thinking in terms of AD (and if so one domain or many), polling, or something else? LAN, WAN, or other? Approximately how many servers, and how are they managed? Will you be able to administer them from the PC where you want to enumerate them, or is this a "drive by" poll?
If you are looking for the "shoot low boys, they're riding shetland ponies" approach, check out SQLDMO (http://support.microsoft.com/default.aspx?scid=kb;en-us;287737).
-PatP
Wednesday, March 28, 2012
Finding database on virtual servers
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.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
>
>
> >--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.
> >
> >.
> >
>|||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:
>> 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.
>> >
>> >.
>> >
>.
>|||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
>
> >--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:
> >
> >> 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.
> >> >
> >> >.
> >> >
> >>
> >.
> >
>|||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
>
> >--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:
> >
> >> 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.
> >> >
> >> >.
> >> >
> >>
> >.
> >
>
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.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
>
>
> >--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.
> >
> >.
> >
>|||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:
>> 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.
>> >
>> >.
>> >
>.
>|||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
>
> >--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:
> >
> >> 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.
> >> >
> >> >.
> >> >
> >>
> >.
> >
>|||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
>
> >--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:
> >
> >> 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.
> >> >
> >> >.
> >> >
> >>
> >.
> >
>
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
>
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
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
Monday, March 19, 2012
Find SQL Servers 2000 & 2005 on the net
In order to find SQL Servers 2000, in my .NET application, I successfully use
"SQLBrowseConnect" from ODBC32.DLL. But this doesn't seem to find instances
of SQL Servers 2005.
Is there anything else available to do this job? Thanks for any hint.
Axel Hecker
To see SQL 2005 instances using enumeration APIs, the SQL Browser service
needs to be running on each SQL 2005 server and the instance not explicitly
hidden.
Hope this helps.
Dan Guzman
SQL Server MVP
"AxelHecker" <AxelHecker@.discussions.microsoft.com> wrote in message
news:73AB9D63-3B29-4926-A2F8-0330DDFF5D11@.microsoft.com...
> In order to find SQL Servers 2000, in my .NET application, I successfully
> use
> "SQLBrowseConnect" from ODBC32.DLL. But this doesn't seem to find
> instances
> of SQL Servers 2005.
> Is there anything else available to do this job? Thanks for any hint.
> Axel Hecker
>
|||Thanks for your reply!
SQL Browser service is running, and the instance is not hidden. But I think
I found out what was wrong: I assumed that SQLBrowseConnect would also find
local instances (which is not true).
Thanks anyway,
Axel Hecker
"Dan Guzman" schrieb:
> To see SQL 2005 instances using enumeration APIs, the SQL Browser service
> needs to be running on each SQL 2005 server and the instance not explicitly
> hidden.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "AxelHecker" <AxelHecker@.discussions.microsoft.com> wrote in message
> news:73AB9D63-3B29-4926-A2F8-0330DDFF5D11@.microsoft.com...
>
>
"SQLBrowseConnect" from ODBC32.DLL. But this doesn't seem to find instances
of SQL Servers 2005.
Is there anything else available to do this job? Thanks for any hint.
Axel Hecker
To see SQL 2005 instances using enumeration APIs, the SQL Browser service
needs to be running on each SQL 2005 server and the instance not explicitly
hidden.
Hope this helps.
Dan Guzman
SQL Server MVP
"AxelHecker" <AxelHecker@.discussions.microsoft.com> wrote in message
news:73AB9D63-3B29-4926-A2F8-0330DDFF5D11@.microsoft.com...
> In order to find SQL Servers 2000, in my .NET application, I successfully
> use
> "SQLBrowseConnect" from ODBC32.DLL. But this doesn't seem to find
> instances
> of SQL Servers 2005.
> Is there anything else available to do this job? Thanks for any hint.
> Axel Hecker
>
|||Thanks for your reply!
SQL Browser service is running, and the instance is not hidden. But I think
I found out what was wrong: I assumed that SQLBrowseConnect would also find
local instances (which is not true).
Thanks anyway,
Axel Hecker
"Dan Guzman" schrieb:
> To see SQL 2005 instances using enumeration APIs, the SQL Browser service
> needs to be running on each SQL 2005 server and the instance not explicitly
> hidden.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "AxelHecker" <AxelHecker@.discussions.microsoft.com> wrote in message
> news:73AB9D63-3B29-4926-A2F8-0330DDFF5D11@.microsoft.com...
>
>
Find SQL Servers 2000 & 2005 on the net
In order to find SQL Servers 2000, in my .NET application, I successfully use
"SQLBrowseConnect" from ODBC32.DLL. But this doesn't seem to find instances
of SQL Servers 2005.
Is there anything else available to do this job? Thanks for any hint.
Axel HeckerTo see SQL 2005 instances using enumeration APIs, the SQL Browser service
needs to be running on each SQL 2005 server and the instance not explicitly
hidden.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"AxelHecker" <AxelHecker@.discussions.microsoft.com> wrote in message
news:73AB9D63-3B29-4926-A2F8-0330DDFF5D11@.microsoft.com...
> In order to find SQL Servers 2000, in my .NET application, I successfully
> use
> "SQLBrowseConnect" from ODBC32.DLL. But this doesn't seem to find
> instances
> of SQL Servers 2005.
> Is there anything else available to do this job? Thanks for any hint.
> Axel Hecker
>|||Thanks for your reply!
SQL Browser service is running, and the instance is not hidden. But I think
I found out what was wrong: I assumed that SQLBrowseConnect would also find
local instances (which is not true).
Thanks anyway,
Axel Hecker
"Dan Guzman" schrieb:
> To see SQL 2005 instances using enumeration APIs, the SQL Browser service
> needs to be running on each SQL 2005 server and the instance not explicitly
> hidden.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "AxelHecker" <AxelHecker@.discussions.microsoft.com> wrote in message
> news:73AB9D63-3B29-4926-A2F8-0330DDFF5D11@.microsoft.com...
> > In order to find SQL Servers 2000, in my .NET application, I successfully
> > use
> > "SQLBrowseConnect" from ODBC32.DLL. But this doesn't seem to find
> > instances
> > of SQL Servers 2005.
> >
> > Is there anything else available to do this job? Thanks for any hint.
> >
> > Axel Hecker
> >
>
>
"SQLBrowseConnect" from ODBC32.DLL. But this doesn't seem to find instances
of SQL Servers 2005.
Is there anything else available to do this job? Thanks for any hint.
Axel HeckerTo see SQL 2005 instances using enumeration APIs, the SQL Browser service
needs to be running on each SQL 2005 server and the instance not explicitly
hidden.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"AxelHecker" <AxelHecker@.discussions.microsoft.com> wrote in message
news:73AB9D63-3B29-4926-A2F8-0330DDFF5D11@.microsoft.com...
> In order to find SQL Servers 2000, in my .NET application, I successfully
> use
> "SQLBrowseConnect" from ODBC32.DLL. But this doesn't seem to find
> instances
> of SQL Servers 2005.
> Is there anything else available to do this job? Thanks for any hint.
> Axel Hecker
>|||Thanks for your reply!
SQL Browser service is running, and the instance is not hidden. But I think
I found out what was wrong: I assumed that SQLBrowseConnect would also find
local instances (which is not true).
Thanks anyway,
Axel Hecker
"Dan Guzman" schrieb:
> To see SQL 2005 instances using enumeration APIs, the SQL Browser service
> needs to be running on each SQL 2005 server and the instance not explicitly
> hidden.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "AxelHecker" <AxelHecker@.discussions.microsoft.com> wrote in message
> news:73AB9D63-3B29-4926-A2F8-0330DDFF5D11@.microsoft.com...
> > In order to find SQL Servers 2000, in my .NET application, I successfully
> > use
> > "SQLBrowseConnect" from ODBC32.DLL. But this doesn't seem to find
> > instances
> > of SQL Servers 2005.
> >
> > Is there anything else available to do this job? Thanks for any hint.
> >
> > Axel Hecker
> >
>
>
Find SQL Servers 2000 & 2005 on the net
In order to find SQL Servers 2000, in my .NET application, I successfully us
e
"SQLBrowseConnect" from ODBC32.DLL. But this doesn't seem to find instances
of SQL Servers 2005.
Is there anything else available to do this job? Thanks for any hint.
Axel HeckerTo see SQL 2005 instances using enumeration APIs, the SQL Browser service
needs to be running on each SQL 2005 server and the instance not explicitly
hidden.
Hope this helps.
Dan Guzman
SQL Server MVP
"AxelHecker" <AxelHecker@.discussions.microsoft.com> wrote in message
news:73AB9D63-3B29-4926-A2F8-0330DDFF5D11@.microsoft.com...
> In order to find SQL Servers 2000, in my .NET application, I successfully
> use
> "SQLBrowseConnect" from ODBC32.DLL. But this doesn't seem to find
> instances
> of SQL Servers 2005.
> Is there anything else available to do this job? Thanks for any hint.
> Axel Hecker
>|||Thanks for your reply!
SQL Browser service is running, and the instance is not hidden. But I think
I found out what was wrong: I assumed that SQLBrowseConnect would also find
local instances (which is not true).
Thanks anyway,
Axel Hecker
"Dan Guzman" schrieb:
> To see SQL 2005 instances using enumeration APIs, the SQL Browser service
> needs to be running on each SQL 2005 server and the instance not explicitl
y
> hidden.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "AxelHecker" <AxelHecker@.discussions.microsoft.com> wrote in message
> news:73AB9D63-3B29-4926-A2F8-0330DDFF5D11@.microsoft.com...
>
>
e
"SQLBrowseConnect" from ODBC32.DLL. But this doesn't seem to find instances
of SQL Servers 2005.
Is there anything else available to do this job? Thanks for any hint.
Axel HeckerTo see SQL 2005 instances using enumeration APIs, the SQL Browser service
needs to be running on each SQL 2005 server and the instance not explicitly
hidden.
Hope this helps.
Dan Guzman
SQL Server MVP
"AxelHecker" <AxelHecker@.discussions.microsoft.com> wrote in message
news:73AB9D63-3B29-4926-A2F8-0330DDFF5D11@.microsoft.com...
> In order to find SQL Servers 2000, in my .NET application, I successfully
> use
> "SQLBrowseConnect" from ODBC32.DLL. But this doesn't seem to find
> instances
> of SQL Servers 2005.
> Is there anything else available to do this job? Thanks for any hint.
> Axel Hecker
>|||Thanks for your reply!
SQL Browser service is running, and the instance is not hidden. But I think
I found out what was wrong: I assumed that SQLBrowseConnect would also find
local instances (which is not true).
Thanks anyway,
Axel Hecker
"Dan Guzman" schrieb:
> To see SQL 2005 instances using enumeration APIs, the SQL Browser service
> needs to be running on each SQL 2005 server and the instance not explicitl
y
> hidden.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "AxelHecker" <AxelHecker@.discussions.microsoft.com> wrote in message
> news:73AB9D63-3B29-4926-A2F8-0330DDFF5D11@.microsoft.com...
>
>
Find SQL Servers
Hi,
Is there a script or some tool that I can use to search for all the SQL
Servers running in my domain? Thanks.Please take a look here:
http://sqlserver2000.databases.aspf...my-network.html
Cheers,
Paul Ibison|||Thanks a lot
"Paul Ibison" wrote:
> Please take a look here:
> http://sqlserver2000.databases.aspf...my-network.html
> Cheers,
> Paul Ibison
>
>
Is there a script or some tool that I can use to search for all the SQL
Servers running in my domain? Thanks.Please take a look here:
http://sqlserver2000.databases.aspf...my-network.html
Cheers,
Paul Ibison|||Thanks a lot
"Paul Ibison" wrote:
> Please take a look here:
> http://sqlserver2000.databases.aspf...my-network.html
> Cheers,
> Paul Ibison
>
>
Find SQL Servers
Hi,
Is there a script or some tool that I can use to search for all the SQL
Servers running in my domain? Thanks.
Thanks a lot
"Paul Ibison" wrote:
> Please take a look here:
> http://sqlserver2000.databases.aspfaq.com/how-do-i-find-all-the-available-sql-servers-on-my-network.html
> Cheers,
> Paul Ibison
>
>
Is there a script or some tool that I can use to search for all the SQL
Servers running in my domain? Thanks.
Thanks a lot
"Paul Ibison" wrote:
> Please take a look here:
> http://sqlserver2000.databases.aspfaq.com/how-do-i-find-all-the-available-sql-servers-on-my-network.html
> Cheers,
> Paul Ibison
>
>
Find SQL Servers
Hi,
Is there a script or some tool that I can use to search for all the SQL
Servers running in my domain? Thanks.Please take a look here:
http://sqlserver2000.databases.aspfaq.com/how-do-i-find-all-the-available-sql-servers-on-my-network.html
Cheers,
Paul Ibison|||Thanks a lot
"Paul Ibison" wrote:
> Please take a look here:
> http://sqlserver2000.databases.aspfaq.com/how-do-i-find-all-the-available-sql-servers-on-my-network.html
> Cheers,
> Paul Ibison
>
>
Is there a script or some tool that I can use to search for all the SQL
Servers running in my domain? Thanks.Please take a look here:
http://sqlserver2000.databases.aspfaq.com/how-do-i-find-all-the-available-sql-servers-on-my-network.html
Cheers,
Paul Ibison|||Thanks a lot
"Paul Ibison" wrote:
> Please take a look here:
> http://sqlserver2000.databases.aspfaq.com/how-do-i-find-all-the-available-sql-servers-on-my-network.html
> Cheers,
> Paul Ibison
>
>
Find SQL Server Product key installed on server
Hello;
I am trying to get information of SQL Server licensed key which is installed on our servers. I am using SQL Server 2005 standard edition. Is there a way to find out the product key which is installed on servers. I did some searches and found some thirdparty software to get that information but I don't want to use them on production, is there a way which microsoft recommends.
ThanksHello;
I am trying to get information of SQL Server licensed key which is installed on our servers. I am using SQL Server 2005 standard edition. Is there a way to find out the product key which is installed on servers. I did some searches and found some thirdparty software to get that information but I don't want to use them on production, is there a way which microsoft recommends.
Thanks
mmmm. Do you really think that Micro$oft is going to recommend software that could be used by the unscrupulous to pirate their software? :confused:|||Microsoft has a tool that is normally used by bulk licensors of their software that is used for license reporting that does display this information. If you are using bulk licensing (where you deploy from a set of master images, then write a check directly to Microsoft for the copies you have in production use) then check with either your own license administrator (usually a domain admin) or with your Microsoft Sales Representative.
-PatP
I am trying to get information of SQL Server licensed key which is installed on our servers. I am using SQL Server 2005 standard edition. Is there a way to find out the product key which is installed on servers. I did some searches and found some thirdparty software to get that information but I don't want to use them on production, is there a way which microsoft recommends.
ThanksHello;
I am trying to get information of SQL Server licensed key which is installed on our servers. I am using SQL Server 2005 standard edition. Is there a way to find out the product key which is installed on servers. I did some searches and found some thirdparty software to get that information but I don't want to use them on production, is there a way which microsoft recommends.
Thanks
mmmm. Do you really think that Micro$oft is going to recommend software that could be used by the unscrupulous to pirate their software? :confused:|||Microsoft has a tool that is normally used by bulk licensors of their software that is used for license reporting that does display this information. If you are using bulk licensing (where you deploy from a set of master images, then write a check directly to Microsoft for the copies you have in production use) then check with either your own license administrator (usually a domain admin) or with your Microsoft Sales Representative.
-PatP
Find servers runnign reporting services
How can I enumerate only those servers running reporting services in
c#.
I can get a list of all SQL Server instances on the local network
using:
SmoApplication.EnumAvailableSqlServers(false)
How can i filter this list to include only those instances where
Reporting Services is running?
Thanks in advance
Dave ArkleyOn Sep 14, 7:06 am, daveark...@.wildair.net wrote:
> How can I enumerate only those servers running reporting services in
> c#.
> I can get a list of all SQL Server instances on the local network
> using:
> SmoApplication.EnumAvailableSqlServers(false)
> How can i filter this list to include only those instances where
> Reporting Services is running?
> Thanks in advance
> Dave Arkley
You can loop through the list and run a query on each SQL Server
Instance similar to the following to determine if SSRS is installed
(at least for the most part):
SELECT COUNT(*) FROM SYS.DATABASES WHERE [NAME] IN
('REPORTSERVER','REPORTSERVERTEMPDB');
If the query comes back w/a result of 2, then Reporting Services is
installed. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
c#.
I can get a list of all SQL Server instances on the local network
using:
SmoApplication.EnumAvailableSqlServers(false)
How can i filter this list to include only those instances where
Reporting Services is running?
Thanks in advance
Dave ArkleyOn Sep 14, 7:06 am, daveark...@.wildair.net wrote:
> How can I enumerate only those servers running reporting services in
> c#.
> I can get a list of all SQL Server instances on the local network
> using:
> SmoApplication.EnumAvailableSqlServers(false)
> How can i filter this list to include only those instances where
> Reporting Services is running?
> Thanks in advance
> Dave Arkley
You can loop through the list and run a query on each SQL Server
Instance similar to the following to determine if SSRS is installed
(at least for the most part):
SELECT COUNT(*) FROM SYS.DATABASES WHERE [NAME] IN
('REPORTSERVER','REPORTSERVERTEMPDB');
If the query comes back w/a result of 2, then Reporting Services is
installed. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Monday, March 12, 2012
Find out transactions not replicated
Hi,
I have set up a transactional replication with Publisher,
Distributor and Subscriber on diff servers.
I want to find out (if any) transactions that have not
been replicated from publisher to distributor , and from
distributor to subscriber.
Can you tell me which tables / sprocs I can use to find
this info.
Thank you
Shrikant
use the view MS_distributionstatus to figure out how many commands have to
be replicated; have a look at the UndelivCmdsInDistDB column. You can find
this view in the distribution database.
To get an idea of the commands remaining in the distribution database do
this.
1) connect to your subscriber and query this table.
declare @.varbinary varbinary(300)
select @.varbinary=transaction_timestamp From MSreplication_subscriptions
print @.varbinary
2) with the value for varbinary paste it into the below query - this are the
command waiting to be applied
use distribution
--select From msrepl_transactions where xact_seqno =@.varbinary
exec sp_browsereplcmds @.xact_seqno_start ='0x0001BD2A000055990010'
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"SP" <anonymous@.discussions.microsoft.com> wrote in message
news:0b7701c4e109$48cc99a0$a501280a@.phx.gbl...
> Hi,
> I have set up a transactional replication with Publisher,
> Distributor and Subscriber on diff servers.
> I want to find out (if any) transactions that have not
> been replicated from publisher to distributor , and from
> distributor to subscriber.
> Can you tell me which tables / sprocs I can use to find
> this info.
> Thank you
> Shrikant
I have set up a transactional replication with Publisher,
Distributor and Subscriber on diff servers.
I want to find out (if any) transactions that have not
been replicated from publisher to distributor , and from
distributor to subscriber.
Can you tell me which tables / sprocs I can use to find
this info.
Thank you
Shrikant
use the view MS_distributionstatus to figure out how many commands have to
be replicated; have a look at the UndelivCmdsInDistDB column. You can find
this view in the distribution database.
To get an idea of the commands remaining in the distribution database do
this.
1) connect to your subscriber and query this table.
declare @.varbinary varbinary(300)
select @.varbinary=transaction_timestamp From MSreplication_subscriptions
print @.varbinary
2) with the value for varbinary paste it into the below query - this are the
command waiting to be applied
use distribution
--select From msrepl_transactions where xact_seqno =@.varbinary
exec sp_browsereplcmds @.xact_seqno_start ='0x0001BD2A000055990010'
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"SP" <anonymous@.discussions.microsoft.com> wrote in message
news:0b7701c4e109$48cc99a0$a501280a@.phx.gbl...
> Hi,
> I have set up a transactional replication with Publisher,
> Distributor and Subscriber on diff servers.
> I want to find out (if any) transactions that have not
> been replicated from publisher to distributor , and from
> distributor to subscriber.
> Can you tell me which tables / sprocs I can use to find
> this info.
> Thank you
> Shrikant
Friday, March 9, 2012
Find out how much data was replicated
Hi
I use SQL Servers 2000 SP3 on Windows 2000 Advanced Server
SP4. I've set up a transactional replication w/o updating
between two servers, one of which is a publisher and the
other is a distributor/pull-subscriber. I wonder if SQL
Server has means of finding out how much data was
replicated (i.e. sent to the subscriber), say, during the
day. If it hasn't, are there any third-party tools, which
would allow me to do this?
--
Many thanks,
Oskcheck the replication monitor in EM it will display a "gross" level of
information processed by article published.
"Osk" wrote:
> Hi
> I use SQL Servers 2000 SP3 on Windows 2000 Advanced Server
> SP4. I've set up a transactional replication w/o updating
> between two servers, one of which is a publisher and the
> other is a distributor/pull-subscriber. I wonder if SQL
> Server has means of finding out how much data was
> replicated (i.e. sent to the subscriber), say, during the
> day. If it hasn't, are there any third-party tools, which
> would allow me to do this?
> --
> Many thanks,
> Osk
>|||Hi
What do you mean by "gross" level? I need to know how much
bytes were delivered to the subscriber during a time interval.
--
Thanks,
Osk
>--Original Message--
>check the replication monitor in EM it will display a
"gross" level of
>information processed by article published.
>"Osk" wrote:
>> Hi
>> I use SQL Servers 2000 SP3 on Windows 2000 Advanced Server
>> SP4. I've set up a transactional replication w/o updating
>> between two servers, one of which is a publisher and the
>> other is a distributor/pull-subscriber. I wonder if SQL
>> Server has means of finding out how much data was
>> replicated (i.e. sent to the subscriber), say, during the
>> day. If it hasn't, are there any third-party tools, which
>> would allow me to do this?
>> --
>> Many thanks,
>> Osk
>>
>.
>
I use SQL Servers 2000 SP3 on Windows 2000 Advanced Server
SP4. I've set up a transactional replication w/o updating
between two servers, one of which is a publisher and the
other is a distributor/pull-subscriber. I wonder if SQL
Server has means of finding out how much data was
replicated (i.e. sent to the subscriber), say, during the
day. If it hasn't, are there any third-party tools, which
would allow me to do this?
--
Many thanks,
Oskcheck the replication monitor in EM it will display a "gross" level of
information processed by article published.
"Osk" wrote:
> Hi
> I use SQL Servers 2000 SP3 on Windows 2000 Advanced Server
> SP4. I've set up a transactional replication w/o updating
> between two servers, one of which is a publisher and the
> other is a distributor/pull-subscriber. I wonder if SQL
> Server has means of finding out how much data was
> replicated (i.e. sent to the subscriber), say, during the
> day. If it hasn't, are there any third-party tools, which
> would allow me to do this?
> --
> Many thanks,
> Osk
>|||Hi
What do you mean by "gross" level? I need to know how much
bytes were delivered to the subscriber during a time interval.
--
Thanks,
Osk
>--Original Message--
>check the replication monitor in EM it will display a
"gross" level of
>information processed by article published.
>"Osk" wrote:
>> Hi
>> I use SQL Servers 2000 SP3 on Windows 2000 Advanced Server
>> SP4. I've set up a transactional replication w/o updating
>> between two servers, one of which is a publisher and the
>> other is a distributor/pull-subscriber. I wonder if SQL
>> Server has means of finding out how much data was
>> replicated (i.e. sent to the subscriber), say, during the
>> day. If it hasn't, are there any third-party tools, which
>> would allow me to do this?
>> --
>> Many thanks,
>> Osk
>>
>.
>
Find out how much data was replicated
Hi
I use SQL Servers 2000 SP3 on Windows 2000 Advanced Server
SP4. I've set up a transactional replication w/o updating
between two servers, one of which is a publisher and the
other is a distributor/pull-subscriber. I wonder if SQL
Server has means of finding out how much data was
replicated (i.e. sent to the subscriber), say, during the
day. If it hasn't, are there any third-party tools, which
would allow me to do this?
Many thanks,
Oskcheck the replication monitor in EM it will display a "gross" level of
information processed by article published.
"Osk" wrote:
> Hi
> I use SQL Servers 2000 SP3 on Windows 2000 Advanced Server
> SP4. I've set up a transactional replication w/o updating
> between two servers, one of which is a publisher and the
> other is a distributor/pull-subscriber. I wonder if SQL
> Server has means of finding out how much data was
> replicated (i.e. sent to the subscriber), say, during the
> day. If it hasn't, are there any third-party tools, which
> would allow me to do this?
> --
> Many thanks,
> Osk
>|||Hi
What do you mean by "gross" level? I need to know how much
bytes were delivered to the subscriber during a time interval.
Thanks,
Osk
>--Original Message--
>check the replication monitor in EM it will display a
"gross" level of
>information processed by article published.
>"Osk" wrote:
>
>.
>
I use SQL Servers 2000 SP3 on Windows 2000 Advanced Server
SP4. I've set up a transactional replication w/o updating
between two servers, one of which is a publisher and the
other is a distributor/pull-subscriber. I wonder if SQL
Server has means of finding out how much data was
replicated (i.e. sent to the subscriber), say, during the
day. If it hasn't, are there any third-party tools, which
would allow me to do this?
Many thanks,
Oskcheck the replication monitor in EM it will display a "gross" level of
information processed by article published.
"Osk" wrote:
> Hi
> I use SQL Servers 2000 SP3 on Windows 2000 Advanced Server
> SP4. I've set up a transactional replication w/o updating
> between two servers, one of which is a publisher and the
> other is a distributor/pull-subscriber. I wonder if SQL
> Server has means of finding out how much data was
> replicated (i.e. sent to the subscriber), say, during the
> day. If it hasn't, are there any third-party tools, which
> would allow me to do this?
> --
> Many thanks,
> Osk
>|||Hi
What do you mean by "gross" level? I need to know how much
bytes were delivered to the subscriber during a time interval.
Thanks,
Osk
>--Original Message--
>check the replication monitor in EM it will display a
"gross" level of
>information processed by article published.
>"Osk" wrote:
>
>.
>
Find out how much data was replicated
Hi
I use SQL Servers 2000 SP3 on Windows 2000 Advanced Server
SP4. I've set up a transactional replication w/o updating
between two servers, one of which is a publisher and the
other is a distributor/pull-subscriber. I wonder if SQL
Server has means of finding out how much data was
replicated (i.e. sent to the subscriber), say, during the
day. If it hasn't, are there any third-party tools, which
would allow me to do this?
Many thanks,
Osk
check the replication monitor in EM it will display a "gross" level of
information processed by article published.
"Osk" wrote:
> Hi
> I use SQL Servers 2000 SP3 on Windows 2000 Advanced Server
> SP4. I've set up a transactional replication w/o updating
> between two servers, one of which is a publisher and the
> other is a distributor/pull-subscriber. I wonder if SQL
> Server has means of finding out how much data was
> replicated (i.e. sent to the subscriber), say, during the
> day. If it hasn't, are there any third-party tools, which
> would allow me to do this?
> --
> Many thanks,
> Osk
>
|||Hi
What do you mean by "gross" level? I need to know how much
bytes were delivered to the subscriber during a time interval.
Thanks,
Osk
>--Original Message--
>check the replication monitor in EM it will display a
"gross" level of
>information processed by article published.
>"Osk" wrote:
>.
>
I use SQL Servers 2000 SP3 on Windows 2000 Advanced Server
SP4. I've set up a transactional replication w/o updating
between two servers, one of which is a publisher and the
other is a distributor/pull-subscriber. I wonder if SQL
Server has means of finding out how much data was
replicated (i.e. sent to the subscriber), say, during the
day. If it hasn't, are there any third-party tools, which
would allow me to do this?
Many thanks,
Osk
check the replication monitor in EM it will display a "gross" level of
information processed by article published.
"Osk" wrote:
> Hi
> I use SQL Servers 2000 SP3 on Windows 2000 Advanced Server
> SP4. I've set up a transactional replication w/o updating
> between two servers, one of which is a publisher and the
> other is a distributor/pull-subscriber. I wonder if SQL
> Server has means of finding out how much data was
> replicated (i.e. sent to the subscriber), say, during the
> day. If it hasn't, are there any third-party tools, which
> would allow me to do this?
> --
> Many thanks,
> Osk
>
|||Hi
What do you mean by "gross" level? I need to know how much
bytes were delivered to the subscriber during a time interval.
Thanks,
Osk
>--Original Message--
>check the replication monitor in EM it will display a
"gross" level of
>information processed by article published.
>"Osk" wrote:
>.
>
Sunday, February 26, 2012
Find databases in FULL recovery mode
I am maintaining the backend SQL 2000 Server for a number of Share
Point 2003 application servers. The problem I am having is that new
databases are created by the Administrators of the SharePoint app
which is not a problem, but nightly I run the Integrity and
Optimization jobs on all user sql databases and this fails because the
new databases are either in FULL recovery mode. I just want to keep
the recovery modes for all user db's in SIMPLE recovery.
Is there a script that would notify me of a new database created and
what recovery mode it is in? or maybe a nightly job that could find
all databases in Full mode except for the system dbs and then change
the user db from FULL to SIMPLE before my optimization job runs?
I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
is creating a databases it modifies it to FULL.
Thanks for any help,
Richard Rios
richardrios@.earthnewsdaily.com
Why don't you use
SELECT DATABASEPROPERTYEX('model' , 'recovery' )
Write an sp which picks database names from master..sysdatabase
Thanks
GYK
"Richard Rios" wrote:
> I am maintaining the backend SQL 2000 Server for a number of Share
> Point 2003 application servers. The problem I am having is that new
> databases are created by the Administrators of the SharePoint app
> which is not a problem, but nightly I run the Integrity and
> Optimization jobs on all user sql databases and this fails because the
> new databases are either in FULL recovery mode. I just want to keep
> the recovery modes for all user db's in SIMPLE recovery.
> Is there a script that would notify me of a new database created and
> what recovery mode it is in? or maybe a nightly job that could find
> all databases in Full mode except for the system dbs and then change
> the user db from FULL to SIMPLE before my optimization job runs?
> I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
> is creating a databases it modifies it to FULL.
> Thanks for any help,
> Richard Rios
> richardrios@.earthnewsdaily.com
>
Point 2003 application servers. The problem I am having is that new
databases are created by the Administrators of the SharePoint app
which is not a problem, but nightly I run the Integrity and
Optimization jobs on all user sql databases and this fails because the
new databases are either in FULL recovery mode. I just want to keep
the recovery modes for all user db's in SIMPLE recovery.
Is there a script that would notify me of a new database created and
what recovery mode it is in? or maybe a nightly job that could find
all databases in Full mode except for the system dbs and then change
the user db from FULL to SIMPLE before my optimization job runs?
I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
is creating a databases it modifies it to FULL.
Thanks for any help,
Richard Rios
richardrios@.earthnewsdaily.com
Why don't you use
SELECT DATABASEPROPERTYEX('model' , 'recovery' )
Write an sp which picks database names from master..sysdatabase
Thanks
GYK
"Richard Rios" wrote:
> I am maintaining the backend SQL 2000 Server for a number of Share
> Point 2003 application servers. The problem I am having is that new
> databases are created by the Administrators of the SharePoint app
> which is not a problem, but nightly I run the Integrity and
> Optimization jobs on all user sql databases and this fails because the
> new databases are either in FULL recovery mode. I just want to keep
> the recovery modes for all user db's in SIMPLE recovery.
> Is there a script that would notify me of a new database created and
> what recovery mode it is in? or maybe a nightly job that could find
> all databases in Full mode except for the system dbs and then change
> the user db from FULL to SIMPLE before my optimization job runs?
> I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
> is creating a databases it modifies it to FULL.
> Thanks for any help,
> Richard Rios
> richardrios@.earthnewsdaily.com
>
Find databases in FULL recovery mode
I am maintaining the backend SQL 2000 Server for a number of Share
Point 2003 application servers. The problem I am having is that new
databases are created by the Administrators of the SharePoint app
which is not a problem, but nightly I run the Integrity and
Optimization jobs on all user sql databases and this fails because the
new databases are either in FULL recovery mode. I just want to keep
the recovery modes for all user db's in SIMPLE recovery.
Is there a script that would notify me of a new database created and
what recovery mode it is in? or maybe a nightly job that could find
all databases in Full mode except for the system dbs and then change
the user db from FULL to SIMPLE before my optimization job runs?
I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
is creating a databases it modifies it to FULL.
Thanks for any help,
Richard Rios
richardrios@.earthnewsdaily.comWhy don't you use
SELECT DATABASEPROPERTYEX('model' , 'recovery' )
Write an sp which picks database names from master..sysdatabase
Thanks
GYK
"Richard Rios" wrote:
> I am maintaining the backend SQL 2000 Server for a number of Share
> Point 2003 application servers. The problem I am having is that new
> databases are created by the Administrators of the SharePoint app
> which is not a problem, but nightly I run the Integrity and
> Optimization jobs on all user sql databases and this fails because the
> new databases are either in FULL recovery mode. I just want to keep
> the recovery modes for all user db's in SIMPLE recovery.
> Is there a script that would notify me of a new database created and
> what recovery mode it is in? or maybe a nightly job that could find
> all databases in Full mode except for the system dbs and then change
> the user db from FULL to SIMPLE before my optimization job runs?
> I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
> is creating a databases it modifies it to FULL.
> Thanks for any help,
> Richard Rios
> richardrios@.earthnewsdaily.com
>
Point 2003 application servers. The problem I am having is that new
databases are created by the Administrators of the SharePoint app
which is not a problem, but nightly I run the Integrity and
Optimization jobs on all user sql databases and this fails because the
new databases are either in FULL recovery mode. I just want to keep
the recovery modes for all user db's in SIMPLE recovery.
Is there a script that would notify me of a new database created and
what recovery mode it is in? or maybe a nightly job that could find
all databases in Full mode except for the system dbs and then change
the user db from FULL to SIMPLE before my optimization job runs?
I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
is creating a databases it modifies it to FULL.
Thanks for any help,
Richard Rios
richardrios@.earthnewsdaily.comWhy don't you use
SELECT DATABASEPROPERTYEX('model' , 'recovery' )
Write an sp which picks database names from master..sysdatabase
Thanks
GYK
"Richard Rios" wrote:
> I am maintaining the backend SQL 2000 Server for a number of Share
> Point 2003 application servers. The problem I am having is that new
> databases are created by the Administrators of the SharePoint app
> which is not a problem, but nightly I run the Integrity and
> Optimization jobs on all user sql databases and this fails because the
> new databases are either in FULL recovery mode. I just want to keep
> the recovery modes for all user db's in SIMPLE recovery.
> Is there a script that would notify me of a new database created and
> what recovery mode it is in? or maybe a nightly job that could find
> all databases in Full mode except for the system dbs and then change
> the user db from FULL to SIMPLE before my optimization job runs?
> I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
> is creating a databases it modifies it to FULL.
> Thanks for any help,
> Richard Rios
> richardrios@.earthnewsdaily.com
>
Find databases in FULL recovery mode
I am maintaining the backend SQL 2000 Server for a number of Share
Point 2003 application servers. The problem I am having is that new
databases are created by the Administrators of the SharePoint app
which is not a problem, but nightly I run the Integrity and
Optimization jobs on all user sql databases and this fails because the
new databases are either in FULL recovery mode. I just want to keep
the recovery modes for all user db's in SIMPLE recovery.
Is there a script that would notify me of a new database created and
what recovery mode it is in? or maybe a nightly job that could find
all databases in Full mode except for the system dbs and then change
the user db from FULL to SIMPLE before my optimization job runs?
I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
is creating a databases it modifies it to FULL.
Thanks for any help,
Richard Rios
richardrios@.earthnewsdaily.comWhy don't you use
SELECT DATABASEPROPERTYEX('model' , 'recovery' )
Write an sp which picks database names from master..sysdatabase
Thanks
GYK
"Richard Rios" wrote:
> I am maintaining the backend SQL 2000 Server for a number of Share
> Point 2003 application servers. The problem I am having is that new
> databases are created by the Administrators of the SharePoint app
> which is not a problem, but nightly I run the Integrity and
> Optimization jobs on all user sql databases and this fails because the
> new databases are either in FULL recovery mode. I just want to keep
> the recovery modes for all user db's in SIMPLE recovery.
> Is there a script that would notify me of a new database created and
> what recovery mode it is in? or maybe a nightly job that could find
> all databases in Full mode except for the system dbs and then change
> the user db from FULL to SIMPLE before my optimization job runs?
> I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
> is creating a databases it modifies it to FULL.
> Thanks for any help,
> Richard Rios
> richardrios@.earthnewsdaily.com
>
Point 2003 application servers. The problem I am having is that new
databases are created by the Administrators of the SharePoint app
which is not a problem, but nightly I run the Integrity and
Optimization jobs on all user sql databases and this fails because the
new databases are either in FULL recovery mode. I just want to keep
the recovery modes for all user db's in SIMPLE recovery.
Is there a script that would notify me of a new database created and
what recovery mode it is in? or maybe a nightly job that could find
all databases in Full mode except for the system dbs and then change
the user db from FULL to SIMPLE before my optimization job runs?
I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
is creating a databases it modifies it to FULL.
Thanks for any help,
Richard Rios
richardrios@.earthnewsdaily.comWhy don't you use
SELECT DATABASEPROPERTYEX('model' , 'recovery' )
Write an sp which picks database names from master..sysdatabase
Thanks
GYK
"Richard Rios" wrote:
> I am maintaining the backend SQL 2000 Server for a number of Share
> Point 2003 application servers. The problem I am having is that new
> databases are created by the Administrators of the SharePoint app
> which is not a problem, but nightly I run the Integrity and
> Optimization jobs on all user sql databases and this fails because the
> new databases are either in FULL recovery mode. I just want to keep
> the recovery modes for all user db's in SIMPLE recovery.
> Is there a script that would notify me of a new database created and
> what recovery mode it is in? or maybe a nightly job that could find
> all databases in Full mode except for the system dbs and then change
> the user db from FULL to SIMPLE before my optimization job runs?
> I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
> is creating a databases it modifies it to FULL.
> Thanks for any help,
> Richard Rios
> richardrios@.earthnewsdaily.com
>
Subscribe to:
Posts (Atom)