active/passive with multiple instances

Our production enviornment consists of 2 production servers in an
active\passive set up. The production enviornment has 3 instances. Is it
possible that a single instance can fail over to the standby node and leaving
the other 2 behind? We think this is happeing but I thought if the server
fails over to the passive server then it would include all 3 instances (All
or nothing)
Thanks
Nope. Each instance has its own resource dependency list. If a resource
that is unique for a single instance times out, it will trip that instance
only for failover.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ronc" <ronc@.discussions.microsoft.com> wrote in message
news:9232DBCF-60C1-4A35-9A96-53594E2BC504@.microsoft.com...
> Our production enviornment consists of 2 production servers in an
> active\passive set up. The production enviornment has 3 instances. Is it
> possible that a single instance can fail over to the standby node and
> leaving
> the other 2 behind? We think this is happeing but I thought if the server
> fails over to the passive server then it would include all 3 instances
> (All
> or nothing)
> Thanks
|||That's why there are Resource Groups. Clearly, if the underlying hardware
has an issue, then all groups will failover, but that is just because they
share a common resource, not resource dependencies.
The cluster group, and typically the MS DTC, are left behind when the SQL
Server group fails over.
Each resource group is a separate virtual host and behaves independently
from the others.
Sincerely,
Anthony Thomas

"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:O9cbhWzBGHA.3528@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Nope. Each instance has its own resource dependency list. If a resource
> that is unique for a single instance times out, it will trip that instance
> only for failover.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "ronc" <ronc@.discussions.microsoft.com> wrote in message
> news:9232DBCF-60C1-4A35-9A96-53594E2BC504@.microsoft.com...
it[vbcol=seagreen]
server
>
|||Thus turning your cluster into active/active :-D
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:O9cbhWzBGHA.3528@.TK2MSFTNGP12.phx.gbl...
> Nope. Each instance has its own resource dependency list. If a resource
> that is unique for a single instance times out, it will trip that instance
> only for failover.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "ronc" <ronc@.discussions.microsoft.com> wrote in message
> news:9232DBCF-60C1-4A35-9A96-53594E2BC504@.microsoft.com...
>
|||No, thus leaving your cluster as a multi-instance cluster. There is
absolutely, no such thing as an active/active or active/passive SQL Server
cluster. Such a thing does not exist at all.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Kevin3NF" <Kevin@.DontNeedViagra3NF-inc.com> wrote in message
news:eV5VIU9EGHA.1464@.TK2MSFTNGP11.phx.gbl...
> Thus turning your cluster into active/active :-D
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:O9cbhWzBGHA.3528@.TK2MSFTNGP12.phx.gbl...
>
|||Unless you are still running SQL 7.0.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:ueVAb99EGHA.648@.TK2MSFTNGP14.phx.gbl...
> No, thus leaving your cluster as a multi-instance cluster. There is
> absolutely, no such thing as an active/active or active/passive SQL Server
> cluster. Such a thing does not exist at all.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Kevin3NF" <Kevin@.DontNeedViagra3NF-inc.com> wrote in message
> news:eV5VIU9EGHA.1464@.TK2MSFTNGP11.phx.gbl...
>
|||Yes...you are correct...but to date I have not heard anyone outside of
academia and Microsoft fora use
multi-instance. When they say Active/Active, we all know what they mean :-)
Sorry to have pushed your terminology button Mike :-)
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:ueVAb99EGHA.648@.TK2MSFTNGP14.phx.gbl...
> No, thus leaving your cluster as a multi-instance cluster. There is
> absolutely, no such thing as an active/active or active/passive SQL Server
> cluster. Such a thing does not exist at all.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Kevin3NF" <Kevin@.DontNeedViagra3NF-inc.com> wrote in message
> news:eV5VIU9EGHA.1464@.TK2MSFTNGP11.phx.gbl...
>
|||Personally I hate the term, sorry Mike. I like A/A much better. For example,
here at work we have a multi instance cluster. That does not tell you
anything, now if I said it was A/A/P would you guess it's a 3 node cluster
with 2 or more instances?
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
"Kevin3NF" <Kevin@.DontNeedViagra3NF-inc.com> wrote in message
news:e3rstAaFGHA.140@.TK2MSFTNGP12.phx.gbl...
> Yes...you are correct...but to date I have not heard anyone outside of
> academia and Microsoft fora use
> multi-instance. When they say Active/Active, we all know what they mean
> :-)
> Sorry to have pushed your terminology button Mike :-)
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:ueVAb99EGHA.648@.TK2MSFTNGP14.phx.gbl...
>
|||Despite my earlier jab, I am siding with Mike. Active-Active-P-A-... just
invites misunderstanding. New users expect clustering to be a scale out
technology based on this nomenclature. Explaining that it isn't and why
becomes discouraging. Single- and Multi-instance describes the cluster's
basic functionality, something that no combination of Active and Passive
does.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:%237mHGGaFGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Personally I hate the term, sorry Mike. I like A/A much better. For
> example, here at work we have a multi instance cluster. That does not tell
> you anything, now if I said it was A/A/P would you guess it's a 3 node
> cluster with 2 or more instances?
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://www.msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
>
> "Kevin3NF" <Kevin@.DontNeedViagra3NF-inc.com> wrote in message
> news:e3rstAaFGHA.140@.TK2MSFTNGP12.phx.gbl...
>
|||I guess it all boils down to what a true DBA/SQL Guru knows/believes (Geoff)
and a computer hacks knows (Me). Since I have only seen you wrong once in
the last few years, I will take the back seat here and let you drive
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:ut09M2eFGHA.3036@.tk2msftngp13.phx.gbl...
> Despite my earlier jab, I am siding with Mike. Active-Active-P-A-... just
> invites misunderstanding. New users expect clustering to be a scale out
> technology based on this nomenclature. Explaining that it isn't and why
> becomes discouraging. Single- and Multi-instance describes the cluster's
> basic functionality, something that no combination of Active and Passive
> does.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:%237mHGGaFGHA.1124@.TK2MSFTNGP10.phx.gbl...
>

Active Active Clustering

I am planning to install Multiple Instance SQL Server 2000 cluster (two instances) on Windows 2000 Advanced servers (two nodes). All the examples I have seen deal with Single Instance SQL Servers. What I have read that in case of a Multiple Instance ins
tallation you merely run the SQL Server setup program another time after the installation of the first virtual server is completed. I have also read that during the install you chose one of the nodes (one of the two Windows boxes) as the owner of one virt
ual SQL Server instance. Then when you run the SQL Server setup program a second time, you specify the other node as the owner of the other SQL Server virtual instance.
My question is this: where do I specify a node as the owner of a particular SQL Server instance? On the SQL Server 2000 Failover Clustering white paper, Appendix B, http://www.microsoft.com/technet/pro...x?pf=true#XSLT
section133121120120 step 10 shows a dialog box where you specify the configured nodes. Do I specify only one node as the Configured Node? Is that how I identify the node that owns the particular SQL Server instance? Or do I we identify it some other way
? Thanks.
I am sorry that I used the old terminology from SQL Server 7.0. Thanks in advance for your help.
|||You have a preferred owners list on the properties page of the resource
group. The host nodes are listed in preferred order. The actual ownership
can be changed by moving the group to a particular host. During setup, you
install SQL from the node that owns the disk resource where you intend to
put SQL. The dialog box you refer to selects the nodes that SQL is allowed
to run on. Once SQL is installed, the instance can be moved to any allowed
node.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"EY" <shafaqrahim@.hotmail.com> wrote in message
news:69BBB5B8-E23A-451F-B8FE-EAADD88A8E56@.microsoft.com...
> I am planning to install Multiple Instance SQL Server 2000 cluster (two
instances) on Windows 2000 Advanced servers (two nodes). All the examples I
have seen deal with Single Instance SQL Servers. What I have read that in
case of a Multiple Instance installation you merely run the SQL Server setup
program another time after the installation of the first virtual server is
completed. I have also read that during the install you chose one of the
nodes (one of the two Windows boxes) as the owner of one virtual SQL Server
instance. Then when you run the SQL Server setup program a second time, you
specify the other node as the owner of the other SQL Server virtual
instance.
> My question is this: where do I specify a node as the owner of a
particular SQL Server instance? On the SQL Server 2000 Failover Clustering
white paper, Appendix B,
http://www.microsoft.com/technet/pro...on133121120120
step 10 shows a dialog box where you specify the configured nodes. Do I
specify only one node as the Configured Node? Is that how I identify the
node that owns the particular SQL Server instance? Or do I we identify it
some other way? Thanks.
>
|||Thanks Mr. Hitten for answering my question so promptly.

accessing the same database using multiple threads

hi,

do i need to use specially synchronized code if i have multiple threads inserting, updating and reading rows to and from the same database? in this case, i know that no 2 threads will try to insert or update the exact same row into the DB, however, multiple threads might try to read the same row from the database.

thanks!

No, if the threads are using different conenction objects you can use your code as you already might have implemented it. In case of reusing a SQLConnection object (using an instance or a singleton object) you will have to make sure that the connection does not already has an open transction as parallel transactions are not allowed (if you

are using explicit transactions9

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

ok cool fanks!!

Accessing stored proc multiple return values

Hi,
I have a problem. I have two stored procs. One I am building currently
(sp_load) and another that is already in the data warehouse and which I
have no control over (sp_log_event).
sp_log_event is for control logging. It accepts a process name
parameter. It outputs 3 return parameters by issuing the following
command:
SELECT
load_id,
last_succ_load_id,
datEventDate
FROM
ctl_event_log_header
WHERE
load_Id = @.intLoadId
I am no expert on this but as I understand it these are technically not
output parameters. If I create an Execute SQL Task in DTS I have the
option of setting these 3 return values to my global variables in my
package - which is easy enough and I am already doing this.
However my problem is that I now need to call this (sp_log_event) from
within the stored proc I am creating (sp_load). Something like EXEC
MY_SP @.processname
If the return was an output parameter i could simply do EXEC MY_SP
@.processname, @.loadid output
Also if it was just one return value I could do
EXEC @.loadid = (MY_SP @.processname)
But it is neither of these scenarios and I can't work out how I can get
access to these 3 returned values from the confines of my procedure.
load id is a primary key so the select will definitely only return one
record. how do i get access to the 3 return variables and assign them
to variables within my stored proc (sp_load)evs
BOL has very good examples how to use storerd procedure that has a few
OUTPUT parameters
BTW , it is really bad practice to use sp_ prefix to name stored
procedures, because in that way SQL Server is going to check for system
stored procedures first
"evs" <evan.winstanley@.gmail.com> wrote in message
news:1149050325.045357.63970@.u72g2000cwu.googlegroups.com...
> Hi,
> I have a problem. I have two stored procs. One I am building currently
> (sp_load) and another that is already in the data warehouse and which I
> have no control over (sp_log_event).
> sp_log_event is for control logging. It accepts a process name
> parameter. It outputs 3 return parameters by issuing the following
> command:
> SELECT
> load_id,
> last_succ_load_id,
> datEventDate
> FROM
> ctl_event_log_header
> WHERE
> load_Id = @.intLoadId
>
> I am no expert on this but as I understand it these are technically not
> output parameters. If I create an Execute SQL Task in DTS I have the
> option of setting these 3 return values to my global variables in my
> package - which is easy enough and I am already doing this.
> However my problem is that I now need to call this (sp_log_event) from
> within the stored proc I am creating (sp_load). Something like EXEC
> MY_SP @.processname
> If the return was an output parameter i could simply do EXEC MY_SP
> @.processname, @.loadid output
> Also if it was just one return value I could do
> EXEC @.loadid = (MY_SP @.processname)
> But it is neither of these scenarios and I can't work out how I can get
> access to these 3 returned values from the confines of my procedure.
> load id is a primary key so the select will definitely only return one
> record. how do i get access to the 3 return variables and assign them
> to variables within my stored proc (sp_load)
>|||Hi Uri,
Thanks for the reply. First of all, what is BOL? :)
Second of all - I am not actually naming my stored procs like that. I
just used that for simplicity. They are actually USP_CTL_xxx and
USP_ETL_xxx
Cheers though.
Uri Dimant wrote:
> evs
> BOL has very good examples how to use storerd procedure that has a few
> OUTPUT parameters
> BTW , it is really bad practice to use sp_ prefix to name stored
> procedures, because in that way SQL Server is going to check for system
> stored procedures first
>
>
> "evs" <evan.winstanley@.gmail.com> wrote in message
> news:1149050325.045357.63970@.u72g2000cwu.googlegroups.com...|||evs
BOL -Books On Line (tool suppliedb by MS with SQL Server)

> just used that for simplicity. They are actually USP_CTL_xxx and
> USP_ETL_xxx
I was referencing to <(sp_log_event). from your previous post
"evs" <evan.winstanley@.gmail.com> wrote in message
news:1149052185.818089.227430@.h76g2000cwa.googlegroups.com...
> Hi Uri,
> Thanks for the reply. First of all, what is BOL? :)
> Second of all - I am not actually naming my stored procs like that. I
> just used that for simplicity. They are actually USP_CTL_xxx and
> USP_ETL_xxx
> Cheers though.
>
> Uri Dimant wrote:
>
>|||On 30 May 2006 21:38:45 -0700, evs wrote:

>Hi,
>I have a problem. I have two stored procs. One I am building currently
>(sp_load) and another that is already in the data warehouse and which I
>have no control over (sp_log_event).
>sp_log_event is for control logging. It accepts a process name
>parameter. It outputs 3 return parameters by issuing the following
>command:
>SELECT
> load_id,
> last_succ_load_id,
> datEventDate
>FROM
> ctl_event_log_header
>WHERE
> load_Id = @.intLoadId
>
>I am no expert on this but as I understand it these are technically not
>output parameters. If I create an Execute SQL Task in DTS I have the
>option of setting these 3 return values to my global variables in my
>package - which is easy enough and I am already doing this.
>However my problem is that I now need to call this (sp_log_event) from
>within the stored proc I am creating (sp_load). Something like EXEC
>MY_SP @.processname
>If the return was an output parameter i could simply do EXEC MY_SP
>@.processname, @.loadid output
>Also if it was just one return value I could do
>EXEC @.loadid = (MY_SP @.processname)
>But it is neither of these scenarios and I can't work out how I can get
>access to these 3 returned values from the confines of my procedure.
>load id is a primary key so the select will definitely only return one
>record. how do i get access to the 3 return variables and assign them
>to variables within my stored proc (sp_load)
Hi evs,
CREATE TABLE #tmp ( load_id -- datatype
, last_succ_load_id -- datatype
, datEventDate -- datatype
);
INSERT INTO #tmp (load_id, last_succ_load_id, datEventDate)
EXEC MY_SP @.processname;
SELECT load_id, last_succ_load_id, datEventDate
FROM #tmp;
DROP TABLE #tmp;
Hugo Kornelis, SQL Server MVP|||Hugo,
Thank you so much mate! Works perfectly. I was aware of temporary
tables I have just never used them before and didn't think of it as an
option. Thanks again.
Hugo Kornelis wrote:
> On 30 May 2006 21:38:45 -0700, evs wrote:
>
> Hi evs,
> CREATE TABLE #tmp ( load_id -- datatype
> , last_succ_load_id -- datatype
> , datEventDate -- datatype
> );
> INSERT INTO #tmp (load_id, last_succ_load_id, datEventDate)
> EXEC MY_SP @.processname;
> SELECT load_id, last_succ_load_id, datEventDate
> FROM #tmp;
> DROP TABLE #tmp;
> --
> Hugo Kornelis, SQL Server MVP

Accessing one database from multiple SQL servers

Apologies if this question has been asked before. I am about to upgrade a multiuser application based on Access97 and VB6 to C# and possibly SQL Server Express.

My customer does not want to use a server based database solution ie a single instance of SQL Server, but prefers to have a common networked dataset which is accessed via user's own individual database engines. This is because of restrictions on the installation of server based apps.

Does this scenario completely rule out the use of SQL Server Express?

Thanks

You can use SQL Server Express installed on each machine (it's pretty lightweight) with no problem, and even set up a replication between them if they need to share data. A better bet might be to use SQL Server Compact Edition, which is "embeddable". See here for more info:

http://www.microsoft.com/sql/editions/compact/default.mspx

Buck

|||Thanks. I 'll take a look at that.

Accessing multiple recordsets in MRS

Hi all,
Can anybody tell me how to acccess multiple recordsets in mrs. Please
read the following scenario.
I have written a Stored Procedure in SQL Server, this SP returns two
recordsets, suppose the sp name is my_test_sp (lets assign some alias
for those two recordsets as recordsrt1 and recordset2). I am creating a
report which is fetching data from my_test_sp, lets call this report as
my_test_report.
When I set the dataset properties of my_test_report to access
my_test_sp, the field list it generates contais fields that are from
recordset1, it dosenot display any field from recordset2.
Now here; is there any way i can access both recordset1 and recordset2
at a same time. It ll be ok if i create more than one datasets, but
then the question will be can i connect a singal data object like table
to multiple datasets at the same time? and will there be any link
between these two datasets?
I hope I am not missing anything at the design time. Your help will be
grately appriciated.
Thanks in advace. :)
Manoj SA few design issues to consider:
1. RS can only handle one result set at a time from a Stored Procedure (as
you have seen). If you want both you would need the SP to have a parameter
that tells it which to return and then call the SP twice.
2. RS does not associated datasets with one another.
3. Subreports are how you Master-detail
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Monty" <manojshirgurkar@.gmail.com> wrote in message
news:1164179273.330677.7870@.b28g2000cwb.googlegroups.com...
> Hi all,
> Can anybody tell me how to acccess multiple recordsets in mrs. Please
> read the following scenario.
> I have written a Stored Procedure in SQL Server, this SP returns two
> recordsets, suppose the sp name is my_test_sp (lets assign some alias
> for those two recordsets as recordsrt1 and recordset2). I am creating a
> report which is fetching data from my_test_sp, lets call this report as
> my_test_report.
> When I set the dataset properties of my_test_report to access
> my_test_sp, the field list it generates contais fields that are from
> recordset1, it dosenot display any field from recordset2.
> Now here; is there any way i can access both recordset1 and recordset2
> at a same time. It ll be ok if i create more than one datasets, but
> then the question will be can i connect a singal data object like table
> to multiple datasets at the same time? and will there be any link
> between these two datasets?
> I hope I am not missing anything at the design time. Your help will be
> grately appriciated.
> Thanks in advace. :)
> Manoj S
>|||you're talking about MARS right?
MARS was a scam; it doesn't work
I still can't open two datareaders on the same connection.. and that's
what it's advertised as fixing
those cocksuckers just need to go back to ADO; things have been buggy
as hell ever since they went down this ADO.net route.. I mean; what a
joke
-Aaron
Monty wrote:
> Hi all,
> Can anybody tell me how to acccess multiple recordsets in mrs. Please
> read the following scenario.
> I have written a Stored Procedure in SQL Server, this SP returns two
> recordsets, suppose the sp name is my_test_sp (lets assign some alias
> for those two recordsets as recordsrt1 and recordset2). I am creating a
> report which is fetching data from my_test_sp, lets call this report as
> my_test_report.
> When I set the dataset properties of my_test_report to access
> my_test_sp, the field list it generates contais fields that are from
> recordset1, it dosenot display any field from recordset2.
> Now here; is there any way i can access both recordset1 and recordset2
> at a same time. It ll be ok if i create more than one datasets, but
> then the question will be can i connect a singal data object like table
> to multiple datasets at the same time? and will there be any link
> between these two datasets?
> I hope I am not missing anything at the design time. Your help will be
> grately appriciated.
> Thanks in advace. :)
> Manoj S

Accessing Multiple databases from Stored procedure in SQL 2000

Hi ,

I have around 5 databases with same structure used to store data for different locations and services.

I have created an intermediate database which will have all the stored procedures to access data from above 5 databases.

My queries are common to all the databases. so,I would like to pass database name as an argument to my stored proc and execure query on specified database.

I tried this doing using "USE Databasename " Command. but it says ...We can not use "USE command " in stored proc or triggers. so ..what i did is ..

--

CREATE PROCEDURE TestDB_access(@.dbname varchar(100))
AS

BEGIN

DECLARE @.localDB varchar(100)

Set @.LocalDB=@.dbname

if @.LocalDB='XYZ'

EXEC ('USE XYZ ')
elseif @.LocalDB='ABC'

EXEC ('USE ABC')

Select * from Mytable

END

When I run this from my database , it gives me an error "unable to find table "mytable".

What is the best way to make my queries work with all the databases.

Thanks in advance

Venu Yankarla

Not sure what you are exactly trying to do but you can qualify a table with a database name and use it that way, however there is a limit in an SP of referring to 8 databases.

Try something like select * from Pubs..Authors where Pubs is the db name

|||

Thank you for your reply.

I can use the query "select * from Pubs..Authors where Pubs is the db name " if i know from which database i need to get the data.

In my case ... I want this dbname as a parameter to stored procedure. I can pass this to stored proc as parameter and bulid a string for SQL query and execute it. But I feel this as time consuming process as my queries will have atleast 5 joins in one query.

So , what I am looking for is .... Can we execute a query related to tables from a database (ABC or DEF or ETC) by using a stored proc in a database (XYZ)

Common thing in my case ..all databases has the same table structures but different data related to different locations.

in Query analyzer , we can execute queries on database (ABC or DEF) by staying in database (XYZ) using statement USE ABC and then execute SQL query.

USE ABC will change the database context and execute the query on "ABC" db tables.

Hope ..I am clear now..

Thanks

Venu yankarla

|||I think you will have to use sp_executesql for this