Is Physical server name stored in the Master database

**Simon Hayes** · Jul 23 '05, 08:37 AM

Re: Is Physical server name stored in the Master database

<gdabbara@brown shoe.com> wrote in message
news:1112383277 .914231.158550@ o13g2000cwo.goo glegroups.com.. .[color=blue]
> Hi,
> We have a situation where we want to move our current database
> server to a different hardware and rename the server.
> If we change the Physical server name, do we have to go thru the whole
> process of changing it in sql server by running sp_dropserver,
> sp_addserver?[/color]

Yes - but this should be a quick task, and it's easily scripted if you have
many servers to change.
[color=blue]
> Can we have an alias for the server name in DNS and can sql server
> resolve the server name internally by going thru DNS?
> For eg: Our current database server name is FFSQL-PRD01. We have it
> registered in EM as FFSQL-PRD01. Now we renamed the server to
> STLSQL-PRD01, and added an entry in DNS for STLSQL-PRD01 with alias of
> FFSQL-PRD01( the same old name as alias). After doing this when I go
> to EM and click on FFSQL-PRD01 would it give me an error? or can it
> resolve the server name by going to DNS.[/color]

As far as I know, EM will resolve the name using a SQL client alias (if you
created one in the client network utility), or the operating system's name
resolution method (DNS, in this case). But why not just change the
registration name in EM, and connect to the real name? If you have a lot of
EM registrations, then you can script them using the SQLDMO ServerGroup and
RegisteredServe r objects.
[color=blue]
> When you install SQl server the default instance has to be the physical
> server name, Why? Is the physical server name stored in Master
> database( SYSSERVERS table)?[/color]

If you have multiple instances, you need to define what 'default' means.
Since Microsoft don't know what you will call your instances, they probably
decided to go with just the physical name as the default; it also means you
can upgrade from previous MSSQL versions without having problems related to
instance naming.
[color=blue]
> Thanks for your help.
> Geetha
>[/color]

To rename a server and change your EM registration is not usually a big job
(unless you have many servers to rename), so I'm not sure why you want to
use an alias to keep the old name - perhaps your real issue is how to
connect client applications to the new server name? If so, it depends on how
the client connects - you may have to change DSNs, or ADO connection
strings, or whatever. You might be able to use DNS aliases or client-side
aliases as part of a migration process, but in the longer run it will make
administration much easier if you use the real server name.

If this doesn't help, I suggest you give some more details about exactly why
you need some sort of alias, rather than just using the new server name.

Simon

**gdabbara@brownshoe.com** · Jul 23 '05, 08:37 AM

Re: Is Physical server name stored in the Master database

Simon Hayes wrote:[color=blue]
> <gdabbara@brown shoe.com> wrote in message
> news:1112383277 .914231.158550@ o13g2000cwo.goo glegroups.com.. .[color=green]
> > Hi,
> > We have a situation where we want to move our current database
> > server to a different hardware and rename the server.
> > If we change the Physical server name, do we have to go thru the[/color][/color]
whole[color=blue][color=green]
> > process of changing it in sql server by running sp_dropserver,
> > sp_addserver?[/color]
>
> Yes - but this should be a quick task, and it's easily scripted if[/color]
you have[color=blue]
> many servers to change.
>[color=green]
> > Can we have an alias for the server name in DNS and can sql server
> > resolve the server name internally by going thru DNS?
> > For eg: Our current database server name is FFSQL-PRD01. We have[/color][/color]
it[color=blue][color=green]
> > registered in EM as FFSQL-PRD01. Now we renamed the server to
> > STLSQL-PRD01, and added an entry in DNS for STLSQL-PRD01 with alias[/color][/color]
of[color=blue][color=green]
> > FFSQL-PRD01( the same old name as alias). After doing this when I[/color][/color]
go[color=blue][color=green]
> > to EM and click on FFSQL-PRD01 would it give me an error? or can it
> > resolve the server name by going to DNS.[/color]
>
> As far as I know, EM will resolve the name using a SQL client alias[/color]
(if you[color=blue]
> created one in the client network utility), or the operating system's[/color]
name[color=blue]
> resolution method (DNS, in this case). But why not just change the
> registration name in EM, and connect to the real name? If you have a[/color]
lot of[color=blue]
> EM registrations, then you can script them using the SQLDMO[/color]
ServerGroup and[color=blue]
> RegisteredServe r objects.
>[color=green]
> > When you install SQl server the default instance has to be the[/color][/color]
physical[color=blue][color=green]
> > server name, Why? Is the physical server name stored in Master
> > database( SYSSERVERS table)?[/color]
>
> If you have multiple instances, you need to define what 'default'[/color]
means.[color=blue]
> Since Microsoft don't know what you will call your instances, they[/color]
probably[color=blue]
> decided to go with just the physical name as the default; it also[/color]
means you[color=blue]
> can upgrade from previous MSSQL versions without having problems[/color]
related to[color=blue]
> instance naming.
>[color=green]
> > Thanks for your help.
> > Geetha
> >[/color]
>
> To rename a server and change your EM registration is not usually a[/color]
big job[color=blue]
> (unless you have many servers to rename), so I'm not sure why you[/color]
want to[color=blue]
> use an alias to keep the old name - perhaps your real issue is how to[/color]
[color=blue]
> connect client applications to the new server name? If so, it depends[/color]
on how[color=blue]
> the client connects - you may have to change DSNs, or ADO connection
> strings, or whatever. You might be able to use DNS aliases or[/color]
client-side[color=blue]
> aliases as part of a migration process, but in the longer run it will[/color]
make[color=blue]
> administration much easier if you use the real server name.
>
> If this doesn't help, I suggest you give some more details about[/color]
exactly why[color=blue]
> you need some sort of alias, rather than just using the new server[/color]
name.[color=blue]
>
> Simon[/color]

Hi,
Thanks a lot for your quick responses. The reason we are pushing
for DNS alias, is because we have a lot of linked server connections
and DTS package jobs that we might have to change as they are using the
server name. I just used EM as an example. I would think DTS packages
would run fine based on what you have said earlier about it being able
to resolve the name from DNS or SQl client config.

Thanks
Geetha

Is Physical server name stored in the Master database

Is Physical server name stored in the Master database

Comment

Comment