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 12»»

2008 SQL replication Expand / Collapse
Author
Message
Posted Wednesday, September 25, 2013 9:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 27, 2013 6:16 AM
Points: 8, Visits: 15
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
Post #1498456
Posted Wednesday, September 25, 2013 10:52 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:00 AM
Points: 2,993, Visits: 2,590
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).



Post #1498665
Posted Thursday, September 26, 2013 6:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 27, 2013 6:16 AM
Points: 8, Visits: 15
happycat59 (9/25/2013)
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.[quote]



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


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 )
Post #1498793
Posted Thursday, September 26, 2013 7:02 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 5,863, Visits: 12,941
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

---------------------------------------------------------------------

Post #1498810
Posted Thursday, September 26, 2013 7:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:45 AM
Points: 5,572, Visits: 6,356
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1498812
Posted Thursday, September 26, 2013 7:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 27, 2013 6:16 AM
Points: 8, Visits: 15
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
Post #1498838
Posted Thursday, September 26, 2013 7:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:45 AM
Points: 5,572, Visits: 6,356
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1498841
Posted Thursday, September 26, 2013 8:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 27, 2013 6:16 AM
Points: 8, Visits: 15
Brandie Tarvin (9/26/2013)
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

[quote]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
Post #1498878
Posted Thursday, September 26, 2013 10:44 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:00 AM
Points: 2,993, Visits: 2,590

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.




Post #1499173
Posted Friday, September 27, 2013 5:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:45 AM
Points: 5,572, Visits: 6,356
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1499314
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse