Saturday, April 27, 2013

RavenDB

image

Schema free

Schema free, document databaseForget about tables, rows, mappings or complicated data-layers. RavenDB is a document-oriented database you can just dump all your objects into. Queries are amazingly fast and flexible.

Scalable

It scalesSharding, replication and multi-tenancy are supported out-of-the-box. Scaling out is as easy as it gets.

Transactional

TransactionalACID transactions are fully supported, even between different nodes. If you put data in, it is going to stay there. We care about your data and we keep it safe.

High performance

Fast and high performantRavenDB is a very fast persistence layer for every type of data model. Skip creating complicated mapping or multi-layer DALs, just persist your entities. It Just Works, and it does the Right Thing.

APIs

Variety of APIsRavenDB comes with a carefully designed client API for .NET, Silverlight, Javascript and REST over HTTP.

Feature rich and extensible

ExtensibleBuilt with extensibility in mind, RavenDB can be easily extended via bundles. Many integration points ensure you can always squeeze more out of RavenDB. You aren't shackled to a One Size Fits None solution.

Carefully designed

Carefully designed with best practices in mindEvery bit of code was carefully considered. RavenDB was designed with best-practices in mind, and it ensures that everything Just Works.

Get the details from http://ravendb.net/

Monday, April 22, 2013

Reduce Dev Time and Improve Performance with MongoDB Training

Learning best practices for MongoDB development and administration can help reduce development time for your application, and increase reliability and performance. Get yourself and your team up to speed quickly with 10gen's comprehensive courses in person and online:

  • MongoDB for Developers: Learn best practices for document-based data moderling, queries and commands, map/reduce and basic administration.
  • MongoDB for Administrators: Diagnose performance issues, import and export data from MongoDB and establish the proper backup and restore routines.

Sunday, April 21, 2013

Free ebook: Introducing Microsoft SQL Server 2012

665156.indd

The final and complete version of Introducing Microsoft SQL Server 2012, by Ross Mistry and Stacia Misner, is now ready as a free download! You can download the PDF version of this title here

 

 

 

 

 

 

 

PART I   DATABASE ADMINISTRATION (by Ross Mistry)

1.   SQL Server 2012 Editions and Engine Enhancements

2.   High-Availability and Disaster-Recovery Enhancements

3.   Performance and Scalability

4.   Security Enhancements

5.   Programmability and Beyond-Relational Enhancements

PART II   BUSINESS INTELLIGENCE DEVELOPMENT (by Stacia Misner)

6.   Integration Services

7.   Data Quality Services

8.   Master Data Services

9.   Analysis Services and PowerPivot

10.   Reporting Services

INCLUDE Index Column Order

You can create an include index as shown below.

USE [AdventureWorks2012]
GO

CREATE UNIQUE NONCLUSTERED INDEX
[AK_Product_ProductNumber] ON [Production].[Product]
(
[ProductNumber] ASC
)
INCLUDE (
[Class],
[Color]
)
GO

So in this there are two columns are included in INCLUDE clause. So the question is, will the column order matters?


Since it is not only the columns in the SELECT list that need to be present in the INCLUDE clause, order of those columns is irrelevant.


However, if you look the UI of INCLUDE index, you will see following screen.


image


In the above screen, you can see that there is an option of changing the order by pressing Move Up or Move Down buttons.


So the question is, if the order of the columns is irrelevant, why you have an option of changing it?

CREATE INDEX Options

Like a series of SET Options, thought of doing another series on CREATE INDEX options in coming weeks.

These are the available options,

image

Saturday, April 20, 2013

Why MongoDB doesn’t need Transactions and Constraints

Eye brows may be raised when we say MongoDB doesn’t support transactions and constraints. First let us look at why we need transactions and constraints in relational databases.

Let us think about a simple blog system in relational databases.

image

So in relational, one transaction need to be saved in three tables. So there can be a scenario where post will get updated and tags will not be updated.  To avoid this, you can run all the statements in a transaction.

Also, there can be another scenario where user can insert into tags without inserting into posts table. So the constraints are used to avoid such situations.

How are we saving this data in MongoDB.

image

So in MongoDB, tags and comments are embedded to the posts documents so that above scenarios will not exist. 

Monday, April 15, 2013

How Does a DBA fit in ITIL process

DBAs find allot of problems and need to implement process to fill the gap. Money or the concept of making money is often lost between internal organizations because of lack of communication and especially lack of process. ITIL is recognized as the de facto standard for IT Service Management.ITIL is a best practices framework. Kind of..ITIL has a strong relationship with the ISO9000 quality framework. Today all the big organization like HP, EDS , IBM strictly follow the ITIL process and shared the same with client so they can rely on them . As per the current job market it is a mandatory for all the support engineer must be attend the ITIL training and should be complete the foundation exam. ITIL has two major groups, service delivery (what services must IT provide to the business) and service support (how does IT ensure access and service).

View it now

Sunday, April 14, 2013

DROP and REMOVE Mongo documents

In MongoDB you have two options to remove all the documents in a collection.

db.users.drop ()

db.users.remove()

thought the output of above two statements ( removing the all the documents in a collection) is same, there are couple of differences with two options.

remove will remove document one by one where as drop will remove all the documents at once. So in case you are dropping documents of a large collation, drop will be much faster than the remove.

However, drop will remove the meta data related to the collection as well. for example, it will remove the indexes associated with the collection.

So if you want to remove all the documents in a large collection, better option is use drop statement and recreate indexes again.

Saturday, April 6, 2013

Viewing Query Plan from Activity Monitor

There are lot of options to view query plan. Activity Monitor has an option of displaying Recent Expensive Queries.

image

You can view the query plan by right clicking the relevant query by as shown below.

image

BIT Column Indexes

There are two myths around BIT column indexes.

Myth #1. Cannot create indexes on BIT columns.

Not sure how this was evolved. But this not absolutely false. Let us try it.

USE tempdb
GO

CREATE TABLE
Employee
(ID INT IDENTITY PRIMARY KEY CLUSTERED,
Name Varchar(50),
Status BIT
)

CREATE INDEX idx_employee_status ON Employee(Status)

Above statement is successful and you can create the index is created.


image


I verified this in SQL Server 2000 SP4 and it was successful.

Myth #2. BIT Column indexes are not useful.


Above argument has a base. BIT column can have possible three values, 0, 1 and NULL. Therefore index selectivity is low.  For example, consider the above table which has 10 million rows and status will say whether these are active or not.  In large table, active records will be very less so active = 0X1 will be highly selective.

SQL Server 2000 Losing Security Support on April 9

SQL Server 2000 is at the end of its product lifecycle and will lose its "extended support" from Microsoft on Tuesday, April 9.

The loss of extended support means no more security updates will be delivered to customers via Microsoft's update service. Remedies for organizations still using SQL Server 2000 are to upgrade the product (Microsoft is recommending moving to its current SQL Server 2012 product) or pay for "custom support" through Microsoft Premier Support services, according to a Microsoft announcement.

It's possible to continue to run SQL Server 2000 after April 9, but that approach isn't recommended by Microsoft for security reasons. Self-help resources, such as Knowledge Base articles and troubleshooting tools, will continue to be available online for a minimum of 12 months.

http://redmondmag.com/articles/2013/04/05/sql-server-2000-support.aspx

Friday, April 5, 2013

SET ANSI_NULL_DFLT_ON & SET ANSI_NULL_DFLT_OFF

This is the ninth post of SET Statement series.

By default when you add a column to the table without specifying the nullability , column will be nullable.

If you run this script,

CREATE TABLE Test
(ID INT)

table will be as follows.


image


You can change the behavior of this by SET ANSI_NULL_DEFT_OFF to ON as shown below.

SET ANSI_NULL_DFLT_OFF ON

CREATE TABLE
Test
(ID INT)

image


You can see that nullability of the column is off.


When SET ANSI_NULL_DFLT_ON is ON, new columns created allow null values if the nullability status of the column is not explicitly specified. When SET ANSI_NULL_DFLT_OFF is ON, new columns created does not allow null values if the nullability status of the column is not explicitly specified.


SET ANSI_NULL_DFLT_ON does not affect columns created with an explicit NULL or NOT NULL.


Both SET ANSI_NULL_DFLT_OFF and SET ANSI_NULL_DFLT_ON cannot be set ON at the same time. If one option is set ON, the other option is set OFF.


If the both options are off , is_ansi_null_default_on value of the database will be taken when tables are created.

SELECT name,is_ansi_null_default_on FROM sys.databases
You can change this option from the database options page as shown below.
image

The setting of SET ANSI_NULL_DFLT_ON does not apply when tables are created using the SELECT INTO statement. It applies only for CREATE TABLE and ALTER TABLE statements.


SET IMPLICIT_TRANSACTIONS ON


SET NOCOUNT


SET DEADLOCK_PRIORITY


SET CONCAT_NULL_YIELDS_NULL


SET FORCEPLAN


SET PARSEONLY


SET IDENTITY_INSERT


SET FMTONLY

Thursday, April 4, 2013

MySQL's creator on why the future belongs to MariaDB

It's fair to say that MySQL creator Michael "Monty" Widenius is not a fan of Oracle. When the company announced in April 2009 that it was purchasing Sun, Widenius saw a bleak future ahead for the (still) wildly popular open source database, which Sun had snapped up in 2008.

The day the Sun purchase was announced, Widenius responded in the tried and true open source fashion — he forked MySQL, launching MariaDB, and took a swathe of MySQL developers with him.

"Many of the original MySQL core developers, including me, didn't believe that Oracle would be a good owner of MySQL and we wanted to ensure that the MySQL code base would be free forever," Widenius explains.

Read more http://www.computerworld.com.au/article/457551/dead_database_walking_mysql_creator_why_future_belongs_mariadb/

Tuesday, April 2, 2013

Robomongo 0.6.8

Are you irritated by using command line to manage MongoDB, there is FREE tool named Robomongo.

You can download executable for windows and Mac from http://www.robomongo.org/ for free. According to the site, Linux version is coming.

image

From the left hand , you have the option of navigating to databases and collection.

Also, you can run the mongodb queries and retrieve the relevant data.

image

Apart from these option followings are the options you have. So , you can duplicate collection and manage Indexes and manage shards.

image

Monday, April 1, 2013

SET FMTONLY

This is the eight post of SET Statement series

when SET FMTONLY set to ON, query will return only metadata to the client. This setting can be used to test the format of the response without actually running the query.

USE AdventureWorks2012;
GO
SET FMTONLY ON
;
GO
SELECT
*
FROM Production.Product;
GO
SET FMTONLY OFF
;
GO

Output of this is,


image


So there will be no rows returned.


However, this SET option is set to depreciate with SQL Server 2012.


Previous posts of this series,


SET IMPLICIT_TRANSACTIONS ON


SET NOCOUNT


SET DEADLOCK_PRIORITY


SET CONCAT_NULL_YIELDS_NULL


SET FORCEPLAN


SET PARSEONLY


SET IDENTITY_INSERT

Microsoft Announces SQL Server 2012 R2 and SQL Server 2012 R3!

Today Microsoft has announced that the next two versions of SQL Server, which will include new features such as Hekaton and clustered, writable columnstore indexes, will adopt the R2 and R3 monikers respectively. Here is the tentative schedule:

  • SQL Server 2012 R2
    • Q3 2013
    • Hekaton in-memory database
  • SQL Server 2012 R3
    • Q1 2014
    • Clustered, writable columnstore indexes