Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Posts
    272

    Unanswered: Replicate every 15 minutes: insane?

    The company I work for has no experience with replication, and neither have I.
    Now I recieved a functional specifications document and apparently what they want is use replication to maintain a copy of a production database on the data warehouse server.
    The database is 70GB, and they want to do it every 15 minutes (so their reports will contain up-to-date data).

    As I said, I know nothing about replication, but to me, this sounds like madness. I fear that this will will (at least) have a negative impact on the performance of the production database.

    So is it possible? Does replication have a big impact on the database or is it hardly noticable? I expect about 500 new records every 15 minutes; production database and data warehouse are on different servers; both are SQL2005.
    Last edited by ivon; 01-15-08 at 04:38.

  2. #2
    Join Date
    Jul 2007
    Posts
    96
    You don't necessarely need to transfer the whole 70Gb of data. You can, for instance (and note that I'm not a replication expert either), set a point in time and transfer the whole data. From that point in time on you only transfer the changes. There are a couple of ways to do it but I'll leave that to the gurus to explain as I never did it myself and I only know how it's done in theory

    A database that only has 500 new records / 15mins should have pretty much no impact on replication performance if it's done right.

    Don't worry just set up some decent hard-drive arrays and a fast network connection so the transfer is done as fast as possible, thus minimizing the overall impact.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I have set up replication but not on a big, mission critical system.

    Diabolic is talking about transactional replication. We had it set up similar to you - OLTP, 5 min transactional replication (maybe less...) to DSS database. We experienced no problems. I haven't set this up for some time though and, as I say, it was not on a really critical system. Others here will be better able to help. 15 min transaction replication does not sound a problem to me though on the surface of it.

  4. #4
    Join Date
    Nov 2002
    Posts
    272
    Thanks for the reassuring replies. I asked around and transactional replication is what they have in mind. If I get it correctly the replicated database reads the changes from the transaction log of the source. My fear was that it would do complicated queries.
    We requested that testing will be done before we give their plan our OK, though. Just to be sure.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Gosh - I'm starting realise how much I have forgotten about transactional replication....

    I think the following are true:
    The transaction is not considered committed (the properties of ACID satisfied) until the replicated transaction is committed.
    You can chose, with stored procedures, to replicate the data changes OR instead the procedure execution command. The former (may) require much more data replicated but less strain on the subscriber. The latter the opposite.

    .... I'm really not sure about my first point now - I might be confusing with mirroring.

  6. #6
    Join Date
    Feb 2007
    Posts
    62
    Log file shipping. You can export the redo logs of committed data. You can also import them. Tada!
    You need to ensure certain configurations are in place first but basically that's it.

    http://www.microsoft.com/technet/pro.../logship1.mspx

    Other replication stuff here:
    http://msdn2.microsoft.com/en-us/library/ms152485.aspx

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by pootle flump
    Gosh - I'm starting realise how much I have forgotten about transactional replication....

    I think the following are true:
    The transaction is not considered committed (the properties of ACID satisfied) until the replicated transaction is committed.
    You can chose, with stored procedures, to replicate the data changes OR instead the procedure execution command. The former (may) require much more data replicated but less strain on the subscriber. The latter the opposite.

    .... I'm really not sure about my first point now - I might be confusing with mirroring.
    Mirroring is new with SQL 2005 (transactional replication existed in SQL 2000).

    Mirroring you must kick the users off periodically to apply log files. Transactional replication, you do not have to do that.

    I believe that you can configure transactional replication so that the subscriber does not have to be on line (asynch commit), but I don't swear to it.

    Log shipping (SQL 2000 and SQL 2005) can accomplish much of what you are talking about doing, but again I believe that it requires that used periodically get booted off in order to do reporting.

    In all, I think your best bet is to explore transactional replication.

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •