Multiple instances of MSSQL and IP addresses

Hi.
Not sure if this is the correct newsgroup (could not find another suitable g
roup), and if
not please redirect me to the proper one...
We're running multiple instances of MSSQL 2000 on a server. This is done to
easily
incorporate client DBs into our testing operations. The problem is that all
instances
share the same IP address (but use different ports). So we can access each i
nstance
separately as we want, but we would like it better that each instance is lis
tening on its
own IP address.
But we can't find a way to change the IP address a named instance is listeni
ng on.
Anybody know how to do this?
NOTE: we prefer not to be using Clustering and/or Virtual Server. This is a
single
development server used for testing client scenario's...
Tx.Hi
Does this mean you have a network card for each instance? In which case you
may be able to disable the ports you don't want to use in the network
configurations IP Filtering options. Alternatively you could do it at the
firewall/router.
John
"Arjan de Haan" wrote:

> Hi.
> Not sure if this is the correct newsgroup (could not find another suitable
group), and if
> not please redirect me to the proper one...
> We're running multiple instances of MSSQL 2000 on a server. This is done
to easily
> incorporate client DBs into our testing operations. The problem is that al
l instances
> share the same IP address (but use different ports). So we can access each
instance
> separately as we want, but we would like it better that each instance is l
istening on its
> own IP address.
> But we can't find a way to change the IP address a named instance is liste
ning on.
> Anybody know how to do this?
> NOTE: we prefer not to be using Clustering and/or Virtual Server. This is
a single
> development server used for testing client scenario's...
> Tx.
>
>
>|||"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:BFD4DDB0-FAC1-4E24-9C33-999F9AAF902E@.microsoft.com...
> Hi
> Does this mean you have a network card for each instance? In which case yo
u
> may be able to disable the ports you don't want to use in the network
> configurations IP Filtering options. Alternatively you could do it at the
> firewall/router.
> John
>
No, the server has (currently) just one NIC.
To better clarify things:
The server running the multiple instances of MSSQL can be reached through a
number of IP
addresses. Is it possible, either through changing settings in Windows 2000
(which is
running on the server) or by changing some settings of each MSSQL instance t
o have each
instance receive packets directed to a specific IP address. For example,
3 running instances of MSSQL
SQL_A, 'binds' to IP xxx.xxx.0.1, port 1140,
SQL_B, 'binds' to IP xxx.xxx.0.2, port 1140, and
SQL_C, 'binds' to IP xxx.xxx.0.3, port 1140.
Currently all instances of MSSQL can be reached through the same IP address
but by using
different port numbers, like this:
3 running instances of MSSQL
SQL_A, 'binds' to IP xxx.xxx.0.1, port 1140,
SQL_B, 'binds' to IP xxx.xxx.0.1, port 1143, and
SQL_C, 'binds' to IP xxx.xxx.0.1, port 1146.
We prefer the first setup described. Is this possible without resorting to v
irtual servers
and clustering?
Thanks for your help.
...Arjan...|||Hi
I don't think there is a way to bind to a specific IP address, you are
limited to blocking ports.
How are these IP addresses assigned?
John|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1115966034.360116.66440@.o13g2000cwo.googlegroups.com...
> Hi
> I don't think there is a way to bind to a specific IP address, you are
> limited to blocking ports.
> How are these IP addresses assigned?
>
John,
the server running the MSSQL instances has been assigned multiple IP address
es (visible
thru Network properties / TCP IP properties / Advanced properties). This was
apparently
done on the server itself.
Just checked but it is indeed reachable under all its assigned IP addresses.
...Arjan...|||Hi
I think you can only do this at the firewall then.
John|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1115985697.036330.184570@.g14g2000cwa.googlegroups.com...
> Hi
> I think you can only do this at the firewall then.
>
In another newsgroup they suggested I need multiple NICs in the server for t
his, and even
then they were not sure it could be done.
Thanks anyway for answering.
...Arjan...|||Hi
See http://support.microsoft.com/defaul...kb;en-us;816792
which states
Note When you select this check box, you enable filtering for all
adaptors, but you configure the filters individually for each adaptor.
The same filters do not apply to all adaptors.
Which is what I pointed you to in the first post. As it is at adaptor
level having multiple IP addresses for the one adapter will not work.
John

Multiple backslashes in physical file name

Accidentally, I've managed to have this as part of a Create Database:

... FILENAME = N'C:\MSSQL\Data\\\testdb_Log.LDF' ...

, notice the triple backslash. The Create Database statement works fine,
and sp_helpdb says the log file name is:

C:\MSSQL\Data\\\testdb_Log.LDF

I noticed the MSDOS command prompt also allows multiple backslashes,
they're reduced to one when performing the command and I guess
SQL Server does the same thing, so no problem so far really.

But is it supposed to work this way? Quite confusing, isn't it?Not sure if it supposed to work that way, but it would get quite confusing after a while. It may even give you "unexpected results" if you detach and try to reattach the files. If you can get away with it, I would heartily suggest getting the files renamed.|||This sounds like a question for Old Man Phelan.|||It gets better... This actually goes back to the Unix days, and has to do with how pathing is logically based. Just for jolly factors, try to explain the difference between:dir c:\windows\\system32
dir c:\\windows\system32Why does one work, and one fail? What causes the time lag? What did you really do?

Answers will follow, but I'd love to hear folks try to talk this fiasco out a bit first.

-PatP|||Hmnmm...I got a network path not found message from the second one. I am going to guess that the system is interpreting this as "Using the protocol 'C', go to the machine called 'Windows', and get the file called 'System32'". The delay is caused by waiting for all of the DNS servers to chime in saying "Nope. No server called 'Windows' here." Still no idea why the first example works, though.|||Hello Pat I can proof both of your lines correct ;)

check this
for
dir c:\windows\\system32

c:\windows\ md (alt+092)system32

dir c:\\windows\system32

c:\md (alt+092)windows

Now both are valid folders and path too... ;)
Put your Numlock on and type numbers from there|||Hmnmm...I got a network path not found message from the second one. I am going to guess that the system is interpreting this as "Using the protocol 'C', go to the machine called 'Windows', and get the file called 'System32'". The delay is caused by waiting for all of the DNS servers to chime in saying "Nope. No server called 'Windows' here." Still no idea why the first example works, though.Bingo! Full marks for that half of the problem!

Now to give a few more clues on the first half of the problem...

dir c:\windows\system32\.\
dir c:\windows\system32\..\
dir c:\windows\system32\..\.\
dir c:\windows\system32\..\..\What do "dot" and "double dot" refer to? Based on that answer, why is an empty reference logically the same as a "dot" reference?

NOTE: If you have only worked with Microsoft Operating Systems, you are at a sore disadvantage here. This is another clue.

-PatP|||It may even give you "unexpected results" if you detach and try to reattach the files. If you can get away with it, I would heartily suggest getting the files renamed.

That's how I noticed it, I was trying to attach a database
file when I got an error message because the referred Log file didn't exist. Then I happened
to notice the double backslash in the Log file name. This wasn't the cause of the error though, the file
was simply missing. But, no idea how the double backslash got there!|||But, no idea how the double backslash got there!My first guess would be a typo (fat fingers, flying furiously, flubbing fiendishly). My next guess would be a keyboard problem (key bounce). Next would be a numeric directory name typed with Num Lock turned off... After that, I'd give up!

-PatP|||Bingo! Full marks for that half of the problem!

Now to give a few more clues on the first half of the problem...

dir c:\windows\system32\.\
dir c:\windows\system32\..\
dir c:\windows\system32\..\.\
dir c:\windows\system32\..\..\What do "dot" and "double dot" refer to? Based on that answer, why is an empty reference logically the same as a "dot" reference?

NOTE: If you have only worked with Microsoft Operating Systems, you are at a sore disadvantage here. This is another clue.

-PatP

. refers to the current directory you are in (i.e. c:\windows\system32\.\ is the c:\windows\system32 directory.)

.. refers to the parent directory of the one you are in (i.e c:\windows\system32\.. refers to c:\windows directory.)

so the ..\..\ would put you at the c:\WINDOWS folder.
[EDIT ... I typed too fast] ... ..\..\ puts you at the root of the C drive!|||Hmm. I am still a touch confused about the empty reference. When you type in

dir \

or

cd \

You end up referring to the root of the current drive. dir \\ gives you

C:\Documents and Settings\mcrowley>dir \\
The filename, directory name, or volume label syntax is incorrect.

Hmm...It may be time to dust off the Linux test machine...|||\\ on some systems means referring to a network
computer.|||My first guess ...

It's a SP who makes up the filename I think.
I'll have to trace myself through the code. Funny it
hasn't occurred when we've been using the same SP
before.

Multipile Instance in MSDE

I have installed 2 MSDE 2000 engines in to the PC. one is no instance name, another one's instance name is 'TEST'.

I could use a GUI MSSQL manager to connect both with local machine.

But when I tried to connect both with other machine, I found that I only could connect either one which is start in service earlier.

My case is normal?

Are they conflict by the same network port? If yes, could I change the portnumber of the MSDE??

Thanks for your support!!Hi Walama,

I haven't used MSDE a whole lot, but it does sound like changing the port number might be the trick.

The SVRNETCN.EXE program can do that for you. SVRNETCN.EXE configures the remote protocol for your MSDE instances - but maybe you already know that because it sounds like you got at least one instance hooked up.

You can select which instance to configure, what protocol and the "Properties..." button for TCP/IP has a place to set a port number.

The program is located in your "C:\Program Files\Microsoft SQL Server\80\Tools\Bin" folder.

I posted some screen-shots of how to connect to MSDE from Enterprise Manager recently - although I haven't done the write up yet.

http://kb.xmlx.ca/article.aspx?id=10065

Hope this helps...|||Hi Scott,

Thx for you suggestion. I am changing the port number now, but I want to know that if I use the 3rd party tool to manage the database. So do you know how do I enter the portnumber for those tools??