July 29, 2015 at 8:50 am
I am hoping for some general guidance on best practise around global SQL server management.
Scenario is as follows: I work for our Head office and we have multiple businesses out there around the globe, some of which have SQL servers, all at different versions, some setup well others not so good. We want to start managing the servers centrally so we can control best practise and identify issues etc.
I have started by generating a list of servers and am currently getting usage details and assigning local owners.
Ideally what I now want to do is setup a Central management Server and start creating policies so I can evaluate our SQL servers against them.
My questions are:
1.Central Management server only uses Windows authentication, is it bad practise to have one windows account which has sysadmin on all the company SQL servers? I think I will need that level to be able to evaluate the policies.
2.Does anyone have any pointers or advice for how this kind of thing is usually done?
Many thanks in advance for any help
David
July 29, 2015 at 10:17 am
1. No, I've done this. However I've usually had a "group" that has access as sysadmin. My view is that domain admins should have access. If I need remote hands, I need them. However some prefer it's just DBAs.
2. I tend to ensure each instance manages /monitors itself. While it's good to have something like SQL Monitor watching the environment, if I lose connectivity or my monitor machine goes down, I want some things monitored on the local instance. While sending policies out from a central place makes sense, I find I have to allow for lots of exceptions. I also want some things like backups, space, some stats captured locally, regularly, but each instance.
July 30, 2015 at 2:40 am
1. It's fine. Though technically if you're only using it to come up with standards, you might set up a service account and give it just the permissions it requires (e.g. view server state, and a few others). We do that for example for the SCOM monitoring accounts.
2. It goes pretty much how you've described. Split things up into what you want to know, which for us is:
- Backups
- Integrity / Maintenance
- Capacity
- Agent Jobs
- Security
Write scripts to gather relevant detail remotely from each server each morning and stick them into a centralised server. Then build more scripts to report off of them as you find issues. Then you can build policies to evaluate, pick them up, and fix them (using EPMF - though I don't think highly of it, it's all there is).
August 5, 2015 at 1:41 am
Thanks for the replies.
We have decided to setup the following structure for our DBAs:
Global DBA Group – sysadmin on all our SQL servers at all the sites.
VERY selective membership – limited to logins which will be used in emergency situations for support requirements. Management Approval will be required for Servers with DBs containing sensitive data.
Local DBA groups per site – sysadmin on local site SQL servers
Membership of these groups are up to the SQL server ‘owners’ (Someone based at that site).
Global CMS Group – Specific permissions on all SQL Servers to be able to evaluate policies via Central Management Server.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply