Monday, March 26, 2012

ODBC Data Source On Clustered SQL Server

We created an ODBC datasource on a SQL Server in the cluster. Then there was
a hardware failover and subsequently unrelated DTS processes failed because
the data source wasn't defined on the failover machine. Even though those
processes did not use the ODBC source, in fact nothing used the ODBC source,
it was just created to be used in testing.
So, how do you create an ODBC datasouce that will be cluster-wide and not
just machine specific? Do you have to do it through the Cluster Management
console?
Thanks,
Bob
ODBC DSN's are not services and not applications; they are specifications,
typically stored in the registry. If you need it, just like mapped
networked drives, then you will need to create them separately on each
cluster node that requires them. There is no way to cluster them.
However, if you switched to using Data Links or ODBC file-based DSNs, then
you could store them on one of the shared clustered drives. In this case,
they would failover with the group, retain the same drive letter and path;
so, they would always be present with the virtual server.
On to another topic. Although we allow retaining the DTS package in the
MSDB repository and logging to that package, we do not allow the execution
of DTSRun on the DBMS servers. We require our clients to execute those on
an Application Server.
We try to minimize the number of external processes that run alongside the
DBMS memory space in order to maximize performance and system stability.
The last thing we want is for an external process to foul connected to an
internal SQL Server thread.
Sincerely,
Anthony Thomas

"Bob C" <BobC@.discussions.microsoft.com> wrote in message
news:65CC8979-C05A-43A9-92D6-9B156958E8CD@.microsoft.com...
> We created an ODBC datasource on a SQL Server in the cluster. Then there
was
> a hardware failover and subsequently unrelated DTS processes failed
because
> the data source wasn't defined on the failover machine. Even though those
> processes did not use the ODBC source, in fact nothing used the ODBC
source,
> it was just created to be used in testing.
> So, how do you create an ODBC datasouce that will be cluster-wide and not
> just machine specific? Do you have to do it through the Cluster Management
> console?
> Thanks,
> Bob

No comments:

Post a Comment