New York, New York

Very excited about going to New York April 24th and speaking at SQLSaturday. My girlfirend and I will go down on Thursday and watch some Broadway shows and visit a museum or 2.

The talk is '3rd Key Normal Form: That's crazy talk!!!' - a fellow employee has this on his whiteboard in his cube. In all my time as a developer and DBA, there is always some denormalization, or really no fully normalization to begin with.

The session is going to show some of the experiences I have encountered over the years. One is in the Laboratory Information System Management arena. My first job out of college was to help normalize data from a file system (written in Profressional Basic - DOS 3.3). We used FoxPro DOS to create a report writer for our clients. Many years later MS Access came along, and so did Visual Basic. I had some great ideas that the developers (Chemistists by training) did not want to implement.

The second example is a FEMA program for funding distribution and Performance Indicator tracking. This was a great example of compound primary keys geeting larger and larger. Two lessons here: Not every table is part of a hierachy and Parent-child can benefit from ID (identity field) primary keys.

If I have time, I think I want to get the audience into a 4-6th normal form discussion. Paul Nielson will be there, and it would be great to visit with him about database normalization.

God Bless,
Thomas

Winton Marsalis and Fragmentation

With replication and mirroring on our large databases, there is a limit on the amount of reindexing that can be done each evening. We have a job that loops through a couple of tables to pick what to attempt to reindex/reorg on a nightly basis. Recently, it has been updated to a better structure of lookup tables and 3 levels of possible work:

1. Rebuild if AvgFrag > 60 or AvgPageSpaceUsed <> 30 or AvgPageSpaceUsed < 65
ELSE
3. Update Stats

I added a History table to record State/End Date and Before/After AvgFrag & SpaceUsed

We hope after a couple of months we can get some useful statistics to change FillFactor on some indexes.

Tuesday night I brought my girlfriend to see Winton Marsalis and the Jazz Lincoln Center ensemble performing in Baton Rouge. The set started with some old and new swing, then finished with 7 works dedicated to 7 different artists. The composer explained how the song was written with the artist and his/her works of art in mind.

http://www.wyntonmarsalis.org/2009/12/23/marsalis-tour-march-2010/

It gave a unique view in how any type of work you do can be art...

God Bless and keep smiling,
Thomas :)

Updating large tables that are Replicated and Mirrored

I am working on an article for submission to explain the steps we use to update a Transactional Replicated table to a Data Warehouse where the database is also mirrored to an off-site Disaster recovery site.

The mirrored site connection and transfer rate ranges from 500k/sec to 2000k/sec and the added columns to a large (20+million rows) produce alot of TLog to transfer to mirrored site. The replicated transaction is delayed by the mirror.

In general, here are the steps:
1. Bring OLTP and OLAP systems down. This is already done because of release.
2. Make sure all transactions have been committed to subscribers. Use Replication Monitor.
3. Generate scripts for DROP and CREATE Publication and Subscription(s)
4. DROP Publication and subscription(s)
5. ALTER TABLE or any other scriptions on all databases, publication and subscribers
6. Create Publication without running any add subscriptions parts of the scripts
7. Use system SP - sp_scriptpublicationcustomprocs to generate replication stored procedures.
8. Execute SPs on Subscriber(s)
9. Run Create subscription script with @sync_type='none' not 'automatic'
10. Use Replication Validation from Replication Monitor to see if everything is OK

The only issue has been if the mirror still has not finished the transaction. Sometimes we have to wait 1-2 hours for this to finish.

There is a new trace flag -T1448, that is suppose to help with this, but the test we have tried have not shown this to be so.

God Bless,
Thomas