SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 can you write to AG Replicas?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

albertkohl
Aged Yak Warrior

USA
738 Posts

Posted - 06/14/2013 :  22:52:48  Show Profile  Visit albertkohl's Homepage  Reply with Quote
is there any way to setup SQL 2012's Availability Groups to allow you to write to the Secondary (replica) Node, and that transaction replicate back to the Primary Node? or can you ONLY write to the Primary Node?

Thanks in advance!

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 06/15/2013 :  00:47:53  Show Profile  Visit russell's Homepage  Reply with Quote
There would be no reason to do that. Anyway, according to BOL you can't.

If you want multiple nodes to be writable then Peer to Peer Transactional Replication is what you want. I've posted many times about the benefits and caveats of Peer to Peer.

Feel free to post back if you have questions about it.
Go to Top of Page

albertkohl
Aged Yak Warrior

USA
738 Posts

Posted - 06/15/2013 :  00:55:59  Show Profile  Visit albertkohl's Homepage  Reply with Quote
do you have any links on how to set it up? in my deployment, i have two servers, one i consider primary, and a secondary one. 90% of the writes come from the primary, but every now and again, i'd like to write to the secondary and have it relay that transaction back to the primary. also, if the primary goes down, i'd like to be able to bring it back up to day using the secondary when it comes back online.

Thanks!
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 06/15/2013 :  01:44:15  Show Profile  Visit russell's Homepage  Reply with Quote
Follow the link in my earlier post. Then the links at the bottom of that page.

But first...may I ask some questions?

If 90% of the writes are to be to one node, why not make it 100%? Then you have several HA options available.

How many databases need to be synched? I ask because perhaps mirroring or Availability Groups really are the solution, but again, read only for the failover server.

Is what you really want failover clustering? This provides automatic failover in the event of a server failure, but each node shares the same storage. So an SAN/Filer error will bring down the entire cluster.

In short, what is your SLA? Then it will be much easier to help recommend an appropriate strategy.


Go to Top of Page

albertkohl
Aged Yak Warrior

USA
738 Posts

Posted - 06/20/2013 :  15:31:13  Show Profile  Visit albertkohl's Homepage  Reply with Quote
my original issue was that i didnt want to replicate certain operations to both servers, so instead, i used a little of both. A.G. for the main data, and then a separate db w/ replication for the articles i care about for the other 10%. works great. Thanks!
Go to Top of Page

albertkohl
Aged Yak Warrior

USA
738 Posts

Posted - 06/27/2013 :  10:28:18  Show Profile  Visit albertkohl's Homepage  Reply with Quote
Hello Russell, needed to resurrect this topic. i tried setting up the peer-to-peer replication, but i keep getting an error when i try to add the second node:

TITLE: Configure Peer-To-Peer Topology Wizard
------------------------------

SQL Server could not create a subscription for Subscriber 'DBSERVER01'.

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

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

The publication 'NMOL_PROCESSING' does not exist.
Could not update the distribution database subscription table. The subscription status could not be changed.
The subscription could not be created.
The subscription could not be found.
Changed database context to 'PROCESSING'. (Microsoft SQL Server, Error: 20026)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=20026&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


here's the write up i was using: http://msdn.microsoft.com/en-us/library/ms152536(v=sql.105).aspx

any ideas?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000