two prod database servers:-one is for insert/update/delete another one is for select

  • Hi Experts,

    currently we used to perform insert/update/delete/select operations on transnational tables in same database.

    In order to make efficient enterprise case management system,we are looking for 2 database servers , planning to use one server for insert/update/delete operations and second server used for select/reporting purpose .

    and these 2 servers should be sync with each other with in milliseconds.

    is this something possible ? or any other ways to achieve this.

    Please guide me.

  • SQL Server 2012 AlwaysOn Availability Groups. One primary, one read-only replica.

  • Thanks Cody K,

    So read-only replica is always sync with primary and we can query on read-only replica at any time.

  • Yep. The 70-462 exam training kit book has a thorough overview of how to set it up.

  • Something to note however is you will need enterprise edition on both the main instance and the replica which will double your licensing costs (or more than double if you are running standard edition in your production environment)

    - Tony Sweet

  • Definitely worth noting. For Standard, you can still use Mirroring or Log Shipping, if you can handle a delay.

  • belvdr (4/10/2014)


    Definitely worth noting. For Standard, you can still use Mirroring or Log Shipping, if you can handle a delay.

    If a delay is okay, Incremental data loads at maintenance window periods is also good. 😀

    Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

  • Jampandu (4/8/2014)


    So read-only replica is always sync with primary and we can query on read-only replica at any time.

    Provided the AlwaysOn replicas are configured for synchronous commit, they'll be in sync. Provided you set the secondary replica to Read. Or create a listener, configure ReadOnly routing and set the secondary replica to read-intent only they can be queried.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • belvdr (4/10/2014)


    Definitely worth noting. For Standard, you can still use Mirroring or Log Shipping, if you can handle a delay.

    You can't use secondary mirrors for reporting without enterprise edition and its snapshot functionality. That mirror must also be licensed because it's being actively used.

    You can't use log shipping for reporting unless you plan to disconnect all the users on the schedule you're using to apply the new log backups.

  • Cody K (4/10/2014)


    belvdr (4/10/2014)


    Definitely worth noting. For Standard, you can still use Mirroring or Log Shipping, if you can handle a delay.

    You can't use secondary mirrors for reporting without enterprise edition and its snapshot functionality. That mirror must also be licensed because it's being actively used.

    You can't use log shipping for reporting unless you plan to disconnect all the users on the schedule you're using to apply the new log backups.

    we will use enterprise edition only

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply