SolutionBase: Handle remote tasks with the SQL Server Web Data Administrator

Here's a look at how the SQL Server Web Data Administrator can allow you to easily perform a number of valuable tasks remotely.

If you need the ability to manage your SQL Server data wherever you are, or if you're a DBA who needs to work remotely after hours or when you're on call, Microsoft recently released a Web interface to that can help you manage your SQL Server databases. With this tool, called the SQL Server Web Data Administrator, you can:
  • Create/edit databases in SQL Server 2000 or MSDE 2000.
  • Perform ad-hoc queries against databases and save them to your file system.
  • Export and import database schema and data.
  • Manage users and roles.
  • View, create, and edit stored procedures.

I'll give you an overview of how this tool works and give you a few quick looks at the tool in action.

You can download the SQL Server Web Data Administrator from Microsoft’s Web site. Before installing it, you must meet the following requirements:
  • Windows 2000 SP4, Windows Server 2003, or Windows XP (on the machine where you're actually installing the program)
  • SQL Server 7.0 SP2 or higher
  • Microsoft .NET SDK or Visual Studio.NET
  • IE 5.5 or higher
  • IIS

Installing the Web Data Administrator
Once you have downloaded the tool, click the Setup.msi and the welcome screen is displayed. Click Next. The following two Windows ask you to accept the license agreement and select your Installation folder. After you choose an installation location, click Next to start the installation. Once the installation is complete, click Close.

Working with the Web Data Administrator
Before you can begin working with this tool, you must first know how you can access it via IIS. To configure the Web Data Administrator to work with IIS, click Start | Programs | Microsoft SQL Web Data Administration | SQL Web Data Administrator. You are presented with a Window to either start IIS or choose Cassini Personal Web Server (Figure A).

Figure A

Cassini is a sample Web server written using .Net framework that demonstrates ASP.NET. You can download Cassini from Microsoft. For the purposes of this article, we are going to choose IIS and click Start.

You can also access the tool directly by typing the following link in IE:


After clicking Start or typing the link manually, the welcome screen for the Web Data Administrator appears as shown in Figure B. You can log in to SQL Server via Windows Integrated authentication or SQL Server authentication.

Figure B

Now that you have logged into your SQL Server via IIS, you'll see the screen in Figure C. You can perform one of the following:
  • Create new databases
  • Edit, query, or delete existing databases
  • Import databases
  • Export databases
  • Create and edit logins
  • Create and edit server roles

Figure C

At this point, you can begin to see the power of the Web Data Administrator. Let's dig a little deeper into each of these areas to show you how you can effectively manage your SQL Server.

Creating a new database
In order to create a new database, simply click the Create New Database link and enter the name of your new database. For example, I created a database called Techrepublic, as shown in Figure D.

Figure D

Once the database is created, you can manage the objects by choosing the appropriate task, as shown in Figure E.

Figure E

You can also view the objects by clicking Server Tools | Databases and choosing the database that you want to manage objects on. For example, if we click Server Tools | Databases | Pubs, we can view all the tables by choosing the appropriate link. Next, we can create a query and execute it for information as shown. Finally, we can run a quick backup if necessary, as shown in Figure F.

Figure F

Exporting/importing a new database
Exporting and importing a database is just as simple as creating a database. Just select Server Tools | Export/Import and choose the database and objects. Once that is complete, click Export or Import as shown in Figure G, and save the file to the appropriate place.

Figure G

Creating logins and server roles
You can quickly create a login by choosing Server Tools | Security and then clicking the Security icon. Next, choose the Create New Login link on the right side of the screen (Figure H).

Figure H

You can also add and edit server roles (Figure I) by choosing the Roles icon.

Figure I

End sum
In the age of 24 x 7 Web sites with tolerance for virtually almost no downtime, a DBA often needs a way to quickly perform tasks when off-site. The SQL Server Web Data Administrator is very powerful and gives you the ability to do a multitude of things with your SQL Server remotely. I recommend that you download it and give it a try. Of course, when setting it up in a production environment, don't forget to take the appropriate security precautions.