Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help rebuilding a SQL server with replicated databases Expand / Collapse
Author
Message
Posted Thursday, May 2, 2013 7:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:40 PM
Points: 2,449, 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
Post #1448799
Posted Thursday, May 2, 2013 2:25 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:40 PM
Points: 2,449, 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
Post #1448978
Posted Tuesday, May 7, 2013 1:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 8:10 AM
Points: 12, Visits: 202
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
Post #1450017
Posted Tuesday, May 7, 2013 7:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:40 PM
Points: 2,449, 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.
Post #1450127
Posted Wednesday, May 8, 2013 7:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 7, 2013 7:14 AM
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/
Post #1450577
Posted Wednesday, May 8, 2013 8:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:40 PM
Points: 2,449, 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/
Post #1450610
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse