Transactional replication of VLDBs

[sql hunter]

Hi Folks,

I am assigned to do a transactional replication of a large database of size
more than 4 TB and As the snapshot cannot hold the large amount of data I am
looking for better solution like initialize the database from the backup and
then perform the replication.?

Any suggestions,ideas or very good links to do this?

[Ben Thul]

SQL 2005 added the option to do exactly what you're looking to do:
initialize from backup. Generally, the steps are:

1) Create publication and add articles
2) Create backup (this can be your normal daily backup if space is a
concern)
3) Restore backup to subscriber (don't delete that backup file yet;
you need it for step 4)
4) Add the subscription to the publication (calling sp_addsubscription
with the @sync_type = 'initialize with backup' and provide the
relevant details for your backup file)

One thing to watch out for is making sure that the distribution
retention is set sufficiently high so that you have time to ship the
backup file and restore it before transactions start to roll off. You
can either set the minimum retention or you can disable the
distribution cleanup job while you're doing your work. Good luck!
--
Ben

[abcd123477 via SQLMonster.com]

Thanks Ben,

Can please you be some what elaborated so that can get a better picture.

Ben Thul wrote:
>SQL 2005 added the option to do exactly what you're looking to do:
>initialize from backup. Generally, the steps are:
>
>1) Create publication and add articles
>2) Create backup (this can be your normal daily backup if space is a
>concern)
>3) Restore backup to subscriber (don't delete that backup file yet;
>you need it for step 4)
>4) Add the subscription to the publication (calling sp_addsubscription
>with the @sync_type = 'initialize with backup' and provide the
>relevant details for your backup file)
>
>One thing to watch out for is making sure that the distribution
>retention is set sufficiently high so that you have time to ship the
>backup file and restore it before transactions start to roll off. You
>can either set the minimum retention or you can disable the
>distribution cleanup job while you're doing your work. Good luck!
>--
>Ben
>

>> Hi Folks,
>>
>[quoted text clipped - 4 lines]

>>
>> Any suggestions,ideas or very good links to do this?

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-replication/201008/1

[Ben Thul]

This is a "help me to help you situation". What about the above
explanation was unclear for you? Only when I know that can I try to
explain it better.
--
Ben

On Aug 26, 3:58 pm, "abcd123477 via SQLMonster.com" <u63807@uwe>
wrote:

> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-replication/2010...

[abcd123477 via SQLMonster.com]

I am sorry Ben it was not you.But its me could not understand well.I am new
DBA I am sorry bro.
Actually i was looking how to deal with the snapshot folder at the creation
of the publication and configuring the distributor...

Ben Thul wrote:
>This is a "help me to help you situation". What about the above
>explanation was unclear for you? Only when I know that can I try to
>explain it better.
>--
>Ben
>
>On Aug 26, 3:58 pm, "abcd123477 via SQLMonster.com" <u63807@uwe>
>wrote:
>> Thanks Ben,
>>

>[quoted text clipped - 28 lines]

>> --
>> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-replication/2010...

--
Message posted via http://www.sqlmonster.com

[Ben Thul]

There's no need to apologize for being new or for not understanding.
Everybody (and I mean everybody) has been the new guy and there's
nothing saying that you have to understand everything. :)

I think I understand what you're getting at. I think that you assume
that you need to create a snapshot when you create a publication.
That's not true. In fact, you needn't even create a snapshot agent if
you don't want to. Does that make sense?
--
Ben

On Aug 27, 3:20 pm, "abcd123477 via SQLMonster.com" <u63807@uwe>