Database change policy and procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Database change policy and procedure

Database change policy and procedure

Rate Topic

Display Mode

Topic Options

Author

Message

joffwilliams

Posted Tuesday, October 02, 2012 2:24 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 14, 2013 11:36 AM
Points: 91, Visits: 149

Presently we have no defined process or policy for changes to SQL Server instances and databases in our environment. I'm working on creating some written policy and procedure. This is desperately needed, and a high priority.

I'm curious what the rest of the community does in your own environments. Specifically, these are the major changes I'm initially looking at process for:

- SQL Server installations and removals.
- SQL Server instance and database configuration changes.
- Backup and restore requests.
- Permissions changes.
- Use of server-level roles.
- Regular security audits and review.

If anyone would be willing to share how you manage these (or other major database-related changes) where you are (and possibly indicate the industry you are in and size of the organization) I'd be grateful. Thanks!

Post #1367283

foxxo

Posted Wednesday, October 03, 2012 7:51 AM

Say Hey Kid

Group: General Forum Members
Last Login: Today @ 7:06 AM
Points: 693, Visits: 1,028

I'm curious what the rest of the community does in your own environments. Specifically, these are the major changes I'm initially looking at process for:

- SQL Server installations and removals.
- SQL Server instance and database configuration changes.
- Backup and restore requests.
- Permissions changes.
- Use of server-level roles.
- Regular security audits and review.

Im in medium-large organisation and production changes are handled by ITIL practises. The change initiator or sys admin raises an RFC which is risk assessed and approved by all stakeholders. The change is recorded in a change calender on sharepoint.

Depending on the configuration change, this is normally approved by the DBA team leader.

Permissions and role changes are approved by the system owner or area manager in writing.

Audits / backups /restores are handled by a DBA.

SQL Installs are approved by the infrastructure manager to comply with licencing and DR resourcing.

Post #1367685

joffwilliams

Posted Wednesday, October 03, 2012 11:02 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 14, 2013 11:36 AM
Points: 91, Visits: 149

foxxo (10/3/2012)

Perfect. Thank you! Anyone else?

Post #1367868

brendan woulfe

Posted Wednesday, October 03, 2012 11:46 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 192, Visits: 915

I have yet to see a change request policy that I liked. Tongue

I will just say that if I was you I would try to write the policies so that they accomplish your goals without adding too much red tape. I worked at one company where the change requests got completely out of hand. The worst I had to deal with was 5 hours of phone calls, a couple face-to-face meetings, and several emails back and forth to get 3 rows added to a lookup table. Took up pretty much a full 8 hour work day for myself and 4 other people when the change was considered low-risk and only impacted a small % of customers.

This wasn't normal but it did happen. The backlog of change requests got so long that a minimum turn around time of 2-3 weeks was expected. This led to everything being an emergency ecm... w00t

This was the extreme case, but it's seems that these types of policies can get out of hand. Just my 2 cents.

Post #1367900

ScottPletcher

Posted Wednesday, October 03, 2012 11:56 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 4:39 PM
Points: 1,320, Visits: 1,772

Actually, Brendan, what you're described is what I've seen over and over.

While well intentioned, such processes usually end up being roadblocks rather than welcome centers (to stay with the road traveling metaphor).

It's very difficult to find the proper balance between proper review and overall speed.

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson

Post #1367915

joffwilliams

Posted Wednesday, October 03, 2012 12:36 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 14, 2013 11:36 AM
Points: 91, Visits: 149

brendan woulfe (10/3/2012)

I will just say that if I was you I would try to write the policies so that they accomplish your goals without adding too much red tape.

Glad to hear this perspective. The goal is to avoid either extreme: having no policy (which is where we are now), or being handcuffed by excessive policy. It's real easy to swing from one side of the pendulum to the other. I expect to begin with a handful of simple policies and only develop more as issues arise.

Thanks for the input so far! Any more would be very welcome.

Post #1367958

ScottPletcher

Posted Wednesday, October 03, 2012 4:11 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 4:39 PM
Points: 1,320, Visits: 1,772

Backup and restore requests.

I've done away with a lot of the "requests" -- just provide 'em a stored proc that can be run to do the backup. The stored proc enforces a naming scheme for the backup (i.e. they tell you the db name they want backed up, but not the backup file name).

Generally the same for restores in dev/qa, if it is a reasonable size.

Tell em if they abuse it, you'll cut them off.

One less cycle of requests you have to deal with Smile

.

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson

Post #1368080

vikingDBA

Posted Tuesday, October 09, 2012 2:26 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:19 AM
Points: 189, Visits: 863

I'm a one-man-band here myself, but I would suggest that you start off small, maybe with a policy that requires at least the documentation of all things done in that regard. That will give you an inside look at what is happening, who is requesting it, and whether anything goes wrong when it is done.

You can then look into actual approvals, etc. later if it is deemed necessary.

Post #1370581

joffwilliams

Posted Wednesday, October 10, 2012 9:19 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 14, 2013 11:36 AM
Points: 91, Visits: 149

Thanks for all the input! I will be publishing our policy in the next couple of weeks, and I believe it is better for the information in this thread.

Thanks again.

Post #1371003

tafountain

Posted Wednesday, October 10, 2012 11:24 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:59 AM
Points: 104, Visits: 333

Foxxo is right on track. By following ITIL practices changes are classified to levels (1-3) as follows:

Level 1's are presented to your Change Advisory Board (CAB) by the Change Manager (CM) with the support of the necessary stakeholders of the request. Requests that require this level of review usually are larger scope and have one or more significant impacts to the organization (costs, time / effort of various resources, etc).

Level 2's are not presented to the CAB. The CM can approve these on their own. However these are significant enough to justify a project based on visibility.

Level 3's are standard changes. These are requests that can be done immediately. These are recognized changes in the organization that are typically repeatable and have procedures and work instructions identified for how to complete the task.

This is just a nutshell explanation but this is basically how you avoid the red tape and manage changes in IT effectively and responsibly.

Post #1371057

« Prev Topic | Next Topic »

Permissions