When you register for a shared hosting plan you are typically provided with the IP address of the Remote SQL Server that you can connect to using your own SQL Server Studio Management tool. Sometimes your ISP might not have given you access to the port that is used to connect to a remote SQL Server! You have two choices: Either send your scripts or database backup to the support guys and ask them to run your scripts on your live database or you could use an Administrative tool if provided by your hosting company to manage your database, where not all do this!

Don’t you wish you could have your own administrative tool that you could use to manage your remote database and have full control of your database objects without the need to contact your support guys and be queued to modify anything in your database?

Well the wish has come true with the myLittleAdmin Web-Based Management tool for SQL Server 2005. This tool is the first web-based management tool found in the market these days that allows you to manage almost all objects of your SQL Servers and databases, including the new SQL Server objects (database snapshots, synonyms, credentials, etc …).

We will not be focusing on explaining the use of these new features in SQL Server 2005 or how to do stuff, but rather give you an overview of all the possible features and functionalities embedded inside the myLittleAdmin tool!

Before we rush and use the myLittleAdmin tool, you will have to first download a copy of the tool. If you follow this link: myLittleAdmin, on the home page you are offered to register and get a free copy of the myLittleAdmin Lite Edition for free! You could test with this lite edition and then decide on purchasing a full copy!

There is an accompanying installation guide, so we will summarize the major steps required for a successful installation.

myLittleAdmin for SQL Server 2005 requires the following:

·         Microsoft Windows 2000, 2003 or XP Professional

·         Microsoft IIS 5.0 or higher

·         Microsoft SQL Server 2005 (Including Express edition)

·         Microsoft .NET framework 1.1 or 2.0

·         A web browser that supports XHTML and CSS2

Just a note here, the tool works only with SQL Server 2005 with all its flavors. If you would like to manage a SQL Server 2000, you need to download the myLittleAdmin for SQL Server 2000 which can be found here: myLittleAdmin for SQL Server 2000 and MSDE.

The older version of the myLittleAdmin was mainly written using ASP 3.0 however; the new myLittleAdmin is built purely in ASP.NET/C#.

Once you download the tool:

·         Open the *.zip* file and extract it to a folder on your hard disk.

·         Open IIS and make that extracted folder an application.

·         Copy the license file (license.config) into the root folder of the application.

If you want to run this application based on .NET 2.0, make sure to copy the DLLs from /bin/Framework2.0/ to /bin folder.

Now that the application is setup as wanted, we are still left with one additional and important configuration step! You need to tell this application to which SQL Server it should talk to and to do so, you need to open the *config.xml*, file located in the root folder, and updates the following section:

Listing 1

<sqlservers>
  <sqlserver address=".\SQLExpress" name="SQLExpress" />
</sqlservers>

As you can see we have registered this tool to work with the SQL Express instance installed on the local machine and we have given a name to this entry *SQLExpress*, it can be any other string.

There are a set of other configurations you can set through this file. The major one is the one we explained above, the others we will just mention them and leave it for you to read the installation guide to know how to configure them.

Add a new interface language in addition to English and Japanese presently set.

Add a new skin for the tool in addition to the default one currently present.

You can change the date display formats.

Another major section of the config file is specifying the backup/restore paths on your server:

Listing 2

<tools>
<backupwh
  backupfolder="\\Server\dbbackup\" 
  backupurl="http://mylittleadmin.host.com/dbbackup" 
/>
<restorewh
  uploadfolder="\\Server\dbupload\"
  deleteafterrestore="true"
/>
</tools>

The *backupwh* section is used to configure the backup folder. You need to create a new virtual directory (Application) on IIS called for example dbbackup and then specify the folder path using the hosting server name in the *backupfolder* attribute and in the *backupurl* attribute specifying the web URL for that folder on your hosting space.

The *restorewh* element specifies the folder to which you can upload your backed up database online and would like to restore them on your remote SQL Server. ASP.NET must have read/write permissions on this folder. Another important attribute is the *deleteafterrestore* which is clear and setting it to true means deleting the backup that you have already uploaded from the server.

Notice the name of *Server* in both elements. The server keyword should be replaced with the IP address of the server where your website is hosted.

Another important feature of this config file is the *Profile* section. The profile section specifies the path to the XML file that contains all the configurations that you need to do as an Administrator on the tool to show/hide the different features present in the myLittleAdmin. For instance you can specify in the profile file that you want to allow users to modify the content of a column inside a table.

To read more about the different configurations required you can still read the installation guide that ships with the tool when you download it to your hard disk.

Now that the tool is installed, it is time to start working with it and exploring the rich features present.

Figure 1

When you open the browser and navigate to the tool, the first screen to pop up is the one that asks you whether to use SQL Server Authentication by specifying a login/password or to use Windows Authentication to connect to the SQL Server that has been configured in the config.xml file.

Once you select the authentication type and press connect you are now inside the myLittleAdmin:

Figure 2

As you can see there is a left-hand side menu listing the major navigation options in the tool with a main window giving information about the current version of the myLittleAdmin.

When you click on the Connection item on the menu, you are given the choice of either check the current connection information or disconnect and close current connection.

Figure 3

.

Clicking the Databases item on the menu gives you the choice of listing the figure shown below:

System Databases

Database Snapshots

User Databases

Figure 4

If you have worked with SQL Server Management Studio you will not notice any difference when you expand a database and check its Tables, Views, Synonyms, Stored Procedures, etc …

In the above figure, we are viewing a set of Tables contained within a database called TheBeerHouse.

If you expand the Tables node under the currently selected database would list all the tables included in the database. As you can see clicking on a table (tbh_Articles, Figure 3) opens a set of new options on the right-hand side window. The name and path of the table is displayed in the header section. Below the header section you can see several icons. The icons are: Open Table, Modify, View Dependencies, Properties, Permissions, and Extended Properties respectively.

Clicking on any icon will redirect you to a new screen with a new set of icons that help you control the table. For instance, clicking on Modify icon show the following screen

Figure 5

This screen allows you to modify the structure of the table, adding, editing, and deleting columns. Clicking the Edit icon beside the ArticleID column shows the following screen

Figure 6

You are being redirected to a new screen that allows you to edit the ArticleID properties (allow nulls, Data Type, etc …)

Notice the black arrow drown on the figure above. This arrow points to an icon called Script. Clicking this icon allow you to edit the column properties using a TSQL Script as follows

Figure 7

Other options to mention on the Modify screen are the following three little icons:

Figure 8

The first icon allows you to go one level up, in this case back to the main screen that was shown when you clicked on a tbh_Articles table.

The second icon allows you to refresh the column and this is useful if you have made any changes to the column.

The third and last icon allows you to synchronize the changes on the column if any among all other database objects that reference this column.

The above three icons will notice them every where while using the myLittleAdmin tool.

The last feature to discuss on the Modify screen is the icon pointed to by a black arrow. This icon allows you to create a new column and add it to the currently viewed table.

Going back to Figure 4, you can see a list of folders each corresponding to items contained within a table.

Table Columns

Clicking the Columns folder would open up a new screen listing all the columns contained in the current table. Each column displayed upon clicking on it, a context-menu opens giving you several options among which is adding a new column, modifying the currently clicked column, renaming the column name, showing properties and extended properties.

Table Keys

If you click on Keys folder you get all the keys defined on the currently selected table such as foreign keys and primary keys:

Figure 9

Clicking on the first key which happens to be a foreign key in this table pops a context-menu which contains several options you can choose from among which is the Modify option, clicking the Modify option shows the following screen

Figure 10

You can easily change the columns associated with the foreign key selected, change the tables involved and then save your changes.

The context-menu that pops up in Figure 8 above allows you also to create a new foreign key.

Going back to Figure 8, clicking on any primary key would also pop up a new context-menu which provides a set of actions that can be triggered on a primary key among which is Modify option. Clicking this option shows the following screen

Figure 11

As you can see in the figure above you can modify the primary key by adding/removing columns that constitute the primary key.

Table Constraints

The third folder listed in Figure 4 above is the Constraints folder. Clicking on this folder shows all the constraints defined on the table. For instance a constraint can be a simple rule that fills a column with a default value when a new row is added to the table. Clicking on any constraint would pop-up a context-menu that allows you to add a new constraint, rename the current one and other useful options.

Table Triggers

The fourth folder shown in Figure 4 above is the Triggers folder. Clicking this folder lists all the triggers defined on the currently selected table. You can modify an existing trigger, add a new trigger and much more options. Notice here when creating a new trigger your only option is TSQL for constructing the trigger.

Table Indexes

The fifth folder shown in Figure 4 above is the Indexes folder. This folder lists all indexers defined on the currently selected table. You can manage indexes by adding new opens, renaming old ones, and checking their properties and extended properties.

Table Statistics

The last folder shown in Figure 4 above is the Statistics folder. Statistics in SQL Server 2005 refers mainly to information that the server collections about the distribution of Data in columns and indexes. To learn more about Statistics in SQL Server 2005, check this article: Basics of Statistics in SQL Server 2005.

You can check the list of views defined in a user-defined database by expanding the Views node. Clicking on each view shows a set of objects similar to the ones shown when you click on a Table. We will not go through them since they constitute a subset of the objects previously explained. The different objects are:

Columns

Triggers

Indexes

Statistics

Synonyms were introduced in SQL Server 2005 where you can define *an alias* for accessing objects inside SQL Server 2005. MyLittleAdmin tool allows you to do add/modify synonyms easily. Once the Synonyms icon is clicked you can edit a previously created synonym or create a one. More on SQL Server 2005 Synonyms can be read here: Using Synonyms in SQL Server 2005.

The programmability section acts as a container for the different programming tasks that can be done on the SQL Server 2005. This section includes management screens for creating/editing/deleting Stored Procedures, managing User Defined functions, Assemblies, Rules, Types whether user defined types or system types and other major functionalities

Figure 12

As shown in the figure above, when you click the Programmability node, it lists of all the different functionalities that can be done on the current database selected are shown.

Stored Procedures

The stored procedures section allows you to create new stored procedures, modify current ones, delete a stored procedure, and view the database objects that are dependant on each stored procedure.

Once you click on the Stored Procedures section, a list of all available stored procedures in the currently selected database is shown below

Figure 13

Upon clicking on a stored procedure, a stored procedure specific context menu is shown! You can do many tasks by selecting options from the context-menu shown.

Clicking on the Details link, you get redirected to a new screen

Figure 14

There is a section called Parameters, once you click this folder, a list of all the parameters to that specific stored procedure is shown.

If you notice the header of the screen in Figure 13 above, there are five icons:

Modify

View Dependencies

Properties

Permissions

Extended Properties

The modify icon allows you to view the stored procedure TSQL code

Figure 15

As you can see you can edit the TSQL that makes up the stored procedure then either cancel or update your changes.

Clicking on the View Details icon allows you to view the database objects that use the specific stored procedure and the objects that the stored procedure works on

Figure 16

As shown in Figure 15 above, the stored procedure aspnet_Membership_FindUsersByEmail depends on a table listed in the section of objects on which the stored procedure depends upon. However, in this specific stored procedure you can see that no other database object uses this stored procedure.

The last icon we will discuss in this section is the Permissions icon. This icon allows you to manage permissions on the stored procedure

Figure 17

Again as in all the screens available in myLittleAdmin, you can manage the permissions using the script-like window.

Going back to Figure 12 above, the context-menu contains other options like creating/modifying a stored procedure, viewing dependencies, renaming, deleting, and other options that we have already discussed in the above paragraphs.

Modifying or creating a new stored procedure simply open a TSQL editor in which you can write or modify the stored procedure.

Functions

Functions’ node is the second node beneath the Programmability node. We will not go through the details of this section since they are exactly the same as the ones described above for stored procedures. One difference in this section is that, clicking on the Functions node displays the functions categorized by their type

Figure 18

Each function type whether the Table-valued functions, that return data in the form of a data or the Scalar-valued functions, that return a single value, act as a container for the functions. However, splitting them this way makes it easier to differentiate those functions and easier to maintain.

Database Triggers

This section allows you to manage all your database defined triggers. If no triggers are present you can simply add a new one using the TSQL window.

Assemblies

SQL Server 2005 acts a host for the .NET CLR. You can create tables, stored procedures, triggers, and many other objects using your favorite programming language through Visual Studio 2005. That has been said, when you create your database objects using a .NET complaint language, as assembly will be generated and this assembly has to be loaded into the SQL Server for the objects to be physically created on your database. For more information on SQL Server CLR Integration, check this article: Introduction to SQL Server CLR Integration.

This section allows you to view all the assemblies that have been loaded to SQL Server.

Types

The types section allows you to manage the system data types, user defined data types, user defined types, and XML Schema Collections

Figure 19

In this section we will create a new user defined data type. Simply click on the User-defined Data Types and the following screen will be shown below

Figure 20

We will create a new data type called iq which is of type int.

Rules

Rules are constraints that we can define and apply on the database objects. MyLittleAdmin allows you to create a new rule using the TSQL editor.

Defaults

The last section of the Programmability is the Default sections. Once again you are allowed to create a new Default using the TSQL editor.

Service broker is a new feature added to the SQL Server 2005 that includes a set of new services including queues, routes, etc …

Since this article does not aim at explaining features in SQL Server 2005 but rather give you an overview of the myLittleAdmin, we will point you to a great MSDN Online Webcast that you can check and get a better understanding of the Service Broker: MSDN Webcast: Introducing Service Broker in SQL Server 2005—Level 200.

Using the Service Broker node you can manage all objects related to it as shown in the figure given below

Figure 21

Browsing a bit through the objects listed above, you can only delete objects but you are unable to modify or create new ones. Hope the next version of myLittleAdmin would enable you with more functionality and control over those controls.

The storage node allows you to manage Full Text Catalogs, Partition Schemes, and Partition Functions.

You can create/modify a Full Text Catalog; however the objects if present can be only deleted if already present in the database

Figure 22

The security section allows you to manage the currently selected database. The objects that you can manage in this section are:

Users

Roles

Schemas

Asymmetric Keys

Certificates

Symmetric Keys

Clicking on the Users node lists all the users that belong to the currently selected database. You will also be able to add a new user

Figure 23

You create a new user by providing username, login name, default schema, specify schemas owned by the user, and the roles the user belong to.

Roles

This section allows you to manage database roles. You can create/modify/delete roles in a similar manner to creating and modifying users

Figure 24

Schemas

The schemas section is much similar to the above two sections. You can create/modify/delete schemas from your database. In SQL Server 2005, a schema is more like a container that can contain tables, stored procedures, and other database objects. It is a logical grouping of the database objects.

Creating a new schema is a very simply task to do

Figure 25

The Asymmetric Keys, Certificates, and Symmetric Keys nodes are added to myLittleAdmin just to view and delete current objects only.

The security section of the myLittleAdmin allows you to manage Logins, Server Roles, and credentials on all the databases currently present in the SQL Server. You can view the current Logins, create new ones and modify them

Figure 26

Server Roles and Credentials are only present to view the current server roles and credentials.

This section allows you create and modify backup devices, view aliases to linked servers, and create/modify triggers based on servers

Figure 27

Creating a backup device is very simple; you specify the backup device name and the physical path on the server’s machine.

MyLittleAdmin allows you to browse linked servers without being able to modify any.

The Server Triggers section allows you to create new server triggers and modify current ones.

The management section allows you to view error logs and activity monitor generated by the SQL Server engine and the contained databases.

This section is richest section in myLittleAdmin management tool. It contains several functionalities that are heavily used especially the Query Analyzer, which allows you to run your queries against your databases online.

New Query

This is the Query Analyzer. It has been improved from the old version of myLittleAdmin. Now you have more options to control

Figure 28

You can type your query in the area specified, you can even set some options in the Options tab. When you finish typing the query, you submit it. The results will be shown in the Results tab. Any messages you get when the query executes (error messages or any kind of messages) can be viewed using the Messages tab.

Generate INSERT Script

The most interesting feature in myLittleAdmin is the ability to generate INSERT statements for your database. This feature usually comes as part of a third-party control. However, it is now embedded within the myLittleAdmin tool

Figure 29

You should supply in some configuration fields, select the database, table, columns, and here you go simply click Generate and the script will popup for you.

CSV Import Wizard

This wizard allows you to populate your database tables from data stored in CSV files

Figure 30

You select the database, table, columns, and the CSV files and then import the data. You can also configure some options for the CSV file being imported by going to the Options tab.

Backup Wizard

This wizard allows you to backup your database to a location on the server where the SQL Server is installed

Figure 31

You specify the database, the backup type (Full, Differential, or Transaction Log), a name for the backup, and the place where to store it.

Restore Wizard

The restore wizard lets you restore a previously backed up database

Figure 32

You need to specify the database you want to restore, provide a new database name for the one restored, and finally specify the physical location of the backup database. In the Options tab you can specify restore options.

Backup Wizard & Restore Wizard (Web Hosting)

These two sections are the same as the ones explained above. However they were added in the myLittleAdmin especially for web hosting companies so that users can backup/restore databases by saving a backup on the local machine and restoring from the local machine, which is a great advantage to clients.

Detach Wizard

This wizard allows you to detach a database and store the ditched database on the server

Figure 33

You select the database, configure some options, and then detach the database.

Attach Wizard

Using this wizard you can attach a previously detached database

Figure 34

You type the database name and specify the database files, located on the server where the SQL Server is installed, to attach.

Shrink Database

This option allows you to shrink a database. Shrinking a database reduces the size of SQL Server data and log files

Figure 35

You just need to specify the database name and press on Shrink Database.

Change Password

In this final option of the Tools section, you can change the password of the currently logged in user by specifying old and the new passwords.

The preferences section contains options that allow you to configure the look and feel of myLittleAdmin.

You can select a skin and display language for the tool.

This is the final section we discuss in the myLittleAdmin tool. To get more help on the tool you can refer to the following resources:

·         Online Forums

·         Send an email to the support department

In this article, we have covered most of the features included in the myLittleAdmin Web-based management tool. The aim of this article was to highlight for you the different featured included rather than teach you how to administer your SQL Server 2005.

Last but not least, I would like to thank Ms. Elian Chrebor, the MyLittleTool sales representative for offering and providing me all the required help and support to accomplish this article! Thank you Elian!

Hope you enjoyed this article.