Introduction

f there's one thing constant in the IT and hosting industries, it's that technology is constantly changing and evolving. For me, keeping up with it all has been nothing short of a challenge. When Microsoft SQL Server 2005 came out towards the end of 2005, I pushed it to the side. It was just another thing I didn't have time to learn. But as my customers started asking for it, I realized I had to bite the bullet and get it installed.

But I knew that when I implemented MS SQL 2005 in my hosted environment customers would need help connecting to it. They were already used to myLittleTools' myLittleAdmin (MLA) for MS SQL 2000, but it didn't support MS SQL 2005. Fortunately, myLittleTools was already way ahead of me and had created a version of MLA for MS SQL 2005, now based on ASP.NET and C# versus classic ASP. So, after finally getting the server side together, I contacted myLittleTools for an eval copy and began the process of getting myLittleAdmin 2005 installed and running.

The best part about MLA 2005 is that it can do everything you can do in the SQL Management Studio. Which means you can tighten down your database security and only let MLA access it instead. It also makes for happy customers who don't need to figure out how to connect SQL Management Studio to your database server and can access their databases from anywhere they have an Internet connection.

This article is meant to be a brief overview of MLA 2005 from a web hosting perspective and is no way fully comprehensive of MS SQL 2005 or MLA2005. There's just too much to talk about and too little space to do it. But it should give you a good idea of what MLA 2005 is capable of and that it's a perfect fit for web hosts who need to offer their customers easy but robust access to their SQL 2005 databases.

So without further ado, let's dive right in.

Installation

Installation in IIS 6.0 on a Windows 2003 Standard SP2 box, as you would expect, is straightforward. There is no executable to install. In fact, it goes in much like PHP installs. Just copy the files into a web accessible directory, enable the version of .NET you want to use on the site (MLA 2005 supports both .NET and .NET2), configure the MLA folder as an IIS application, edit the config XML file to setup the database connectivity, then browse to the web directory you installed MLA. Oh, and don't forget to copy the license file to the root of MLA. That's it!

The MLA installation documentation is very simple and spells this all out clearly so I won't go step by step through the install process here. Just follow the install guide step by step and you won't have any problems.

Where I did have an issue, and this was not related to MLA, was configuring MS SQL 2005 to allow remote connections. You need to ensure you do this or you'll pull your hair out trying to figure out why MLA can't talk to MS SQL 2005. The following KB article will get you set up right: http://support.microsoft.com/kb/914277

Configuration

Once MLA is installed and connecting to MS SQL 2005 correctly, you can configure the features that customers will have access to by modifying some XML configuration files. You can easily turn on or off features you want to offer, which in turn affects what the user will see in the MLA web interface.

Once you've done this, and log into MLA, you should see something like the following. In this case, all features are turned on so you can see what is available through this app.

Some features in the navigation tree that you usually don't want to show to end-users, such as System Databases, can be removed from the XML file that handles the tree view hiding these options from users. All the config options are conveniently located in one XML folder which makes it very simple to modify and customize MLA to display only what you want the customer to see and use. And the manual does a nice job of explaining what you need to modify to remove some of the more worrisome features for shared database setups.

Features

In addition to a wealth of features already included in MLA 2000, including easy management of your MS SQL data, table structure, stored procedures, backups, and more, MLA 2005 sports a cleaner, more intuitive interface, support for database schemas, SSL certificates and asymmetric/symmetric keys, database synonyms, database snapshots, and an enhanced editable data grid making data editing a breeze for end users.

The administrator-editable config file allows you to turn on or off practically any feature that MLA 2005 offers so if you want to just allow customers to view and edit their table data you can, or if you want to allow them complete control over their databases then you can turn all the features on. It's up to you.

I'll briefly discuss each of the main areas of MLA 2005 below since these pretty well mimic the features of SQL Management Studio which you are already hopefully familiar with. Each area is accessible via an expanding contents menu at the left side of the application.

Connection

The Connection menu area has a couple of options: Connection Info and Disconnect. Connection info has two tabs that shows the connection information being used to connect to the database and the license information. You'd probably want to remove the license tab through the appropriate XML file as it's not necessary for the end-user to see. The disconnect option to log the user out of the interface.

Databases

The Databases menu section is where your customers will be doing a lot of the real work they need to do. Here is where users can view their databases, edit their tables, etc..

You can see in the capture of the expanded menu from the databases section.

The System Databases section shows you the usual list of system databases. Users can view the data but without explicit access to the tables, they can't modify anything. Still, I would remove this tree item from the XML to ensure they don't "play around". There's no need for them to see this information.

The Database Snapshots area allows you to create a read-only, stable snapshot of a database at a point in time. This is useful for recovery of a database if, say, you damage data. Snapshots have pros and cons that are beyond the scope of this article, and are an Enterprise-only feature so many of you smaller hosts probably won't be able to offer it. As with all tree items, you can remove it from the tree so it can't be used. The point is, it's there if you need it and can take advantage of it.

The User Databases is where users can administer their databases. Users will only see the databases they're entitled to see. You can see by the expanded view image at left that on each user database there are more features than many customers will probably ever need or use, but the important thing is they're all there and available and all able to be turned on or off depending on your needs.

Within the User Databases section, users can modify their tables, views, create synonyms for their databases, create and manage Stored Procedures and Triggers, create and view Full Text catalogs, and manage security on their database.

Many of you will be using a Control Panel of some sort to allow your customer to create and manage their database users so the Security section probably can safely be hidden from view since we usually don't want users creating users at will outside of the control panel. I'll briefly cover some of the main areas of MLA 2005 to whet your appetite!

Tables

When clicking on the database name in the contents menu, the list of available tables appears in the right window pane. Left clicking on the table name pops up a nifty little menu containing the operations available to that table. You can easily create a new table, modify the structure of an existing table, open a table to edit the data, and much more. This is a nice feature that MLA 2000 didn't have and it's a welcome addition.

Creating a new table is quite simple. Choose New Table from the menu and the following screen opens.

As you can see, you have all the options available to you to create the table as you would in the SQL Management Studio. You can even simply create T-SQL scripts by clicking the little script icon at the top left of the section (circled).

If you instead click the Open Table menu item, the table opens and the data is then available for viewing and editing as in the following screenshot. Note the small menu icons. At the top you can go back up a level, refresh the data, or synchronize the treeview with the database. Below that you can export the data a XML, XLS, or CSV, and create a new data item. Then next to each row of data you can either delete the row or edit the row. You can also modify the content just by clicking on the column data.

When editing the row, you can easily modify the data contained in the row as in the following screenshot .

Views, Stored Procedures, and other Advanced Database Operations

Views, Stored Procedure, and most other more advanced operations on the database can be created through the respective content menu items. Creating these operations can be done by entering the T-SQL code into the script area and then clicking the create button as in the following screenshot.

Notice that a nice template is created for you so you can just plug your T-SQL code in, click the create button, and you're done. Most of the other more advanced operations are set up this same way so I won't bore you to death with an explanation of each section.

Synonyms

New with MS SQL 2005 and MLA 2005 was the ability to create a database synonym so you don't have to use the long notation to reference a database and it's perfect if you need to switch to another database temporarily to, say, do some testing then switch back. Customers should like this feature quite a bit and it's easily configured through MLA 2005.

Security

The Security contents menu section allows you to create, view, edit, and delete all logins on the server, assign server roles, and manage server credentials. I would argue that this section isn't really appropriate for a shared server environment so it should be hidden from the user by modifying the appropriate XML file. But if this tool is being used to manage your server in a non-shared environment, it could be very useful. Again, like all features in MLA, they're there if you need them and can be hidden if you don't. (Users with no admin rights can only see their own login)

Server Objects

Server Objects allows you to create server-level objects such as backup devices, linked servers, and triggers. This section allows you to view any available backup devices, linked servers, and server triggers.

Management

The management section allows you to view any active sessions related to the currently logged in user. If you log in as the system administrator, you'll be able to see all active sessions on the server. The error logs are usually only viewable by system administrators.

Tools

The tools section provides a number of useful features such as the database query, backup wizard for web hosting, detach and attach wizard, shrink database, and a tie-in to myLittleBackup.

The New Query feature makes it simple to type in some SQL and get a result set back, much like the old Query Analyzer. You can also import a pre-made .sql script using the folder icon at the top right of the screenshots as well, and set limits on the number of results you want returned in the options tab.

The results of the query are returned nicely formatted

You can also easily generate insert scripts to reimport data into a table, and use the CSV import wizard to import data from a CSV file.

Backup and Restore Wizards. myLittleAdmin also offers two wizards for backup and restore. One is a complete backup wizard and shown in the screenshots below. However, as a web host you probably don't want to give your users access to all these capabilities so there is also a built-in web hosting version of the Backup and Restore wizard which is substantially limited but will allow users to do a basic backup and restore of their databases if necessary.

Detach and Attach Wizards. myLittleAdmin can detach and reattach databases just as you can do via the SQL Management Studio. While detaching a database is probably not something you want offer web-hosted customers as they may inadvertently disconnect their databases, the attach feature may be handy since many users are coming from other hosts and need to easily import their MS SQL database into their hosting plan. The attach features makes it easy for them to do this.

Detaching a database.

Attaching a database.

Shrink Database. How many times have your users databases grown out of hand? If you haven't set your MS SQL databases Recovery Model to Simple the transactions logs will grow rapidly and you'll end up running out of disk space before you know it. The Shrink Database feature allows you to shrink the database size and dump those old transactions just as you can in the SQL Management Studio.

Change Password. If you so choose, you can allow your database users to change their database user password through this function. Most web hosting control panels do this through their interface so this is really not a necessary feature for hosted operations, but for individual and company operations this would be a very handy feature.

Preferences

Finally, there is a preferences section that the user can use to change their desired display and language preferences. They can change features such as skins (if you have additional skins installed), their language settings (by default English, French, Deutsch, and Japanese are available), their date formats, and how many rows they want to be displayed in data grids.

Conclusion

opefully this article gives you a quick overview on myLittleAdmin for SQL Server 2005. This is a complex and very complete application that gives you all the features you have in the MS SQL 2005 Management Studio, but totally web-based and totally customizable. For you hosts out there, this is a necessary piece of software to provide your customers. No MS SQL database management software I have seen or tested comes close to the completeness of this application and your customers will appreciate having the ability to control their MS SQL databases without needing to know install the Management Studio or having to be on a machine that has it installed, making their databases truly manageable from anywhere they have a web connection! I highly recommend it!