Moving Databases Between SQL Server 2000 Editions
I used SQL Server 2000 Personal Edition to create a database. Can I easily move this database to a new SQL Server machine that's running SQL Server 2000 Enterprise Edition? Can I also go from Enterprise Edition to Personal Edition?
Several important feature differences exist between the different SQL Server 2000 editions, but the underlying data structures in the database are the same. You can easily move databases between the different editions by using either basic backup and restore techniques or the sp_detach_db and sp_attach_db stored procedures. In fact, you can even move a SQL Server 7.0 database to SQL Server 2000 (although this move is a one-way process—you can't go from SQL Server 2000 to SQL Server 7.0).
Because the various SQL Server 2000 editions have different feature sets, queries might behave differently while running on a particular edition. The "Features Supported by the Editions of SQL Server 2000" topic in SQL Server 2000 Books Online (BOL) provides a helpful discussion of what feature differences you'll find.
Connecting to a SQL Server Instance Running Outside a Firewall
I'm having a hard time connecting to a SQL Server 2000 instance that's running on the other side of a firewall. I've opened port 1433 on the firewall, and I've used the Client Network Utility to create a server alias that references the correct TCP/IP address. I can ping the SQL Server machine by IP address. Using this same technique, I can connect to a SQL Server 7.0 machine running on the same network segment behind the same firewall. What am I doing wrong?
SQL Server 7.0 always chooses port 1433 as the default port for TCP/IP connections. However, SQL Server 2000 doesn't, and connecting to SQL Server 2000 behind a firewall is slightly more complex than connecting to SQL Server 7.0.
SQL Server 2000 supports multiple instances on the same physical machine, which can have just one TCP/IP address. Of course, multiple SQL Server instances can't share one port number (e.g., 1433) at the same time, which means that each SQL Server instance must have a separate port number. Having to hard-code the port number for every SQL Server instance within every client workstation is inconvenient, so SQL Server Net-Libraries need a mechanism for querying a particular machine (by server name or IP address) and determining which TCP/IP ports SQL Server instances are using at that time. Here's how SQL Server 2000 manages this problem. (You can find details about this problem in the "Controlling Net-Libraries and Communications Addresses" topic of SQL Server 2000 Books Online—BOL.)
When an application requests a connection to a remote computer, dbnetlib.dll opens a connection to UDP port 1434 on the computer network name or IP address that the connection specifies. All computers running an instance of SQL Server 2000 listen on this port. When a client dbnetlib.dll connects to this port, the server returns a packet that lists all the instances running on the server. For each instance, the packet reports the server Net-Libraries, network addresses, and port number on which the instance is listening. After the dbnetlib.dll on the application computer receives this packet, that computer chooses a Net-Library that's enabled on both the application computer and the instance of SQL Server and makes a connection to SQL Server. BOL warns, "You cannot assign UDP port 1434 to an application other than SQL Server on computers running instances of SQL Server 2000. Network administrators managing network filters must allow communications on UDP port 1434 to enable SQL Server 2000 connections to pass through the filter." So, you must open UDP port 1434 to talk to a SQL Server 2000 box on the other side of a firewall.
Here's the second potential problem you might be facing. SQL Server 2000 doesn't default to TCP/IP port 1433 for named instances. Port numbers for SQL Server named instances are chosen when the instance is first installed. The SQL Server installation manager randomly picks a port that the server isn't using. The SQL Server named instance then listens on that port forever unless you manually change it. You can change the port number or simply verify which port the server is configured to listen on by opening the Server Network Utility, selecting the TCP/IP protocol, selecting the General tab, then clicking Properties.
End of Article
Prev. page
1
[2]
next page -->
