אוק4
Written by:
ronen ariely
04/10/2021 01:02 
This is an answer to a question in the Microsoft's QnA forum. As a result of a bug in the forum, I could not publish the answer
Original question is here:
https://docs.microsoft.com/en-us/answers/questions/575426/sql-server-transaction-replication-error-online-in.html
-------------------- Question ---------------------
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?
-------------------- Answer ---------------------
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
CREATE DATABASE Test
GO
USE Test
GO
DROP TABLE IF EXISTS fyi_links_indexed
GO
Create table fyi_links_indexed(url char(100))
GO -- No index yet
-- Let's find the last LSN from the log file.
-- This will help us to filter the log file in our sample instead of read the entire log file each time.
SELECT TOP 1 [Current LSN]
FROM fn_dblog(null,null)
ORDER BY [Current LSN] DESC
GO -- remember this value for next step. In my test Db I got 00000025:00000a18:0024
-- For each query now we will use: where [Current LSN] > '00000025:00000a18:0024'
-- Let's CREATE INDEX ONLINE and check what was added in the transaction log
CREATE INDEX fyi_links_url ON fyi_links_indexed (url)
WITH (ONLINE = ON);
GO
-- just for understanding let's see waht added to the transaction log:
SELECT * FROM fn_dblog(null,null)
where [Current LSN] > '00000025:00000a18:0024'
GO
-- Notice that creating the index online writes multiple rows in the transaction log
-- just for understanding let's confirm that this index was created ONLINe,
-- which is what the replication see as well
-- and what failed in the standards edidion
SELECT [Current LSN], [Transaction ID], [Transaction Name] FROM fn_dblog(null,null)
where [Current LSN] > '00000025:00000a18:0024' and [Transaction Name] in('CREATE INDEX','ONLINE_INDEX_DDL')
GO
-- our next task is to find the transaction ID
-- search for Transaction name "CREATE INDEX"
SELECT [Transaction ID], [Transaction Name] FROM fn_dblog(null,null)
where [Current LSN] > '00000025:00000a18:0024' and Operation = 'LOP_BEGIN_XACT' and [Transaction Name] = 'CREATE INDEX'
GO -- 0000:0000042d
-- Now we have the transaction ID so we can get the object ID if the entitiy which was used ONLINE (in our case the INDEX)
select [Lock Information]
FROM fn_dblog(null,null) where [Transaction ID] = '0000:0000042d' and [Lock Information] like '%object_id = %'
GO
-- result should be like:
-- HoBt 0:ACQUIRE_LOCK_SCH_M METADATA: database_id = 9 INDEXSTATS(object_id = 901578250, index_id or stats_id = 2), lockPartitionId = 0
-- Notice the object Id (in my case 901578250)
That is all!
We found the id of the problematic entity which was created using ONLINE
What next?
The problem is that cleaning the information from the transaction log in ordefr to continue the replication as it is, might be very advance and complexs (can be done in theory)
SO **at this point we know what to fix** but the next question is how to fix...
The simples way is to start the replication from this point after the problematic entity was already created since if we use the current transaction log then the issue will appear even if we drop the the index now since the replication is done log by log and once we will use the log that include the `CREATE INDEX ONLINE` then the issue will appear again.
After you create the replication then we need to avoid related issue in the future
In production the execution of index rebuild (which can use ONLINE) for example, is usually done by manual exaction or maintenance tasks which executed in Jobs (Unfortunately it can come from other triggers which are more difficult to find if you are not familiar with the system).
At this point you must confirm that if your duplicated the same jobs from the source server then you should fix all these jobs and other task which use ONLINE.
This is a separate task
### find jobs which include the world ONLINE
SELECT
s.step_id as 'Step ID',
j.[name] as 'SQL Agent Job Name',
s.database_name as 'DB Name',
s.command as 'Command'
FROM msdb.dbo.sysjobsteps AS s
INNER JOIN msdb.dbo.sysjobs AS j ON s.job_id = j.job_id
WHERE s.command LIKE '%ONLINE%'
### Last question we has here
> Is there a method by chance to do transactional replication without the primary key indexing being applied?
No. Transactional replication requires a primary key constraint on each published table.
https://docs.microsoft.com/en-us/sql/relational-databases/replication/administration/frequently-asked-questions-for-replication-administrators?view=sql-server-ver15#how-do-i-manage-constraints-on-published-tables---