New to clustering and wanted to know if anyone had any hardware suggestions
with regard to the dedicated switch for the heartbeat network.
Initially this will be a 2 node Active/Active cluster hosting SQL Server
connected to a SAN. I do expect to add at least another set of clustered
hosts or two before year's end.
We typically use all Cisco equipment.
Suggestions?
"john d" <johnd@.discussions.microsoft.com> wrote in message
news:D9FCFA8C-13FB-4A7C-9E88-9573BEF9C047@.microsoft.com...
> New to clustering and wanted to know if anyone had any hardware
> suggestions
> with regard to the dedicated switch for the heartbeat network.
Anything will work for you. I, personally, prefer dumb hubs. That way Bucky
can misconfigure it and hose up my heartbeat network.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp classes are:
Denver starting Feb 12th
NYC starting Feb 19th
|||I agree with Russ. The bandwith requirements for the heartbeat are really
low. I wish I could get some old co-ax NICS and just run a single cable
instead of an active device.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Russ Kaufmann [MVP]" <russ@.clusterhelp.com> wrote in message
news:erF4iczOHHA.4280@.TK2MSFTNGP02.phx.gbl...
> "john d" <johnd@.discussions.microsoft.com> wrote in message
> news:D9FCFA8C-13FB-4A7C-9E88-9573BEF9C047@.microsoft.com...
> Anything will work for you. I, personally, prefer dumb hubs. That way
> Bucky can misconfigure it and hose up my heartbeat network.
>
> --
> Russ Kaufmann
> MVP - Windows Server - Clustering
> ClusterHelp.com, a Microsoft Certified Gold Partner
> Web http://www.clusterhelp.com
> Blog http://msmvps.com/clusterhelp
> The next ClusterHelp classes are:
> Denver starting Feb 12th
> NYC starting Feb 19th
>
|||Geoff/Russ - Thanks for your input. Glad I didn't already go out and buy a
switch.
"Geoff N. Hiten" wrote:
> I agree with Russ. The bandwith requirements for the heartbeat are really
> low. I wish I could get some old co-ax NICS and just run a single cable
> instead of an active device.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Russ Kaufmann [MVP]" <russ@.clusterhelp.com> wrote in message
> news:erF4iczOHHA.4280@.TK2MSFTNGP02.phx.gbl...
>
|||For a 2-node system, we typically use just a network cross-over cable. If
you expand this cluster for additional nodes, the heartbeat uses multi-cast
to communicate (Win2K3); so, you will definitely want to isolate this
network from the others, either by constructing a physical interconnect
between the servers or through a larger corporate switch interconnect and
the use of VLANs.
Regardless, unless you also duplicate the hardware, this network will also
represent a Single Point of Failure. If you are unfamiliar with this term,
you should learn it. It is the single most important concept with regards
to Highly Available systems.
In this case, you must provide multiple network paths between the servers.
In addition to the dedicated heartbeat network, we typically allow the
public network to also transport cluster heartbeat communications. In
Cluster Administrator, on the cluster object, you can specify the network
priority for cluster communications, but also include the secondary network
as a fail-safe.
Sincerely,
Anthony Thomas
"john d" <johnd@.discussions.microsoft.com> wrote in message
news:DC09A8BC-4126-4002-B526-401B90D2A1DB@.microsoft.com...
> Geoff/Russ - Thanks for your input. Glad I didn't already go out and buy
a[vbcol=seagreen]
> switch.
>
> "Geoff N. Hiten" wrote:
really[vbcol=seagreen]
Showing posts with label network. Show all posts
Showing posts with label network. Show all posts
Thursday, March 29, 2012
Sunday, March 25, 2012
Cluster name with multiple IP addresses?
Can multiple IP addresses be assigned to a cluster and/or virtual name?
I have a 2-node Active/Passive cluster on a switched (not routed) network.
Each node has 3 NICs, each on its own subnet;
a) public/client access - 192.168.10.0
b) private/cluster traffic - 10.0.0.0
c) data backup network - 192.168.20.0
The cluster name "SQLCLST" and the virtual machine name "SQLSVR" have been
defined with IP addresses on the public/client network.
My backup software is cluster aware but because the traffic is separated via
switched network, I need the cluster and/or virtual machine name to respond
to IP addresses on two different subnets. Is this possible? Did this make
sense? ;)
TIA
It is not an uncommon request, nor is it too difficult. Once you have the
NICs and networks defined in the cluser configuration, you run the SQL setup
wizard to add the networks to SQL Server. To use them effectively, look
into connection-specific suffixes. That way you can force traffic to a
particular network.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Structured Chaos" <jeffery_tyree@.yahoo.com> wrote in message
news:%23Y%23s8Mv0HHA.5380@.TK2MSFTNGP04.phx.gbl...
> Can multiple IP addresses be assigned to a cluster and/or virtual name?
> I have a 2-node Active/Passive cluster on a switched (not routed) network.
> Each node has 3 NICs, each on its own subnet;
> a) public/client access - 192.168.10.0
> b) private/cluster traffic - 10.0.0.0
> c) data backup network - 192.168.20.0
> The cluster name "SQLCLST" and the virtual machine name "SQLSVR" have been
> defined with IP addresses on the public/client network.
> My backup software is cluster aware but because the traffic is separated
> via switched network, I need the cluster and/or virtual machine name to
> respond to IP addresses on two different subnets. Is this possible? Did
> this make sense? ;)
> TIA
>
|||Thanks for the quick info. Apparently it is a little more difficult than
that, heheh
I did not see any dialog/screen that allowed for the addition of new
network - only to modify node membership.
I have a ticket opened with MS. They have escalated it stating that they
don't even know if SQL will support this configuration and are attempting to
duplicate it in their labs. The MS SQL Data Engine Team manager stated they
may even have to look at the code...
Should you happen to have any detailed steps on making this work, I'd
appreciate it and apparently MS would too! LOL
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OQYwDPx0HHA.1184@.TK2MSFTNGP04.phx.gbl...
> It is not an uncommon request, nor is it too difficult. Once you have the
> NICs and networks defined in the cluser configuration, you run the SQL
> setup wizard to add the networks to SQL Server. To use them effectively,
> look into connection-specific suffixes. That way you can force traffic to
> a particular network.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Structured Chaos" <jeffery_tyree@.yahoo.com> wrote in message
> news:%23Y%23s8Mv0HHA.5380@.TK2MSFTNGP04.phx.gbl...
>
|||OK, you are not trying to stack multiple IP addresses on a single NIC, you
are using a unique IP address for each NIC. That isn't a problem.
The problem comes from having the same DNS alias (network name) respond on
two different subnets. That is a networking issue, not a clustering issue.
I would use connection-specific domain suffixes to differentiate the
networks. Also, I would use DNS SRV records to abstract the names. I.E.
MySQLCluster points to the IP and port of the normal public NIC.
MySQLClusterBackup points to the IP and port on the backup LAN.
I have used this setup in the past and it works just fine.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Structured Chaos" <jeffery_tyree@.yahoo.com> wrote in message
news:Ooh45iT2HHA.2752@.TK2MSFTNGP06.phx.gbl...
> Thanks for the quick info. Apparently it is a little more difficult than
> that, heheh
> I did not see any dialog/screen that allowed for the addition of new
> network - only to modify node membership.
> I have a ticket opened with MS. They have escalated it stating that they
> don't even know if SQL will support this configuration and are attempting
> to duplicate it in their labs. The MS SQL Data Engine Team manager stated
> they may even have to look at the code...
> Should you happen to have any detailed steps on making this work, I'd
> appreciate it and apparently MS would too! LOL
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:OQYwDPx0HHA.1184@.TK2MSFTNGP04.phx.gbl...
>
I have a 2-node Active/Passive cluster on a switched (not routed) network.
Each node has 3 NICs, each on its own subnet;
a) public/client access - 192.168.10.0
b) private/cluster traffic - 10.0.0.0
c) data backup network - 192.168.20.0
The cluster name "SQLCLST" and the virtual machine name "SQLSVR" have been
defined with IP addresses on the public/client network.
My backup software is cluster aware but because the traffic is separated via
switched network, I need the cluster and/or virtual machine name to respond
to IP addresses on two different subnets. Is this possible? Did this make
sense? ;)
TIA
It is not an uncommon request, nor is it too difficult. Once you have the
NICs and networks defined in the cluser configuration, you run the SQL setup
wizard to add the networks to SQL Server. To use them effectively, look
into connection-specific suffixes. That way you can force traffic to a
particular network.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Structured Chaos" <jeffery_tyree@.yahoo.com> wrote in message
news:%23Y%23s8Mv0HHA.5380@.TK2MSFTNGP04.phx.gbl...
> Can multiple IP addresses be assigned to a cluster and/or virtual name?
> I have a 2-node Active/Passive cluster on a switched (not routed) network.
> Each node has 3 NICs, each on its own subnet;
> a) public/client access - 192.168.10.0
> b) private/cluster traffic - 10.0.0.0
> c) data backup network - 192.168.20.0
> The cluster name "SQLCLST" and the virtual machine name "SQLSVR" have been
> defined with IP addresses on the public/client network.
> My backup software is cluster aware but because the traffic is separated
> via switched network, I need the cluster and/or virtual machine name to
> respond to IP addresses on two different subnets. Is this possible? Did
> this make sense? ;)
> TIA
>
|||Thanks for the quick info. Apparently it is a little more difficult than
that, heheh
I did not see any dialog/screen that allowed for the addition of new
network - only to modify node membership.
I have a ticket opened with MS. They have escalated it stating that they
don't even know if SQL will support this configuration and are attempting to
duplicate it in their labs. The MS SQL Data Engine Team manager stated they
may even have to look at the code...
Should you happen to have any detailed steps on making this work, I'd
appreciate it and apparently MS would too! LOL
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OQYwDPx0HHA.1184@.TK2MSFTNGP04.phx.gbl...
> It is not an uncommon request, nor is it too difficult. Once you have the
> NICs and networks defined in the cluser configuration, you run the SQL
> setup wizard to add the networks to SQL Server. To use them effectively,
> look into connection-specific suffixes. That way you can force traffic to
> a particular network.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Structured Chaos" <jeffery_tyree@.yahoo.com> wrote in message
> news:%23Y%23s8Mv0HHA.5380@.TK2MSFTNGP04.phx.gbl...
>
|||OK, you are not trying to stack multiple IP addresses on a single NIC, you
are using a unique IP address for each NIC. That isn't a problem.
The problem comes from having the same DNS alias (network name) respond on
two different subnets. That is a networking issue, not a clustering issue.
I would use connection-specific domain suffixes to differentiate the
networks. Also, I would use DNS SRV records to abstract the names. I.E.
MySQLCluster points to the IP and port of the normal public NIC.
MySQLClusterBackup points to the IP and port on the backup LAN.
I have used this setup in the past and it works just fine.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Structured Chaos" <jeffery_tyree@.yahoo.com> wrote in message
news:Ooh45iT2HHA.2752@.TK2MSFTNGP06.phx.gbl...
> Thanks for the quick info. Apparently it is a little more difficult than
> that, heheh
> I did not see any dialog/screen that allowed for the addition of new
> network - only to modify node membership.
> I have a ticket opened with MS. They have escalated it stating that they
> don't even know if SQL will support this configuration and are attempting
> to duplicate it in their labs. The MS SQL Data Engine Team manager stated
> they may even have to look at the code...
> Should you happen to have any detailed steps on making this work, I'd
> appreciate it and apparently MS would too! LOL
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:OQYwDPx0HHA.1184@.TK2MSFTNGP04.phx.gbl...
>
Cluster IP address change and delay
Hi all,
I assume that you have followed http://support.microsoft.com/kb/244980 or
http://msdn2.microsoft.com/en-us/library/ms190460.aspx? Which version of SQL
Server is this?
John, thanks for your response. Yes I did follow MS knowledge base
articles. I have MS SQL server 2005 x64.
I did the ping commands. Well, I have tested the failover many hours
after that...
>
John, thanks for your response. Yes I did follow MS knowledge base
articles. I have MS SQL server 2005 x64.
>
>
I did the ping commands. Well, I have tested the failover many hours
after that...
>
Any idea why this is so slow?
>
Thanks,
Aleu
I can't think of any other reasons why this should be an issue if your
DNS and routing are working fine. You may want to try changing it back
and seeing if the time improves.
Unfortunately, I cannot change it back. Not only the IP addresses of
cluster resources have changed but also, cluster node's IP addresses
(moved to a different network). The "SQL IP address 1" becomes active
very fast (in around 6 seconds), however, "SQL Network Name" takes a few
good minutes (3-4) to change status from "online pending" to "online".
>
Unfortunately, I cannot change it back. Not only the IP addresses of
cluster resources have changed but also, cluster node's IP addresses
(moved to a different network). The "SQL IP address 1" becomes active
very fast (in around 6 seconds), however, "SQL Network Name" takes a few
good minutes (3-4) to change status from "online pending" to "online".
>
I have checked system logs and found few relevant things:
>
1. The configuration of theAdminConnection\TCPprotocol in the SQL
instance VALIDATION is not valid.
>
2. Database mirroring connection error 4 'An error occurred while
receiving data: '10053(An established connection was aborted by the
software in your host machine.)'.' for 'TCP://hostname.x.x:2343'.
>
3. Database mirroring connection error 4 'An error occurred while
receiving data: '64(The specified network name is no longer available.)'.'
>
4. Configuration option 'Agent XPs' changed from 1 to 0. Run the
RECONFIGURE statement to install.
>
I do not know whether they are of any use, but (1) seems to be relevant.
What does it mean? Where can I check/change settings ofAdminConnection\TCPprotocol?
>
What is the best place to look for the error logs? Does MS SQL server
store its error logs in some specific files? If so, what is the filename
and it's location?
>
Sorry for many questions but I am still newbie in this thing.
>
Thanks,
Aleu
There are quite a few posts if you look for "AdminConnection\TCP"
http://groups.google.com/groups/sea...ection%5CTCP%22
they seems to suggest networking issues (incorrect aliases, firewall
issues etc) or possibly service account permissions.
I have recently changed IP addresses on my MS SQL cluster (the new IP
addresses belongs to a different network). Both physical node's IP
addresses and resource's IP addresses have been changed.
Everything seems to work fine. The cluster is up and responding
normally. The resources failover to another node when needed correctly.
However, I have noticed that the time it takes for the resource to
failover to another node is much longer than in the past (before IP
addresses change). I have noticed that "Network Name" is the resource
type that causes the delay. Could you please advise if there is
something that I overlooked and needs to be changed.updated as well, so
that groups fail over faster?
Thanks,
AleuHi
<aleu@.vp.plwrote in message news:f34s2m$b1q$2@.news.onet.pl...
Quote:
Originally Posted by
Hi all,
>
I have recently changed IP addresses on my MS SQL cluster (the new IP
addresses belongs to a different network). Both physical node's IP
addresses and resource's IP addresses have been changed.
>
Everything seems to work fine. The cluster is up and responding normally.
The resources failover to another node when needed correctly. However, I
have noticed that the time it takes for the resource to failover to
another node is much longer than in the past (before IP addresses change).
I have noticed that "Network Name" is the resource type that causes the
delay. Could you please advise if there is something that I overlooked and
needs to be changed.updated as well, so that groups fail over faster?
>
Thanks,
Aleu
I assume that you have followed http://support.microsoft.com/kb/244980 or
http://msdn2.microsoft.com/en-us/library/ms190460.aspx? Which version of SQL
Server is this?
When did you test the failover as it will take time to propogate the IP
address change. Have you tried to ping each of the servers or resolve the
names in the DNS?
John|||John Bell wrote:
Quote:
Originally Posted by
I assume that you have followed http://support.microsoft.com/kb/244980 or
http://msdn2.microsoft.com/en-us/library/ms190460.aspx? Which version of SQL
Server is this?
John, thanks for your response. Yes I did follow MS knowledge base
articles. I have MS SQL server 2005 x64.
Quote:
Originally Posted by
When did you test the failover as it will take time to propogate the IP
address change. Have you tried to ping each of the servers or resolve the
names in the DNS?
I did the ping commands. Well, I have tested the failover many hours
after that...
Any idea why this is so slow?
Thanks,
Aleu|||Hi
On May 28, 2:17 pm, "a...@.vp.pl" <a...@.vp.plwrote:
Quote:
Originally Posted by
John Bell wrote:
Quote:
Originally Posted by
I assume that you have followedhttp://support.microsoft.com/kb/244980or
http://msdn2.microsoft.com/en-us/li...0460.aspx?Which version of SQL
Server is this?
>
John, thanks for your response. Yes I did follow MS knowledge base
articles. I have MS SQL server 2005 x64.
>
Quote:
Originally Posted by
When did you test the failover as it will take time to propogate the IP
address change. Have you tried to ping each of the servers or resolve the
names in the DNS?
>
I did the ping commands. Well, I have tested the failover many hours
after that...
>
Any idea why this is so slow?
>
Thanks,
Aleu
I can't think of any other reasons why this should be an issue if your
DNS and routing are working fine. You may want to try changing it back
and seeing if the time improves.
John|||John Bell wrote:
Quote:
Originally Posted by
>
I can't think of any other reasons why this should be an issue if your
DNS and routing are working fine. You may want to try changing it back
and seeing if the time improves.
Unfortunately, I cannot change it back. Not only the IP addresses of
cluster resources have changed but also, cluster node's IP addresses
(moved to a different network). The "SQL IP address 1" becomes active
very fast (in around 6 seconds), however, "SQL Network Name" takes a few
good minutes (3-4) to change status from "online pending" to "online".
I have checked system logs and found few relevant things:
1. The configuration of the AdminConnection\TCP protocol in the SQL
instance VALIDATION is not valid.
2. Database mirroring connection error 4 'An error occurred while
receiving data: '10053(An established connection was aborted by the
software in your host machine.)'.' for 'TCP://hostname.x.x:2343'.
3. Database mirroring connection error 4 'An error occurred while
receiving data: '64(The specified network name is no longer available.)'.'
4. Configuration option 'Agent XPs' changed from 1 to 0. Run the
RECONFIGURE statement to install.
I do not know whether they are of any use, but (1) seems to be relevant.
What does it mean? Where can I check/change settings of
AdminConnection\TCP protocol?
What is the best place to look for the error logs? Does MS SQL server
store its error logs in some specific files? If so, what is the filename
and it's location?
Sorry for many questions but I am still newbie in this thing.
Thanks,
Aleu|||Hi Aleu
On May 28, 5:55 pm, "a...@.vp.pl" <a...@.vp.plwrote:
Quote:
Originally Posted by
John Bell wrote:
>
Quote:
Originally Posted by
I can't think of any other reasons why this should be an issue if your
DNS and routing are working fine. You may want to try changing it back
and seeing if the time improves.
>
Unfortunately, I cannot change it back. Not only the IP addresses of
cluster resources have changed but also, cluster node's IP addresses
(moved to a different network). The "SQL IP address 1" becomes active
very fast (in around 6 seconds), however, "SQL Network Name" takes a few
good minutes (3-4) to change status from "online pending" to "online".
>
I have checked system logs and found few relevant things:
>
1. The configuration of theAdminConnection\TCPprotocol in the SQL
instance VALIDATION is not valid.
>
2. Database mirroring connection error 4 'An error occurred while
receiving data: '10053(An established connection was aborted by the
software in your host machine.)'.' for 'TCP://hostname.x.x:2343'.
>
3. Database mirroring connection error 4 'An error occurred while
receiving data: '64(The specified network name is no longer available.)'.'
>
4. Configuration option 'Agent XPs' changed from 1 to 0. Run the
RECONFIGURE statement to install.
>
I do not know whether they are of any use, but (1) seems to be relevant.
What does it mean? Where can I check/change settings ofAdminConnection\TCPprotocol?
>
What is the best place to look for the error logs? Does MS SQL server
store its error logs in some specific files? If so, what is the filename
and it's location?
>
Sorry for many questions but I am still newbie in this thing.
>
Thanks,
Aleu
There are quite a few posts if you look for "AdminConnection\TCP"
http://groups.google.com/groups/sea...ection%5CTCP%22
they seems to suggest networking issues (incorrect aliases, firewall
issues etc) or possibly service account permissions.
I assume that the error messages regarding mirroring are related to
the period when you are switching, therefore may be expected.
John
Thursday, March 22, 2012
Cluster Fails when lose network...
Hello. We have a SQL Server 2000 Cluster on Windows Server 2003 that is
running great. We had a network outage last week, however, and the
whole cluster went down. We noticed that the Cluster Group "IP Address"
resource went down and that then took the SQL Server services offline.
We reproduced this behavior this morning by unplugging the WAN network
card [while leaving the "heartbeat" connection] and the Cluster Group
failed and took the SQL Cluster group offline as a result. This is a
major flaw. What needs to be configured to work around this? A
stand-alone SQL Server does not fail when unplugged from the network, it
just keeps on running until connectivity is restored.
This isn't a flaw, it is a feature. The cluster service monitors the
service and its underlying resources. When a resource is unavailable, the
cluster tries to find a node where the resource is available. If you unplug
one host from the WAN, the SQL server group should fail over to the other
node as should any other cluster groups. Read a little more about clusters
and you will see why this is happening.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"michael [multnomah]" <wewill@.reclaimourfuture.net> wrote in message
news:g7mdnZS4QJZQaQ3cRVn-iA@.speakeasy.net...
> Hello. We have a SQL Server 2000 Cluster on Windows Server 2003 that is
> running great. We had a network outage last week, however, and the
> whole cluster went down. We noticed that the Cluster Group "IP Address"
> resource went down and that then took the SQL Server services offline.
> We reproduced this behavior this morning by unplugging the WAN network
> card [while leaving the "heartbeat" connection] and the Cluster Group
> failed and took the SQL Cluster group offline as a result. This is a
> major flaw. What needs to be configured to work around this? A
> stand-alone SQL Server does not fail when unplugged from the network, it
> just keeps on running until connectivity is restored.
|||This is correct. The cluster service periodically connects to SQL Server to
verify that it is usable. If it cannot connect it will fail the resource
and attempt to bring it back on line.
Rand
This posting is provided "as is" with no warranties and confers no rights.
running great. We had a network outage last week, however, and the
whole cluster went down. We noticed that the Cluster Group "IP Address"
resource went down and that then took the SQL Server services offline.
We reproduced this behavior this morning by unplugging the WAN network
card [while leaving the "heartbeat" connection] and the Cluster Group
failed and took the SQL Cluster group offline as a result. This is a
major flaw. What needs to be configured to work around this? A
stand-alone SQL Server does not fail when unplugged from the network, it
just keeps on running until connectivity is restored.
This isn't a flaw, it is a feature. The cluster service monitors the
service and its underlying resources. When a resource is unavailable, the
cluster tries to find a node where the resource is available. If you unplug
one host from the WAN, the SQL server group should fail over to the other
node as should any other cluster groups. Read a little more about clusters
and you will see why this is happening.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"michael [multnomah]" <wewill@.reclaimourfuture.net> wrote in message
news:g7mdnZS4QJZQaQ3cRVn-iA@.speakeasy.net...
> Hello. We have a SQL Server 2000 Cluster on Windows Server 2003 that is
> running great. We had a network outage last week, however, and the
> whole cluster went down. We noticed that the Cluster Group "IP Address"
> resource went down and that then took the SQL Server services offline.
> We reproduced this behavior this morning by unplugging the WAN network
> card [while leaving the "heartbeat" connection] and the Cluster Group
> failed and took the SQL Cluster group offline as a result. This is a
> major flaw. What needs to be configured to work around this? A
> stand-alone SQL Server does not fail when unplugged from the network, it
> just keeps on running until connectivity is restored.
|||This is correct. The cluster service periodically connects to SQL Server to
verify that it is usable. If it cannot connect it will fail the resource
and attempt to bring it back on line.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Tuesday, March 20, 2012
Cluster does not restart SQL group after network failure
All,
We have just rebuilt a SQL 7.0/NT cluster with Windows 2003/SQL2000 in an
active/passive configuration using 2 nodes. During the course of testing it
we had a general network failure in which the network was unavailable. The
virtual SQL and Windows IP address resources went down and did not come up
automatically once the network was available again. The nodes are configured
for automatic failback.
I can't imagine that in the 2 1/2 years the original cluster was running
that we never once had the network go down, but I do know that during that
time I never had a outage where I had to manually move the cluster group
(which causes the cluster to re-initialize both resources and brings
everything back to normal).
I'm thinking that maybe I'm missing a dependency somewhere or something's
changed between NT and 2003 that I'm not accounting for. Anyone seen this or
have any tips? Thanks in advance!
-Dan
Nope, that is pretty much expected behavior. The cluster manager will try
and restart the resources on each possible node until the retry count is
exhausted. Unfortunately, until the network resource is restored, no node
has the ability to run the SQL group. With the physical network port
offline, the IP address(es) will not come online. Nothing dependant on them
will come online, including the Network Name and the SQL Server. If the
network comes back before the retry timeout and count is exhausted, the
cluster will bring the system online. Otherwise it stays down.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:74592016-0B61-4834-8C28-1AD1B864B688@.microsoft.com...
> All,
> We have just rebuilt a SQL 7.0/NT cluster with Windows 2003/SQL2000 in
an
> active/passive configuration using 2 nodes. During the course of testing
it
> we had a general network failure in which the network was unavailable.
The
> virtual SQL and Windows IP address resources went down and did not come up
> automatically once the network was available again. The nodes are
configured
> for automatic failback.
> I can't imagine that in the 2 1/2 years the original cluster was running
> that we never once had the network go down, but I do know that during that
> time I never had a outage where I had to manually move the cluster group
> (which causes the cluster to re-initialize both resources and brings
> everything back to normal).
> I'm thinking that maybe I'm missing a dependency somewhere or something's
> changed between NT and 2003 that I'm not accounting for. Anyone seen this
or
> have any tips? Thanks in advance!
> -Dan
|||Geoff,
Thanks for the post! I guess I'll just have to make sure the retry &
timeout are set high.
"Geoff N. Hiten" wrote:
> Nope, that is pretty much expected behavior. The cluster manager will try
> and restart the resources on each possible node until the retry count is
> exhausted. Unfortunately, until the network resource is restored, no node
> has the ability to run the SQL group. With the physical network port
> offline, the IP address(es) will not come online. Nothing dependant on them
> will come online, including the Network Name and the SQL Server. If the
> network comes back before the retry timeout and count is exhausted, the
> cluster will bring the system online. Otherwise it stays down.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:74592016-0B61-4834-8C28-1AD1B864B688@.microsoft.com...
> an
> it
> The
> configured
> or
>
>
|||Be careful adjusting those numbers. TOo high can cause just as many
problems as too low. Given the frequency of the network outage and the fact
that something like that will NEVER go unnoticed, I would not change
anything. The cluster failover is designed to reduce the typical 30-45
minute human reponse time for a down server. You shouldn't expect the
clustering software do deal with anything beyond that scope. Adjusting the
parameters to try and expand that coverage will only expose a gap somewhere
else. Just document a cluster check as part of your network failure
recovery procedure and you will be fine.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:34AB2749-41BF-4A68-8E55-04AEAAE75C38@.microsoft.com...[vbcol=seagreen]
> Geoff,
> Thanks for the post! I guess I'll just have to make sure the retry &
> timeout are set high.
> "Geoff N. Hiten" wrote:
try[vbcol=seagreen]
node[vbcol=seagreen]
them[vbcol=seagreen]
in[vbcol=seagreen]
testing[vbcol=seagreen]
come up[vbcol=seagreen]
running[vbcol=seagreen]
that[vbcol=seagreen]
group[vbcol=seagreen]
something's[vbcol=seagreen]
this[vbcol=seagreen]
We have just rebuilt a SQL 7.0/NT cluster with Windows 2003/SQL2000 in an
active/passive configuration using 2 nodes. During the course of testing it
we had a general network failure in which the network was unavailable. The
virtual SQL and Windows IP address resources went down and did not come up
automatically once the network was available again. The nodes are configured
for automatic failback.
I can't imagine that in the 2 1/2 years the original cluster was running
that we never once had the network go down, but I do know that during that
time I never had a outage where I had to manually move the cluster group
(which causes the cluster to re-initialize both resources and brings
everything back to normal).
I'm thinking that maybe I'm missing a dependency somewhere or something's
changed between NT and 2003 that I'm not accounting for. Anyone seen this or
have any tips? Thanks in advance!
-Dan
Nope, that is pretty much expected behavior. The cluster manager will try
and restart the resources on each possible node until the retry count is
exhausted. Unfortunately, until the network resource is restored, no node
has the ability to run the SQL group. With the physical network port
offline, the IP address(es) will not come online. Nothing dependant on them
will come online, including the Network Name and the SQL Server. If the
network comes back before the retry timeout and count is exhausted, the
cluster will bring the system online. Otherwise it stays down.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:74592016-0B61-4834-8C28-1AD1B864B688@.microsoft.com...
> All,
> We have just rebuilt a SQL 7.0/NT cluster with Windows 2003/SQL2000 in
an
> active/passive configuration using 2 nodes. During the course of testing
it
> we had a general network failure in which the network was unavailable.
The
> virtual SQL and Windows IP address resources went down and did not come up
> automatically once the network was available again. The nodes are
configured
> for automatic failback.
> I can't imagine that in the 2 1/2 years the original cluster was running
> that we never once had the network go down, but I do know that during that
> time I never had a outage where I had to manually move the cluster group
> (which causes the cluster to re-initialize both resources and brings
> everything back to normal).
> I'm thinking that maybe I'm missing a dependency somewhere or something's
> changed between NT and 2003 that I'm not accounting for. Anyone seen this
or
> have any tips? Thanks in advance!
> -Dan
|||Geoff,
Thanks for the post! I guess I'll just have to make sure the retry &
timeout are set high.
"Geoff N. Hiten" wrote:
> Nope, that is pretty much expected behavior. The cluster manager will try
> and restart the resources on each possible node until the retry count is
> exhausted. Unfortunately, until the network resource is restored, no node
> has the ability to run the SQL group. With the physical network port
> offline, the IP address(es) will not come online. Nothing dependant on them
> will come online, including the Network Name and the SQL Server. If the
> network comes back before the retry timeout and count is exhausted, the
> cluster will bring the system online. Otherwise it stays down.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:74592016-0B61-4834-8C28-1AD1B864B688@.microsoft.com...
> an
> it
> The
> configured
> or
>
>
|||Be careful adjusting those numbers. TOo high can cause just as many
problems as too low. Given the frequency of the network outage and the fact
that something like that will NEVER go unnoticed, I would not change
anything. The cluster failover is designed to reduce the typical 30-45
minute human reponse time for a down server. You shouldn't expect the
clustering software do deal with anything beyond that scope. Adjusting the
parameters to try and expand that coverage will only expose a gap somewhere
else. Just document a cluster check as part of your network failure
recovery procedure and you will be fine.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:34AB2749-41BF-4A68-8E55-04AEAAE75C38@.microsoft.com...[vbcol=seagreen]
> Geoff,
> Thanks for the post! I guess I'll just have to make sure the retry &
> timeout are set high.
> "Geoff N. Hiten" wrote:
try[vbcol=seagreen]
node[vbcol=seagreen]
them[vbcol=seagreen]
in[vbcol=seagreen]
testing[vbcol=seagreen]
come up[vbcol=seagreen]
running[vbcol=seagreen]
that[vbcol=seagreen]
group[vbcol=seagreen]
something's[vbcol=seagreen]
this[vbcol=seagreen]
Cluster backup
Quick question...
2 node active/passive (excuse the terminology...) cluster.
My network admin wants to have my SQL backups (performed via. maint. plan
nightly) stored on the quorum drive.
We are also performing log shipping at 15 minute intervals to an offsite
server.
I think storing the backups on the quorum drive is a bad idea as if the disk
array dies completely, we have no backup.
Also, would that scheme not necessitate adding the quorum drive as a
resource to the SQL Server?
Comments and thoughts please.
Thanks in advance.
B.
You are correct, its a bad idea
The Quorum drive would have to be a
dependency for SQL. Which would mean one group for basically everything. If
the backups filled the disk, the cluster would not be happy.
A good idea is use the MP to a disk, and then backup to some kind of tape.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Tam O'Shanter" <Tam@.Oshanter.com> wrote in message
news:b6prd.6024491$6p.961500@.news.easynews.com...
> Quick question...
> 2 node active/passive (excuse the terminology...) cluster.
> My network admin wants to have my SQL backups (performed via. maint. plan
> nightly) stored on the quorum drive.
> We are also performing log shipping at 15 minute intervals to an offsite
> server.
> I think storing the backups on the quorum drive is a bad idea as if the
> disk
> array dies completely, we have no backup.
> Also, would that scheme not necessitate adding the quorum drive as a
> resource to the SQL Server?
> Comments and thoughts please.
> Thanks in advance.
> B.
>
>
|||Thanks Rodney,
Validation of what I thought.
Would you suggest backing up to a local disk on one of the nodes? Or rather,
having the MP write to a UNC on another machine in the environment
(management server etc..).
I think the UNC is the way to go, but I'd be interested in your thoughts.
Every morning the bak's would be moved from the backup location to an
external firewire drive and physically removed from the facility. This
setup, in conjunction with a logshp (15 min. intervals) to an offsite box
seems like my best option.
Comments?
Thanks again.
B.
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:ORI$OI%231EHA.1192@.tk2msftngp13.phx.gbl...
> You are correct, its a bad idea The Quorum drive would have to be a
> dependency for SQL. Which would mean one group for basically everything.
If[vbcol=seagreen]
> the backups filled the disk, the cluster would not be happy.
> A good idea is use the MP to a disk, and then backup to some kind of tape.
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://msmvps.com/clustering - Blog
> "Tam O'Shanter" <Tam@.Oshanter.com> wrote in message
> news:b6prd.6024491$6p.961500@.news.easynews.com...
plan
>
|||Tam, you have a problem. You can't backup SQL using the MP to a UNC, though
the last time I tried was years ago. I also don't like the idea of local
node backups. Can you add a disk to the array? Or does the current array
have space already? Remember with the MP you can delete old backups to keep
the space minimal.
Rod
"Tam O'Shanter" <Tam@.Oshanter.com> wrote in message
news:kXprd.3931798$yk.598061@.news.easynews.com...
> Thanks Rodney,
> Validation of what I thought.
> Would you suggest backing up to a local disk on one of the nodes? Or
> rather,
> having the MP write to a UNC on another machine in the environment
> (management server etc..).
> I think the UNC is the way to go, but I'd be interested in your thoughts.
> Every morning the bak's would be moved from the backup location to an
> external firewire drive and physically removed from the facility. This
> setup, in conjunction with a logshp (15 min. intervals) to an offsite box
> seems like my best option.
> Comments?
> Thanks again.
> B.
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:ORI$OI%231EHA.1192@.tk2msftngp13.phx.gbl...
> If
> plan
>
|||Yes, you can backup to a UNC. I do it all the time. There is even a
community KB article on how to do backup to a UNC share. Personally, I
think it is one of the best ways to back up a system.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:u9yvudB2EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Tam, you have a problem. You can't backup SQL using the MP to a UNC,
though
> the last time I tried was years ago. I also don't like the idea of local
> node backups. Can you add a disk to the array? Or does the current array
> have space already? Remember with the MP you can delete old backups to
keep[vbcol=seagreen]
> the space minimal.
> Rod
> "Tam O'Shanter" <Tam@.Oshanter.com> wrote in message
> news:kXprd.3931798$yk.598061@.news.easynews.com...
thoughts.[vbcol=seagreen]
box[vbcol=seagreen]
everything.[vbcol=seagreen]
the
>
|||Excellent. I will file that away for future use Maybe it was 7.0 or 6.5
or 4.21 or 4.2, I have been using SQL for a long time. What I don't like
about UNC is that any network glitch or outage can potentially kill the
backup.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:O845A2H2EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Yes, you can backup to a UNC. I do it all the time. There is even a
> community KB article on how to do backup to a UNC share. Personally, I
> think it is one of the best ways to back up a system.
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:u9yvudB2EHA.2568@.TK2MSFTNGP10.phx.gbl...
> though
> keep
> thoughts.
> box
> everything.
> the
>
|||Yep. You can also easily overwhelm a network. I use a dedicated link with
a connection-specific domain suffix to segment the backup traffic from the
normal SQL traffic. I also tune the file share to maximize write
capability. I just upgraded the backup host server (which is also my tape
host server) to handle the load.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:%23kJFlsI2EHA.2572@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Excellent. I will file that away for future use Maybe it was 7.0 or 6.5
> or 4.21 or 4.2, I have been using SQL for a long time. What I don't like
> about UNC is that any network glitch or outage can potentially kill the
> backup.
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://msmvps.com/clustering - Blog
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:O845A2H2EHA.2568@.TK2MSFTNGP11.phx.gbl...
local[vbcol=seagreen]
array[vbcol=seagreen]
This[vbcol=seagreen]
a[vbcol=seagreen]
maint.[vbcol=seagreen]
if[vbcol=seagreen]
a
>
|||We use the Tivoli suite of products...Tivoli storage manager for the OS, and
Tivoli data protection for SQL. We cart all back ups off the servers in case
there is a drive failure. As long as your cluster is active / passive, it's
not too intense to set up and use, but active / active gets a bit trickier.
both nodes need to be able to authenticate to the back up server as each othr
on active / active....we have 2 such clusters running this way, and 5 more
active/ passives.
"Geoff N. Hiten" wrote:
> Yep. You can also easily overwhelm a network. I use a dedicated link with
> a connection-specific domain suffix to segment the backup traffic from the
> normal SQL traffic. I also tune the file share to maximize write
> capability. I just upgraded the backup host server (which is also my tape
> host server) to handle the load.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:%23kJFlsI2EHA.2572@.tk2msftngp13.phx.gbl...
> local
> array
> This
> a
> maint.
> if
> a
>
>
sqlsql
2 node active/passive (excuse the terminology...) cluster.
My network admin wants to have my SQL backups (performed via. maint. plan
nightly) stored on the quorum drive.
We are also performing log shipping at 15 minute intervals to an offsite
server.
I think storing the backups on the quorum drive is a bad idea as if the disk
array dies completely, we have no backup.
Also, would that scheme not necessitate adding the quorum drive as a
resource to the SQL Server?
Comments and thoughts please.
Thanks in advance.
B.
You are correct, its a bad idea
The Quorum drive would have to be adependency for SQL. Which would mean one group for basically everything. If
the backups filled the disk, the cluster would not be happy.
A good idea is use the MP to a disk, and then backup to some kind of tape.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Tam O'Shanter" <Tam@.Oshanter.com> wrote in message
news:b6prd.6024491$6p.961500@.news.easynews.com...
> Quick question...
> 2 node active/passive (excuse the terminology...) cluster.
> My network admin wants to have my SQL backups (performed via. maint. plan
> nightly) stored on the quorum drive.
> We are also performing log shipping at 15 minute intervals to an offsite
> server.
> I think storing the backups on the quorum drive is a bad idea as if the
> disk
> array dies completely, we have no backup.
> Also, would that scheme not necessitate adding the quorum drive as a
> resource to the SQL Server?
> Comments and thoughts please.
> Thanks in advance.
> B.
>
>
|||Thanks Rodney,
Validation of what I thought.
Would you suggest backing up to a local disk on one of the nodes? Or rather,
having the MP write to a UNC on another machine in the environment
(management server etc..).
I think the UNC is the way to go, but I'd be interested in your thoughts.
Every morning the bak's would be moved from the backup location to an
external firewire drive and physically removed from the facility. This
setup, in conjunction with a logshp (15 min. intervals) to an offsite box
seems like my best option.
Comments?
Thanks again.
B.
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:ORI$OI%231EHA.1192@.tk2msftngp13.phx.gbl...
> You are correct, its a bad idea
The Quorum drive would have to be a> dependency for SQL. Which would mean one group for basically everything.
If[vbcol=seagreen]
> the backups filled the disk, the cluster would not be happy.
> A good idea is use the MP to a disk, and then backup to some kind of tape.
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://msmvps.com/clustering - Blog
> "Tam O'Shanter" <Tam@.Oshanter.com> wrote in message
> news:b6prd.6024491$6p.961500@.news.easynews.com...
plan
>
|||Tam, you have a problem. You can't backup SQL using the MP to a UNC, though
the last time I tried was years ago. I also don't like the idea of local
node backups. Can you add a disk to the array? Or does the current array
have space already? Remember with the MP you can delete old backups to keep
the space minimal.
Rod
"Tam O'Shanter" <Tam@.Oshanter.com> wrote in message
news:kXprd.3931798$yk.598061@.news.easynews.com...
> Thanks Rodney,
> Validation of what I thought.
> Would you suggest backing up to a local disk on one of the nodes? Or
> rather,
> having the MP write to a UNC on another machine in the environment
> (management server etc..).
> I think the UNC is the way to go, but I'd be interested in your thoughts.
> Every morning the bak's would be moved from the backup location to an
> external firewire drive and physically removed from the facility. This
> setup, in conjunction with a logshp (15 min. intervals) to an offsite box
> seems like my best option.
> Comments?
> Thanks again.
> B.
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:ORI$OI%231EHA.1192@.tk2msftngp13.phx.gbl...
> If
> plan
>
|||Yes, you can backup to a UNC. I do it all the time. There is even a
community KB article on how to do backup to a UNC share. Personally, I
think it is one of the best ways to back up a system.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:u9yvudB2EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Tam, you have a problem. You can't backup SQL using the MP to a UNC,
though
> the last time I tried was years ago. I also don't like the idea of local
> node backups. Can you add a disk to the array? Or does the current array
> have space already? Remember with the MP you can delete old backups to
keep[vbcol=seagreen]
> the space minimal.
> Rod
> "Tam O'Shanter" <Tam@.Oshanter.com> wrote in message
> news:kXprd.3931798$yk.598061@.news.easynews.com...
thoughts.[vbcol=seagreen]
box[vbcol=seagreen]
everything.[vbcol=seagreen]
the
>
|||Excellent. I will file that away for future use
Maybe it was 7.0 or 6.5or 4.21 or 4.2, I have been using SQL for a long time. What I don't like
about UNC is that any network glitch or outage can potentially kill the
backup.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:O845A2H2EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Yes, you can backup to a UNC. I do it all the time. There is even a
> community KB article on how to do backup to a UNC share. Personally, I
> think it is one of the best ways to back up a system.
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:u9yvudB2EHA.2568@.TK2MSFTNGP10.phx.gbl...
> though
> keep
> thoughts.
> box
> everything.
> the
>
|||Yep. You can also easily overwhelm a network. I use a dedicated link with
a connection-specific domain suffix to segment the backup traffic from the
normal SQL traffic. I also tune the file share to maximize write
capability. I just upgraded the backup host server (which is also my tape
host server) to handle the load.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:%23kJFlsI2EHA.2572@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Excellent. I will file that away for future use
Maybe it was 7.0 or 6.5> or 4.21 or 4.2, I have been using SQL for a long time. What I don't like
> about UNC is that any network glitch or outage can potentially kill the
> backup.
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://msmvps.com/clustering - Blog
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:O845A2H2EHA.2568@.TK2MSFTNGP11.phx.gbl...
local[vbcol=seagreen]
array[vbcol=seagreen]
This[vbcol=seagreen]
a[vbcol=seagreen]
maint.[vbcol=seagreen]
if[vbcol=seagreen]
a
>
|||We use the Tivoli suite of products...Tivoli storage manager for the OS, and
Tivoli data protection for SQL. We cart all back ups off the servers in case
there is a drive failure. As long as your cluster is active / passive, it's
not too intense to set up and use, but active / active gets a bit trickier.
both nodes need to be able to authenticate to the back up server as each othr
on active / active....we have 2 such clusters running this way, and 5 more
active/ passives.
"Geoff N. Hiten" wrote:
> Yep. You can also easily overwhelm a network. I use a dedicated link with
> a connection-specific domain suffix to segment the backup traffic from the
> normal SQL traffic. I also tune the file share to maximize write
> capability. I just upgraded the backup host server (which is also my tape
> host server) to handle the load.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:%23kJFlsI2EHA.2572@.tk2msftngp13.phx.gbl...
> local
> array
> This
> a
> maint.
> if
> a
>
>
sqlsql
Saturday, February 25, 2012
Cllient Unable to establish connection
Hi Am using a SQL 2k server and have been regularly logging into other SQL 2K servers in the network using the SQL Query Analyser/Enterprise Manager. After suffering a BSD yesterday, unable to log in to any server from my Query Analyser/Enterprise Manager - get this error: "Client Unable to Establish Connection".
Others are also not able establish connection - they get a General Network error when trying to connecting to my server.
Think the Blue screen death corrupted some SQL driver in the process. Now am apprehensive about uninstalling and re-installing the SQL server since am unable to backup the databases.
Can anybody help please?SQL 2k?
I find it very unlikely...did you bounce the box?
Did and admin revoke your rights?
Blue screen of death? Haven;'t seen that in a loong while.
What o/s and what patches?|||SQL 2K with SP 3. Running on Win 2k (SP4) Server. There has been no change in the admin rights. it just stopped working after the crash.
that's why its confusing.. not much help from Miscrosoft either.
Originally posted by Brett Kaiser
SQL 2k?
I find it very unlikely...did you bounce the box?
Did and admin revoke your rights?
Blue screen of death? Haven;'t seen that in a loong while.
What o/s and what patches?|||Check your Client Config utility ? Try creating an alias using TCP/IP ..
What SQl Client you are using from where you accessing SQL Server ..?|||Tried the Client Network Utility - the TCP/IP option is not enabled when i try to add an alias. Only the Multiple Protocol, Apple Talk, VIA and "Other" options are enabled.
The server is installed in my laptop and am accessing from the same machine using the SQL Query Analyser. The laptop is a Win2k Server (SP4).
Originally posted by aashu
Check your Client Config utility ? Try creating an alias using TCP/IP ..
What SQl Client you are using from where you accessing SQL Server ..?
Others are also not able establish connection - they get a General Network error when trying to connecting to my server.
Think the Blue screen death corrupted some SQL driver in the process. Now am apprehensive about uninstalling and re-installing the SQL server since am unable to backup the databases.
Can anybody help please?SQL 2k?
I find it very unlikely...did you bounce the box?
Did and admin revoke your rights?
Blue screen of death? Haven;'t seen that in a loong while.
What o/s and what patches?|||SQL 2K with SP 3. Running on Win 2k (SP4) Server. There has been no change in the admin rights. it just stopped working after the crash.
that's why its confusing.. not much help from Miscrosoft either.
Originally posted by Brett Kaiser
SQL 2k?
I find it very unlikely...did you bounce the box?
Did and admin revoke your rights?
Blue screen of death? Haven;'t seen that in a loong while.
What o/s and what patches?|||Check your Client Config utility ? Try creating an alias using TCP/IP ..
What SQl Client you are using from where you accessing SQL Server ..?|||Tried the Client Network Utility - the TCP/IP option is not enabled when i try to add an alias. Only the Multiple Protocol, Apple Talk, VIA and "Other" options are enabled.
The server is installed in my laptop and am accessing from the same machine using the SQL Query Analyser. The laptop is a Win2k Server (SP4).
Originally posted by aashu
Check your Client Config utility ? Try creating an alias using TCP/IP ..
What SQl Client you are using from where you accessing SQL Server ..?
Client-side Redirect problem
Hi there.
I have a database mirroring session running, and both principal and mirror are working fine. But when i unplug the network cable from principal server, creating a failover scenario, the client doesn′t get redirected to the mirror.
I get an error saying: "A transport-level error has ocurred when received results from the server. The specified network name is no longer available."
connection = "Data Source=SISAD;Failover Partner=Projecto-SWS1;Initial Catalog=SIERE;Persist Security Info=True;User ID=sa;Password=testing;Connection Timeout=100";
//this method is called many times by a thread, emulating some request to the server
public static void Inserir(int codAutor)
{
SqlConnection sqlConnection = new SqlConnection(connection);
SqlCommand command = new SqlCommand("insert into Autor values(@.codAutor,'test')", sqlConnection);
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@.codAutor", codAutor.ToString());
command.CommandTimeout = 200;
sqlConnection.Open();
command.ExecuteNonQuery();
sqlConnection.Close();
}
I think the error is because the ExecuteNonQuery() method return info about the rows affected on the server. But in this case how can i create a failover scenario with client-side redirect ?
Thanks in advance.
I think i′ve resolved the problem...
The issue here is that connections are separated into pools by connection string, when using ADO.NET connection pooling.
It seems that when failover occurs, the attempts to reconnect to the database are retrieving connections from the pool, and the datasource is still the failed principal and not the new principal.
I deactivated connection pooling, and now the clients are connecting to the mirror when when principal fails.
Can someone give me some feedback about this ? It′s seems that i can′t use connection pooling when using client-side redirect.
|||Hi RDSC,
I believe your assessment is correct. The logic to failover to the partner server only occurs when a new connection is established. Rather than disabling connection pooling altogether, you can clear this individual pool (I believe that there is a clearpool connection property that you can set) and should see the connections succeed. If you do try this, please let me know how it turns out.
Thanks,
Il-Sung
Program Manager, SQL Server Protocols.
Hi there.
You are allright. In my code i can see if there is a connection error, and than set clearpool property. Next time the connection will try to connect to the new principal. So connection pooling can be used with client side redirect.
There is something you should be aware. If clearpool property is used, the state of the connection changes to "Closed", because connections from the pool are set to be discarded, so if you still want to use that connection, you have to open it again.
Thanks for you help Sung Lee.
Client-side Redirect problem
Hi there.
I have a database mirroring session running, and both principal and mirror are working fine. But when i unplug the network cable from principal server, creating a failover scenario, the client doesn′t get redirected to the mirror.
I get an error saying: "A transport-level error has ocurred when received results from the server. The specified network name is no longer available."
connection = "Data Source=SISAD;Failover Partner=Projecto-SWS1;Initial Catalog=SIERE;Persist Security Info=True;User ID=sa;Password=testing;Connection Timeout=100";
//this method is called many times by a thread, emulating some request to the server
public static void Inserir(int codAutor)
{
SqlConnection sqlConnection = new SqlConnection(connection);
SqlCommand command = new SqlCommand("insert into Autor values(@.codAutor,'test')", sqlConnection);
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@.codAutor", codAutor.ToString());
command.CommandTimeout = 200;
sqlConnection.Open();
command.ExecuteNonQuery();
sqlConnection.Close();
}
I think the error is because the ExecuteNonQuery() method return info about the rows affected on the server. But in this case how can i create a failover scenario with client-side redirect ?
Thanks in advance.
I think i′ve resolved the problem...
The issue here is that connections are separated into pools by connection string, when using ADO.NET connection pooling.
It seems that when failover occurs, the attempts to reconnect to the database are retrieving connections from the pool, and the datasource is still the failed principal and not the new principal.
I deactivated connection pooling, and now the clients are connecting to the mirror when when principal fails.
Can someone give me some feedback about this ? It′s seems that i can′t use connection pooling when using client-side redirect.
|||Hi RDSC,
I believe your assessment is correct. The logic to failover to the partner server only occurs when a new connection is established. Rather than disabling connection pooling altogether, you can clear this individual pool (I believe that there is a clearpool connection property that you can set) and should see the connections succeed. If you do try this, please let me know how it turns out.
Thanks,
Il-Sung
Program Manager, SQL Server Protocols.
Hi there.
You are allright. In my code i can see if there is a connection error, and than set clearpool property. Next time the connection will try to connect to the new principal. So connection pooling can be used with client side redirect.
There is something you should be aware. If clearpool property is used, the state of the connection changes to "Closed", because connections from the pool are set to be discarded, so if you still want to use that connection, you have to open it again.
Thanks for you help Sung Lee.
Friday, February 24, 2012
Clients being disconnected periodically over WAN
Some background information. I have a Windows 2003 Server that is also
running SQL 2000 on network "172.16.30.0". I have a remote facility,
"172.16.20.0", that is connected by WAN with a full T1 line. When the remote
users login to our SQL application the server creates a routing statement for
each user's machine. If I do a "route print" statement I see the static
routes for the facility, "172.16.20.0 255.255.255.0 172.16.30.11" under both
Active and Persistent routes. I also see the IP addresses of the remote
computers under Active Routes. For some reason the remote users are being
disconnected from the SQL application but their IP addresses still remain
under Active Routes. They cannot log back into the SQL application until I
manually delete their routes from the SQL Server. Once this is done they can
successful log back into the SQL application. Is the server suppose to keep
track of remote machine routing connections? We migrated from Frame Relay to
VPN connectivity and I do not recall seing each remote user's IP address
within the "Route Print" statement previously.
Not my usual turf, but since no one else responded, I'll put my foot in my
mouth.
I think you have a network issue, not a database issue. It sounds like
youhave a timeout in TCP/IP (or whatever) that is doing the disconnect. I
have not seen SS drop a user or prevent one from logging in (assuming valid
userid, etc), even multiple connections from the same place.
I know there are several places that timeout could be, so someone with more
of a networks background will have to help you point your shotgun.
Joseph R.P. Maloney, CSP,CCP,CDP
"Bill Freeze" wrote:
> Some background information. I have a Windows 2003 Server that is also
> running SQL 2000 on network "172.16.30.0". I have a remote facility,
> "172.16.20.0", that is connected by WAN with a full T1 line. When the remote
> users login to our SQL application the server creates a routing statement for
> each user's machine. If I do a "route print" statement I see the static
> routes for the facility, "172.16.20.0 255.255.255.0 172.16.30.11" under both
> Active and Persistent routes. I also see the IP addresses of the remote
> computers under Active Routes. For some reason the remote users are being
> disconnected from the SQL application but their IP addresses still remain
> under Active Routes. They cannot log back into the SQL application until I
> manually delete their routes from the SQL Server. Once this is done they can
> successful log back into the SQL application. Is the server suppose to keep
> track of remote machine routing connections? We migrated from Frame Relay to
> VPN connectivity and I do not recall seing each remote user's IP address
> within the "Route Print" statement previously.
>
running SQL 2000 on network "172.16.30.0". I have a remote facility,
"172.16.20.0", that is connected by WAN with a full T1 line. When the remote
users login to our SQL application the server creates a routing statement for
each user's machine. If I do a "route print" statement I see the static
routes for the facility, "172.16.20.0 255.255.255.0 172.16.30.11" under both
Active and Persistent routes. I also see the IP addresses of the remote
computers under Active Routes. For some reason the remote users are being
disconnected from the SQL application but their IP addresses still remain
under Active Routes. They cannot log back into the SQL application until I
manually delete their routes from the SQL Server. Once this is done they can
successful log back into the SQL application. Is the server suppose to keep
track of remote machine routing connections? We migrated from Frame Relay to
VPN connectivity and I do not recall seing each remote user's IP address
within the "Route Print" statement previously.
Not my usual turf, but since no one else responded, I'll put my foot in my
mouth.
I think you have a network issue, not a database issue. It sounds like
youhave a timeout in TCP/IP (or whatever) that is doing the disconnect. I
have not seen SS drop a user or prevent one from logging in (assuming valid
userid, etc), even multiple connections from the same place.
I know there are several places that timeout could be, so someone with more
of a networks background will have to help you point your shotgun.
Joseph R.P. Maloney, CSP,CCP,CDP
"Bill Freeze" wrote:
> Some background information. I have a Windows 2003 Server that is also
> running SQL 2000 on network "172.16.30.0". I have a remote facility,
> "172.16.20.0", that is connected by WAN with a full T1 line. When the remote
> users login to our SQL application the server creates a routing statement for
> each user's machine. If I do a "route print" statement I see the static
> routes for the facility, "172.16.20.0 255.255.255.0 172.16.30.11" under both
> Active and Persistent routes. I also see the IP addresses of the remote
> computers under Active Routes. For some reason the remote users are being
> disconnected from the SQL application but their IP addresses still remain
> under Active Routes. They cannot log back into the SQL application until I
> manually delete their routes from the SQL Server. Once this is done they can
> successful log back into the SQL application. Is the server suppose to keep
> track of remote machine routing connections? We migrated from Frame Relay to
> VPN connectivity and I do not recall seing each remote user's IP address
> within the "Route Print" statement previously.
>
Clients being disconnected periodically over WAN
Some background information. I have a Windows 2003 Server that is also
running SQL 2000 on network "172.16.30.0". I have a remote facility,
"172.16.20.0", that is connected by WAN with a full T1 line. When the remote
users login to our SQL application the server creates a routing statement for
each user's machine. If I do a "route print" statement I see the static
routes for the facility, "172.16.20.0 255.255.255.0 172.16.30.11" under both
Active and Persistent routes. I also see the IP addresses of the remote
computers under Active Routes. For some reason the remote users are being
disconnected from the SQL application but their IP addresses still remain
under Active Routes. They cannot log back into the SQL application until I
manually delete their routes from the SQL Server. Once this is done they can
successful log back into the SQL application. Is the server suppose to keep
track of remote machine routing connections? We migrated from Frame Relay to
VPN connectivity and I do not recall seing each remote user's IP address
within the "Route Print" statement previously.Not my usual turf, but since no one else responded, I'll put my foot in my
mouth.
I think you have a network issue, not a database issue. It sounds like
youhave a timeout in TCP/IP (or whatever) that is doing the disconnect. I
have not seen SS drop a user or prevent one from logging in (assuming valid
userid, etc), even multiple connections from the same place.
I know there are several places that timeout could be, so someone with more
of a networks background will have to help you point your shotgun.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Bill Freeze" wrote:
> Some background information. I have a Windows 2003 Server that is also
> running SQL 2000 on network "172.16.30.0". I have a remote facility,
> "172.16.20.0", that is connected by WAN with a full T1 line. When the remote
> users login to our SQL application the server creates a routing statement for
> each user's machine. If I do a "route print" statement I see the static
> routes for the facility, "172.16.20.0 255.255.255.0 172.16.30.11" under both
> Active and Persistent routes. I also see the IP addresses of the remote
> computers under Active Routes. For some reason the remote users are being
> disconnected from the SQL application but their IP addresses still remain
> under Active Routes. They cannot log back into the SQL application until I
> manually delete their routes from the SQL Server. Once this is done they can
> successful log back into the SQL application. Is the server suppose to keep
> track of remote machine routing connections? We migrated from Frame Relay to
> VPN connectivity and I do not recall seing each remote user's IP address
> within the "Route Print" statement previously.
>
running SQL 2000 on network "172.16.30.0". I have a remote facility,
"172.16.20.0", that is connected by WAN with a full T1 line. When the remote
users login to our SQL application the server creates a routing statement for
each user's machine. If I do a "route print" statement I see the static
routes for the facility, "172.16.20.0 255.255.255.0 172.16.30.11" under both
Active and Persistent routes. I also see the IP addresses of the remote
computers under Active Routes. For some reason the remote users are being
disconnected from the SQL application but their IP addresses still remain
under Active Routes. They cannot log back into the SQL application until I
manually delete their routes from the SQL Server. Once this is done they can
successful log back into the SQL application. Is the server suppose to keep
track of remote machine routing connections? We migrated from Frame Relay to
VPN connectivity and I do not recall seing each remote user's IP address
within the "Route Print" statement previously.Not my usual turf, but since no one else responded, I'll put my foot in my
mouth.
I think you have a network issue, not a database issue. It sounds like
youhave a timeout in TCP/IP (or whatever) that is doing the disconnect. I
have not seen SS drop a user or prevent one from logging in (assuming valid
userid, etc), even multiple connections from the same place.
I know there are several places that timeout could be, so someone with more
of a networks background will have to help you point your shotgun.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Bill Freeze" wrote:
> Some background information. I have a Windows 2003 Server that is also
> running SQL 2000 on network "172.16.30.0". I have a remote facility,
> "172.16.20.0", that is connected by WAN with a full T1 line. When the remote
> users login to our SQL application the server creates a routing statement for
> each user's machine. If I do a "route print" statement I see the static
> routes for the facility, "172.16.20.0 255.255.255.0 172.16.30.11" under both
> Active and Persistent routes. I also see the IP addresses of the remote
> computers under Active Routes. For some reason the remote users are being
> disconnected from the SQL application but their IP addresses still remain
> under Active Routes. They cannot log back into the SQL application until I
> manually delete their routes from the SQL Server. Once this is done they can
> successful log back into the SQL application. Is the server suppose to keep
> track of remote machine routing connections? We migrated from Frame Relay to
> VPN connectivity and I do not recall seing each remote user's IP address
> within the "Route Print" statement previously.
>
Clients being disconnected periodically over WAN
Some background information. I have a Windows 2003 Server that is also
running SQL 2000 on network "172.16.30.0". I have a remote facility,
"172.16.20.0", that is connected by WAN with a full T1 line. When the remot
e
users login to our SQL application the server creates a routing statement fo
r
each user's machine. If I do a "route print" statement I see the static
routes for the facility, "172.16.20.0 255.255.255.0 172.16.30.11" under both
Active and Persistent routes. I also see the IP addresses of the remote
computers under Active Routes. For some reason the remote users are being
disconnected from the SQL application but their IP addresses still remain
under Active Routes. They cannot log back into the SQL application until I
manually delete their routes from the SQL Server. Once this is done they ca
n
successful log back into the SQL application. Is the server suppose to keep
track of remote machine routing connections? We migrated from Frame Relay t
o
VPN connectivity and I do not recall seing each remote user's IP address
within the "Route Print" statement previously.Not my usual turf, but since no one else responded, I'll put my foot in my
mouth.
I think you have a network issue, not a database issue. It sounds like
youhave a timeout in TCP/IP (or whatever) that is doing the disconnect. I
have not seen SS drop a user or prevent one from logging in (assuming valid
userid, etc), even multiple connections from the same place.
I know there are several places that timeout could be, so someone with more
of a networks background will have to help you point your shotgun.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Bill Freeze" wrote:
> Some background information. I have a Windows 2003 Server that is also
> running SQL 2000 on network "172.16.30.0". I have a remote facility,
> "172.16.20.0", that is connected by WAN with a full T1 line. When the rem
ote
> users login to our SQL application the server creates a routing statement
for
> each user's machine. If I do a "route print" statement I see the static
> routes for the facility, "172.16.20.0 255.255.255.0 172.16.30.11" under bo
th
> Active and Persistent routes. I also see the IP addresses of the remote
> computers under Active Routes. For some reason the remote users are being
> disconnected from the SQL application but their IP addresses still remain
> under Active Routes. They cannot log back into the SQL application until
I
> manually delete their routes from the SQL Server. Once this is done they
can
> successful log back into the SQL application. Is the server suppose to ke
ep
> track of remote machine routing connections? We migrated from Frame Relay
to
> VPN connectivity and I do not recall seing each remote user's IP address
> within the "Route Print" statement previously.
>
running SQL 2000 on network "172.16.30.0". I have a remote facility,
"172.16.20.0", that is connected by WAN with a full T1 line. When the remot
e
users login to our SQL application the server creates a routing statement fo
r
each user's machine. If I do a "route print" statement I see the static
routes for the facility, "172.16.20.0 255.255.255.0 172.16.30.11" under both
Active and Persistent routes. I also see the IP addresses of the remote
computers under Active Routes. For some reason the remote users are being
disconnected from the SQL application but their IP addresses still remain
under Active Routes. They cannot log back into the SQL application until I
manually delete their routes from the SQL Server. Once this is done they ca
n
successful log back into the SQL application. Is the server suppose to keep
track of remote machine routing connections? We migrated from Frame Relay t
o
VPN connectivity and I do not recall seing each remote user's IP address
within the "Route Print" statement previously.Not my usual turf, but since no one else responded, I'll put my foot in my
mouth.
I think you have a network issue, not a database issue. It sounds like
youhave a timeout in TCP/IP (or whatever) that is doing the disconnect. I
have not seen SS drop a user or prevent one from logging in (assuming valid
userid, etc), even multiple connections from the same place.
I know there are several places that timeout could be, so someone with more
of a networks background will have to help you point your shotgun.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Bill Freeze" wrote:
> Some background information. I have a Windows 2003 Server that is also
> running SQL 2000 on network "172.16.30.0". I have a remote facility,
> "172.16.20.0", that is connected by WAN with a full T1 line. When the rem
ote
> users login to our SQL application the server creates a routing statement
for
> each user's machine. If I do a "route print" statement I see the static
> routes for the facility, "172.16.20.0 255.255.255.0 172.16.30.11" under bo
th
> Active and Persistent routes. I also see the IP addresses of the remote
> computers under Active Routes. For some reason the remote users are being
> disconnected from the SQL application but their IP addresses still remain
> under Active Routes. They cannot log back into the SQL application until
I
> manually delete their routes from the SQL Server. Once this is done they
can
> successful log back into the SQL application. Is the server suppose to ke
ep
> track of remote machine routing connections? We migrated from Frame Relay
to
> VPN connectivity and I do not recall seing each remote user's IP address
> within the "Route Print" statement previously.
>
Sunday, February 19, 2012
Client Statistics / Network Statistics
I am trying to track down a slowly preforming query and am looking at the client statistics tab; specifically the Network Statistics and Bytes received from server.
I have two servers one running SQL Server 2000 and the other running SQL Server 2005, 64-bit.
The data on the servers is similar, some schema differences, some record differences -- but nearly 32 million records in one table.
The same query is taking longer on the new SQL Server 2005 machine. The main difference that I see on the client statistics tab is the bytes received from server is almost always triple on the new server.
Were there any changes in the information SQL Server 2000 and 2005 reports back? Could the 64-bit be the cause for the increase (might make sense if it were double, but not triple)?
Any other ways I can eliminate this metric from being the cause of the slow running queries?
Thanks, Richard
If I run a simple SELECT * FROM Orders against the Northwind database on SQL Server 2000 with Query Analyzer or on SQL Server 2005 (32-bit) with Management Studio I get 830 rows on each, and 157,065 bytes on 2000 vs 157,097 on 2005. (Get the Northwind database from here http://msdn2.microsoft.com/en-us/library/ms143221.aspx). Try it on your 64-bit server and see what you get. If you also get 157,097 then maybe you're using different data types in your database on the 64-bit server?|||Hi Richard,
I don't know the answer to the question of client statistics but I would personally check the execution of both of your queries through Profiler. Look at READS and CPU for both versions and see if there is a difference internally to SQL Server before checking to see the results being sent back to the client.
The DURATION counter could be misleading in your situation because the time taken to transfer the data back to the client (and the client to close the resultset) will factor into duration (actually, the DURATION counter is usually misleading due to issues of locking, blocking, client conneciton remaining open, low bandwidth to server, Disk bottlenecks, etc).
In regards to 64-bit showing double the number of characters, everything I've read suggests that 64-bit is the same code base and that your client app should not notice any difference between connecting to a 32-bit SQL Server or a 64-bit SQL Server.
You didn't mention how many rows and columns were returned by each query but if your data and schema is not 100% identical on both servers then I would assume the issue is in the data being returned.
Jared
|||I started with SQL Profiler and saw that the duration for my complex query was almost always slower on my new server. There are definite differences in the execution plans, but the number of rows returned is 23 and the fields requested are identical -- there are differences in the length of some of the varchars, but the actual data returned is identical.
Schema and rows shouldn't be a factor -- indexes and statistics are the same, execution plans are different and the "Bytes received from server" are different. Time statistics are slower.
I'm in the process on loading Northwind on these two servers so that I can compare with the other respondent. However, a DBA in another department ran a similar test against SQL Server 2000, SQL Server 2005, and SQL Server 2005, 64-bit and found that the Bytes received was greater (double to triple) on SQL Server 2005; reguardless of the 64-bit.
I'll run some more tests and post my results -- I just hope that I'm not chasing some difference in the "Client Statistics" tab between 2000 and 2005.
Thanks for your interest, Richard
client server sql
hello,
we are trying to design a client server db, we want to hold the data at the client/station for the whole day to minimize network trafic before transfering it to the server at the end of the day. can you please give us a hint how to design the system.(since this is our first project !)
thanksOriginally posted by yonas g tesfai
hello,
we are trying to design a client server db, we want to hold the data at the client/station for the whole day to minimize network trafic before transfering it to the server at the end of the day. can you please give us a hint how to design the system.(since this is our first project !)
thanks
Can't help much with this, except to say that this is not what the term "client-server" usually means. Client-server is where you have an application on the client working with a database on the server in real time.
Presumably you are going to have to replicate your database design onto every client machine, and then build a batch process to copy the data up to the server at the end of the day. Sounds nasty.
When you say this is your first project, do you mean your first project with this requirement (but you are experienced in "normal" client-server apps), or your first database application project. If the latter, then I would question whether you have simply misunderstood the term client-server and are "barking up the wrong tree" with your approach! It is certainly a goal of client-server to minimise network traffic, but not to eradicate it entirely.|||Is there a way of storing data in a batch during the day and then uploading to the server at the end of the working day. Could this be done using a SQL client server system.|||Originally posted by yonas g tesfai
Is there a way of storing data in a batch during the day and then uploading to the server at the end of the working day. Could this be done using a SQL client server system.
Sure. You just need to have a database on each client, with table structures that mirror the table structures on the server. At the end of the day you would then run a batch process to upload the data from the client to the server (insert new records, update updated records, delete deleted records).
I can imagine lots of issues with this (I have never actually done it). My primary concern would be: is this a multi-user system? If so, how will you handle the fact that users A and B have both inserted a new record with the same PK? Or that A and B both updated the same record with different values? Or that A updated a record and B deleted it? Will there also be a download to the client so it has up to date data for the next day?
On the other hand, if there is only 1 client, or if each client is responsible for a unique subset of records (i.e. users A and B WILL NOT and COULD NOT insert/update/delete the same records ever) then things are not so bad.
Please give more information about your requirements. I would reiterate that this is not standard practice in my experience of database systems. You may well have a valid requirement to do this, or you may just be makng a BIG mistake. At the moment, I don't have enough information to know which!
we are trying to design a client server db, we want to hold the data at the client/station for the whole day to minimize network trafic before transfering it to the server at the end of the day. can you please give us a hint how to design the system.(since this is our first project !)
thanksOriginally posted by yonas g tesfai
hello,
we are trying to design a client server db, we want to hold the data at the client/station for the whole day to minimize network trafic before transfering it to the server at the end of the day. can you please give us a hint how to design the system.(since this is our first project !)
thanks
Can't help much with this, except to say that this is not what the term "client-server" usually means. Client-server is where you have an application on the client working with a database on the server in real time.
Presumably you are going to have to replicate your database design onto every client machine, and then build a batch process to copy the data up to the server at the end of the day. Sounds nasty.
When you say this is your first project, do you mean your first project with this requirement (but you are experienced in "normal" client-server apps), or your first database application project. If the latter, then I would question whether you have simply misunderstood the term client-server and are "barking up the wrong tree" with your approach! It is certainly a goal of client-server to minimise network traffic, but not to eradicate it entirely.|||Is there a way of storing data in a batch during the day and then uploading to the server at the end of the working day. Could this be done using a SQL client server system.|||Originally posted by yonas g tesfai
Is there a way of storing data in a batch during the day and then uploading to the server at the end of the working day. Could this be done using a SQL client server system.
Sure. You just need to have a database on each client, with table structures that mirror the table structures on the server. At the end of the day you would then run a batch process to upload the data from the client to the server (insert new records, update updated records, delete deleted records).
I can imagine lots of issues with this (I have never actually done it). My primary concern would be: is this a multi-user system? If so, how will you handle the fact that users A and B have both inserted a new record with the same PK? Or that A and B both updated the same record with different values? Or that A updated a record and B deleted it? Will there also be a download to the client so it has up to date data for the next day?
On the other hand, if there is only 1 client, or if each client is responsible for a unique subset of records (i.e. users A and B WILL NOT and COULD NOT insert/update/delete the same records ever) then things are not so bad.
Please give more information about your requirements. I would reiterate that this is not standard practice in my experience of database systems. You may well have a valid requirement to do this, or you may just be makng a BIG mistake. At the moment, I don't have enough information to know which!
Thursday, February 16, 2012
Client Network Utility Service Pack
2 Questions:
1) I am getting back into SQL more than before, in my new job. I need to find the service pack installed on the clients who have the Client Network Utility installed. I do not remember how to do this.
2) Running 'Select @.@.VERSION' on the server returns 7.00.1063 but does not indicate the service pack. Does this still mean sp4 has been installed?
I'm trying to ensure we'll all up to date since our dual 700 procs are acting eratic...jumps between 10 - 95% or so.
Server info:
NT Server SP6
SQL 7.0
2GB Ram
2 procs @. 700Mhz
Roughly 80 network connections.
No other programs running on server except Antivirus.
Thank you!Yes - sp4 is installed. Also, antivirus can cause several issues with any application. When you say jumping from 10% to 95%, are you isolating the application that is causing this ?|||Originally posted by rnealejr
Yes - sp4 is installed. Also, antivirus can cause several issues with any application. When you say jumping from 10% to 95%, are you isolating the application that is causing this ?
Thanks for the reply! Based on the Perf Monitor, sqlservr.exe uses up most of the CPU. VNC is what I am using to connect to it to monitor it remotely, which does take up some of the CPU. There are only a couple other programs that will take up 1 or 2 of the CPU, but that doesn't even seem worthy of mentioning.
There is only one database that is about 4.5 GB large. It is essentially an out of the box CRM system database, not custom built.
I have read an online resource for using Performance Monitor which was very helpful, but I haven't been able to determine what exactly SQL is doing to cause it to spike.
There are no jobs running while I am monitoring the performance. Memory doesn't seem to be a problem at all, and my indexes appear to be fine.
Thanks again!|||I would trash vnc and install terminal services (the administrative mode /not application) to remotely connect to your sql server machine. vnc causes cpu spikes. rtvscan is an antivirus application that causes spikes as well. When it hits 95% what applications are consuming the resource (is it only sql) ?
1) I am getting back into SQL more than before, in my new job. I need to find the service pack installed on the clients who have the Client Network Utility installed. I do not remember how to do this.
2) Running 'Select @.@.VERSION' on the server returns 7.00.1063 but does not indicate the service pack. Does this still mean sp4 has been installed?
I'm trying to ensure we'll all up to date since our dual 700 procs are acting eratic...jumps between 10 - 95% or so.
Server info:
NT Server SP6
SQL 7.0
2GB Ram
2 procs @. 700Mhz
Roughly 80 network connections.
No other programs running on server except Antivirus.
Thank you!Yes - sp4 is installed. Also, antivirus can cause several issues with any application. When you say jumping from 10% to 95%, are you isolating the application that is causing this ?|||Originally posted by rnealejr
Yes - sp4 is installed. Also, antivirus can cause several issues with any application. When you say jumping from 10% to 95%, are you isolating the application that is causing this ?
Thanks for the reply! Based on the Perf Monitor, sqlservr.exe uses up most of the CPU. VNC is what I am using to connect to it to monitor it remotely, which does take up some of the CPU. There are only a couple other programs that will take up 1 or 2 of the CPU, but that doesn't even seem worthy of mentioning.
There is only one database that is about 4.5 GB large. It is essentially an out of the box CRM system database, not custom built.
I have read an online resource for using Performance Monitor which was very helpful, but I haven't been able to determine what exactly SQL is doing to cause it to spike.
There are no jobs running while I am monitoring the performance. Memory doesn't seem to be a problem at all, and my indexes appear to be fine.
Thanks again!|||I would trash vnc and install terminal services (the administrative mode /not application) to remotely connect to your sql server machine. vnc causes cpu spikes. rtvscan is an antivirus application that causes spikes as well. When it hits 95% what applications are consuming the resource (is it only sql) ?
Client Network Utility in 2005
Where can I find the "Client Network Utility" in SQL 2005?scott
You need Configuration Tools--SQL Server Surface Area Manager .
"scott" <sbailey@.mileslumber.com> wrote in message
news:OTE$Kj9bGHA.4932@.TK2MSFTNGP03.phx.gbl...
> Where can I find the "Client Network Utility" in SQL 2005?
>|||I think you mean Configuration Tools | SQL Server Configuration Manager
(SQL Native Client Configuration section).
*mike hodgson*
http://sqlnerd.blogspot.com
Uri Dimant wrote:
>scott
>You need Configuration Tools--SQL Server Surface Area Manager .
>
>
>
>"scott" <sbailey@.mileslumber.com> wrote in message
>news:OTE$Kj9bGHA.4932@.TK2MSFTNGP03.phx.gbl...
>
>
>|||Yep, thanks Mike
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:Ojd7AGAcGHA.636@.T
K2MSFTNGP05.phx.gbl...
I think you mean Configuration Tools | SQL Server Configuration Manager (SQL
Native Client Configuration section).
mike hodgson
http://sqlnerd.blogspot.com
Uri Dimant wrote:
scott
You need Configuration Tools--SQL Server Surface Area Manager .
"scott" <sbailey@.mileslumber.com> wrote in message
news:OTE$Kj9bGHA.4932@.TK2MSFTNGP03.phx.gbl...
Where can I find the "Client Network Utility" in SQL 2005?|||thanks for directions.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23bxxyWAcGHA.4892@.TK2
MSFTNGP02.phx.gbl...
Yep, thanks Mike
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:Ojd7AGAcGHA.636@.T
K2MSFTNGP05.phx.gbl...
I think you mean Configuration Tools | SQL Server Configuration Manager (SQL
Native Client Configuration section).
mike hodgson
http://sqlnerd.blogspot.com
Uri Dimant wrote:
scott
You need Configuration Tools--SQL Server Surface Area Manager .
"scott" <sbailey@.mileslumber.com> wrote in message
news:OTE$Kj9bGHA.4932@.TK2MSFTNGP03.phx.gbl...
Where can I find the "Client Network Utility" in SQL 2005?
You need Configuration Tools--SQL Server Surface Area Manager .
"scott" <sbailey@.mileslumber.com> wrote in message
news:OTE$Kj9bGHA.4932@.TK2MSFTNGP03.phx.gbl...
> Where can I find the "Client Network Utility" in SQL 2005?
>|||I think you mean Configuration Tools | SQL Server Configuration Manager
(SQL Native Client Configuration section).
*mike hodgson*
http://sqlnerd.blogspot.com
Uri Dimant wrote:
>scott
>You need Configuration Tools--SQL Server Surface Area Manager .
>
>
>
>"scott" <sbailey@.mileslumber.com> wrote in message
>news:OTE$Kj9bGHA.4932@.TK2MSFTNGP03.phx.gbl...
>
>
>|||Yep, thanks Mike
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:Ojd7AGAcGHA.636@.T
K2MSFTNGP05.phx.gbl...
I think you mean Configuration Tools | SQL Server Configuration Manager (SQL
Native Client Configuration section).
mike hodgson
http://sqlnerd.blogspot.com
Uri Dimant wrote:
scott
You need Configuration Tools--SQL Server Surface Area Manager .
"scott" <sbailey@.mileslumber.com> wrote in message
news:OTE$Kj9bGHA.4932@.TK2MSFTNGP03.phx.gbl...
Where can I find the "Client Network Utility" in SQL 2005?|||thanks for directions.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23bxxyWAcGHA.4892@.TK2
MSFTNGP02.phx.gbl...
Yep, thanks Mike
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:Ojd7AGAcGHA.636@.T
K2MSFTNGP05.phx.gbl...
I think you mean Configuration Tools | SQL Server Configuration Manager (SQL
Native Client Configuration section).
mike hodgson
http://sqlnerd.blogspot.com
Uri Dimant wrote:
scott
You need Configuration Tools--SQL Server Surface Area Manager .
"scott" <sbailey@.mileslumber.com> wrote in message
news:OTE$Kj9bGHA.4932@.TK2MSFTNGP03.phx.gbl...
Where can I find the "Client Network Utility" in SQL 2005?
Client Network Utility cannot create alias for VPN SQL server connection?
I am trying to connect two SQL servers in different non-trusted domains.
SQLServer11 in Domain A and SQLServer22 in domain B. This is through a VPN
connection. Domain A has the VPN server. On SQLServer22 I could ping
SQLServer11.mydomain.local at IP address 192.168.100.50. On SQLServer22 I
use the client network utility to create an alias for SQLServer11 as
follows:
Network Libraries: TCP/IP
Server name: 192.168.100.50
Port No: 1433
Server alias: SQLServer11
Then I registered the new SQLServer11 in enterprise manager. But I keep
getting the error message "SQL server does not exist or access
denied.ConnectionOpen(Connect())" .
Why is this so? And how can I rectify the problem'If you use ping -a <ip address> does it bring back the correct name for the
server? If you ping SQLServer11 does it return the proper ip address? If
you are using NT authentication to register then try SQL authentication..
Verify that it is using port 1433 and that SQL Server is actually listening
on TCP/IP.
Rand
This posting is provided "as is" with no warranties and confers no rights.
SQLServer11 in Domain A and SQLServer22 in domain B. This is through a VPN
connection. Domain A has the VPN server. On SQLServer22 I could ping
SQLServer11.mydomain.local at IP address 192.168.100.50. On SQLServer22 I
use the client network utility to create an alias for SQLServer11 as
follows:
Network Libraries: TCP/IP
Server name: 192.168.100.50
Port No: 1433
Server alias: SQLServer11
Then I registered the new SQLServer11 in enterprise manager. But I keep
getting the error message "SQL server does not exist or access
denied.ConnectionOpen(Connect())" .
Why is this so? And how can I rectify the problem'If you use ping -a <ip address> does it bring back the correct name for the
server? If you ping SQLServer11 does it return the proper ip address? If
you are using NT authentication to register then try SQL authentication..
Verify that it is using port 1433 and that SQL Server is actually listening
on TCP/IP.
Rand
This posting is provided "as is" with no warranties and confers no rights.
client network utility & server network utility - what's the difference?
I'm confused about this. If I need MSDE installed on a server and have 10
workstations accessing it AND I don't want to use port 1433, what's the
process I need to go through to make that work right. I've fumbled around
with it and am not sure I follow the process. It seems logical to me that I
would run the server one on the server, set the port and then run the client
one on the client and set it to match the server but I hvae a feelign that's
not the case. Especially because since I'm not installing MSDE on any of the
client machines there is no network utility installed (AFAIK).
I'm also confused as to how DISABLENETWORKPROTOCOLS fits into this part of
things (if at all).
Thanks,
Keith
Yes, your thought is very logical and workable if the client network utility
is installed on workstations, so that you can have apps on workstation know
whih port is used to communicate with SQL Server. Actually, if you to create
a ODBC DSN to SQL Server, the DSN configuration provides you a dialog to
specify network library to use (TCP, Named Pipe...) and port number (or
dynamically determined).
However, in most cases, you do not need to run the client network utiliy on
workstations. The port number can be specified in the applications'
ConnectionString (if the app is designed well, there should be someway to
configure its connectionString, i.e. the database connection information).
AS for DISABLENETWORKPROTOCOLS, it is an installtion parameter. if you do
not explicitly set it to 0 when you install MSDE, by default, the MSDE is
not accessible from other computer. If you want the MSDE being accessible by
other computer, you either set it to 0 at installation or you can enable
network access after installation.
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:%23phBoIf9GHA.3620@.TK2MSFTNGP04.phx.gbl...
> I'm confused about this. If I need MSDE installed on a server and have 10
> workstations accessing it AND I don't want to use port 1433, what's the
> process I need to go through to make that work right. I've fumbled around
> with it and am not sure I follow the process. It seems logical to me that
> I
> would run the server one on the server, set the port and then run the
> client
> one on the client and set it to match the server but I hvae a feelign
> that's
> not the case. Especially because since I'm not installing MSDE on any of
> the
> client machines there is no network utility installed (AFAIK).
> I'm also confused as to how DISABLENETWORKPROTOCOLS fits into this part of
> things (if at all).
> Thanks,
> Keith
>
|||Just wondering if there's a clear answer to this - if
DISABLENETWORKPROTOCOLS is set to 0 for let's say an initial installation of
MSDE sp3 so that the server is accessible from other workstations, what
happens if I set DISABLENETWORKPROTOCOLS=1 for an upgrade to sp4? Will that
basically disable the networkability for the specified instance of MSDE from
that point forward, or is that only temporary for the installation process?
"Norman Yuan" <NotReal@.NotReal.not> wrote in message
news:%23v3k7Xf9GHA.3264@.TK2MSFTNGP04.phx.gbl...
> Yes, your thought is very logical and workable if the client network
> utility is installed on workstations, so that you can have apps on
> workstation know whih port is used to communicate with SQL Server.
> Actually, if you to create a ODBC DSN to SQL Server, the DSN configuration
> provides you a dialog to specify network library to use (TCP, Named
> Pipe...) and port number (or dynamically determined).
> However, in most cases, you do not need to run the client network utiliy
> on workstations. The port number can be specified in the applications'
> ConnectionString (if the app is designed well, there should be someway to
> configure its connectionString, i.e. the database connection information).
> AS for DISABLENETWORKPROTOCOLS, it is an installtion parameter. if you do
> not explicitly set it to 0 when you install MSDE, by default, the MSDE is
> not accessible from other computer. If you want the MSDE being accessible
> by other computer, you either set it to 0 at installation or you can
> enable network access after installation.
>
> "Keith G Hicks" <krh@.comcast.net> wrote in message
> news:%23phBoIf9GHA.3620@.TK2MSFTNGP04.phx.gbl...
>
|||hi Bill,
Bill Hicks wrote:
> Just wondering if there's a clear answer to this - if
> DISABLENETWORKPROTOCOLS is set to 0 for let's say an initial
> installation of MSDE sp3 so that the server is accessible from other
> workstations, what happens if I set DISABLENETWORKPROTOCOLS=1 for an
> upgrade to sp4? Will that basically disable the networkability for
> the specified instance of MSDE from that point forward, or is that
> only temporary for the installation process?
as per
http://msdn.microsoft.com/library/de...tsql_84xl.asp,
DISABLENETWORKPROTOCOLS=n
In SQL Server 2000 SP3 or later, specifies how the Installer configures the
network protocol support for the instance of the Desktop Engine being
installed or upgraded. n is an integer number, and should be set to either 0
or 1.
These are the behaviors of DISABLNETWORKPROTOCOLS in SP3a or later:
Value Specified for n Installing New Instance
1:
Upgrading Existing Instance: Instance is configured with all server
Net-Libraries disabled.
Installing New Instance: Instance is configured with all server
Net-Libraries disabled.
0:
Upgrading Existing Instance: The existing server Net-Library configuration
is retained.
Installing New Instance: Instance is configured with default server
Net-Libraries and addresses enabled.
Parameter not specified, or is any value other than 0 or 1:
Upgrading Existing Instance: The existing server Net-Library configuration
is retained.
Installing New Instance: Instance is configured with all server
Net-Libraries disabled.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.bizhttp://italy.mvps.org
DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
-- remove DMO to reply
workstations accessing it AND I don't want to use port 1433, what's the
process I need to go through to make that work right. I've fumbled around
with it and am not sure I follow the process. It seems logical to me that I
would run the server one on the server, set the port and then run the client
one on the client and set it to match the server but I hvae a feelign that's
not the case. Especially because since I'm not installing MSDE on any of the
client machines there is no network utility installed (AFAIK).
I'm also confused as to how DISABLENETWORKPROTOCOLS fits into this part of
things (if at all).
Thanks,
Keith
Yes, your thought is very logical and workable if the client network utility
is installed on workstations, so that you can have apps on workstation know
whih port is used to communicate with SQL Server. Actually, if you to create
a ODBC DSN to SQL Server, the DSN configuration provides you a dialog to
specify network library to use (TCP, Named Pipe...) and port number (or
dynamically determined).
However, in most cases, you do not need to run the client network utiliy on
workstations. The port number can be specified in the applications'
ConnectionString (if the app is designed well, there should be someway to
configure its connectionString, i.e. the database connection information).
AS for DISABLENETWORKPROTOCOLS, it is an installtion parameter. if you do
not explicitly set it to 0 when you install MSDE, by default, the MSDE is
not accessible from other computer. If you want the MSDE being accessible by
other computer, you either set it to 0 at installation or you can enable
network access after installation.
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:%23phBoIf9GHA.3620@.TK2MSFTNGP04.phx.gbl...
> I'm confused about this. If I need MSDE installed on a server and have 10
> workstations accessing it AND I don't want to use port 1433, what's the
> process I need to go through to make that work right. I've fumbled around
> with it and am not sure I follow the process. It seems logical to me that
> I
> would run the server one on the server, set the port and then run the
> client
> one on the client and set it to match the server but I hvae a feelign
> that's
> not the case. Especially because since I'm not installing MSDE on any of
> the
> client machines there is no network utility installed (AFAIK).
> I'm also confused as to how DISABLENETWORKPROTOCOLS fits into this part of
> things (if at all).
> Thanks,
> Keith
>
|||Just wondering if there's a clear answer to this - if
DISABLENETWORKPROTOCOLS is set to 0 for let's say an initial installation of
MSDE sp3 so that the server is accessible from other workstations, what
happens if I set DISABLENETWORKPROTOCOLS=1 for an upgrade to sp4? Will that
basically disable the networkability for the specified instance of MSDE from
that point forward, or is that only temporary for the installation process?
"Norman Yuan" <NotReal@.NotReal.not> wrote in message
news:%23v3k7Xf9GHA.3264@.TK2MSFTNGP04.phx.gbl...
> Yes, your thought is very logical and workable if the client network
> utility is installed on workstations, so that you can have apps on
> workstation know whih port is used to communicate with SQL Server.
> Actually, if you to create a ODBC DSN to SQL Server, the DSN configuration
> provides you a dialog to specify network library to use (TCP, Named
> Pipe...) and port number (or dynamically determined).
> However, in most cases, you do not need to run the client network utiliy
> on workstations. The port number can be specified in the applications'
> ConnectionString (if the app is designed well, there should be someway to
> configure its connectionString, i.e. the database connection information).
> AS for DISABLENETWORKPROTOCOLS, it is an installtion parameter. if you do
> not explicitly set it to 0 when you install MSDE, by default, the MSDE is
> not accessible from other computer. If you want the MSDE being accessible
> by other computer, you either set it to 0 at installation or you can
> enable network access after installation.
>
> "Keith G Hicks" <krh@.comcast.net> wrote in message
> news:%23phBoIf9GHA.3620@.TK2MSFTNGP04.phx.gbl...
>
|||hi Bill,
Bill Hicks wrote:
> Just wondering if there's a clear answer to this - if
> DISABLENETWORKPROTOCOLS is set to 0 for let's say an initial
> installation of MSDE sp3 so that the server is accessible from other
> workstations, what happens if I set DISABLENETWORKPROTOCOLS=1 for an
> upgrade to sp4? Will that basically disable the networkability for
> the specified instance of MSDE from that point forward, or is that
> only temporary for the installation process?
as per
http://msdn.microsoft.com/library/de...tsql_84xl.asp,
DISABLENETWORKPROTOCOLS=n
In SQL Server 2000 SP3 or later, specifies how the Installer configures the
network protocol support for the instance of the Desktop Engine being
installed or upgraded. n is an integer number, and should be set to either 0
or 1.
These are the behaviors of DISABLNETWORKPROTOCOLS in SP3a or later:
Value Specified for n Installing New Instance
1:
Upgrading Existing Instance: Instance is configured with all server
Net-Libraries disabled.
Installing New Instance: Instance is configured with all server
Net-Libraries disabled.
0:
Upgrading Existing Instance: The existing server Net-Library configuration
is retained.
Installing New Instance: Instance is configured with default server
Net-Libraries and addresses enabled.
Parameter not specified, or is any value other than 0 or 1:
Upgrading Existing Instance: The existing server Net-Library configuration
is retained.
Installing New Instance: Instance is configured with all server
Net-Libraries disabled.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.bizhttp://italy.mvps.org
DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
-- remove DMO to reply
Client Network Utility - tcp/ip
what is the syntax the server alias and the server name?
Is this correct: server alias should be the
"computer-name\sql-instance-name"? Will it work against a default instance?
server name should be the "ip-address\instance name"?
You can use anything for a server alias. If you want to hide that fact that
an instance is an instance, you can just use a single word as the alias. Or
you could mask a default instance as a named instance. The server name can
be either the network (computer) name or the IP-address if you are using
TCP/IP, with the instance name added if necessary.
Jacco Schalkwijk
SQL Server MVP
"Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
news:OeOF%23rPVFHA.3024@.TK2MSFTNGP14.phx.gbl...
> what is the syntax the server alias and the server name?
> Is this correct: server alias should be the
> "computer-name\sql-instance-name"? Will it work against a default
> instance?
> server name should be the "ip-address\instance
> name"?
>
Is this correct: server alias should be the
"computer-name\sql-instance-name"? Will it work against a default instance?
server name should be the "ip-address\instance name"?
You can use anything for a server alias. If you want to hide that fact that
an instance is an instance, you can just use a single word as the alias. Or
you could mask a default instance as a named instance. The server name can
be either the network (computer) name or the IP-address if you are using
TCP/IP, with the instance name added if necessary.
Jacco Schalkwijk
SQL Server MVP
"Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
news:OeOF%23rPVFHA.3024@.TK2MSFTNGP14.phx.gbl...
> what is the syntax the server alias and the server name?
> Is this correct: server alias should be the
> "computer-name\sql-instance-name"? Will it work against a default
> instance?
> server name should be the "ip-address\instance
> name"?
>
Client Network Utility - tcp/ip
what is the syntax the server alias and the server name?
Is this correct: server alias should be the
"computer-name\sql-instance-name"? Will it work against a default instance?
server name should be the "ip-address\instance name"?You can use anything for a server alias. If you want to hide that fact that
an instance is an instance, you can just use a single word as the alias. Or
you could mask a default instance as a named instance. The server name can
be either the network (computer) name or the IP-address if you are using
TCP/IP, with the instance name added if necessary.
Jacco Schalkwijk
SQL Server MVP
"Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
news:OeOF%23rPVFHA.3024@.TK2MSFTNGP14.phx.gbl...
> what is the syntax the server alias and the server name?
> Is this correct: server alias should be the
> "computer-name\sql-instance-name"? Will it work against a default
> instance?
> server name should be the "ip-address\instance
> name"?
>
Is this correct: server alias should be the
"computer-name\sql-instance-name"? Will it work against a default instance?
server name should be the "ip-address\instance name"?You can use anything for a server alias. If you want to hide that fact that
an instance is an instance, you can just use a single word as the alias. Or
you could mask a default instance as a named instance. The server name can
be either the network (computer) name or the IP-address if you are using
TCP/IP, with the instance name added if necessary.
Jacco Schalkwijk
SQL Server MVP
"Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
news:OeOF%23rPVFHA.3024@.TK2MSFTNGP14.phx.gbl...
> what is the syntax the server alias and the server name?
> Is this correct: server alias should be the
> "computer-name\sql-instance-name"? Will it work against a default
> instance?
> server name should be the "ip-address\instance
> name"?
>
Subscribe to:
Posts (Atom)