March 2, 2011 at 12:35 am
Hi
When you have a number of SQL Server instances/boxes that you have to manage, how do you do this the simplest way? To you log in locally on each server when needed and run SSMS locally on the server? Do you run SSMS on your workstation and execute it with "Run As..." and an account with admin privileges on each SQL Instance and register them in SSMS?
I'm a bit curious how I can do this better.
/Andy.l
March 2, 2011 at 12:53 am
What is the number we are looking at?
Doing manually would be tough for each instance.
You could go for third party tools or build your own solution which would give the health check reports on daily basis.
M&M
March 2, 2011 at 7:38 am
Considering it is a large # of servers &/or instances, you have a few different options.
1 – buy a monitoring tool(Red Gate /Quest / Idera / SQLSentry)
Pro's – These s tools are built by people that know a lot about SQL Server. The tools require a bit of configuration to get things dialed in, but they explain a lot about what you are looking at. More importantly, when things go south, they will help you pinpoint the issues a little quicker, especially if you are a new to SQL internals.
Con's – These tools are priced on a per server basis(possible per instance for some out there), so if you have 50 servers to monitor, and the tool cost $2,000, you'll make the sales guy very happy. Of course I'm sure you'd get a volume discount. You will also pay a yearly maintenance/support fee. Don't take this the wrong way, these are very good tools, I have used and / or demoed each one of them extensively(Red Gate /Quest / Idera / SQLSentry).
But if cost is a factor, check this out from brent ozar and the guys from quest
http://sqlserverpedia.com/wiki/Central_Management_Server
I did this a little differently than brent because at the place I was at, we had a very strict change management policy. So instead of linking the CMS to all the servers, I linked all the servers to the CMS, because that was my server.
Then set up reporting services and nightly jobs to query the linked servers. This was to report failed jobs(backups / dbcc/ etc.). You could set it up to report back DMV stats, but that is what the management data warehouse is for. Or, check out the Utility Control Point in 2008 R2 (you enterprise for the UCP and it only reports on 2008 R2 and above).
Pro's – this solution is fairly cheap, provided you have an instance of sql server 2008 or better that you can build this on.
Con's – this will require manual upkeep to make sure the servers are all current.
Brent has a lot of good information on his site.
PS- There are many more pro's and con's for these tools, but you will need to weigh what you need with what you have, or what you can afford.
Hope this gives you a good start.
Chris
March 2, 2011 at 12:06 pm
Hi
For monitoring/management I use db watch from http://www.dbwatchsoftware.com so I have a solution in place.
My question is more practical for day to day operations.
To you log in to each server locally through remote desktop, or do you use SSMS locally from you local workstation and running is with "run as..." with an account with admin rights on al the SQL servers and just add them in ssms?
/Andy.L
March 2, 2011 at 12:47 pm
Depending what I'm doing on the server I will either use RDP or the CMS(Central Management Server). Typically I will just access any SQL Server using the CMS capability in SSMS from my desktop or my utility server. If you set the CMS up correctly it will help you stay organized.
BUT be very careful of which database the queries window is pointing at if you have multiple databases open.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply