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.
| 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. |
 |
|
|
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! |
|
|
|
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. |
|
|
|
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! |
|
|
|
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).aspxany ideas? |
|
|
|
|
|
|
|
|
can you write to AG Replicas?