SQL Server Performance

Replication without initial snapshot

Discussion in 'SQL Server 2008 Replication' started by cnikirk, Dec 17, 2010.

  1. cnikirk Member

    I have a large database several terabytes in size. I would like to replicate only the new data that is being inserted or updated and not the whole database. Is it possible to set up some kind of replication where I don't need the initial snapshot of the database? I just want the new data in a database with the same schema so that it will be much smaller than the original.Thanks.
  2. satya Moderator

    How many tables exists on this database?
    How many tables can be involved in replication?
    Yes it is possible with transactional replication.
  3. cnikirk Member

    I want to replicate all tables but without the data. There are around 75 tables. How do you start transactional replication without the initial snapshot? Thanks. Oh and I believe I read that this is possible by using a backup as the snapshot, but I don't even want that. I simply want empty tables so that I will need very little space in the destination db.
  4. satya Moderator

    Is this going to be continuous that you want to replicate the database objects without the data?
    If so your best bet is to generate database & object creation step on regular basis and apply the same on destination server. to copy the table structure without data::

    SELECT * INTO tblNew FROM tblOld WHERE 1=2

    The above query will copy the structure of an existing table(tblOld) into the new table(tblNew).

    See the TSQL below which will be used when a snapshot needs to be generated (look at bold) :
    exec sp_addpublication @publication = N'Test', @description = N'Transactional publication of database ''Test'' from Publisher ''TestSrv''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
    Coming back to your question are you looking to provide sort of standby or availability for your databases to just keep the structure and not the data, then replication is not a suitable feature in this case.


  5. cnikirk Member

    Thanks for your reply. I'm probably not being very clear. I do not want a snapshot ever. My current db is about 2.5 terabytes. The new db I want to create with the same structure can never grow over 100 gig.We want to replicate the new data that comes into the 2.5 TB database over to the new small db. At some point we will purge data in the new database so that only a few days worth of data is kept there. We don't need all of the 2.5 TB of info on the new database, we only need the new data. Does that make sense?This way our custom software can look at one server for older data, and at the other server for newer data.The reason we don't just start a brand new db on the new server is complex but there is a reason. We need the new data to flow into both the 2.5 TB database and the brand new one.Thanks.

Share This Page