Replication and Data Migrations

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Replication and Data Migrations

Replication and Data Migrations

Rate Topic

Display Mode

Topic Options

Author

Message

rphilips

Posted Thursday, February 07, 2013 11:14 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 9:22 AM
Points: 33, Visits: 162

I have a test database and transactional replication set up and running properly. Now that it is up, I have a couple of questions concerning making changes to the Publication database. My understanding is that if I need to make changes to the table structures or add tables to the Publication database that I need to remove replication, make my changes, and then set-up replication again. Is this correct or can I just stop all the SQL Agent replication jobs, restart them after making the changes, and then reinitialize the subscription?

Similarly, if I do a data migration that adds 30,000 rows to the base table and 25,000 + rows to 10+ other tables, should I remove replication before migrating? The process runs about 25 hours.

I am using SQL 2008 R2 on a windows 2008 R2 server. All service packs have been applied.

Thanks, Rosalind

Post #1417262

happycat59

Posted Thursday, February 07, 2013 8:07 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:54 PM
Points: 2,467, Visits: 2,059

Depending on how you configure your publication, replication is quite happy for you to make changes to table structures and replication will make the equivalent change to the subscriber. Have a look at article "Making Schema Changes on Publication Databases" in Books Online - it should tell you all that you need to know.

As for the data migration - that it your call. 30,000 + 25,000 rows in 25 hours doesn't seem like very much to me - personally, I would leave replication running.

Post #1417434

rphilips

Posted Monday, February 11, 2013 2:47 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 9:22 AM
Points: 33, Visits: 162

Thank you for your help. I will check out the article and do some more testing.

Post #1418639

MyDoggieJessie

Posted Monday, February 11, 2013 8:22 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 2,035, Visits: 3,759

Check out this article, I think it will help :)
http://www.sqlservercentral.com/Forums/Topic1388718-391-1.aspx#bm1388866

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" Wink

Post #1418724

rphilips

Posted Tuesday, February 12, 2013 10:48 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 9:22 AM
Points: 33, Visits: 162

Thank you. The article was helpful. I like your problem solving process.

Post #1419107

« Prev Topic | Next Topic »

Permissions