Software

Ask something
Discussion Locked

Migrating SQL 2000 Server

0 helpful
14 replies

by men

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
SQL 2000 server was initially installed on a cluster, with 2 boxes CSGSQL1 and CSGSQL2 (setup active/passive). The software was installed on SQL1, and the SQL box was our failover. SQL1 has been evicted from the cluster, and the hard drive crashedbefore the settings could be copied over to SQL2. Now we have SQL2 (No longer running the cluster service - acting as a stand-alone box) running, but the master databse only wants to start on the non-existant cluster. Is there any way to install anew instance and migrate everything including the master database's user securities from the old SQL database. We have succesfully attatched the other databases to a new instance, but manually entering in the user information and securities of the old master database is not an option. If more information is needed I will gladly provide it. Thank you in advance if you can help out with any information. I would offer more points if I had them.

14 replies

by maxwell edison

The following is from Microsoft article:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_clustering_2hpu.asp

(REMOVE SPACES from the pasted URL.)

Usually, you run a clustered instance of SQL Server under the control of Microsoft Cluster Service (MSCS). However, it may be necessary to run a clustered instance of SQL Server as a stand-alone instance (for example, when you want to perform administrative operations like running an instance of SQL Server in single-user mode). To connect to a clustered instance of SQL Server 2000 in stand-alone mode using sockets, both the IP address and network name resources must be online for the virtual server on which the instance was installed.

If these resources cannot be online, connect using Named Pipes. However, you must create an alias on the client side to talk to the pipe name on which the instance of SQL Server is listening. Use SQL Server Network Utility to find out the pipe name.

For more information, see Failover Cluster Troubleshooting:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_clustering_87vr.asp

(continued in comments..)

by maxwell edison

.
.
SQL Server 2000 cannot log on to the network after it migrates to another node:

SQL Server service account passwords must be identical on all nodes or else the node cannot restart a SQL Server service that has migrated from a failed node.

If you change the SQL Server service account passwords on one node, you must change the passwords on all other nodes. However, if you change the account using SQL Server Enterprise Manager, this task will be done automatically.

SQL Server cannot access the cluster disks.
A node cannot recover cluster disks that have migrated from a failed node if the shared cluster disks use a different letter drive. The disk drive letters for the cluster disks must be the same on both servers. If they are not, review your original installation of the operating system and Microsoft Cluster Service (MSCS). For more information, see the Microsoft Windows NT? 4.0, Enterprise Edition, Windows? 2000 Advanced Server, or Windows 2000 Datacenter Server documentation.

If the Network Name is offline and you cannot connect using TCP/IP, you must use Named Pipes:

To connect using Named Pipes, create an alias using the Client Network Utility to connect to the appropriate computer. For example, if youhave a cluster with two nodes (Node A and Node B), and a virtual server (Virtsql) with a default instance, you can connect to the server that has the Network Name resource offline by doing the following:

by maxwell edison

.
.
Determine on which node the group containing the instance of SQL Server is running by using the Cluster Administrator. For this example, it will be Node A.

Start the SQL Server service on that computer using net start. For more information about using net start, see Starting SQL Server Manually.

Start the SQL Server Network Utility on Node A. View the pipe name on which the server is listening. It should be similar to \\.\$$\VIRTSQL\pipe\sql\query.

On the client computer, start the Client Network Utility.

Create an alias SQLTEST1 to connect via Named Pipes to this pipe name. To do this, put Node A as the server name and edit the pipe to be \\.\pipe\$$\VIRTSQL\sql\query. Connect to this instance using the alias SQLTEST1 asthe server name.

by maxwell edison

.
.
To remove a failover clustered instance:

On the Welcome screen of the Microsoft SQL Server Installation Wizard, click Next.

On the Computer Name screen, click Virtual Server and specify the name of the server from which to remove a clustered instance. Click Next.

On the Installation Selection screen, click Upgrade, remove, or add components to an existing instance of SQL Server.

On the Instance Name screen, for a default instance, click Default. For a named instance, specify the name of the instance to remove. Click Next.

On the Existing Installation screen, click Uninstall your existing installation. Click Next.

On the Remote Information screen, specify the password that is a valid administrator password on all nodes in the cluster. Click Next.

In the Setup message "Successfully uninstalled the instance . . . ", click OK.

On the Setup Complete screen, click Finish.
If you are instructed to restart the computer, do so now. It is important to read the message from SQL Server Setup when you are done with installation. Failure to restart any of the specified nodes may cause failures when you run the Setup program in the future on any node in the failover cluster.

by maxwell edison

.
.
To recover from failover cluster failure in Scenario 2:

In Scenario 2, failure is caused by Node 1 being down or offline but not irretrievably broken. This could be caused, for example, by an operating system failure.

After Node 1 fails, the Microsoft? SQL Server™ 2000 failover cluster fails over to Node 2.

Run SQL Server Setup and remove Node 1. For more information, see How to remove a failover clustered instance.

Resolve the problem with Node 1.

Ensure that the Microsoft Cluster Service (MSCS) cluster is working and all nodes are online.

Run the Setup program on Node 2 and add Node 1 back to the failover cluster. For more information, see How to add nodes to an existing virtual server (Setup).

by men

I'm sorry if I wasn't clear, I don't want to go back to using the cluster. I want to leave it on the SQL2 as a stand-alone box. We are currently creating a brand new box to be a back-up(in case SQL2 goes down too), and I will need to migrate everything to that one too. Both of them will be self-contained. No clustering, they will just be stand-alone SQL server boxes.

by timthetoolman

Hi,

Well if you dont get an solution from Maxwell's extended answer....

You always have the option of taking your most recent system disk backup from SQL1 and restoring it to SQL2 - to make SQL2, in effect, SQL1 again.

Cheers,
Tim.

This discussion is closed to new replies.