Monday, December 31, 2012

Last Post of The Year 2012

This year in November, while attending a training titled "Making it Happen", when trainer asked everyone to write down their current goals and hurdles that they are facing to achieve those goals, I took lot of time to think about these hurdles and finally filled all five options of hurdles with same words i.e. Me and Myself.

Though year 2012 was amazing, when I have learned a lot about 
  • SQL Server Wait Stats, and how actually to use them to resolve different issues.
  • SQL Serve Extended Events, and how they are going to be a biggest tool in near future for every DBA.
  • SQL Server Service Broker, and its real magics for single point of Administration for multiple instances.
  • SQL Server Log, and interpretation of different type of messages.
  • How to modify System stored procedures according to your need.
  •  And much more
 I think I have shared far less (45 Blog Posts) then I have learned, and I have made a commitment that during 2013 I will keep sharing (through blog post and a book), that What Ever I Will Learn from this community.
For me most inspiring blogger of the year 2012 was Paul Randal and most inspiring personalities were  Jacob Sebastian and Afeef Janjua.

 

Thursday, December 27, 2012

SQL Server: Client IP Along with DDL Change Log Using Service Broker



Last day, we have discussed all three possible methods for DDL Change Log and as per my suggestions, if you don’t need to conditionally allow/disallow changes then Service Broker is the best way to capture these changes. This method additionally allows you to submit change information to a separate instance on internet as a loosely coupled message.

One of blog reader raised a question that what else we need to add in script if we also need to capture machine IP from where change is coming.

Well answer is simple. We already have information of SPID so we can use this SPID and get client machine IP address by querying sys.dm_exec_connections.  
Change already defined stored procedure as following.



Wednesday, December 26, 2012

SQL Server: Three Common DDL Change Log Methods



Who is changing your objects (tables, views, stored procedures, functions etc) or creating new one, or who actually deleted one or more objects? These are normal questions when more than one person are working on a same database.
Production environment is mostly kept secure for unauthorized access and few known persons are allowed to make changes BUT still you need to keep a track of these changes and if it’s a development database then it is also must to keep a complete log of each change.
Three major ways, we can keep track of these changes.
1.                 DDL Trigger & Event Notifications
2.                 Extended Events
3.                 Service Broker & Event Notifications
DDL Trigger method is most commonly used method, where we write a ddl (after) trigger on each database separately and using information from event notifications, we decide whether to rollback any DDL change or just dump change information to a table.
Extended Events, is the most advance method, not only for DDL change tracking but it’s going to be next biggest tool for DBAs. SQL Server 2012, introduced three new events for DDL change tracking.
1.                 object_altered
2.                 object_created
3.                 object_deleted
Paul Randal script for extended event creation is good one to follow, but don’t forget to change events.
 Service Broker (with event notifications), is the best way I have ever found for DDL Change Tracking before SQL Server 2012. Though its initial steps are bit lengthy, that is why; most people avoid using this method.
Using service broker, you can dump all databases changes data to a single table on an instance, or you can transmit changes information as a message to other instance on internet (if need to create a single point of administration for multiple instances).
(What is Service Broker and what type of objects you need to create, can be found here and here)
Use following simple steps to create DDL Changes Log, for multiple databases on an instance.


Monday, December 3, 2012

SQL Server: SET SET, A Strange Compatibility Upgradation Issue



During upgradation of a client database from SQL Server 2000 (Compatibility Level 80) to SQL Server 2005 (Compatibility Level 90), I have found that there are changes which need to apply for compatibility level 90, that are still not documented. Like everyone knows that *= or =* type joins are not acceptable in compatibility level 90. But what about following simple code.
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 80
GO
USE [AdventureWorks]
GO
DECLARE @MyVar INT
SET SET @MyVar = 5
Executing above code with compatibility  level 80 doesn’t generate any error but when we execute same code with COMPATIBILITY_LEVEL =90, it will generate following error.

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.