Monday, May 27, 2013

Compress Backup Information in Backup and Restore Events

In SSMS, there is report called , “Backup and Restore Events” report.

image

if you consider latest backups backup size are almost similar. (~  339 MB) However, if you really see the physical file size, it is 79 MB.

So the report and physical file size is different.

You can measure the file size by running a query.

SELECT 
bs.media_set_id,
bs.backup_finish_date,
bs.type,
bs.backup_size / 1024 backup_size_KB,
bs.compressed_backup_size / 1024 compressed_backup_size_KB,
mf.physical_device_name
FROM dbo.backupset AS bs
INNER JOIN dbo.backupmediafamily AS mf
ON bs.media_set_id = mf.media_set_id
WHERE database_name = 'AdventureWorks2012'
ORDER BY backup_finish_date DESC;
GO

image


So , here you can see the correct value can be obtained you query compressed_backup_size column rather than querying backup_size column. so it seems like, SSMS report is using backup_size column rather than compressed_backup_size column which seems to be BUG!

Sunday, May 26, 2013

How Vertica Was the Star of the Obama Campaign, and Other Revelations

The 2012 Obama re-election campaign has important implications for organizations that want to make better use of big data. The hype about its use of data is certainly justified, but a lesser-noticed aspect of the campaign ran against another kind of data hype we’ve all heard: the Silicon Valley hype around Hadoop that goes too far and claims an unreasonably large role for Hadoop. One of the most critical contributors to the Obama campaign’s success was the direct access it had to a massive database of voter data stored in Vertica.

Read more on http://citoresearch.com/data-science/how-vertica-was-star-obama-campaign-and-other-revelations

Wednesday, May 22, 2013

Monday, May 20, 2013

Before Triggers in SQL Server

There are lots of questions asking whether there are before triggers in SQL Server. There are nothing called Before Triggers in SQL Server.

What is the requirement for the Before Trigger?

Let us say, you want to verify some values other table before inserting it. In SQL Server, you can use INSTEAD OF TRIGGER.

CREATE TRIGGER tr_data_before ON Table_Data
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
IF
EXISTS (SELECT 1 FROM Tablle_Data2)
BEGIN
INSERT INTO
dbo.Table_Data
SELECT *
FROM INSERTED
END
END

However, in Oracle and DB2 there are Before Triggers.

Sunday, May 19, 2013

What Operation Type Invoked a Trigger?

This was a question brought to my attention and I didn’t have an answer. After searching, I realized that there is no direct function for it. Hence I came up with following solution.

I used inserted and deleted virtual tables.

Statement

inserted

deleted

INSERT

rows just inserted

 

DELETE

 

rows just deleted

UPDATE

modified row contents

original row contents

With the above cases, I came up with following trigger.

CREATE TRIGGER trg_data_ins_del_upd
ON Data
FOR INSERT,DELETE,UPDATE
AS
BEGIN
DECLARE
@ins int ,@del int

SELECT
@ins = Count (*) From inserted
SELECT @del = Count (*) From deleted
IF @ins > 0 AND @del > 0
INSERT INTO Operation (Operation) VALUES ('Update')
ELSE IF @ins > 0
INSERT INTO Operation (Operation) VALUES ('Insert')
ELSE IF @del > 0
INSERT INTO Operation (Operation) VALUES ('Delete')
END

However, I feel in the above case, you better of having three separate triggers than loading everything to one trigger.

Wednesday, May 15, 2013

Infrastructure as a Service for SQL Server

SQL Server Virtual images available for SQL 2008 R2 and SQL Server 2012 in 3 editions – Web, standard and enterprise.  If you have an existing SQL Server license with software assurance, as an additional benefit you can move your existing license to Windows Azure and simply pay for compute and storage.                 

Prices are determined based on

  • Size of the database
    • Web Edition if size of database < 5 GB
    • Business Edition if size of database < 150 GB
    • Least unit for which prices are charged is a GB. Example: If a database is 2.4 GB,  charges will be levied for 3 GB
  • Computing power required
  • Version of License

your free trial contains

 

http://www.windowsazure.com/en-us/pricing/free-trial/

virtual machines & cloud services / 750 compute hours per month

Monday, May 13, 2013

Maintenance Plan Blunders - 1

Maintenance Plans seems to be some what popular with novice DBAs simply because you don’t have to think much to configure a Maintenance Plan.

However, today I came across with two “interesting” cases. I will explain one of them today.

This is one of them.

 

image

According to this plan, it take a full backup and just after the full backup , it takes a differential backup!!!

Differential backup will contain all the modified data pages after the last full backup. Since these plans are running on mid-night, it is highly unlikely, you get any modifications after the full backup.  This means that you should have a differential backup for a different plan.

Friday, May 3, 2013

Search in MongoDB

I came across this incidents while working on some MongoDB. I am not sure it is the expected behavior, so thought of posting it.

I have a collection called zipcodes which has following attributes.

image

So, I need to get the count for two state , I executed following script.

image

it returned me  1596 which was incorrect. Then started to go more into details, I started with by interchanging the values and then I got a different results.

image

Then I remove first row and executed it again.

image

Oops, I got the same values as before. Then I realize that, whenever we pass values for same attribute only the last one will be taken in. So for the above case I should us $in.

This behavior is same for insert, update, find etc.

Wednesday, May 1, 2013

ME Bank picks 'cheaper' Microsoft stack over Linux

The bank, which is one of Australia's smaller banks with 250,000 customers, does not have any bank branches. It is currently undertaking an AU$57 million technology transformation program.

ME Bank's enterprise architect Jem Richards said that after the bank examined the costs between the two platforms, including the cost of providing tech support, ultimately decided that the Windows Server 2012 and SQL Server 2012 option would be cheaper.

"Although the alternative Linux-based platform is essentially free to deploy, based on our past experience, we knew that it would cost more to support than Windows. This made the overall costs of the two operating systems approximately the same," he said in a statement.

"In addition, Microsoft and Windows community specialists were readily available to help us configure Temenos T24, whereas finding the relevant skills for the alternative platform with Temenos was proving to be a lot more difficult."

Richards said that the unnamed Linux platform would cost AU$100,000 more to upgrade, while the Microsoft licence would require no additional cost over five years.

Read more at http://www.zdnet.com/au/me-bank-picks-cheaper-microsoft-stack-over-linux-7000014732/

UI Features in RoboMongo

I posted about RoboMongo tool around one month back. Thought of writing about two UI features of the tool.

Intelli-sence

This tol provides the intellisense feature so that users have the option of selecting the command rather than typing it.

image

Query Formatting

When large queries are typed, it will format the queries with different colors as shown below.

image