Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Mini-Me Expand / Collapse
Author
Message
Posted Friday, November 21, 2008 7:23 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:30 PM
Points: 810, Visits: 2,120
99% of the scripts we use are in house or customized from sites like SQLServerCentral.com

That being said, there are a couple third party tools that are worth their weight in gold such as ones that compare databases on a QA/Development Server with their eventual incarnations in production. Any script involving heavily used remote logins, I'd always be hesitant about.

That being said, there's a cool feature about SQL 2008 Management Studio I read about at http://www.sqlskills.com/BLOGS/KIMBERLY/post/SQL-Server-2008-Central-Management-Servers-have-you-seen-these.aspx where for example, you can right click on a group folder, such as where you have a group of QA Servers bundled up, and query that entire group. For example, right click on the folder, select new query, and a query window opens up. Any query done in that window is run against the entire set of servers in that grouping. This type of integration could provide DBA's more power to create even more powerful customized scripts.


Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Post #606508
Posted Friday, November 21, 2008 7:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, June 7, 2014 7:51 PM
Points: 42, Visits: 351
The queries help out alot. It's nice to know that a certain situation is in need of attention before it becomes a big mess. For instance there are times when a service gets hung so an email alert gets sent so the service can be restarted. These types of queries are invaluable when operating in a high volume environment.


Post #606532
Posted Friday, November 21, 2008 8:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 24, 2008 11:14 AM
Points: 1, Visits: 8
How much "checking" I build into my custom systems depends on time and money available to do them. I haven't taken the SQL Agent route, typically I have a Windows service that periodically processes various multi-step business transactions. As part of the cycle of jobs-to-do, I usually place my "checks" code at the end of each processing cycle. My service always uses stored procedures and I usually build a few that will cull my data looking for issues that need to be brought to someone's attention. When something is found, I send off an email to the appropriate personnel (I usually maintain in the database a list of emails tied to types of activities).

Transaction Status
I should also note that I create transaction tables that stores the various transactions within my system. As part of these tables I determine the main steps to complete the transaction and create a status date/time column for each step as part of the same transaction row definition. These columns are defaulted to NULL. I also create a Status Notes column that can contain textual status info. This defaults to "Waiting to be processed". When the process completes a step, it runs a sProc that applies the current date/time to the appropriate status date column and updates the Status Notes column with the results of that step. If a process encounters an error, part of the error handling applies the error info in the Status Notes.

Notifications based upon type of activity
Sometimes the issue is support-related and I'll send it to system support folks. Typically these are business transactions that could not complete and need some human eyes to help it along. Having the Status Notes column greatly assists these folks when they analyze the transaction tables.
Sometimes the issue is business-related, such as manufacturing jobs running late and I need to notify the appropriate business unit manager.

Use of SQL Agent
Many of my clients would not allow their SQL Servers to send email as part of their internal security policies. Some even go so far as to disable SQL Agent. I have to create systems within the confines given to me and if it's a 4' x 6' jail cell, so be it.
Post #606552
Posted Friday, November 21, 2008 8:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 11:36 AM
Points: 27, Visits: 136
We review our SQL Servers every morning looking for "Corrupted" next to the database name. We also have alerts set up to e-mail us. We were having some problems with our storage system and found out that a database can become corrupted with no alert being triggered, especially if there's been no activity against it. So we added scheduled jobs to run CheckDB against every database just before we come in to work. In my opinion this is a must, especially for databases that aren't necessarily accessed every minute or every hour.
Post #606558
Posted Friday, November 21, 2008 8:26 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 11:19 AM
Points: 459, Visits: 1,221
Great topic. Something I give a lot of thought to, and always looking for new ideas. In many ways, I think my responsibilities consist in large part of the following:
1. Prevent bad stuff from happening (Protect)
2. If it happens, become aware of it quickly (Detect)
3. Resolve the issues accurately and efficiently (React)

My company has a pretty diverse environment (SQL versions between 6.5 and 2005, multiple domains, both physical and virtual servers). That, in addition to having a very limited budget for DBA tools, has forced me to use a variety of methods. Three main ones come to mind, in decreasing order of automation:

1. SQL Agent jobs for emailing me about really bad conditions (ex: SQL errors with a high severity, xaction log about to run out of space) in real time. I have a job that runs early in the morning to do nothing but send me an email, to verify that both SQL Agent and our email infrastucture are functioning properly.

2. Custom SQL scripts using Red Gate's SQL Multi-Script tool to catch conditions that while important, can usually wait a few hours (ex: disk space getting low, missing backups, SQL Error log entries that the SQL Agent jobs don't report on). I run these scripts a few times throughout the day. Doesn't take too much time...

3. Server-side traces to catch things like unauthorized use of the sa account, poor-performing queries.

It would be great to be notified about everything automatically when it happens. That said, I still think there's many instances where a bit of at least somewhat-manual checks are warranted. The manual checks can act as an audit of the automated checks.


Brian Kukowski
Post #606566
Posted Friday, November 21, 2008 8:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 6, 2013 8:07 AM
Points: 109, Visits: 489
Yes, typically, I would have jobs on the various servers in Agent and they would normally email (we used SQLAnswersMail on SQL Server 2000 because it has nice HTML formatting and allows you to a lot of flexibility).

The jobs would email the appropriate people when they detected problems. Some would be daily, weekly or monthly. The emails were always designed to go to the people who would fix the problem and be sent on behalf of the people who could answer any questions (sometimes these are the same person!)). Preferably the bad data was a link to a web based application's maintenance page where the problem could be corrected.

I always liked to ensure that the addresses for the recipients were either a mailing list or stored in a table (or sometimes the email address was extracted from the system - like the person who was the rep for the customer) so that the job never needed to be changed for simple job assignment changes. In cases wherre the rep for a customer had to do something, there was a fallback so that if the rep's email address was NULL (or the rep was terminated), it would go to the department head (and then, if that wasn't available, to a DBA). So even the jobs were anticipating problems with the integrity of their email configuration, to some extent.
Post #606576
Posted Friday, November 21, 2008 8:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 6, 2013 8:07 AM
Points: 109, Visits: 489
And another thing - being proactive, I would never do this stuff twice. If I had to investigate a problem (and couldn't easily prevent it with a CONSTRAINT or something), at the very least, I would set up a job to email me about it - even putting the investigatory SQL I would need to figure it out in the email. The next time that job detected the problem, I would ramp it up into a full-blown system (based on that SQL) to identify who should be fixing these problems and give them as much information as they needed to get it fixed.
Post #606583
Posted Friday, November 21, 2008 10:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, June 8, 2013 9:40 AM
Points: 142, Visits: 286
We maintain a database called the Data Quality Engine (DQE) into which an operator can insert various kinds of tests that target data either on our SQL Server or Oracle systems. Most tests are run in a daily job and those results that fall outside certain boundaries are added to an email alert.
Post #606690
Posted Friday, November 21, 2008 5:54 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:14 PM
Points: 58, Visits: 676
We have a fairly large Access application with a SQL2000 back-end (about 120 users) which is one of our core systems - local government to manage properties, rates, and all sorts of applications (building consents, resource consents, etc.).

It's quite a configurable application, which is great in allowing us to configure it for all sorts of processes, but it's not so good at enforcing data integrity or ensuring users follow proper processes.

We have created a wide variety of audit scripts (I think we're up to about 200 now) that check all sorts of data entry, relationships, etc. Each can be scheduled to run daily/weekly/monthly, on any given day, fire emails back to the users to notify them to fix the data, and escalate the issues if they are left unresolved.

I'm sure there are plenty of tools around to do this, but we just created a simple DTS package with a couple of control tables and a series of scripts. Saves us heaps of time and helps identify repeat offenders, who might need further training, or just a damn good thrashing.


Chris
Post #606909
Posted Monday, November 24, 2008 9:09 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 19, 2014 8:09 AM
Points: 707, Visits: 393
I regularly write SQL queries to check for certain conditions in my data. We've developed a system used to automate the calculation of Teacher Bonuses that are based on Student Assessment Scores. The system basically is dependent on the right data being in the right place at the right time, which doesn't always happen. Therefore, we use a number of SQL queries to identify conditions such as mismatched data or missing critical data. Although the development of the queries consumed a lot of time, the queries continue to pay great dividends when used in a proactive manner.


Post #607646
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse