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.
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.
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
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
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
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.
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.
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
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.