Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


2008 SQL replication


2008 SQL replication

Author
Message
tlawrence71
tlawrence71
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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
happycat59
happycat59
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3561 Visits: 3094
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).



tlawrence71
tlawrence71
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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 )
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6324 Visits: 13685
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

---------------------------------------------------------------------
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7745 Visits: 8719
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
tlawrence71
tlawrence71
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7745 Visits: 8719
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
tlawrence71
tlawrence71
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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
happycat59
happycat59
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3561 Visits: 3094

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.



Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7745 Visits: 8719
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
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