The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,919 other followers

SQL Server 2000: How do I register multiple databases with Microsoft SQL Server Enterprise Manager?

Posted by jpluimers on 2012/02/24

I’m in the midst of a big migration traject.

One class of systems involved in the migration is SQL Server 2000 databases (yes, I know that SQL Server 2000 has been in Extended Support mode since April 8, 2008, that’s one of the grounds for migration).

Sometimes, you need SQL Server Enterprise Manager (a plugin to the Microsoft Management Console) to work on those databases to see what needs to be migrated (yes, this should be in the documentation, of which the ultimate form is the system itself ;-).

SQL Server Enterprise Manager has a nice feature that you can group SQL Server registration into SQL Server Groups.

However, it also has a limitation: you cannot add the same SQL Server to multiple SQL Server Groups. That is: unless you create an alias on the SQL Server, as aliases are not counted in the “unique” criterion.

Below are the top 2 links I found using “multiple databases” “sql server”  “enterprise manager”. Since I’m not sure the content of those will last, below are the full quotes.

Besides adding a SQL Server Alias (which has other uses as well), the most important steps are “6” in the first quote, and “1.6” in the second quote: your alias needs to point to the same IP address (or hostname) as your regular server.

How do I register multiple databases with MS SQL Enterprise Manager?

MS Enterprise Manager does not allow you to register the same database server multiple times. It is sometimes useful to create multiple registrations, if you want to connect to different databases on the same server. To workaround this, follow the steps below.

  1. Go to Start – Programs – Microsoft SQL Server – Select “SQL Server Client Network Utility”
  2. Go to Alias Tab
  3. Click “Add”
  4. In the “Server alias:” text box, enter a name you want to use to distinguish this database server
  5. In the “Network libraries:” section, select “TCP/IP”
  6. In the “Connection parameters” section, change the “Server name:” to your database server, i.e.; mssql01.discountasp.net
  7. Click OK

Once the Alias is created, you can register it using the same method described in this Knowledge Base article.

How can I open multiple databases in SQL Server Enterprise Manager?

  1. Create SQL Server Alias
    (Please repeat the following steps if you want to open mutiple databases)

    1. Open SQL Server Client Network Utility.
    2. When Client Network Utility opens, click on the Alias tab.
    3. Click on the Add button.
    4. In the Server Alias text box enter a name for your SQL connection.
    5. Select TCP/IP under Network libraries
    6. Enter the IP address of the Server your SQL database is located on, in the Server name text box.
    7. Click on the OK button.
    8. Your Server alias will now be configured, click on the Apply button.
    9. Click on the OK button.
  2. Create SQL Server Connection in Enterprise Manager
    (Please repeat following  steps if you want to open mutiple databases)

    1. Open SQL Server Enterprise Manager.
    2. When Enterprise Manager opens, right click SQL Server Group and select New SQL Server Registration.
    3. The Register SQL Server wizard will now open.
    4. Click on the Next button.
    5. From the Available Servers drop down menu, select the server alias you created earlier.
    6. Click on the Add button.
    7. The server should now be listed under Added servers, click on the Next button.
    8. When the next dialog box appears select The SQL Server login information that was assigned to me by the system administrator see above.
    9. Click on the Next button.
    10. Enter your Login name and Password ? this will be used every time you connect to your SQL server.
    11. Click on the Next button.
    12. Ensure that Add the SQL Servers to an existing SQL Server group is selected and SQL Server Group is selected for Group name.
    13. Click on the Next button.
    14. When the next dialog box opens you can click on the Finish button.
    15. Your SQL Connection will now be created.

–jeroen

via:

One Response to “SQL Server 2000: How do I register multiple databases with Microsoft SQL Server Enterprise Manager?”

  1. […] of days ago. Today one the less frequently used development virtual machines would not see the SQL Server 2000 alias it could find […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: