Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 can you write to AG Replicas?

Author  Topic 

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2013-06-14 : 22:52:48
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

5072 Posts

Posted - 2013-06-15 : 00:47:53
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

740 Posts

Posted - 2013-06-15 : 00:55:59
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

5072 Posts

Posted - 2013-06-15 : 01:44:15
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

740 Posts

Posted - 2013-06-20 : 15:31:13
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

740 Posts

Posted - 2013-06-27 : 10:28:18
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
   

- Advertisement -