Sql-server – create an AlwaysOn replication of a DB where some tables don’t have a primary key

sql serversql-server-2016

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?

Best Answer

The replication that is used by availability groups is akin to database mirroring, not table replication (SQL Server Replication). AG replication has no requirements regarding primary keys on tables. The limitations and restrictions are clearly documented at Prerequisites, Restrictions, and Recommendations for Always On availability groups -> Availability Database Prerequisites and Restrictions.

For the situation you are describing, you would want to Configure read-only routing for an Always On availability group and have the BI team to include ApplicationIntent=ReadOnly in the connection string so that their queries run on the secondary.