3

We have a new production box with two SQL servers,SQL Server 2016 Standard edition. The current plan is to have:

  1. One primary SQL server
  2. Backup SQL server:only SQL Report server,which points to primary server.

Can you please suggest any good replication strategy to move data from primary to back up server and which does not use Single User mode. Realtime replication is expected.

In primary server lots of insert/update happens and will be online 24/7. New into Replication.

Only one database in Primary SQL server,have to replicate the same db tables/views/triggers etc into Backup server.

Have done some research on replication(Snapshot,Transaction,Merge),but i am not sure about the Single User mode thing in this context.

4

AlwaysOn Active Secondaries should provide the capabilities you're looking for, including virtually realtime replication of data, and reporting capability on the secondary.

The Always On availability groups active secondary capabilities include support for read-only access to one or more secondary replicas (readable secondary replicas). A readable secondary replica can be in either synchronous-commit availability mode, or asynchronous-commit availability mode. A readable secondary replica allows read-only access to all its secondary databases. However, readable secondary databases are not set to read-only. They are dynamic. A given secondary database changes as changes on the corresponding primary database are applied to the secondary database. For a typical secondary replica, the data, including durable memory optimized tables, in the secondary databases is in near real time. Furthermore, full-text indexes are synchronized with the secondary databases. In many circumstances, data latency between a primary database and the corresponding secondary database is only a few seconds.

This technology also provides the ability to failover to the secondary in the event of a hardware or software failure on the primary, with minimal or no data loss.

Transactional replication won't prevent access to the data at the subscriber. Transactional Replication offers a huge amount of customization and requires quite a lot of configuration and management, whereas AlwaysOn provides a simplified way of making an entire database or set of databases highly available.

1

Yes, I agreed to Max Vernon's answer as well, to enable read-only on AlwaysOn secondary.

There are few benefits to go about using AlwaysOn:

  • You met your objective by enabling read-only on secondary node (as explained by Max Vernon)
  • You have set up redundancy for your production server
  • And because of that, it will be easy to schedule DB/server maintenance as there will only be little downtime involve (probably a less than ~3 mins for failover)
  • During the DB/server maintenance on the secondary node, you can choose whether to allow reporting service to connect to the primary node
  • Because there will be a use of listener, application services will just need to supply the listener in the connection string (do note that an additional parameter "ApplicationIntent=ReadOnly" will be needed in reporting application - that means, only one name is being used

You may refer to this link here on setting up AlwaysOn. I find the tricker part would probably be setting up on the clusters, AD (Active Domain) creation and coordination with the Windows SA team. Thereafter, it will be pretty much straightforward (even easier than mirroring).

However, if you are still keen on using replication. Yes, it can meet your purpose as well. In that case, transaction replication should be the way to go.

  • I am new into both Alwayson and replication.If i go with Transaction Replication,how is single user mode related to it? – sparktech Apr 26 '18 at 12:47
  • I am not quite sure about the single-user mode context here. But from the past replication that i have setup, it will be a multi-user mode settings by default, i.e. multiple application services could connect and use it. I did a quick google, it appears that people wanted to sometimes make the subscribed database to single-user mode for maintenance purpose. social.msdn.microsoft.com/Forums/sqlserver/en-US/… – Yoshiaki Apr 27 '18 at 0:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.