April 8, 2014 at 10:32 pm
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.
April 8, 2014 at 11:09 pm
SQL Server 2012 AlwaysOn Availability Groups. One primary, one read-only replica.
April 8, 2014 at 11:19 pm
Thanks Cody K,
So read-only replica is always sync with primary and we can query on read-only replica at any time.
April 9, 2014 at 12:45 am
Yep. The 70-462 exam training kit book has a thorough overview of how to set it up.
April 10, 2014 at 8:35 am
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
April 10, 2014 at 1:51 pm
Definitely worth noting. For Standard, you can still use Mirroring or Log Shipping, if you can handle a delay.
April 10, 2014 at 2:49 pm
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.
April 10, 2014 at 3:08 pm
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" 😉
April 10, 2014 at 6:27 pm
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.
April 10, 2014 at 7:21 pm
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