Active/Active Clustering - Help!

Hi,
Could someone please explain to me in an idiots guide kind of way!!! How
Active Active clustering works when the underlying Windows 2003 cluster
is Active\Passive using mscs with a central SAN.
I dont understand how you can run SQL on both nodes when only one node
can see the actual SAN resources at anyone time.
The scenario I have is I currently have a Windows 2003 Active/Passive
cluster with SQL 2000 running on 2 nodes with a SAN. We want to utilise
the other node to be able to run a another high end database that we are
developing to run along side the first one. However if one of the nodes
fail the both Databases should be able to run on one node until the
other is running again. For scalability purposes we may at some point
wish to add a third node if this is possible.
If someone can explain this to me and how to implement it from both
Windows point of view and SQL I would be grateful.
many thanks
A/A is not a SQL term, but it is a Windows Server 2003 Clustering term. That
part can be very confusing. I have no idea why SQL does not use the term,
but I will explain it anyways
In SQL you have instances of SQL. You can have 16 per cluster. 1 instance
will own the resources at a time. It's part of the shared nothing model. You
can run two or more instances on a node at a time. The other node does not
have to have any instances. Or you can run both nodes with 1 instance each,
giving one master each, and as many User DB's as needed each.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Jody Stoll" <jo@.mastercare.co.uk> wrote in message
news:e%23x7pauqEHA.3848@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Could someone please explain to me in an idiots guide kind of way!!! How
> Active Active clustering works when the underlying Windows 2003 cluster is
> Active\Passive using mscs with a central SAN.
> I dont understand how you can run SQL on both nodes when only one node can
> see the actual SAN resources at anyone time.
> The scenario I have is I currently have a Windows 2003 Active/Passive
> cluster with SQL 2000 running on 2 nodes with a SAN. We want to utilise
> the other node to be able to run a another high end database that we are
> developing to run along side the first one. However if one of the nodes
> fail the both Databases should be able to run on one node until the other
> is running again. For scalability purposes we may at some point wish to
> add a third node if this is possible.
> If someone can explain this to me and how to implement it from both
> Windows point of view and SQL I would be grateful.
> many thanks
|||Actually it is pretty simple. You install a complete second instance of SQL
with all new disk devices, IP addresses, and SQL Instance Name. Each
database instance is completely independent. The only issue you have is
making sure there is enough resources available, especially physical memory,
when you 'stack' the instances on the same host.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jody Stoll" <jo@.mastercare.co.uk> wrote in message
news:e%23x7pauqEHA.3848@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Could someone please explain to me in an idiots guide kind of way!!! How
> Active Active clustering works when the underlying Windows 2003 cluster
> is Active\Passive using mscs with a central SAN.
> I dont understand how you can run SQL on both nodes when only one node
> can see the actual SAN resources at anyone time.
> The scenario I have is I currently have a Windows 2003 Active/Passive
> cluster with SQL 2000 running on 2 nodes with a SAN. We want to utilise
> the other node to be able to run a another high end database that we are
> developing to run along side the first one. However if one of the nodes
> fail the both Databases should be able to run on one node until the
> other is running again. For scalability purposes we may at some point
> wish to add a third node if this is possible.
> If someone can explain this to me and how to implement it from both
> Windows point of view and SQL I would be grateful.
> many thanks
|||Geoff,
thanks for the swift reply, is there any chance you can detail the steps
that I have to go through in order to achieve this. Both nodes have
sufficient resources to run both databases on one node when that happens.
However , i'm still vague on the actual steps needed in order to achive this
as well as the steps taken with regards to the windows side of things as i
still dont understand how the 'passive' windows node can access the Shared
drives when it is 'active' as far as sql is concerned.
cheers
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e9LMLCvqEHA.1160@.tk2msftngp13.phx.gbl...
> Actually it is pretty simple. You install a complete second instance of
SQL
> with all new disk devices, IP addresses, and SQL Instance Name. Each
> database instance is completely independent. The only issue you have is
> making sure there is enough resources available, especially physical
memory,
> when you 'stack' the instances on the same host.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jody Stoll" <jo@.mastercare.co.uk> wrote in message
> news:e%23x7pauqEHA.3848@.TK2MSFTNGP14.phx.gbl...
>
|||"Jody Stoll" <jo@.mastercare.co.uk> wrote in message
news:e%23x7pauqEHA.3848@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Could someone please explain to me in an idiots guide kind of way!!! How
> Active Active clustering works when the underlying Windows 2003 cluster
> is Active\Passive using mscs with a central SAN.
> I dont understand how you can run SQL on both nodes when only one node
> can see the actual SAN resources at anyone time.
Either node can see resources on the SAN, but a resource can only be seen by
one node at a time.
In other words, if you create 6 LUNS on your SAN, you can make 3 available
to Node 1, 3 to Node2, or 4 to Node 1, 2 to Node 2, or even 6 to Node1 and
none to Node 2.
So, it's really a matter of how you slice up your SAN.
For example, in my current cluster I have the 3 disks in a Resource group
dedicated to SQL server on Node2 and my Quorom disk in a resource group on
Node1.
So, I think to answer your questions... carve more LUNS out of your SAN and
create a new resource group (with the disks, IPs, etc), move that to your
second node and go from there.

> The scenario I have is I currently have a Windows 2003 Active/Passive
> cluster with SQL 2000 running on 2 nodes with a SAN. We want to utilise
> the other node to be able to run a another high end database that we are
> developing to run along side the first one. However if one of the nodes
> fail the both Databases should be able to run on one node until the
> other is running again. For scalability purposes we may at some point
> wish to add a third node if this is possible.
> If someone can explain this to me and how to implement it from both
> Windows point of view and SQL I would be grateful.
> many thanks
|||"Jody" <jo@.mastercare.co.uk> wrote in message
news:ODWvcnxqEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Geoff,
> thanks for the swift reply, is there any chance you can detail the steps
> that I have to go through in order to achieve this. Both nodes have
> sufficient resources to run both databases on one node when that happens.
> However , i'm still vague on the actual steps needed in order to achive
this
> as well as the steps taken with regards to the windows side of things as i
> still dont understand how the 'passive' windows node can access the
Shared
> drives when it is 'active' as far as sql is concerned.
They use DIFFERENT drives.
|||Close, but that won't work in a cluster. The LUNS need to be visible to all
nodes in the cluster. The cluster service arbitrates ownership so only one
node can access each disk resource at a time. Active-Active is a holdover
from the SQL 7 clustering days when clustering was sort of hacked into the
product. The proper term is multi-instance.
Anyway, SAN resources are typically presented as one or more LUNS. Each LUN
maps to a physical disk in Windows and becomes a physical disk resource for
the cluster. Each LUN is managed independantly, unless you group them
together in a cluster resource group. That would be used for keeping
several disks together for use by a SQL instance.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:P1J8d.276564$bp1.172788@.twister.nyroc.rr.com. ..
> "Jody Stoll" <jo@.mastercare.co.uk> wrote in message
> news:e%23x7pauqEHA.3848@.TK2MSFTNGP14.phx.gbl...
> Either node can see resources on the SAN, but a resource can only be seen
by
> one node at a time.
> In other words, if you create 6 LUNS on your SAN, you can make 3 available
> to Node 1, 3 to Node2, or 4 to Node 1, 2 to Node 2, or even 6 to Node1 and
> none to Node 2.
> So, it's really a matter of how you slice up your SAN.
> For example, in my current cluster I have the 3 disks in a Resource group
> dedicated to SQL server on Node2 and my Quorom disk in a resource group on
> Node1.
> So, I think to answer your questions... carve more LUNS out of your SAN
and
> create a new resource group (with the disks, IPs, etc), move that to your
> second node and go from there.
>
>
|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:efUmEx6qEHA.1644@.tk2msftngp13.phx.gbl...
> Close, but that won't work in a cluster.
I wasn't clear.
You're right the LUNS have to be visiable to all nodes, but only one at a
time can "own" it. That's what I meant about "make visible"
Sorry for the confusion.

> The LUNS need to be visible to all
> nodes in the cluster. The cluster service arbitrates ownership so only
one
> node can access each disk resource at a time. Active-Active is a holdover
> from the SQL 7 clustering days when clustering was sort of hacked into the
> product. The proper term is multi-instance.
> Anyway, SAN resources are typically presented as one or more LUNS. Each
LUN
> maps to a physical disk in Windows and becomes a physical disk resource
for
> the cluster. Each LUN is managed independantly, unless you group them
> together in a cluster resource group. That would be used for keeping
> several disks together for use by a SQL instance.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message[vbcol=seagreen]
> news:P1J8d.276564$bp1.172788@.twister.nyroc.rr.com. ..
How[vbcol=seagreen]
cluster[vbcol=seagreen]
seen[vbcol=seagreen]
> by
available[vbcol=seagreen]
and[vbcol=seagreen]
group[vbcol=seagreen]
on[vbcol=seagreen]
> and
your[vbcol=seagreen]
utilise[vbcol=seagreen]
are[vbcol=seagreen]
nodes
>
|||Hi, Geoff
This is only useful if you have different DB devices.
How it works if you want to have the same up to date DB device on both
instances?
Regards,
Constantin
"Geoff N. Hiten" wrote:

> Actually it is pretty simple. You install a complete second instance of SQL
> with all new disk devices, IP addresses, and SQL Instance Name. Each
> database instance is completely independent. The only issue you have is
> making sure there is enough resources available, especially physical memory,
> when you 'stack' the instances on the same host.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jody Stoll" <jo@.mastercare.co.uk> wrote in message
> news:e%23x7pauqEHA.3848@.TK2MSFTNGP14.phx.gbl...
>
>
|||You can't do that with Microsoft and SQL clustering. SQL Clustering is a
failover technology designed as a component of a high-availability solution.
It is not a scale-out technology. If you need more processing power, you
have to buy a bigger box.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"C.Dimitrov" <CDimitrov@.discussions.microsoft.com> wrote in message
news:102B6086-7330-4D85-8791-7307D8CCF964@.microsoft.com...[vbcol=seagreen]
> Hi, Geoff
> This is only useful if you have different DB devices.
> How it works if you want to have the same up to date DB device on both
> instances?
> Regards,
> Constantin
> "Geoff N. Hiten" wrote:
SQL[vbcol=seagreen]
memory,[vbcol=seagreen]
How[vbcol=seagreen]
cluster[vbcol=seagreen]
utilise[vbcol=seagreen]
are[vbcol=seagreen]
nodes[vbcol=seagreen]

AcquireConnection returns null

Hi,

we are facing some issue to get the underlying OledbConnection from the runtime ConnectionManager.

below is the code sample that we are using

IDtsConnectionService conService = (IDtsConnectionService)this.serviceProvider.GetService(typeof(IDtsConnectionService));

if (conService == null)

return;

ArrayList conCollection = conService.GetConnectionsOfType("OLEDB");

for (int count = 0; count < conCollection.Count; count++)

{

string conName = ((ConnectionManager)conCollection[count]).Name;

if (conName == conMgrname)

{

conMgr = DtsConvert.ToConnectionManager90((ConnectionManager)conCollection[count]);

ConnectionManager cm = DtsConvert.ToConnectionManager(conMgr);

Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerAdoNet cmado = cm.InnerObject as Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerAdoNet;

OleDbConnection conn= cmado.AcquireConnection(null) as OleDbConnection;

}

}

In the sample above the conn is null.

To this query Darren replied to use the connection of type ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 to create the connection.

Your call to GetConnectionsOfType("OLEDB"); will return native OLE-DB connections, not ADO.NET connections, using the OleDbConnection.

The connection type for the ADo>NET OLE-DB connection is -

ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

but if user creates an New OLEDB Connection from the Connection Manager panel of the BIDS and selects the same from the custom UI how to get the underlying OLEDBConnection? the CreationName in this case is "OLEDB" and not ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089.

Replace the code inside the if branch with this:

ConnectionManager cm = conCollection[count] as ConnectionManager;

OleDbConnection conn= cm.AcquireConnection(null) as OleDbConnection;

HTH.

|||

Hi bob,

It still returns me null.

Thanks,

Dharmbir

|||

You are right. OLE DB connection manager uses native connections internally and the AcquireConnection method does not know how to cast it to managed OleDbConnection.

You may try if the following will give you the managed connection:

oleDbConnection = ((IDTSConnectionManagerDatabaseParameters90)connectionManager.InnerObject).GetConnectionForSchema() as OleDbConnection;

However, I am not confident you can use it in the execution time.

HTH.

|||

Hi Bob,

thanks a lot. it really worked fine.

now i have one more issue regarding the connection informaction. I am using OracleConnection at runtime and for this reason i want to fetch the user id, password and datasource information from the connectionstring of OledbConnection. but the connection string doesn't exposes the password.

Can you please help me out for this?

Thanks,

Dharmbir

|||There is no way to get a password from a connection manager because it is a sensitive information. I would go with limiting your component to only use ADO.NET OLE DB connections (the one you can get using AcquireConnection) or provide a way for the users to enter missing passwords.|||

Hi All!

I have a problem with AcquireConnection in c#... I wrote this code:

public override void AcquireConnections(object transaction)

{

if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)

{

ConnectionManager cm = DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);

ConnectionManagerAdoNet cmAdo = cm.InnerObject as ConnectionManagerAdoNet;

if (cmAdo == null)

throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO connection.");

this.conn = cmAdo.AcquireConnection(transaction) as OracleConnection;

}

but the 'conn' is ALWAYS null...

I try

this.conn = ((IDTSConnectionManagerDatabaseParameters90)cmAdo).GetConnectionForSchema() as OracleConnection;

too, but no result: the 'conn' is null again...

Nobody help me?

Martina

AcquireConnection returns null

Hi,

we are facing some issue to get the underlying OledbConnection from the runtime ConnectionManager.

below is the code sample that we are using

IDtsConnectionService conService = (IDtsConnectionService)this.serviceProvider.GetService(typeof(IDtsConnectionService));

if (conService == null)

return;

ArrayList conCollection = conService.GetConnectionsOfType("OLEDB");

for (int count = 0; count < conCollection.Count; count++)

{

string conName = ((ConnectionManager)conCollection[count]).Name;

if (conName == conMgrname)

{

conMgr = DtsConvert.ToConnectionManager90((ConnectionManager)conCollection[count]);

ConnectionManager cm = DtsConvert.ToConnectionManager(conMgr);

Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerAdoNet cmado = cm.InnerObject as Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerAdoNet;

OleDbConnection conn= cmado.AcquireConnection(null) as OleDbConnection;

}

}

In the sample above the conn is null.

To this query Darren replied to use the connection of type ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 to create the connection.

Your call to GetConnectionsOfType("OLEDB"); will return native OLE-DB connections, not ADO.NET connections, using the OleDbConnection.

The connection type for the ADo>NET OLE-DB connection is -

ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

but if user creates an New OLEDB Connection from the Connection Manager panel of the BIDS and selects the same from the custom UI how to get the underlying OLEDBConnection? the CreationName in this case is "OLEDB" and not ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089.

Replace the code inside the if branch with this:

ConnectionManager cm = conCollection[count] as ConnectionManager;

OleDbConnection conn= cm.AcquireConnection(null) as OleDbConnection;

HTH.

|||

Hi bob,

It still returns me null.

Thanks,

Dharmbir

|||

You are right. OLE DB connection manager uses native connections internally and the AcquireConnection method does not know how to cast it to managed OleDbConnection.

You may try if the following will give you the managed connection:

oleDbConnection = ((IDTSConnectionManagerDatabaseParameters90)connectionManager.InnerObject).GetConnectionForSchema() as OleDbConnection;

However, I am not confident you can use it in the execution time.

HTH.

|||

Hi Bob,

thanks a lot. it really worked fine.

now i have one more issue regarding the connection informaction. I am using OracleConnection at runtime and for this reason i want to fetch the user id, password and datasource information from the connectionstring of OledbConnection. but the connection string doesn't exposes the password.

Can you please help me out for this?

Thanks,

Dharmbir

|||There is no way to get a password from a connection manager because it is a sensitive information. I would go with limiting your component to only use ADO.NET OLE DB connections (the one you can get using AcquireConnection) or provide a way for the users to enter missing passwords.|||

Hi All!

I have a problem with AcquireConnection in c#... I wrote this code:

public override void AcquireConnections(object transaction)

{

if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)

{

ConnectionManager cm = DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);

ConnectionManagerAdoNet cmAdo = cm.InnerObject as ConnectionManagerAdoNet;

if (cmAdo == null)

throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO connection.");

this.conn = cmAdo.AcquireConnection(transaction) as OracleConnection;

}

but the 'conn' is ALWAYS null...

I try

this.conn = ((IDTSConnectionManagerDatabaseParameters90)cmAdo).GetConnectionForSchema() as OracleConnection;

too, but no result: the 'conn' is null again...

Nobody help me?

Martina

Accessing the underlying sql query from the rdl file

Our client uses the report builder to generate reports for collection of employees. We would like to use the employee records in this report to perform some additional processing (such as the list of employees gets assigned to a particular group).

Programmatically, I can retrieve a byte stream from rs.GetReportDefinition( "\myClientEmpReport") and deserialize the stream into Report object (as define by ReportDefinition.xsd).

I can then manually drill down and retrieve the SematicQuery xml from the commandText field.

The problem is how to convert the SemanticQuery format into a T-Sql query that I can run against the view that the report model is based off of.

Is this possible?

Thanks,

Arjay

P.S. Running SQL 2005 Reporting Services, VC# 2005, ASP.Net 2.0.

After attempting to autogenerate serialization classes on the SemanticQuery xml with Xsd and XsdObjectGen, I ended up hand coding some classes that handled recursion. From there, I was able to regenerate the sql query by walking through the filter and grouping sections. While this isn't a generic solution, it works well for my needs because the model I need this for is a single datasource derived from a view.

As a feature request, it sure would be nice to be able get the query string from the reporting engine directly.