Using SQL Server 2016 and I have a 3rd party application that uses a large database on my SQL Server for data storage, processing, everything.
I have a BI team that needs access to this data to run reporting and batch-processing tasks, some of which include heavy queries that bring the server to its knees, so they can't be doing that on the live DB. To that end, I have an existing log-shipping solution which maintains a replication of this DB for them to use, but the amount of data and number of users who depend on data from that replication has grown to the point where taking the DB offline several times during the day to perform a log-shipping update is no longer acceptable.
So, I want to use an AlwaysOn availability group replication instead of log-shipping, so that the data remains online during business hours and is automatically updated.
However, my problem is that the application whose database I'm replicating is a little old and out of date, and whoever designed it clearly didn't know or care much about database optimization, since a lot of the larger tables don't have a primary key.
I've added indexes to some of the tables, which has worked OK, but if I modify the structure of the tables at all (such as adding a primary key), the application refuses to recognize them as valid and stops working. So these tables are going to have to remain without primary keys until I can convince the vendor to add them, which is likely to take years.
In the meantime, Is there anything I can do? I've tried using transactional replication and it's replicated all the tables withprimary keys just fine, but the tables without keys simply aren't there in the replicated DB.
I've heard that merge replication can be done on tables without primary keys, and it's possible to use merge replication for availability groups. Is that true? If it is, can you point me in the right direction to create such a replication?