CODEWITHSUNDEEP
sql-serverdatabase-replicationdatabase-mirroring
Zsolt
Zsolt

Reputation: 345

SQL Server 2016 Standard - High performance (async) mirroring

I have a SQL Server 2016 Standard Edition. I would like to mirror a database to a server which is located far away.

Currently a scheduled script creates backups and then uploads them to another server. I know this is not an ideal solution.

I would like to have a (nearly) real-time clone of the database, without thinking on what to synchronize. I would like to sync all changes, regardless of the type of the change (insert, update, delete, alter, SP, table, index, default values, etc.).

So,

I think I would need the high performance (async) mirroring feature of SQL Server, but it is only available in the Enterprise Edition. The "High safety" mode is not an option for me, because of the network latency and maybe the mirror server is not available sometimes.

My other thought was to replicate the database. But when I create a new table, let's say, then I have to mark that table that it also must be published. There are always so many changes in the database, so it is not an option to manually mark the newly created tables, SPs to publish.

Is there a way to set up the replication to mimic the async mirroring behavior? Or create a stored procedure that could be the last step of the release procedure that changes the publications to publish new and changed objects automatically?

Or generally, how would you solve this using SQL 2016 STD?

Upvotes: -1

Views: 571

Answers (1)

Ben Thul
Ben Thul

Reputation: 32737

You're on SQL 2016 so Availability Groups are... available to you as an option. Because you're on Standard Edition, it'd be a Basic Availability Group, but even those support asynchronous commit.

Upvotes: 0

Related Questions