locked
SQL Server 2012 - Updatable transactional replication - No data initialization ? RRS feed

  • Question

  • Hi,

    Is it possible to configure updatable transactional replication to initialize the schemas and everything but ignore the data ?

    In the current case, the transactional part of the DB is huge and contains only live data that are deleted anyway after a few weeks. If one of the DB server fails we don't really care about that live data, thus spending a huge amount of time transferring the DB backup/snapshot (or the data part of the initialization) from a site to another would take a few weeks and then we would still have to synch the delta between current and backup.

    So overall, by the time the new server is in synch again, the data that was loaded initially is gone or will be soon purged.

    Thus importing the data on initialization is wasted time and resources.

    Thanks


    • Edited by O.Ragain Wednesday, October 21, 2015 5:56 PM
    Wednesday, October 21, 2015 5:55 PM

Answers

  • Hi O.Ragain,

    It is impossible to configure updatable transactional replication to initialize the schemas and everything except data. But, you can reduce the initialize time using the following methords.

    When you initialize the subscriber, you can initialize the subscriber from other type of backups such as filegroup, differential or log backup as the initialization source instead of the full database bakeup, there is an example of initializing the subscriber from filegroup backup for your reference, detailed steps are shown as below.

    1.Configure the replication publication property, set “Allow initialization from backup” as ture.
    2.Take a backup of the filegroups on the publisher that you want to replicate.
    3. Restore the filegroup backup on the subscriber using RECOVERY option.
    4. Configure the subscription.

    EXEC sp_addsubscription 
    @publication = ‘publicationname’, 
    @subscriber =’servername’, 
    @destination_db = ‘Subscriber database name’,
    @sync_type = 'initialize with backup',
    @backupdevicetype ='disk',
    @backupdevicename = ' Path and name of backup file used for initialization'

    Alternative, you can also use other type backup to initialize the subscriber, for detailed information, please refer to this article.


    Ice Fan
    TechNet Community Support




    • Marked as answer by O.Ragain Tuesday, October 27, 2015 3:19 PM
    • Edited by Ice Fan Wednesday, October 28, 2015 1:25 AM
    Monday, October 26, 2015 8:47 AM

All replies

  • Hi O.Ragain,

    It is impossible to configure updatable transactional replication to initialize the schemas and everything except data. But, you can reduce the initialize time using the following methords.

    When you initialize the subscriber, you can initialize the subscriber from other type of backups such as filegroup, differential or log backup as the initialization source instead of the full database bakeup, there is an example of initializing the subscriber from filegroup backup for your reference, detailed steps are shown as below.

    1.Configure the replication publication property, set “Allow initialization from backup” as ture.
    2.Take a backup of the filegroups on the publisher that you want to replicate.
    3. Restore the filegroup backup on the subscriber using RECOVERY option.
    4. Configure the subscription.

    EXEC sp_addsubscription 
    @publication = ‘publicationname’, 
    @subscriber =’servername’, 
    @destination_db = ‘Subscriber database name’,
    @sync_type = 'initialize with backup',
    @backupdevicetype ='disk',
    @backupdevicename = ' Path and name of backup file used for initialization'

    Alternative, you can also use other type backup to initialize the subscriber, for detailed information, please refer to this article.


    Ice Fan
    TechNet Community Support




    • Marked as answer by O.Ragain Tuesday, October 27, 2015 3:19 PM
    • Edited by Ice Fan Wednesday, October 28, 2015 1:25 AM
    Monday, October 26, 2015 8:47 AM
  • Thanks for the answer.
    Tuesday, October 27, 2015 3:19 PM
  • No, but you could with bi-directional transactional replication. Transactional replication with updateable subscribers is deprecated in SQL 2012. Still works, but is not in SQL 2016.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Tuesday, October 27, 2015 5:37 PM
    Answerer
  • Hi Hilary,

    Yeah, I discovered that transactional replication would allow me to not sync anything at initialization which is great :) It is also, in the end, crazy easy to setup bi-directional once you use the right options (some options between publication and subscription stored proc do conflict). It did require me to revamp a few tables in the DB though. Your old article helped.

    Thanks

    Tuesday, October 27, 2015 6:44 PM