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.
Reference : Pinal Dave (https://blog.sqlauthority.com)
9 Comments. Leave new
using Netapp Filers for our storage and utilizing snapmirror we have a mix of it all. We have production running on sql2005 while transactions are backed up hourly, once the backup is done the data is mirrored to the mirrored volume at at DR site. At the DR site we have a sql2005 box ready to mount any of these mirrored, verified volumes. From there we can do a restore via Netapp SME, and get the db where we want it.
Our company is very interested in a cold standby strategy as part of a DRP for one of our customers. The goal is to incur as little as possible any up time expenses in terms of infrastructure maintenance, licensing and energy costs, etc…
Acceptable parameters:
1. Recover within 4-8 hours.
2. Last night’s full backup.
3. Cost no more than $50 a month.
Nice to have:
1. Ability to scale out recovered environment when deemed necessary.
2. Up to the minute recovery of transaction data.
3. Recover within 1-2 hours.
I conducted a proof-of-concept a few months ago that is being called into action currently. The plan is to use the low cost non-Cap. Ex. cloud computing hosted by Amazon.
On the Amazon Cloud, we quickly put together three servers, a DC, a Citrix server farm with a single server in it, and a MSSQL server running on Amazon’s Standard Large AMI. Once configured to the way we want, these images are bundled and saved on the Amazon S3.
Challenges:
1. DRP involves more than just the database server, changes to the DC and user profiles also need recovering. What is the best and easiest way to replicate changes to the dormant environment. We know all changes would have to be stored on the S3 in Amazon to be replayed later. Would daily exporting of AD objects suffice?
2. Is doing a daily full backup a good idea even though the databases are relatively small, lets assume, 25 databases each about 4 GB. With this strategy, we can only guarantee the recovery of last night’s backup. We also have hourly transaction log backups. How can we best make use of these log backups to meet one of the “nice to have” requirements?
3. Are there other challenges we have not foresee?
Level 1
————
Our company has certain data which requires high availability so we use the database mirroring along with witness ( debatable) to make sure we can switch over with no data loss.
Having said that, the challenge here is to maintain not only the database but also the ssis packages hosted on the server, ssrs reports on the server.
Since this information is stored on msdb, it becomes very critical as we cannot replicate the msdb database as far as I know, as it will have the reference to the original msdb database.
So we had planned to replicate the entire scenario of ssis packages and ssrs reports on to the mirror server.
The next challege here is the principal server has certain data integrations to another sql server. These integrations have to be replicated but the server reference has to be changed if linked servers are used.
The next challenge we had is to do database mirroring between different locations for eg. US and europe, we achieved this by creating a common domain for the sql servers and it worked like charm. Now the speed could be a issue will keep posted based on the performance.
The level1 gives the comfort of high availability of data with automatic failover even in case of a server failure.
Level 2
———-
The data is backed up daily and transaction backups are done every 2 hours.
Level 3
———-
The data is stored external to the building.
Dave
Here is our current situation..
Our production server has databases where it is used just to read the data and we have another server where we load data (no updates) and then backup it up and restore on to production with different name and then switch over the databases which is a 5 min downtime for production databases.
sql 2005 Ent,Win 2003,litespeed,databse size range from 300GB – 2 TB.
Now I am plnning to reduce the time spent on backing up and restoring on to production, do you think mirroring or any other option will work and how expensive they are.
Hello Mike,
On production server where database is used just for read a Mirrored database can be configured and then create a snapshot of mirrored database for reading purpose.
Mirroring in asynchronous mode (high performance) does not much affect the performance of primary database.
Regards,
Pinal
Hello Mike,
On production server where database is used just for read a Mirrored database can be configured and then create a snapshot of mirrored database for reading purpose.
Mirroring in asynchronous mode (high performance) does not much affect the performance of primary database.
Regards,
Pinal Dave
would you know how to link a server to analysis services 8.0 so that I can pass mdx query through sql from powerbuilder.
“Cold Standby – Code Standby servers need” – a typo in third word.
Hi,
very nice post.At my work place we are having Warm Standby Server onfiguration in which we are shipping logs every hour from production to standby which we are using perticularly for reporting purpose.
But some times weare facing many issues while log shipping if n/w gets slow down.But its easy & nice set up but it needs extra care for checking log shipping process.