DEVELOPER BEST PRACTICES / GUIDELINES 1

[jrc1]

I am a DBA trying to developer a list of standards/guidelines for our developers to follow. Included on the list is object naming conventions, database security and object privileges. One example is not using sp_ for user created stored procedure (recommend using usp_). Another example would be having dbo as owner of user created tables and not db user.

Has anyone else developed a list that they can share or know of some good resources to help define such a list???


Thanks is advance,
Jeff

 

[MeanGreen]

Here are a few sites with references to what you are trying to do:

UI Standards:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart01/html/sa01i1.asp

Programming Specifications:

http://web.comlab.ox.ac.uk/oucl/publications/books/PfS/

Hope this helps.

 

[SQLSister]

Some things to think about in naming. I agree about using USP instead of SP, but we also require the middle part of the name to specify what tasks the stored procedure does and then end with a name that will help us identify the module or function. so if the stored procedure does only select statements for the airportsearch it would be naemd something like:
USP_SELECT_AIRPORTSEARCH
but if it did an insert or an update for the restaurant admin, it would be named:
USP_INSUPD_RESTAURANTADMIN
Any object that is created stricly as an experiment or for testing purposes will have the word test in the name somewhere. This helps me identify objects that do not need to be moved to production.

Other thoughts - I require all database access to be through stored procedures and never ad hoc queries written in the user interface code. This enables me to see exactly what data is being accessed, to help optimize the code, to set permissions on stored procedures rather than directly on tables and to know what might be affected by any database changes. This is also faster for data access. The downside is that you do have to give your developers access to the database to develop from, but I believe they should have that access anyway to the development database. As long as they do not have the same rights on production, this should not create a problem. DBAs are the only people who have the right to change the production database. As far as cahnges to the database structure, only dbas can do that altough the developers can create a test table to try to figure out what they they to do a particular task. THey just can't change or create a table or view that they are really going to use. Developers can and do create stored procedures and functions. Senior developers will be allowed to create items with little supervsion by me (although I will review their code to see what is being done in the database). Junior programmers are only allowed to create simple stored procedures (which we specifically specify to them that they can create, they don't create any procedures without me knowing in advance) and all their code is checked by mee before being released to testing. When they work on something that requires a complex stored porcedure, one of the senior people writes it and then goes over it with them to ensure they understand how it works.

 

[jrc1]

SQLSister,

Thanks for the reply. Do you have any set procedures for Developers accessing the master db specifically xp_sendmail and xp_cmdshell. For xp_sendmail I created a SP that calls xp_sendmail and granted the Developer access to the SP.

Also any thoughts on DTS pkg owners, naming conventions, etc..

Jeff

 

[jrc1]

Does anyone have any set procedures for Developers accessing the master db specifically xp_sendmail and xp_cmdshell. For xp_sendmail I created a SP that calls xp_sendmail and granted the Developer access to the SP.

Also any thoughts on DTS pkg owners, naming conventions, etc..

Jeff

 

[tlbroadbent]

Check out the ressources on Narayana Vyas Kondreddi's web site. He has articles about developer standards and adinstration standards.

SQL Server administration best practices

http://vyaskn.tripod.com/sql_server_administration_best_practices.htm

Database coding conventions, best practices, programming guidelines

http://vyaskn.tripod.com/coding_conventions.htm

Database object naming conventions

http://vyaskn.tripod.com/object_naming.htm

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.