September 25, 2013 at 9:48 am
I have a 2008 SQL server i need to replicate for a reporting server on a Nightly basis. I had the replication working originally but had to break it for a software upgrade. The DB has grown in size from 40 GB to 150 GB ( a lot of imports .When i tried to re-enable snapshot replication the Publisher takes around 40 Minutes the subscriber I let run for over 12 hrs and it never completed.
Currently i am doing a manual backup From the publisher and then a manual restoration to the subscriber. When the DB is compressed the backup is 28 GB and transfers to the subscriber in less then 5 minutes backup.
Would it be better to do a transnational replication ? Is there ways to have SQL only update the tables that changed ?
When all of our imports are done i expect the Db to be approximately 250 GB
September 25, 2013 at 10:52 pm
If you only need this once a day, why not simply restore a backup to the reporting server. You should be able to automate this using SQL Server Agent.
FYI : replication does support initialising the subscriber from a database backup - have a look at http://technet.microsoft.com/en-us/library/ms147834.aspx
Transactional replication definitely is an option. You can set this up so that it runs continuously or when scheduled (e.g. during off peak time) if needed. Transaction replication only sends insert/update/deletes for tables that have been changed (ie. tables that have had inserts/updates/deletes done to them in the published database).
September 26, 2013 at 6:39 am
happycat59 (9/25/2013)
I have been doing the restore to the reporting server daily Its is a 2 hr Manual process.
FYI : replication does support initializing the subscriber from a database backup - have a look at http://technet.microsoft.com/en-us/library/ms147834.aspx%5B/quote%5D
I looked at the article previously It still will keep my reporting server offline ( no real gain on the initialization time from what is being done now )
Transactional replication definitely is an option. You can set this up so that it runs continuously or when scheduled (e.g. during off peak time) if needed. Transaction replication only sends insert/update/deletes for tables that have been changed (ie. tables that have had inserts/updates/deletes done to them in the published database).
What would be the impact on my production server for this ( i know if i try to force a replication with the snapshot replication the DB Becomes Unusable during the process )
September 26, 2013 at 7:02 am
If you only need data on the reporting server nightly have you considered log shipping? Just hold off on the restores during the day and have the database in standby\read only mode
---------------------------------------------------------------------
September 26, 2013 at 7:05 am
Data Mirroring is also an option. But...
You broke replication for an upgrade. Why did you not upgrade the other server? If you did, why did you not rebuild / reinitialize replication?
You shouldn't have to keep doing this manually. Broken replication can be fixed.
September 26, 2013 at 7:33 am
I broke the replication for a software DB upgrade that cannot be done when replication is enabled Both My Main SQL cluster and the reporting server are on the same patch level
September 26, 2013 at 7:35 am
tlawrence71 (9/26/2013)
I broke the replication for a software DB upgrade that cannot be done when replication is enabled Both My Main SQL cluster and the reporting server are on the same patch level
But now that the upgrade is completed, surely you can reeanable everything.
September 26, 2013 at 8:19 am
Brandie Tarvin (9/26/2013)
tlawrence71 (9/26/2013)
But now that the upgrade is completed, surely you can reeanable everything.
That is the problem it is taking forever to try to get the subscriber to initialize 12 + hrs and it never finished
September 26, 2013 at 10:44 pm
What would be the impact on my production server for this ( i know if i try to force a replication with the snapshot replication the DB Becomes Unusable during the process )
If you use transactional replication, the impact should be pretty low. Once initialised, the publication does not actually touch the source database (well not as from a record locking perspective). The logreader agent only accesses the database's transaction log. It can be scheduled (just like any other SQL Server Agent job) at any time you wish if you need to make sure that it has minimum impact on prime time processing. Otherwise, set it up to continuously run. If you do schedule it to run off peak, it may impact on the size of the transaction log file - log records must remain in the transaction log until the logreader agent has processed them (into the distribution database).
Whilst I am not 100% sure, I think that the issue you are most likely experiencing when the snapshot is being created is blocking - the snapshot process is probably holding a read lock on the table(s). If this is the case, you may be able to reduce the impact by creating several smaller publications - each snapshot would run for a shorted period of time and may block other processes but for a shorted period of time.
September 27, 2013 at 5:16 am
It's been a while since I've done replication, but can't you take a current backup of the database, restore it to the subscriber, then rebuild the subscription details?
If you're having so much trouble with replication that it's not reinitializing, then something is seriously fubar and you might find it faster to just rebuild it using a current copy of the db.
October 4, 2013 at 3:19 am
Brandie Tarvin (9/27/2013)
It's been a while since I've done replication, but can't you take a current backup of the database, restore it to the subscriber, then rebuild the subscription details?If you're having so much trouble with replication that it's not reinitializing, then something is seriously fubar and you might find it faster to just rebuild it using a current copy of the db.
Yes, initialising a subscription from a database backup is definitely an option. Have a look at http://technet.microsoft.com/en-us/library/ms147834.aspx for more info
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply