Stored Procedure Checklist

DBAs find it easier to manage large Database Server environments when checklists are used. This Stored procedure checklist  will stimulate some ideas you could use

For acceptance into the SQL Server environment , I’ve developed some scripts that scan the T-SQL code for some items in this checklist

       1)  Same case for T-SQL Keywords. I prefer UPPER CASE , but I know other DBA\Developers like lower case.

      2)  Inspect input parameters for valid values (numbers, strings etc)

      3)  Force developers to justify  usage of  dynamic SQL (SP_EXECUTESQL, EXEC('SELECT col1…..')

      4)  Commenting. I prefer a comment block at the top with usage and revision history . Snippets throughout the code to enhance the understanding

      5)  Limit xp_cmdshell usage

      6)  Think in set theory. Avoid looping  such as cursors.

      7)  Clean up . Close objects ,drop #temp tables, deallocate\close cursors, close transactions

      8)  Use try...catch blocks. Trap errors and report into a log file

     9)  Performance is satisfactory

    10) Investigate input parameters for SQL injection attacks. Better if it occurs at the application level .

     11)  Manage  security. Follow the SQL Server Security Policy.Access to stored procedure and underlying objects

 

Author: Jack Vamvas (http://www.sqlserver-dba.com)

IT Job Search Engine

I've been working on integrating RSS feeds into a SQL Server db . Using OPENXML , you can get some powerful inputting , without to much data manipulation.  This http://www.itjobfeed.com/ grabs the feeds from a list , via DTS , scans for syntax errors  and then enters link and meta information into the db .

It would be useful to see more xml lists under certain sectors to have the same data structures.

They are inviting more companies,organisations,agencies to supply them with feeds to expand the search engine

ITJobfeed  was sold to a large job site company - and have now integrated the engine into their services.

Related Posts

OpenXML - more than 8000 characters

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Development

These are some basics when it comes to development on SQL Server.

Insufficient change control testing -Potentially the worst mistake to make, where developers will use a test case. Do a full TPC benchmark.

Unrealistic test environments - Provide an adequate db for the developers. One easy way is to export the production statistics into the test db

No change control procedure - In busy software companies, version control for schemas and sql code is paramount

Not enough testing instances- Thorough SQL developers often keep 4 environments, DEV for development, TEST for unit testing, QA for pre-production testing, and PROD for production. Any less and you risk missing errors

No performance tracking - With all the counters and alerts handling , there's no excuse for missing early warning signs, not to mention custom software packages

Poor security management - SQL Server offers a host of access control mechanisms. In dbs I audit, I still see obvious security holes

Non-standard external environments - This is a bad practice where every database uses different versions, inconsistent aliases and non-standard/varying file locations.