March 14, 2006 at 7:02 am
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
March 14, 2006 at 7:55 am
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/
March 15, 2006 at 8:47 am
Thanks,
The links were a great starting point
March 15, 2006 at 8:51 am
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.
March 15, 2006 at 2:43 pm
A few things to add no matter what the source for your 'standards' that really should apply anywhere:
There must be:
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."
March 15, 2006 at 3:27 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply