Point in Time Recovery - MongoDB

In production environment database management system should have this feature since there can be situation where you need to recover your database for given time( database looks like same state for given point )

This is well-defined and valuable process for current database management system. MS SQL Server , Oracle DB2 etc  have good steps to do point in time recovery and you can find lot of information regarding these type of RDBMS.

But with NoSQL Technology very less resources are available for this topic and today I'm going to explain how we can do Point in time recovery in MongoDB.

Important : 

When you get stuck with problem in mongo databases better to start with new mongodb instance and do all the restoring to that instance and test/validate. if everything looks good for you then you can transfer data to appropriate place like primary server and allow replication propagate the corrected records to the secondaries. 

Problem : 

The backupDB database has one collection, backupColl. At midnight every night, the system is backed up with a mongodump. 

Your server continued taking writes for a few hours, until 02:46:39. At that point, someone (not you) ran the command:

 db.backupColl.drop()

Your job is to put your database back into the state it was in immediately before the database was dropped, 

Answer :

Step 1 :  Restore your latest backup file into new mongodb instance. this is server going to be your test server.

mongorestore -h <hostname:port> <backup file path>

Step 2 : Take oplog backup from existing server ( better to take it form Primary or large oplog file among member servers). Oplog file keep all operations and it is capped fire but it is not a BACKUP file.

mongodump -h <hostname:port> -d local -c oplog.rs -o oplogD

Step 3 : Move and rename this "oplog.rs.bson" file to "oplog.bson"

mkdir oplogR
mv oplogD/local/oplog.rs.bson oplogR/oplog.bson

Step 4:  Then you have to find exact timestamps for delete operation happened. for that you can convert your backup oplog.bson file. you can use dumpmongo command to convert this file to human readable format.

bsondump oplog.bson > oplog.txt  

bsondump oplog.bson > oplog.json

Then you can use grep command or simple find mechanism to find "drop" keyword. if not you can you your existing database to find this 

db.oplog.rs.find()

Your goal would be find "ts" field for given keyword ("drop")

"ts" : Timestamp( 1398778745, 1 )

Please make this value like this : 1398778745:1

Step 5: Note that the mongorestore command has two options, one called --oplogReplay and the other called oplogLimit. You will now replay this oplog on the restored stand-alone server BUT you will stop before this offending update / delete operations. ( basically server should not be modified )

mongorestore -h <hostname:port> --oplogReplay --oplogLimit 1398778745:1 oplogR

This will restore each operation from the oplog.bson file in oplogR directory stopping right before the entry with ts value Timestamp( 1398778745, 1 )

Step 6: Once you have verified it then you can write the restored records to the appropriate place in the real primary (and allow replication propagate the corrected records to the secondaries).

High Availability on MongoDB

This is article completely summary of official mongo site : http://docs.mongodb.org/manual/core/replica-set-high-availability/

Replica Set

• Replica set provides High Availability using automatic failover. Failover allows secondary members to become primary if primary is unavailable. Failover, in most situations does not require manual intervention.
•  Replicas set members keep the same data set but they are independent.
• To select Primary Server there will be an election between other servers. Place a majority of voting members and all the members that can become primary in this facility.

Replica Set Elections

•          Replica sets use elections to determine which set member will become primary. Election occurs after initiating a replica set, and also any time the primary becomes unavailable
•          The primary is the only member in the set that can accept write operations.
•          Elections are part of the failover process
•          While an election is in process, the replica set has no primary and cannot accept writes.

Factors and Conditions that Affect Elections

Heartbeats

•          Replica set members send heartbeats (pings) to each other every two seconds. If a heartbeat does not return within 10 seconds, the other members mark the delinquent member as inaccessible.

Priority Comparisons

•          The Priority setting affects elections. Priorities specify higher values to make a member more eligible to become primary and lower values to make the member less eligible to become primary.
•          Priority 0 = Cannot become primary and do not seek election

Optime

•          The timestamp of the last operation that a member applied from the oplog.

      Connections

•    A replica set member cannot become primary unless it can connect to a majority of the members in the replica set

Election Triggering Events

•          Replica sets hold an election any time there is no primary. Specifically, the following:
•          The initiation of new replica set.
•          A secondary loses contact with a primary. Secondaries call for elections when they cannot see a primary.
•          A primary step down.

o   After receiving the replSetStepDown command

o   If one of the current secondaries is eligible for election and has a higher priority

o   If primary cannot contact a majority of the members of the replica set.

Note: When a primary steps down, it closes all open client connections, so that client don’t attempt to write data to a secondary. This helps client maintain an accurate view of the replica set and helps prevent rollbacks.

Participation in Elections

• By default, all members have a priority of 1 and have an equal chance of becoming primary. In the default, all members also can trigger an election.
• Only members in the following states can vote: PRIMARY, SECONDARY, RECOVERING, ARBITER, and ROLLBACK.

Non-Voting Members

• Non-voting members hold copies of the replica set’s data and can accept read operations from client applications
• Non-voting members do not vote in elections, but can “veto” an election and become primary.

It is about MongoDB indexes.

No need to re-write again. Yeah this article is so simple to read and understand.

Link : http://docs.mongodb.org/manual/core/indexes-introduction/

Thank you very much 10gen come up with good articles. seems like MongoDB documentation is getting more easy to read and understand.



 

sys.sysindexes

Today, I’m going to discuss about one of the system view called “sys.sysindexes”. This contains one row for each index  in the current database.

• To Get All the Index in Current Database

         SELECT * FROM sysindexes

• To Get All the Indexes on “Response” table

         SELECT * FROM sysindexes where id = object_id('Response')

• To get the row count in “Response” Table

         SELECT rowcnt FROM sysindexes where id = object_id('Response') and indid < 2 

what is the mean about "indid " :
Answer : ID of the index:
                    0 = Heap
                    1 = Clustered index
                    >1 = Nonclustered index

Even though you are trying to select the 2 rows (indid < 2 ) , you will never get it. Because your table ('Response') is either Heap or Clusted/non-clusted index table.

You can use this view to get the row count easily without putting weight on the table.

Keep in mind :

XML indexes are not supported

Partitioned tables and indexed are not fully supported

More info : http://technet.microsoft.com/en-us/library/ms190283.aspx

Top 10 New Features in SQL 2012

http://blogs.technet.com/b/jweston/archive/2012/04/18/top-10-new-features-in-sql-2012.aspx

The Logical Query Processing in T-SQL

Today I’m going to discuss about the Logical Query Processing in T-SQL

Logical Query Processing:  The conceptual interpretation of the query that explain what the correct result of the query is

Physical Query Processing:  Processing of the query by database engine. Produce the result defined by logical query processing.

Following are the Main Query Clauses in the order that you are supposed to type (known as “keyed-in-order”)

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY

But the Logical Query Processing order, which is the Conceptual interpretation order, is different

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Because of this logical order you won’t get expected result for below sample query,

SELECT Name , YEAR(Joindate) AS JoinDate

FROM dbo.Emplyees

WHERE JoinDate > 2000

This should be,

SELECT Name , YEAR(Joindate) AS JoinDate

FROM dbo.Emplyees

WHERE YEAR(Joindate)  > 2000

What is SQL Server Operating System ( SQLOS)

In the past, scalability was the major issue in SQL Server because it supported only for small or medium sized workloads due to its engine capabilities. With this issue Microsoft had to make massive changes to SQL server to serve for the large workloads demands

The Key observation here is that DBMS and OS schedulers must work together. Either OS must have built in support for DBMS or DBMS must have a special scheduling layer.

If I summarized the SQLOS:

• Application Layer between SQL Server components and the OS

• Provides more refined control of resource allocation

• Centralizes resource allocation to provide more efficient management and accounting

• Abstracts the concepts of resource management from components such as the Query Engine & Query Optimizer

The SQLOS was created to centralize common low-level tasks within the SQL Server process.  Having a central location for these tasks means less duplication of code within the various components of the engine, but it also offers the flexibility to adjust SQL Server to new and advanced hardware architectures without impacting the other areas of SQL Server code.

The SQLOS behaves very much like an operating system.  It abstracts the concept of memory management, I/O, scheduling etc. from the other components within the SQL engine.  In this way, these components do not need to worry about managing things like NUMA and Resource Governor, they simply make resource allocation calls to the SQLOS via an API.

The SQL engine is still a process like any other process running on a Windows server.  It does not have any special privileges or priority over other process.  The SQLOS does not bypass Windows, it simply manages the resources within the SQL Server process space in a way that is efficient for SQL Server.

Additional Reading : http://blogs.msdn.com/b/sqlosteam/archive/2010/06/23/sqlos-resources.aspx