CODEWITHSUNDEEP
sqlsql-serverdatabase-administrationdatabase-replicationtransactional-replication
user2608654
user2608654

Reputation: 11

Disable Replication, Keep Replicated Tables?

I have transactional replication set up between Database1 and Database2 using SQL Server 2017. About 20 tables are being replicated to Database2, a few of them are pretty large. I would like to disable replication but at the same time I would like to keep all the data being replicated to DB2 as well as the keys etc. In short, when I turn off replication I don't want to loose any of the replicated data.

Any recommendations?

Upvotes: 0

Views: 1326

Answers (1)

Severalnines
Severalnines

Reputation: 311

Important Note:

Before proceeding, ensure you have backups of both Database1 and Database2 to avoid any data loss.

Keep in mind that this process should be done with caution, especially on production databases, and it's recommended to test it in a non-production environment first to ensure a smooth transition.

Additionally, consider documenting the entire process and keep a record of all the steps taken, as well as any issues encountered during the migration, for future reference and troubleshooting.

1. Pause replication:

First, pause replication to stop new transactions from being replicated to Database2.

You can do this using SQL Server Management Studio (SSMS) or T-SQL commands. In SSMS, right-click on the replication publication and choose "Pause."

2. Generate scripts for tables:

You'll need to script out the structure and data for each replicated table. You can do this through SSMS:

Right-click on the database (Database1) in SSMS.

Go to "Tasks" > "Generate Scripts..."

In the wizard, select the specific tables you want to script.

Choose the "Schema and Data" option.

Generate the script and save it.

3. Execute scripts on Database2:

Copy and execute the generated scripts on Database2. This will create the tables and populate them with data. Be careful with large tables, as this may take a while.

4. Reestablish referential integrity:

If you have foreign key relationships between tables, you may need to script and recreate them on Database2 in the correct order to maintain referential integrity.

5. Remove replication components:

After confirming that the data is successfully copied and the schema is intact, you can safely remove the replication components.

In SSMS, right-click on the replication publication and choose "Delete."

Follow the steps to remove the publication. This will also remove the subscriptions.

6. Clean up replication objects:

Manually clean up any remaining replication-related objects, such as replication stored procedures and triggers, that were not automatically removed during the deletion process.

7. Verify data consistency:

Double-check the data in both databases to ensure it's consistent. Look for any discrepancies between the two databases.

8. Backup and maintain:

Perform regular backups of Database2 and ensure that any future schema changes or data updates are properly managed without relying on replication.

Upvotes: 0

Related Questions