IIS Admin Pack: Database Manager

OK, this blog entry sat in my drafts for more than a month now, but finally today I will be talking about another cool feature of the IIS Admin Pack, Database Manager.

Database Manager allows you to easily manage a SQL Server Database from within the IIS Manager User Interface. Now, why would you need another UI for managing a database? Here are some of the facts that make Database Manager interesting:

  1. No additional components are required in the machine, just by installing the IIS Admin Pack you get a UI with support for remote management, and many more features (with TP2 it will install SQL Server Management Objects).
  2. Allows you to manage both local and remote database from your machine.
  3. By using the Remote Management capabilities of IIS provides a clean firewall-friendly option for managing a remote SQL Server.
  4. Exposes a public extensibility platform with support for other databases.

Features

The version included in the Admin Pack Technical Preview 2 includes the following features:

  1. Add/Rename/Drop and Edit Tables
  2. Primary Key/Indexes and Foreign Keys management
  3. Query Analyzer like function
  4. Data Editing
  5. View/Drop Store Procedures

The only permissions you need to get all the functionality in the database are "db_databreader, db_datawriter and db_ddladmin", where db_ddladmin is only required if the schema manipulation such as adding tables, deleting tables, etc, is needed.

Database Manager automatically reads the list of Connection Strings stored in the configuration system of the selected object, for example a specific site's config, or a specific application, and they will be automatically pre-loaded in the Database Manager connections tree view where you can just expand any of them and you will get the tables and all the functionality. This option makes it really convenient for querying data and allows server administrators to maintain control on what databases can be managed using this feature making it a really nice option for developers building Data Driven applications. It additionally allows users to specify their own connections that are not stored in configuration files and still get all the functionality.

This means that if you are like many of the web sites running in hosted environments (or are a hoster that provisions a single user/database in advance for users) and you only get a single database user for accessing the data as well as doing all the database maintenance, then you can live with the connection strings approach. This provides a convenient way to provide delegation in a secure way, by adding the connectionString in the root web.config with a locationPath for that particular web site and being able to lock them prevents them from uploading new connectionStrings that could be just trying to fish for users/passwords in your server. As a Server Administrator, you can enforce this functionality by specifying allowClientConnectionStrings="false" in administration.config.

Remote Administration

Arguably what makes IIS Database Manager really interesting is that it uses the IIS Manager Remote Administration infrastructure, allowing it to work remotely using nothing but HTTPS to communicate to the server. This way the server does not require to open any additional ports, all the communication happens over the IIS Remote Management HTTPS protocol. It also means that the server is the only one that requires connectivity with the Database Server, making this ideal for hosting environments because the Clients does not require any software installed on their machines, no SQL libraries or anything, just IIS Manager which can be run in Windows Server 2008, Windows Vista, Windows 2003 and Windows XP.

Note that when using IIS Manager locally such as when using Windows Vista or Windows 2008 to manage "localhost" then IIS Manager will connect directly to SQL so no additional jump through HTTPS.

To try to explain this using a picture:

 Database Manager Options

Extensibility

Another very interesting feature of Database Manager is that it was conceived as an easy way to expose different databases (not only SQL Server) to Web Developers, IT-Pro's and Hosters. This means that we created it following a Provider model where all you need to do is implement a single class in C# called DatabaseProvider, register it in the Server and now you can manage your own database. We have implemented the SQL Server functionality on top of this provider model and are working on other providers that you should soon hear from.

Andrew Birck, who is a Developer in my team wrote a very nice blog entry on how to extend the functionality. To keep it simple he implemented a DataSet provider that allows you to manage an XML as if it was a real database, allowing you to use the same features (Create Table, Edit Data, etc), make sure to check it out at: http://blogs.msdn.com/andrewbi/archive/2008/05/21/writing-a-databaseprovider.aspx

No Comments