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.