High performance isn't always an advantage...

Hello
MSSQL 2000 + SP3 on Windows 2000 AS + SP4
I've moved my databases to new server.
IO subsystem now TEN times faster!
I have the system of two jobs performing defragmentation
of my data. One of the jobs issues DBCC INDEXDEFRAG
statements and the second one monitors state of transaction
log and stops first job if log more than 70% full. SQLAgent
can run jobs only with 1 minute intervals. So I can monitor
log status only once in minute. Everything was all right at old
server. Now on new hardware log overfills in couple of seconds
and monitoring job can't catch the moment of overfilling and
correctly stop the defragmentation.
Two questions:
1. Should I increase Tlog size depending on hardware performance
2. Is there any way to monitor log state more ofter than once
in a minute?
Thanks.
Serge ShakhovYou can have a job which uses an endless loop and WAITFOR with, say, 10 seconds.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Serge Shakhov" <REMOVETHIS_ACETYLENE@.mail.ru> wrote in message
news:83ndmb.5kr.ln@.proxyserver.ctd.mmk.chel.su...
> Hello
> MSSQL 2000 + SP3 on Windows 2000 AS + SP4
> I've moved my databases to new server.
> IO subsystem now TEN times faster!
> I have the system of two jobs performing defragmentation
> of my data. One of the jobs issues DBCC INDEXDEFRAG
> statements and the second one monitors state of transaction
> log and stops first job if log more than 70% full. SQLAgent
> can run jobs only with 1 minute intervals. So I can monitor
> log status only once in minute. Everything was all right at old
> server. Now on new hardware log overfills in couple of seconds
> and monitoring job can't catch the moment of overfilling and
> correctly stop the defragmentation.
> Two questions:
> 1. Should I increase Tlog size depending on hardware performance
> 2. Is there any way to monitor log state more ofter than once
> in a minute?
> Thanks.
> Serge Shakhov
>|||Hello
> You can have a job which uses an endless loop and WAITFOR with, say, 10
seconds.
That's a good idea!
But I'll try to avoid cycling process by executing every minute job wich
will last 58-59 seconds and perform checks say every 5 seconds...
Thank you Tibor.
Serge Shakhovsql

High fragmentation with 0 rows in the table

I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
rows into an empty table and then deletes them and keeps going in the loop
for about 6 hours. If the table is not truncated before the job starts again
the following night, the job performance degrades considerably. It turns out
that after the job is done running, there are hundreds of data pages
allocated for this table with high extent fragmentation.
My question is why these pages are not de-allocated when there are no rows
in the table to begin with? If I truncate the table and add 6 rows manually,
then only one data page is allocated to the table. The table has about 6
columns with 4 columns of data type smallint, one decimal and a couple of
char(5).
Hi,
There could be several reasons. Are you commiting your transactions after
each loop? Also, the delete statement is a logged transaction, that means
everytime your script deletes the data, it is written in the transaction log
that causes space (data pages) to be allocated. Not sure how your script is
coded, so can't really be too specific.
The truncate statement is NOT logged so when you issue it, it wipes out the
table clean. That is why you are seeing this behaviour.
HTH
DeeJay Puar
MCDBA
"Adam" wrote:

> I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> rows into an empty table and then deletes them and keeps going in the loop
> for about 6 hours. If the table is not truncated before the job starts again
> the following night, the job performance degrades considerably. It turns out
> that after the job is done running, there are hundreds of data pages
> allocated for this table with high extent fragmentation.
> My question is why these pages are not de-allocated when there are no rows
> in the table to begin with? If I truncate the table and add 6 rows manually,
> then only one data page is allocated to the table. The table has about 6
> columns with 4 columns of data type smallint, one decimal and a couple of
> char(5).
>
|||do you have any indexes on this table, specificaly a clustered one, or is
this a heap ?
and how is the "delete" done, with a 'delete tablename' ?
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:58830C32-198D-4DFF-9149-DBA76F8FB444@.microsoft.com...
>I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> rows into an empty table and then deletes them and keeps going in the loop
> for about 6 hours. If the table is not truncated before the job starts
> again
> the following night, the job performance degrades considerably. It turns
> out
> that after the job is done running, there are hundreds of data pages
> allocated for this table with high extent fragmentation.
> My question is why these pages are not de-allocated when there are no rows
> in the table to begin with? If I truncate the table and add 6 rows
> manually,
> then only one data page is allocated to the table. The table has about 6
> columns with 4 columns of data type smallint, one decimal and a couple of
> char(5).
>
|||On Tue, 13 Dec 2005 15:12:23 -0500, "David J. Cartwright"
<davidcartwright@.hotmail.com> wrote:
>do you have any indexes on this table, specificaly a clustered one, or is
>this a heap ?
>and how is the "delete" done, with a 'delete tablename' ?
What he said.
Would a simple "truncate table" be a quick fix?
(not that it's so hard to defrag anyway)
J.
|||I am not 100% if the app is doing a commit after each delete but even if that
was not the case, after the final commit at the end of the 6th hour,
shouldn't the empty pages get deallocated?
"DeeJay Puar" wrote:
[vbcol=seagreen]
> Hi,
> There could be several reasons. Are you commiting your transactions after
> each loop? Also, the delete statement is a logged transaction, that means
> everytime your script deletes the data, it is written in the transaction log
> that causes space (data pages) to be allocated. Not sure how your script is
> coded, so can't really be too specific.
> The truncate statement is NOT logged so when you issue it, it wipes out the
> table clean. That is why you are seeing this behaviour.
> HTH
> DeeJay Puar
> MCDBA
> "Adam" wrote:
|||At the moment, there are no indexes and yes, my next plan of attack is to put
a clustered index to see how that pans out. I don't know for sure if that
will solve the problem 100% because now, the delete would also have to do
more IOs to maintain the clustered index. Also, a table with the clustered
index will get fragmented over time.
The SQL is doing "delete table".
"David J. Cartwright" wrote:

> do you have any indexes on this table, specificaly a clustered one, or is
> this a heap ?
> and how is the "delete" done, with a 'delete tablename' ?
> "Adam" <Adam@.discussions.microsoft.com> wrote in message
> news:58830C32-198D-4DFF-9149-DBA76F8FB444@.microsoft.com...
>
>
|||The current fix in fact is to truncate the table before the batch starts,
however, if SQL Server is accumulating empty pages with 0 rows in the table
then the performance starts to degrade over time while the 6 hour job is
running. If I can figure out the reason and the solution to why SQL Server is
not deallocating empty pages, then I can stop it from taking the time for
doing table scan through these empty pages and can tweek many other nightly
jobs that I am sure are facing a similar situation.
thoughts?
"jxstern" wrote:

> On Tue, 13 Dec 2005 15:12:23 -0500, "David J. Cartwright"
> <davidcartwright@.hotmail.com> wrote:
> What he said.
> Would a simple "truncate table" be a quick fix?
> (not that it's so hard to defrag anyway)
> J.
>
>
|||On Thu, 15 Dec 2005 06:21:02 -0800, "Adam"
<Adam@.discussions.microsoft.com> wrote:
>At the moment, there are no indexes and yes, my next plan of attack is to put
>a clustered index to see how that pans out.
It should.
If you delete from a table without a clustered index, SQLServer leaves
the pages deallocated until ... The Ghost wakes up and reclaims them,
which it does when it feels like it, and then it takes as long as it
takes.
Fun, huh?
Add the clustered index, if for no other reason than this.
Google the newsgroup archives or the web for more about the ghost,
there's very little about it in MSDN or BOL.
Josh

High fragmentation with 0 rows in the table

I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
rows into an empty table and then deletes them and keeps going in the loop
for about 6 hours. If the table is not truncated before the job starts again
the following night, the job performance degrades considerably. It turns out
that after the job is done running, there are hundreds of data pages
allocated for this table with high extent fragmentation.
My question is why these pages are not de-allocated when there are no rows
in the table to begin with? If I truncate the table and add 6 rows manually,
then only one data page is allocated to the table. The table has about 6
columns with 4 columns of data type smallint, one decimal and a couple of
char(5).Hi,
There could be several reasons. Are you commiting your transactions after
each loop? Also, the delete statement is a logged transaction, that means
everytime your script deletes the data, it is written in the transaction log
that causes space (data pages) to be allocated. Not sure how your script is
coded, so can't really be too specific.
The truncate statement is NOT logged so when you issue it, it wipes out the
table clean. That is why you are seeing this behaviour.
HTH
DeeJay Puar
MCDBA
"Adam" wrote:
> I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> rows into an empty table and then deletes them and keeps going in the loop
> for about 6 hours. If the table is not truncated before the job starts again
> the following night, the job performance degrades considerably. It turns out
> that after the job is done running, there are hundreds of data pages
> allocated for this table with high extent fragmentation.
> My question is why these pages are not de-allocated when there are no rows
> in the table to begin with? If I truncate the table and add 6 rows manually,
> then only one data page is allocated to the table. The table has about 6
> columns with 4 columns of data type smallint, one decimal and a couple of
> char(5).
>|||do you have any indexes on this table, specificaly a clustered one, or is
this a heap ?
and how is the "delete" done, with a 'delete tablename' ?
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:58830C32-198D-4DFF-9149-DBA76F8FB444@.microsoft.com...
>I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> rows into an empty table and then deletes them and keeps going in the loop
> for about 6 hours. If the table is not truncated before the job starts
> again
> the following night, the job performance degrades considerably. It turns
> out
> that after the job is done running, there are hundreds of data pages
> allocated for this table with high extent fragmentation.
> My question is why these pages are not de-allocated when there are no rows
> in the table to begin with? If I truncate the table and add 6 rows
> manually,
> then only one data page is allocated to the table. The table has about 6
> columns with 4 columns of data type smallint, one decimal and a couple of
> char(5).
>|||On Tue, 13 Dec 2005 15:12:23 -0500, "David J. Cartwright"
<davidcartwright@.hotmail.com> wrote:
>do you have any indexes on this table, specificaly a clustered one, or is
>this a heap ?
>and how is the "delete" done, with a 'delete tablename' ?
What he said.
Would a simple "truncate table" be a quick fix?
(not that it's so hard to defrag anyway)
J.|||I am not 100% if the app is doing a commit after each delete but even if that
was not the case, after the final commit at the end of the 6th hour,
shouldn't the empty pages get deallocated?
"DeeJay Puar" wrote:
> Hi,
> There could be several reasons. Are you commiting your transactions after
> each loop? Also, the delete statement is a logged transaction, that means
> everytime your script deletes the data, it is written in the transaction log
> that causes space (data pages) to be allocated. Not sure how your script is
> coded, so can't really be too specific.
> The truncate statement is NOT logged so when you issue it, it wipes out the
> table clean. That is why you are seeing this behaviour.
> HTH
> DeeJay Puar
> MCDBA
> "Adam" wrote:
> > I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> > rows into an empty table and then deletes them and keeps going in the loop
> > for about 6 hours. If the table is not truncated before the job starts again
> > the following night, the job performance degrades considerably. It turns out
> > that after the job is done running, there are hundreds of data pages
> > allocated for this table with high extent fragmentation.
> >
> > My question is why these pages are not de-allocated when there are no rows
> > in the table to begin with? If I truncate the table and add 6 rows manually,
> > then only one data page is allocated to the table. The table has about 6
> > columns with 4 columns of data type smallint, one decimal and a couple of
> > char(5).
> >|||At the moment, there are no indexes and yes, my next plan of attack is to put
a clustered index to see how that pans out. I don't know for sure if that
will solve the problem 100% because now, the delete would also have to do
more IOs to maintain the clustered index. Also, a table with the clustered
index will get fragmented over time.
The SQL is doing "delete table".
"David J. Cartwright" wrote:
> do you have any indexes on this table, specificaly a clustered one, or is
> this a heap ?
> and how is the "delete" done, with a 'delete tablename' ?
> "Adam" <Adam@.discussions.microsoft.com> wrote in message
> news:58830C32-198D-4DFF-9149-DBA76F8FB444@.microsoft.com...
> >I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> > rows into an empty table and then deletes them and keeps going in the loop
> > for about 6 hours. If the table is not truncated before the job starts
> > again
> > the following night, the job performance degrades considerably. It turns
> > out
> > that after the job is done running, there are hundreds of data pages
> > allocated for this table with high extent fragmentation.
> >
> > My question is why these pages are not de-allocated when there are no rows
> > in the table to begin with? If I truncate the table and add 6 rows
> > manually,
> > then only one data page is allocated to the table. The table has about 6
> > columns with 4 columns of data type smallint, one decimal and a couple of
> > char(5).
> >
>
>|||The current fix in fact is to truncate the table before the batch starts,
however, if SQL Server is accumulating empty pages with 0 rows in the table
then the performance starts to degrade over time while the 6 hour job is
running. If I can figure out the reason and the solution to why SQL Server is
not deallocating empty pages, then I can stop it from taking the time for
doing table scan through these empty pages and can tweek many other nightly
jobs that I am sure are facing a similar situation.
thoughts?
"jxstern" wrote:
> On Tue, 13 Dec 2005 15:12:23 -0500, "David J. Cartwright"
> <davidcartwright@.hotmail.com> wrote:
> >do you have any indexes on this table, specificaly a clustered one, or is
> >this a heap ?
> >and how is the "delete" done, with a 'delete tablename' ?
> What he said.
> Would a simple "truncate table" be a quick fix?
> (not that it's so hard to defrag anyway)
> J.
>
>|||On Thu, 15 Dec 2005 06:21:02 -0800, "Adam"
<Adam@.discussions.microsoft.com> wrote:
>At the moment, there are no indexes and yes, my next plan of attack is to put
>a clustered index to see how that pans out.
It should.
If you delete from a table without a clustered index, SQLServer leaves
the pages deallocated until ... The Ghost wakes up and reclaims them,
which it does when it feels like it, and then it takes as long as it
takes.
Fun, huh?
Add the clustered index, if for no other reason than this.
Google the newsgroup archives or the web for more about the ghost,
there's very little about it in MSDN or BOL.
Josh

High fragmentation with 0 rows in the table

I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
rows into an empty table and then deletes them and keeps going in the loop
for about 6 hours. If the table is not truncated before the job starts again
the following night, the job performance degrades considerably. It turns out
that after the job is done running, there are hundreds of data pages
allocated for this table with high extent fragmentation.
My question is why these pages are not de-allocated when there are no rows
in the table to begin with? If I truncate the table and add 6 rows manually,
then only one data page is allocated to the table. The table has about 6
columns with 4 columns of data type smallint, one decimal and a couple of
char(5).Hi,
There could be several reasons. Are you commiting your transactions after
each loop? Also, the delete statement is a logged transaction, that means
everytime your script deletes the data, it is written in the transaction log
that causes space (data pages) to be allocated. Not sure how your script is
coded, so can't really be too specific.
The truncate statement is NOT logged so when you issue it, it wipes out the
table clean. That is why you are seeing this behaviour.
HTH
DeeJay Puar
MCDBA
"Adam" wrote:

> I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to
6
> rows into an empty table and then deletes them and keeps going in the loop
> for about 6 hours. If the table is not truncated before the job starts aga
in
> the following night, the job performance degrades considerably. It turns o
ut
> that after the job is done running, there are hundreds of data pages
> allocated for this table with high extent fragmentation.
> My question is why these pages are not de-allocated when there are no rows
> in the table to begin with? If I truncate the table and add 6 rows manuall
y,
> then only one data page is allocated to the table. The table has about 6
> columns with 4 columns of data type smallint, one decimal and a couple of
> char(5).
>|||do you have any indexes on this table, specificaly a clustered one, or is
this a heap ?
and how is the "delete" done, with a 'delete tablename' ?
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:58830C32-198D-4DFF-9149-DBA76F8FB444@.microsoft.com...
>I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> rows into an empty table and then deletes them and keeps going in the loop
> for about 6 hours. If the table is not truncated before the job starts
> again
> the following night, the job performance degrades considerably. It turns
> out
> that after the job is done running, there are hundreds of data pages
> allocated for this table with high extent fragmentation.
> My question is why these pages are not de-allocated when there are no rows
> in the table to begin with? If I truncate the table and add 6 rows
> manually,
> then only one data page is allocated to the table. The table has about 6
> columns with 4 columns of data type smallint, one decimal and a couple of
> char(5).
>|||On Tue, 13 Dec 2005 15:12:23 -0500, "David J. Cartwright"
<davidcartwright@.hotmail.com> wrote:
>do you have any indexes on this table, specificaly a clustered one, or is
>this a heap ?
>and how is the "delete" done, with a 'delete tablename' ?
What he said.
Would a simple "truncate table" be a quick fix?
(not that it's so hard to defrag anyway)
J.|||I am not 100% if the app is doing a commit after each delete but even if tha
t
was not the case, after the final commit at the end of the 6th hour,
shouldn't the empty pages get deallocated?
"DeeJay Puar" wrote:
[vbcol=seagreen]
> Hi,
> There could be several reasons. Are you commiting your transactions after
> each loop? Also, the delete statement is a logged transaction, that means
> everytime your script deletes the data, it is written in the transaction l
og
> that causes space (data pages) to be allocated. Not sure how your script i
s
> coded, so can't really be too specific.
> The truncate statement is NOT logged so when you issue it, it wipes out th
e
> table clean. That is why you are seeing this behaviour.
> HTH
> DeeJay Puar
> MCDBA
> "Adam" wrote:
>|||At the moment, there are no indexes and yes, my next plan of attack is to pu
t
a clustered index to see how that pans out. I don't know for sure if that
will solve the problem 100% because now, the delete would also have to do
more IOs to maintain the clustered index. Also, a table with the clustered
index will get fragmented over time.
The SQL is doing "delete table".
"David J. Cartwright" wrote:

> do you have any indexes on this table, specificaly a clustered one, or is
> this a heap ?
> and how is the "delete" done, with a 'delete tablename' ?
> "Adam" <Adam@.discussions.microsoft.com> wrote in message
> news:58830C32-198D-4DFF-9149-DBA76F8FB444@.microsoft.com...
>
>|||The current fix in fact is to truncate the table before the batch starts,
however, if SQL Server is accumulating empty pages with 0 rows in the table
then the performance starts to degrade over time while the 6 hour job is
running. If I can figure out the reason and the solution to why SQL Server i
s
not deallocating empty pages, then I can stop it from taking the time for
doing table scan through these empty pages and can tweek many other nightly
jobs that I am sure are facing a similar situation.
thoughts?
"jxstern" wrote:

> On Tue, 13 Dec 2005 15:12:23 -0500, "David J. Cartwright"
> <davidcartwright@.hotmail.com> wrote:
> What he said.
> Would a simple "truncate table" be a quick fix?
> (not that it's so hard to defrag anyway)
> J.
>
>|||On Thu, 15 Dec 2005 06:21:02 -0800, "Adam"
<Adam@.discussions.microsoft.com> wrote:
>At the moment, there are no indexes and yes, my next plan of attack is to p
ut
>a clustered index to see how that pans out.
It should.
If you delete from a table without a clustered index, SQLServer leaves
the pages deallocated until ... The Ghost wakes up and reclaims them,
which it does when it feels like it, and then it takes as long as it
takes.
Fun, huh?
Add the clustered index, if for no other reason than this.
Google the newsgroup archives or the web for more about the ghost,
there's very little about it in MSDN or BOL.
Josh

Hiding instances on the n/w (Port 1434)

Hi,
I tried to hide an instance of MSSQL2000 SP3 on the n/w by first removing
all the enabled protocols using the server network utility which worked fine,
i then renabled the TCP/IP protocol and selected hide server which seemed to
switch the port number to 2433, which i understand has an implication for
clients regarding connection strings including port numbers, aliasing etc.
I then switched the port number back to 1433 and de-selected the hide server
option but now i can connect to the machine using query analyzer when i know
the machine name but if i try to hit the button which lists all servers on
the n/w the server does not appear. I believe this list is populated based on
a n/w port scan which builds the list based on machines that respond to a
request on UDP port 1434.
If anyone knows why the server will not appear i would appreciate a pointer,
i have also attempted to find help about blocking on port 1434 but cannot
find help on how to do so, keep getting instructions on blocking at the
firewall, i just want to understand how this works.
Thanks in advance
Pat
Pmcg,
You cannot prevent SQL Server listening on port 1434. If you want to prevent
clients accessing this port, you have no choice but to use a firewall.
SQL Server's enumeration mechanism can only list servers that are registered
in Enterprise Manager, and ones that are on your subnet - the broadcast does
not work across routers or firewalls. Perhaps this is the problem.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Pmcg" wrote:

> Hi,
> I tried to hide an instance of MSSQL2000 SP3 on the n/w by first removing
> all the enabled protocols using the server network utility which worked fine,
> i then renabled the TCP/IP protocol and selected hide server which seemed to
> switch the port number to 2433, which i understand has an implication for
> clients regarding connection strings including port numbers, aliasing etc.
> I then switched the port number back to 1433 and de-selected the hide server
> option but now i can connect to the machine using query analyzer when i know
> the machine name but if i try to hit the button which lists all servers on
> the n/w the server does not appear. I believe this list is populated based on
> a n/w port scan which builds the list based on machines that respond to a
> request on UDP port 1434.
> If anyone knows why the server will not appear i would appreciate a pointer,
> i have also attempted to find help about blocking on port 1434 but cannot
> find help on how to do so, keep getting instructions on blocking at the
> firewall, i just want to understand how this works.
>
> Thanks in advance
> Pat

Hiding instances on the n/w (Port 1434)

Hi,
I tried to hide an instance of MSSQL2000 SP3 on the n/w by first removing
all the enabled protocols using the server network utility which worked fine
,
i then renabled the TCP/IP protocol and selected hide server which seemed to
switch the port number to 2433, which i understand has an implication for
clients regarding connection strings including port numbers, aliasing etc.
I then switched the port number back to 1433 and de-selected the hide server
option but now i can connect to the machine using query analyzer when i know
the machine name but if i try to hit the button which lists all servers on
the n/w the server does not appear. I believe this list is populated based o
n
a n/w port scan which builds the list based on machines that respond to a
request on UDP port 1434.
If anyone knows why the server will not appear i would appreciate a pointer,
i have also attempted to find help about blocking on port 1434 but cannot
find help on how to do so, keep getting instructions on blocking at the
firewall, i just want to understand how this works.
Thanks in advance
PatPmcg,
You cannot prevent SQL Server listening on port 1434. If you want to prevent
clients accessing this port, you have no choice but to use a firewall.
SQL Server's enumeration mechanism can only list servers that are registered
in Enterprise Manager, and ones that are on your subnet - the broadcast does
not work across routers or firewalls. Perhaps this is the problem.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Pmcg" wrote:

> Hi,
> I tried to hide an instance of MSSQL2000 SP3 on the n/w by first removing
> all the enabled protocols using the server network utility which worked fi
ne,
> i then renabled the TCP/IP protocol and selected hide server which seemed
to
> switch the port number to 2433, which i understand has an implication for
> clients regarding connection strings including port numbers, aliasing etc.
> I then switched the port number back to 1433 and de-selected the hide serv
er
> option but now i can connect to the machine using query analyzer when i kn
ow
> the machine name but if i try to hit the button which lists all servers on
> the n/w the server does not appear. I believe this list is populated based
on
> a n/w port scan which builds the list based on machines that respond to a
> request on UDP port 1434.
> If anyone knows why the server will not appear i would appreciate a pointe
r,
> i have also attempted to find help about blocking on port 1434 but cannot
> find help on how to do so, keep getting instructions on blocking at the
> firewall, i just want to understand how this works.
>
> Thanks in advance
> Pat

Hiding instances on the n/w (Port 1434)

Hi,
I tried to hide an instance of MSSQL2000 SP3 on the n/w by first removing
all the enabled protocols using the server network utility which worked fine,
i then renabled the TCP/IP protocol and selected hide server which seemed to
switch the port number to 2433, which i understand has an implication for
clients regarding connection strings including port numbers, aliasing etc.
I then switched the port number back to 1433 and de-selected the hide server
option but now i can connect to the machine using query analyzer when i know
the machine name but if i try to hit the button which lists all servers on
the n/w the server does not appear. I believe this list is populated based on
a n/w port scan which builds the list based on machines that respond to a
request on UDP port 1434.
If anyone knows why the server will not appear i would appreciate a pointer,
i have also attempted to find help about blocking on port 1434 but cannot
find help on how to do so, keep getting instructions on blocking at the
firewall, i just want to understand how this works.
Thanks in advance
PatPmcg,
You cannot prevent SQL Server listening on port 1434. If you want to prevent
clients accessing this port, you have no choice but to use a firewall.
SQL Server's enumeration mechanism can only list servers that are registered
in Enterprise Manager, and ones that are on your subnet - the broadcast does
not work across routers or firewalls. Perhaps this is the problem.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Pmcg" wrote:
> Hi,
> I tried to hide an instance of MSSQL2000 SP3 on the n/w by first removing
> all the enabled protocols using the server network utility which worked fine,
> i then renabled the TCP/IP protocol and selected hide server which seemed to
> switch the port number to 2433, which i understand has an implication for
> clients regarding connection strings including port numbers, aliasing etc.
> I then switched the port number back to 1433 and de-selected the hide server
> option but now i can connect to the machine using query analyzer when i know
> the machine name but if i try to hit the button which lists all servers on
> the n/w the server does not appear. I believe this list is populated based on
> a n/w port scan which builds the list based on machines that respond to a
> request on UDP port 1434.
> If anyone knows why the server will not appear i would appreciate a pointer,
> i have also attempted to find help about blocking on port 1434 but cannot
> find help on how to do so, keep getting instructions on blocking at the
> firewall, i just want to understand how this works.
>
> Thanks in advance
> Pat