locked
Best practice for local 'test' to 'remote' production mirroring RRS feed

  • Question

  • Hi

    Which kind of procedure do you suggest for having local (development) and remote (production) database (sql2008 standard) synchronized?
    I read about mirroring, log shipping, merge replication and don't know where to start

    Monday, January 23, 2012 11:35 AM

Answers

  • Take look into Replications (Merge,Snapshot,Transactional)
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by zipfeli Tuesday, January 24, 2012 10:01 AM
    Tuesday, January 24, 2012 6:16 AM
  • I am with RamJaddu on this... You should go for Backup + Restore on the system. And doing it automatically should not really be that big of a deal. The easiest way to do that is to write an additional copy-only full backup to a share with the SQL Agent and have another agent job on your Dev system run the restore. Two lines of SQL code...

    In the end there are many things to consider when you go down that road, and the main issue for me would be: What do you plan to change on Dev in the DB? Will you update data? Will you update structures? Will you update code? If the answer to all this is no, well, then you have an easy life. In this case you could go for mirroring and apply a database snapshot on top... Readonly, but accurate and easy to do. If you only update code you can follow Uri and go for replication. If you start updating data things get tricky... Most of the time replication will still do fine, but you might end up with key violations if you are not extra careful. At latest when you start changing the data structures/schemas you are doomed and we go back to Backup+Restore...

    So... Take your pick...

    • Marked as answer by zipfeli Tuesday, January 24, 2012 10:01 AM
    Tuesday, January 24, 2012 6:32 AM

All replies

  • What is your goal? With Mirroring and Log Shipping your secondary database is read only but having mirroring along with Witness, you can automatic failover.....

    http://msdn.microsoft.com/en-us/library/ms190202.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, January 23, 2012 11:49 AM
  • Sycn Prod and DEv Environments ?

    I would suggest you do with some kind of full back refresh on weekly / forth night basis - 


    http://uk.linkedin.com/in/ramjaddu
    Monday, January 23, 2012 12:09 PM
  • Thanks for fast reply Uri. I want to develop with production data..sorry for unclear question
    Monday, January 23, 2012 12:11 PM
  • Thanks, I'm already doing this by hand..now I wan't to accomplish that task automatically :-)

    Monday, January 23, 2012 12:13 PM
  • Take look into Replications (Merge,Snapshot,Transactional)
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by zipfeli Tuesday, January 24, 2012 10:01 AM
    Tuesday, January 24, 2012 6:16 AM
  • I am with RamJaddu on this... You should go for Backup + Restore on the system. And doing it automatically should not really be that big of a deal. The easiest way to do that is to write an additional copy-only full backup to a share with the SQL Agent and have another agent job on your Dev system run the restore. Two lines of SQL code...

    In the end there are many things to consider when you go down that road, and the main issue for me would be: What do you plan to change on Dev in the DB? Will you update data? Will you update structures? Will you update code? If the answer to all this is no, well, then you have an easy life. In this case you could go for mirroring and apply a database snapshot on top... Readonly, but accurate and easy to do. If you only update code you can follow Uri and go for replication. If you start updating data things get tricky... Most of the time replication will still do fine, but you might end up with key violations if you are not extra careful. At latest when you start changing the data structures/schemas you are doomed and we go back to Backup+Restore...

    So... Take your pick...

    • Marked as answer by zipfeli Tuesday, January 24, 2012 10:01 AM
    Tuesday, January 24, 2012 6:32 AM
  • If you want to have readeable secondary replicas you should take a look to SQL 2012 AlwaysOn feature
    Javier Villegas | SQL Server DBA | @javier_vill on Twitter
    Wednesday, January 25, 2012 2:16 PM