Unanswered: combining sql server instances, need help
I have two sql servers, each with several databases on them. Is there an automated, or way to script, combining these servers into one? I want to create a 3rd server, and put all the databases from both server 1 and 2 on it as a back up percaution. My theory is that on server 1 and 2, I will back those databases up nightly via script, and then on server 3, restore the databases nightly. If either server 1 or 2 dies, I simply put that IP address on server 3, and poof! The databases do not go down (however the backup may be some hours old). That gives me time to troubleshoot the down server, and customers do not suffer downtime. Server 3 would also need to be complete with jobs, full text catalogs, logins, etc...
Has anyone developed a system like this? I'm not a wiz in qa yet, so this will take me some time to develop and refine. If you have any tips, I would appreciate it.
Thanks for your answer.
Keep in mind, I don't just want 1 primary, and 1 secondary server. Actually, I have several sql servers. I would like to have all these databases ready on one single spare sql server. Is this possible with log shipping?
Remember , log shipping is available in SQl2000 only for SQl 7.0 Servers you hav to manually set it up . If you are moe intrested , let me know and I can send you some sql files . I have L/shipping setup on all of my production servers .. (17 Primary Servers use 9 Standby servers consolidating 2 Server to 1
That would be great! For example, I have 10 sql servers, with 10 databases each. Now I want to set up one sql server as a backup, and put all 100 databases on it. so when one sql server goes down, I just put that IP address on my backup server, and delete the other 90 databases.
Save the file on the the PATH as mentioned in the previous file as synch_data.bat . The previous file creates a job (has to be run on SQl Qry Analyzer) . The step of the job is calling this batch file attached. You will have to check all the physical references according to your servers .
If you need more detail Let me know