I have two SQL server .. Main data server and Back up data server.
both are running on Windows Server 2003 and having SQL 2000.
What i want to accomplish is to have minimum downtime .... meaning if my Main data server fails .. i can easily point all my client to the back up data server ... with out losing any transactions.. in other words can i have them mirrored like in novell ...
right now, i am just relying on the backup file that we are running every morning in the Main server and restoring it takes about 30 mins... if my main server fails in middle part of the day.. all the transactions that were added in the main server that day will be lost..
any suggestions? .. im looking at the DTS local package, is this ok? a step by step process on how to go about this, will be highly appreciated.
before i was testing the replication features of SQL.. but it is very complicated.. and one time i was able to make it work... but only for a few days.. it just stop with an error in the SQL agent .. finally i abandoned the idea.
thank you in advance.