Web Data Administration Tool from Microsoft

Wednesday Mar 31st 2004 by Gregory A. Larsen
Share:

Are you a Database Administrator that has a desire to work remotely but don't have access to your databases while you are out of your office? Would you consider using a web interface to manage your SQL Server databases and Logins? If so, then you might want to consider checking out Microsoft's tool that will allow you to perform some SQL Server administration functions via the web.



Are you a Database Administrator that has a desire to work remotely but don't have access to your databases while you are out of your office? Would you consider using a web interface to manage your SQL Server databases and Logins? If so, then you might want to consider checking out Microsoft's tool that will allow you to perform some SQL Server administration functions via the web. The tool is called WEB Data Administrator and can be downloaded from Microsoft. The WEB Data Administrator tool is written in ASP.NET. In this article, I will give you a quick introduction of the capabilities of this tool, as well as let you know some of the key functionality that is missing from the tool.

How to Obtain the Web Data Administrator Tool

You can download the Web Data Administrator Tool from Microsoft. The download is 2292 KB is size. The Web Data Administrator Tool needs to be installed on a machine running Windows 2000 and IIS, plus the machine needs to have the .NET Framework SDK installed. To download the file and obtain additional information about this tool go to the following web site: http://www.microsoft.com/downloads/details.aspx?FamilyID=c039a798-c57a-419e-acbc-2a332cb7f959&DisplayLang=en

Quick Tour of the Web Data Administrator Tool

Once you have downloaded and installed the Web Data Administration tool it will be all set up and ready, for you to use, to perform your remote administration tasks. The default home page for the tool can be found at http://<webserver>/webadmin/default.aspx, where <webserver> is the name of the webserver where you installed the tool. When logged directly onto my IIS machine the home page for the Web Data Administrator tool looks like below.

 Click for larger image
As you can see this tool allows you to enter a Username, Password, and a Server name, plus it allows you determine whether you want to connect via a Windows or SQL Sever authenticated login. This interface allows you to connect to any SQL Server machine that can be reached from your IIS machine.

Once you login to a server, the tool provides you a number of "SERVER TOOLS" for managing your environment. Below is a screen shot that was displayed when I connect to the local SQL Server that resides on my IIS machine.



 Click for larger image

As you can see, there are 4 different SERVER TOOLS, "Databases", "Import", "Export" and "Security". Note that on the above screen shot the "Databases" server tool is highlighted, so the right pane displays all the databases on my local SQL Server. Let's review how each of these SERVER TOOLS can help you manage your SQL Server environment remotely.



Using Web Data Administrator to Manage Databases

From reviewing the prior screen shot, you can see that this tool allows you to create a new database, plus edit, query or delete an existing database. If you want to create a new database just click on the link named "Create new database" in the upper right hand corner of the page. After clicking on this link, the following page is displayed.

 Click for larger image
Basically you are given just a simple prompt that allows you just to enter the name of the database you wish to create. You are not given all the extra options that are normally available when creating a database via Enterprise manager or T-SQL. After you have entered the database name, all you need to do is click on the "Create" button and the database will be created.

If you want to manage objects within a database, first you need to click on the "Databases" SERVER TOOL item, and then click on the database for which you want to manage objects. When I do this for the Northwind database, the following screen is displayed.



 Click for larger image
Here you can see that when you are managing a particular database you are allowed to manage Tables, Stored Procedures, Properties, Users, and Roles, as well as you are allowed to write queries against SQL Server. Also for each table you can edit, rename, or delete, as well as create a new table. This interface allows you to do most of the DBA tasks associated with a database.

I am not going to show you how each of these options work, but I will at least show you how you can use the "Query" tool to return or modify data in your database. When you click on the Query Tool, the following web page is displayed.



 Click for larger image

From this web page you are can write, execute and save your query. You can also browse for a query that you already have saved. Say you wanted to display the top 10 Orders records. To do this you would just type the command "select top 10 * from orders" into the text box provided on the above page and then hit the "Execute" button. When I did this on my server this is what was returned.




 Click for larger image
Note the records requested in the SELECT statement appear at the bottom of the page. The query tool allows you to review and manipulate the data in your database via a web interface using T-SQL commands.

Not only can you review data, but you can also use this query interface to manipulate your SQL Server using any T-SQL code. Here is an example of a T-SQL command to backup the Northwind database.



 Click for larger image

I think the database interface of Web Data Administrator exposes you to a lot of functionality for managing Databases. Plus with the Query tool you can basically manage the database and server if you know the T-SQL required for the particular task you need to perform.



Using Web Data Administrator to Import and Export Data Wizard

This tool has a couple of data wizards that allows you to Import and Export databases. This functionality allows you to migrate a database using the Web Data Administrator tool from one server to another. Let's use the export tool to export the database called "test" on my local server. After I click on the "Export" link under SERVER TOOLS the following web page is displayed.

 Click for larger image

From this page, you can select which database components you want to export and the options associated with the export. Pay close attention to the disclaimer at the bottom of this page. I would suggest you fully test the Export functionality so you know exactly what might not be correctly generated.

To create an export file of the "test" database all I needed to do was click on the Export button. When the Export is ready to save the generated script, I was prompted for a file location where I want to save the script to build the database "test."

If you review the script generated by the Export wizard you will find it generated a series of T-SQL statements. These statements will create the database and all its objects and then insert the data into each object.

If you want to Import a database you would click on the "Import" link under SERVER TOOLS. When this is done the following IMPORT DATABASE screen will be displayed.

 Click for larger image
To insert a database all that needs to be done is identify a script file and click on the "Import" Button. Also, note this screen has a similar disclaimer around possible problems that might occur when managing a database without using the Web Data Administrator tool.

If you want to use these wizards, I would suggest you fully test the Export and Import functions so you understand exactly what they accomplish. Hopefully after some testing you might be able to determine what you can manage outside of the Web Data Administrator tool without compromising the output generated by the Export tool.

Using Web Data Administrator to Manage Security

As you can see below, the Security tool in Web Data Administrator will allow you to manage Logins and Server Roles.

 Click for larger image

When managing Logins you can either modify an existing login, or create a new login. Below is the screen that will be displayed when you click on the Logins icon on the above screen.




 Click for larger image

If you want to edit an existing login then click on the text "Edit Login" next to the login you wish to modify. If you want to create a new login, you need to click on the text "Create new Login" in the upper right of the screen above. When you click on the "Create new Login" link, the following web page is displayed.




 Click for larger image

Here you are able to create the new login as either a Windows or SQL Server authenticated login. When adding a new login you are also able to set the default database and assign logins to Server and/or Database roles.

One thing that is interesting about entering logins is the fact that you are not given two prompts for entering the password, so make sure you are careful to not incorrectly type the password. For some reason this Login interface does not allow you to change a password for an existing Login.

When using this interface to manage Server Roles the following web page is displayed.

 Click for larger image

As you can see, this page will allow you to add or edit the members of the Server roles. To edit a particular role just click on the "Add or Edit Logins" link next to the appropriate server Role.




Database Administration Functions missing from Web Data Administration tool

Even though the Web Data Administrator tool can do quite a bit, it is still missing some functionality you might need to manage your environment. Here is a partial list of the key database management functions missing from the tool:

  • No way to create and manage triggers
  • Can't create a database backup and restore a database
  • No functionality to manage SQL Server Agent Jobs
  • No method of creating or editing DTS packages
  • There is no way to add a new database role
  • No way to add an existing database user to an existing database role
  • Can't change the password of an existing Login
  • Can't add indexes to an existing table, except for a primary key
  • You can not manage replication with the tool
  • No method for creating or editing linked servers

Even though these are not provided with the tool, some of these items can be performed by using the Query tool to submit T-SQL code to perform these tasks.

Conclusion

The free Web Data Administrator tool goes a long way to allow you to manage your SQL Server databases using a web interface. Although this tool does not have all the functionality of Enterprise manager, it still has much functionality that might allow you to perform most DBA functions remotely via the web. The best thing about this tool is it is free and Microsoft provides you with the source code. If you are considering how you might provide remote database administration then I would suggest you review this product to determine if it provides the functionality you need.

» See All Articles by Columnist Gregory A. Larsen

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved