active/active sql node

Hello,
This is my time to setup active/active SQL on two nodes cluster environment.
I am very familiar with active/passive in which setup one virtual name such
as virtualsql01 and publish to user. For active/active, it seems to me that
system does not allow me to have something like virtualsql01 and
virtualsql02 but it forces to key-in instant name like virtualsql01\xxxx.
My question is that how can we have something like virtualsql01 and
virtualsql02 in the same cluster environment. I believe it will be easy for
everyone in my place to call db server in this way.
Thanks.
Your VirtualSQL01 is the default instance for the cluster. Like a
stand-alone server, you only get one default instance, except it is per
cluster, not per node. I avoid the whole default name on a cluster and use
only named instances. That way, I don't have to explain to the developers
why the names look different.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"naruphon" <naruphon@.hotmail.com> wrote in message
news:O5OnMwY2FHA.3416@.tk2msftngp13.phx.gbl...
> Hello,
> This is my time to setup active/active SQL on two nodes cluster
> environment. I am very familiar with active/passive in which setup one
> virtual name such as virtualsql01 and publish to user. For active/active,
> it seems to me that system does not allow me to have something like
> virtualsql01 and virtualsql02 but it forces to key-in instant name like
> virtualsql01\xxxx. My question is that how can we have something like
> virtualsql01 and virtualsql02 in the same cluster environment. I believe
> it will be easy for everyone in my place to call db server in this way.
> Thanks.
>
|||Just want to add that if you already have virutalsql01 in a cluster, you
can't have virtualsql01\xxx in that same cluster. In other words, there can
be only a single SQL instance per virtual server.
Suffice it to say that you are not the only person puzzled by this peculiar
design choice. I for one would much prefer what you wanted, i.e. simply
virtualsql01, virutalsql02, and so on for different SQL instances in a
cluster.
But if you are willing to explicitly use port numbers, you don't have to
force your users to use the format of virtualserver\sqlinstance. Instead,
they can use <fqdn>,<port number> (e.g. nycsql01.nyc.myfirm.com,9876) to
connect. Some people consider using an explicit port number in a connection
string not to be best practice, I should note.
Linchi
"naruphon" <naruphon@.hotmail.com> wrote in message
news:O5OnMwY2FHA.3416@.tk2msftngp13.phx.gbl...
> Hello,
> This is my time to setup active/active SQL on two nodes cluster
> environment. I am very familiar with active/passive in which setup one
> virtual name such as virtualsql01 and publish to user. For active/active,
> it seems to me that system does not allow me to have something like
> virtualsql01 and virtualsql02 but it forces to key-in instant name like
> virtualsql01\xxxx. My question is that how can we have something like
> virtualsql01 and virtualsql02 in the same cluster environment. I believe
> it will be easy for everyone in my place to call db server in this way.
> Thanks.
>
|||Normally, on a stand-alone system, you would get 1 ServerName (default
instance) and then possibly several named instances ServerName\Instance1,
ServerName\Instance2, . . ., ServerName\Instance15, up to 15 named and 1
default, or 16 namedactually, you can have many more, but Microsoft only
supporting up to 16 total instances, named or default.
The problem with clusters is that each participating node looks like a stand
alone server in that each may host all instances; thus, the binaries and the
naming convention above still applies; however, for a cluster, you have to
create a virtual server resource group for each instance to support the Disk
(which is the critical factor), IP, and Network Name. It is the Network
Name, which knows nothing of SQL Server instances, that must each be unique
within the cluster installation.
So, you end up with unique network names because of clustering, which is a
restriction even before SQL Server is installed, and unique instance names
because of multiple concurrent SQL Server hosting on any one failover node.
Sincerely,
Anthony Thomas

"naruphon" <naruphon@.hotmail.com> wrote in message
news:O5OnMwY2FHA.3416@.tk2msftngp13.phx.gbl...
> Hello,
> This is my time to setup active/active SQL on two nodes cluster
environment.
> I am very familiar with active/passive in which setup one virtual name
such
> as virtualsql01 and publish to user. For active/active, it seems to me
that
> system does not allow me to have something like virtualsql01 and
> virtualsql02 but it forces to key-in instant name like virtualsql01\xxxx.
> My question is that how can we have something like virtualsql01 and
> virtualsql02 in the same cluster environment. I believe it will be easy
for
> everyone in my place to call db server in this way.
> Thanks.
>
|||Thanks to everyone, very straight to the point, keen and concise.
Naruphon
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%23AOg2vi2FHA.1576@.TK2MSFTNGP15.phx.gbl...
> Normally, on a stand-alone system, you would get 1 ServerName (default
> instance) and then possibly several named instances ServerName\Instance1,
> ServerName\Instance2, . . ., ServerName\Instance15, up to 15 named and 1
> default, or 16 namedactually, you can have many more, but Microsoft only
> supporting up to 16 total instances, named or default.
> The problem with clusters is that each participating node looks like a
> stand
> alone server in that each may host all instances; thus, the binaries and
> the
> naming convention above still applies; however, for a cluster, you have to
> create a virtual server resource group for each instance to support the
> Disk
> (which is the critical factor), IP, and Network Name. It is the Network
> Name, which knows nothing of SQL Server instances, that must each be
> unique
> within the cluster installation.
> So, you end up with unique network names because of clustering, which is a
> restriction even before SQL Server is installed, and unique instance names
> because of multiple concurrent SQL Server hosting on any one failover
> node.
> Sincerely,
>
> Anthony Thomas
>
> --
> "naruphon" <naruphon@.hotmail.com> wrote in message
> news:O5OnMwY2FHA.3416@.tk2msftngp13.phx.gbl...
> environment.
> such
> that
> for
>

Acessing SQL through a DOS login script

Does anybody know if it is possible to pass an NT Environment variable to an SQL Server and then return the results back to the DOS?

Example: when a user logs in, their username is stored in the environment variable %USERNAME%. I would like to send this to SQL and then look up their department and then depending on what department they are in, different drives are mapped.

I think this would be done with stored procedures, but I am quite new to these.

Many thanks in advance
Matt CottrellYes I believe you can, try this from DOS, substring in your login criteria

isql -Uxx -Pxx -Sxx -n -Q"PRINT '%USERNAME%'"

You can then create a stored procedure that "PRINTS" out the drive mapping

"NET USE D: \\Server\Home "

Have your script file pass the USERNAME in then redirect the output to a file and run the file from the DOS command script

Example
isql -Uxx -Pxx -Sxx -n -Q"sp_MakeMappings '%USERNAME%'" -oC:\temp\mapping.cmd

C:\temp\mapping.cmd

del C:\temp\mapping.cmd

Accessing SWebmObjectSet Objects

I'm working in an environment where domain structure and firewall rules only allow access to a SQL Server - including OS - via a SQL Server client connection. I'm attempting to collect various pieces of information for an inventory database that are not normally accessible through standard t-sql calls.

A specific example is collecting network adapter and IP information via WMI and sp_OAxxx procedures. I've been able to interface WMI and retrieve the SWebmObjectSet collection with the information I want, but I can't seem to get to the individual objects in the collection for two reasons. First, t-sql doesn't have any sort of "for each" construct that allows me to iterate through the objects. Second, the Item() method of SWebmObjectSet requires an object path that I haven't been able to enumerate.

I'm avoiding enabling xp_cmdshell in SQL 2005 so calls such as "ipconfig /all" are not at option at this point.

How can I access the individual objects in the collection via t-sql? Is there another technology I might use? Remember - I can only access via a standard SQL client.

Here is some code I've come up with so far.

-- INITIALIZE SCRIPT

DECLARE@.wmiLocatorINT,

@.wmiServicesINT,

@.wmiObjectSetINT,

@.wmiObjectCountINT,

@.wmiObjectINT,

@.wmiNetAdapterNameNVARCHAR(200),

@.wmiNetAdapterIPNVARCHAR(200),

@.loopIdxINT,

@.oleSourceNVARCHAR(500),

@.oldDescNVARCHAR(500),

@.rcBIGINT,

@.msgNVARCHAR(400)

-- INITIALIZE WMI COM OBJECTS

EXEC @.rc = master.dbo.sp_OACreate 'WbemScripting.SWbemLocator', @.wmiLocator OUTPUT

IF @.rc <> 0 BEGIN

PRINT 'Create WMI object failed'

RETURN

END ELSE BEGIN

EXEC @.rc = master.dbo.sp_OAMethod @.wmiLocator, 'ConnectServer', @.wmiServices OUTPUT, '.'

IF @.rc <> 0 BEGIN

EXEC master.dbo.sp_OADestroy @.wmiLocator

RETURN

END

END

-- COLLECT DESIRED DATA

EXEC@.rc = master.dbo.sp_OAMethod @.wmiServices, 'InstancesOf', @.wmiObjectSet OUTPUT, 'Win32_NetworkAdapterConfiguration'

EXEC@.wmiObjectCount= master.dbo.sp_OAGetProperty @.wmiObjectSet, 'Count', @.wmiObjectCount OUTPUT

SELECT@.loopIdx= 0

WHILE @.loopIdx < @.wmiObjectCount - 1 BEGIN

EXEC@.rc= master.dbo.sp_OAMethod @.wmiObjectSet, 'Item', @.wmiObject OUTPUT, @.loopIdx

IF @.rc <> 0 BEGIN

EXEC@.rc= master.dbo.sp_OAGetErrorInfo @.wmiObjectSet, @.oleSource OUTPUT, @.oldDesc OUTPUT

END ELSE BEGIN

EXEC@.rc= master.dbo.sp_OAGetProperty @.wmiObject, 'Caption', @.wmiNetAdapterName OUTPUT

EXEC@.rc= master.dbo.sp_OAGetProperty @.wmiObject, 'IPAddress', @.wmiNetAdapterIP OUTPUT

END

SELECT@.loopIdx= @.loopIdx + 1

END

-- CLEANUP

EXEC master.dbo.sp_OADestroy @.wmiServices

EXEC master.dbo.sp_OADestroy @.wmiLocator

Is it fair to assume that you have completely explored and discarded the various system metadata, security, stastical and configuration functions, as well as the ODBC functions?

Would it be possible to have a Windows Scheduler task that would freqently run, using SQLCmd.exe to populate a table in the server with the desired information?

|||

If you mean the SQL Server metadata, security, statistical and configuration functions, then yes I have. But it is entirely possible I missed something which is why I posted the question.

It is possible to use the Windows Scheduler as you noted. I'm not a fan of installing DBA utilities on every server I manage if I can get away with a centralized solution. Additionally, there are many devices in scope that have firewall rules preventing console access and/or file transfer mechanisms. In short, the only mechanism I have is a standard SQL client. That is not to say that I couldn't petition the security team for relaxed access, but security is king here and it would be a battle I would likely lose.

One solution I've considered is use of the xp_cmdshell. Enabling it in SQL 2005 is possible, but I would prefer to leave it alone if I can.

Bruce.

|||

This really sounds like a task for an administrative WMI script -controlled and executed by the system administrators, retrieving the data and storing it in a central server for you to access. I would think that such an approach would molify the net administrator's security concerns. Easily done with MOM/SMS or whatever monitoring/management software is being used.

Accessing Oracle Stored procedures from SQL2K

I have created a linked Oracle server in my SQL2000 environment. I can execute queries just fine. eg select * from DEVX..SCHEMA.TABLE where DEVX is the name of the linked server. However, I cannot seem to execute a stored procedure that is resident on th
e Oracle server
The procedure is called XOKHOUS and the method is UPDADR. It takes a number of parameters and returns a 0 or 1 as a scalar value.
I've tried:
declare @.result int
set @.result=DEVX..XOKHOUS.UPDADR(@.param1,@.param2)
with no luck. Suggestions? Thanks.
> I have created a linked Oracle server in my SQL2000 environment.
> I can execute queries just fine. eg select * from DEVX..SCHEMA.TABLE
> where DEVX is the name of the linked server. However, I cannot seem
> to execute a stored procedure that is resident on the Oracle server
> The procedure is called XOKHOUS and the method is UPDADR. It
> takes a number of parameters and returns a 0 or 1 as a scalar value.
> I've tried:
> declare @.result int
> set @.result=DEVX..XOKHOUS.UPDADR(@.param1,@.param2)
>
> with no luck. Suggestions? Thanks.
>
You can use the OPENQUERY() command to call remote stored procedures. For
more information, please consult your SQL Server Books Online.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Accessing Oracle Stored procedures from SQL2K

I have created a linked Oracle server in my SQL2000 environment. I can execu
te queries just fine. eg select * from DEVX..SCHEMA.TABLE where DEVX is the
name of the linked server. However, I cannot seem to execute a stored proce
dure that is resident on th
e Oracle server
The procedure is called XOKHOUS and the method is UPDADR. It takes a numbe
r of parameters and returns a 0 or 1 as a scalar value.
I've tried:
declare @.result int
set @.result=DEVX..XOKHOUS.UPDADR(@.param1,@.param2)
with no luck. Suggestions? Thanks.> I have created a linked Oracle server in my SQL2000 environment.
> I can execute queries just fine. eg select * from DEVX..SCHEMA.TABLE
> where DEVX is the name of the linked server. However, I cannot seem
> to execute a stored procedure that is resident on the Oracle server
> The procedure is called XOKHOUS and the method is UPDADR. It
> takes a number of parameters and returns a 0 or 1 as a scalar value.
> I've tried:
> declare @.result int
> set @.result=DEVX..XOKHOUS.UPDADR(@.param1,@.param2)
>
> with no luck. Suggestions? Thanks.
>
--
You can use the OPENQUERY() command to call remote stored procedures. For
more information, please consult your SQL Server Books Online.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Accessing Oracle Stored procedures from SQL2K

I have created a linked Oracle server in my SQL2000 environment. I can execute queries just fine. eg select * from DEVX..SCHEMA.TABLE where DEVX is the name of the linked server. However, I cannot seem to execute a stored procedure that is resident on the Oracle serve
The procedure is called XOKHOUS and the method is UPDADR. It takes a number of parameters and returns a 0 or 1 as a scalar value
I've tried
declare @.result in
set @.result=DEVX..XOKHOUS.UPDADR(@.param1,@.param2
with no luck. Suggestions? Thanks> I have created a linked Oracle server in my SQL2000 environment.
> I can execute queries just fine. eg select * from DEVX..SCHEMA.TABLE
> where DEVX is the name of the linked server. However, I cannot seem
> to execute a stored procedure that is resident on the Oracle server
> The procedure is called XOKHOUS and the method is UPDADR. It
> takes a number of parameters and returns a 0 or 1 as a scalar value.
> I've tried:
> declare @.result int
> set @.result=DEVX..XOKHOUS.UPDADR(@.param1,@.param2)
>
> with no luck. Suggestions? Thanks.
>
--
You can use the OPENQUERY() command to call remote stored procedures. For
more information, please consult your SQL Server Books Online.
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.