Help us improve the AWS re:Post Knowledge Center by sharing your feedback in a brief survey. Your input can influence how we create and update our content to better support your AWS journey.
When using ms-replication at the Source for Ongoing Replication, sp_droparticle fails with "The article 'abc' does not exist."
Greetings,
When using ms-sqlserver as a source database and the DMS task set to use ms-replication for ongoing replication, the stored procedure call sp_droparticle fails with this error message
"The article 'tablename' does not exist.
We have a process to rename tables and sometimes these tables are captured in an ms-replication publication. If the table is in a publication, then in order to rename it, it has to be removed from all subscriptions and from each associated publication first.
For AWS, the query to get the publication had to be widened to check for sysservers.srvname is null. I can see the article in the publication created by DMS, however a call to sp_droparticle fails. I understand that for AWS there is no subscription available from the source so no attempt is made to remove the article from this type of subscription, however, I was hopeful that tables could be removed from AWS/DMS publications so a table could be renamed. Is there another way to do this in script?
- Language
- English
- Newest
- Most votes
- Most comments
When using MS-REPLICATION with AWS DMS for ongoing replication from a SQL Server source, removing articles (tables) from the publication can be challenging due to the unique way AWS DMS sets up replication.
The error you're encountering suggests that while you can see the article in the DMS-created publication, the standard SQL Server stored procedure sp_droparticle is not recognizing it. This is likely because AWS DMS configures replication differently than a typical SQL Server replication setup.
Unfortunately, there isn't a straightforward scripted method to remove articles from AWS DMS publications using standard SQL Server procedures. AWS DMS manages these publications internally, and they don't behave exactly like traditional SQL Server publications.
To rename a table that's part of an AWS DMS replication task, you'll need to take a different approach:
- Stop the AWS DMS replication task.
- Remove the table from the task's table mappings.
- Rename the table in your source database.
- Update the task's table mappings to include the renamed table.
- Restart the replication task.
This process ensures that AWS DMS reconfigures its internal replication setup to accommodate the renamed table. While it's not as straightforward as using sp_droparticle, it allows you to achieve your goal of renaming tables without conflicts with the AWS DMS replication process.
Keep in mind that this approach may require you to resynchronize data for the renamed table, depending on your specific DMS task configuration and requirements.
Sources
Capturing data changes for ongoing replication from SQL Server - AWS Database Migration Service
Step 2: Configure Your Microsoft SQL Server Source Database - Database Migration Guide
