My recent Talk

My recent talk on Performance optimization of stored procedures happened at SQL Server user group meeting at Microsoft Colombo. This time I was concentrating only on how to optimize the stored procedures. As usual I took quite a bit of time, and couldn’t complete the example thoroughly. My entire presentation was intended to complete

The presentation and samples are available at SQL Server Universe.

Moving to the new company

Finally one of the long awaited move is happening. eCollege has its branch in Sri Lanka now. I am leaving the current company (virtusa) and joining eCollege as a Database Lead. Still there is some confusion whether my designation should be Lead - Database Administration, Lead - Database Technologies or simply as Database Lead but the roles and responsibilies will be the same.
With this move my virtusa email address will cease to exist. Please contact me on other email addresses.

Table Partitioning - Again

I wrote some time back about changing the identity property of an existing table.
http://preethiviraj.blogspot.com/2007/09/table-partitioning.html

The author of Inside SQL Server, Kalen Delaney wrote about it recently. As I used to read her blogs, I wrote a reply to that.
http://sqlblog.com/blogs/kalen_delaney/archive/2007/07/13/did-you-know-it-s-in-the-books-online.aspx

Interestingly, my friend and co worker Jude Pieries too wrote about the same feature in his article
http://sqlserveruniverse.com/content/DEVL0400101022007SwitchingFromNonIDENTITY.aspx


PLEASE HAVE A LOOK!

Recovering database using proper Backup/Restore Strategy

Again I was asked to speak at the Sri Lankan SQL Server User group Meeting. This time it is on Recovering database using proper Backup/Restore Strategy.
It happened on January 16 2008. I was supposed to be the first speaker. Due to some technical difficulties I took the second slot.

The slides and examples will be published at http://sqlserveruniverse.com/ shortly.

Index /Clustered Index SCAN When Top 1 Is Used

One of my recent articles is published at SQL Server Worldwide User Group.

Even a simple statement which SELECTs the last (or first) row of a table may cause clustered index scan or index scan. But there are ways to make it a seek.

Interested?? Have a look at http://www.sswug.org/see/37061

The site may request you to be a member to read the article. But eventhough you may have to give your credit card details, You can have a free trial membership.

If you dont like the site, you can cancel the membership without paying any money.

MCITP Certification

Finally after a long time last month I decided to do MCITP certification. Even though I had the confidence that I have the knowledge and experience in the areas covered, I was lazy to make my mind towards this exam.
However, as December is usually a low work period as many take their annual leave during this period, I decided to do this exam and also to encourage other DBAs in my department to do the same.
As I was able to get MCTS certification in 2006, it is only a matter of two exams. I did the first exam (70-443) on December 26, 2006 and the next one (70-444) today. The exams were not difficult but it expects the candidates to have wide knowledge and experience on the topics covered. With this exam Microsoft will declare me as a Microsoft Certified Information Technology Professional : Database Administrator.

70-443 had case studies and questions on case studies. It takes some level of concentration.
When compared to 70-443, 70-444 is a piece of cake. :)

Table Partitioning

I was asked to speak at the Sri Lankan SQL Server User group Meeting on Table Partitioning.
I did it last week (August 16 2007) and as usual I took another 15 minutes from the next speaker too.

The slides and example are available at
http://sqlserveruniverse.com/files/folders/meeting05/entry301.aspx
http://sqlserveruniverse.com/files/folders/meeting05/entry302.aspx

Interestingly, I found a method to change (Cerate/drop) the identity property of a column during my preparation for this presentation. I was told by many members that they were forced to add a column and drop a column to the table just because they wanted to have the identity property added or dropped.

Is it Interesting? Check out at the examples

Database Auditing: Method 3 - Service broker (2005 only)

Another method of database auditing is using Service broker. It again sends the details to the queue and service broker takes care of the transaction handling.
This too like replication, works in seperate thread and a possible solution if the application works in a critical manner.
Could be used against different server


However, it is quite limited to SQL Server 2005 only.
If you are using SQL Server 2000 or 7.0, you can consider the option of using message queue (e.g.MSMQ) however, message queue is not a database system. Thus, transaction management is available. In simple terms, if one entry fails, MSMQ will not rollback. Also, it has its own limitations.

Database Auditing: Method 2 - Replication

Transact replication is another method of auditing tables.

Description:
Modify the stored procedures generated by replication to suit the needs. Optionally you can use triggers discussed in the previous post against the replicated tables as well.

Advantages:

• Replication works in a separate thread. Thus, application will not be affected directly.
• Replication reads from Transaction log. It is much faster as it is sequential.

Disadvantages:

• The replicated data should reside in a separate database. This is the first limitation.
• Some editions do not support transact replication. Merge replication may require complex stored procedures/triggers
• Replication is one of the least published mechanism. Troubleshooting may be a nightmare.

Links:

• I should create some links.

Database Auditing: Method 1 - Triggers

I am going to talk about different methods for database auditing. Auditing could be done at different levels:
1. Capture who did what (e.g. "Jim modified with invoice")
1. Capture the last updated datetime of a record as well. ("Invoice 1003 last modified by Jim at 21 Nov 2005 12:35:42.767")
2. Capture the previous data and the current data with CURRENT_TIMESTAMP.

To do auditing, we need to establish a few priciples.
Any layer can audit the data available only at that layer. For example, real user name (the user who uses the application may be different from the application's user name to connect to the database. The DB won't know the application user unless specified explicitly.
There are different techniques available for auditing.

Today, its about using triggers for database auditing.

Description:
Use either instead of or after triggers to capture the data.

Advantages:

• Easy to write.
• Useful when the database is already in place.

Disadvantages:

• Performance goes down; database needs more resources; application waits until trigger is completed.
• Blob fields are not audited; update to blob field only will not be audited.
• Complexity increases when additional triggers are placed.
• Triggers are fired automatically; application has no control on that.

Some useful links:

• http://www.sqlservercentral.com/scripts/contributions/521.asp This script creates the triggers to audit table
• http://www.sqlservercentral.com/columnists/tsilva/triggersforauditing.asp An article on this subject
• http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=259&messageid=234334 Comments posted on above article including from me.