January 29, 2009 at 6:26 am
Hi All,
I am a new DBA and I was wondering if someone can shed some more light on the following.
If one was working with a company which has over 700 databases, 150 instances etc. I like to check SQL logs every morning when I report to work, does this mean that I need to be checking the logs of about 150 servers, is there an easier way around this.
Also, I use management studio for all forms of management, If I reboot my computer, it means that I loose all the connections that I have made with all the instances, is there something that one can do which remembers all the connections, so that I don’t have to log onto every server one after the other again.
I would also like to get some more information about Clustering as my SQL 2005 book doesn’t go deep into it, what tool is used for monitoring clustering, does anyone have any document/webpage detailing how the system works and also some form of setup, also is clustering something that I might be able to try out myself even though I don’t have the cluster hardware, but I have IDE disks and the operating system.
Thanks in advance.
January 29, 2009 at 7:20 am
I would recommend breaking this post into pieces and ask each question individually. You will get more focused responses.
Here are some quick answers to these.
There are lots of commercially available monitoring tools ranging from simple ones to complex and expensive ones (MOM, for example) that are designed to monitor SQL Server and Windows servers for all kinds of things. Research a bit, include your server management group, and buy one (if your server management group does not already have one that will work).
For clustering, again, talk to your server management group. If you have an MSDN license, you can configure servers with virtual server or virtual PC and cluster them, then you just have to install SQL on the cluster. It can get complicated, so getting a book that covers the subject is a good idea.
January 29, 2009 at 7:25 am
I was at a bank with 68 SQL Server instances, 1 Oracle 9i and 1 DB2 the tools we used was Embacadero and Microsoft's System center operations manager, the files adjustments take less than 30 minutes in the morning because the data team does only data. There are system admins for IBM iseries and Windows because both Oracle and DB2 runs on 64bits IBM iseries. I cannot cover cluster because I was doing both Oracle and SQL Server as needed.
http://www.microsoft.com/systemcenter/operationsmanager/en/us/default.aspx
Kind regards,
Gift Peddie
January 29, 2009 at 8:43 am
Also, I use management studio for all forms of management, If I reboot my computer, it means that I loose all the connections that I have made with all the instances, is there something that one can do which remembers all the connections, so that I don’t have to log onto every server one after the other again.
This can be done with the Server Registration tool built-in management Studio.
It is under the View-->>Registered Servers.
You can add all the server instances you are managing in there; when you wanna connect to any server in the list, you only need to double click the server name and it will appear in your Object Explorer.
One good thing about this Register Servers list is that You can Export the registered server list as a file and then Import it to any other server. That way you don't have to Register the server one by one again, all you need to do is take the Exported file and import it.
To Import/Export, you only need to Right Click the 'Database Engine' in the Registered Server Pane.
It is free. 🙂
January 30, 2009 at 3:01 am
Divide your registered servers up into groups (right click in registered server and add new server group) so they group together eg Dev, test and live groups.
Then repeat the registration and groups bit for each of the Analysis services, Integration Services & Reporting Services and export each one so you don't lose it.
I manage loads of servers via management studio and rarely need to log on to the actual server. Ensure that you and your backup DBAs have the permissions to connect to each server via windows authentication (set up a DBAs windows group and use that not individual logins) and list and lock up the sa passwords in the fireproof safe.
Any jobs running via sql agent should email the DBA group if they fail (not just you as you take holidays) - try a test job that sends you mail on each server as part of the setup to ensure it works.
And ensure the master, model, msdb and report server databases are backed up on each server. Not doing this got a predecessor a rapid move sideways!
Keep all your SQL Scripts. Your script library is your most essential tool. Create new ones for such things as setting up a backup job - generate script from an existing job so you can just change the time and database name - I have one for each server.
Create restore scripts for critical databases and test them. Then they are ready for an emergency when you aren't as cool headed as usual.
Check all databases are backed up - I often check certain development servers comparing the list of databases to the list of backup jobs as I find developers and the network guys sneak a new one in when I'm not looking and never back them up.
Good luck.
January 30, 2009 at 5:46 am
If you subscribe to SQL Server mag, you can find an excellent tool built by Rodney Lundrum. His really wonderful SSIS package has allowed us to begin automatically collecting and centralizing everything from version and patch level to SQLAgent status, job history, disk space, log space and usage, and today we will lay the groundwork for centralizing error logs. We keep collected ideas from scripts from many folks whom we have lost track of but greatly appreciate their insights.
We have extended Rodney's brilliant work and monitor about 60 instances with the info emailed to us every morning. We maintain a considerable amount of data history so we can spot trends. Daily reporting is quite crude yet, because none of us are well versed in that, but we do have some really useful daily data, such as centralized backup, maintenance, and job failures, database growth, disk space changes, and of particular value is a rolling daily view of t-log growth and percent used.
We have intended to share our version this excellent monitoring tool also but have not gotten around to it. Contact me at rcoffman@bandag.com.
January 30, 2009 at 7:19 am
Hi can some one share the monitoring tool with me?
I would like to see and if I can would like add something to it.
January 30, 2009 at 9:50 am
DB Artisan - Embarcadero
SQL Diagnostic Manager - Idera
January 31, 2009 at 5:16 am
P Jones (1/30/2009)
Divide your registered servers up into groups (right click in registered server and add new server group) so they group together eg Dev, test and live groups.Then repeat the registration and groups bit for each of the Analysis services, Integration Services & Reporting Services and export each one so you don't lose it.
I manage loads of servers via management studio and rarely need to log on to the actual server. Ensure that you and your backup DBAs have the permissions to connect to each server via windows authentication (set up a DBAs windows group and use that not individual logins) and list and lock up the sa passwords in the fireproof safe.
Any jobs running via sql agent should email the DBA group if they fail (not just you as you take holidays) - try a test job that sends you mail on each server as part of the setup to ensure it works.
And ensure the master, model, msdb and report server databases are backed up on each server. Not doing this got a predecessor a rapid move sideways!
Keep all your SQL Scripts. Your script library is your most essential tool. Create new ones for such things as setting up a backup job - generate script from an existing job so you can just change the time and database name - I have one for each server.
Create restore scripts for critical databases and test them. Then they are ready for an emergency when you aren't as cool headed as usual.
Check all databases are backed up - I often check certain development servers comparing the list of databases to the list of backup jobs as I find developers and the network guys sneak a new one in when I'm not looking and never back them up.
Good luck.
Some really good advice there.
Just to add to this thread, with that many servers \ instances I would think some kind of monitoring tool would be in order.Idera SQL Diagnostic Manager is the first thing I look at when I get to my desk.
January 31, 2009 at 6:34 pm
rick (1/30/2009)
If you subscribe to SQL Server mag, you can find an excellent tool built by Rodney Lundrum. His really wonderful SSIS package has allowed us to begin automatically collecting and centralizing everything from version and patch level to SQLAgent status, job history, disk space, log space and usage, and today we will lay the groundwork for centralizing error logs. We keep collected ideas from scripts from many folks whom we have lost track of but greatly appreciate their insights.We have extended Rodney's brilliant work and monitor about 60 instances with the info emailed to us every morning. We maintain a considerable amount of data history so we can spot trends. Daily reporting is quite crude yet, because none of us are well versed in that, but we do have some really useful daily data, such as centralized backup, maintenance, and job failures, database growth, disk space changes, and of particular value is a rolling daily view of t-log growth and percent used.
We have intended to share our version this excellent monitoring tool also but have not gotten around to it. Contact me at rcoffman@bandag.com.
I haven't seen this article or the tool, but I implemented my own set of SSIS packages that poll all 110 instances of my SQL SErver environment daily to obtain a variety of information, DB online status, DDL modifications (implemented DDL triggers on the 2005 instances), last backup date/time for all DBs, job failure information for the previous day, etc. Based on the data that is collected, I put together a variety of reports that my entire DBA team has access to. We assign someone to review the reports each morning and address any issues. Our DBA jobs on each system are prefixed with "DBA_" so any job failures for DBA jobs are one report, all other job failures, a separate report. This eliminated the need to put job steps on jobs to email us, and has cut down on email significantly. The trick is that we need to look at the reports.
Overall, SSIS is very customizable and has worked well for my team, managing 110+ instances of SQL Server 2000, 2005, and soon 2008.
General layout of my team's solution:
1. Executed from a central DBA Admin server.
2. We maintain a list of servers and instances, and contacts for all DB's on each instance on our admin server, aka central repository.
3. Each package queries the central repository to get a list of instances to poll through.
4. A for-each loop goes through the list.
5. A connection manager is updated via script task in the for each container.
6. Data flow tasks pull the data from the instance connection updated in the script task, then pushed to our central repository.
7. Reports are read from the central repository.
Additional notes: I have different branches with the package to execute certain commands for SQL 2000 boxes compared to SQL 2005, with the ability to add another branch for SQL 2008 servers.
Best of luck,
Steve
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply