I was told that my company is thinking about inplementing a "backup" server for out content loading server (aka load server)... this server loads files from several companies and then replicates the data out to our production environment to be seen. This server has serveral DTS/IS packages that do all this data movement and manipulation... I am not too up to date on technology for what they are asking so I thought I would ask here for some recommendations to head in the right direction...
I am not sure if clustering will work or maybe mirroring the database...
The server now is a publisher so the backup server would have to pick up if the #1 failed and then give command back to #1 once it is good... this is the part I have no idea about...
The #2 server would not have to really do anything but keep its data up to date unless #1 fails so you could say it would be fine to be a passive server...
Any ideas on what direction I should look into?
Thanks ahead of time.
It sounds like mirroring may be the best solution for you.
WIth synchronous mode, you can have the failover happen automatically and transparent to the application.
It also gives you a second copy of the data.
|||Would mirroring handle the publishing also?
This is the biggest issue I have...
|||Sorry, but I would NOT deploy Database Mirroring for this. It isn't even going to begin to cover your needs.
Clustering would provide hardware protection while at the same time keeping all of your loading and replication processes running.
Database Mirroring would require you to mirror each of your user databases. You would then have to create processes to keep all of your logins, jobs, SSIS/DTS packages, linked servers, etc. synchronized between the two machines. (Database Mirroring would NOT handle any of this stuff.) The replication engine could pretty much seamlessly failover with Database Mirroring, but you would have a LOT of heavy lifting to do to make your standby truly a standby that you could failover to. Just because it is a new technology, everyone wants to use it all over the place.
In your case, clustering would provide a much simpler DR scenario, particularly since the subscribers can continue to run while the publisher is offline. This isn't going to help if you take a hit to the entire data center, so you will want something in addition to clustering to maintain a second copy of your data which could be loaded back to a rebuilt server.
|||So it sounds like clustering would be the best bet for the DR setup I need.
That was what I was leaning towards initially but I didn't know to much about clustering...
Is there any good documentation/white papers on clustering?
|||There are several docs in Books Online, several webcasts on MSDN, and lots of stuff on various websites, newsgroups, and mailing lists. The biggest item is getting it installed and configured. After that, there is nothing special from a SQL Server side for managing one.|||http://www.sql-server-performance.com/clustering_resources.asp|||Thanks for everything... I will start reading up on it...