administering the SQL Server centrally

[masds]

I took the SQL server DBA responsibility in our company,
I have more than 10 SQL servers or even more to administer in the future, how can I administer them centrally? use EM? Any tips for centrally administering many SQL server instances? Thanks

 

[JayKusch]

Using SQL built in tool, you would in deed need to use EM. Third party utilities would be like DBArtisan by Embarcadero.

Thanks

J. Kusch

 

[miq]

hi,
EM is a good bet considering easy to use graphically rich UI and multi-server jobs + centralized error reporting.

B.R,
miq

 

[Catadmin]

You want to download all the client tools to your local machine (including Query Analyzer). QA can do some things that EM can't and vice versa. Make sure to have both of them in your "pocket", Books Online and maybe even all the code samples. You can get this downloaded from the SQL CD by just choosing "Install Client Tools Only" and then do a custom install to make sure you get all the goodies you want.



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[masds]

Thanks for all these good advice, thinking of every SQL Server instance may have different backup jobs, to monitor these jobs and automatically test the backups will be a pain, I don't have a plan yet.

 

[Catadmin]

If you register all your instances in your Enterprise Manager tool, then monitoring is less of a pain, because all you have to do is open up each instance name and refresh the jobs every once in a while so you can see if they're running. With everything registered in one tool, it's easy to look at.

The main thing I keep forgetting is that just because I can see the instances, doesn't mean I can restore them or back them up to my local hard drive. I keep trying to do a restore, catching myself because I don't see my drive setup, then playing musical chairs with the backup files to move them to the proper instance I'm doing my restore to.

Lastly, make sure you have a test server, and not a test instance, set up for testing restores, etc. It needs to be a separate physical machine, even if you register it in your own version of EM. And Remote Desktop is a wonderful tool for when you have to log into those remote servers and check Event Logs, etc.



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[masds]

Thanks for all the help,

Catadmin, If one of these servers hasn't enough space for the coming backup, I should be able to get warning, do you have any ideas on this issue?

 

[miq]

HI,
you should configure performace alert on storage mechanism where data is stored (like local harddisk or shared disk) and allow it to inform you through netsend or email about low dis space. This is the easiest and the safest route because you would not appriciated a failed backup operation. proactive error handling is always a better approach then reactive.

B.R,
miq

 

[Catadmin]

Miq is correct. The only way SQL Server will tell you if there's not enough space is after the job has failed due to low disk space.



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"