SQL Server Transaction Replication error Online index operations can only be performed in Enterprise edition of SQL Server from Ent to Standard

Question

SQL Server Transaction Replication error Online index operations can only be performed in Enterprise edition of SQL Server from Ent to Standard

Ted Fletcher 1

I'm setting up transactional replication for multiple databases in SQL Server where the publisher is on SQL Server 2014 Enterprise Edition and the target is SQL Server 2017 Standard. All but 2 of the databases work great, and the error I'm running into is right at the end of applying the snapshot to the subscriber for those 2.

"Online index operations can only be performed in Enterprise edition of SQL Server"

I'm assuming this means that those 2 databases have at one point applied indexing or changes with ONLINE=ON? I have attempted to create the snapshot without the clustered and nonclustered indexes and keep hitting the error as well.

If this is true, Is there a way to tell which articles in the database have been scripted with ONLINE=ON to cause this error to pop up?

I'm planning a full index rebuild with ONLINE=OFF on the smaller of the source databases hoping that is the workaround but am curious if anyone out there knows the solution. Would hate to have to be forced to get Enterprise Edition just because of this error!

UPDATE: The index rebuild did not get me away from the error. Is there a method by chance to do transactional replication without the primary key indexing being applied?

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-10-03T17:58:15.087+00:00

II can't see anything in sys.indexes that seem to reflect ONLINE=ON/OFF, so that may not be stored with the index.

Have you tried to nail down for which index(es) this occurs? Maybe there is something else which is special with these indexes? (Not that I can think of what that would be.)
CathyJi-MSFT 22,396 Reputation points Microsoft External Staff

2021-10-04T06:58:59.22+00:00

Hi @Ted Fletcher ,

Please check your SQL server replication monitor, and share us the more related message about the error. Or you can using profiler trace to find the related reason that cause the issue.
Ted Fletcher 1 Reputation point

2021-10-04T11:56:57.647+00:00

Command attempted: if @@trancount > 0 rollback tran
(Transaction sequence number: 0x000000370001B59100E800000000, Command ID: 1)
Ted Fletcher 1 Reputation point

2021-10-04T12:13:33.25+00:00

No there's nothing in sys.indexes that tracks this. I'm assuming it is kept in the transaction log, however the error is not limited to indexes. It could also be and ALTER statement that was done as well with ONLINE=ON. Even finding the offending object(s) would be great to know at this point.
Ted Fletcher 1 Reputation point

2021-10-04T12:39:03.553+00:00

Thanks @CathyJi-MSFT ! We have found that most likely the offending issue stems from an index rebuild job that has been running 3x a week. If we turn off that job, how then would we be able to "cleanse" the transaction log so that we could then enable transactional replication? So in other words, we'd need to go 1 step further.

3 answers

Your answer

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-10-03T17:58:15.087+00:00

II can't see anything in sys.indexes that seem to reflect ONLINE=ON/OFF, so that may not be stored with the index.

Have you tried to nail down for which index(es) this occurs? Maybe there is something else which is special with these indexes? (Not that I can think of what that would be.)
CathyJi-MSFT 22,396 Reputation points Microsoft External Staff

2021-10-04T06:58:59.22+00:00

Hi @Ted Fletcher ,

Please check your SQL server replication monitor, and share us the more related message about the error. Or you can using profiler trace to find the related reason that cause the issue.
Ted Fletcher 1 Reputation point

2021-10-04T11:56:57.647+00:00

Command attempted: if @@trancount > 0 rollback tran
(Transaction sequence number: 0x000000370001B59100E800000000, Command ID: 1)
Ted Fletcher 1 Reputation point

2021-10-04T12:13:33.25+00:00

No there's nothing in sys.indexes that tracks this. I'm assuming it is kept in the transaction log, however the error is not limited to indexes. It could also be and ALTER statement that was done as well with ONLINE=ON. Even finding the offending object(s) would be great to know at this point.
Ted Fletcher 1 Reputation point

2021-10-04T12:39:03.553+00:00

Thanks @CathyJi-MSFT ! We have found that most likely the offending issue stems from an index rebuild job that has been running 3x a week. If we turn off that job, how then would we be able to "cleanse" the transaction log so that we could then enable transactional replication? So in other words, we'd need to go 1 step further.

Answer 1

Awesome! I LOVE THIS QUESTION

Finally we have excuse to dig to the internals

I'm assuming this means that those 2 databases have at one point applied indexing or changes with ONLINE=ON?

This make sense...

But remember that "Online index operations" can include multiple tasks like CREATE/ALTER/DROP INDEX; ALTER TABLE (add or drop UNIQUE or PRIMARY KEY constraints with CLUSTERED index option and alter columns).

am curious if anyone out there knows the solution

For first step, we need to find who/how/which task was executed and used ONLINE which lead to this issue. Meaning we need to find the task which was used ONLINE and block you from using none-Enterprise edition.

Note! For the sake of the discussion I will speak about CREATE INDEX but the same procedure can be applied to other ONLINE tasks, which are logged in the transaction log.

This might be a bit complex and advance, since the information about how we created the index, is not stored in the database tables (metadata) as much as remember since once it was created it has no value/uses (usually, since as we found now it might have a value in this question).

Therefore, we need to pull the information from the transaction log exactly from the same place that the transaction replication takes it from.

For this task we will use the undocumented function fn_dblog

Lets demonstrate it from start to end

----------

I AM GETTING ERROR FROM THE FORUM WHEN POSTING THE ANSWER!

Give me some time and I will find a workaround. Maybe this is too long answer... I will try to post the rest in separate message

Ronen Ariely 15,206 Reputation points

2021-10-03T23:01:46.477+00:00

Not working I will publish the answer as post in my blog...

Answer 2

Ronen Ariely 15,206

I published the answer in my blog since there was issue to publish it here.

check this:

https://ariely.info/Blog/tabid/83/EntryId/302/SQL-Server-Transaction-Replication-from-Enterprise-to-Standard-error-Online-index-operations-can-only-be-performed-in-Enterprise-edition.aspx

Ted Fletcher 1 Reputation point

2021-10-04T12:16:20.77+00:00

Fantastic post, unfortunately using the queries above we're still unable to determine the offending articles as it may not be an index but perhaps an ALTER or UPDATE statement. I've been digging through the results since early this morning trying to pinpoint potential offending object_ids without much luck.
Ted Fletcher 1 Reputation point

2021-10-04T12:40:04.043+00:00

Thanks @Ronen Ariely ! We have found that most likely the offending issue stems from an index rebuild job that has been running 3x a week. If we turn off that job, how then would we be able to "cleanse" the transaction log so that we could then enable transactional replication? So in other words, we'd need to go 1 step further.
Ted Fletcher 1 Reputation point

2021-10-04T12:55:29.427+00:00
@Ronen Ariely - I've got some testing scenarios I'm contemplating and was curious on which would be the most likely successful path.

Disable the offending job that uses ONLINE=ON

Shrink the transaction log on the publishing database (in an attempt to remove the transaction log history set with that "feature"?)

Rerun the snapshot for the publisher and attempt to reinitialize the transactional replication.

step 2 is the one in question to me. I'm curious if shrinking the log is the "cleanup" task to allow the transaction log to be free of the watermarking that has put into it from that multix per week. job

Thanks again
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-10-04T21:12:54.307+00:00

I can't see that shrinking the log is going to matter. Shrink only removes inactive VLFs at the end of the file. If the VLF is inactive, it does not matter what's in it.

Truncating the log by backing it up, may help. Then again, if the offending index rebuild is in the transaction log because it has yet to be replicated, it will remain in the transaction log until it has been replicated.

I have not used replication much, so I may be way off. But I'm thinking that the ONLINE command wants to be replicated because it occurred after the replication was set up. So maybe you need to start over and keep the job disabled.

Then again, what happens once you have replication going, and you perform an online index rebuild?

Yet then again, surely you can setup replication without replicating indexes. Maybe that is the way to go?
Ted Fletcher 1 Reputation point

2021-10-04T22:15:47.447+00:00

I have no problem resetting up the replication. And yes you can setup replication without indexes, but not the ones on the primary key.

Also, if the online index rebuild is done on the publication database (enterprise) and you someday want to add or remove an article from publication requiring a new snapshot to be taken, the replication setup will fail on the Standard version right when it tries to apply the snapshot.

I'm going to try disabling those offending jobs, backup and truncate the transaction log, rebuild with ONLINE = Off on the indexes and then resetup the Publication and Subscriber to see if that does the trick early tomorrow morning. fingers crossed

I'll be sure to report back results as well
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-10-05T21:24:49.197+00:00

...and then is the question is what will happen if you run an ONLINE rebuild once the replication has started going? Will that ruin the show? I guess you better test early in the game.

Answer 3

Dileep Veldi 1

This step-by-step guide walks you through the basics of using SQL transactional replication between on-premises infrastructure and Cloud PaaS infrastructure. Get an overview of standard transactional replication from on-premises SQL server database to Azure SQL managed instance database.

https://dileepveldi.medium.com/standard-transactional-replication-from-on-premise-sql-to-azure-sql-managed-instance-ba52a60f0ba2

Share via

SQL Server Transaction Replication error Online index operations can only be performed in Enterprise edition of SQL Server from Ent to Standard

3 answers

Your answer