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.

What's New That's Not So New

SQL Server 2005 is finally out with a Lot of "new" things! People are so exited!
Microsoft has released the list of things that are new in SQL Server. One of the Nice feature is change of definition of Schema. Schema is no more refers to a user.
However, in practice most of the companies used the same technique. They created a dummy user and created the objects under that schema. Somewhere in 2000 when we faced same set of tables for two different - interconnected - systems (Accounts Payables and Accounts receivables) we came up with the mechanism of using schemas.

The application connects to the database based on the application user.

However, we had issues of db users connecting to server for gathering information. Some may search on wrong tables and tell us "The data I stored is missing".
However, those are simple issues and were resolved without much delay.

So is redefining schema a new concept. Yes and No. It is yes as at the database level it is redefined. No as it is the way the industry uses.

Security in SQL Server.

At once, security was the least feature I checked on SQL Server. In our office environment, the database is always on the backend, no direct access is allowed to the server. the appliation server is behind the fiewall, and the database server is always accessed through stored procedures. I had a few dynamic sql in stored procedures, however, they too are really secure (or I felt so). Most of the time, the security threat comes from inside. Some are dure to the limitations in SQL Server 2000.

• dbo rights are given to developpers so that they can create objects in DBo schema;
• sa rights are given to developpers so that they can use profiler to to debug a stored procedure.

The first issue comes as a by product of how schema defined in SQL Server 2000; It follows a simple formula of schema = user. To avoid users from creating tables in their user schema (and move to another schema when they leave the company) DBAs encouraged all to use DBO schema. That gives full access to developpers even to drop existing objects.

SQL 2005 handles it very well by seperating users from schema. User may use a default schema. However, schema is simply a logical collection of objects.

Second scenario is even serious. To run profiler, a user should be able to have sa rights. A person with sa right can do anything. including removing all other users (this includes the actual "DBA" as well) BANG! Currently I was avoiding this by running a profiler against the "profile user" to identify whats going on!

SQLS erver has solved this issue by allowing explicit access to run profiler.

Let me explore and share more security features later.

How DBCC CHECKIDENT behaves?

DBCC CHECKIDENT is a handy command to reset the value of the identity fields. However, they behave slightly differently at different situations

What will be the outcome of the following command:
DBCC CHECKIDENT ('Table_a', reseed, 100) . Is it hundred?

Well, it depends. it depends on whether the table is brand new (vergin?) or got some data already. If the table ever had data, even if you truncate table, the identity will start from next value.
That is, if the table even had data the next identity value will be 101 and not 100.
Try yourself. If anyone gets a different value, check your code :)