locked
Accessing the mirrored servers RRS feed

  • Question

  • Hi

    We're in the process of designing an OLTP system having one server as the main server against which the application will connect and execute INSERTS, UPDATES, DELETES and SELECTS while a backup real-time synch server/DB against which all reports will run. The scope of all this is to split the load across two servers. both servers will be located in the same building connected via Gbit NICs.

    Now, my question. I was thinking of implementing asycronous DB mirroring between the servers. Does this option sounds fine? Will we be able to access (re-direct) all reporting requirements to the mirrored server while the principal server will take care of the transaction processing. I'm not sure if the mirrored server is accessibile during mirroring, i.e. active! (NO DATA updates will be done on the mirrored server).

    The alternative to mirroring is to use one-way transaction replication. I've tried this and it works however I'm not sure about this concept.... If on the published DB, I insert a record in a table which in turn will fire a trigger which will insert another record in a table, (these are two transactions), what will happen on the subscribed DB? will it receive 2 transactions (the one done by the application and the one by the trigger)? I'm assuming yes and therefore I'll have to take care of that on the subscribed DB (using the NOT FOR REPLICATION option)... will I have to face this also for DB mirroring, i.e. I'll receive 2 transactions?

    From your experience, which option will work and will work best?

    Regards
    Brian

    Tuesday, February 2, 2010 10:12 AM

Answers

  • Hi Brian,

    The mirrored instance in a daabase mirroring configuration is not accessible unless it is failed over . You cannot direct any of your OLTP operations to the mirrored databases , becasue the mirrored db is always ( in restoring state) .

    The only option with respect to mirroring is go for taking database snapshots of your mirrored instance on a daily basis and allow your reporting users t access the db snapshot. But this is unfortunately only an enterprise edition feature ($$ matters)

    Replication would be the best option. As lons as both the tables are marked for publishing the changes on both the table will be replicated.


    Thanks, Leks
    • Marked as answer by Brian118 Wednesday, February 3, 2010 3:36 PM
    Tuesday, February 2, 2010 5:17 PM
    Answerer