locked
Database Copy update RRS feed

  • Question

  • I have sql server 2008 database and i made a copy of it on the same server instance , i need when there is any update to first database then the update reflected on the copy database

    Thanks.

    Sunday, February 17, 2013 3:00 PM

Answers

  • Hello Malik, Ideally, you are looking at Transactional Replication where the changes made to source database(publisher) are reflected to destination database(subscriber) almost instantly. you have other options as well such as database mirroring and log shipping but in both the cases secondary database is not accessible at all times. There are different replication methods as well but If you want to be able access the secondary database just like the first one and have updated data sent to the secondary database as soon as possible and the dataflow is always one way(from source to destination) , Transactional replication choice is the best for you. Only constraint is your tables should have primary key to do transactional replication.

    This video from Microsoft should help you in configuring Transactional replication http://technet.microsoft.com/en-us/sqlserver/ee818985.aspx

    or refer this http://www.sqlservercentral.com/blogs/databaseexpertisecom/2010/10/03/transactional-replication-setup-_2800_by-abi--chapagai_2900_/


    Hope it Helps!!


    Sunday, February 17, 2013 8:30 PM

All replies

  • Hello Malik, Ideally, you are looking at Transactional Replication where the changes made to source database(publisher) are reflected to destination database(subscriber) almost instantly. you have other options as well such as database mirroring and log shipping but in both the cases secondary database is not accessible at all times. There are different replication methods as well but If you want to be able access the secondary database just like the first one and have updated data sent to the secondary database as soon as possible and the dataflow is always one way(from source to destination) , Transactional replication choice is the best for you. Only constraint is your tables should have primary key to do transactional replication.

    This video from Microsoft should help you in configuring Transactional replication http://technet.microsoft.com/en-us/sqlserver/ee818985.aspx

    or refer this http://www.sqlservercentral.com/blogs/databaseexpertisecom/2010/10/03/transactional-replication-setup-_2800_by-abi--chapagai_2900_/


    Hope it Helps!!


    Sunday, February 17, 2013 8:30 PM
  • Do you want the 'copy' database to be read/write or only READ? If it is only read , take a look at Logshipping or even mirroring...

    http://www.sqlserver-training.com/how-to-setup-mirroring-in-sql-server-screen-shots/-


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, February 18, 2013 5:58 AM
  • Hi,

    I believe it depends on the type of the copy you want if is updatable(read/write) copy you can use transactional replication.

    if you want it a copy there is no update on the copy you can use log shipping or mirroring.

    I recommend if you read about SQL Server high availability solution to evaluate each one and choose the best.

    I hope this is helpful.


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid

    MCSE Data Platform
    MCITP: SQL Server 2008 Administration/Development
    MCSA SQL Server 2012
    MCTS: SQL Server Administration/Development

    MyBlog

    Monday, February 18, 2013 6:21 AM
  • Hi Malik

    You can choose any option given by all here geeks, but there is one more option "Database Snapshots" If you need read only updated copy of databases.

    Thanks

    Saurabh Sinha

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Monday, February 18, 2013 1:29 PM
  • As said every one,

    First you need to define

    what should be your secondary database status i,e it can be read only status or no one can use so that it can keep restoring status?

    you can use logshipping/database mirrioring & Snapshot(this is not much good option) or you can set up your own custom scripts to get it restored using an windows batch file or with the agent .


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Thursday, February 21, 2013 10:28 PM