Web Based Database Administration for SQL Server

By: Jeremy Kadlec   |   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.
  • For more information visit the MSSQLTips.com - Administration Product Category
• 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.
  • For more information visit the MSSQLTips.com - Administration Product Category
• 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 - 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

Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009.

View all my tips

We Recommend

• More SQL Server DBA Tips...

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 Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis! Get your SQL Server database under version control now! Find out why... Is “blocking” a bad word at your company? Contact the Edgewood SQL Server Consultants for the resolution. Spring Clean Your Data - Clean your global contact data with Melissa Data tools for SSIS. Download a free trial! Optimizing SQL Server performance can be a daunting task. Or is it?