Database Monitoring

Can anybody help me with the following on my MS SQL Server 2000 database.

1. All tables should have a lastModificationDate column. Any changes and inserts should have the system time updated with a trigger or so. We shouldnt be inserting the value using SQL statements into this column.

2. There shouldnt be any deletes on the table. Any deleted records should be marked as inactive or deleted, so it wont come in queries, but should be physically present in the tables.

3. A modification log table, which will carry the table name, the column identifier, user modified, old value and the timestamp.

Thanks
Kishore1. I dont think u need to have triggers for this - just create ModificationDate field & set it as NOTNULL & Default value as CurrentDate

2. Do u want this also to happen in backend with out frontend access then... go for triggers for delete statement and set that to 1 or 0, but anyway u need to change the 'select' query to check the Falg feild.

3. Do u want this to happen in trigger... I would say pls dont go for that... I doesnt know well about ur appln... but if it have Millions of data and for maintenance purpose u run a backend query(update/delete with trigger attached)... just imagine how this trigger will work...

Database Mirroring Monitor

Hi there!

I've some trouble with monitoring a database mirroring session. I am testing a lot configuration possiblities, and i use the database mirroring monitor to view the mirroring states.

But in some (most) of my configurations, i only get following informations in the database mirroring monitor:

Oldest unsend transaction, Unrestored Log, Current Restore rate and the Witness Adress. I am working in High safety mode with automatic failover.

Question 1: What are the reasons that all the other indicators (Unsend log, Current send rate, Mirror commit overhead) are not shown?

Question 2: In the moment, i am using certificates for the mirroring communication. I think, my client (who the database mirroring monitor is running) connects with the usual windows authentication to the server and only selects from sys.database_mirroring or something else. So there is no need to create inbound certificates on the partners for the client who is connecting with the mirroring monitor?

Torsten

1. The counters that you say you cannot see are ones that are specific to the principal. Make sure that your connection from the monitor to the principal is good.

2.OK, sounds a little strange. Looks like the certs are what is messing things up. So, the connection from the monitor into the partners should be SA to make everything run nicely. But if the conneciton cannot be SA, then the monitor can come in as a normal login and get the data as long as the login is a member of the DBM_Monitor security group in MSDB. Note: the job that updates the base table for monitoring must be running as well.

Thanks,

Mark

|||

Hi Mark,

1. The connection is good, all related users have logins on the principle, are member of the dbm_monitor group, and are in sysadmin group.

In my opinion its not a problem of the database mirroring monitor, because the main stored procedure doesn't even show the results:

sp_dbmmonitorresults 'EASYRIS_41', 0,1

--> EASYRIS_41 1 4 1 NULL NULL NULL NULL NULL NULL NULL NULL 2006-06-21 12:38:44.547 2006-06-21 12:38:44.547 2006-06-21 14:38:44.547

Furthermore i've recognized that the Database Mirroring Monitor Job is located on the MIRROR (thats because the mirror has been the principal when i set up the session). Should the job run on BOTH partners? Furthermore the job is runnning under 'sa'. sa is not member of dbm_monitor group, and i can not put him into the group.

What is the exact recommendation? Is there any documentation i've not seen?

Thanks and sorry for the extra work ;-)

Torsten

|||

yes, you must run the job on both servers. if the job is running under SA then that's fine.

The user that the monitor logs in as can be in the dbm_monitor group if you do not want it to be SA. If it is SA, then the monitor should be able to update the info anyway...

mark