question with sql 2005 named instance port assignment

hi
i have a sql 2000 default instance and i just installed a sql 2005
named instance. now i want to assign a specific port to my sql 2005
instance
i am trying to figure out the IPAll section of the TCP/IP Properties --
> IP Addresses tab of the sql server configuration manager.
my entries look like this
IP1 has the following settings (i've masked out the IP addresses with
x's)
Active: Yes
Enabled: No
IP Address: xxx.xxx.x.xxx
TCP Dynamic Ports: 0
TCP Port:
IP2 has the following settings:
Active: Yes
Enabled: No
IP Address: xxx.x.x.x
TCP Dynamic Ports: 0
TCP Port:
IPAll
TCP Dynamic Ports: 1500
TCP Port:
it looks like sql server 2005 named instance now listens on port
1500. I then read this in the books on line:
To configure a static port, leave the TCP Dynamic Ports box blank and
provide an available port number in the TCP Port box
TCP Dynamic Ports
Blank, if dynamic ports are not enabled. To use dynamic ports, set to
0.
For IPAll, displays the port number of the dynamic port used.
which is correct?
Delete the TCP Dynamic Ports value and supply a value for TCP Port and
(re)start SQL Service. Check the SQL Errorlog to make sure it's picked up
the change.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1173191178.548086.32920@.c51g2000cwc.googlegro ups.com...
> hi
> i have a sql 2000 default instance and i just installed a sql 2005
> named instance. now i want to assign a specific port to my sql 2005
> instance
> i am trying to figure out the IPAll section of the TCP/IP Properties --
> my entries look like this
> IP1 has the following settings (i've masked out the IP addresses with
> x's)
> Active: Yes
> Enabled: No
> IP Address: xxx.xxx.x.xxx
> TCP Dynamic Ports: 0
> TCP Port:
> IP2 has the following settings:
> Active: Yes
> Enabled: No
> IP Address: xxx.x.x.x
> TCP Dynamic Ports: 0
> TCP Port:
> IPAll
> TCP Dynamic Ports: 1500
> TCP Port:
> it looks like sql server 2005 named instance now listens on port
> 1500. I then read this in the books on line:
> To configure a static port, leave the TCP Dynamic Ports box blank and
> provide an available port number in the TCP Port box
> TCP Dynamic Ports
> Blank, if dynamic ports are not enabled. To use dynamic ports, set to
> 0.
> For IPAll, displays the port number of the dynamic port used.
>
> which is correct?
>
|||On Mar 6, 4:07 pm, "Jasper Smith" <jasper_smi...@.hotmail.com> wrote:[vbcol=seagreen]
> Delete the TCP Dynamic Ports value and supply a value for TCP Port and
> (re)start SQL Service. Check the SQL Errorlog to make sure it's picked up
> the change.
> --
> HTH,
> Jasper Smith (SQL Server MVP)http://www.sqldbatips.com
> "Derek" <gepetto_2...@.yahoo.com> wrote in message
> news:1173191178.548086.32920@.c51g2000cwc.googlegro ups.com...
>
>
>
>
>
>
>
>
>
Hi Jasper, thank you. is there a reason why to choose this method
over the other?

question with sql 2005 named instance port assignment

hi
i have a sql 2000 default instance and i just installed a sql 2005
named instance. now i want to assign a specific port to my sql 2005
instance
i am trying to figure out the IPAll section of the TCP/IP Properties --
> IP Addresses tab of the sql server configuration manager.
my entries look like this
IP1 has the following settings (i've masked out the IP addresses with
x's)
Active: Yes
Enabled: No
IP Address: xxx.xxx.x.xxx
TCP Dynamic Ports: 0
TCP Port:
IP2 has the following settings:
Active: Yes
Enabled: No
IP Address: xxx.x.x.x
TCP Dynamic Ports: 0
TCP Port:
IPAll
TCP Dynamic Ports: 1500
TCP Port:
it looks like sql server 2005 named instance now listens on port
1500. I then read this in the books on line:
To configure a static port, leave the TCP Dynamic Ports box blank and
provide an available port number in the TCP Port box
TCP Dynamic Ports
Blank, if dynamic ports are not enabled. To use dynamic ports, set to
0.
For IPAll, displays the port number of the dynamic port used.
which is correct?Delete the TCP Dynamic Ports value and supply a value for TCP Port and
(re)start SQL Service. Check the SQL Errorlog to make sure it's picked up
the change.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1173191178.548086.32920@.c51g2000cwc.googlegroups.com...
> hi
> i have a sql 2000 default instance and i just installed a sql 2005
> named instance. now i want to assign a specific port to my sql 2005
> instance
> i am trying to figure out the IPAll section of the TCP/IP Properties --
> my entries look like this
> IP1 has the following settings (i've masked out the IP addresses with
> x's)
> Active: Yes
> Enabled: No
> IP Address: xxx.xxx.x.xxx
> TCP Dynamic Ports: 0
> TCP Port:
> IP2 has the following settings:
> Active: Yes
> Enabled: No
> IP Address: xxx.x.x.x
> TCP Dynamic Ports: 0
> TCP Port:
> IPAll
> TCP Dynamic Ports: 1500
> TCP Port:
> it looks like sql server 2005 named instance now listens on port
> 1500. I then read this in the books on line:
> To configure a static port, leave the TCP Dynamic Ports box blank and
> provide an available port number in the TCP Port box
> TCP Dynamic Ports
> Blank, if dynamic ports are not enabled. To use dynamic ports, set to
> 0.
> For IPAll, displays the port number of the dynamic port used.
>
> which is correct?
>|||On Mar 6, 4:07 pm, "Jasper Smith" <jasper_smi...@.hotmail.com> wrote:[vbcol=seagreen]
> Delete the TCP Dynamic Ports value and supply a value for TCP Port and
> (re)start SQL Service. Check the SQL Errorlog to make sure it's picked up
> the change.
> --
> HTH,
> Jasper Smith (SQL Server MVP)http://www.sqldbatips.com
> "Derek" <gepetto_2...@.yahoo.com> wrote in message
> news:1173191178.548086.32920@.c51g2000cwc.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
Hi Jasper, thank you. is there a reason why to choose this method
over the other?

question with sql 2005 named instance port assignment

hi
i have a sql 2000 default instance and i just installed a sql 2005
named instance. now i want to assign a specific port to my sql 2005
instance
i am trying to figure out the IPAll section of the TCP/IP Properties --
> IP Addresses tab of the sql server configuration manager.
my entries look like this
IP1 has the following settings (i've masked out the IP addresses with
x's)
Active: Yes
Enabled: No
IP Address: xxx.xxx.x.xxx
TCP Dynamic Ports: 0
TCP Port:
IP2 has the following settings:
Active: Yes
Enabled: No
IP Address: xxx.x.x.x
TCP Dynamic Ports: 0
TCP Port:
IPAll
TCP Dynamic Ports: 1500
TCP Port:
it looks like sql server 2005 named instance now listens on port
1500. I then read this in the books on line:
To configure a static port, leave the TCP Dynamic Ports box blank and
provide an available port number in the TCP Port box
TCP Dynamic Ports
Blank, if dynamic ports are not enabled. To use dynamic ports, set to
0.
For IPAll, displays the port number of the dynamic port used.
which is correct?Delete the TCP Dynamic Ports value and supply a value for TCP Port and
(re)start SQL Service. Check the SQL Errorlog to make sure it's picked up
the change.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1173191178.548086.32920@.c51g2000cwc.googlegroups.com...
> hi
> i have a sql 2000 default instance and i just installed a sql 2005
> named instance. now i want to assign a specific port to my sql 2005
> instance
> i am trying to figure out the IPAll section of the TCP/IP Properties --
>> IP Addresses tab of the sql server configuration manager.
> my entries look like this
> IP1 has the following settings (i've masked out the IP addresses with
> x's)
> Active: Yes
> Enabled: No
> IP Address: xxx.xxx.x.xxx
> TCP Dynamic Ports: 0
> TCP Port:
> IP2 has the following settings:
> Active: Yes
> Enabled: No
> IP Address: xxx.x.x.x
> TCP Dynamic Ports: 0
> TCP Port:
> IPAll
> TCP Dynamic Ports: 1500
> TCP Port:
> it looks like sql server 2005 named instance now listens on port
> 1500. I then read this in the books on line:
> To configure a static port, leave the TCP Dynamic Ports box blank and
> provide an available port number in the TCP Port box
> TCP Dynamic Ports
> Blank, if dynamic ports are not enabled. To use dynamic ports, set to
> 0.
> For IPAll, displays the port number of the dynamic port used.
>
> which is correct?
>|||On Mar 6, 4:07 pm, "Jasper Smith" <jasper_smi...@.hotmail.com> wrote:
> Delete the TCP Dynamic Ports value and supply a value for TCP Port and
> (re)start SQL Service. Check the SQL Errorlog to make sure it's picked up
> the change.
> --
> HTH,
> Jasper Smith (SQL Server MVP)http://www.sqldbatips.com
> "Derek" <gepetto_2...@.yahoo.com> wrote in message
> news:1173191178.548086.32920@.c51g2000cwc.googlegroups.com...
>
> > hi
> > i have a sql 2000 default instance and i just installed a sql 2005
> > named instance. now i want to assign a specific port to my sql 2005
> > instance
> > i am trying to figure out the IPAll section of the TCP/IP Properties --
> >> IP Addresses tab of the sql server configuration manager.
> > my entries look like this
> > IP1 has the following settings (i've masked out the IP addresses with
> > x's)
> > Active: Yes
> > Enabled: No
> > IP Address: xxx.xxx.x.xxx
> > TCP Dynamic Ports: 0
> > TCP Port:
> > IP2 has the following settings:
> > Active: Yes
> > Enabled: No
> > IP Address: xxx.x.x.x
> > TCP Dynamic Ports: 0
> > TCP Port:
> > IPAll
> > TCP Dynamic Ports: 1500
> > TCP Port:
> > it looks like sql server 2005 named instance now listens on port
> > 1500. I then read this in the books on line:
> > To configure a static port, leave the TCP Dynamic Ports box blank and
> > provide an available port number in the TCP Port box
> > TCP Dynamic Ports
> > Blank, if dynamic ports are not enabled. To use dynamic ports, set to
> > 0.
> > For IPAll, displays the port number of the dynamic port used.
> > which is correct?
Hi Jasper, thank you. is there a reason why to choose this method
over the other?sql

Question Regarding Job Schedules

Hi

My instance of SQL Server 2005 is installed on a Win Server 2003 box. I am trying to schedule an SSIS job. I have created a proxy using my NTLogin as Credential. When I run the job, it returns with the following error:

Message
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'XXXXXXXX', error code 0x54b. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
where XXXXXXXX is my Domain\NTLogin

I access the Server box and discovered that the Windows Task Scheduling Service is not running. Does SQL Server Scheduling depend on this?

I'm quite sure that SQL Server Agent has nothing to do with Windows Task Scheduler. However I have no idea about your error...|||

Hi

Thanks for the reply. Here is what I am doing to test this:

I have a Test SSIS package that has a singe Execute SQL Task which connects to a SQL 2000 db and makes an insert into a table. This works fine when I run it through the VS Studio IDE.

After this, I registered the Package with the Integration Services and when I execute the package by right clicking and selecting the Execute Package option it executes again.

I restarted the SQL Server Agent to run under my NT account. Now, the first piece of the puzzle is that when I try to create a new credential with my NT Account, it throws an error saying that :
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

An error occurred during encryption. (Microsoft SQL Server, Error: 15467)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=09.00.1116&EvtSrc=MSSQLServer&EvtID=15467&LinkId=20476
I ran a Create Credential Script to insert the credential and created a proxy using that. Then I created a job with a new SSIS step that runs under the proxy. Now when I try to run the job it fails to execute with the following two entires in the log:

Message
[298] SQLServer Error: 3621, The statement has been terminated. [SQLSTATE 01000] (ConnIsLoginSysAdmin)

Message
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'CSFB\jgattani', error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
Can I provide you with any more information that might assist you in trying to figure out whats going on?

Regards
Jay

|||Hi

I had posted my question a couple of days back and did not get a response. Is the question vague or does it need more information. Please let me know if I could provide more details in understanding the problem I am facing.

We are stuck at this point and would like to get this resolved and use the SQL Server 2005 instead of good old SQL Server 2000.

Thanks
|||Hi,

just an idea... Changing the service login wasn't supported in older builds of SQL Server 2005. Perhaps there is still a problem with that... I would not change the service's credentials...

Encryption problem can also occur when you store "save" data (like database logins) within the package and encrypt the package with the user key. You should use a password for the package instead and use it when you run the package...

Again: just an idea...|||Hi

Thanks for the response. I think that the root cause of the issue is my inability to create the credential. If I have the SQL Server Agent running under my NT Acount and then the job Steps running under the "SQL Agent Service Account" and my nt account as the owner of the job, everything works perfect.

sql

Question regarding Instance

If I do a default SQL Server installation, and then find that I'd like to
have several instances installed after all, will I be stuck?
Thanks,
- Joe Geretz -
Hi,
When ever you do a new SQL server Installation after a default installation,
The Installation program will identify the old instance
and will create a NEW NAMED INSTANCE automatically.
All the subsequent installation after the default installation the server
name will be "MACHINENAME\<NAME YOU GIVE>"
So no worry, you will never get struck.
Thanks
Hari
MCDBA
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:#lMllL4FEHA.1396@.TK2MSFTNGP11.phx.gbl...
> If I do a default SQL Server installation, and then find that I'd like to
> have several instances installed after all, will I be stuck?
> Thanks,
> - Joe Geretz -
>

Question regarding Instance

If I do a default SQL Server installation, and then find that I'd like to
have several instances installed after all, will I be stuck?
Thanks,
- Joe Geretz -Hi,
When ever you do a new SQL server Installation after a default installation,
The Installation program will identify the old instance
and will create a NEW NAMED INSTANCE automatically.
All the subsequent installation after the default installation the server
name will be "MACHINENAME\<NAME YOU GIVE>"
So no worry, you will never get struck.
Thanks
Hari
MCDBA
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:#lMllL4FEHA.1396@.TK2MSFTNGP11.phx.gbl...
> If I do a default SQL Server installation, and then find that I'd like to
> have several instances installed after all, will I be stuck?
> Thanks,
> - Joe Geretz -
>

Question RE /PAE In Active-Active Cluster

I have a 2003 Enterprise Ed 2 node cluster with each node having 8 gig of
memory.
On each active node, I am running a single named instance of sql server 2000
enterprise wiath latest svc packs.
The boot ini on the servers is currently configured with the /PAE switch only.
Each SQL named instance is defined to manage memory dynmaciclly. There are
no plans to add addtl memory to the servers.
I need to account for defined memory within each node to accomodate a
failover of either node to the other and run the resepctive SQL Server
instances.
What impact does the /PAE switch have on this particular configuration and
would it be better instead to use the /3GB switch? I believe that would
provide 3 gigs of memory to each sql server if running on the same node with
1 gig left for the O/S.
Would the /PAE switch not be necessary then?
thanks
Tom
/3GB, /PAE, and /AWE plus sp_configing your memory in SQL are great ideas,
but they all have to be monitored and test with your application, hardware,
and exact configuration. Have you called the hardware vendor to find out
what they suggest from a hardware stand point?
I am sure a true SQL DBA/MVP like Geoff will follow with a detailed SQL
explanation of the switches above for you
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
"Tom Frost" <TomFrost@.discussions.microsoft.com> wrote in message
news:269FF0A2-3F04-4767-8D67-15C9DC20AA95@.microsoft.com...
>I have a 2003 Enterprise Ed 2 node cluster with each node having 8 gig of
> memory.
> On each active node, I am running a single named instance of sql server
> 2000
> enterprise wiath latest svc packs.
> The boot ini on the servers is currently configured with the /PAE switch
> only.
> Each SQL named instance is defined to manage memory dynmaciclly. There are
> no plans to add addtl memory to the servers.
> I need to account for defined memory within each node to accomodate a
> failover of either node to the other and run the resepctive SQL Server
> instances.
> What impact does the /PAE switch have on this particular configuration and
> would it be better instead to use the /3GB switch? I believe that would
> provide 3 gigs of memory to each sql server if running on the same node
> with
> 1 gig left for the O/S.
> Would the /PAE switch not be necessary then?
> thanks
> Tom
>
|||Right now, your SQL Instances get about 1.6GB of physical RAM each. They
should increase memory usage until they get to that level then stay there
long term.
/PAE lets the Operating System see all the physical memory in the servers.
It is necessary anytime a 32-bit OS has more than 4 GB of physical RAM.
I might enable AWE memory in SQL. If you do, you must set a maximum memory
value for SQL on a multi-instance cluster. Two SQL behaviors combine to
make this a necessity. First, AWE kills dynamic memory allocation. Memory
is allocated at service startup and never shrinks. Second, the maximum
memory value is set to the actual physical memory amount. SQL grabs
everything. You will need to set the actual memory values on both instances
so they can "stack" on the same server without overcommitting memory. Be
sure and leave some for the OS. I would recommend 3GB or so, but you can
watch the Memory | Available MBytes counter to check.
As for the /3GB switch, you could go that route. You won't replace the /PAE
switch, you will add this switch to the boot.ini startup options.
One option I have used in this situation was to set up the two instances
with asymmetrical memory allocation. If you know one server is under higher
load, you can allocate more memory to that instance. I ran a similar system
to yours with 4GB on one instance, 2GB on the other instance, and 2GB left
for the OS. The perfmon counter SQLServer:Buffer manager | Page Life
Expectancy will let you know relative memory pressure. Be sure to use a
fairly long observation time with the server under normal workload to
determine true memory pressure.
Any way you go, you will need to manually set memory levels and monitor to
see of you have overcommitted memory. However, SQL loves memory so you may
see some performance gains. Use the Page Life Expectancy counter to see the
difference before and after.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tom Frost" <TomFrost@.discussions.microsoft.com> wrote in message
news:269FF0A2-3F04-4767-8D67-15C9DC20AA95@.microsoft.com...
>I have a 2003 Enterprise Ed 2 node cluster with each node having 8 gig of
> memory.
> On each active node, I am running a single named instance of sql server
> 2000
> enterprise wiath latest svc packs.
> The boot ini on the servers is currently configured with the /PAE switch
> only.
> Each SQL named instance is defined to manage memory dynmaciclly. There are
> no plans to add addtl memory to the servers.
> I need to account for defined memory within each node to accomodate a
> failover of either node to the other and run the resepctive SQL Server
> instances.
> What impact does the /PAE switch have on this particular configuration and
> would it be better instead to use the /3GB switch? I believe that would
> provide 3 gigs of memory to each sql server if running on the same node
> with
> 1 gig left for the O/S.
> Would the /PAE switch not be necessary then?
> thanks
> Tom
>
|||The /3GB switch simply restricts the OS to 1GB of RAM, which allows
applications to address up to 3GB of RAM. The /PAE switch simply loads a
different NT kernel which contains the code to address RAM > 4GB.
The AWE setting in SQL Server works with the /PAE switch. If you enable
AWE, but don't turn on the /PAE switch, then you can not address the
extended memory space. So, in order to allow AWE to utilize the RAM above
4GB, you need to turn on the /PAE switch as well.
In terms of running multiple instances on a single machine, you need to
balance the RAM. (If your business requirements will allow performance
degradation in the event of a failover, then you don't necessarily need to
do this.) You control the maximum amount of RAM that SQL Server will
address by setting the max server memory config option.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Tom Frost" <TomFrost@.discussions.microsoft.com> wrote in message
news:269FF0A2-3F04-4767-8D67-15C9DC20AA95@.microsoft.com...
>I have a 2003 Enterprise Ed 2 node cluster with each node having 8 gig of
> memory.
> On each active node, I am running a single named instance of sql server
> 2000
> enterprise wiath latest svc packs.
> The boot ini on the servers is currently configured with the /PAE switch
> only.
> Each SQL named instance is defined to manage memory dynmaciclly. There are
> no plans to add addtl memory to the servers.
> I need to account for defined memory within each node to accomodate a
> failover of either node to the other and run the resepctive SQL Server
> instances.
> What impact does the /PAE switch have on this particular configuration and
> would it be better instead to use the /3GB switch? I believe that would
> provide 3 gigs of memory to each sql server if running on the same node
> with
> 1 gig left for the O/S.
> Would the /PAE switch not be necessary then?
> thanks
> Tom
>
sql

Question on upgrade to SQL Server 2005

We are planning to upgrade one of the SQL Server 2000 instance on a
windows 2003 box to SQL Server 2005. I was wondering during the upgrade
will it allow me to chang the name of the existing 2000 instance to a
different name so that we can follow our naming convention.
ThanksSQL Server takes the name of the machine. So, you will have to rename the
machine, and then edit the local server name in the sysservers table.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"shub" <shubtech@.gmail.com> wrote in message
news:1153428116.503813.172530@.p79g2000cwp.googlegroups.com...
> We are planning to upgrade one of the SQL Server 2000 instance on a
> windows 2003 box to SQL Server 2005. I was wondering during the upgrade
> will it allow me to chang the name of the existing 2000 instance to a
> different name so that we can follow our naming convention.
> Thanks
>|||Yes you can rename an Instance -before the upgrade. See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
I think that renaming instances is not currently supported in SQL 2005
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"shub" <shubtech@.gmail.com> wrote in message
news:1153428116.503813.172530@.p79g2000cwp.googlegroups.com...
> We are planning to upgrade one of the SQL Server 2000 instance on a
> windows 2003 box to SQL Server 2005. I was wondering during the upgrade
> will it allow me to chang the name of the existing 2000 instance to a
> different name so that we can follow our naming convention.
> Thanks
>

Question on upgrade to SQL Server 2005

We are planning to upgrade one of the SQL Server 2000 instance on a
windows 2003 box to SQL Server 2005. I was wondering during the upgrade
will it allow me to chang the name of the existing 2000 instance to a
different name so that we can follow our naming convention.
ThanksSQL Server takes the name of the machine. So, you will have to rename the
machine, and then edit the local server name in the sysservers table.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"shub" <shubtech@.gmail.com> wrote in message
news:1153428116.503813.172530@.p79g2000cwp.googlegroups.com...
> We are planning to upgrade one of the SQL Server 2000 instance on a
> windows 2003 box to SQL Server 2005. I was wondering during the upgrade
> will it allow me to chang the name of the existing 2000 instance to a
> different name so that we can follow our naming convention.
> Thanks
>|||Yes you can rename an Instance -before the upgrade. See:
http://msdn.microsoft.com/library/d...nstall_5r8f.asp
I think that renaming instances is not currently supported in SQL 2005
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"shub" <shubtech@.gmail.com> wrote in message
news:1153428116.503813.172530@.p79g2000cwp.googlegroups.com...
> We are planning to upgrade one of the SQL Server 2000 instance on a
> windows 2003 box to SQL Server 2005. I was wondering during the upgrade
> will it allow me to chang the name of the existing 2000 instance to a
> different name so that we can follow our naming convention.
> Thanks
>

Question on SQL Cluster Resource Groups

I have a sql cluster with my virtual server name, IP address, etc. in one
group. In the other group I have my sql instance, san attached disk
resources, sql agent etc. In this second group I also have a ftp server that
utilizes the vbscript for fail over and uses a san disk resource.
The problem I have is if the second group fails over say, due to a sql
server error which has happened, then my ftp site dies as users put to the
ftp site via the name / ip of the virtual server but then the virtual server
cannot see the disk resources that are part of the second group.
Is there any issues with putting the virtual server name & ip address in the
same group with sql, sql agent, disk resources, etc.? In essence, putting
everything in one group.
Thanks in advance,
John
You have a couple of options, and I'm sure different people may have
different opinions w.r.t. their pros and cons.
Option 1:
Move the resources from group 1 into the second group, and edit the
dependencies property of your virtual server to include the disk resources
you mentioned. In order to protect the HA of your SQL Server instance, you
may want to edit the advanced property tab of the resources--moved over from
group 1--so that their faulire doesn't affect the group.
Option 2:
Keep the groups as they are. Create a file share resource(s) in group 2, and
use the file share via the virtual server. I don't know the nature of your
particular virtual server, so I'm not sure this works for you.
Personally, I prefer to keep the SQL Server group clean, and will try very
hard to question any attempt to include additional resources in that group.
However, in your situation you already have a bunch of non-SQL Server native
resources in the SQL group so adding a few more may not do a great deal more
potential harm (of course that all depends on the details of the virtual
server in question.)
Linchi
"John - PDX" <JohnPDX@.discussions.microsoft.com> wrote in message
news:D5396113-A4A6-4EA3-973F-B4CBF2021CDB@.microsoft.com...
>I have a sql cluster with my virtual server name, IP address, etc. in one
> group. In the other group I have my sql instance, san attached disk
> resources, sql agent etc. In this second group I also have a ftp server
> that
> utilizes the vbscript for fail over and uses a san disk resource.
> The problem I have is if the second group fails over say, due to a sql
> server error which has happened, then my ftp site dies as users put to the
> ftp site via the name / ip of the virtual server but then the virtual
> server
> cannot see the disk resources that are part of the second group.
> Is there any issues with putting the virtual server name & ip address in
> the
> same group with sql, sql agent, disk resources, etc.? In essence, putting
> everything in one group.
> --
> Thanks in advance,
> John
|||I think your biggest problem is one of definition. By definition, each
Cluster Resource Group IS A SEPARATE VIRTUAL SERVER.
Each group has its own Disk, IP, Network Name, and application resources.
If the FTP server is dependent on the SQL Server instance, then define the
FTP site resources within the SQL Server Cluster Resource Group; otherwise,
create a separate Cluster Resource Group for IIS.
Every Cluster has at least 1 resource group BEFORE there exists any failover
applications, the Cluster Group itself, which must stay independent and
uncontaminated by other resources.
Cluster Resource Group:
Quorum Disk, Cluster IP, Cluster Network Name.
Microsoft Distributed Transaction Coordinator (MS DTC) Resource Group:
MSDTC dedicated Disk, MSDTC IP, MSDTC Network Name.
SQL Server Resource Group:
SQL Server dedicated Disk, SQL Server IP, SQL Server Network Name, SQL
Server Instance, SQL Agent.
Internet Information Server (IIS) Resource Group:
IIS dedicated Disk, IIS IP, IIS Network Name, FTP Site.
Although possible to include the IIS resources within the SQL Server
Resource Group, you would be better off creating a separate group dedicated
to IIS resources. End users would connect to SQL Server through the SQL
Server Cluster Resource Group Network Name and SQL Server Instance name
defined within that group. End users would connect to the FTP site through
the IIS Cluster Resource Group Network Name and FTP site name defined within
that group.
Whenever a Cluster Resource Group fails over, the Disk, IP, Network Name,
and dependent application resources defined within that group fail over
together. Even if the SQL Server resource group failed over, the FTP site
would still be available on the original host through the IIS Cluster
Resource Group Network Name. They are independent.
You use the physical node names to define cluster resources and to manage
non-clustered components on each node, like the OS and swap spaces, Network
Backup software, etc. You use the Cluster Resource Group Network Name to
manage the cluster as a whole. You use the MS DTC Network Name to manage MS
DTC, the SQL Server Network Name to manage SQL Server, and, finally, the IIS
Network Name to manager IIS. All independently but from within the same
cluster.
To go one step farther, you should really install separate hardware and
cluster IIS using Microsoft Network Load Balancing high availability
clustering, not Sever Cluster failover clustering.
Hope this helps.
Sincerely,
Anthony Thomas

"Linchi Shea" <linchi_shea@.NOSPAMml.om> wrote in message
news:OqN4Qps0FHA.2932@.TK2MSFTNGP10.phx.gbl...
> You have a couple of options, and I'm sure different people may have
> different opinions w.r.t. their pros and cons.
> Option 1:
> Move the resources from group 1 into the second group, and edit the
> dependencies property of your virtual server to include the disk resources
> you mentioned. In order to protect the HA of your SQL Server instance, you
> may want to edit the advanced property tab of the resources--moved over
from
> group 1--so that their faulire doesn't affect the group.
> Option 2:
> Keep the groups as they are. Create a file share resource(s) in group 2,
and
> use the file share via the virtual server. I don't know the nature of your
> particular virtual server, so I'm not sure this works for you.
> Personally, I prefer to keep the SQL Server group clean, and will try very
> hard to question any attempt to include additional resources in that
group.
> However, in your situation you already have a bunch of non-SQL Server
native
> resources in the SQL group so adding a few more may not do a great deal
more[vbcol=seagreen]
> potential harm (of course that all depends on the details of the virtual
> server in question.)
> Linchi
> "John - PDX" <JohnPDX@.discussions.microsoft.com> wrote in message
> news:D5396113-A4A6-4EA3-973F-B4CBF2021CDB@.microsoft.com...
the[vbcol=seagreen]
putting
>

Question on Settings in Connection

I've programmed a user defined function (SQL2000), which in a specific query
references a linked server (another SQL instance, BTW contained in same
physical server). The sintaxis is ok, but i couldn't apply the definition
because of following error:
"Error 7405: Heteogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
options to be set for the connection. This ensures consistente
query semantics. Enable these options and then reissue your query."
I set the corresponding settings in both servers, section Connections of
Server's properties, but to no avail.
Which is the trick here? How is resolved the 'connection' issue referred in
the error message?
Thanks in advanceMiguel Castanuela (MiguelCastanuela@.discussions.microsoft.com) writes:
> I've programmed a user defined function (SQL2000), which in a specific
> query references a linked server (another SQL instance, BTW contained in
> same physical server). The sintaxis is ok, but i couldn't apply the
> definition because of following error:
> "Error 7405: Heteogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
> options to be set for the connection. This ensures consistente
> query semantics. Enable these options and then reissue your query."
> I set the corresponding settings in both servers, section Connections of
> Server's properties, but to no avail.
> Which is the trick here? How is resolved the 'connection' issue referred
> in the error message?
The trick is to stop using Enterprise Manager for editing functions and
stored procedures. Use Query Analyzer instead, this is a far better tool
for the task.
The particular problem here, is that Enterprise Manager creates functions
and procedures with ANSI_NULLS and QUOTED_IDENTIFIER OFF, and these
settings are saved with the procedure/function. Thus you need to recreate
the function with ANSI_NULLS ON. (In Query Analyzer all needed options
are ON by default.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Many thanks, it resolves the problem.
In ahead I will take this great tip in account.
"Erland Sommarskog" wrote:

> Miguel Castanuela (MiguelCastanuela@.discussions.microsoft.com) writes:
> The trick is to stop using Enterprise Manager for editing functions and
> stored procedures. Use Query Analyzer instead, this is a far better tool
> for the task.
> The particular problem here, is that Enterprise Manager creates functions
> and procedures with ANSI_NULLS and QUOTED_IDENTIFIER OFF, and these
> settings are saved with the procedure/function. Thus you need to recreate
> the function with ANSI_NULLS ON. (In Query Analyzer all needed options
> are ON by default.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Question on named instance

Hi,
I have sql2000 and 2 instances running, none have the default port. From the
BOL:
"A TCP port is chosen dynamically the first time the MSSQL$instancename
service is started."
"SQL Server 2000 clients do not have to be configured to connect to an
instance of SQL Server 2000. The SQL Server 2000 client components query a
computer running instances of SQL Server 2000 to determine the Net-Libraries
and network addresses for each instance. The client components then
transparently choose a supported Net-Library and address for the connection
without having to be configured on the client. The only information the
application must supply is the computer name and instance name."
Questions:
1, If I restart the MSSQL$instancename service the port is going to change
or fixed there?
2, what is "client components" refers to?
2, I need to connect to these instances from jdbc which only needs server
name and port number which is different from the above statement, how can I
connect?
Thanks
> 1, If I restart the MSSQL$instancename service the port is going to
change
> or fixed there?
I have no idea about this. If I had to guess, I would say that it
doesn't change. Suppose that a client obtains the port number for an
instance, then the instance gets restarted; now the client has an
incorrect port number (if the port has changed) and it has to get all
that information again. But this is just a guess.

> 2, what is "client components" refers to?
MDAC, JDBC drivers, any client libraries.

> 2, I need to connect to these instances from jdbc which only needs
server
> name and port number which is different from the above statement, how
can I
> connect?
You can look into the registry and obtain the port number from there
(not sure exactly where, but you could google for it) or you could use
a JDBC driver that "knows" how to determine ports for named instances,
such as the open source jTDS or the commercial drivers.
Alin.
Disclaimer: I am a jTDS developer.
|||Jen
The port the instance uses is fixed. You should find the default instance
will be running on port 1433. The named instance will be assigned by SQL
Server when you created the instance. If you want to see what ports you are
using, from the SQL Server programs group choose Server Network Utility (You
need to do this on the server running SQL Server). On there click on TCP/IP
and then properties, this will show you the port the instance is using.
Regards
John
"Alin Sinpalean" wrote:

> change
> I have no idea about this. If I had to guess, I would say that it
> doesn't change. Suppose that a client obtains the port number for an
> instance, then the instance gets restarted; now the client has an
> incorrect port number (if the port has changed) and it has to get all
> that information again. But this is just a guess.
>
> MDAC, JDBC drivers, any client libraries.
> server
> can I
> You can look into the registry and obtain the port number from there
> (not sure exactly where, but you could google for it) or you could use
> a JDBC driver that "knows" how to determine ports for named instances,
> such as the open source jTDS or the commercial drivers.
> Alin.
> Disclaimer: I am a jTDS developer.
>
|||The automatic assignment of ports on named instances that WERE NOT
configured manually do not change. However, upon startup, the first time,
one is acquired and cached. Upon secondary startups, if that same port is
in use by another process, then SQL Server will attempt binding to a new
port number.
What is meant by port assignment on the client end is that SQL Server
exposes UDP 1434, called Dynamic Discovery. Anyone who queries this will
receive a list of instances and port assignments which will automatically
configure clients to connect to those ports.
Sincerely,
Anthony Thomas

"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:932AC1DF-7195-416B-8DD6-6110BFEB4086@.microsoft.com...
Jen
The port the instance uses is fixed. You should find the default instance
will be running on port 1433. The named instance will be assigned by SQL
Server when you created the instance. If you want to see what ports you are
using, from the SQL Server programs group choose Server Network Utility (You
need to do this on the server running SQL Server). On there click on TCP/IP
and then properties, this will show you the port the instance is using.
Regards
John
"Alin Sinpalean" wrote:

> change
> I have no idea about this. If I had to guess, I would say that it
> doesn't change. Suppose that a client obtains the port number for an
> instance, then the instance gets restarted; now the client has an
> incorrect port number (if the port has changed) and it has to get all
> that information again. But this is just a guess.
>
> MDAC, JDBC drivers, any client libraries.
> server
> can I
> You can look into the registry and obtain the port number from there
> (not sure exactly where, but you could google for it) or you could use
> a JDBC driver that "knows" how to determine ports for named instances,
> such as the open source jTDS or the commercial drivers.
> Alin.
> Disclaimer: I am a jTDS developer.
>

Question on named instance

Hi,
I have sql2000 and 2 instances running, none have the default port. From the
BOL:
"A TCP port is chosen dynamically the first time the MSSQL$instancename
service is started."
"SQL Server 2000 clients do not have to be configured to connect to an
instance of SQL Server 2000. The SQL Server 2000 client components query a
computer running instances of SQL Server 2000 to determine the Net-Libraries
and network addresses for each instance. The client components then
transparently choose a supported Net-Library and address for the connection
without having to be configured on the client. The only information the
application must supply is the computer name and instance name."
Questions:
1, If I restart the MSSQL$instancename service the port is going to change
or fixed there?
2, what is "client components" refers to?
2, I need to connect to these instances from jdbc which only needs server
name and port number which is different from the above statement, how can I
connect?
Thanks> 1, If I restart the MSSQL$instancename service the port is going to
change
> or fixed there?
I have no idea about this. If I had to guess, I would say that it
doesn't change. Suppose that a client obtains the port number for an
instance, then the instance gets restarted; now the client has an
incorrect port number (if the port has changed) and it has to get all
that information again. But this is just a guess.

> 2, what is "client components" refers to?
MDAC, JDBC drivers, any client libraries.

> 2, I need to connect to these instances from jdbc which only needs
server
> name and port number which is different from the above statement, how
can I
> connect?
You can look into the registry and obtain the port number from there
(not sure exactly where, but you could google for it) or you could use
a JDBC driver that "knows" how to determine ports for named instances,
such as the Open Source jTDS or the commercial drivers.
Alin.
Disclaimer: I am a jTDS developer.|||Jen
The port the instance uses is fixed. You should find the default instance
will be running on port 1433. The named instance will be assigned by SQL
Server when you created the instance. If you want to see what ports you are
using, from the SQL Server programs group choose Server Network Utility (You
need to do this on the server running SQL Server). On there click on TCP/IP
and then properties, this will show you the port the instance is using.
Regards
John
"Alin Sinpalean" wrote:

> change
> I have no idea about this. If I had to guess, I would say that it
> doesn't change. Suppose that a client obtains the port number for an
> instance, then the instance gets restarted; now the client has an
> incorrect port number (if the port has changed) and it has to get all
> that information again. But this is just a guess.
>
> MDAC, JDBC drivers, any client libraries.
>
> server
> can I
> You can look into the registry and obtain the port number from there
> (not sure exactly where, but you could google for it) or you could use
> a JDBC driver that "knows" how to determine ports for named instances,
> such as the Open Source jTDS or the commercial drivers.
> Alin.
> Disclaimer: I am a jTDS developer.
>|||The automatic assignment of ports on named instances that WERE NOT
configured manually do not change. However, upon startup, the first time,
one is acquired and cached. Upon secondary startups, if that same port is
in use by another process, then SQL Server will attempt binding to a new
port number.
What is meant by port assignment on the client end is that SQL Server
exposes UDP 1434, called Dynamic Discovery. Anyone who queries this will
receive a list of instances and port assignments which will automatically
configure clients to connect to those ports.
Sincerely,
Anthony Thomas
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:932AC1DF-7195-416B-8DD6-6110BFEB4086@.microsoft.com...
Jen
The port the instance uses is fixed. You should find the default instance
will be running on port 1433. The named instance will be assigned by SQL
Server when you created the instance. If you want to see what ports you are
using, from the SQL Server programs group choose Server Network Utility (You
need to do this on the server running SQL Server). On there click on TCP/IP
and then properties, this will show you the port the instance is using.
Regards
John
"Alin Sinpalean" wrote:

> change
> I have no idea about this. If I had to guess, I would say that it
> doesn't change. Suppose that a client obtains the port number for an
> instance, then the instance gets restarted; now the client has an
> incorrect port number (if the port has changed) and it has to get all
> that information again. But this is just a guess.
>
> MDAC, JDBC drivers, any client libraries.
>
> server
> can I
> You can look into the registry and obtain the port number from there
> (not sure exactly where, but you could google for it) or you could use
> a JDBC driver that "knows" how to determine ports for named instances,
> such as the Open Source jTDS or the commercial drivers.
> Alin.
> Disclaimer: I am a jTDS developer.
>

Question on named instance

Hi,
I have sql2000 and 2 instances running, none have the default port. From the
BOL:
"A TCP port is chosen dynamically the first time the MSSQL$instancename
service is started."
"SQL Server 2000 clients do not have to be configured to connect to an
instance of SQL Server 2000. The SQL Server 2000 client components query a
computer running instances of SQL Server 2000 to determine the Net-Libraries
and network addresses for each instance. The client components then
transparently choose a supported Net-Library and address for the connection
without having to be configured on the client. The only information the
application must supply is the computer name and instance name."
Questions:
1, If I restart the MSSQL$instancename service the port is going to change
or fixed there?
2, what is "client components" refers to?
2, I need to connect to these instances from jdbc which only needs server
name and port number which is different from the above statement, how can I
connect?
Thanks> 1, If I restart the MSSQL$instancename service the port is going to
change
> or fixed there?
I have no idea about this. If I had to guess, I would say that it
doesn't change. Suppose that a client obtains the port number for an
instance, then the instance gets restarted; now the client has an
incorrect port number (if the port has changed) and it has to get all
that information again. But this is just a guess.
> 2, what is "client components" refers to?
MDAC, JDBC drivers, any client libraries.
> 2, I need to connect to these instances from jdbc which only needs
server
> name and port number which is different from the above statement, how
can I
> connect?
You can look into the registry and obtain the port number from there
(not sure exactly where, but you could google for it) or you could use
a JDBC driver that "knows" how to determine ports for named instances,
such as the open source jTDS or the commercial drivers.
Alin.
Disclaimer: I am a jTDS developer.|||Jen
The port the instance uses is fixed. You should find the default instance
will be running on port 1433. The named instance will be assigned by SQL
Server when you created the instance. If you want to see what ports you are
using, from the SQL Server programs group choose Server Network Utility (You
need to do this on the server running SQL Server). On there click on TCP/IP
and then properties, this will show you the port the instance is using.
Regards
John
"Alin Sinpalean" wrote:
> > 1, If I restart the MSSQL$instancename service the port is going to
> change
> > or fixed there?
> I have no idea about this. If I had to guess, I would say that it
> doesn't change. Suppose that a client obtains the port number for an
> instance, then the instance gets restarted; now the client has an
> incorrect port number (if the port has changed) and it has to get all
> that information again. But this is just a guess.
> > 2, what is "client components" refers to?
> MDAC, JDBC drivers, any client libraries.
> > 2, I need to connect to these instances from jdbc which only needs
> server
> > name and port number which is different from the above statement, how
> can I
> > connect?
> You can look into the registry and obtain the port number from there
> (not sure exactly where, but you could google for it) or you could use
> a JDBC driver that "knows" how to determine ports for named instances,
> such as the open source jTDS or the commercial drivers.
> Alin.
> Disclaimer: I am a jTDS developer.
>|||The automatic assignment of ports on named instances that WERE NOT
configured manually do not change. However, upon startup, the first time,
one is acquired and cached. Upon secondary startups, if that same port is
in use by another process, then SQL Server will attempt binding to a new
port number.
What is meant by port assignment on the client end is that SQL Server
exposes UDP 1434, called Dynamic Discovery. Anyone who queries this will
receive a list of instances and port assignments which will automatically
configure clients to connect to those ports.
Sincerely,
Anthony Thomas
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:932AC1DF-7195-416B-8DD6-6110BFEB4086@.microsoft.com...
Jen
The port the instance uses is fixed. You should find the default instance
will be running on port 1433. The named instance will be assigned by SQL
Server when you created the instance. If you want to see what ports you are
using, from the SQL Server programs group choose Server Network Utility (You
need to do this on the server running SQL Server). On there click on TCP/IP
and then properties, this will show you the port the instance is using.
Regards
John
"Alin Sinpalean" wrote:
> > 1, If I restart the MSSQL$instancename service the port is going to
> change
> > or fixed there?
> I have no idea about this. If I had to guess, I would say that it
> doesn't change. Suppose that a client obtains the port number for an
> instance, then the instance gets restarted; now the client has an
> incorrect port number (if the port has changed) and it has to get all
> that information again. But this is just a guess.
> > 2, what is "client components" refers to?
> MDAC, JDBC drivers, any client libraries.
> > 2, I need to connect to these instances from jdbc which only needs
> server
> > name and port number which is different from the above statement, how
> can I
> > connect?
> You can look into the registry and obtain the port number from there
> (not sure exactly where, but you could google for it) or you could use
> a JDBC driver that "knows" how to determine ports for named instances,
> such as the open source jTDS or the commercial drivers.
> Alin.
> Disclaimer: I am a jTDS developer.
>

Question on Installing Named Instance

Does anyone know if a named instance can be installed without having to
reboot the server right away? Would like to install it now but schedule a
reboot when it would be more convienent.
ThanksForgot to mention, will be installing SQL Server 2000 Standard Edition with
SP3a on Windows Server 2003 Standard edition.
"MACason" wrote:
> Does anyone know if a named instance can be installed without having to
> reboot the server right away? Would like to install it now but schedule a
> reboot when it would be more convienent.
> Thanks

Question on Installing Named Instance

Does anyone know if a named instance can be installed without having to
reboot the server right away? Would like to install it now but schedule a
reboot when it would be more convienent.
ThanksForgot to mention, will be installing SQL Server 2000 Standard Edition with
SP3a on Windows Server 2003 Standard edition.
"MACason" wrote:

> Does anyone know if a named instance can be installed without having to
> reboot the server right away? Would like to install it now but schedule a
> reboot when it would be more convienent.
> Thanks