January 11, 2010 at 11:53 am
I am an accidental DBA. I enjoy it and would like to bring our organization up to speed with good documentation and best practices. I was never mentored and mostly self taught but have taken a couple of courses. I have used SQLServerCentral for multiple questions. It is a superp resource.
With all of that said, I was wondering if anyone had and wouldn't mind sharing their documentation for Standard Operating Procedures and Best Practices. I was wanting to design and implement a proper and secure SQL S.O.P. and Best Practices for my company. Starting from scratch is a little overwhelming and scary. I think I know mostly what I am doing but would love to see how others have documented and implemented their environments. I don't know how often different organizations share documents of this sort but would love to at least have some template or starting point for my own documentation.
Many thanks...
January 11, 2010 at 12:11 pm
I can't share my company's documents, because they are IP of the company. However, I can say, the best place to start is probably MSDN. They have a lot of baseline "best practices" and recommendations that are generally applicable.
The second step from there is documenting the business policies on the subject. For example, there needs to be a data loss policy. How many minutes, hours, microseconds, days, months, or whatever, of data is acceptable loss to the company? That will be key to determining your policies on DR, including backups, colocations, log shipping/replication/mirroring, and so on. But the first decision in that regard is a business decision, not a technical one.
Of course, the first reaction is likely to be, "we can't lose any data ever under any circumstances, no way, forget it, why are you asking such a stupid question". Then you have to go into how much it will cost to have dedicated lines set up going to multiple remote colocations, high enough speed to do full replication over those lines. Then there are server costs, maintenance costs, personnel costs in order to have 24x7x365.25 coverage at multiple sites, and so on. Once they realize that it will cost a couple of fortunes, they'll come back down to Earth and, most likely, begin to look at possibly shipping the backup tapes to someone's garage at the end of every week, or something like that, as their DR plan.
And so on, with each subject that a DBA needs to get from the business. For example, do they expect you to be available 24x7x356.25 by phone? What about if you're sick or on vacation in Antarctica, where they don't have cellular coverage? Do they expect you to work from home if you are sick and they need you to fix something? Are they paying for a VPN solution/do they already have one? Do you VPN from your home computer, assuming you have one, or do they provide you with a laptop or something comparable just for that contingency? If so, who is responsible for making sure the laptop or your home computer complies with security policies like virus definition updates and firewall needs?
There are a lot of business decisions involved in defining your policies and practices. Mainly because a lot of them involve money decisions. Others can end up having legal ramifications. (Do they get sued if a database goes down? If so, are you fired, or do you have something in writing from the business that says they don't expect 100% uptime from you?)
Start with those kinds of things. They'll matter the most in the long run, and are what you have to base a lot of your other decisions on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 11, 2010 at 2:06 pm
I have been looking at documentation like you suggested. One such document suggests using different accounts for the SQL services. How many different services are created if everything was installed for SQL? Eight or so? Do most people use AD accounts or local accounts? Do you put them in an AD group or Local group for anything in particular? By the way, I was referencing the bottom of page 6-8 on the linked document. Particularly the middle of page 7. It suggests a domain account for each service if you are on a domain. Do I need to create a different account for each service or use one? From what I have read, it suggests each service has its own account. But what about multiple servers or multiple instances. What do you suggest then?
These are just a few of the gazillion questions that I need to research and implement. Seeing how someone else organized their documentation and what standards they regularly adhere to would go a long way in helping out getting started on our own policy and procedures.
I guess I see why you say it is IP (Internal Property?). It requires a lot of work to produce a document of this caliber. I guess I was looking for guidance on how to put it together (topics, headings, sections) as well as the content. I do read MSDN as well as my daily SQLServerCentral emails and other articles about SQL. Just hoping for a base template and a kick start in the right direction. Not to mention I don't trust my decisions on every topic of SQL as good enough to set a company standard. I know a good chunk and research everything as it comes up, but you can't beat the experience of those that have been there.
January 11, 2010 at 2:34 pm
The questions on accounts will depend on your security needs.
It's doubtful that you'd need different accounts for the same service on different servers. You might, if they need to have different rights for some reason, but that's a bit of a stretch in my opinion.
The whole point on that is that, for example, Reporting Services probably doesn't need the rights to create files on your backup server, but SQL Agent almost certainly does. Reporting Services won't need to create files there, but SQL Agent will probably be running your backups, so it will. Logically compartment what they need to be able to access, and you'll see what needs to be done. If you think with it that way, it'll become clear. "What does this service need to do?" = "What rights does the account for the service need to have?"
Generally, I'd go with one account for Reporting Services, one for Analysis Services, one for SQL Agent, and one for anything/everything else. Reporting Services and Analysis Services are the ones most likely to be at least somewhat exposed to security issues. SQL Agent is the one most likely to be subject to a security escalation attack (where some developer, given limited rights in SQL, uses an SQL Agent job to grant himself unlimited rights). And so on. Each one should have what it needs, and no more (and no less, of course).
As for documentation layout, the main thing is to make sure everything is in there. A layout should generally include sections for:
name of the policy,
date it was written and by whom,
effective dates (from and to, or "permanent", that kind of thing),
who it applies to (departments, job titles, whole company, etc.),
who is charged with enforcing it,
outline statement of the policy,
why it was written,
a details section that can be added to or clarified as needed
Beyond that, just make it look good, and make sure it reads easily, but still covers what it needs to. Lawyers are good at making it say what it needs to, but tend to leave it unintelligible to non-lawyers. You want to avoid that, but completeness is critical, so learn to ballance the two.
"IP" = "Intellectual Property", meaning it's a written document that someone controls the rights to. Like anything copyrighted or patented or trademarked. In this case, it means I'd have to get permission from the company lawyers to publish copies of internal policies, and that's not going to happen.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply