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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Faiga DiegelSr Database EngineerCommented:
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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
Faiga DiegelSr 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
craigbtgAuthor Commented:
Thanks for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.