Powerless

My family and just returned to Florida from a trip to northern New England to visit churches and individuals that financially support our ministry with New Tribes Mission. I think this needs a little explanation for those who are not familiar with the workings independent, non-denominational missions. New Tribes does not pay any salaries, but each individual that becomes a member of the mission needs to visit churches and individuals of like faith in order to raise the funds necessary to live. Then after you are working for the mission you return on regular basis to those churches and individuals to report on what you have been doing. I liken it to the Interview and Performance Review process that you go through when landing and keeping a job. The first time you visit a church to present the ministry you will be doing, you are interviewing with them. When they choose to financially support you, they have "hired" you. Then you return to tell them about the work you have done, you are having your performance review. Have you really done what you told them you were going to do? Hopefully the answer is "Yes", and they will continue to financially support you and maybe give you raise.

All that to actually get to the real story. We flew from Orlando to Manchester, NH, on Thursday night and had our first speaking engagement at Calvary Baptist Church in Newport, ME, on Sunday morning. Thursday and Friday night we stayed at my brother's house in Derry, NH. Well, I setup my laptop at my brother's to use while we were there. On Saturday we packed up and headed for Newport. I went to the church Saturday night to setup and did not have a power strip so I got everything setup and tested (projector & laptop) using my laptop battery. On Sunday morning I arrived early to get the power strip I was borrowing setup so I could plug in my laptop. I went to my laptop bag to get my power cord and, OH NO, it was not there! I had left it at my brother's 3 1/2 hours and 140 miles away! I currently use a 5 year Dell Latitude D600, so the battery life is not long, so I knew I could be in trouble. I use the PowerPoint slides as my notes and expand on them extemporaneously and I had not printed out the presentation, so I had no notes. Having attended a Successful Technical Speaking class presented by Andy Warren at End to End Training this spring, I knew better, but hey, I'd never not had power before, so why be prepared? Needless to say, about halfway through the presentation the battery died and I had a blank screen. Since I had recently put together the presentation, most of it was fresh in my mind, so I was able to complete it without leaving out any major points, but this was not a situation I am interested in repeating. In the long run, this worked out, as I learned that several slides incorporated more detail than was necessary and I was able to remove them to improve the flow of the presentation. Thankfully the people were very gracious and understanding, so my faux pas was forgiven.

The moral of the story, make sure you pack everything and print out those slides!

Audit Trails and Logging

This week at SQLServerCentral there have been a couple of articles on Audit Trails and Logging (Part One and Part Two) that did a good job giving the basics of logging changes made to the data. In the discussion for part two someone asked about logging select statements and the returned results. While logging the selects is reasonable, logging the results of each query is, in my mind, a little out of whack. I understand why you may want to log the results as they may change over time. For example, Mary Smith married Joe Johnson and takes his last name, or hyphenates so she is now Mary Johnson or Mary Smith-Johnson. A query for LastName = 'Smith' the day before the change returns Mary, but the day after it does not. So in the event that data was compromised it will be more difficult to determine whose information was compromised if only the query is logged and not the data. There are several questions that come up now:

• How much space do you have for logging and is the expense associated with it justifiable?
• How far back do you keep the logged data?
  
• Do you log queries of the logged data?
• Who has access to the logged data?

These are questions that come up right off the top of my head and do not address the mechanics of the logging. There are no "Select" triggers in SQL Server so that eliminates triggers, which also eliminates being able to log ad-hoc queries using Access, SSMS, Query Analyzer, or any OLE DB or ODBC connection (Excel). One solution suggested in the discussion is using a server-side trace which works for logging the query and who ran it, but will not log the results. As I am a firm believer in not granting direct table access, I would start out by putting logging in the stored procedures used for data access. Of course, that can lead to some issues with developers who would prefer to use an ORM tool, like Linq To SQL or SubSonic, for the data access layer. Granted they are still able to use stored procedures through these tools, but one of the selling points is that you "never have to write another stored procedure". So now they not only have to data access code in the DAL, but also auditing code. If the audit needs to include the data, can I do a batch insert with the ORM tool using the dataset, list, collection I already have or do I have to iterate through the results and insert one at a time? Or do I have a separate call to the database that runs the select again, like:

Insert Into audit
Select
query,
user,
data columns....
From
table(s)
Where
criteria

This means now I am storing the user and query with each row returned and I am doubling the reads on my database.

I certainly don't have the solution to this problem. I know that for Inserts/Updates/Deletes, I like triggers because they put all the code in one place and will fire on all changes unless someone explicitly disables triggers before making a change and if this happens you have other problems.

I'd be interested in hearing what other people think.

June oPASS Meeting

I attended the June oPASS meeting and post meeting gathering at Bennigan's this evening. The speakers were Ginny Caughey and Steve Lasker, Program Manager for SQL Server Compact Edition, and, guess what, they spoke on SQL Server Compact Edition. It was a very informative meeting as I have had no experience or exposure to SQL Server CE and the presentation certainly gave me something to think about. I actually have a personal project that I am currently using SQL Server Express with, but I am now considering moving to SQL Server CE. Why? SQL Server CE is lightweight, portable, and appears to meet the application requirements. It would also give me some experience with SQL Server CE which I can use to evaluate it for use in future projects. The database can be packaged with the application and if I want to move it to another PC I can simply copy and paste. After the session 7 of us, including the speakers, adjourned to Bennigan's for some more discussion. I always find it enlightening to listen to Andy Warren voice his opinions. He is very much an idea guy, and has usually thought through the subject. We discussed the direction of SQL Server CE, Linq to SQL, and SQL Server Data Services. It's nice to hear about development directions from someone at Microsoft and to see the interest in "user" opinions and ideas.

Once again I found it to be a productive use of my time, even though the topic was not one that seemed to apply to my interests and work. As always, I encourage you to take the time to find and attend user groups in your area.