March 13, 2006 at 10:43 am
Ok....bear with me....I'll try to explain what I'm trying to do. We have two web servers. One production and one development. We have two sql servers...again, prod and dev. I want to setup an alert that will notify me whenever one of our developers manages to push code to either prod or dev that is pointing to the wrong sql server. Doesnt happen often...in fact it only happened once but it was enough of a mess that it prompted us to think of ways to get alerted whenever this occurs. Any idea?
I cant find anything that shows eg. WebServer-1 is connecting to SqlServer-2 etc. Does this make sense?
Thanks in advance.
btw- Using MS Server 2003 and MS SQL 2005.
March 14, 2006 at 8:33 am
I'm not sure about how to do something like that in SQL2005, but in SQL2000, you can periodically check master.dbo.sysprocesses. The hostname column will (usually) tell you the name of the machine that is connected to a particular process. It does for every web server that I have connected, anyway.
Try creating a scheduled job that does something like:
If Exists (Select * from master.dbo.sysprocesses where hostname = 'TESTWEBSERVER')
EXEC YourAlertProcedure
It is probably very similar in SQL2005.
IMHO the better solution is to have a physical device (firewall, e.g.) that prevents cross-environment access.
I have never tried anything like what I am suggesting with the scheduled job, so proceed with caution.
jg
March 14, 2006 at 8:38 am
Excellant thank you!
The firewall idea will be in place at some point....but until we get the production servers out to our offsight facility they will be living on the same network. Even if I dont impliment your script idea the new knowledge of where to go to find that kind of information was most helpful!
Thanks again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply