Web Based Database Administration for SQL Server

By:   |   Updated: 2007-08-15   |   Comments (6)   |   Related: > Tools


Problem

I am working at an organization where I need to have access to my SQL Servers around the clock.  I do not have a company laptop to check into the environment and troubleshoot issues directly so I am looking for alternatives to meet the need.  We are on a tight budget so I wanted to find out what native tools are available.  Do you have any suggestions for SQL Server tools to meet this 24X7 management need?

Solution

With SQL Server growing to support 24X7 needs in many organizations, supporting SQL Server based applications after hours is essential because the need is typically not that intense nor do most organizations have the budget to have a 24X7 staff.  As such, as the DBA you have a number of options when it comes to determining how you want to manage the SQL Server environment.  Here are a few of the options that are available:

  • Laptop Computer - If the need is great enough, request the on call DBA to have access to a laptop computer to manage the SQL Server environment as needed.  The cost for a reasonably powered notebook is less than $1500 USD, so this could be a viable option without having to put more expensive infrastructure in place.
  • VPN - Another option is to have VPN access to the network from your local machine and use the native SQL Server tools installed on your machine to manage the SQL Server environment.  This will eliminate the machine cost for the company if that is an issue.
  • Citrix - Some organizations also use Citrix based access to the network.  Once logged in to the network, then you can launch the native SQL Server tools installed on your Citrix machines to manage the SQL Server environment.  If the Citrix environment is not already in place then this option could be costly.  If it is already in place then adding the SQL Server tools could be a very simple solution to the issue.
  • Microsoft SQL Web Data Administrator - In April of 2004, Microsoft released the SQL Web Data Administrator application which is a free tool from to perform typical SQL Server administration and development tasks.  You have the ability to perform the following tasks:
    • Database, table, stored procedure, relational object, etc. creation, modification and deletion
    • Manage security
    • Import data and export relational objects
  • Third Party Web Based Application - Leverage a third party application to manage the SQL Server instances over the internet with complete functionality to manage the SQL Server environment.
  • Third Party Handheld Device - Leverage a third party application to manage the SQL Server instances via a handheld device i.e. phone or Blackberry.  Depending on the application, this provides a great deal of flexibility to be able to manage SQL Server with the device already in your pocket.
  • Hop in the car - Drive into work to manage the SQL Server environment from your desktop machine.  Although this is typically not pleasant at 2:00 AM, it is an option if the physical security permits access to the building.

 

Microsoft SQL Web Data Administrator - Installation and Configuration

Since you have the need to manage SQL Server remotely at a very low cost, then one of the first options to consider based on price is the Microsoft SQL Web Data Administrator.  This is a web based application that can be installed on a web server in your environment to manage your SQL Servers over a browser.  Let's jump into this tool to see if it will meet your needs:

 

Microsoft SQL Web Data Administrator - Installation and Configuration

  • Welcome Screen - Review the message on the interface and press the 'Next >' button to begin the process.

MicrosoftSQLWebDataAdministrator 1

  • License Agreement - Review the EULA and once you agree select the 'I Agree' radio button then press the 'Next >' button to continue the process.

MicrosoftSQLWebDataAdministrator 2

  • Select Installation Folder - Validate the folder is correct and determine if you want to application accessible to everyone or just you then press the 'Next >' button to continue the process.

MicrosoftSQLWebDataAdministrator 3

  • Confirm Installation - Review the message on the interface then press the 'Next >' button to begin the installation.

MicrosoftSQLWebDataAdministrator 4

  • Installing Microsoft SQL Web Data Administrator - No action.

MicrosoftSQLWebDataAdministrator 5

  • Installation Complete - Review the message on the interface then press the 'Close' button to finish the installation.

MicrosoftSQLWebDataAdministrator 6

  • Launching the Interface - Navigate to Start | All Programs | Microsoft SQL Web Data Administrator | SQL Web Data Administrator.
  • Launching the Application - Once the interface loads, select the applicable port or just press the 'Start' button.

MicrosoftSQLWebDataAdministrator 7

  • Login - Specify the SQL Server instance, the authentication mode then press the 'Login' button.

MicrosoftSQLWebDataAdministrator 8

  • Main Interface - The main interface will load with the high level navigation on the left to include the Databases, Import, Export and Security.
  • Databases Interface - The main interface is the related to the databases where you have the ability to edit, query and/or delete data in addition to creating a new database.

MicrosoftSQLWebDataAdministrator 9

  • Databases | Database Interface - When you click on the 'edit' hyperlink from the Databases interface it takes you to a listing of all of the tables with the owner, creation date and row count and the ability to edit, rename or delete the table.
  • Database Navigation - On this interface you have the ability to manage tables, stored procedures, issue query, review database properties, review the users and roles.

MicrosoftSQLWebDataAdministrator 10

  • Edit Stored Procedure - On this interface you have the ability edit existing stored procedures.

MicrosoftSQLWebDataAdministrator 11

  • Query Window - The query interface has a standard input and output screen.  The input screen on the top provides the opportunity to issue queries directly or to upload a query from a file.  The output screen on the bottom provides the results for each batch as a separate result set in gray.

MicrosoftSQLWebDataAdministrator 12

 

MicrosoftSQLWebDataAdministrator 13

  • Import Database - The import database functionality permits creating a database via a script file.

MicrosoftSQLWebDataAdministrator 15

  • Export Database - The export interface provides the ability to export the DML, DDL and data to a set of text files.

MicrosoftSQLWebDataAdministrator 14

 

Microsoft SQL Web Data Administrator - Limitations

Based on analysis of the application, some of the functionality that is available with Enterprise Manager\Management Studio is not available with the Microsoft SQL Web Data Administrator to include:

  • The ability to review the Windows Error Logs, the SQL Server Error Log and the SQL Server Agent Error Log.  This information can be key if SQL Server is suspected of having issues.
  • The ability to stop and restart the SQL Server services.  I believe stopping SQL Server may be available with access to xp_cmdshell (if it is enabled), but restarting the SQL Server services is not available as a T-SQL command because the database engine is not available.
  • Not aware of an interface to manage Jobs, Alerts, Operators and\or DTS Packages as well as Reporting Services, Analysis Services and SSIS Packages as a portion of the Business Intelligence Management Studio.

Based on this information, it is recommended to test the Microsoft SQL Web Data Administrator application to validate it meets your needs from a performance and management perspective.  Although it is a free tool, if the tool does not meet your needs then seek a solution that will do so.

 

Next Steps
  • Determine your needs as well as the expectations from your management and users to manage your SQL Server environment.  Be sure to understand how quickly you need to respond and resolve the problem as well as the expected frequency to remotely manage or troubleshoot a SQL Server issue.
  • Once the needs are clearly identified, then review the options that are available in the industry to determine which options should be setup in a development or test environment to validate the overall functionality.
  • With a test environment in place, be sure to test the functionality and performance from your home, hotel or remote office to validate the solution is viable.
  • Once you have the right solution in place from in a test environment and worked out the kinks then implement the solution in production.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2007-08-15

Comments For This Article




Monday, May 13, 2013 - 6:24:16 PM - 3Essentials.com Back To Top (23929)

The component you get the "missing component" error on is apparently related to SQLDMO.DLL, which is one of the components of “SQL Database Management Object”.  The documentation for SQL Server 2008 indicated that support for SQL-DMO was going to be removed in the next version of SQL Server.  It was actually removed from SQL Server 2008 too, they just maintained support for it through the SQL Server 2005 backwards compatibility components.  So... for SQL 2008, you can get this component installed by searching for the download for the SQL Server 2005 backwards compatibility components and installing that package after you've got SQL 2008 on there.   Then this solution works fine (we've used it for SQL 08 installs for several years). 

For SQL 2012, we're going to be out of luck.  The documentation for SQL Server 2012 indicates that it has been removed completely and is no longer supported.  So if you’re using SQL Server 2012, this web admin solution will not be an option.  


Wednesday, September 12, 2012 - 8:27:06 AM - Bala Back To Top (19486)

I have installed the SQL webAdmin and try to connect to the server(SQL server 2008) I got the eror like

Retrieving the COM class factory for component with CLSID {10020200-E260-11CF-AE68-00AA004A34D5} failed due to the following error: 80040154.

Please suggest what should I do?


Did I missed anything or It wont work with SQL Server 2008

Please suggest.

Thanks



Tuesday, July 3, 2012 - 2:31:08 AM - Karubum Back To Top (18318)

Hi,

I had an sql script in database by intruder. I import invaded database from "EXPORTDATABASE" in WEB Data Administrator and clean it.

When I try to import clean database from "IMPORT DATABASE" in WEB Data Administrator, I am getting the error as follows:

There was an error importing the database. The status of the import is unknown. Incorrect syntex near '>>'. 

I do not have such '>>' syntex in the coding-file. 

What should I do?


Tuesday, February 28, 2012 - 3:45:37 PM - Kelly Jones Back To Top (16192)

Perfect post. Here’s a tool that lets youbuild your cloud database apps without codinghttp://www.caspio.com/


Monday, January 10, 2011 - 8:38:35 PM - Jeremy Kadlec Back To Top (12559)

Wamberto,

Good question.  Do you meet all of the requirements on this page?

http://www.microsoft.com/downloads/en/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en

I did not see support for Windows 7.

What version of SQL Server are you using?

Thank you,
Jeremy Kadlec


Monday, January 10, 2011 - 11:36:42 AM - wamberto de farias Back To Top (12556)

I tried to install the web database admin, but i got an error: the setup requeries a Internet Information Server 4 or higher. I use the windows 7 with iis7. what should i do ??? thanks















get free sql tips
agree to terms