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
 SQL Server 2012 Forums
 Replication (2012)
 Two Site Replication
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lowfreq
Starting Member

Canada
1 Posts

Posted - 06/12/2013 :  13:27:29  Show Profile  Reply with Quote
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

USA
35940 Posts

Posted - 06/14/2013 :  14:29:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
5071 Posts

Posted - 06/14/2013 :  22:03:20  Show Profile  Visit russell's Homepage  Reply with Quote
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.

Edited by - russell on 06/14/2013 22:04:48
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 06/19/2013 :  21:10:35  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
5071 Posts

Posted - 06/26/2013 :  00:34:59  Show Profile  Visit russell's Homepage  Reply with Quote
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

India
2 Posts

Posted - 08/13/2013 :  02:30:45  Show Profile  Reply with Quote
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

Edited by - vidyasagr on 08/13/2013 02:33:06
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 08/13/2013 :  11:38:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  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.09 seconds. Powered By: Snitz Forums 2000