Standby Server in SQL

Hi Manish,

Standby servers - Standby Server is a type of server that can be brought online in a situation when Primary Server goes offline and application needs continuous (high) availability of the server. There is always a need to set up a mechanism where data and objects from primary server are moved to secondary (standby) server. This mechanism usually involves the process of moving backup from the primary server to the secondary server using T-SQL scripts. Often, database wizards are used to set up this process.

We will now glance at the various types of standby servers.

Hot Standby – Hot Standby can be achieved in SQL Server using SQL Server 2005 Enterprise Edition and the later enterprise versions. SQL Server 2005 has introduced Mirroring of database that can be configured for automatic failover in a disaster situation. In the case of synchronous mirroring database is replicated to both the servers simultaneously. This is a little expensive but provides the best high availability. In this case, both primary and standby servers have same data all the time.

Warm Standby – In Warm Standby automatic failover is not configured. This is usually set up using Log Shipping or asynchronous mirroring. Sometimes warm standby is lagging by a few minutes or seconds, which results into loss of few latest updates when primary server fails and newer server needs to be failed over. Sometimes warm server that is lagging by a few transactions is brought back to the current state by applying recent transaction log.

Cold Standby – Code Standby servers need to be switched manually, and sometimes all the backups as well as the required OS need to be applied. Cold Standby just physically replaces the previous server.

I am curious to know what do you all think about this these three types of standby servers. I am eager to know what kind of server setup you have at your workplace. If you can share these details, it will be very useful to all the readers. If I find your information interesting, I will create a new blog post with due credit to you.

Please refer the below link

http://blog.sqlauthority.com/2009/07/11/sql-server-standby-servers-and-types-of-standby-servers/

Thanks

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Satyapriya Nayak14y ago
Hi Manish,

Standby servers - Standby Server is a type of server that can be brought online in a situation when Primary Server goes offline and application needs continuous (high) availability of the server. There is always a need to set up a mechanism where data and objects from primary server are moved to secondary (standby) server. This mechanism usually involves the process of moving backup from the primary server to the secondary server using T-SQL scripts. Often, database wizards are used to set up this process.

We will now glance at the various types of standby servers.

Hot Standby – Hot Standby can be achieved in SQL Server using SQL Server 2005 Enterprise Edition and the later enterprise versions. SQL Server 2005 has introduced Mirroring of database that can be configured for automatic failover in a disaster situation. In the case of synchronous mirroring database is replicated to both the servers simultaneously. This is a little expensive but provides the best high availability. In this case, both primary and standby servers have same data all the time.

Warm Standby – In Warm Standby automatic failover is not configured. This is usually set up using Log Shipping or asynchronous mirroring. Sometimes warm standby is lagging by a few minutes or seconds, which results into loss of few latest updates when primary server fails and newer server needs to be failed over. Sometimes warm server that is lagging by a few transactions is brought back to the current state by applying recent transaction log.

Cold Standby – Code Standby servers need to be switched manually, and sometimes all the backups as well as the required OS need to be applied. Cold Standby just physically replaces the previous server.

I am curious to know what do you all think about this these three types of standby servers. I am eager to know what kind of server setup you have at your workplace. If you can share these details, it will be very useful to all the readers. If I find your information interesting, I will create a new blog post with due credit to you.

Please refer the below link

http://blog.sqlauthority.com/2009/07/11/sql-server-standby-servers-and-types-of-standby-servers/

Thanks