Active/Passive > Active/Active

You mean going from a single instance cluster to a
multiple instance cluster (this is the correct
terminology).
It depends on your disk setup. Remember that each SQL
virtual server needs its own set of dedicated disks, not
to mention a dedicated disk for the quorum, and if you're
using W2K3, one for MS DTC.
Also, you need to ensure you have enough memory and
processor to handle the additional load.
I suggest you look at any of the numerous sources out
there, including the SQL 2K HA book by MS Press, or the
failover clustering whitepaper on Microsoft.com
Hi Allan,
Thanks for replying. I should make myself clearer.
Right now I have 2 Dell 6450's attached to EMC Clarion.
They are setup as Active/Passive cluster. The company bought 2
new Dell 6650's and I will be swapping out the servers one at a time to upgrade the H/W.
One of the developers wants to go Active/Active and the only way I think I can do this is uninstall SQL and uninstall the cluster, then reinstall the cluster and and reinstall SQL. If that's the case I think we'll stay with Active/Passive since we couldn
't afford the downtime.
"Allan Hirt" wrote:

> You mean going from a single instance cluster to a
> multiple instance cluster (this is the correct
> terminology).
> It depends on your disk setup. Remember that each SQL
> virtual server needs its own set of dedicated disks, not
> to mention a dedicated disk for the quorum, and if you're
> using W2K3, one for MS DTC.
> Also, you need to ensure you have enough memory and
> processor to handle the additional load.
> I suggest you look at any of the numerous sources out
> there, including the SQL 2K HA book by MS Press, or the
> failover clustering whitepaper on Microsoft.com
>
>
|||I think your developer misunderstands the whole 'active-passive...'
nomenclature. Do you want one or two virtual servers on your cluster? If
one, then it is 'active-passive' or 'single-instance'. Two or more means
'active-active' or 'multi-instance'. If you want to transition your
existing single instance to new hosts, that is a different issue.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"plane123" <plane123@.discussions.microsoft.com> wrote in message
news:305B32A8-6A53-4337-A2AE-48F60DE459CB@.microsoft.com...
> Hi Allan,
> Thanks for replying. I should make myself clearer.
> Right now I have 2 Dell 6450's attached to EMC Clarion.
> They are setup as Active/Passive cluster. The company bought 2
> new Dell 6650's and I will be swapping out the servers one at a time to
upgrade the H/W.
> One of the developers wants to go Active/Active and the only way I think I
can do this is uninstall SQL and uninstall the cluster, then reinstall the
cluster and and reinstall SQL. If that's the case I think we'll stay with
Active/Passive since we couldn't afford the downtime.[vbcol=seagreen]
>
> "Allan Hirt" wrote:
|||Thanks for your input.
I think in order for me to create an active/active setup, I need to reconfigure the shared disk, which I can't do, I'd disrupt all the other attached servers.
Thanks
"Geoff N. Hiten" wrote:

> I think your developer misunderstands the whole 'active-passive...'
> nomenclature. Do you want one or two virtual servers on your cluster? If
> one, then it is 'active-passive' or 'single-instance'. Two or more means
> 'active-active' or 'multi-instance'. If you want to transition your
> existing single instance to new hosts, that is a different issue.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "plane123" <plane123@.discussions.microsoft.com> wrote in message
> news:305B32A8-6A53-4337-A2AE-48F60DE459CB@.microsoft.com...
> upgrade the H/W.
> can do this is uninstall SQL and uninstall the cluster, then reinstall the
> cluster and and reinstall SQL. If that's the case I think we'll stay with
> Active/Passive since we couldn't afford the downtime.
>
>

Active/Passes Instances on a Single Virtual Server

Hi all,
Does anyone know if it possible to have active/passive
instances of SQL on a single virtual server? I'm
thinking not but if anyone has any info it would be much
appreciated.
Cheers,
Dave.
Are you asking if you can have an Active / Passive (now referred to as
Multi-Instance) cluster on a single physical server? If so since Clustering
is a hardware fail over solution so it would be mostly pointless. With that
said I have seen people use VMWare to simulate multiple servers for testing
purposes but there is no reason you would want to do this in a production
scenario.
Andrew J. Kelly SQL MVP
"Dave N" <anonymous@.discussions.microsoft.com> wrote in message
news:6dab01c48388$6c39ba50$a601280a@.phx.gbl...
> Hi all,
> Does anyone know if it possible to have active/passive
> instances of SQL on a single virtual server? I'm
> thinking not but if anyone has any info it would be much
> appreciated.
> Cheers,
> Dave.
|||I posted more out of curiosity to be honest. A colleague
asked if he could have a more than one SQL instance on a
single virtual server (i.e not create a new SQL instance
on a new virtual server on the same cluster). I'm not
talking at the hardware level no, at a SQL level. My
colleague states that you could then run the 2 SQL
instances in an active/passive mode. This would be in a
testing (dev) environment yes - purely theory work at the
minute.

>--Original Message--
>Are you asking if you can have an Active / Passive (now
referred to as
>Multi-Instance) cluster on a single physical server? If
so since Clustering
>is a hardware fail over solution so it would be mostly
pointless. With that
>said I have seen people use VMWare to simulate multiple
servers for testing
>purposes but there is no reason you would want to do
this in a production
>scenario.
>--
>Andrew J. Kelly SQL MVP
>
>"Dave N" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:6dab01c48388$6c39ba50$a601280a@.phx.gbl...
much
>
>.
>
|||Dave,
You can install SQL Server on a single-node cluster. However, each clustered
instance of SQL Server on that one machine must be installed with a new
virtual server name and IP.
Unless there's some subtlety I'm missing, I believe your colleague is wrong.
On a single node cluster, there is no other node of the cluster to fail over
to. In other words, the end result is Active, period, and no passive
whatsoever, because there's no second node. Or a better way to put it,
you're suggesting multi-instance SQL Server virtual servers on a single
node.
Also, as Andrew points out, there's no failover on a single-node cluster,
and so single-node clusters are normally meant only for testing and
learning.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Dave N" <anonymous@.discussions.microsoft.com> wrote in message
news:6e9901c48398$6ac157e0$a401280a@.phx.gbl...[vbcol=seagreen]
> I posted more out of curiosity to be honest. A colleague
> asked if he could have a more than one SQL instance on a
> single virtual server (i.e not create a new SQL instance
> on a new virtual server on the same cluster). I'm not
> talking at the hardware level no, at a SQL level. My
> colleague states that you could then run the 2 SQL
> instances in an active/passive mode. This would be in a
> testing (dev) environment yes - purely theory work at the
> minute.
> referred to as
> so since Clustering
> pointless. With that
> servers for testing
> this in a production
> message
> much
|||Ron,
Thanks for this. I am Dave's colleague who originally
posed the question. This was derived from the principle
that if clustering fails over pre-defined (cluster-aware)
resources why not failover between resource defined on one
node only. Since Dave posted this I have acrried out some
more investigations and beleive the SQL setup wizard is
limiting the installation to select resources on diffrent
nodes. If indeed it cannot be done it would be nice to
know if this is a limitation of MSCS or SQL Setup.
NB Although there is a BOL entry headed 'How to install a
one-node failover cluster' it just install a single
instance that doens't failover.
Many thanks,
Tim
>--Original Message--
>Dave,
>You can install SQL Server on a single-node cluster.
However, each clustered
>instance of SQL Server on that one machine must be
installed with a new
>virtual server name and IP.
>Unless there's some subtlety I'm missing, I believe your
colleague is wrong.
>On a single node cluster, there is no other node of the
cluster to fail over
>to. In other words, the end result is Active, period, and
no passive
>whatsoever, because there's no second node. Or a better
way to put it,
>you're suggesting multi-instance SQL Server virtual
servers on a single
>node.
>Also, as Andrew points out, there's no failover on a
single-node cluster,
>and so single-node clusters are normally meant only for
testing and
>learning.
>Hope this helps,
>Ron
>--
>Ron Talmage
>SQL Server MVP
>
>"Dave N" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:6e9901c48398$6ac157e0$a401280a@.phx.gbl...
colleague[vbcol=seagreen]
the[vbcol=seagreen]
If[vbcol=seagreen]
active/passive
>
>.
>
|||Tim,
I do not properly understand your statement. Can you please elaborate?
You wrote " SQL setup wizard is limiting the installation to select resources on diffrent nodes. If indeed it cannot be done it would be nice to know if this is a limitation of MSCS or SQL Setup."
Please give more detail. What type of resources you are talking about. AFAIK, in the SQL setup wizard the only resource that you select is the the shared disk resources. Yeah, if the disk resource you want to use is
being owned by another node then the setup cannot use it. This is not a limitation, its by design. Meaning Windows cluster uses the "shared nothing" topology where in only one node can own the disk.
You wrote "NB Although there is a BOL entry headed 'How to install a one-node failover cluster' it just install a single instance that doens't failover."
Thats true, you cannot failover because it is a single node cluster. Failover is between nodes and not on the same node.
BTW, if you have more than one shared disk on a single node cluster then you can have multiple virtual SQL Server 2000 (supported upto 16) -- each will require a seperate shared disk.
Additional Information:
====================
I highly recommend you to review the first two links to get an idea of what MSCS and SQL Failover Cluster is, what it can do and what it cannot do.
Windows Clustering: An Overview of Microsoft Clustering Technologies
http://support.microsoft.com/?id=325423
SQL Server 2000 Failover Clustering
http://www.microsoft.com/technet/pro.../failclus.mspx
Microsoft SQL Server 2000 High Availability Series
http://www.microsoft.com/technet/pro...y/sqlhalp.mspx
Introduction to Microsoft SQL Server 2000 Clustering
http://support.microsoft.com/default...lurb051001.asp
Microsoft SQL Server 2000 Virtual Server: Things You Should Know
http://support.microsoft.com/default...lurb032602.asp
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Active/Active/Active/Passive

Hi Thanks a lot for your quick response.
One more question. Can we have
Active/Active/Active/Passive talking to single database?

>--Original Message--
>We are using SQL2000 and considering clustering the
>solution. Could someone point us good documentation on
>clustering. We are particularly interested in
>Active/Active/Active/Passive
>Thanks in advance
>Amar
>.
>
No.
"Amar" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:2da4801c46a8d$88b60620$a401280a@.phx.gbl...[vbcol=seagreen]
> Hi Thanks a lot for your quick response.
> One more question. Can we have
> Active/Active/Active/Passive talking to single database?

Accuracy of Host column on SS 2000 Enterprise Manager Current Activity Process Info Screen

Yesterday and today, the Process Info screen in Enterprise Manager indicates that the "Host", a single workstation on our network, is generating around 50 connections to the database. This particular application is well-behaved and typically generates only 2 to 5 connections per instance. Furthermore, we cannot find this particular workstation/host on our network because we retired it off the network some time ago.

Anyone know of the accuracy of the Hosts column on this Enterprise Manager screen? We're suspecting that the network address column is correct but the Hosts column is incorrect. There are about 10 different network addresses pertaining to this particular Host (1-5 connections per network address).

Can you check the mAC Address for that host and find on the network?|||I have also been having that problem. I was able to use the MAC address to track down the PC when I first saw the problem, but found that the computer name was not what was displayed in SQL EM. Additionally, I am continuing to have the same problem but have found that different MAC addresses are being listed all with the same Host name. Should you find more information regarding this issue please post your results here.

Accuracy of Host column on SS 2000 Enterprise Manager Current Activity Process Info Screen

Yesterday and today, the Process Info screen in Enterprise Manager indicates that the "Host", a single workstation on our network, is generating around 50 connections to the database. This particular application is well-behaved and typically generates only 2 to 5 connections per instance. Furthermore, we cannot find this particular workstation/host on our network because we retired it off the network some time ago.

Anyone know of the accuracy of the Hosts column on this Enterprise Manager screen? We're suspecting that the network address column is correct but the Hosts column is incorrect. There are about 10 different network addresses pertaining to this particular Host (1-5 connections per network address).

Can you check the mAC Address for that host and find on the network?|||I have also been having that problem. I was able to use the MAC address to track down the PC when I first saw the problem, but found that the computer name was not what was displayed in SQL EM. Additionally, I am continuing to have the same problem but have found that different MAC addresses are being listed all with the same Host name. Should you find more information regarding this issue please post your results here.

accessing two databases from a single Stored Procedure

Is it possible to access another database from a single stored procedure of another database. If it is possible, please show how.

If both the DBs are on single Server and the current user have access permission on both database then you can use the following query..

Select SomeColumns From CurrentDBName..TableName
Select SomeColumns From OtherDBName..TableName

If the other database on different database Server then you have to use the Linked Server..

EXEC sp_addlinkedserver @.server = 'SERVER', @.provider = 'SQLOLEDB.1', @.srvproduct = '', @.provstr = 'Privider=SQLOLEDB.1;Data Source=TargetServer;Initial Catalog=Database'

go
Exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER', @.useself = true, @.locallogin = null, @.rmtuser = 'Userid', @.rmtpassword = 'Password'

On your SP you can use..

Select * From OpenQuery(MyRemoteServer, 'Select * From Sysobjects')

--OR

Select * From MyRemoteServer.DatabaseName.dbo.Sysobjects

|||

If the databases are on the same server just qualify the tables (in the database) with the database name

i.e.

Assume your stored procedure is in a database called MyDB. In your stored procedure, you can use the follwoing to access tables on from two different databases.

Select col1 from pubs.dbo.authors t1
Inner join adventurewords.dbo.sales t2 on t1.id = t2.id

If the database is on another server, you can create a linked server (and assuming security is OK).

Assume stored procedure is on a database called MyDB on server named Server1 and table you are trying to access is on Server2

Select * from [Server2].pubs.dbo.authors

|||

You can validate the Linked Server already present or not using..

select srvname From Master..sysservers

|||create procedure Proc_name as
begin
select * from database1..Tablename left join
select * from database2..Tablename
end

Accessing single value using tuple

Hello,

I tried to access a single cell value using tuple but am getting incorrect result. I know the value is there and the tuple exists because I was able to retrieve the value using allmembers. This is a simple MDX query, I hope someone can tell me what I did wrong.

SELECT non empty { [Measures].[Weight]} ON COLUMNS,

non empty

{

([Time].[Year].[2007] ,

[Category Type].[Raw Materials] ,

[Category].allmembers)

}

ON ROWS FROM [MCube]

The query above returns:

2007 Raw Materials All 2,450,000

2007 Raw Materials Yarn & Fiber 1,250,000

2007 Raw Materials Auxiliary Materials 1,200,000

As you can see from the query above that the weight for Auxiliary Materials for 2007 is 1.2M. However, the query below returns no result as if the tuple for Auxiliary Materials does not exist. What did I do wrong?

SELECT non empty { [Measures].[Weight]} ON COLUMNS,

non empty

{

([Time].[Year].[2007] ,

[Category Type].[Raw Materials] ,

[Category].[Auxiliary Materials])

}

ON ROWS FROM [MCube]

Thanks,

SB

Do you have same problem if you remove NON EMPTY from columns and rows? And you are using Analysis Services 2000, right?

Vidas Matelis

|||

Hi Vidas,

Yes, same problem without "non empty" on both row and column. The result is an empty cell with weight column heading and no row heading.

I am using Analysis Services 2005.

Thanks.

SB

|||

SouBee,

Could you please try to specifie full member qualifier: dimensionName.hierarchyName.Member .Try to do this in both statements (One that works, and second that don't). Example: [Category].[Category].AllMembers, etc. Maybe there is some name matching between hierarchy and level.

You can also try build similar query in any front end and see if results are then same inconstent. Capturing statement with SQL Profiler might also help.

Vidas Matelis

|||

Hi Vidas,

Thanks for taking the time to read my posts.I had greatly simplified my query to make it easier to understand. I apologize for not fully qualifying the dimensions. I know I can get the cube browser to return the resultset I need, the only problem is to get RS Query Browser to do the same. When "Aggregate(calculated member)" is used in the report, RS automatically modified the MDX query and caused a big chunk of the resultset to be truncated. Thanks for your suggestion to use SQL Profiler because I was able to capture the MDX query I need (with minor modification) from executing Analysis Service cube browser.

This is the MDX query generated by RS 2005, it truncates the resultset and produces incorrect result when Aggregate(calculated member) is used:

SELECT NON EMPTY { [Measures].[Weight] } ON COLUMNS,

NON EMPTY
{[Time].[Year].[Year].ALLMEMBERS *
[Raw Materials].[Category Type].[Category Type].ALLMEMBERS *
{[Raw Materials].[Category].[All]} *
{[Raw Materials].[Material Type].[All]},

[Time].[Year].[Year].ALLMEMBERS *
[Raw Materials].[Category Type].[Category Type].ALLMEMBERS *
[Raw Materials].[Category].[Category].ALLMEMBERS *
{[Raw Materials].[Material Type].[All]},

([Time].[Year].[Year].ALLMEMBERS *
[Raw Materials].[Category Type].[Category Type].ALLMEMBERS *
[Raw Materials].[Category].[Category].ALLMEMBERS *
[Raw Materials].[Material Type].[Material Type].ALLMEMBERS
)

} ON ROWS FROM [MCube]

This is the solution to my problem. It returns all data without truncating.

SELECT NON EMPTY { [Measures].[Weight} ON COLUMNS,

non empty

{

[Time].[Year].[Year].ALLMEMBERS *

[Raw Materials].[Category Type].[Category Type].ALLMEMBERS *

{[Raw Materials].[Category].[All]} *

{[Raw Materials].[Material Type].[All]},

[Time].[Year].[Year].ALLMEMBERS *

[Raw Materials].[Category Type].[Category Type].ALLMEMBERS *

[Raw Materials].[Category].[Category].ALLMEMBERS *

{[Raw Materials].[Material Type].[All]},

CROSSJOIN

(

AddCalculatedMembers([Time].[Year].[Year].MEMBERS),

CROSSJOIN

(

AddCalculatedMembers([Raw Materials].[Category Type].[Category Type].MEMBERS),

CROSSJOIN

(

AddCalculatedMembers([Raw Materials].[Category].[Category].MEMBERS),

AddCalculatedMembers([Raw Materials].[Material Type].[Material Type].MEMBERS)

)

)

)

}

ON ROWS FROM [MCube]

Thanks.

SB