Friday, January 26, 2007

A Special Republic Day

My first republic day as a Ex- Sailor. I am no more in Delhi, so I don’t feel that warm and tension. I always believe that if you want to know the significance and true spirit Republic Day it is Delhi the place to be. I still remember, those days when I and Leena (my wife) went first time to watch republic day parade in 2002. On that day we could not make it because of high security though I was in uniform. But in the next year we could make it. The whole function will be around 3 hrs and it is always colorful, vibrant and worth to watch. It is a trailer show of the diverse India. I got a pass of enclosure just opposite to VVIP’s daises. Now all those days are gone. I am not sure I would ever watch republic day parade again from India Gate. I really miss Delhi.

Thursday, January 25, 2007

SQL Server 2005 DMVs for monitoring/tuning TEMPDB

In SQL Server 2005 , TempDB plays a significant role in your overall SLQ server performance than earlier versions. In earlier version it was difficult to monitor the determine bottle neck.

In SQL Server 2005, you have number of DMVs to see which users are accessing the TEMPDB database , the internal object being used and version Stores size. The DMV are as follows

Sys.dm_db_file_space_usage
Sys.dm_db_session_file_usage
Sys.dm_db_task_space_usage

Note : In SQL Server 2005 these DMVs only applicable to Tempdb

Counters to Monitor Tempdb Row versioning usage

(a) SQL Server:Transaction Object : Version Generation Rate (KB/s)
(b) SQL Server:Transaction Object : Version cleanup Rate (KB/s)

Refer : http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

AUTO_UPDATE_STATISTICS_ASYNC Database Option in SQL Server 2005

SQL Server 2005 supports a new option called AUTO_UPDATE_STATISTICS_ASYNC which can be used to fine tune your database. Normally, when an executing query triggers an automatic updating of statistics through the query optimizer , the query has to wait until the statistics are updated before proceed further . in other words it can also called synchronous operation. But When AUTO_UPDATE_STATISTICS_ASYNC option is set to ON, queries do not wait for the statistics to be updated before compiling. Ie. it is an asynchronous process Instead, the out-of-date statistics are put on a queue for updating by a worker thread in a background process. The query and any other concurrent queries compile immediately by using the existing out-of-date statistics. Because there is no delay for updated statistics, query response times are predictable; however, the out-of-date statistics may cause the query optimizer to choose a less-efficient query plan

Note : AUTO_UPDATE_STATISTICS_ASYNC option has no effect if the AUTO_UPDATE_STATISTICS database option is OFF

Refer : http://msdn2.microsoft.com/en-us/library/ms190397.aspx

Wednesday, January 24, 2007

Insallation of SQL Server 2005 from Local Folder

If you are installing SQL Server from CD media dumps which is copied into a local/network folder (in SQL Server 2005 Installation CDs are Two where as in DVD media only one DVD) , the folders should be named as Servers and Tools for CD1 and CD2 respectively. Otherwise , client components and SQL Server BOL will not be installed. There will be error if you are not named the folder as above mentioned.

Ref : http://msdn2.microsoft.com/en-us/library/ms144259.aspx

Saturday, January 20, 2007

SQL Server Compatibility Level

Compatibility level of a database specifies SQL Server version compatibility and can be set to SQL Server 7.0(70), SQL Server 2000 (80) or SQL Server 2005 (90). When set to other than SQL Server 2005 (90), the compatibility makes the database behavior compatible with that version of SQL Server. By default it is set to 90. other options are given for primarily to address Upgradation problem, if any. The compatibility level can not be set using ALTER DATABASE Command. You should use sp_dbcmptlevel system stroed procedure to change compatibility.

Syntax : sp_dbcmptlevel datbasename , [70/80/90]

Significance of Tempdb in SQL Server

Tempdb is instance wide Temporary workspace that SQL Server uses for various operations. Tempdb is created from Model Database whenever the SQLSERVER service is started/restarted. Tempdb has simple recover model. It is a rule of thumb that you should never keep tempdb on the same location where your user database is stored. It is always recommended that tempdb should locate in a high performing disk subsystem (preferably RAID 10 or RAID 0). It is also to be configured with enough size for better performance, so that system no need to do the space allocation dynamically. You can also create multiple tempdb datafiles to minimize on IAM and SGAM and there by improve the performance.

Very common operation which consume tempdb are appended below :-

(a) Store explicitly created Temp tables , Table variables, Stored procedure, cursors etc.
(b) Stores Private and Global variable used throughout the instance
(c) Worktables associated with ORDER BY, GROUP BY, CURSOR
(d) Many System administration activities like DBCC commands uses TempDB
(e) If you have choose Snapshot Isolation Level then it uses Tempdb
(f) If your Report Server Uses Caching, then tempdb is used extensively
(g) If your have used SORT_IN_TEMPDB option while rebuilding indexes.

To relocate Tempdb Follow these steps

(a) Alter the file path by following command

Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
GO

(b) Restart the service and run sp_helpfile tempdb to confirm the above action

Refer :
Concurrency enhancements for the tempdb database --
http://support.microsoft.com/kb/328551

Hide SQL Server Instance in SQL Server 2000 and SQL Server 2005

To hide SQL Server 2005 Instance

In SQL Server 2005 you can hide a specific instance while other instance continue to expose. To hide a instance steps are as follows
1. Open SQL Server Configuration Manager from Prgramfile – Microsoft SQL Server 2005 – Configuration Tool
2. In SQL Server Configuration Manager, Expand SQL Server 2005 Network Configuration,
3. Right Click on “Protocol for ” and select properties
4. In Flag pane , you can find HideInstance with No set. Change No to Yes.
5. No need to restart the service, when you pressed OK, the new setting is immediately applied and new connection will not be able to connect to the instance using SQL Browser Service. You can still connect to the instance by specifiying protocol , Server name/IP address and port number or the named pipe directly

To hide an instance of SQL Server 2000:

1. Open the SQL Server Network Utility.
2. Select the instance that you want to hide. (This is only available on SQL Server 2000 instances). 3. Select TCP/IP from the list of enabled protocols.
4. Click Properties.
5. Select the Hide Server check box.
6. Click OK.
Note : Hide Server Option Cannot Be Used on Multiple Instances of SQL Server 2000

Refer :http://support.microsoft.com/kb/308091

SQL Browser Service in SQL 2005

SQL Server Browser Service in SQL 2005 provides information about the SQL Server 2005 instances installed on a machine. It provides the instance name and version number for each instance of the database engine and analysis server engine installed on a system. Its counter part in SQL Server 2000 is SQL Server Resolution Service which operates on UDP Port 1434 and provides information about the SQL Server 2000 instances on a particular machine. SQL Server Browser service is instance insensitive and it gets installed with the first instance of a machine. When SQL Server Browser Service starts, it accumulate information about Port number, named pipes of all instances installed on the system from windows registry. The it begins to listen network request on UDP port 1434.

It is not necessary to have SQL Server browser running , if it is not running you can still connect to a instance of a system by providing protocol , ServerName/,PortNumber or named pipe directly in the connection string Eg. TCP:ServerName,1456. If you have only one default instance then SQL Server Browser does not have any significance, because default instance always listens to 1433.

Thursday, January 18, 2007

Grant SQL Profiler Run Privilege to Login

In SQL 2000 , If you want to grant permission to run profiler to user /login , then the only way was to add that login to SysAdmin Serverrole. And no DBA would like to do that and ultimately DBA has to run profiler for this user. This was always a administrative hindrance. In SQL 2005, this long standing demand has been granted by Microsoft. Now what you can do is u can Grant Alter Trace permission to any login who want to run profiler and no need to add this login to SysAdmin Server role.

Grant Alter Trace LoginName

Wednesday, January 17, 2007

Apply Command : New in SQL Server 2005

Apply command can be used with a table-valued user defied function (UDF) so that the UDF accepts a different parameter value for each corresponding row being processed by the main query. There are two ways to apply, apply command.
(a) Cross Apply :- It is bit confusing because it has a contradictory behavior from name. ie. it operates more like an inner join tan a cross join. The Cross Apply command will join data from the main query with any table-valued data sets from the UDF. If no data is returned from the UDF, then the row from the main query is also not returned. So, its just behave like inner join
(b) Outer Apply :- It will return all the rows from main query , if at all no data is returned from the UDF. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.

It is well explained in BOL with perfect example. But thing is that you must know something like this exists in SQL Server 2005.

Thursday, January 11, 2007

Implementation and usage of DDL Trigger and its saga in SQL Server 2005

A new feature was introduced in SQL Server 2005 called DDL Trigger, which fires in response to a variety of Server scope or Database scope, Data Definition Language (DDL) events. These events can generally be fired by any DDL statements like Create, Alter, Drop, or DCL (Data Control Language) like Grant, Deny or Revoke.

DDL trigger can be created at two levels:

Database level (like Create, Alter, Drop table, Procedure, Function or any database scoped objects)
• Server Level trigger (like Create, Alter, Drop Databases/Login/ Certificate )
Problem
In the earlier versions, tracking DDL activities on a database or on a server was not straight forward. Running profiler or trace was the only solution. Consider, you have a group of developers responsible for all the Development activities like Creation or Modification of Stored Procedures. Typically, this group is added to DB_DDLAdmin database fixed role. Here, to track which developer has modified a particular stored procedure was not possible, since all users have the DDLAdmin privilege. To resolve these type of issues DDL trigger is introduced in SQL Server 2005.

DDL Triggers is useful for the following purpose:• Auditing
• Regulating database or server operation
• Securing the database or server from malpractice
• Issue an alert when a DDL event occurs in Database or Server

Permission
To create a DDL trigger with a server scope (ON ALL SERVER) requires CONTROL SERVER permissions on the server. To create a DDL trigger within the database scope (ON DATABASE) requires ALTER ANY DATABASE DDL TRIGGER permission in the current database.

DDL Event Group

The Figure 1 lists the DDL event groups that can be used to run a DDL trigger or an event notification and also the Transact-SQL statements they cover. You can use a top level Event group instead of mentioning all the events in the trigger. For example, using DDL_TABLE_VIEW_EVENTS event group instead of mentioning specified events like Create_Table,Alter_Table,Drop_Table,Create_view, Alter_View,Drop_View

Figure 1





How DDL Trigger works?

EventData()
EVENTDATA function returns XML data which contains the information about server or database events. EVENTDATA is called when an event notification fires and it can also be used inside the body of a DDL or logon trigger.

Database scoped DDL Trigger.

Any DDL statement, which alters the schema of an object inside the database, will be tracked in this type of trigger. (Please note that few events are not tracked (read as bug) and this will be fixed in SQL Server 2008 version). Generally, the DDL Statement at the database level are Create, Alter, Drop tables , Procedure ,Views ,Functions , Users, Role , Certificate, etc. (For all the database level events , please refer to http://msdn.microsoft.com/en-us/library/ms189871.aspx ). Either you can hard code the specific event to be tracked inside the trigger (like Create_Table,Create_Procedure etc) or use DDL_DATABASE_LEVEL_EVENTS event group to track all the database scoped events. Whether you need to track all the events or only specific events will depend upon the requirements. Also there are event groups like DDL_TRIGGER_EVENTS, DDL_FUNCTION_EVENTS, DDL_SYNONYM_EVENTS, DDL_SSB_EVENTS, DDL_DATABASE_SECURITY_EVENTS, DDL_EVENT_NOTIFICATION_EVENTS, DDL_PROCEDURE_EVENTS, DDL_TABLE_VIEW_EVENTS, DDL_TYPE_EVENTS, DDL_XML_SCHEMA_COLLECTION_EVENTS, DDL_PARTITION_EVENTS, DDL_ASSEMBLY_EVENTS which are also used in EventNotification. Mostly all the events are applicable for DDL Triggers as well.

Step by step in Brief

(a) Create a table to store the logged data. Here in this scenario the table created is DDLDatabaseScopedAuditTrail.
(b) Create DDL Trigger which logs the information into this table. Which event needs to be tracked will depend upon the requirements. EventData() function returns a whole lot of information. In this example, let us track Eventtype,Login,Database User name and TSQL which caused the trigger to fire and the fired Datetime. For a better understanding the output of EventData() function to an XML type column has been captured However, this may not be needed in the actual senario. Select only the necessary events as logging any extra information is always a performance hinderance.
This scenario, dipicts tacking three kind of events:
• Trigger to track all events .
• Trigger to track a group of events
• Trigger to track a specific event

Code Block for Database scoped DDL Trigger.

-- Database for DDL Trigger Demo
Create Database DDLTriggerTest
Go

-- Create Log table

If Object_ID('DDLAuditTrail') is not null
BEGIN
DROP TABLE DDLAuditTrail
END

CREATE TABLE dbo.DDLDatabaseScopedAuditTrail
(RowNum int identity,
EventType nvarchar(100) ,
AttemptedDate datetime NOT NULL DEFAULT GETDATE(),
ServerLogin nvarchar(100) NOT NULL,
DBUser nvarchar(100) NOT NULL,
TSQLText varchar(max) ,
EventData xml NOT NULL
)
Go

--Trigger to track all Database Events using EventGroup

Create TRIGGER db_DDLtrg_Audit_All_Database_Scoped_Events
ON DATABASE
FOR
DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON

If Object_ID('DDLDatabaseScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()
INSERT dbo. DDLDatabaseScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)
END
Go


-- Trigger to track specific group of Database events using Database Event Group

Create TRIGGER db_DDLtrg_Audit_All_TABLE_VIEW_EVENTS
ON DATABASE
FOR
DDL_TABLE_VIEW_EVENTS
AS
SET NOCOUNT ON

If Object_ID('DDLDatabaseScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

INSERT dbo.DDLDatabaseScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)
END
Go


-- Trigger to track very specific Database events


Create TRIGGER db_DDLtrg_Audit_Specific_Event
ON DATABASE
FOR
CREATE_Table, Create_Procedure
AS
SET NOCOUNT ON
If Object_ID('DDLDatabaseScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

INSERT dbo.DDLDatabaseScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)
END
Go

DDL Trigger for Server Scoped Objects Event tracking

Server scoped events can be tracked using a server scoped DDL trigger. Here also , you can track all the events using DDL_SERVER_LEVEL_EVENTS group event or you can be more specific tracking only CREATE_DATABASE and CREATE_LOGIN.

Steps in Breif :-(a) Create a table to store the logged data. Here the table created is DDLServerScopedAuditTrail. It is better to create this table in the Master or in any specific user database other than the business related database..
(b) Create DDL Trigger which logs the information into this table. Events depend upon the requirements. Here three types of events have been detailed.


DDL Trigger to track Server Scoped events


Use Master
GO
If Object_ID('DDLServerScopedAuditTrail') is not null
BEGIN
DROP TABLE DDLServerScopedAuditTrail
END

CREATE TABLE dbo.DDLServerScopedAuditTrail
(RowNum int identity,
EventType nvarchar(100) ,
AttemptedDate datetime NOT NULL DEFAULT GETDATE(),
ServerLogin nvarchar(100) NOT NULL,
DBUser nvarchar(100) NOT NULL,
TSQLText varchar(max) ,
EventData xml NOT NULL
)

Go

--Trigger to track all Server Events using EventGroup


CREATE TRIGGER Svr_DDLtrg_Audit_All_Server_events
ON All Server
FOR
DDL_SERVER_LEVEL_EVENTS

AS
SET NOCOUNT ON
If Object_ID('DDLServerScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

INSERT
dbo. DDLServerScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)


End
Go


Trigger to track specific group of Server events using Server Event Group

CREATE TRIGGER Svr_DDLtrg_Audit_All_DDL_SERVER_SECURITY_EVENTS
ON All Server
FOR
DDL_SERVER_SECURITY_EVENTS

AS
SET NOCOUNT ON
If Object_ID('DDLServerScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

INSERT
dbo. DDLServerScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)
End
GO


-- Trigger to track very specific Server events

CREATE TRIGGER Svr_DDLtrg_Audit_All_DDL_SERVER_SPECIFIC_EVENT
ON All Server
FOR
CREATE_DATABASE ,CREATE_LOGIN
AS
SET NOCOUNT ON
If Object_ID('DDLServerScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

INSERT
dbo. DDLServerScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)

End


Limitation

• DDL triggers will not fire in response to events that affect local or global temporary tables and stored procedures.
• DDL triggers fire only after the DDL statements that trigger them are run. DDL triggers cannot be used as INSTEAD OF triggers
Known issues of DDL Trigger in SQL Server 2005
• Truncate statement is not tracked by any event
• SP_Rename event is not tracked

For more information, please refer to:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124493
For an issue reported when you alter a Replicated Table, Please refer to:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=331367

SQL Server 2008
In SQL Server 2008, there are few more events added like sp_Rename is tracked using RENAME Event.

System objects for DDL Triggers

You can use the sys.triggers catalog view to see the database scoped DDL triggers. The parent_class column in this catalog view has a value of Zero which indicates that it’s a DDL Trigger. You can use the sys.server_triggers catalog view to get a list of server scoped DDL triggers. The sys.trigger_events and sys.server_trigger_events catalog views contain a row for each time a database is scoped and a server scoped trigger is fired, respectively.

Query to find out the Events and other details of DDL Trigger

SELECT Name, s.type_desc SQL_or_CLR,is_disabled, e.type_desc FiringEvents
FROM sys.server_triggers s
INNER JOIN sys.server_trigger_events e ON s.object_id = e.object_id

Where can I find DDL trigger in SSMO?

Database level DDL triggers are listed in the Programmability -- >> Database Triggers folder in Object Explorer. The server-level triggers are listed under the Server Objects -- >>Triggers folder in Object Explorer.

Figure 2



Drop DDL Trigger

The ON DATABASE or ON ALL SERVER clause should be used with DROP TRIGGER to drop a DDL trigger. All the other operations are similar to the DML trigger ie. ALTER TRIGGER to modify a DDL trigger definition, DISABLE TRIGGER to disable a DDL trigger and ENABLE TRIGGER to enable a DDL trigger.

Summary
DDL Trigger without any doubt is one of the best features of SQL Server 2005. There are known issues like few commands are not tracked by any events. In spite of these limitations, it gives a very good value add to the product in day to day activities. Now-a-days, auditing is obvious in any application and DDL trigger fits in there. We can make a complete automated auditing system using the DDL trigger and Reporting Service of SQL Server 2005.

Wednesday, January 10, 2007

Monitoring Memory Requirement

System Monitor is used to monitor memory usage in the server. In addition to System Monitor Tool you can use new Dynamic Management views also to collect data about SQL Server Memory.

Steps for Analyzing Memory Requirement

(a) Check how much physical memory is installed on the Box
(b) What other application/processes are consuming memory on the server
© Use System Monitor to capture the following counters
• Memory : Available Bytes :- it indicate how many bytes of memory are available
• Memory : Pages/Sec : Specifies how many pages must be read from the disk or written to the disk to resolve page faults
• SQLServer:BufferManager : This is very important counter , which will give you the Cache-hit ratio. This counter identifies the percentage of pages that were found in the buffer pool without read the disk. The value of this counter should be over 90%. High values indicate good cache usage and minimal disk access when searching for data
• SQLServer:MemoryManager:Total Server Memory :- Determines the amount of physical memory used by each instance of SQL Server
• Process:Working Set :- Gives the set of memory pages that have been recently accessed by the threads running in the process and can be used to determine how much memory SQL Server is using
• SQLServer:Buffer Manager:Page Life Expectance : It gives the average time spent by a data page in the data cache. A value of less than 3000 seconds indicates that SQL Server need more memory.

Dynamic Management Views used to collect data about SQL Server Memory Usage

• Sys.dm_exec_query_stats : Provides statistics on memory and CPU usage for a specific query.
• Sys.dm_exec_cached_plans : Gives a list of the query plans that are currently cached in mamory
• Sys.dm_os_memory_objects : Provides information about object types in memory, such as Memobj_Compile_Adhoc and Memobj_statement
• Sys.dm_os_memory_clerks returns the se of all memory clerks that are currently active in the instance of SQL Server.

RESTORE VERIFYONLY Different behavior in SQL 2000 and SQL 2005

You have a RESTORE VERIFYONLY option in both SQL 2000 and SQL 2005. But it has different behavior in SQL 2000 and SQL 2005. In 2000 what VERIFYONLY do is, it will check the media header and then returned a success or an error. The entire backup set could be invalid, and every other sector on the media could be bad. But aslong as the media header was intact, it would return a success. So this behavior effectively rendered this command worthless in SQL 2000.I would tell in sql 2000 you should not use this command. However, SQL Server2005 now performs the necessary checks, so you should execute this command every time you perform a backup.

Sunday, January 07, 2007

New year resolution – 2007

By the end of this year I would, no…no… I must... be a Microsoft SQL Server Developer/ DBA who possess good knowledge in Oracle also. I am planning to join for a Oracle course now. My next goal is to get certified in Oracle. And also I have learnt a lot in data warehousing area, thanks to my current project. By the end of this project , I am sure I will have a clear understanding of data warehousing process also. So hopes to get certified in Microsoft 70-446.. so more or less I have a tight schedule in this year and hopes I will be able to achieve my goals.

MCITP SQL 2005 DBA - Yet another mile stone in my SQL journey

I am a MCITP in SQL 2005 track from 6 Jan 07. The exam was no. 70-447 and I scored 918 out of 1000. I was little late to achieve this goal as per my schedule , but at the end of the day I am more than happy now. By getting certified one does not become a master , but it of course prove that he/she has a learning habit and if u have done in the true spirit u will have fare understanding about the tools and the new features of the product. So, I would always recommend to get certified as soon as possible. The exam was not easy as I thought. The question pattern has totally changed. This is my 9 th Microsoft certification, so I took this exam for granted. But when I saw the questions and the new case study section I was sweating. You have to read 4-5 papers of the Case study document and understand the language and the actual requirement. And also each case study section has its own time limit. So anybody going for 70-447 , must know this pattern change and the new case study section.

Total time – 210 min
Total Questions – 66
Pass marks - 700
Total - 1000