MDF Files currupted: instances

Showing posts with label instances. Show all posts

Wednesday, March 28, 2012

Named Pipes problem on Clustered SQL Server

We recently have had issues connecting to clustered SQL Server
instances and have been working with PSS. I was unable to find any
useful information to assist in the troubleshooting process, so I have
decided to post this here in case anyone else searches on this like I
did.
Our Configuration:
2 Nodes, Windows 2003 Enterprise SP1, 5 SQL Server Named Instances
(SP4) in active/active configuration.
Our Initial Problem:
We were receiving GNE (General Network Error) when running some jobs or
connecting remotely to any of the named instances via Named Pipes.
When we looked in the SQL Server logs for each instance, we saw that
all instances reported "Listening on TCP, Shared Memory, Named Pipes"
During the troubleshooting we eventually found that we were unable to
connect to any of the instances using named pipes that were owned by of
the cluster nodes unless an alias was created.
Troubleshooting steps that helped us:
Try to connect to each instance using Query Analyzer. In the "Connect
to SQL Server"s "Connect to:" field try the following for each
instance:
Examples use a server called: SQLSERVER1\INSTANCE running on 1433.
After each successful connect, you should verify the connection's net
library using the following query:
select net_library from sysprocesses where spid = @.@.spid
1.) tcp:192.168.1.1,1433
2.) tcp:SQLSERVER1,1433
3.) tcp:SQLSERVER1\INSTANCE
4.) np:\\SQLSERVER1\pipe\MSSQL$INSTANCE\sql\query
5.) np:SQLSERVER1\INSTANCE
Here's the rundown of how each one is testing connectivity:
Number 1 is basic TCP connectivity. If this fails, look at TCP,
cluster VIP for that instance, port blocking on firewalls, etc. This
method does NOT use SSRP (SQL Server Resolution Protocol/Service).
Number 2 is like #1 except now DNS and/or WINS resolution is involved.
If #1 works and #2 doesn't, look at name resolution over TCP/IP. #3
uses the same name resolution as #2, however now you are using SSRP to
get the port number of the instance. (More on SSRP below). #4 uses
TCP name resolution and then connects to the SQL Server Name on the
cluster using the exact pipe name of the instance. If #4 fails, try
number 2 to test name resolution and also ensure that there is a log
entry in the SQL Server log that says "Listening on Named Pipes". #4
Uses Name Resolution and SSRP to query the pipe name.
What is SSRP?
This is the million dollar question, especially in a SQL Server
Clustered Environment. Good luck finding a nice whitepaper by
Microsoft about it, because I was unable to. After piecing together a
bunch of different posts, articles and other resources I was able to
glean the following (please note, this is my interpretation, not
absolute fact)
SSRP listens on each instance of SQL Server installed in the cluster
environment using UDP Port 1434. When a request is made for a named
pipes instance of SQL Server, the client request first queries UDP 1434
using a hex packet of 0x3. The request is handed to the SSRP Active
Server (which is the first SQL Server instance on each node that was
brought online) The SSRP Active Server then enumerates the instances
from the registry key:
HKLM\Software\Microsoft\MicrosoftSQLServer\InstalledInstances
and then SSRP Enumerates the connection information for each instance
using the key(s):
HKLM\Software\Microsoft\MicrosoftSQLServer\(instance
name)\MSSQLServer\SuperSocketNetLib\
SSRP then returns on UDP1434 to the client is a list of listening
instances and their configuration information.
You can actually see this in action using PortQry or a tool called UDP
Port Tool by http://www.simplecomtools.com/
I was unable to get PortQry to work in my environment, so I used the
UDP Port Tool. You will need to bind 1434 on your local machine (right
side of window) then enter the connection information (IP address of
SQL Server IP and port 1434 (left side of window) and enter "03"
without the quotes and set the data type to hex. This will return the
information provided by SSRP.
Now, the meat of the problem....
We found that on one of our nodes NONE of the instances would connect
using method 5 (above). However, method 4 would work. When we queried
SSRP on any of these instances, no data was returned. By logging on to
that node and executing:
netstat -an -b -p UDP
We were able to see that 0.0.0.0,1434 UDP was bound to PID xxxx. This
SHOULD have been the PID of the FIRST SQL Instance to be brought online
to that node. When we looked at both Task Manager Processes (selecting
the column for PID) there was no PID running with the number xxxx.
After looking in the historical logs, we found that there was an
instance at one point that was running on that node with PID xxxx,
however it had crashed and restarted under a new PID, however the
1434UDP bind was never released, therefore it did not thing it needed
to be the Active SSRP Server. A full cluster reboot is the Microsoft
PSS recommendation for this situation.
I hope this helps someone. We banged our heads against the wall for a
while on this, and if this only helps one person out there, hopefully
my newsgroup karma gets better. Please feel free to reply to this
with technical corrections or comments. Like I mentioned above, this
is what I found and is my interpretation.
ThanksOne other interesting note regarding SSRP that I came across while
reserching this did not apply to us, but may apply is registry
permissions while enumerating. There is a technet article on this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;922131&sd=rss&spid=2852|||Wrong article!! This is the article regarding registry permissions:
http://support.microsoft.com/kb/888529
The other article has to do with SQL2005 installed over SQL2000 and
then removed.
wuteva wrote:
> One other interesting note regarding SSRP that I came across while
> reserching this did not apply to us, but may apply is registry
> permissions while enumerating. There is a technet article on this:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;922131&sd=rss&spid=2852

named pipes

The cluster consists of two nodes and of two instances. One default sql
instance and one another named instanse.
To connect to the named instance all the clients have alias(done by
cliconfg.exe), but when the instance fails over to another node no one
can't connect to this named instance anymore. If i edit the alias to
use the default instance pipe name, this helps!
It seems named pipes don't fail over to another node.. Any suggestions?
Thanks in advance.
Andrew
Have you run cliconfg.exe on both nodes, not just the "primary"?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"kreit" <kreit@.mail.ru> wrote in message
news:1118052606.954693.185910@.g47g2000cwa.googlegr oups.com...
The cluster consists of two nodes and of two instances. One default sql
instance and one another named instanse.
To connect to the named instance all the clients have alias(done by
cliconfg.exe), but when the instance fails over to another node no one
can't connect to this named instance anymore. If i edit the alias to
use the default instance pipe name, this helps!
It seems named pipes don't fail over to another node.. Any suggestions?
Thanks in advance.
Andrew
|||I run cliconfg.exe on the clients side. The problem is that our
end-user applications aren't ms sql instances aware of and i need to
provide an alias.
i can change the alias on the user machines via some kind of gpo etc..
Andrew
Tom Moreau =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
> Have you run cliconfg.exe on both nodes, not just the "primary"?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "kreit" <kreit@.mail.ru> wrote in message
> news:1118052606.954693.185910@.g47g2000cwa.googlegr oups.com...
> The cluster consists of two nodes and of two instances. One default sql
> instance and one another named instanse.
>
> To connect to the named instance all the clients have alias(done by
> cliconfg.exe), but when the instance fails over to another node no one
> can't connect to this named instance anymore. If i edit the alias to
> use the default instance pipe name, this helps!
>
> It seems named pipes don't fail over to another node.. Any suggestions?
>=20
>=20
>=20
> Thanks in advance.
> Andrew
|||This sounds strange. Have you tried removing the aliases from the client
machines?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"kreit" <kreit@.mail.ru> wrote in message
news:1118228154.834811.10600@.o13g2000cwo.googlegro ups.com...
I run cliconfg.exe on the clients side. The problem is that our
end-user applications aren't ms sql instances aware of and i need to
provide an alias.
i can change the alias on the user machines via some kind of gpo etc..
Andrew
Tom Moreau писал(а):
> Have you run cliconfg.exe on both nodes, not just the "primary"?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "kreit" <kreit@.mail.ru> wrote in message
> news:1118052606.954693.185910@.g47g2000cwa.googlegr oups.com...
> The cluster consists of two nodes and of two instances. One default sql
> instance and one another named instanse.
>
> To connect to the named instance all the clients have alias(done by
> cliconfg.exe), but when the instance fails over to another node no one
> can't connect to this named instance anymore. If i edit the alias to
> use the default instance pipe name, this helps!
>
> It seems named pipes don't fail over to another node.. Any suggestions?
>
> Thanks in advance.
> Andrew
|||If i remove - i just can't connect..
Andrew
Tom Moreau =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):[vbcol=seagreen]
> This sounds strange. Have you tried removing the aliases from the client
> machines?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "kreit" <kreit@.mail.ru> wrote in message
> news:1118228154.834811.10600@.o13g2000cwo.googlegro ups.com...
> I run cliconfg.exe on the clients side. The problem is that our
> end-user applications aren't ms sql instances aware of and i need to
> provide an alias.
> i can change the alias on the user machines via some kind of gpo etc..
> Andrew
> Tom Moreau =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
|||This sounds like either a DNS issue or that SQL Server is not listening on
TCP/IP. Check out the Server Network Utility on your cluster - or the SQL
Server errorlog to see what protocols SQL Server will "hear". Also, ensure
that your clients are using TCP/IP. Check out their Client Network Utility
to confirm.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"kreit" <kreit@.mail.ru> wrote in message
news:1118242276.507481.291570@.f14g2000cwb.googlegr oups.com...
If i remove - i just can't connect..
Andrew
Tom Moreau писал(а):[vbcol=seagreen]
> This sounds strange. Have you tried removing the aliases from the client
> machines?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "kreit" <kreit@.mail.ru> wrote in message
> news:1118228154.834811.10600@.o13g2000cwo.googlegro ups.com...
> I run cliconfg.exe on the clients side. The problem is that our
> end-user applications aren't ms sql instances aware of and i need to
> provide an alias.
> i can change the alias on the user machines via some kind of gpo etc..
> Andrew
> Tom Moreau писал(а):
|||with tcp/ip everything is ok. I've a problem with named pipes. Our
application isn't tcp/ip aware, just named pipes - thats all.
Andrew
Tom Moreau =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
> This sounds like either a DNS issue or that SQL Server is not listening on
> TCP/IP. Check out the Server Network Utility on your cluster - or the SQL
> Server errorlog to see what protocols SQL Server will "hear". Also, ensu=
re
> that your clients are using TCP/IP. Check out their Client Network Utili=
ty[vbcol=seagreen]
> to confirm.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "kreit" <kreit@.mail.ru> wrote in message
> news:1118242276.507481.291570@.f14g2000cwb.googlegr oups.com...
> If i remove - i just can't connect..
> Andrew
> Tom Moreau =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
nt[vbcol=seagreen]
ql[vbcol=seagreen]
s?[vbcol=seagreen]
|||ok, for example
Active-Active cluster. One instance server1, another one -
server2\instance
Since my application isnt't cluster aware i create an alias to connect
via named pipes.
- server alias: server2
- server name: server2\instance
- pipe name: \\server2\pipe\MSSQL$INSTANCE\sql\query
when server2 fails over to server1 i can't use my application. At this
moment I can ping server2 successfully.
If i change on the client the alias server2:
- server alias: server2 (it's the same)
- server name: server1
- pipe name: \\server1\pipe\\sql\query
I can connect and can't use my application
|||kreit wrote:
> The cluster consists of two nodes and of two instances. One default sql
> instance and one another named instanse.
>
> To connect to the named instance all the clients have alias(done by
> cliconfg.exe), but when the instance fails over to another node no one
> can't connect to this named instance anymore. If i edit the alias to
> use the default instance pipe name, this helps!
>
> It seems named pipes don't fail over to another node.. Any suggestions?
>
Maybe some of the registry is not replicated. In the cluster registry
there should be about 7 registry entries for each sql server resource
which are used to replicate some of the sql registry keys. Check if they
exist. Also check the contents of software\microsoft\microsoft sql
server\instance\mssqlserver\supersocketnetlib\np\p ipename on the problem
node when sql is running there.
The service account of the default instance should be able to read the
registry key's of the named instance and vice versa. Sqlservers log
mentions its listening on named pipes?
What I think is kind of strange is that you are able to use the client
network tools aliases but not tcp\ip. Your program should not even know
about named pipes or tcp/ip. It just needs to talk to the sqldriver.
Maybe its time to instal ethereal (http://www.ethereal.com/) on one of
the clients.
Hans
|||kreit wrote:
> ok, for example
> Active-Active cluster. One instance server1, another one -
> server2\instance
> Since my application isnt't cluster aware i create an alias to connect
> via named pipes.
> - server alias: server2
> - server name: server2\instance
> - pipe name: \\server2\pipe\MSSQL$INSTANCE\sql\query
>
> when server2 fails over to server1 i can't use my application. At this
> moment I can ping server2 successfully.
> If i change on the client the alias server2:
> - server alias: server2 (it's the same)
> - server name: server1
> - pipe name: \\server1\pipe\\sql\query
> I can connect and can't use my application
>
Just to make very sure, you do know the difference between the virtual
server name and the node name?
'when server2 fails over to server1 i can't use my application'
should be:
'when virtual server2 fails over to node1 i can't use my application'
Clients should connect to the virtual server name and not to the not to
the name of the physical server.
Hans
sql

Monday, March 26, 2012

Named instances..anything different than default ?

We have always used default instances of SQL Server and wanted to start
considering multiple named instances on one physical host. Anything to watch
out for other than just the name change which goes as
Hostname\NamedInstance1 ?
ThanksHassam
Each instance on the server consumes memory , just be aware.
"Hassan" <hassan@.hotmail.com> wrote in message
news:u1rPioitHHA.4688@.TK2MSFTNGP05.phx.gbl...
> We have always used default instances of SQL Server and wanted to start
> considering multiple named instances on one physical host. Anything to
> watch out for other than just the name change which goes as
> Hostname\NamedInstance1 ?
> Thanks
>|||Hello,
As well as each installed named instance require seperate SQL Server
licenses.
Thanks
Hari
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u2sfZ8itHHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hassam
> Each instance on the server consumes memory , just be aware.
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:u1rPioitHHA.4688@.TK2MSFTNGP05.phx.gbl...
>> We have always used default instances of SQL Server and wanted to start
>> considering multiple named instances on one physical host. Anything to
>> watch out for other than just the name change which goes as
>> Hostname\NamedInstance1 ?
>> Thanks
>|||> As well as each installed named instance require seperate SQL Server
> licenses.
I believe licensing varies by both SQL Server version and edition. Separate
licenses are needed in some cases but not others.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uS1F4hjtHHA.4572@.TK2MSFTNGP02.phx.gbl...
> Hello,
> As well as each installed named instance require seperate SQL Server
> licenses.
> Thanks
> Hari
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u2sfZ8itHHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hassam
>> Each instance on the server consumes memory , just be aware.
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:u1rPioitHHA.4688@.TK2MSFTNGP05.phx.gbl...
>> We have always used default instances of SQL Server and wanted to start
>> considering multiple named instances on one physical host. Anything to
>> watch out for other than just the name change which goes as
>> Hostname\NamedInstance1 ?
>> Thanks
>>
>|||For SQL 2005 you don't need licenses for separate instances. You do
need one license for the machine though.
Dan Guzman wrote:
>> As well as each installed named instance require seperate SQL Server
>> licenses.
> I believe licensing varies by both SQL Server version and edition.
> Separate licenses are needed in some cases but not others.
>|||Is that true ? That would be cool.
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:O7EZ59ntHHA.1728@.TK2MSFTNGP06.phx.gbl...
> For SQL 2005 you don't need licenses for separate instances. You do need
> one license for the machine though.
>
> Dan Guzman wrote:
>> As well as each installed named instance require seperate SQL Server
>> licenses.
>> I believe licensing varies by both SQL Server version and edition.
>> Separate licenses are needed in some cases but not others.|||Check the readme and licensing info that comes with SQL 2005. I also
checked with our MS rep about it.
Hassan wrote:
> Is that true ? That would be cool.
> "Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
> news:O7EZ59ntHHA.1728@.TK2MSFTNGP06.phx.gbl...
>> For SQL 2005 you don't need licenses for separate instances. You do need
>> one license for the machine though.
>>
>> Dan Guzman wrote:
>> As well as each installed named instance require seperate SQL Server
>> licenses.
>> I believe licensing varies by both SQL Server version and edition.
>> Separate licenses are needed in some cases but not others.
>

Named instances..anything different than default ?

Named Instances Problem (I Think)

I'm struggling a little with this. I hope someone can help.

Ho do i install SQL Server 2005 Express so i can connect to it with "localhost", not "<MACHINE_NAME>/SQLEXPRESS". I cant seem to find any obvious setting in setup.

Could anyone shed any light on this?

Regards,

Anthony

I think you want to connect only to default instance in that case just specify the instance name as default while installing sql express refer, http://msdn2.microsoft.com/en-us/library/ms143722.aspx

|||

You can use an alias configured on the clients to connect to the name only. See the BOL for more information about configuring aliases. You will have to use the SQL Server configuration manager.

Jens K. Suessmeyer

http://www.sqlserver2005.de

named instances and sp3a - how to enable access?

As I understand it, the slammer virus took advantage of port 1434 which is e
nabled over udp for sql clients to find out the ports named instances are us
ing. If applying sp3a disables listening on port 1434, can named instances s
till be used? Do the ports
need to be known and hardcoded in advance?
TIA,
John BrownIn the Server network Utiliy simply choose a fixed port number, and the
clients would have to know the fixed port number to connect.
Or you could still use Dynamic ports, (the check box) and enable port 1434,
and the end user does NOT have to know the port number...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"John Brown" <anonymous@.discussions.microsoft.com> wrote in message
news:F9DBE64E-3137-41B3-91D1-ECDFC0086B08@.microsoft.com...
> As I understand it, the slammer virus took advantage of port 1434 which is
enabled over udp for sql clients to find out the ports named instances are
using. If applying sp3a disables listening on port 1434, can named instances
still be used? Do the ports need to be known and hardcoded in advance?
> TIA,
> John Brown|||Thanks Wayne,
so if I enable 1434, does this open me up to the slammer virus still?
TIA,
John Brown|||SP3a fixes the exploit in the SQL Server Resolution Service used by Slammer,
it does not stop the server listening on UDP1434 as this is required for
resolving endpoints for named instances unless every client has the port
hard coded into a connection string or an alias in the client network
utlity. As long as you have SP3a applied you'll be okay but make sure that
port is still blocked on external firewalls.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"John Brown" <anonymous@.discussions.microsoft.com> wrote in message
news:F5708C23-286E-49B6-88BC-4CD6C5F1C768@.microsoft.com...
> Thanks Wayne,
> so if I enable 1434, does this open me up to the slammer virus still?
> TIA,
> John Brown

named instances and sp3a - how to enable access?

As I understand it, the slammer virus took advantage of port 1434 which is enabled over udp for sql clients to find out the ports named instances are using. If applying sp3a disables listening on port 1434, can named instances still be used? Do the ports need to be known and hardcoded in advance
TIA
John BrownIn the Server network Utiliy simply choose a fixed port number, and the
clients would have to know the fixed port number to connect.
Or you could still use Dynamic ports, (the check box) and enable port 1434,
and the end user does NOT have to know the port number...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"John Brown" <anonymous@.discussions.microsoft.com> wrote in message
news:F9DBE64E-3137-41B3-91D1-ECDFC0086B08@.microsoft.com...
> As I understand it, the slammer virus took advantage of port 1434 which is
enabled over udp for sql clients to find out the ports named instances are
using. If applying sp3a disables listening on port 1434, can named instances
still be used? Do the ports need to be known and hardcoded in advance?
> TIA,
> John Brown|||SP3a fixes the exploit in the SQL Server Resolution Service used by Slammer,
it does not stop the server listening on UDP1434 as this is required for
resolving endpoints for named instances unless every client has the port
hard coded into a connection string or an alias in the client network
utlity. As long as you have SP3a applied you'll be okay but make sure that
port is still blocked on external firewalls.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"John Brown" <anonymous@.discussions.microsoft.com> wrote in message
news:F5708C23-286E-49B6-88BC-4CD6C5F1C768@.microsoft.com...
> Thanks Wayne,
> so if I enable 1434, does this open me up to the slammer virus still?
> TIA,
> John Brown

Named Instances

During the install SQL 2000 askes to use the default to name a instance. Is
it better to name the instance or use the default? What is the function of
instances in SQL?Hello,
With Microsoft SQL Server 2000, you have the option of installing multiple
copies, or instances of SQL Server on one computer. When setting up a new
installation of SQL Server 2000 or maintaining an existing installation,
you can specify it as:
A default instance of SQL Server.
This instance is identified by the network name of the computer on which it
is running. Applications using client software from earlier versions of SQL
Server can connect to a default instance. SQL Server version 6.5 or SQL
Server version 7.0 servers can operate as default instances. However, a
computer can have only ONE version functioning as the default instance at a
time.
A named instance of SQL Server.
This instance is identified by the network name of the computer plus an
instance name, in the format <computername>\<instancename>. Applications
must use SQL Server 2000 client components to connect to a named instance.
A computer can run any number of named instances of SQL Server
concurrently. A named instance can run at the same time as an existing
installation of SQL Server version 6.5 or SQL Server version 7.0. The
instance name cannot exceed 16 characters.
The maximum number of instances supported in SQL Server 2000 is 16. For
more information, refer to the following articles:
Multiple Instances of SQL Server
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_
ar_cs_9i5u.asp
Working with Named and Multiple Instances of SQL Server 2000
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_
runsetup_2xmb.asp
Multiple Instances
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag2k/
html/MultipleInstances.asp
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||If the server later changes name or joins a domain will th instance name
change as well. What I'm doing is replacing are existing SQL server with a
new SQL Server. I've named the new server newsql and made it a member of a
workgroup. After I move the databses over I would then change change the
name on the old server and then make the new server the name of the old
server and join it to the domain. Will the instance name chnage when the
server's name changes?
"Sophie Guo [MSFT]" wrote:
> Hello,
> With Microsoft SQL Server 2000, you have the option of installing multiple
> copies, or instances of SQL Server on one computer. When setting up a new
> installation of SQL Server 2000 or maintaining an existing installation,
> you can specify it as:
> A default instance of SQL Server.
> This instance is identified by the network name of the computer on which it
> is running. Applications using client software from earlier versions of SQL
> Server can connect to a default instance. SQL Server version 6.5 or SQL
> Server version 7.0 servers can operate as default instances. However, a
> computer can have only ONE version functioning as the default instance at a
> time.
> A named instance of SQL Server.
> This instance is identified by the network name of the computer plus an
> instance name, in the format <computername>\<instancename>. Applications
> must use SQL Server 2000 client components to connect to a named instance.
> A computer can run any number of named instances of SQL Server
> concurrently. A named instance can run at the same time as an existing
> installation of SQL Server version 6.5 or SQL Server version 7.0. The
> instance name cannot exceed 16 characters.
> The maximum number of instances supported in SQL Server 2000 is 16. For
> more information, refer to the following articles:
> Multiple Instances of SQL Server
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_
> ar_cs_9i5u.asp
> Working with Named and Multiple Instances of SQL Server 2000
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_
> runsetup_2xmb.asp
> Multiple Instances
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag2k/
> html/MultipleInstances.asp
>
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> =====================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello,
After renaming a server, we can perform the following steps to rename SQL
server instance:
1. Restart the server and then start the SQL Server service. This should
automatically get the *new* Server name and we only need to run the
following procedures to
update syservers system table:
-- For a default instance
EXEC sp_dropserver ''old_name''
GO
EXEC sp_addserver ''new_name'', local
GO
-- For a named instance
EXEC sp_dropserver ''original_name\original_instance_name''
GO
EXEC sp_addserver ''new_server_name\instance_name'', local
GO
2. After running the sp_addserver stored procedure, we must restart the SQL
Server service for the change to take effect. The following article
describes this
scenario in more detail:
303774 BUG: "Renaming A Server" Topic in SQL Server Books Online is
Incomplete
http://support.microsoft.com/?id=303774
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

named instances

I am setting up a 3 + 1 Windows 2003 Server cluster hosting 3 named instances of SQL Server 2000 on the active servers. This SQL Farm will support approximately 150 databases supporting 15 different groups of applications distrbuted amongst a user population of 5000 desktops over mutiple sites. Each named instance of SQL Server will initially host a different subset of the databases. To optimise the server load on each of the 3 active cluster members, over time I want to be able to move the databases supporting any particular application group from one server to a different server in the cluster. Ideally I would like to do this without having to change anything on the client side. I cannot think of any way to do this as I think the client connections will have to refer to the instance name, which in turn will refer to the virtual server name. If anyone knows how I can do this I would appreciate your directions. ThanksHi,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
As my understanding of you question, you want to optimise the server load
within a clustered SQL Server environment and you want to move the database
from one serve to another. If you want to move the databases from one
virtual server to another (in the cluster), you have to make changes to
your client connections as they now need to connect to a new server that
has the databases. The client machines can have alias that point to a
virtual server. In this case, the clients can use the same alias but still
you still have to change alias to point to the new virtual server after the
databases are moved.
Hope this helps and if you still have questions, please feel free to post
new message here and I am ready to help!
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Baisong Wei,
Thnaks for your response, from your description you
understand the problem well. To reiterate I would like to
be able to move databases between the named instances
running on the virtual servers and to update the location
by changing something centrally (like a dns alias or a
server side alias) without having to change anything on
the clients. But the only way I can think of doing it is
to make a synchronised change on all the clients as they
have to refer to both the virtual server name and the
instance name. Any ideas?
Thanks
Geoff.
>--Original Message--
>Hi,
>Thank you for using the newsgroup and it is my pleasure
to help you with
>you issue.
>As my understanding of you question, you want to
optimise the server load
>within a clustered SQL Server environment and you want
to move the database
>from one serve to another. If you want to move the
databases from one
>virtual server to another (in the cluster), you have to
make changes to
>your client connections as they now need to connect to a
new server that
>has the databases. The client machines can have alias
that point to a
>virtual server. In this case, the clients can use the
same alias but still
>you still have to change alias to point to the new
virtual server after the
>databases are moved.
>Hope this helps and if you still have questions, please
feel free to post
>new message here and I am ready to help!
>Thanks.
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>.
>|||Hello Geoff,
As per my knowledge, there may not be a central way to achieve this.
Since you want to move the databases to a different virtual server,
you would need to make appropriate changes to the clients connection
strings. It is the same as moving dbs from one SQL Server to another.
Only difference is that you are using virtual SQL Server.Virtual SQL
Servers is for high availability and not for load balancing.
Does that answer you question ?
Thanks for using MSDN Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computer?s security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
>Content-Class: urn:content-classes:message
>From: <anonymous@.discussions.microsoft.com>
>Sender: <anonymous@.discussions.microsoft.com>
>References: <E539FE0B-030C-4315-8677-41CAD358B95D@.microsoft.com>
<fAHzT4t6DHA.568@.cpmsftngxa07.phx.gbl>
>Subject: RE: named instances
>Date: Wed, 4 Feb 2004 03:16:59 -0800
>Lines: 59
>Message-ID: <0a1901c3eb10$6815d050$3501280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcPrEGgVFRmJzG6iTB2Kxa9WPaBHUA==>Newsgroups: microsoft.public.sqlserver.server
>Path: cpmsftngxa07.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:327510
>NNTP-Posting-Host: tk2msftngxa06.phx.gbl 10.40.1.53
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Baisong Wei,
>Thnaks for your response, from your description you
>understand the problem well. To reiterate I would like to
>be able to move databases between the named instances
>running on the virtual servers and to update the location
>by changing something centrally (like a dns alias or a
>server side alias) without having to change anything on
>the clients. But the only way I can think of doing it is
>to make a synchronised change on all the clients as they
>have to refer to both the virtual server name and the
>instance name. Any ideas?
>Thanks
>Geoff.
>>--Original Message--
>>Hi,
>>Thank you for using the newsgroup and it is my pleasure
>to help you with
>>you issue.
>>As my understanding of you question, you want to
>optimise the server load
>>within a clustered SQL Server environment and you want
>to move the database
>>from one serve to another. If you want to move the
>databases from one
>>virtual server to another (in the cluster), you have to
>make changes to
>>your client connections as they now need to connect to a
>new server that
>>has the databases. The client machines can have alias
>that point to a
>>virtual server. In this case, the clients can use the
>same alias but still
>>you still have to change alias to point to the new
>virtual server after the
>>databases are moved.
>>Hope this helps and if you still have questions, please
>feel free to post
>>new message here and I am ready to help!
>>Thanks.
>>Best regards
>>Baisong Wei
>>Microsoft Online Support
>>----
>>Get Secure! - www.microsoft.com/security
>>This posting is provided "as is" with no warranties and
>confers no rights.
>>Please reply to newsgroups only. Thanks.
>>.
>|||Vikrant
Thanks for your reply. We'd come to the same conclusion but I'm glad it has been confirmed by you guys
Cheer
Geoff.sql

Named Instances

When applying patches if I have a default instance and
named instance on the same server, does the patch affect
both instances or can I patch them seperate?The executable code for each instance is deliberatly in a separate =directory so patches affect each instance seperately. This is so you can =test thingsin isolation. The exception to this is patches to tye clein =tools (Query Analyser, EM etc) as these are shared between instances.
Mike John
"Larry" <anonymous@.discussions.microsoft.com> wrote in message =news:04c901c39421$f2c73900$a101280a@.phx.gbl...
> When applying patches if I have a default instance and > named instance on the same server, does the patch affect > both instances or can I patch them seperate?|||Larry,
Your only choice is to apply them separately. Think of it as two machines
hiding in one box.
Russell Fields
"Larry" <anonymous@.discussions.microsoft.com> wrote in message
news:04c901c39421$f2c73900$a101280a@.phx.gbl...
> When applying patches if I have a default instance and
> named instance on the same server, does the patch affect
> both instances or can I patch them seperate?|||Larry,
A named instance is essentially a different server from
the default instance (and any other named instances).
You must patch all instances separately.
Tim
>--Original Message--
>When applying patches if I have a default instance and
>named instance on the same server, does the patch affect
>both instances or can I patch them seperate?
>.
>

Named Instances

Is there any limit for installing named instances on a server?Hi
Yes, maximum supported on SQL Server 2000 is 16.
If you have per server licensing, you need a licence for each instance (per
processor licence covers all instances).
Memory. They all need to share the same RAM. If you have 16 instances, with
each needing 1Gb, you need at least 16Gb of RAM on the box.
Regards
Mike
"SB138" wrote:
> Is there any limit for installing named instances on a server?|||Thanks
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Yes, maximum supported on SQL Server 2000 is 16.
> If you have per server licensing, you need a licence for each instance (per
> processor licence covers all instances).
> Memory. They all need to share the same RAM. If you have 16 instances, with
> each needing 1Gb, you need at least 16Gb of RAM on the box.
> Regards
> Mike
> "SB138" wrote:
> > Is there any limit for installing named instances on a server?

named instances

quote:

>--Original Message--
>Hi,
>Thank you for using the newsgroup and it is my pleasure

to help you with

quote:

>you issue.
>As my understanding of you question, you want to

optimise the server load

quote:

>within a clustered SQL Server environment and you want

to move the database

quote:

>from one serve to another. If you want to move the

databases from one

quote:

>virtual server to another (in the cluster), you have to

make changes to

quote:

>your client connections as they now need to connect to a

new server that

quote:

>has the databases. The client machines can have alias

that point to a

quote:

>virtual server. In this case, the clients can use the

same alias but still

quote:

>you still have to change alias to point to the new

virtual server after the

quote:

>databases are moved.
>Hope this helps and if you still have questions, please

feel free to post

quote:

>new message here and I am ready to help!
>Thanks.
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and

confers no rights.

quote:

>Please reply to newsgroups only. Thanks.
>.
>

|||Hello Geoff,
As per my knowledge, there may not be a central way to achieve this.
Since you want to move the databases to a different virtual server,
you would need to make appropriate changes to the clients connection
strings. It is the same as moving dbs from one SQL Server to another.
Only difference is that you are using virtual SQL Server.Virtual SQL
Servers is for high availability and not for load balancing.
Does that answer you question ?
Thanks for using MSDN Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
--
>Content-Class: urn:content-classes:message
>From: <anonymous@.discussions.microsoft.com>
>Sender: <anonymous@.discussions.microsoft.com>
>References: <E539FE0B-030C-4315-8677-41CAD358B95D@.microsoft.com>
<fAHzT4t6DHA.568@.cpmsftngxa07.phx.gbl>
>Subject: RE: named instances
>Date: Wed, 4 Feb 2004 03:16:59 -0800
>Lines: 59
>Message-ID: <0a1901c3eb10$6815d050$3501280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcPrEGgVFRmJzG6iTB2Kxa9WPaBHUA==
>Newsgroups: microsoft.public.sqlserver.server
>Path: cpmsftngxa07.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:327510
>NNTP-Posting-Host: tk2msftngxa06.phx.gbl 10.40.1.53
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Baisong Wei,
>Thnaks for your response, from your description you
>understand the problem well. To reiterate I would like to
>be able to move databases between the named instances
>running on the virtual servers and to update the location
>by changing something centrally (like a dns alias or a
>server side alias) without having to change anything on
>the clients. But the only way I can think of doing it is
>to make a synchronised change on all the clients as they
>have to refer to both the virtual server name and the
>instance name. Any ideas?
>Thanks
>Geoff.
>to help you with
>optimise the server load
>to move the database
>databases from one
>make changes to
>new server that
>that point to a
>same alias but still
>virtual server after the
>feel free to post
>confers no rights.
>|||Vikrant,
Thanks for your reply. We'd come to the same conclusion but I'm glad it has
been confirmed by you guys.
Cheers
Geoff.

Named Instances

During the install SQL 2000 askes to use the default to name a instance. I
s
it better to name the instance or use the default? What is the function of
instances in SQL?Hello,
With Microsoft SQL Server 2000, you have the option of installing multiple
copies, or instances of SQL Server on one computer. When setting up a new
installation of SQL Server 2000 or maintaining an existing installation,
you can specify it as:
A default instance of SQL Server.
This instance is identified by the network name of the computer on which it
is running. Applications using client software from earlier versions of SQL
Server can connect to a default instance. SQL Server version 6.5 or SQL
Server version 7.0 servers can operate as default instances. However, a
computer can have only ONE version functioning as the default instance at a
time.
A named instance of SQL Server.
This instance is identified by the network name of the computer plus an
instance name, in the format <computername>\<instancename>. Applications
must use SQL Server 2000 client components to connect to a named instance.
A computer can run any number of named instances of SQL Server
concurrently. A named instance can run at the same time as an existing
installation of SQL Server version 6.5 or SQL Server version 7.0. The
instance name cannot exceed 16 characters.
The maximum number of instances supported in SQL Server 2000 is 16. For
more information, refer to the following articles:
Multiple Instances of SQL Server
http://msdn.microsoft.com/library/d...-us/architec/8_
ar_cs_9i5u.asp
Working with Named and Multiple Instances of SQL Server 2000
http://msdn.microsoft.com/library/d...-us/instsql/in_
runsetup_2xmb.asp
Multiple Instances
http://msdn.microsoft.com/library/d...-us/dnsqlmag2k/
html/MultipleInstances.asp
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||If the server later changes name or joins a domain will th instance name
change as well. What I'm doing is replacing are existing SQL server with a
new SQL Server. I've named the new server newsql and made it a member of a
workgroup. After I move the databses over I would then change change the
name on the old server and then make the new server the name of the old
server and join it to the domain. Will the instance name chnage when the
server's name changes?
"Sophie Guo [MSFT]" wrote:

> Hello,
> With Microsoft SQL Server 2000, you have the option of installing multiple
> copies, or instances of SQL Server on one computer. When setting up a new
> installation of SQL Server 2000 or maintaining an existing installation,
> you can specify it as:
> A default instance of SQL Server.
> This instance is identified by the network name of the computer on which i
t
> is running. Applications using client software from earlier versions of SQ
L
> Server can connect to a default instance. SQL Server version 6.5 or SQL
> Server version 7.0 servers can operate as default instances. However, a
> computer can have only ONE version functioning as the default instance at
a
> time.
> A named instance of SQL Server.
> This instance is identified by the network name of the computer plus an
> instance name, in the format <computername>\<instancename>. Applications
> must use SQL Server 2000 client components to connect to a named instance.
> A computer can run any number of named instances of SQL Server
> concurrently. A named instance can run at the same time as an existing
> installation of SQL Server version 6.5 or SQL Server version 7.0. The
> instance name cannot exceed 16 characters.
> The maximum number of instances supported in SQL Server 2000 is 16. For
> more information, refer to the following articles:
> Multiple Instances of SQL Server
> http://msdn.microsoft.com/library/d...ft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hello,
After renaming a server, we can perform the following steps to rename SQL
server instance:
1. Restart the server and then start the SQL Server service. This should
automatically get the *new* Server name and we only need to run the
following procedures to
update syservers system table:
-- For a default instance
EXEC sp_dropserver ''old_name''
GO
EXEC sp_addserver ''new_name'', local
GO
-- For a named instance
EXEC sp_dropserver ''original_name\original_instance_name''
GO
EXEC sp_addserver ''new_server_name\instance_name'', local
GO
2. After running the sp_addserver stored procedure, we must restart the SQL
Server service for the change to take effect. The following article
describes this
scenario in more detail:
303774 BUG: "Renaming A Server" Topic in SQL Server Books Online is
Incomplete
http://support.microsoft.com/?id=303774
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Named Instances

Is there any limit for installing named instances on a server?Hi
Yes, maximum supported on SQL Server 2000 is 16.
If you have per server licensing, you need a licence for each instance (per
processor licence covers all instances).
Memory. They all need to share the same RAM. If you have 16 instances, with
each needing 1Gb, you need at least 16Gb of RAM on the box.
Regards
Mike
"SB138" wrote:

> Is there any limit for installing named instances on a server?|||Thanks
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Yes, maximum supported on SQL Server 2000 is 16.
> If you have per server licensing, you need a licence for each instance (pe
r
> processor licence covers all instances).
> Memory. They all need to share the same RAM. If you have 16 instances, wit
h
> each needing 1Gb, you need at least 16Gb of RAM on the box.
> Regards
> Mike
> "SB138" wrote:
>sql

Named Instances

In SQL 2000 Clustering, can I have only one default instance for the entire
cluster or can I have one default per virtual server. In other words, can
all my SQL installs in a cluster be default, each on a different virtual
server?
1 Default or no default. Then you can have up to 15 named or all 16 can be
named

Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
"Arun" <aganesan@.esurance.com> wrote in message
news:OqFY8U5dFHA.3492@.TK2MSFTNGP14.phx.gbl...
> In SQL 2000 Clustering, can I have only one default instance for the
> entire
> cluster or can I have one default per virtual server. In other words, can
> all my SQL installs in a cluster be default, each on a different virtual
> server?
>
|||Each Instance gets its own virtual server. Due to SQL Instance naming
restrictions, you still only get one default instance per cluster.
Personally, I prefer to use only Named Instances on a cluster, just to keep
name appearances consistent.
Geoff N. Hiten
Microsoft SQL Server MVP
"Arun" <aganesan@.esurance.com> wrote in message
news:OqFY8U5dFHA.3492@.TK2MSFTNGP14.phx.gbl...
> In SQL 2000 Clustering, can I have only one default instance for the
> entire
> cluster or can I have one default per virtual server. In other words, can
> all my SQL installs in a cluster be default, each on a different virtual
> server?
>

Named Instances

During the install SQL 2000 askes to use the default to name a instance. Is
it better to name the instance or use the default? What is the function of
instances in SQL?
Hello,
With Microsoft SQL Server 2000, you have the option of installing multiple
copies, or instances of SQL Server on one computer. When setting up a new
installation of SQL Server 2000 or maintaining an existing installation,
you can specify it as:
A default instance of SQL Server.
This instance is identified by the network name of the computer on which it
is running. Applications using client software from earlier versions of SQL
Server can connect to a default instance. SQL Server version 6.5 or SQL
Server version 7.0 servers can operate as default instances. However, a
computer can have only ONE version functioning as the default instance at a
time.
A named instance of SQL Server.
This instance is identified by the network name of the computer plus an
instance name, in the format <computername>\<instancename>. Applications
must use SQL Server 2000 client components to connect to a named instance.
A computer can run any number of named instances of SQL Server
concurrently. A named instance can run at the same time as an existing
installation of SQL Server version 6.5 or SQL Server version 7.0. The
instance name cannot exceed 16 characters.
The maximum number of instances supported in SQL Server 2000 is 16. For
more information, refer to the following articles:
Multiple Instances of SQL Server
http://msdn.microsoft.com/library/de...us/architec/8_
ar_cs_9i5u.asp
Working with Named and Multiple Instances of SQL Server 2000
http://msdn.microsoft.com/library/de...us/instsql/in_
runsetup_2xmb.asp
Multiple Instances
http://msdn.microsoft.com/library/de...us/dnsqlmag2k/
html/MultipleInstances.asp
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||If the server later changes name or joins a domain will th instance name
change as well. What I'm doing is replacing are existing SQL server with a
new SQL Server. I've named the new server newsql and made it a member of a
workgroup. After I move the databses over I would then change change the
name on the old server and then make the new server the name of the old
server and join it to the domain. Will the instance name chnage when the
server's name changes?
"Sophie Guo [MSFT]" wrote:

> Hello,
> With Microsoft SQL Server 2000, you have the option of installing multiple
> copies, or instances of SQL Server on one computer. When setting up a new
> installation of SQL Server 2000 or maintaining an existing installation,
> you can specify it as:
> A default instance of SQL Server.
> This instance is identified by the network name of the computer on which it
> is running. Applications using client software from earlier versions of SQL
> Server can connect to a default instance. SQL Server version 6.5 or SQL
> Server version 7.0 servers can operate as default instances. However, a
> computer can have only ONE version functioning as the default instance at a
> time.
> A named instance of SQL Server.
> This instance is identified by the network name of the computer plus an
> instance name, in the format <computername>\<instancename>. Applications
> must use SQL Server 2000 client components to connect to a named instance.
> A computer can run any number of named instances of SQL Server
> concurrently. A named instance can run at the same time as an existing
> installation of SQL Server version 6.5 or SQL Server version 7.0. The
> instance name cannot exceed 16 characters.
> The maximum number of instances supported in SQL Server 2000 is 16. For
> more information, refer to the following articles:
> Multiple Instances of SQL Server
> http://msdn.microsoft.com/library/de...us/architec/8_
> ar_cs_9i5u.asp
> Working with Named and Multiple Instances of SQL Server 2000
> http://msdn.microsoft.com/library/de...us/instsql/in_
> runsetup_2xmb.asp
> Multiple Instances
> http://msdn.microsoft.com/library/de...us/dnsqlmag2k/
> html/MultipleInstances.asp
>
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hello,
After renaming a server, we can perform the following steps to rename SQL
server instance:
1. Restart the server and then start the SQL Server service. This should
automatically get the *new* Server name and we only need to run the
following procedures to
update syservers system table:
-- For a default instance
EXEC sp_dropserver ''old_name''
GO
EXEC sp_addserver ''new_name'', local
GO
-- For a named instance
EXEC sp_dropserver ''original_name\original_instance_name''
GO
EXEC sp_addserver ''new_server_name\instance_name'', local
GO
2. After running the sp_addserver stored procedure, we must restart the SQL
Server service for the change to take effect. The following article
describes this
scenario in more detail:
303774 BUG: "Renaming A Server" Topic in SQL Server Books Online is
Incomplete
http://support.microsoft.com/?id=303774
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Named Instances

Is there any limit for installing named instances on a server?
Hi
Yes, maximum supported on SQL Server 2000 is 16.
If you have per server licensing, you need a licence for each instance (per
processor licence covers all instances).
Memory. They all need to share the same RAM. If you have 16 instances, with
each needing 1Gb, you need at least 16Gb of RAM on the box.
Regards
Mike
"SB138" wrote:

> Is there any limit for installing named instances on a server?
|||Thanks
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Yes, maximum supported on SQL Server 2000 is 16.
> If you have per server licensing, you need a licence for each instance (per
> processor licence covers all instances).
> Memory. They all need to share the same RAM. If you have 16 instances, with
> each needing 1Gb, you need at least 16Gb of RAM on the box.
> Regards
> Mike
> "SB138" wrote:

named instances

how can I find all the named instances of SQL Server on a server? I want to
compare my licneses to what is really out there.rich,
One can use WMI (Windows Management Instrumentation) to query the services
available on a server. Each SQL Server instance requires a separate service.
http://www.microsoft.com/whdc/hwdev...mi/default.mspx
Chief Tenaya
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:DA7C7BB2-8EA2-4DC5-8048-4E8F94E0B6E4@.microsoft.com...
> how can I find all the named instances of SQL Server on a server? I want
to compare my licneses to what is really out there.|||You can find information on this at www.sqldev.net You may
want to take a look at the following utility available at
the site:
http://www.sqldev.net/misc/ListSQLSvr.htm
-Sue
On Fri, 9 Apr 2004 07:31:13 -0700, "rich"
<anonymous@.discussions.microsoft.com> wrote:

>how can I find all the named instances of SQL Server on a server? I want to compar
e my licneses to what is really out there.|||You could also take a look at the services on the machine. Anything
starting with MSSQL is an instance of SQL/MSDE.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Named Instance timeout problem

I am running two large applications on a 4 cpu SQL server with 2 named insta
nces.
There are times when I am running a query on one named instance that users o
n the other instance are getting time out errors. I am trying to justify gi
ving each instance limits on which cpu's they can use and also potentially m
emory. I tried giving the
more critical application 3 cpu's and the other one cpu. There was still a
similar problem on the critical app when running a intensive query on the ot
her which was just allocated one cpu. My boss insisted that I change both b
ack to sharing all cpu's.
My argument is that if both can hog all 4 cpu's, I can guarantee that there
will be a problem.
When the large queries are running, the HD lights are on constant and the CP
U's are all pegged at 100%. The databases for each application are stored o
n their own mirrored HD.
I was wondering if anyone out there has experienced a similar problem and wo
ndering where to go from here.
Thank You,
EverettWe went with defaults when the instance was installed. I think both are
dynamic.
"Vikram Jayaram [MS]" wrote:

> What about memory allocation? Are both instnaces alloted to have Dynamic
> Memory? In that case one instnace could be hogging system memory and not
> granting the other enough, on time.
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
>|||Right then., Try alloting one of those to fixed memory of a lesser amount
and see if the behaviour still exists. Worth a try!
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Wednesday, March 28, 2012

Named Pipes problem on Clustered SQL Server

named pipes

Monday, March 26, 2012

Named instances..anything different than default ?

Named instances..anything different than default ?

Named instances..anything different than default ?

Named Instances Problem (I Think)

named instances and sp3a - how to enable access?

named instances and sp3a - how to enable access?

Named Instances

named instances

Named Instances

Named Instances

named instances

Named Instances

Named Instances

Named Instances

Named Instances

Named Instances

named instances

Named Instance timeout problem

MDF Files currupted

Blog Archive

About Me