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 |
|
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 |
|
|
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.  |
 |
|
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
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. |
|
|
|
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 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Two Site Replication