Posted on Leave a comment

sp_MSforeachtable

You can use the sp_MSforeachtable undocumented system stored procedure to accomplish tasks on all tables of a database such as rebuilding indexes or recomputing statistics.

This script rebuilds all indexes with a fillfactor of 80 in every table in the pubs database:

USE pubs
GO

EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 80)”
GO

Posted on Leave a comment

SQL Naming Conventions

Overall conventions

– Keep names short as possible, but long enough to be meaningful
– Reserve ALL CAPS only for SQL keywords such as SELECT, UPDATE, INSERT, WHERE, JOIN, AND, OR, LIKE
– Be generous in use of comments, but not distracting
– Do not use spaces in object names

Tables

– A 1-M or M-M named with the primary table name, an underscore, and the dependent table name (ie: user_permissions)
– A “logging” table can have a suffix of ‘log’ as in “userlog”, whereas a an “archive” table with the same structure as a “live” table can be named with an “_archive” suffix as “user_archive”.
– Name a primary key (or in the event of a compound PK create a surrogate identity or guid key) named after the table (ie: users.user_id).  Makes it easier to match up foreign keys to primary keys.
 – All tables should have some form of primary key, having a surrogate identity or guid key makes it easier for the application to reference specific rows.
– Except for the primary key, don’t repeat the table name in the column name (ie: users.username -vs- users.name).  This makes for annoying long and redundant column names.
– Table and column names should typically single words, be all lower case, and use underscores sparingly (with the exception of the primary key “_id” column).  If a column name is multiple words then run the words together such as “lastname” or if it is an commonly understandable abbreviation “lname”.  Another possible convention is to begin with lowercase and use title case for remaining words (ie: lastName).
– Avoid using reserved words for table and column name
– For transaction databases (OLTP) target 3rd normal form for normalization. Is best to normalize as far as possible to best support transactions.  If you don’t need a full blown counterpart OLAP database, then can  add some “rollup” or “aggregation” tables to support long running reports.
– Most “entity” and “lookup/list of value” Table names should end in plural ‘s’ (ie: users, accounts, locations) as a table is a collection of items as opposed to an instance of an item.

Constraints/Indexes

– Primary key index should be “PK_tablename”
– Foreign key index should be “FK_dependenttable_ref_primarytable”
– Alternate key indexes should be “AK_tablename” (for surrogate identity or guid field in the event of a compound PK)
– For all other indexes start with “IX_tablename” or “IX_tablename_colname”, if unique append a “_u” suffix, if clustered append a “_c” suffix, if clustered-unique append a “_cu” suffix
– Defaults are named “DF_tablename_columname”  

Triggers

– Triggers should be used VERY sparingly
– Triggers should be named “TR_tablename” or “TR_tablename_columname”, and append a suffix of “_i” for insert, “_u” for update, “_d” for delete or some combination thereof (ie: “_iu”, “_iud”)
– Perform referential integrity checks and data validations using constraints instead of triggers, as they are faster. Limit the use of triggers only for auditing, custom tasks, and validation that cannot be performed using constraints and would be impractical to enforce in stored procedures.  

Other Object Names

– Use “Pascal” notation for objects with a meaningful prefix (ie: vUserDetails, fnUsersGetDetails)
– Recommend prefix of “v” for views
– Recommend prefix of “fn” for functions, or could even go with “ft” for table-valued functions, “fn” for scalar-valued functions, “fa” for aggregate functions  

Stored Procedures

– DO NOT use “sp_” as a prefix for stored procedures, unless you are specifically creating a user defined system-wide stored procedure.  This is a naming convention reserved for system procedures.
– Use SET NOCOUNT ON at the beginning of stored procedures and triggers
– If you use a prefix for a stored procedures (sp), recommend using something like “p” for process, “r” for report, “u” for utility, etc
– When supporting an application that relies entirely on stored procedures (a highly recommended configuration btw!), it is helpful to incorporate the name of the module into the name and what function the sp suports (ie: pUserGetDetails, pUserSetDetails, rManningStatus)
– Typical action descriptions might include Get, Delete, Update, Write, Archive, Insert, Log along with the primary entity being impacted by the action
– Depending on scale you may choose one of the following conventions to take advantage of the alphabetical sorting in the tree view: uAction
– utility procedures are typically run by database-side administrators or an automated process pModuleAction
– supporting a single application, module could, for example, be the name of the .aspx page the sp supports (ie: pAccountEditGetUser, pAccountEditAddUser, pAccountEditDeleteUser) pApplicationModuleAction
– if your database supports multiple interfaces, then you may want to include the application name

Jobs

– Jobs should be named with project mnemonic name in all caps and brackets and then something indicating in general what it does (ie: “[PROJECT] WeeklyDataPull”)

General SQL Tips

– (NOLOCK) query optimizer hint can (and likely should) be used on most all SELECT statements
– In many cases EXCEPT or NOT EXIST clauses can be used more efficiently in place of LEFT JOIN or NOT IN
– In SQL Server Management Studio, turn on the “Include Actual Query Plan” and run the query. The query plan analyzer often suggests indexes.
– Do not call the same functions repeatedly, instead call them once and store the value in a variable
– Always access tables consistently in the same order in all stored procedures and triggers to avoid deadlocks.
– Use unicode data types like nchar, nvarchar, and ntext sparingly as they use twice as much space as non-unicode data types.
– Use a specific size instead of varchar(max) and nvarchar(max), as they are treated as out of row TEXT blobs and not as efficient in searching as using a specific size (except for instances where a text blob is truly appropriate)
– Minimize use of NULL where possible as they require additional complexity in queries to deal with
– Joins and common elements of complex queries that are used in multiple locations should be incorporated into a view.
– Avoid using ‘*’ in queries, always use explicit column names in your permanent code
– Be sure to explicitly DROP #temp tables and DEALLOCATE cursors as soon as they are not needed
– Using Common Table Expressions (CTE) are often (but not always) more efficient than using #temp tables and recursive functions
 – Using @table variables for small amounts of data are often (but not always) more efficient than using #temp tables and cursors
 – If you do not need a result set for a query, use a constant (ie: IF EXISTS ( SELECT TOP 1 1 FROM users WHERE id = 10 ) )
 – Indentation and capitalization is KEY to readability:
   

--
   SELECT A.col1
        , A.col2
        , B.col3
     FROM tabname1 A (NOLOCK)
    INNER JOIN tabname2 B (NOLOCK)
       ON A.col1 = B.col1
    WHERE A.col3 = 1
 ORDER BY A.col2
Posted on Leave a comment

Database Collation Change

Swiped from DrSQL

http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1333.entry

use [database]
go

Declare @fromCollation sysname
, @toCollation sysname
SET @fromCollation = ‘SQL_Latin1_General_CP1_CI_AS’ –or whatever
SET @toCollation = ‘SQL_Latin1_General_CP1_CI_AS’ –or whatever

SELECT ‘ALTER TABLE ‘ + quotename(TABLE_NAME)
+ ‘ ALTER COLUMN ‘ + quotename(COLUMN_NAME) + ‘ ‘ + quotename(DATA_TYPE)
+ CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then ‘(max)’
WHEN DATA_TYPE in (‘text’,’ntext’) then ”
WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN ‘(‘+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+’)’ )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),’ ‘) END
+ ‘ COLLATE ‘ + @toCollation+ ‘ ‘ + CASE IS_NULLABLE
WHEN ‘YES’ THEN ‘NULL’
WHEN ‘No’ THEN ‘NOT NULL’ END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN (‘varchar’ ,’char’,’nvarchar’,’nchar’,’text’,’ntext’)
AND COLLATION_NAME not like @toCollation

Posted on Leave a comment

Example MS Access Datasource

1. Log into Coldfusion Administrator

https://myserver/CFIDE/administrator/index.cfm

2. Expand Data & Services

3. Select Data Sources

4. Enter a value for Data Source Name, select Microsoft Access as Driver, Click [Add] Button

5. Fill in blanks similar to example below:

CF Data Source Name: radteammetrics
Database File: D:WEB~~BACKED_UP~~DevelopmentdbMYADB.mdb
ColdFusion User Name: Admin
Uncheck “Use Default User name”

Leave “System Database File”, and other fields not referenced above, blank.

6. Click [Submit]

Posted on Leave a comment

Reset Coldfusion Admin Password

To Reset a Lost Administrator Password:

1. Find the the file “neo-security.xml”.

This is in the lib directory of you ColdFusion MX install. For example in Windows it would be something like C:CFusionMXlib and on UNIX /opt/coldfusionmx/lib

2. Change the admin.security.enabled variable to false.
Before:

<var name=’admin.security.enabled’>
<boolean value=’true’/>
</var>

After:

<var name=’admin.security.enabled’>
<boolean value=’false’/>
</var>

3. Restart the ColdFusion process.

4. Access the coldfusion control panel. Depending on how you have it installed, the url should be something like: https://127.0.0.1/CFIDE/administrator/index.cfm

5. Change the administrator password and re-enable security.

5.1. In the control panel, scroll the menu down to and expand “Security”
5.2. Select “CF Admin Password”.
5.3. Check “Use a ColdFusion Administrative password”
5.4. Enter new password and confirm
5.5. Click “Submit Changes”

Posted on Leave a comment

Example OAS App deployment instructions

Installation Instructions

Prerequisites:
1. Platform:
Oracle Application Server 10.1.3
Oracle RDBMS 10.2.0.4

Database Deployment
1. Open Database folder on CD and follow instructions:
a. If deploying to Testing Environment read:
DatabaseTestingInstructions.txt
b. If deploying to Production Environment read:
DatabaseProductionInstructions.txt

Application Deployment
1. Log in to Oracle Server web interface.

2. If the group “{PROJECT_NAME}_Group” does not exist, create it.
a. Go to Cluster Topology and scroll down to Groups list
b. Create a new group
c. Name the group “{PROJECT_NAME}_Group”
d. Click Create

3. If the OC4J Instance for “{PROJECT_NAME}_Home” does not exist, create it.
a. Go to Cluster Topology and scroll to Members list
b. Click the top level Application Server
c. Click Create OC4J Instance
d. Set OC4J Instance Name: “{PROJECT_NAME}_Home”
e. Add to an existing group “{PROJECT_NAME}_Group”
f. Check the box marked “Start instance after creation”
g. Click Create
h. Ensure the instance has started

4. Create Database Connection
a. Select the OC4J instance “{PROJECT_NAME}_Home”
b. Select Administration tab, then JDBC Resources from list
c. Create Connection Pool
i. Under Connection Pool heading click Create
ii. Select “default” application from drop down
iii. Select New Connection Pool
iv. Click Continue
v. NAME: {PROJECT_NAME}_CONNECTION_POOL
vi. Use default connection factory class
vii. JDBC URL: jdbc:oracle:thin:@//{SERVER_NAME}:1521/{SID}
viii. USERNAME: {PROJECT_NAME}_APPLICATION_USER
ix. PASSWORD: {APPLICATION_USER_PASSWORD}
x. Click Finish
d. Create Data Source
i. Under Data Sources heading click Create
ii. Select default application from drop down
iii. Select Managed Data Source
iv. Click Continue
v. NAME: {PROJECT_NAME}_JNDI_CONNECTION
vi. JNDI LOCATION: java:/comp/env/jdbc/{PROJECT_NAME}Datasource
vii. CONNECTION POOL: {PROJECT_NAME}_CONNECTION_POOL
viii. Click Finish

5. Deploy the application
a. Go to Cluster Topology and scroll down to Groups list
b. Select “{PROJECT_NAME}_Group”
c. Select Applications tab
d. Select Deploy
e. Set Archive Location to path to file on CD at Deploy{PROJECT_NAME}_{VERSION}.war
f. Click on next
g. Ensure that Root context and application name fields are the same
h. Click next
i. Select Configure Class Loading (the pencil)
j. Under Import Shared Libraries ensure the following are unchecked:
{Note: list libraries not needed for specific application }
{apache.commons.logging}
{oracle.toplink}
k. Click Ok
l. Click Deploy

6. Configure memory allocation
a. Go to Cluster Topology
b. Select “{PROJECT_NAME}_Home” in the Members list
c. Go to the Administration tab
d. Select Server Properties
e. Set Initial heap size to {MINIMUM_MEMORY}m
f. Set Maximum heap size to {MAXIMUM_MEMORY}m
g. Click Apply
h. Go to Cluster Topology
i. Select checkbox next to “{PROJECT_NAME}_Home” in the Members list
g. Click Restart

7. Test deployment of application and database connection
a. Bring up application login page:
b. Attempt to log in. Contact {poc} for user name and password.

Posted on Leave a comment

Process Notes

Scribble notes here concerning issues, policies, standards, etc to later formalize into something more, well… formal.

All existing documentation should be considered “living” and “a-work-in-progress”.

If you find something that needs fixing, don’t ignore it or complain about it — FIX IT.

All deployment packages (scripts, instructions, data, etc) must be in-house reviewed and tested using development test server before signing for turnover!

Scripts and instructions need to be consistent and follow template.

Template should be starting point and will not cover all nitnoid particulars, but should be at least contain what is common to 80%+ of projects. If it needs updating, then quit gritchin’ and update it!

Keep tabs on processes as far as what works and what doesn’t work so we can develop and document best practices.

Complaining and whining is useless unless it comes with suggestions on how to do it better and those suggestions get recorded. The person doing the complaining is the best candidate to document the solution 🙂

Need to have a consensus between all of us, in that we need to have “same story” to tell people on what we do. Too many “this is how we used to do it” -vs- the culture change we are trying to promote — basically need everyone in our team to be on same page — BTW: what is it that we do anyhow?

Have to find a working balance between the following:

Providing continuity for a project.

Development teams like having a single point of contact to work with.

Not pidgeon-holing people into stove-piped technologies or projects

Progress on project tasks and DBA tasks must continue even when a member who is normally responsible for them is “out-of-pocket”

Where is the line between what a DBE is responsible for and the project developers are responsible for. Can or should it be described in detail or just in a general guidline?

What needs to be done, what we are willing to do, and what our manning and know-how can support.

DBA vs DBE
Do we want everyone to be jack-of-all trades and do both DBA and DBE tasks?

Do we designate certain people to do DBA stuff only or in addition to a lighter DBE load?

Do we have a designated “IT” person every week who is responsible for DBA stuff for that week?

Have to balance accountability for DBA tasks being accomplished with need to maximize the number of people who can perform them.

Need a formal agreement with administrators as far as what level of access and responsibility we have on production servers

Need to find a better solution to task tracking than what is in Outlook

Definitely need a “leadership dashboard” listing all projects with a rack-and-stack prioritization and some estimate of complexity/effort — we need this so that we can authoritatively prioritize and balance our workload. What authority says one project is more important than another? This will benefit testing as well.

There is no accountabilty for problems with a project and this leads to blamestorming and blamestorming leads to anger, and anger leads to hate, and hate leads to pointing the finger at us. The buck *should* stop with the project manager for the project — need to campaign for this to be policy.

Posted on Leave a comment

Database Office

Database Engineering Management
Assignes DBEs to projects
Assignes DBE and DBA tasks
Oversees task completion
Deals with problems and bottlenecks
Sets and enforces policies, standards, operating procedures
Establishes goals

Database Engineering Quality Assurance (QC)
Ensures all products meet standards
Ensures templates and procedures are relavent and up to date
Reviews and approves deployment packages (instructions, scripts, etc) and Software Turnover Notices

Database Administration (DBA)
Each duty day
Ensures development database servers are operational
Ensures development backups completed successfully
Performs development database server tuning and maintenance
Maintains inventory of development databases and schemas
Maintains documentation of database server configuration and processes
Acts as consultant for Testing and Production databases
Evaluates and implements technical requests that impact server level performance

Database Engineering (DBE)
Consultant to application developers concerning all things database
Assigned as project POC for one or more projects
Reviews and approves database schema
Develops stored procedures, functions, automation packages, jobs, remote connections, and processes that run on database server (back end)
Works with developers to perform data analysis, design database structure, and implement stored procedures, functions, and queries that interact with application (front end)

Designated DBE for Project (POC)
Primary point of contact between SCEO and project team for purpose of continuity
Responsible for coordinating and following up on tasks concerning project
Channels database requirements input to project design
Coordinates efforts of other DBEs concerning project.
Part of core team for project (ie: name is on quad chart)
Attends project meetings