SQL Server Performance

Implementation of Replication Strategy

Discussion in 'Getting Started' started by lcerni, Jun 7, 2010.

  1. lcerni New Member

    I was asked to research and implement a new replication strategy to keep the development database in sync with production. Also, this functionality should be expanded to include the user's local databases as desired. Any thoughts out there? I am researching solutions and I was wondering what worked best for most people.
  2. ghemant Moderator

    Hi,
    One has to carefully analyze the business needs and feasibility of implementing available options, could you please elaborate so that it can be easy to get an idea and to give advise!
  3. preethi Member

    I believe you do not want to keep only the schema up to date and you want all data to be copied to Development environment
    A couple of questions here:
    1. Is there any data protection laws agreements affected?
    2. How busy is your production system?
    3. How often you want to move? Weekly? Daily?
    4. What is the recovery model of the production databases? How often you take the backups?
    5. Do you have a mechanism to keep the work in progress? (Schema changes and test data creation in development environment)
  4. lcerni New Member

    I am new with this company so I will try to explain what I know so far. The data in the development database is very old. They want to refresh the data but not the other objects such as procedures due to the fact they might be in development. With transactional replication the data is refresh constantly and thus if a developer wants to make a schema change to a table then the transactional replication would fail. Thus I am thinking that transactional replication is not the way to go. With transactional replication once the initial snapshot is done, the log is used to update the replicated database.
    I am thinking snapshot replication is the way to go but I don't know enough about this replication. With snapshot replication you do a full refresh of the tables. You don't use the transactional log. I need to find out how often they want the data refresh. I am thinking once a month but I will need to verify with management. Also the developers will need to keep track of their schema changes and apply them after the refresh.
  5. preethi Member

    I think it is better to take a backup and restore. You can ask the development team to take care of all the schema changes and procedures (one way is to have another copy and later you can compare.It may not be a good practice to refresh the database from production frequently as you need to disrupt the development work.
    Anyway, its just me.

  6. satya Moderator

    As you had replies from Preethi on the aspects, I would say it is useful to divide replication into two broad categories: replicating data in a server to server environment and replicating data between a server and clients.
    The type of replication you choose for an application depends on many factors, including the physical replication environment, the type and quantity of data to be replicated, and whether the data is updated at the Subscriber. The physical environment includes the number and location of computers involved in replication and whether these computers are clients (workstations, laptops, or handheld devices) or servers.

Share This Page