Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Web Based Database Administration for SQL Server

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (6)   |   Related Tips: More > 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.

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

  • 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.

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

  • Installing Microsoft SQL Web Data Administrator - No action.

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

  • 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.

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

  • 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.

  • 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.

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

  • 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.

 

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

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

 

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.


Last Update: 8/15/2007


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Monday, January 10, 2011 - 11:36:42 AM - wamberto de farias Read The Tip

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


Monday, January 10, 2011 - 8:38:35 PM - Jeremy Kadlec Read The Tip

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


Tuesday, February 28, 2012 - 3:45:37 PM - Kelly Jones Read The Tip

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


Tuesday, July 03, 2012 - 2:31:08 AM - Karubum Read The Tip

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?


Wednesday, September 12, 2012 - 8:27:06 AM - Bala Read The Tip

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



Monday, May 13, 2013 - 6:24:16 PM - 3Essentials.com Read The Tip

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.  



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.