SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help rebuilding a SQL server with replicated databases


Need help rebuilding a SQL server with replicated databases

Author
Message
Kevin Brown-392626
Kevin Brown-392626
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2457 Visits: 425
We have a production SQL server in a virtualized environment and we need to move it to a physical server to address performance issues with an application that accesses the database.

The server has 3 user databases 2 of which are replicated. We are using transactional replication (one way) and the distributor is on a different server.

We use the replicated copies of the database for reporting, and the published databases are currently approaching 200G in size. We have mad some changes to replication to make reporting easier (We convert xml stored in varchar(max) columns to xml during replication, and have persisted calculated columns added on the subscriber. Overall, we would expect to spend a couple days rebuilding replication if it comes down to that.

We would prefer to be able to simply replace the publisher with the new physical server and continue on.

Does anyone have any suggestions for a process that would allow us to do that, or will we just need to bite the bullet and rebuild replication after the publisher is recreated?

thanks,
Kevin
Kevin Brown-392626
Kevin Brown-392626
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2457 Visits: 425
Doing some more digging online, it sounds like if I back up the master, MSDB and published databases (the distributor is on a different server) and restore them with replication settings intact to a server with the same host machine name I should be good. Does anyone know of any common missteps or issues with this process? (which databases go first, drop the databases to single user mode, etc) that I should keep in mind when we go to make this change?

We do have TDE enabled on the databases, I'm thinking the keys are stored in MSDB, so does that mean I won't need to pre-install the keys on the new server and will just be able to restore the databases as long as they are in the correct order, or will I need to install the keys after the system databases but before the user databases?

thanks,
Kevin
rhodric.nicol
rhodric.nicol
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 228
Hi Kevin

I performed something similar afew years ago on SQL2000. Unfortunately it didn't work as expected and just flattened and rebuilt the replication config. It may have been the Txn log lsns not sure. I'd recommend a test using a dummy database.

Good luck
Kevin Brown-392626
Kevin Brown-392626
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2457 Visits: 425
Rebuilding is an option, just our last option. We rebuild our test replication on a regular basis, so the entire process is very well documented. I'm just not looking forward to spending multiple days on the rebuild if I don't have to.

Trying with one of our test databases sounds like a good idea. I'll look into if we can risk one of our environments for a test case.
ivanaldana
ivanaldana
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 102
You have another way of re-recreate the subscription.
You can create a backUp of the publication database and then restore it at the subscriton database, with this the server will replicate only the arrived transactions after the backup.

Take a look to this link.
http://tipsattach.wordpress.com/2011/12/15/inicializar-replicacion-transaccional-desde-un-backup/
Kevin Brown-392626
Kevin Brown-392626
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2457 Visits: 425
Thanks, Unfortunately, I don't think that will help us out much though. Initializing the subscription isn't that bad, but after the subscription is initialized, we have a process to stop replication, convert UTF-8 (needs converted to UTF-16 due to characters) xml stored as varchar to XML columns (replace UTF-8 with UTF-16, convert column to nvarchar, convert column to xml), update the replication SPs to handle conversion on inserts and updates, the extract data from the xml to persisted calculated columns and apply indexing. It's all of the other stuff that tends to take a long time to process, and leaves most of our reporting while it is processing.

ivanaldana (5/8/2013)
You have another way of re-recreate the subscription.
You can create a backUp of the publication database and then restore it at the subscriton database, with this the server will replicate only the arrived transactions after the backup.

Take a look to this link.
http://tipsattach.wordpress.com/2011/12/15/inicializar-replicacion-transaccional-desde-un-backup/

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search