Replication Or Failover Cluster - Best Approach

Hello,

I have a SQL Server 2005 database on one server which on some days can get up to a million new records.  I would like to create a working backup of the database on a secondary server so that if the first fails the second can be used.

I originally thought a failover cluster would be the best approach, but when I installed my second copy of SQL Server the option was grayed out.  I don't need the second server to be a hot backup...it only needs to be ready if the first fails and no records should be lost between the down time.  Can someone please guide me in the right direction?  Would replication do the same thing as a failover cluster?  Why was the failover option grayed out?
craigbtgAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Faiga DiegelConnect With a Mentor Sr Database EngineerCommented:
It actually depends on your needs. replication, clustering, mirroring ahs its pros and cons. Analysing your scenario -- "I would like to create a working backup of the database on a secondary server so that if the first fails the second can be used." -- a Cold standby server would do. The procedure is i think the easiest way to have standby server.

1) install SQL server -- same configuration as your original
2) backup and restore the database
3) maintain copies of the database by periodically applying the transaciton logs.
4) leave the server in Stanby mode.

You have to also plan on your backup scheduling.

These link would give you overview on all availability solution that on SQL 2005: blogs.msdn.com/sql2005event/attachment/564303.ashx
http://www.sql-server-performance.com/article_print.aspx?id=214&type=art
0
 
Faiga DiegelSr Database EngineerCommented:
I believe you have to install MSCS (Microsoft Clustering Services) first before setting up SQL cluster feature. That maybe the reason why it's grayed out.
0
 
Faiga DiegelSr Database EngineerCommented:
And you may find the answer to this thread useful too.
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
Faiga DiegelSr Database EngineerCommented:
0
 
craigbtgAuthor Commented:
Thanks.  Any thoughts on using replication as an option?  It seems like it will work in my scenario also.  Which is the better choice?
0
 
craigbtgAuthor Commented:
Thanks for your help!
0
All Courses

From novice to tech pro — start learning today.