Database Server administration concepts for Architects/ Tech Leads/ Database developers

Most of us must have heard of Database replication/ mirroring but few of us would have tried to explore the details. This article explains about these concepts which would give you an overall understanding of Database server concepts which are used for large applications.


What is Database Mirroring?
Database mirroring is a primarily software solution for increasing database availability.
It maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine.
What is Database Replication?
It is a set of technology for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.
Most of the development community think that above two mentioned questions are enough for database backups/ availability. There is yet another way which is most popular among the Database administrator's which is "Log Shipping".
So what is Log shipping?
It is sending transaction log backups from one database (Primary database) to another (Secondary database) on another server. An optional third server known as Monitor server records the history and status of the backups and restore operations. 
Server limitations for above mentioned techniques
Log Shipping --> It can be configured as One to Many. i.e one primary server and many                                secondary servers. 
                        Or
                        Secondary server can contain multiple Primary databases that are log                               shipped from multiple servers.
Mirroring      --> It's one to one. i.e., One principal server to one mirror server.
Replication  -->
  • Central publisher/distributor, multiple subscribers.
  • Central Distributor, multiple publishers, multiple subscribers.
  • Central Distributer, multiple publishers, single subscriber.
  • Mixed Topology.

Backup/ Restoration
Log Shipping -->This can be done manually or through Log Shipping options.
Mirroring      --> User take backup & restore manually.
Replication  --> User creates an empty database with the same name.
Note: If you see any corrections or have suggestions please do let me know.

Comments

Post a Comment

Popular posts from this blog

Shared Nothing Architecture (SN Architecture) and Sharding database

  SN Architecture : A shared  nothing architecture (SN) is a distributed computing approach in which each node is independent and self-sufficient, and there is no single point of contention required across the system.   This means no resources are shared between nodes (No shared memory, No shared file storage)   The nodes are able to work independently without depending on each other for any work.   Failure on one node affects only the users of that node, however other nodes continue to work without any disruption.   This approach is highly scalable since it  avoid  the existence of single bottleneck in the system. Shared nothing is recently become popular for web development due to its linear scalability. Google has been using it for long time.     In theory, A shared nothing system can scale almost infinitely simply by adding nodes in the form of inexpensive machines.   Sharding:   is  an  architectural approach that distributes a single logical database system into a cluster of mac
Read more

Log shipping vs Mirroring vs Replication for Data Architects/ DBAs

Log  Shipping:   It automatically sends transaction log backups from one database (Known as the primary database) to a database (Known as the Secondary database) on another server. An optional third server, known as the monitor server, records the history and status of backup and restore operations. The monitor server can raise alerts if these operations fail to occur as scheduled.     Mirroring:   Database mirroring is a primarily software solution for increasing database availability.   It maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine.   Replication:   It is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Intern
Read more