Database mirroring

1

Hello

Is it possible to mirror a SQL database to another server by transferring the whole data at once and then only copy the changes in the data on-the-fly from the source db to the mirror db? Can this be done within SQL or other tools are better?

Is it possible afterwards in the mirror db, to do versioning of the records whenever there is a change to maintain history? Can this be done within SQL or other tools are better?

Would the above cause any issues e.g. in efficiency, access, corrupted data etc?

Azure can also be part of the solution.

Thanks

2

The Database mirroring feature would accomplish the first part (transfer whole DB and subsequent changes). However, it is a deprecated feature, only available in Enterprise Edition, and the mirror replica cannot be accessed, even for reading. Availability Groups are the replacement feature, but additionally requires Windows Failover Clustering, and read-only replicas are only available in Enterprise Edition. Both utilize continuous log shipping to redo the entire log on the secondary replica, which prevents any independent writes.

To accomplish your 2nd goal (maintain history of changes) in only the mirror DB, you'll need to look at Transactional Replication plus Temporal Tables. You could also write a custom data replication process using Change Tracking instead of Transactional Replication, but Temporal Tables would probably be the easiest/lowest effort way to maintain a full history. Change Data Capture is a 3rd option, and could provide both change synchronization as well as full history, but you would need to offload the history periodically to allow the CDC captured data to clear from the transaction log.

None of these options would cause data corruption, but they all have benefits and trade-offs for performance and ease-of-use. You'll have to research and test them to decide which is best. There are some 3rd party replication and CDC options, I don't expect they'll be much better than the built-in features.

SQL Server 2022 is adding a feature for bi-directional replication to the cloud:

However it is a variation on an Availability Group and you will likely be limited in how you can update a "replica" and maintain its history, and won't be available for a while. As with all things in the cloud, it is also going to cost more than you think, and likely a good bit more than an equivalent on-premises option.

3

@robert_volk thanks!
As for the db mirroring, is there a better option? I really need to fully access the mirror (read-only is fine) and then would like to build views and stored procedures on top as well as versioning (unless the mirroring itself can version everything). Enterprise edition should be fine but if it is a deprecated feature I guess is a no-go.

I also need to choose tables, rows, columns/fields to mirror rather than the whole thing. I assume this 'partial' mirroring can be implemented much easier? no need to replicate the whole db structure!

4

Sorry, I should have been more clear, DB mirroring and Availability Groups will not do what you're asking. Their primary function is high availability and easy, quick failover, they cannot be used for secondary replicas that need to be modified. Both features always copy the entire database, so any versioning would have to exist on the primary.

Transactional replication is your best option and will offer a lot of flexibility. The technology is solid and optimized, even though it's not as shiny or easy as Availability Groups. You will need to plan, you will need to test, you'll need to monitor carefully in the beginning, and keep a close eye on it until you're comfortable.

Jonathan covers use cases here:

It's part of a series on replication that you should probably read, it's not many articles but he covers some important stuff. He discusses filtered replication a little bit, and also that you can tailor exactly which tables and columns are replicated. You'll need to read Books Online for all the specifics.

Keep in mind that if you are replicating a database that will have frequent schema changes, you will need to re-apply your replication settings with each deployment. I wouldn't disqualify replication on these grounds, but you can't ignore it and it's non-zero effort.

I also mentioned Change Tracking (probably isn't sufficient) and Change Data Capture. Paul Randal has an article on them:

https://docs.microsoft.com/en-us/previous-versions/technet-magazine/cc987538(v=msdn.10)?redirectedfrom=MSDN

CDC can definitely do what you want, you get all the flexibility you need, but you have to write the synchronization yourself. It can be entirely in T-SQL, stored procedures, but there's no automatic copying of changed data like replication has. I'm not sure whatever benefits CDC has over TR (if any) are worth the hassle.

I think the best thing you can do is just play with a test replication prototype. New DB (or use AdventureWorks as a base), set up Replication on it using the Wizard, just publish and subscribe a few tables and test latency and the monitoring tools. You can host the publisher, distributor, and subscriber all on the same instance if you want. Just do a minimal test to get a feel for it, then play a little bit with publishing only certain columns, and so on. Make sure to NOT enable or configure a real database for replication until you are sure you are ready to use it.