Showing posts with label instances. Show all posts
Showing posts with label instances. Show all posts
Thursday, March 29, 2012
Hoe To Connect two different instances of SQL SERVER
How to connect two differect instances of SQL SERVER on LEN or WAN for Data Migration.Refer to your thread http://www.dbforums.com/t995930.html
Friday, March 23, 2012
High paging but plenty of memory available
I have a Win2003/SQL2000 SP4 development server with 4GB of memory. It has
been built with two instances of SQL Server and is also running Virtual
Server. The two SQL instances have been configured to take as much memory as
they want.
I've restored a database from our live system which is also Win2003/SQL2000
SP4 but only has 3GB of memor. The problem is that a query which takes less
than 5 mins on the live system takes over 1 hour on the dev box.
I've checked various counters while this query is running and the one
counter which stands out is "Memory: Page Reads/Sec" which is consistently
above 100 on the dev box but remains at 0 on the live server. However, on the
dev server, the "Memory: Available MBytes" = 2842 and the
"MSSQL$INST01:Target SQL Memory (KB)" and "MSSQL$INST01:Total Server memory
(KB)" are both 2724520 which would make me think there is plenty of memory
and no need for excesive paging.
On the live server, "Memory: Available MBytes" = 104 and the
"MSSQL$INST01:Target SQL Memory (KB)" =874128 while "MSSQL$INST01:Total
Server memory (KB)" = 865384.
DBA72 wrote:
> I have a Win2003/SQL2000 SP4 development server with 4GB of memory.
> It has been built with two instances of SQL Server and is also
> running Virtual Server. The two SQL instances have been configured to
> take as much memory as they want.
> I've restored a database from our live system which is also
> Win2003/SQL2000 SP4 but only has 3GB of memor. The problem is that a
> query which takes less than 5 mins on the live system takes over 1
> hour on the dev box.
> I've checked various counters while this query is running and the one
> counter which stands out is "Memory: Page Reads/Sec" which is
> consistently above 100 on the dev box but remains at 0 on the live
> server. However, on the dev server, the "Memory: Available MBytes" =
> 2842 and the "MSSQL$INST01:Target SQL Memory (KB)" and
> "MSSQL$INST01:Total Server memory (KB)" are both 2724520 which would
> make me think there is plenty of memory and no need for excesive
> paging.
> On the live server, "Memory: Available MBytes" = 104 and the
> "MSSQL$INST01:Target SQL Memory (KB)" =874128 while
> "MSSQL$INST01:Total Server memory (KB)" = 865384.
When running multiple instances it is recommended to specify a maximum
amount of memory for each instance so they don't consume too much. You
didn't mention what edition of SQL Server and the OS you are running on
the dev box. Assuming standard edition, you have 4GB for the OS, two
instances, and virtual server. I don't know how much memory Virtual
Server is using. Once you figure out how much it needs and how much the
OS needs (1GB would be a good start), you can split the remainder
between the two SQL instances.
It's very possible you have a lot of contention for memory on the box.
David Gugick
Quest Software
www.imceda.com
www.quest.com
been built with two instances of SQL Server and is also running Virtual
Server. The two SQL instances have been configured to take as much memory as
they want.
I've restored a database from our live system which is also Win2003/SQL2000
SP4 but only has 3GB of memor. The problem is that a query which takes less
than 5 mins on the live system takes over 1 hour on the dev box.
I've checked various counters while this query is running and the one
counter which stands out is "Memory: Page Reads/Sec" which is consistently
above 100 on the dev box but remains at 0 on the live server. However, on the
dev server, the "Memory: Available MBytes" = 2842 and the
"MSSQL$INST01:Target SQL Memory (KB)" and "MSSQL$INST01:Total Server memory
(KB)" are both 2724520 which would make me think there is plenty of memory
and no need for excesive paging.
On the live server, "Memory: Available MBytes" = 104 and the
"MSSQL$INST01:Target SQL Memory (KB)" =874128 while "MSSQL$INST01:Total
Server memory (KB)" = 865384.
DBA72 wrote:
> I have a Win2003/SQL2000 SP4 development server with 4GB of memory.
> It has been built with two instances of SQL Server and is also
> running Virtual Server. The two SQL instances have been configured to
> take as much memory as they want.
> I've restored a database from our live system which is also
> Win2003/SQL2000 SP4 but only has 3GB of memor. The problem is that a
> query which takes less than 5 mins on the live system takes over 1
> hour on the dev box.
> I've checked various counters while this query is running and the one
> counter which stands out is "Memory: Page Reads/Sec" which is
> consistently above 100 on the dev box but remains at 0 on the live
> server. However, on the dev server, the "Memory: Available MBytes" =
> 2842 and the "MSSQL$INST01:Target SQL Memory (KB)" and
> "MSSQL$INST01:Total Server memory (KB)" are both 2724520 which would
> make me think there is plenty of memory and no need for excesive
> paging.
> On the live server, "Memory: Available MBytes" = 104 and the
> "MSSQL$INST01:Target SQL Memory (KB)" =874128 while
> "MSSQL$INST01:Total Server memory (KB)" = 865384.
When running multiple instances it is recommended to specify a maximum
amount of memory for each instance so they don't consume too much. You
didn't mention what edition of SQL Server and the OS you are running on
the dev box. Assuming standard edition, you have 4GB for the OS, two
instances, and virtual server. I don't know how much memory Virtual
Server is using. Once you figure out how much it needs and how much the
OS needs (1GB would be a good start), you can split the remainder
between the two SQL instances.
It's very possible you have a lot of contention for memory on the box.
David Gugick
Quest Software
www.imceda.com
www.quest.com
High paging but plenty of memory available
I have a Win2003/SQL2000 SP4 development server with 4GB of memory. It has
been built with two instances of SQL Server and is also running Virtual
Server. The two SQL instances have been configured to take as much memory as
they want.
I've restored a database from our live system which is also Win2003/SQL2000
SP4 but only has 3GB of memor. The problem is that a query which takes less
than 5 mins on the live system takes over 1 hour on the dev box.
I've checked various counters while this query is running and the one
counter which stands out is "Memory: Page Reads/Sec" which is consistently
above 100 on the dev box but remains at 0 on the live server. However, on the
dev server, the "Memory: Available MBytes" = 2842 and the
"MSSQL$INST01:Target SQL Memory (KB)" and "MSSQL$INST01:Total Server memory
(KB)" are both 2724520 which would make me think there is plenty of memory
and no need for excesive paging.
On the live server, "Memory: Available MBytes" = 104 and the
"MSSQL$INST01:Target SQL Memory (KB)" =874128 while "MSSQL$INST01:Total
Server memory (KB)" = 865384.DBA72 wrote:
> I have a Win2003/SQL2000 SP4 development server with 4GB of memory.
> It has been built with two instances of SQL Server and is also
> running Virtual Server. The two SQL instances have been configured to
> take as much memory as they want.
> I've restored a database from our live system which is also
> Win2003/SQL2000 SP4 but only has 3GB of memor. The problem is that a
> query which takes less than 5 mins on the live system takes over 1
> hour on the dev box.
> I've checked various counters while this query is running and the one
> counter which stands out is "Memory: Page Reads/Sec" which is
> consistently above 100 on the dev box but remains at 0 on the live
> server. However, on the dev server, the "Memory: Available MBytes" => 2842 and the "MSSQL$INST01:Target SQL Memory (KB)" and
> "MSSQL$INST01:Total Server memory (KB)" are both 2724520 which would
> make me think there is plenty of memory and no need for excesive
> paging.
> On the live server, "Memory: Available MBytes" = 104 and the
> "MSSQL$INST01:Target SQL Memory (KB)" =874128 while
> "MSSQL$INST01:Total Server memory (KB)" = 865384.
When running multiple instances it is recommended to specify a maximum
amount of memory for each instance so they don't consume too much. You
didn't mention what edition of SQL Server and the OS you are running on
the dev box. Assuming standard edition, you have 4GB for the OS, two
instances, and virtual server. I don't know how much memory Virtual
Server is using. Once you figure out how much it needs and how much the
OS needs (1GB would be a good start), you can split the remainder
between the two SQL instances.
It's very possible you have a lot of contention for memory on the box.
David Gugick
Quest Software
www.imceda.com
www.quest.com
been built with two instances of SQL Server and is also running Virtual
Server. The two SQL instances have been configured to take as much memory as
they want.
I've restored a database from our live system which is also Win2003/SQL2000
SP4 but only has 3GB of memor. The problem is that a query which takes less
than 5 mins on the live system takes over 1 hour on the dev box.
I've checked various counters while this query is running and the one
counter which stands out is "Memory: Page Reads/Sec" which is consistently
above 100 on the dev box but remains at 0 on the live server. However, on the
dev server, the "Memory: Available MBytes" = 2842 and the
"MSSQL$INST01:Target SQL Memory (KB)" and "MSSQL$INST01:Total Server memory
(KB)" are both 2724520 which would make me think there is plenty of memory
and no need for excesive paging.
On the live server, "Memory: Available MBytes" = 104 and the
"MSSQL$INST01:Target SQL Memory (KB)" =874128 while "MSSQL$INST01:Total
Server memory (KB)" = 865384.DBA72 wrote:
> I have a Win2003/SQL2000 SP4 development server with 4GB of memory.
> It has been built with two instances of SQL Server and is also
> running Virtual Server. The two SQL instances have been configured to
> take as much memory as they want.
> I've restored a database from our live system which is also
> Win2003/SQL2000 SP4 but only has 3GB of memor. The problem is that a
> query which takes less than 5 mins on the live system takes over 1
> hour on the dev box.
> I've checked various counters while this query is running and the one
> counter which stands out is "Memory: Page Reads/Sec" which is
> consistently above 100 on the dev box but remains at 0 on the live
> server. However, on the dev server, the "Memory: Available MBytes" => 2842 and the "MSSQL$INST01:Target SQL Memory (KB)" and
> "MSSQL$INST01:Total Server memory (KB)" are both 2724520 which would
> make me think there is plenty of memory and no need for excesive
> paging.
> On the live server, "Memory: Available MBytes" = 104 and the
> "MSSQL$INST01:Target SQL Memory (KB)" =874128 while
> "MSSQL$INST01:Total Server memory (KB)" = 865384.
When running multiple instances it is recommended to specify a maximum
amount of memory for each instance so they don't consume too much. You
didn't mention what edition of SQL Server and the OS you are running on
the dev box. Assuming standard edition, you have 4GB for the OS, two
instances, and virtual server. I don't know how much memory Virtual
Server is using. Once you figure out how much it needs and how much the
OS needs (1GB would be a good start), you can split the remainder
between the two SQL instances.
It's very possible you have a lot of contention for memory on the box.
David Gugick
Quest Software
www.imceda.com
www.quest.com
High paging but plenty of memory available
I have a Win2003/SQL2000 SP4 development server with 4GB of memory. It has
been built with two instances of SQL Server and is also running Virtual
Server. The two SQL instances have been configured to take as much memory as
they want.
I've restored a database from our live system which is also Win2003/SQL2000
SP4 but only has 3GB of memor. The problem is that a query which takes less
than 5 mins on the live system takes over 1 hour on the dev box.
I've checked various counters while this query is running and the one
counter which stands out is "Memory: Page Reads/Sec" which is consistently
above 100 on the dev box but remains at 0 on the live server. However, on th
e
dev server, the "Memory: Available MBytes" = 2842 and the
"MSSQL$INST01:Target SQL Memory (KB)" and "MSSQL$INST01:Total Server memory
(KB)" are both 2724520 which would make me think there is plenty of memory
and no need for excesive paging.
On the live server, "Memory: Available MBytes" = 104 and the
"MSSQL$INST01:Target SQL Memory (KB)" =874128 while "MSSQL$INST01:Total
Server memory (KB)" = 865384.DBA72 wrote:
> I have a Win2003/SQL2000 SP4 development server with 4GB of memory.
> It has been built with two instances of SQL Server and is also
> running Virtual Server. The two SQL instances have been configured to
> take as much memory as they want.
> I've restored a database from our live system which is also
> Win2003/SQL2000 SP4 but only has 3GB of memor. The problem is that a
> query which takes less than 5 mins on the live system takes over 1
> hour on the dev box.
> I've checked various counters while this query is running and the one
> counter which stands out is "Memory: Page Reads/Sec" which is
> consistently above 100 on the dev box but remains at 0 on the live
> server. However, on the dev server, the "Memory: Available MBytes" =
> 2842 and the "MSSQL$INST01:Target SQL Memory (KB)" and
> "MSSQL$INST01:Total Server memory (KB)" are both 2724520 which would
> make me think there is plenty of memory and no need for excesive
> paging.
> On the live server, "Memory: Available MBytes" = 104 and the
> "MSSQL$INST01:Target SQL Memory (KB)" =874128 while
> "MSSQL$INST01:Total Server memory (KB)" = 865384.
When running multiple instances it is recommended to specify a maximum
amount of memory for each instance so they don't consume too much. You
didn't mention what edition of SQL Server and the OS you are running on
the dev box. Assuming standard edition, you have 4GB for the OS, two
instances, and virtual server. I don't know how much memory Virtual
Server is using. Once you figure out how much it needs and how much the
OS needs (1GB would be a good start), you can split the remainder
between the two SQL instances.
It's very possible you have a lot of contention for memory on the box.
David Gugick
Quest Software
www.imceda.com
www.quest.com
been built with two instances of SQL Server and is also running Virtual
Server. The two SQL instances have been configured to take as much memory as
they want.
I've restored a database from our live system which is also Win2003/SQL2000
SP4 but only has 3GB of memor. The problem is that a query which takes less
than 5 mins on the live system takes over 1 hour on the dev box.
I've checked various counters while this query is running and the one
counter which stands out is "Memory: Page Reads/Sec" which is consistently
above 100 on the dev box but remains at 0 on the live server. However, on th
e
dev server, the "Memory: Available MBytes" = 2842 and the
"MSSQL$INST01:Target SQL Memory (KB)" and "MSSQL$INST01:Total Server memory
(KB)" are both 2724520 which would make me think there is plenty of memory
and no need for excesive paging.
On the live server, "Memory: Available MBytes" = 104 and the
"MSSQL$INST01:Target SQL Memory (KB)" =874128 while "MSSQL$INST01:Total
Server memory (KB)" = 865384.DBA72 wrote:
> I have a Win2003/SQL2000 SP4 development server with 4GB of memory.
> It has been built with two instances of SQL Server and is also
> running Virtual Server. The two SQL instances have been configured to
> take as much memory as they want.
> I've restored a database from our live system which is also
> Win2003/SQL2000 SP4 but only has 3GB of memor. The problem is that a
> query which takes less than 5 mins on the live system takes over 1
> hour on the dev box.
> I've checked various counters while this query is running and the one
> counter which stands out is "Memory: Page Reads/Sec" which is
> consistently above 100 on the dev box but remains at 0 on the live
> server. However, on the dev server, the "Memory: Available MBytes" =
> 2842 and the "MSSQL$INST01:Target SQL Memory (KB)" and
> "MSSQL$INST01:Total Server memory (KB)" are both 2724520 which would
> make me think there is plenty of memory and no need for excesive
> paging.
> On the live server, "Memory: Available MBytes" = 104 and the
> "MSSQL$INST01:Target SQL Memory (KB)" =874128 while
> "MSSQL$INST01:Total Server memory (KB)" = 865384.
When running multiple instances it is recommended to specify a maximum
amount of memory for each instance so they don't consume too much. You
didn't mention what edition of SQL Server and the OS you are running on
the dev box. Assuming standard edition, you have 4GB for the OS, two
instances, and virtual server. I don't know how much memory Virtual
Server is using. Once you figure out how much it needs and how much the
OS needs (1GB would be a good start), you can split the remainder
between the two SQL instances.
It's very possible you have a lot of contention for memory on the box.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Friday, February 24, 2012
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
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
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
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
Subscribe to:
Posts (Atom)