Web Data Administration Tool from Microsoft - Page 2

March 31, 2004

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