can not access clustering SQL Server after relocation

Hi all:

I have a clustering SQL Server on Node1 and Node2, the Node1 has named
Instance1 and Node2 has named Instance2, no default instance. We
tested it that everthing is OK, then we decide to move to DR location.

The relocation kept the same virtual and phusical server name, and we
did not change SQL Server server network utility. But server IP
addresses were changed(vitual and physical). We can start the
clustering SQL Server as before, and everything looks like well.

However the users can not access it remotely, and they did not change
the client network utility. But if I login the server,I can access it
from Query Analyzer even though ther is not client network utility
setup on the server. I can ping the vitual server name, how can I make
sure the ports are working well? (We did not use the default port for
named instance).

I remember I did input the virtual server IP address when I installed
the clustering SQL Server. Of course, it was change after relocation.
Is it the reason for failed remote login? If it is, how to fix it?

Does anyboday know there is document for clustering SQL Server
relocation?

Thanks
Williew2jin@.hotmail.com (willie) wrote in message news:<6610106b.0408251200.14624760@.posting.google.com>...
> Hi all:
> I have a clustering SQL Server on Node1 and Node2, the Node1 has named
> Instance1 and Node2 has named Instance2, no default instance. We
> tested it that everthing is OK, then we decide to move to DR location.
> The relocation kept the same virtual and phusical server name, and we
> did not change SQL Server server network utility. But server IP
> addresses were changed(vitual and physical). We can start the
> clustering SQL Server as before, and everything looks like well.
> However the users can not access it remotely, and they did not change
> the client network utility. But if I login the server,I can access it
> from Query Analyzer even though ther is not client network utility
> setup on the server. I can ping the vitual server name, how can I make
> sure the ports are working well? (We did not use the default port for
> named instance).
> I remember I did input the virtual server IP address when I installed
> the clustering SQL Server. Of course, it was change after relocation.
> Is it the reason for failed remote login? If it is, how to fix it?
> Does anyboday know there is document for clustering SQL Server
> relocation?
> Thanks
> Willie

This article might help:

http://support.microsoft.com/defaul...8&Product=sql2k

Simon

can not access clustering SQL Server after relocation

Hi all:
I have a clustering SQL Server on Node1 and Node2, the Node1 has named
Instance1 and Node2 has named Instance2, no default instance. We
tested it that everthing is OK, then we decide to move to DR location.
The relocation kept the same virtual and phusical server name, and we
did not change SQL Server server network utility. But server IP
addresses were changed(vitual and physical). We can start the
clustering SQL Server as before, and everything looks like well.
However the users can not access it remotely, and they did not change
the client network utility. But if I login the server,I can access it
from Query Analyzer even though ther is not client network utility
setup on the server. I can ping the vitual server name, how can I make
sure the ports are working well? (We did not use the default port for
named instance).
I remember I did input the virtual server IP address when I installed
the clustering SQL Server. Of course, it was change after relocation.
Is it the reason for failed remote login? If it is, how to fix it?
Does anyboday know there is document for clustering SQL Server
relocation?
Thanks
Williew2jin@.hotmail.com (willie) wrote in message news:<6610106b.0408251200.14624760@.posting.googl
e.com>...
> Hi all:
> I have a clustering SQL Server on Node1 and Node2, the Node1 has named
> Instance1 and Node2 has named Instance2, no default instance. We
> tested it that everthing is OK, then we decide to move to DR location.
> The relocation kept the same virtual and phusical server name, and we
> did not change SQL Server server network utility. But server IP
> addresses were changed(vitual and physical). We can start the
> clustering SQL Server as before, and everything looks like well.
> However the users can not access it remotely, and they did not change
> the client network utility. But if I login the server,I can access it
> from Query Analyzer even though ther is not client network utility
> setup on the server. I can ping the vitual server name, how can I make
> sure the ports are working well? (We did not use the default port for
> named instance).
> I remember I did input the virtual server IP address when I installed
> the clustering SQL Server. Of course, it was change after relocation.
> Is it the reason for failed remote login? If it is, how to fix it?
> Does anyboday know there is document for clustering SQL Server
> relocation?
> Thanks
> Willie
This article might help:
http://support.microsoft.com/defaul...8&Product=sql2k
Simon

can not access clustering SQL Server after relocation

Hi all:
I have a clustering SQL Server on Node1 and Node2, the Node1 has named
Instance1 and Node2 has named Instance2, no default instance. We
tested it that everthing is OK, then we decide to move to DR location.
The relocation kept the same virtual and phusical server name, and we
did not change SQL Server server network utility. But server IP
addresses were changed(vitual and physical). We can start the
clustering SQL Server as before, and everything looks like well.
However the users can not access it remotely, and they did not change
the client network utility. But if I login the server,I can access it
from Query Analyzer even though ther is not client network utility
setup on the server. I can ping the vitual server name, how can I make
sure the ports are working well? (We did not use the default port for
named instance).
I remember I did input the virtual server IP address when I installed
the clustering SQL Server. Of course, it was change after relocation.
Is it the reason for failed remote login? If it is, how to fix it?
Does anyboday know there is document for clustering SQL Server
relocation?
Thanks
Williew2jin@.hotmail.com (willie) wrote in message news:<6610106b.0408251200.14624760@.posting.google.com>...
> Hi all:
> I have a clustering SQL Server on Node1 and Node2, the Node1 has named
> Instance1 and Node2 has named Instance2, no default instance. We
> tested it that everthing is OK, then we decide to move to DR location.
> The relocation kept the same virtual and phusical server name, and we
> did not change SQL Server server network utility. But server IP
> addresses were changed(vitual and physical). We can start the
> clustering SQL Server as before, and everything looks like well.
> However the users can not access it remotely, and they did not change
> the client network utility. But if I login the server,I can access it
> from Query Analyzer even though ther is not client network utility
> setup on the server. I can ping the vitual server name, how can I make
> sure the ports are working well? (We did not use the default port for
> named instance).
> I remember I did input the virtual server IP address when I installed
> the clustering SQL Server. Of course, it was change after relocation.
> Is it the reason for failed remote login? If it is, how to fix it?
> Does anyboday know there is document for clustering SQL Server
> relocation?
> Thanks
> Willie
This article might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;319578&Product=sql2k
Simonsql

can not access clustering SQL Server after relocation

Hi all:
I have a clustering SQL Server on Node1 and Node2, the Node1 has named
Instance1 and Node2 has named Instance2, no default instance. We
tested it that everthing is OK, then we decide to move to DR location.
The relocation kept the same virtual and phusical server name, and we
did not change SQL Server server network utility. But server IP
addresses were changed(vitual and physical). We can start the
clustering SQL Server as before, and everything looks like well.
However the users can not access it remotely, and they did not change
the client network utility. But if I login the server,I can access it
from Query Analyzer even though ther is not client network utility
setup on the server. I can ping the vitual server name, how can I make
sure the ports are working well? (We did not use the default port for
named instance).
I remember I did input the virtual server IP address when I installed
the clustering SQL Server. Of course, it was change after relocation.
Is it the reason for failed remote login? If it is, how to fix it?
Does anyboday know there is document for clustering SQL Server
relocation?
Thanks
Willie
w2jin@.hotmail.com (willie) wrote in message news:<6610106b.0408251200.14624760@.posting.google. com>...
> Hi all:
> I have a clustering SQL Server on Node1 and Node2, the Node1 has named
> Instance1 and Node2 has named Instance2, no default instance. We
> tested it that everthing is OK, then we decide to move to DR location.
> The relocation kept the same virtual and phusical server name, and we
> did not change SQL Server server network utility. But server IP
> addresses were changed(vitual and physical). We can start the
> clustering SQL Server as before, and everything looks like well.
> However the users can not access it remotely, and they did not change
> the client network utility. But if I login the server,I can access it
> from Query Analyzer even though ther is not client network utility
> setup on the server. I can ping the vitual server name, how can I make
> sure the ports are working well? (We did not use the default port for
> named instance).
> I remember I did input the virtual server IP address when I installed
> the clustering SQL Server. Of course, it was change after relocation.
> Is it the reason for failed remote login? If it is, how to fix it?
> Does anyboday know there is document for clustering SQL Server
> relocation?
> Thanks
> Willie
This article might help:
http://support.microsoft.com/default...&Product=sql2k
Simon

can not access clustering SQL Server after relocation

Hi all:
I have a clustering SQL Server on Node1 and Node2, the Node1 has named
Instance1 and Node2 has named Instance2, no default instance. We
tested it that everthing is OK, then we decide to move to DR location.
The relocation kept the same virtual and phusical server name, and we
did not change SQL Server server network utility. But server IP
addresses were changed(vitual and physical). We can start the
clustering SQL Server as before, and everything looks like well.
However the users can not access it remotely, and they did not change
the client network utility. But if I login the server,I can access it
from Query Analyzer even though ther is not client network utility
setup on the server. I can ping the vitual server name, how can I make
sure the ports are working well? (We did not use the default port for
named instance).
I remember I did input the virtual server IP address when I installed
the clustering SQL Server. Of course, it was change after relocation.
Is it the reason for failed remote login? If it is, how to fix it?
Does anyboday know there is document for clustering SQL Server
relocation?
Thanks
Willie
w2jin@.hotmail.com (willie) wrote in message news:<6610106b.0408251200.14624760@.posting.google. com>...
> Hi all:
> I have a clustering SQL Server on Node1 and Node2, the Node1 has named
> Instance1 and Node2 has named Instance2, no default instance. We
> tested it that everthing is OK, then we decide to move to DR location.
> The relocation kept the same virtual and phusical server name, and we
> did not change SQL Server server network utility. But server IP
> addresses were changed(vitual and physical). We can start the
> clustering SQL Server as before, and everything looks like well.
> However the users can not access it remotely, and they did not change
> the client network utility. But if I login the server,I can access it
> from Query Analyzer even though ther is not client network utility
> setup on the server. I can ping the vitual server name, how can I make
> sure the ports are working well? (We did not use the default port for
> named instance).
> I remember I did input the virtual server IP address when I installed
> the clustering SQL Server. Of course, it was change after relocation.
> Is it the reason for failed remote login? If it is, how to fix it?
> Does anyboday know there is document for clustering SQL Server
> relocation?
> Thanks
> Willie
This article might help:
http://support.microsoft.com/default...&Product=sql2k
Simon

Can I: remove clustered constraint, but keep clustering?

All,
In a database accessed 24/7, I have a table a few GB in size that is
currently clustered on a UNIQUE constraint. The data rules have changed,
and the key combination is no longer going to be unique. However, it is
still desirable to cluster on that set of columns.
On a test database, when I do
ALTER TABLE foo
DROP CONSTRAINT bar
CREATE CLUSTERED INDEX bar
ON foo (zip, zap)
it takes lots of time (over an hour). I believe this is because the DROP
CONSTRAINT removes clustering, so SQL Server re-organizes the table as a
heap, then the CREATE INDEX restores clustering, so SQL Server re-organizes
the table according to the clustered index. This is less efficient than I
would prefer, and I do not want to take the database off-line for an hour.
Is there some SQL Server trick I do not know about? Can I leave the
constraint, but somehow disable checking? Can I safely edit the system
tables? I suspect the latter is possible, but I would not want to proceed
without expert advice.
TIA,
Scott NicholAll,
I have found info about DROP_EXISTING at, e.g.,
http://www.winnetmag.com/SQLServer/Article/ArticleID/40405/40405.html, but
this does not apply since I want to go from a constraint to an index, right?
--
Scott Nichol
"Scott Nichol" <reply_to_newsgroup@.scottnichol.com> wrote in message
news:uF4D7C8TEHA.204@.TK2MSFTNGP10.phx.gbl...
> All,
> In a database accessed 24/7, I have a table a few GB in size that is
> currently clustered on a UNIQUE constraint. The data rules have changed,
> and the key combination is no longer going to be unique. However, it is
> still desirable to cluster on that set of columns.
> On a test database, when I do
> ALTER TABLE foo
> DROP CONSTRAINT bar
> CREATE CLUSTERED INDEX bar
> ON foo (zip, zap)
> it takes lots of time (over an hour). I believe this is because the DROP
> CONSTRAINT removes clustering, so SQL Server re-organizes the table as a
> heap, then the CREATE INDEX restores clustering, so SQL Server
re-organizes
> the table according to the clustered index. This is less efficient than I
> would prefer, and I do not want to take the database off-line for an hour.
> Is there some SQL Server trick I do not know about? Can I leave the
> constraint, but somehow disable checking? Can I safely edit the system
> tables? I suspect the latter is possible, but I would not want to proceed
> without expert advice.
> TIA,
> Scott Nichol
>|||The answer is no you cannot disabled the constraint nor can you modify the system table safely to handle the change
As for what is happening, when the clustered index is dropped all non-clustered indexes are changed to having row poitners to the data instead of using the clustered index as the access path
All of them are rebuilt as soon as the clustered index is dropped
When you then recreate the clustered index the whole non-clustered thing happens again switch back from the row pointers to the data identifiers to use accessing the clustered index
I suggest script the drop and add of all your non-clustered indexes, drop them first, then drop and add your clustered, then readd your non-clustered. May still take a while thou so you will have to do an outage time frame.

Can I: remove clustered constraint, but keep clustering?

All,
In a database accessed 24/7, I have a table a few GB in size that is
currently clustered on a UNIQUE constraint. The data rules have changed,
and the key combination is no longer going to be unique. However, it is
still desirable to cluster on that set of columns.
On a test database, when I do
ALTER TABLE foo
DROP CONSTRAINT bar
CREATE CLUSTERED INDEX bar
ON foo (zip, zap)
it takes lots of time (over an hour). I believe this is because the DROP
CONSTRAINT removes clustering, so SQL Server re-organizes the table as a
heap, then the CREATE INDEX restores clustering, so SQL Server re-organizes
the table according to the clustered index. This is less efficient than I
would prefer, and I do not want to take the database off-line for an hour.
Is there some SQL Server trick I do not know about? Can I leave the
constraint, but somehow disable checking? Can I safely edit the system
tables? I suspect the latter is possible, but I would not want to proceed
without expert advice.
TIA,
Scott Nichol
All,
I have found info about DROP_EXISTING at, e.g.,
http://www.winnetmag.com/SQLServer/A...05/40405.html, but
this does not apply since I want to go from a constraint to an index, right?
Scott Nichol
"Scott Nichol" <reply_to_newsgroup@.scottnichol.com> wrote in message
news:uF4D7C8TEHA.204@.TK2MSFTNGP10.phx.gbl...
> All,
> In a database accessed 24/7, I have a table a few GB in size that is
> currently clustered on a UNIQUE constraint. The data rules have changed,
> and the key combination is no longer going to be unique. However, it is
> still desirable to cluster on that set of columns.
> On a test database, when I do
> ALTER TABLE foo
> DROP CONSTRAINT bar
> CREATE CLUSTERED INDEX bar
> ON foo (zip, zap)
> it takes lots of time (over an hour). I believe this is because the DROP
> CONSTRAINT removes clustering, so SQL Server re-organizes the table as a
> heap, then the CREATE INDEX restores clustering, so SQL Server
re-organizes
> the table according to the clustered index. This is less efficient than I
> would prefer, and I do not want to take the database off-line for an hour.
> Is there some SQL Server trick I do not know about? Can I leave the
> constraint, but somehow disable checking? Can I safely edit the system
> tables? I suspect the latter is possible, but I would not want to proceed
> without expert advice.
> TIA,
> Scott Nichol
>
|||The answer is no you cannot disabled the constraint nor can you modify the system table safely to handle the change.
As for what is happening, when the clustered index is dropped all non-clustered indexes are changed to having row poitners to the data instead of using the clustered index as the access path.
All of them are rebuilt as soon as the clustered index is dropped.
When you then recreate the clustered index the whole non-clustered thing happens again switch back from the row pointers to the data identifiers to use accessing the clustered index.
I suggest script the drop and add of all your non-clustered indexes, drop them first, then drop and add your clustered, then readd your non-clustered. May still take a while thou so you will have to do an outage time frame.

Can I: remove clustered constraint, but keep clustering?

All,
In a database accessed 24/7, I have a table a few GB in size that is
currently clustered on a UNIQUE constraint. The data rules have changed,
and the key combination is no longer going to be unique. However, it is
still desirable to cluster on that set of columns.
On a test database, when I do
ALTER TABLE foo
DROP CONSTRAINT bar
CREATE CLUSTERED INDEX bar
ON foo (zip, zap)
it takes lots of time (over an hour). I believe this is because the DROP
CONSTRAINT removes clustering, so SQL Server re-organizes the table as a
heap, then the CREATE INDEX restores clustering, so SQL Server re-organizes
the table according to the clustered index. This is less efficient than I
would prefer, and I do not want to take the database off-line for an hour.
Is there some SQL Server trick I do not know about? Can I leave the
constraint, but somehow disable checking? Can I safely edit the system
tables? I suspect the latter is possible, but I would not want to proceed
without expert advice.
TIA,
Scott NicholAll,
I have found info about DROP_EXISTING at, e.g.,
http://www.winnetmag.com/SQLServer/...405/40405.html, but
this does not apply since I want to go from a constraint to an index, right?
Scott Nichol
"Scott Nichol" <reply_to_newsgroup@.scottnichol.com> wrote in message
news:uF4D7C8TEHA.204@.TK2MSFTNGP10.phx.gbl...
> All,
> In a database accessed 24/7, I have a table a few GB in size that is
> currently clustered on a UNIQUE constraint. The data rules have changed,
> and the key combination is no longer going to be unique. However, it is
> still desirable to cluster on that set of columns.
> On a test database, when I do
> ALTER TABLE foo
> DROP CONSTRAINT bar
> CREATE CLUSTERED INDEX bar
> ON foo (zip, zap)
> it takes lots of time (over an hour). I believe this is because the DROP
> CONSTRAINT removes clustering, so SQL Server re-organizes the table as a
> heap, then the CREATE INDEX restores clustering, so SQL Server
re-organizes
> the table according to the clustered index. This is less efficient than I
> would prefer, and I do not want to take the database off-line for an hour.
> Is there some SQL Server trick I do not know about? Can I leave the
> constraint, but somehow disable checking? Can I safely edit the system
> tables? I suspect the latter is possible, but I would not want to proceed
> without expert advice.
> TIA,
> Scott Nichol
>|||The answer is no you cannot disabled the constraint nor can you modify the s
ystem table safely to handle the change.
As for what is happening, when the clustered index is dropped all non-cluste
red indexes are changed to having row poitners to the data instead of using
the clustered index as the access path.
All of them are rebuilt as soon as the clustered index is dropped.
When you then recreate the clustered index the whole non-clustered thing hap
pens again switch back from the row pointers to the data identifiers to use
accessing the clustered index.
I suggest script the drop and add of all your non-clustered indexes, drop th
em first, then drop and add your clustered, then readd your non-clustered. M
ay still take a while thou so you will have to do an outage time frame.