Compress Backup Information in Backup and Restore Events

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

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

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!

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

MongoDB 2.4 Feature Demo and Q&A on Geo Capabilities

 

Watch Webinar on Geo Capabilities of MongoDB

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.

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.

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

• SQL server / 750 hours of web, standard, or enterprise
• web sites / 10 web sites
• mobile services / 10 mobile services
• relational database / 1 SQL database
• SQL reporting / 100 hours per month
• storage / 70 GB with 50,000,000 storage transactions
• backup / 20 GB
• data transfer / unlimited inbound & 25GB outbound
• media services encoding / 50 GB (input & output combined)
• cdn / 20 GB outbound with 500,000 transactions
• cache / 128 MB
• service bus / 1,500 relay hours and 500,000 messages

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.

 

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.

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.

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

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.

Then I remove first row and executed it again.

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.

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.

Query Formatting

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