By: Jeremy Kadlec | 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.
- 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 - Installation and Configuration |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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.
About the author
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
