May 2, 2013 at 7:09 am
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
May 2, 2013 at 2:25 pm
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
May 7, 2013 at 1:56 am
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
May 7, 2013 at 7:19 am
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.
May 8, 2013 at 7:55 am
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/
May 8, 2013 at 8:33 am
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 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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply