The following presentations were given at a local PASS Chapter.

September 2011

Topic:

How to audit and prevent unwanted user actions.

Abstract:

Many corporations are composed of small divisions located in countries throughout the world. While you might be the lead DBA for your corporation, there are several other employees who have the keys to the kingdom. This presentation will review techniques on how to prevent and/or audit data and schema changes.

Coverage:

1 – Granting correct user access is vital
2 – DML triggers to keep a DATA audit trail.
3 – DDL triggers to keep a SCHEMA audit trail.
4 – Preventing unwanted DATA modifications
5 – Preventing table TRUNCATIONS
6 – Preventing unwanted SCHEMA changes
7 – New Feature (2008) – change data capture

Details:

presentation
script

August 2011

Topic:

Working with bit patterns.

Abstract:

In today’s manufacturing environment, production lines are automated with robotics and sensors. Many of these low end microprocessors and/or integrated circuits are designed for specific tasks such as temperature and pressure control. This presentation will review how to store the memory buffer in a table and use a view to interpret the results.

Coverage:

1 – Using the VARBINARY data type to store the bit pattern.
2 – Storing a version number just in-case the decode changes.
3 – Breaking the pattern into registers and nibbles.
4 – Left shifting of the bits.
5 – Right shifting of the bits.
6 – Combining bits from two registers.
7 – Putting it all together with a view.

Details:

presentation
microprocessor layout example
script

May 2011

Topic:

Designing Custom Maintenance Plans with TSQL.

Abstract:

Have you ever received a call from a client asking for help when their SQL Server database is corrupted? You suggest that they restore the database from the last good backup. There is a long pause on the phone as the client states that the only backups/tapes they have are bad. Do not let this situation happen to you!

We will review how to build a custom maintenance plans from the ground up using TSQL commands. What are the best practices for daily, weekly and monthly tasks? This presentation includes tape rotation schemes and restoring those backups to make sure they really work.

Coverage:

1 – Verify integrity of databases.
2 – Backup databases (full versus differential).
3 – Backup logs
4 – Maintain database indexes.
5 – Maintain index/column statistics.
6 – Remove older data from [msdb].
7 – Remove older backups from file system.

Details:

presentation
scripts

March 2011

Topic:

Leveraging Transaction SQL 2008 to solve business problems (Part I)

Abstract:

This presentation will review how to build a database solution from the ground up using SQL commands. What if you were a SQL Server developer at a local consulting company tasked with creating a database for a local Boy Scout of America (BSA) Troop, where would you begin? The following topics will be reviewed to make sure you have a successful data storage solution.

Database Crafting:

1 – Defining file groups for the database,
2 – Creating schemas to separate tables by functionality,
3 – Creating normalized tables to hold the BSA data
4 – Adding data integrity to the design with constraints/triggers
5 – Adding referential integrity to the design with primary/foreign keys
6 – Adding logins/users and granting rights

Data Loading:

1 – Loading data into staging area with BULK INSERT, OPENROWSET or BCP
2 – TSQL statements for TRANSACTIONS, CONTROL FLOW, and ERROR HANDLING
3 – Writing Stored Procedures to move staging data to production
4 – Schedule Procedures using SQL Server Agent

Granting Access:

1 – Using stored procedures to lock down CRUD (create, read, update, and delete) operations
2 – Using functions and views to look at the data differently.

Details:

To be posted.