Universal Database

There are hundred’s of websites that ask you to register and in the member registration screen / page they usually ask you to fill-in your residential or shipping address details like STATE and COUNTRY. If you go to any ecommerce sites like eBay or PayPal they usually ask for CURRENCY that you like to trade-in. Look at currency exchange site, so may countries are listed with CURRENCY abbreviations. Similarly if you go to sites where you buy and sell used automobiles like www.autotrader.com, there you will see automobile MAKE and MODEL listed, even these two fields are repeated in many other similar sites. These some of the examples that we can easily see over the web, but imagine about the desktop applications in every organization – its mind blowing to think how many copies of this same set of data is sitting and what it might be costing.

Anyways let’s get going, details like STATE, State abbreviations (CT, NY etc), Days (Monday, Tuesday …Sunday), TIME (00:15, 00:30….. 23:45 etc…), MONTH (Jan, Feb, Mar etc) – let me call them as generic for the purpose of writing this article. Below are the examples that I am talking about.

Examples:

DAY:	Select Your Day Monday Tuesay ... ... Sunday
TIME:	Select Time Midnight 12:00 AM 12:15 AM 12:30 AM ... ... ... 11:00 PM 11:15 PM 11:30 PM 11:45 PM
TIMEZONES:	Select Time Zone (GMT -12:00 hours) Eniwetok, Kwajalein (GMT -11:00 hours) Midway Island, Samoa (GMT -10:00 hours) Hawaii (GMT -9:00 hours) Alaska (GMT -8:00 hours) Pacific Time (US & Canada) ... ... ... (GMT +12:00 hours) Auckland, Fiji, Marshall Island
CURRENCY:	Select your currency Afghanistan, Afghani (AFN) Albania, Lek (ALL) Algeria, Dinar (DZD) ... ... ... Samoa, Tala (WST) Zimbabwe, Dollar (ZWD)
CAR MAKE:	Car Make Acura Alfa Romeo ... ... ... Yugo **Model depends on Make
COUNTRY	Select country Afghanistan Albania Algeria ... ... ... Zimbabwe Åland Islands

What came next to my mind was, how about having Universal Database? All the above generic information stored and maintained as one single copy by some organization/ person/ entity, of course with all the disaster recovery and availability in place

How do we share this data with the world – may be via Web services or may be via

AJAX. Create JavaScript (AJAX) functions to fill up the combo boxes; the client website will call the JavaScript function or API to fill the dropdown in his page.

Most of the time these fields are used to ensure that the end user does not make typo errors while entering his details and then the details are stored as is. However many a time the data is stored in relational model and there you need these physical (tables with generic data) table itself to generate your reports. If that is also addressed i.e. if the consumers of the generic data is given access to join with these tables with generic data that would solve the problem.

These are just my meandering – don’t take it serious. But these are near future possibilities or may be already in use who knows – at least I don’t know. However I found something interesting – www.freebase.com. This site is trying to gather/ organize all the information and is like wiki, anyone can update the database. This massive database can be queried, watch the video posted over there – it’s cool!

Powershell for SQL Database Administrators

Here is list of links that can get you started with Power Shell for SQL Database Administrators (DBA)


http://www.databasejournal.com/features/mssql/article.php/3681061/Microsoft-Windows-PowerShell-and-SQL-Server-2005-SMO--Part-I.htm
This is a 3 part article that start from installation and how to interact with Power Shell (PS)

http://www.mssqltips.com/tip.asp?tip=1680 - again this one introduces how to use PS and makes you comfortable with couple of commands

http://www.simple-talk.com/sql/database-administration/why-this-sql-server-dba-is-learning-powershell/
This one directly delves into how to us PS to automate some of the SQL Server admin task, includes good practical samples.

http://itknowledgeexchange.techtarget.com/dba/powershell-sql-server-health-check-script/ - Here is the really working sample that dumps the database information into an excel sheet.

Using the above links you should be able to build a script which can scan all your SQL Servers (listed in a given txt file) and dump the required information into a nicely formatted excel sheet.

But in my case most of the automation that I built around SQL Server was using VB Script, HTML templates and DOS!

I think Power Shell is for new age Database Administrators.

Organizational Changes - why they are painful ?

Why changes don't happen easily in organizations, why are they painful?

Most common reasons, employees don't like it. As popular saying goes "Change is painful".

My understanding is different. Human being always like change, it makes them feel they are alive and that's why they change their furniture's and personal vehicles and on top of it they pay for it and they enjoy doing so.

So why does it not work in organizations?

First reason I think is - it is because the process “change” starts from Management and flows down to people/employees, so it's not their idea or thought that is being implemented. It's an idea of some manager sitting somewhere in an isolated area. Though the change might be strategically the best one - but people at each desk think differently about the management.

Second reason as per my knowledge is the "process" of change is painful. This can be best explained with typical case. Suppose in an organization a developer is writing his Java programs in a notepad and saving it into a source control application which is totally external (not integrated). He has been doing this from last couple of years, let’s say 7 years and it involves very simple steps. Most of the developers know how to use it and also troubleshoot. Apart from this there are good amount website and forums that talk about this current method of Java development setup. Now organization decided to bring in some Integrated Development Environment (IDE) to improve the productivity of the developers. And they decided to bring in some XYZ IDE. Management decided on this "XYZ IDE" as the comity involved in the decision making decided so. Now developers are asked to use this development environment.

All the assumptions or the predictability that developer had about the old process, over course of time are now invalid. He doesn’t know whom to approach, what to look for. Of course this is common thing with every thing that changes in life. What if that developer is given some training? - Even that will not work any magic. I would say people should be given with learning resources that can help them right when they want it. If this kind of help is not available I am sure the change is going to bring just frustration, no productivity, in fact negative productivity. Individual's productivity loss will further spread into his other job areas.

Here what I think should be done:

Involve as many people as possible while implementing change, make it their own. Involve them in brain storming, testing and evaluation processes. So that knowledge start building in the community before the change is brought.

Secondly I would suggest forming a mentor groups before bringing in change. The individuals affected by these changes should be aware of resources that can help them, when they need it. The resources could be the documentation, on-demand video or a co-worker sitting next to him

If someone commits mistakes, he should not be discouraged, by making it painful to fix it or worst case by a office wide memo publicizing his name. Such events will discourage individuals from exploring the "NEW" tools or techniques. Most of the inventions are brought to the world via accidents (while exploring), not by the dedicated R&D efforts, so exploration is good.

Dear Manager - Please Delegate

Some managers do not delegate work to their sub-ordinates, why?

Reasons:

1. Manager has no patience to teach his sub-ordinates on how to do some of the stuffs.
2. Manager simply doesn’t trust his sub-ordinates.
3. He thinks he will lose the control of the department, as he cannot keep up with – “what is happening?” kind of stuff
4. Some managers hesitate to delegate thinking that, what his subordinates will think of him – “he just dumps everything on us and browses eBay all after-noon”
5. Manager thinks the sub-ordinates are not intellectually capable of handling the work.

While manager is one who has capability, maturity, strong business sense and can play the role of a mentor. If this is what manager is then he should not be worried about any of the above.

As a manager you should trust your sub-ordinates and have full faith in them and depend on them, else your boss will ask you – What is your productivity, with 7-8 members under you? And you will have no answer.

Many a times, you will face situations where you think, its better you do it yourself instead of teaching your subordinate, just because you don’t have time and patience to deal with, you cannot slip away from taking the role of a “mentor”. You have to develop that quality of being patient and teach your sub-ordinates. Teaching and making your sub-ordinates more productive is the best time-investment that can count on.

You should keep tab on what is going on in your department to the extent of what is happening, it’s not necessary to understand how it being done. You simply cannot keep account of every piece that happens within the department. It’s like driving the car at its optimal speed, whenever there is some noise or you suspect some performance issue, you need to open the hood and check it out. As a manger you should be spending more time on thinking how to improve the effectiveness of the team rather than looking each individuals tasks and taking account of them.

Yes manager has to browse the world (not eBay) to gain knowledge and develop new thoughts or ideas. However do not forget your sub-ordinates (your team) have more ready ideas and thoughts that can directly affect your team’s effectiveness. So first listen to them and see if you can materialize them.

Read the above paragraphs above about your personnel development again. You have to give knowledge, capabilities and have to trust your team and that’s the way you can develop some level of independence within the team. Do not paralyze your team by making it dependent on you for every decision that you make for them, soon you will be the bottleneck and you will be in stress.

How to Back Up the Transaction Log When the Database Is Damaged

This is one of the best and most frequently asked question in the SQL DBA interview especially if you are being interview for "Production Support DBA" role. "How to Back Up the Transaction Log When the Database Is Damaged" aka "how to perform tail log backup" - you will face this kind of issues when your database which get corrupted and goes in suspect most while in use or when you recover from a server crash mostly in cases like disk failures. When such failures occur your best method to recover the data is to restore your backup (full/diff/t.logs), so when do we need the tail log backups ? Assume a case like this one - you take a full backup every night and differentials every 4 hrs. and t.log backups every 15 minutes, which most of us do. Suppose your t.log backup was about to start at 11:15 AM and crash happens at 11:13 AM. So, we are left with a gap of 11:00 AM to 11:13 AM transaction - how do we recover from this gap, yes we depend on tail log. There is no gurantee that you will recover best all of the data, but is a "best effort" strategy as a responsible DBA. Here is the T-SQL command, BACKUP LOG TestDB TO TestDB_FullRM_log1 WITH NO_TRUNCATE GO Notice the keyword "NO_TRUNCATE", why we need it ? - NO_TRUNCATE clause allows the active part of the t.log to be backed up even if the database is inaccessible/offline, provided the transaction log file is accessible and undamaged, this is very important so keep it in mind :) - for interview as well as for real case where you are required to recover the database from a failure.Why it's called tail log ? - because this is the last backup taken from the failed database in order to recover from failures.

SQL Job Status Reporting

One day I suddenly see that my LiteSpeed DB backup job is running and running forever ! - though it did not fail but it did not complete so its still a failure in my opinion. This can happen due to third part component failure, or due to bug in the Windows OS or in the SQL Server agent. So, for what so ever reason if job/step is hung, its an issue. To overcome this issue I decided to email out the SQL Job status as a summarised report to myself (and other as required).

Here is the script to achieve the same. It will send out an email (assuming you have configure email feature on your SQL Server) with SQL Job status like LastRun time LastRunStatus and TimeTaken for latest execution.

--===================================================

DECLARE @tableHTML NVARCHAR(MAX)

,@SubjLine varchar(500)

SET @tableHTML =

N'<H4>SQL Server Job Status Report:</H4>' +

N'<table border="1">' +

N'<tr><th>Job Name</th><th>is enable ?</th>' +

N'<th>LastRunDateTime</th><th>LastRunDuration</th><th>LastRunOutCome</th>' +

CAST ( (

SELECT

td = J.Name, '',

td = J.Enabled, '',

td = left(Jh.run_date,4) + '-' + SUBSTRING(CAST(Jh.run_date AS CHAR),5,2) + '-' + right(Jh.run_date,2) +' '+

LEFT(REPLICATE('0', 6-LEN(Jh.run_time)) + CAST(Jh.run_time AS CHAR(6)),2) + ':' +

SUBSTRING(REPLICATE('0', 6-LEN(Jh.run_time)) + CAST(Jh.run_time AS CHAR(6)),3,2) + ':' +

SUBSTRING(REPLICATE('0', 6-LEN(Jh.run_time)) + CAST(Jh.run_time AS CHAR(6)),5,2), '',

td = LEFT(REPLICATE('0', 6-LEN(Jh.run_duration)) + CAST(Jh.run_duration AS CHAR(6)),2) + ':' +

SUBSTRING(REPLICATE('0', 6-LEN(Jh.run_duration)) + CAST(Jh.run_duration AS CHAR(6)),3,2) + ':' +

SUBSTRING(REPLICATE('0', 6-LEN(Jh.run_duration)) + CAST(Jh.run_duration AS CHAR(6)),5,2), '',

td = CASE run_status

when 0 then 'Failed'

when 1 then 'Succeeded'

when 2 then 'Retry'

when 3 then 'Canceled'

when 4 then 'Progress'

end, ''

FROM msdb.dbo.sysjobhistory Jh

JOIN msdb.dbo.sysjobs J ON Jh.job_id = J.job_id

WHERE

Jh.Instance_id IN (

SELECT MAX(Jh.instance_id) [Instance_Id]--, J.Job_Id INTO #JobHist

FROM msdb.dbo.sysjobhistory Jh

JOIN msdb.dbo.sysjobs J ON Jh.job_id = J.job_id

WHERE

--Jh.step_id = 0 -- I don't step details, just job outcome

--AND J.enabled = 1 -- enable this condition to filter out DISABLED jobs

J.Name IN (SELECT JobName FROM Monitoring.dbo.SQLJobsToMonitor WHERE ReportedYesNo = 1)

GROUP BY J.Job_Id

)

ORDER BY

CONVERT(DATETIME,

left(Jh.run_date,4) + '-' + SUBSTRING(CAST(Jh.run_date AS CHAR),5,2)

+ '-' + right(Jh.run_date,2) +' '+

LEFT(REPLICATE('0', 6-LEN(Jh.run_time)) + CAST(Jh.run_time AS CHAR(6)),2) + ':' +

SUBSTRING(REPLICATE('0', 6-LEN(Jh.run_time)) + CAST(Jh.run_time AS CHAR(6)),3,2) + ':' +

SUBSTRING(REPLICATE('0', 6-LEN(Jh.run_time)) + CAST(Jh.run_time AS CHAR(6)),5,2)

)

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table><br><h5>Job name: Monitoring - SQL Job Status Report<h5>' ;

SET @SubjLine = 'SQLMail Monitor: '+@@ServerName+': Job Status Report'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'SQLMail_Mail',

@recipients = 'emailid_001;Emailid02',

@subject = @SubjLine,

@body = @tableHTML,

@body_format = 'HTML';

--===================================================

"Monitoring.dbo.SQLJobsToMonitor" is a table which will be holding the list of jobs to be reported, you can add or delete or set the flag "ReportedYesNo" column to 0/1 to filter out the less important jobs from being reported.

Here is the table structure for the same.

--===================================================

CREATE TABLE SQLJobsToMonitor (

PkId INTEGER IDENTITY PRIMARY KEY NOT NULL,

JobName varchar(500),

ReportedYesNo bit default 1

)

--===================================================

Report Sample:

SQL Server Job Status Report:

Job Name	is enable ?	LastRunDateTime	LastRunDuration	LastRunOutCome
SS Maint Job - exec Prc_FutureRevenue @Thread = 1	0	2009-03-14 01:15:17	01:56:31	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 0	0	2009-03-14 01:15:17	01:56:53	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 4	0	2009-03-14 01:15:17	01:57:10	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 9	0	2009-03-14 01:15:17	01:58:15	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 6	0	2009-03-14 01:15:17	01:58:25	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 3	0	2009-03-14 01:15:17	01:58:38	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 2	0	2009-03-14 01:15:17	01:59:18	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 5	0	2009-03-14 01:15:17	01:59:36	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 7	0	2009-03-14 01:15:17	03:22:27	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 8	0	2009-03-14 01:15:17	03:23:24	Succeeded

File Monitoring Using T-SQL

Ever wanted to check for the files and then take some action? - this is very common situation where some team will be providing a data feed or extract (as some people call it). Which are usually in flat file format. Before consuming those files you may want to check if you have received all the files are not and then start the consumption.

But in my case what happened was the SSIS flow was written in such way, it will start consuming the files and then at the end when it finds that one of the file is missing it used to rollback the operation which used to take 30-40 minutes to realize. So I put a step before that SSIS execution step to check for file and following is my implementation

-- Check if my files are available

DECLARE @return_status INT

DECLARE @myFileMask VARCHAR(50)

SET @myFileMask = LEFT(REPLACE(CONVERT(varchar, GETDATE(), 120), '-', ''), 8) + LEFT(REPLACE(CONVERT(varchar, GETDATE(), 114), ':', ''), 2) + '*.*'

EXEC @return_status = dbo.prc_CheckForFiles

@FileMask = @myFileMask,

@FullPath = 'E:\FlatFilesDump\',

@FileCount = 7

IF @return_status = 0

BEGIN

RAISERROR ('My files are not available yet. Please check !', 16, 1)

END

--consume files if all are available

IF @return_status = 1

BEGIN

PRINT 'Files are available!'

EXEC dbo.usp_Test -- this is just a test SP

END

---===================================================================================

Here is the script for prc_CheckForFiles

---===================================================================================

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

GO

/**

Author: Girish

Purpose: To validate if the set of files exist or not (YES/NO)

Usage: Its takes 3 inputs, root path, file mask and count

**/

CREATE PROC [dbo].[prc_CheckForFiles]

(

@FileMask VARCHAR(50), -- search mask like ab*_.txt etc

@FullPath VARCHAR(1000), -- search path like "C:\DumpFilePath\"... UNC paths are NOT ALLOWED

@FileCount INT -- integer to indicate the number of files to be looked for, based on which success or failure is indicated

)

AS

SET NOCOUNT ON

--PRINT @FileMask

--PRINT @FullPath

--PRINT @FileCount

CREATE TABLE #Tmp(

cmdOutput VARCHAR(500)

)

DECLARE @DateStamp VARCHAR(30)

, @CMD VARCHAR(1000)

, @myRowCount INT

SET @CMD = 'DIR '+@FullPath+''+@FileMask+' /B'

--PRINT @CMD

INSERT INTO #Tmp EXEC master.dbo.xp_cmdshell @CMD

SET @myRowCount = @@ROWCOUNT

IF @myRowCount >= @FileCount

RETURN 1 -- expected number of files were found

IF @myRowCount < @FileCount

RETURN 0 -- expected number of files NOT found

DROP TABLE #Tmp

--end

GO

Please note, since this depends on xp_cmdshell and DOS – it might limit you ability to implement in your specific environments.