I have a 300 GB MSSQL 2016 production database on a server and i will need to make a copy of this database on another server for reporting purposes.
On the production database, there are normal clustered/non-clustered indexes and daily read-write operations are happenning.
On the reporting side, I will have the same database tables with columnstore indexes so i will get the oppurtunity to get my report query results faster than live database.
The idea was fine, until i could not find a proper way to replicate these 2 databases and keep them in sync (like with max. 5 mnts tolerance)
I have tried some Log Shipping topologies, didnt work well.
I have tried SQL Replication between these two databases but it does not give %100 data consistency and it is not tolerable for mistakes like when you add or drop a column in table or when you want to deliver a new table to the reporting database etc.
PS. I would consider to have one replica of my prod database in sync (beside reporting database) (Like log shipping read only secondary). So im ready to create another server (totally 3 servers) for a secondary replicated database (1- prod, 1-replica, 1-reporting) if I would find a proper topology..
What do you think is the best way in my case ?
Thanks in advance.
If you want DDL changes to replicate, you will need to use log-shipping or mirroring of some kind-- which means you are going to need to have the same database (down to the page level) on the read-only copy. You will not be able to have different index definitions.
If you want the index definitions to be different, then you will need to come up with a way to replicate the data-- manual scripts are probably the best bet, but it is tricky to keep track of what records are new, updated, and especially deleted in the production database (adding rowversion columns and making sure if you primary keys on all tables are going to be useful). You would also have to manually clone over the DDL changes from the production database into the read-only copy; and update your data replication scripts to match.
I would recommend using one of the mirroring techniques available (log shipping, AG with a read-only mirror, et. al.) and keeping the databases exactly identical.