June 19, 2018 at 5:09 am
Hi,
We have a 2008 based replication system up and running.
We have a table, which is currently replicated and full of millions of records. We did start looking at a routine to purge this table but it has c160 million rows in it and will take too long.
My suggestion was to create a new table, with same constraints, indexes and start again ( we do have to import some of the old data - it's basically a SKU table so it needs the newest record and that's it ) - then rename the tmp table to be the same as the original table
How can I do this - I will have to drop the table etc and then what?
June 19, 2018 at 11:26 am
steve.roberts 86619 - Tuesday, June 19, 2018 5:09 AMHi,We have a 2008 based replication system up and running.
We have a table, which is currently replicated and full of millions of records. We did start looking at a routine to purge this table but it has c160 million rows in it and will take too long.
My suggestion was to create a new table, with same constraints, indexes and start again ( we do have to import some of the old data - it's basically a SKU table so it needs the newest record and that's it ) - then rename the tmp table to be the same as the original table
How can I do this - I will have to drop the table etc and then what?
Drop the table from the publication, do whatever changes you plan on doing with the table.
Add the new table to the publication and generate a new snapshot.
Sue
June 19, 2018 at 11:32 am
I've not done a whole lot with replication, but my guess is that you'll want to at some point turn off that table's replication. I'd be looking at the following steps:
1.) Script out the table and it's permissions and indexes and constraints.
2.) Change the table name and the PK constraint name in the script to new names.
3..) Run that changed script to create the new table. Make doubly sure that the structure, permissions, indexes, and constraints are the same.
4.) Schedule some down time for the following:
5.) Turn off replication for that specific table.
6.) Back up the database as well as the transaction logs.
7.) Shrink the transaction log file.
8.) Verify you have plenty of disk space for your transaction log file.
9.) Copy the data from the existing table into the new table, specifying TABLOCKX for the new table on the INSERT.
10.) Verify that all the data in both tables is identical.
11.) Drop the old table.
12.) Rename the new table to the old name, and rename any named constraints (like the PK) to the name that existed with the old table.
13.) Add the new table back to replication. Use a snapshot if need be.
14.) Verify replication is working.
15.) Let the users back in.
Someone better versed in replication may know if there are some ways to shortcut the amount of time this might take.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 20, 2018 at 8:41 am
Hi,
My plan so far was
Drop article from replication
Rename table to table_tmp
I assume I need to rename this table on the subscribers as well
Create new table
Add it back into replication?
June 20, 2018 at 9:12 am
steve.roberts 86619 - Wednesday, June 20, 2018 8:41 AMHi,My plan so far was
Drop article from replication
Rename table to table_tmp
I assume I need to rename this table on the subscribers as well
Create new table
Add it back into replication?
I got a little more detailed in the steps I provided, but the concept is the same...
Once you stop replication, you can rename the table on the subscriber, or you can drop it altogether, because you're going to end up sending the entire table's worth of data across the wire again anyway....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 20, 2018 at 10:45 am
steve.roberts 86619 - Wednesday, June 20, 2018 8:41 AMHi,My plan so far was
Drop article from replication
Rename table to table_tmp
I assume I need to rename this table on the subscribers as well
Create new table
Add it back into replication?
Just drop the table from the subscriber since it's removed from the publication. After you make the changes with data, renaming to the table on the published database you can add the table back and generate a snapshot so the new table and data go to the subscriber.
Sue
June 20, 2018 at 11:02 am
sgmunson - Wednesday, June 20, 2018 9:12 AMsteve.roberts 86619 - Wednesday, June 20, 2018 8:41 AMHi,My plan so far was
Drop article from replication
Rename table to table_tmp
I assume I need to rename this table on the subscribers as well
Create new table
Add it back into replication?I got a little more detailed in the steps I provided, but the concept is the same...
Once you stop replication, you can rename the table on the subscriber, or you can drop it altogether, because you're going to end up sending the entire table's worth of data across the wire again anyway....
If someone only wants to generate the snapshot of just the added article, it really would need more detail. But it depends on the publication.
But (I should have added this earlier for the poster) if they want to just generate a snapshot for only the added article, that's where the add article needs to be done with force invalidate snapshot to true/1. And then the publication has to have immediate sync and allow anonymous set to false/0
Sue
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply