I am trying to set up a transactional replication database on two MSSQL 2008 Standard servers. We need to be able to query off of the subscriber database, but we would also like to be able to use the subscriber as a backup if the primary server fails.
Is it at all possible to use the subscriber database, if it is replicating ALL of the data from the publisher, as a backup primary database, or do I need to use some other form of replication/mirroring that still allows near real-time querying?
You need to ask yourself some specific questions on what you want then it will be easy to use the right solution.
Do you want to read the data from the secondary in real time? Do you want to be always connected to the secondary or is it ok to get disconnected for few seconds to sync from primary? Do you want to do this for one db or for 50 databases? Do you want to switch to the secondary when primary fails automatically? Or do you want this to handle manually and sometime delay is fine here?
Based on the above, you can determine if you need log shipping/ database mirroring/replication.
