1

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?

  • An Availability Group is not replication (merge or otherwise); it doesn't give a rip about your schema, and you can have as many tables without keys as you want (though I'd be curious to know why the vendor doesn't like PKs). You need an underlying WSFC and then you can create an AG. Starting points here and here. – Aaron Bertrand Jan 27 '20 at 18:05
  • I think it was just that the programmers were very ignorant of databases in general. We're talking about a 3rd party plugin for a legacy 16-bit windows app that is barely supported at all any more, but is still the best software in its sector for what it does. How it came to be still in use and business critical in 2020 is a long story which I may tell sometime. – Geoff Griswald Jan 28 '20 at 9:32
7

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.