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
» 
Comment and Contribute
