multiple instances

[terry712]

just a quickie - afraid i'm not a ms or sql person

i'm ok up to a point but i need to put a sql server in this week and the software company asked if i minded if they ran 2 sql instances as their are 2 databses to run and they must have the same name to work properly

it's sql 2000 or w2k
how do i install with 2 instances ?

 

[JayKusch]

Install SQL Server from the CD the first time. This will create your "Default" instance named after the server. So if your server is called MyServer, the Default instance of SQL Server will be called MyServer.

Then run the SQL Server install from CD AGAIN! Here you will be given the choice to "name" your second instance during the install. The default option will naturally be greyed out due to the first install.

Name that instance something meaningful. In my example we will call this instance SecondSQLServer. Finish the install out as usual.

Now you will have an instance of SQL Server called ...

MyServer\SecondSQLServer. A "named" instance always follows the rule of ServerName\InstanceName.

Be careful when you create SQL queries though! It sometimes get tricky in calling a named instance. You may find that you have to enclose the instance name in [] for it to work.

Like ...

SELECT *
FROM [MyServer\SecondSQLServer].MyDB.dbo.MyTable

As an FYI ... you are allowed 16 Name Instances per server.

Thanks

J. Kusch

 

[hmckillop]

J.Kusch

Are then required to use the Server Administrator to enable TCP/IP for the second instance, or does the install do this?
The only reason why I ask is, that if you install MSDE and require remote access to it, you need to specifically go in and setup the TCP/IP or any other protocol in order to get remote access to that instance of SQL.
Thanks in advance


"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[JayKusch]

I beleive you can share the same port SQL Server coomunicated out of, that being 1433 or you can configure the other instance to use the othe default port SQL Server uses which is port 1434.

As for setting the instance up to connect to a specific IP address, I remember it being done my registering the IP and the the instance name as 10.0.2.21\MySQLServerInstance1
in the client network utility.


Thanks

J. Kusch

 

[terry712]

cheers

i'll give it a whirl