I need help with defining development standards and practices

Question

I need help with defining development standards and practices

Bryant Tolar

SSC Enthusiast

Points: 151
More actions
March 14, 2006 at 7:02 am

#113741

Hello all,
I am the senior dba at a shop that has recently experienced a great deal of growth.
We are also changing our focus from simply supporting applications to developing our own.
This is making it very apparent that we need a well defined set of standards (T-SQL) and practices (Procedures for object creation, security, etc).
This is a new task for me and I was wondering if anyone out there new of a good reference.
Thanks
Bryant

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

As always, there is a fine balance between standards/guidelines and the overhead of having to enforce them. I found these helpfull:

http://www.sql-server-performance.com/vk_sql_best_practices.asp

http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLServerDatabases.aspx

http://msdn.microsoft.com/practices/

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Bryant Tolar SSC Enthusiast Points: 151 More actions · Answer 2

Bryant Tolar

SSC Enthusiast

Points: 151

March 15, 2006 at 8:47 am

#626798

Thanks,

The links were a great starting point

Wayne-153714 Hall of Fame Points: 3536 More actions · Answer 3

Some standards we've found useful here:

1.When replacing a stored procedure or function, rename the old version using sp_rename (assists with speedy rollback and version control). Rename using sprocname_WF_20060309_CommentClearquestProject. Cleanup the old version history every now and then by scripting the old versions and deleting them.

2.Script all your stored procedures, tables, user defined functions and views and add them to VSS. Update VSS manually whenever you modify an object.

3.Comments in all stored procedures and functions.

4.Use Operations database only as a play area for temp tables. Clean out this database when it gets too large. Standard is WF_20060309_ThisIsATest or HermesCustomer_WF_20060309_Testing.

5.Use Archive database for permanent backup of data. Every time you update data in a table, backup the data to Archive using tablename_WF_20060309. Also add a new line to ArchiveLog table in Archive database for auditing purposes. This is to facilitate easy rollback in case of error. When the database gets too large back it up, move the backup file to ndbackup01 and drop all tables.

6.If you are not a sysadmin or database owner, always use dbo when creating tables and stored procedures. If not, your login becomes the owner, you start having multiple versions of tables, owner chaining, and the next time you need to move to a new server you need to drop them (you can’t drop a user if the user owns objects).

7.Use dbo when executing stored procedures – this is to speed up performance, else the execution plan gets copied to your user every time you run it.

8.When you have an object (table, job, etc.) that you will not use again, but you would rather not delete it, rename it and prefix it with ‘z_’. The lets everyone know the object is obsolete, and it always appears at the bottom of your listing – effectively ‘hiding’ it from sight.

When in doubt - test, test, test!

Wayne

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

A few things to add no matter what the source for your 'standards' that really should apply anywhere:

There must be:

management buy-in and support
a process associated with the 'standards'
enforcement of the 'standards'
room for exceptions

Remember, 'standards' are things that need to evolve with your organization, sio there must also be a comittment to revisit (update) them on a periodic basis.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

Rudy is correct.

You need a process for the first place. and you need to understand what standards could be technically implemented and what standards could be process-based implemented.

Example: you may create a stored procedure renaming procedure that will append a certain prefix or suffix to the name. This is a technical implementation. Give developers access to the code only through VSS is a technical implementation. Give only a senior DBA rights to create databases is a technical implementation. Enforce a standard that this senior DBA will use a certain convention to name these databases is a process-based implementation.

You will also as Rudy says need room for exceptions because the third-party products may come with their own names and standards. Also, the new employees may come with their own preferences and it will take more time to re-train someone to use the new standards than for this someone to develop the application.

I would enforce 2 things: 1. comment your code as much as possible and 2. Check with BOL for the things that may be obsolete in the future releases and don't use them: like Internet Data Connector, sp_adduser, older outer join syntax with =*

Regards,Yelena Varsha