SQL Server 2014 Transactional Replication

SQL 2014 DAG do not have merge replication ?

Not my main expert area, but as far as I see:

When you want to switch the DR site to become the primary role, it has to offer same functionalities as the current live site, doesn't it?

I see there are some failover modes in which you might only use a replica database for read only access.

Bye. Olaf.

For MSSQL 2012 and 2014, AG is only supported on Enterprise Edition.
I read something about a Basic AG solution that will be available in MSSQL 2016 Standard Edition but the replica wouldn't be available to read and nor for backups.

right now the SQL 2014 enterprise DAG still at most can read only, right?

the read will be load balanced in all replica ?

I knew since SQL 2016 enterprise, all read is load balanced between replica !

WSFC = Windows Server Failover Cluster

AlwaysOn Availability Groups are built on top of Windows Server Failover Clustering.  They leverage the mechanisms it uses for keepalives and all that jazz to detect failover conditions.  So when you are building the DR site, you would need to join that Windows Server to the windows server failover cluster before you can configure AlwaysOn on it.

So that said if you want your DR site to be a part of the availability group - you need Enterprise.  AlwaysOn is an Enterprise only feature.

I am a little confused as to where the merge replication is in the system.  Are you saying you need merge replication into the DR site (ie. DR site is fed from multiple servers that are all writable)?

I am sorry ,what is basic AG solution ?

Is something that doesn't exist yet but should be released in MSSQL 2016 version. Basically is old Mirror solution built in AG.

the read will be load balanced in all replica ?

AG isn't a load balancing solution but High Availability and Disaster Recovery solutions.

PadawanDBA,

"AlwaysOn Availability Groups are built on top of Windows Server Failover Clustering.  They leverage the mechanisms it uses for keepalives and all that jazz to detect failover conditions.  So when you are building the DR site, you would need to join that Windows Server to the windows server failover cluster before you can configure AlwaysOn on it."

yep, very reasonable, do the DR serve also inside the SAME DAG always on group ? and as DR site always miles aways, can DAG handle it well, like the design of mirror ? I heard DAG just a much better mirror techonology, right?

and in this case, application no need to change SQL Server name as the DR SQL serve is not going to have the same server name ? the DB listener will handle it using the same SQL server connection name ?

"So that said if you want your DR site to be a part of the availability group - you need Enterprise.  AlwaysOn is an Enterprise only feature."

oh yes.

"I am a little confused as to where the merge replication is in the system.  Are you saying you need merge replication into the DR site (ie. DR site is fed from multiple servers that are all writable)? "

yes, we are using SQL 2008 with 2 x subscriber and 1 x publisher. all using bidirectional merge replication.

yes, we can write to diff subscriber at diff time and they will merge the change by time stamp, etc.

Vitor Montalvão,

"Is something that doesn't exist yet but should be released in MSSQL 2016 version. Basically is old Mirror solution built in AG."

on standard edition so it is cheaper  ? but DAG also use mirror solution as it is a much better mirror? sql2015 start to use mirror but that mirror is not good and if mirror on VMware, is a nightmare

""the read will be load balanced in all replica ?
AG isn't a load balancing solution but High Availability and Disaster Recovery solutions. "

SQL 2016 spec has it, we start it have it on SQL 2016, please correct me if I am wrong.

on standard edition so it is cheaper  ?

That's the rumors.

but DAG also use mirror solution as it is a much better mirror?

Yes.

SQL 2016 spec has it, we start it have it on SQL 2016, please correct me if I am wrong.

Can you share those specs with us?

and as DR site always miles aways, can DAG handle it well, like the design of mirror ?

You will want to make sure that the DR site is set up as an asynchronous replica and depending on the reliability of your link between your datacenters, you may want to revoke it's quorum voting abilities so it can't impact failovers in your primary production site.  It's not a blanket rule, but there are cases where it makes sense.

I heard DAG just a much better mirror techonology, right?

It's really a best of replication and mirroring rolled into one.  The TCP endpoints are mirroring endpoints - under the hood it is *very* similar to mirroring.

yes, we are using SQL 2008 with 2 x subscriber and 1 x publisher. all using bidirectional merge replication.

So are you looking at replacing the replication or how do these fit into the topology?  I'm just confused where the AlwaysOn AGs are coming into play.

Vitor Montalvão,

SQL 2016 spec has it, we start it have it on SQL 2016, please correct me if I am wrong.
Can you share those specs with us?

can't now :):)

PadawanDBA,

"so it can't impact failovers in your primary production site.  It's not a blanket rule, but there are cases where it makes sense.
"

what I want is the replica inside the DAG can be miles away, in another DR city or country ?

"It's really a best of replication and mirroring rolled into one.  The TCP endpoints are mirroring endpoints - under the hood it is *very* similar to mirroring."

replication ? merge replication ? can replace this ? I don't think so ..

"So are you looking at replacing the replication or how do these fit into the topology?  I'm just confused where the AlwaysOn AGs are coming into play. "

we are trying to. but I know it is not possible.

I am concerning more on where the replica can stay in the DAG group, as we have DR project too and we don't want to, once failover to the DR server, application name and SQL server name don't need to change.

once failover to the DR server, application name and SQL server name don't need to change.

Aren't you confusion DR with HA?

what I want is the replica inside the DAG can be miles away, in another DR city or country ?

That's fine.  Make it an asynchronous replica and monitor the health of the link between the DR site and your primary site.  If it keeps failing heartbeats remove it's quorum vote.

replication ? merge replication ? can replace this ? I don't think so ..

You're correct.  Can *not* replace merge replication (i was moreso referring to transactional replication).  Replication is the only option for an out of the box solution for multiple writable nodes.

we don't want to, once failover to the DR server, application name and SQL server name don't need to change.

Makes sense.  With an asynchronous replica, you can't enable automatic failover, so it will require manual failover to the dr site.  That said, I don't know of an instance where you would want to automatically fail over to your DR site.  As long  as all of your connection strings point to the listener, nothing would need to change from that perspective.

Vitor,

"Aren't you confusion DR with HA? "

no.

in HA it failover by itself from time to time automatically expect we set it not to, the failback a lot of time is manual.

our definition can be different, however but it is what we are doing here.

you know I said DAG is using Mirror technology and as MS using mirror solution basically mainly for DR solution and as DAG use mirror technology, that's why I talk about that.

PadawanDBA,

"Make it an asynchronous replica and monitor the health of the link between the DR site and your primary site.  If it keeps failing heartbeats remove it's quorum vote."

tks.

that's why it operate like a cluster.

"You're correct.  Can *not* replace merge replication (i was moreso referring to transactional replication).  Replication is the only option for an out of the box solution for multiple writable nodes . "

ok, very clear.

but I can tell you that the SQL server PDW edition, the SQL server version of RAC, can do this and each SQL server in that RAC is a clone of each other, SQL server will just make the RAC in DB level instead of cluster level, the oracle method.

this is the real multi master SQL serve edition since SQL2008.

"Makes sense.  With an asynchronous replica, you can't enable automatic failover, so it will require manual failover to the dr site. "

then can't implement DR failure for this case?

"As long  as all of your connection strings point to the listener, nothing would need to change from that perspective. "

listener handle the change in name transform for us ? I guess so... so no need to change name anyway ..

"That said, I don't know of an instance where you would want to automatically fail over to your DR site.  "

I don't get this bit, you mean everything relies on listener to check which one is the DR server ?

hi,

any doc/link which confirm failover on DR solution using DAG doesn't involve the change of server name/connection on both SQL server size and application size?

that one is interesting.

and which also said this DR configuration need to be in async mode  ?
 
any step by steps guild to create DR solution in which serve in 2 x diff city (long distance) using SQL server 2014 with SP1
?

marrowyung, did you hijack this question?

We never heard nothing from the OP (htam).

I am sorry, why you say that?

hi, it seems that I can't close this question as I can't see the close button

The question was posted by the user "htam", of course you don't see a close button logged in as marrowyung. Either you just had the same question and just appended your to htams or your company owns both accounts, you have to login as htam to close the question, if you are that account, too.

Bye, Olaf.

then ignore it , I am just confused.