Installing Microsoft SQL Enterprise Manager

First: What is SQL Enterprise Manager?
Microsoft sells their server software with their own SQL service. This is a competitive product and offers the features used by large companies with massive databases and large demands. Their competitors have good products also and the market at the time of this writing is competitive (2004). SQL servers are database engines. They provide access to data and the ability to manipulate the data. They generally have no interface to allow the user to build and manage a database. This is the purpose of the SQL Enterprise Manager. It is for use with Microsoft's SQL Server.

Second: You need to know that there are version problems and not all MS SQL Managers are the same. Not all versions of MS SQL Server can be accessed by the same version of SQL Enterprise Manager. There is a dividing line at version 6.5. Pre 6.5 requires an earlier version of the SQL Enterprise Manger and, of course, post 6.5 requires a later version. Windows 2K and 2K3 SQL servers can use either the 2K or 2K3 versions of the manager, which is most of the in-service hosted SQL servers (5/04).

Third: Where to get the SQL Manager?
Microsoft includes the SQL Enterprise Manager as part of their SQL Server software, which is usually sold with their W2K or W2K3 server software. The manager may be installed from the server software disks. Of course this means one must have purchased the SQL Server software and have a CD Key.

There is also a Developer's Edition of the SQL Server available for US$50.00. Microsoft SQL Developer Edition

Forth: Do I have to use MS SQL Enterprise Manager?
No. There are other $50 database tools. Some are web based. Also Access 2000 can be used to edit the database.
MS SQL Enterprise Manager Alternative

How to install the SQL Enterprise Manager.

Windows 2K
Each new version of The Small Business Server (SBS) comes with more features. If you have installed the server you know if your version came with the SQL Server software. You can browse the CD's (typically CD #2) and find a folder named SQL2000. If there is no such folder, your version did not include the SQL server.

Within the folder is a program labeled AUTORUN.EXE. Double click the file to run it.

Select SQL Server 2000 Components
On your W2K and XP workstations your will get an error stating Microsoft SQL Server 2000 Standard Edition server component is not supported on this operating system. Only client components will be available for installation.

Click OK and proceed.
The install Wizard will open and walk you through the installation.
When asked which components to install, accept the default. Be sure to install the BOOKS as they are part of the SQL Enterprise Manger's Help System.

How to use the SQL Enterprise Manager on Hosted Sites?

Notes
The SQL Enterprise Manger is designed to allow DataBase Administrators (DBA's) to work with clusters of SQL servers housing multiple large applications. Fortunately it also works well for web developers working with many small sites too.

The manager runs in the Microsoft Management Console (MMC). It displays the typical Explorer like arrangement with a tree on the left and items on the right. The tree starts off with the Console Root and then Microsoft SQL Servers. Within the Servers section there are GROUPS. Initially only the default SQL Server Group is shown. Add each of your sites as a group.

In shared environments the SQL database must be setup by the system administrator prior to use of the manager. Contact us and provide the information indicated below. We will setup your database connection. The Enterprise Manger has the ability to create databases. But, you will typically lack the authority/permissions needed to create a database. The exception is for those of you with dedicated or co-located servers. Attempts to create a database in a shared environment will usually fail. Not to worry, you can complete the tasks needed to setup your database.

Adding SQL Enterprise Manager Groups

These steps assume you have contacted the ISP (us) and had your SQL database setup.

Right click the group in which you want to place a new group. Microsoft SQL Servers is the top group and new sites should probably have their own group under this.

Where you choose to click is not important as the dialog allow you to choose where the group will be inserted. The example shown shows each new site as an equal of the default SQL Server Group.

A new group can be a sub-group. You could have multiple applications and databases within a single site.

Once the group is created, right-click on it and Register a New Server within the group.

You may use the Wizard or not.

You will need to know the name of the SQL server. This can be just a computer name if it is on a local network. On most hosted systems the ISP will provide a server name.

The SQL database will have to be set up by the ISP's administrator. This is a security measure to prevent users from setting up unlimited databases and filling the server's hard drive.

You typically provide:
SQL Database Name
SQL username
SQL password
DSN name

These names can be almost any name you choose. In shared environments, make the name something unique to your site. The ISP will advise you if there is a conflict and typically select something similar. Once set up the ISP will advise you of the details of the setup. The final information is what you need to complete setup via the wizard.

Most of the Wizard's options are self explanatory. The choices are few. If you get it wrong, no fear, just change it and try again.

A major GOT CHA...
On shared systems there is a often a huge lag when opening the Database List within an SQL Server Group. Shared servers can have 500 or more databases. It takes several minutes for all of that information to download to the Enterprise Manger. This can take considerable time on a dial up connection and 5 to 15 minutes on DSL. During that time the Enterprise Manger will seem to be frozen. The Task Manger will show it as Not Responding. Let it run. Eventually it will display a list of available databases. You will only have access permission to your database.

This 'lockup' can happen in several cases. When you see the manager stop responding, just wait. It can be particularly infuriating when a connection setup fails. To delete a bad connection, select the group name so the connection appears in the right-hand window. You can then right-click the connection in the right-hand window and delete or edit it.

At this point you should be connected to your database and be able to create and design tables, views and processes. You can also import and export data.

Backups are handled by the ISP and you most likely will not have the authority/permissions needed to execute the backup tasks. You can export data to a folder within the site and then download the data.

Multiple Databases on the Same Server
Web designers typically place many of their sites with the same hosting company. Shared server hosting may place the databases for several clients in the same server. MS SQL Enterprise Manager is designed such that you are expected to have access to all the databases in a server. In a hosted environment this is usually not so.

To work with multiple databases in the same server you must select the option to "Prompt for password." This will allow you to change from one database to another. It is frustrating on shared environments as each popup menu requires a round trip to the server and a lengthy download. When right-clicking on the server to change login information it is best to wait, as switching to another task often creates problems with popup, in which case you have to repeat the wait.

It is a good idea to select the 'Prompt Each TIme' option for each database within the same server and avoid the waits.

Faster Way
You can use Access 2000 or later to edit the database. It is faster and offers more options than SQL Enterprise Manager 2K. There are some SQL 2K3 features that will not be available via Access 2K.