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
 SQL Server 2012 Forums
 Replication (2012)
 Two Site Replication

Author  Topic 

lowfreq
Starting Member

1 Post

Posted - 2013-06-12 : 13:27:29
Im looking at putting in a two server replication model for SQL server 2008.

I'm new to replication, so my question is this...
Our requirement is to have Near Time Sync between two servers, sharing the same instance/data.

Both servers must allow Read and Write.

I've been looking around and I think I need Transactional Replication, but it doesn't say whether writes are permitted to the subscriber.

Thank you for any help you can provide!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-06-14 : 14:29:55
Have you looked at peer to peer replication?

You should consider SQL Server 2012's AlwaysOn feature where you can have a readable replica. You'd only have one replica that's writing, but you'd have two replicas for reading.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-06-14 : 22:03:20
Peer to Peer, as Tara pointed out, is probably the way to go. You will need to manage identity columns and make sure all replicated tables have primary keys, but other than that, it's pretty easy to setup and maintain.

As for identity columns, seed them so there can be no conflicts. For example, in a two node topography, seed the identities on one server odd and the other even. DO NOT box yourself in so that you could run out of numbers (like seeding server A at 1 and server B and 1,000,000). There is no conflict resolution in peer to peer, so you will have to ensure that primary key values are distinct accross all nodes in the topology.

In Transactional Replication, the subscriber(s) should be treated as read only, so that's not a candidate in your case.

I personally can't figure out a use for the always on feature in SQL 2012, but could be convinced. Not if you need all noes to be writable obviously.

EDIT: Tara! Good to see you back.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-06-19 : 21:10:35
quote:
Originally posted by russell


I personally can't figure out a use for the always on feature in SQL 2012, but could be convinced.



Russell, we are currently implementing an AlwaysOn cluster for our most critical product. It will have 6 nodes, 3 at the primary site and 3 at the DR site. One of the nodes at the primary site will be for writes, one of the nodes at the primary site will be for reads (read intent replica), and the third server at the primary site will be a warm standby. The 3 servers at the DR site are in case we lose the primary site. 2 of the servers at the DR site will use async, and the 3 at the primary site will use sync. The last server at the DR site is a cold standby due to an AlwaysOn limitation. All 6 servers have a Fusion-io SSD card, and all 6 are identical hardware (256GB of memory with 4 sockets (8 cores each). These 6 serves will also use a storage array for some of the database load.

The application will use the AG's listener name, regardless of where the primary replica and secondary replica are. So finally no connection string changes when we move production to our DR site for testing!

When on the phone with a Fusion-io engineer a few weeks back, he said in reference to our architecture: wow that's state of the art. This product is absolutely critical, and we've gone all out on it. I'm currently working on the scripts to upgrade the database to 2012, which involves index changes and adding compression. The product is currently back ended by SQL Server 2005 and struggling there. We are using transactional replication for the reports. Can't wait to get rid of replication!



Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-06-26 : 00:34:59
I love replication lol. Please let me know how it all goes. I've been struggling to find a valid use case, but seems you have one. Thanks.
Go to Top of Page

vidyasagr
Starting Member

2 Posts

Posted - 2013-08-13 : 02:30:45
Hi, i am newly joined to learn the sql dba, how many issues are facing in Replication and Entire SQL DBA? Please advise me about this.

sagarvidya
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-08-13 : 11:38:40
quote:
Originally posted by vidyasagr

Hi, i am newly joined to learn the sql dba, how many issues are facing in Replication and Entire SQL DBA? Please advise me about this.

sagarvidya



Please start a new thread for any questions that you may have. Your question isn't clear enough for us to help though, so please explain in further detail (in a new post).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -