Problem
As your IT team grows more and more people may have direct impact on your SQL Server databases. At the onset it may have been just a couple of developers and a DBA, but as your company becomes more successful and more resources are put on developing, maintaining and supporting a database driven application the potential for inconsistent processes and undocumented changes increases. Just when you need more control and structure things tend to sway the other way, because now there are more people and components that need to be managed. So what is the best process to put controls in now so when things grow beyond one or two people chaos does not follow?
Solution
With most IT processing it is often essential to document your policies with standardized procedures. In most cases there are already established rules, whether they are formalized or not there is some type of rules or procedures that are followed on a day to day basis. One potential problem with this un-formalized approach is that one person may have their set of procedures and another person may have a totally different set of procedures. As mentioned already with a small group of people this may not be as big an issue, but as more and more people perform the same tasks you are bound to have further discrepancies.
Define a standardized format
The first step in formalizing the process is to document your already established rules, although they may be loosely defined there is some process that you are already following. In order to document your policies and procedures it is helpful to have a standard format that you follow. This can simply be a Word document or database application that you develop. Whatever the approach, you should establish a standard template for documentation.
Here is a simple format that can be used to document your policy and procedures. This is just a starting point and you can add or remove whatever items make sense.
| Policy | Name of the policy. |
| Effective Date | Date policy became effective. |
| Purpose | Short description of why this policy has been created. |
| Affected | Individuals or groups of people affected by the policy. |
| References | Reference to other existing policies |
| Definitions | Definitions of certain key words to provide context. |
| Procedures | Process that should be followed. |
To take this a step further, here is a sample policy for changing the “sa” password.
| Policy | Quarterly SQL Server “sa” password change |
| Effective Date | 8/9/2007 |
| Purpose | The SQL Server “sa” password is used only as a last resort to access the database server. To ensure that this password is secure and not used by unauthorized users the password will be changed on a quarterly basis. |
| Affected | SQL Server database administrators |
| References | Policy 56B – Use of SQL Server “sa” account Policy 424A – Strong password policy |
| Definitions | “sa” – the default SQL Server system administrator account |
| Procedure | The password should be changed as follows: 1. Changed on the first Monday of the following months (January, April, July, October) |
As you can see from the sample policy this is not that difficult. Although this example is pretty simple, if you document things in a step by step approach you should be able to document most procedures without much problem.
You may also think that since you are the only one responsible for maintaining your systems that going through this process is not worth it. Think back to a process that you ran a year ago and you know that you will need to run again. Wouldn’t be easier to just document the steps now and then revert back to the policy when you need to duplicate the steps? Also, you need to think about the potential for more people coming on board and the impact this will have if your policies are not documented. Also, if you document the policies now, when new people do come on board you can ensure that they follow what you have already outlined by just giving them the well documented procedures.
One common pitfall when documenting things is the assumption that other people know what you are talking about. Based on this it is very easy to gloss over the details and just write some high level procedures. Therefore it is helpful to have someone else review the process and even approve the process. By having someone else read and understand what has been documented there are at least two people now that understand the policy as written.
Standardized Approach
Now that you have formalized some of your processes you can guarantee that the approach that is used is standardized as well. This can really be for just about everything that affects SQL Server such as:
- applying service packs
- deploying database changes
- adding logins and users
- sysadmin level access
- object naming standards
- coding documentation standards
- standard login passwords
- use of standard logins
- etc…
As you can see a lot of the things that you do on a day to day basis you already have a standard approach for how to address and tackle these issues. Take the time and formalize the approach and ensure that going forward everyone adheres to the same rules.
Next Steps
- Determine the documentation format you are going to use
- Determine how this will be stored (Word, PDFs, database) and where it will be stored so everyone has read access
- Start defining policies for your simple tasks before you take on the hard ones
- If you are the only DBA start the process now, before more people come on board
- Now that you have a framework start documenting
Greg Robidoux has been working with databases for 35+ years with extensive hands on SQL Server experience from version 6.5 to 2025. He has authored over 250 technical articles and delivered several presentations online and at various conventions. Greg is also the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server.
