Replication reverted partitioned table to primary filegroup, can I re-link to the partitions?

Question

We have 2 servers:

• Server A. production (no partitions)
• Server B. New Server (partitions and compression)

We restored a backup to the new server, partitioned a large table by datetime and turned on compression, and we were happy.

Trying to be clever, we turned on replication to update the new server with the latest data on the large table, but that broke the partitioning and compression.

We cannot query any old data on the new server, only data from when we turned on replication.

The clustered index is the old one, and compression is not on, and no partitions to be seen in SSMS, it's using the PRIMARY filegroup (same as the current live server is using).

On the physical disk, I can see the partition files, and they are still massive with GBs of data, so I know the data is there, but it appears the table has lost the link to it.

It appears turning on replication replicated the filegroup side of things. Is there a way to point the table back to partitions and save all the work we did getting it ready for switch over?

It's a pretty large database. This one table is 11TB on the production server.

SQL Server 2017 Enterprise Edition.

Answer 1

Trying to be clever, we turned on replication to update the new server with the latest data on the large table, but that broke the partitioning and compression.

By default replication will initialize the subscriber with a copy of the table from the publisher. This could have dropped your existing table on the subscriber.

On the physical disk, I can see the partition files, and they are still massive with GBs of data, so I know the data is there, but it appears the table has lost the link to it.

Replication won't change files or filegroups, but if the table on those filegroups was dropped, there's no way to recover it other than restoring a backup.

If you want to copy the publication data to the subscriber before starting replication, that's called Manual Initialization. It allows you to tell replication that you've already initialized the subscriber and to just start applying changes.

Also when you set this up again, note that it's not normally necessary to land partitions on different filegroups. It's more complicated and doesn't perform any better, so unless you are trying to put older data on slower disks, you can just put all the partitions on the same filegroup. Using a non-default filegroup, and adding multiple (6 is a good choice) equal-sized files to the filegroup are both good practices.