Who needs it?Have you ever wundered how your SQL Server(s) were doing down on the 13th floor while your up on the 15th floor troubleshooting a workstation with no Enterprise Manager available on this side of the elevator?
Have you ever had to reinstall the OS on the PC you use to dial in from home and not have the SQL Server CD available, yet needed to check the system from home once the OS was reinstalled?
Have you ever needed to run a DBCC check_ident while you were in a long and important meeting with no time to get back to your workstation or the computer room without dropping out of the meeting?
Did one of these questions cause you to recall another situation where you we not able to get to an Enterprise Manager, but you desperately needed access to your SQL Servers?
If so, here is a solution. Virtually every PC has a browser. If you set up a utility that will give you the ability to check the status of your SQL servers and add, modify or run tasks in a documented SQL Server network from a browser then you will be better able to administer your system from anywhere at anytime as long as you have a browser available.
I use the admin subsystem utility in a distributed environment. While you could make full use of the tool in a one database environment, the prevalence of distributed SQL Server environments leads me to the conclusion that the materials offered here will be most relevant if presented in the context of the distributed environment. It should be relatively easy for you to determine which processes and concepts to use in a single SQL Server environment.
Everything I will show you is in use in a hybrid (OLTP/Data Warehouse) system with a remote hot site using SQL 6.5 SP4, IIS 4.0, and Windows NT 4.0 SP3. I have made an effort to make sure everything works with the latest Netscape and Microsoft browsers, but the reality is that this utility will provide too much power to risk exposing it to the internet. This means you can build your subsystem to work with only one browser product if you only use one in your enterprise. This is an important consideration for you because once you start using the architecture you build with the admin subsystem you will want to extend the functionality as appropriate to your system.
The first step will be to establish communication from the browser population you wish to include to the SQL Server where your scheduled tasks live. This will enable you to check the tasks from any of these browsers. Take a look at a small demonstration of the Active Tasks Status Inquiry. The buttons on the main menu have been abbreviated in the demo so that you can see a couple of possible scenarios. The opening scenario (the "Normal" button) is what you could expect to see when the administration tasks and the servers in the network are running normally. A second scenario (the "Broken" button) depicts a network with some problems. This should give you a good idea of how easy it is to keep an eye on the servers with this tool. (The demo represents screen shots of a system. No live system is connected to this site.) If you want this for your system, follow the instructions.
Once the first step posted and there is some feedback that people want more, you can expect to see details on documenting the SQL Server network in an admin database, scheduling SQL Server tasks from the browser, and complete source for scripts used to administer the SQL Server network.
I guess that's the catch. You gotta let me know if you want more.