none
Mirroring/Replication to a copy that will be updated

    Question

  • Hey guys,

    I'm new to SQL management (databases in general, really), and I recently was given a project to sort of help me get my feet wet.

    We currently keep the client's data in-house and they use some software of ours that pulls the data; they want a copy of it kept up to date at their site, and they will want to modify the database at their end (probably just adding custom tables and whatnot for their specific locale; the software will still be pulling from the in-house database). From what I've read so far, it looks like mirroring is pretty trivial -- simply plug in a fully formed TCP and there ya go. Is that the best solution here? Will that copy the initial contents of the database?

    Moreover, from what I understand, we have multiple DBs residing on the same server; can it be scaled for JUST that one DB, or does mirroring do it for the whole server instance?

    We're running 2008, I believe.

    Thanks,

    Colin


    • Edited by cosmic_cow Sunday, February 19, 2012 7:59 PM Added info
    Sunday, February 19, 2012 7:35 PM

Answers

  • Hi,

    If your requirement is below ie you have the db at your end . You want an updated copy of the db at customer end and they should need to make customer changes with db like creating tables etc. Is my understanding correct?

    If yes, your best possible method would be transactional replication where data from your end goes to customers end but changes done at their end does not come back. Ofcourse customer would not be able to make any changes (structural changes) to tables which are replicated from your end. They can obviously add more tables at their end and it wont be reflected at your end unless you manually create them.

    Replication is done per database and not for the whole instance as such.

    Mirroring is not a good choice as the secondary db will never be readable.

    HTH


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com

    Monday, February 20, 2012 12:03 PM
  • You probable need to set the article properties to something like keep existing object unchanged in the action if in use setting.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Monday, February 20, 2012 6:15 PM

All replies

  • Hi,

    Mirroring would not be a good fit for your requirements. Mirroring only protects databases that have been configured for mirroring. Clustering protects the entire instance.

    In your requirements you mention that the secondary system needs to be updated. Mirroring keeps the mirror (secondary) copy of the database in the restoring state. Therefore it is not available to service requests.

    You also state that clients will be receiving data from your site but they want to update the database as well. Do you have any plans to how you are going to limit there access so they don't change tables that your software uses? Are you going to deny them access to update your applications schema or add a schema that they can add their specific tables to?

    Replication might be the closest fit for you but you need someway to prevent overwrites of the changes they make by the replication from the main site.

    Selecting the Appropriate Type of Replication


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    Monday, February 20, 2012 1:56 AM
  • Hi,

    If your requirement is below ie you have the db at your end . You want an updated copy of the db at customer end and they should need to make customer changes with db like creating tables etc. Is my understanding correct?

    If yes, your best possible method would be transactional replication where data from your end goes to customers end but changes done at their end does not come back. Ofcourse customer would not be able to make any changes (structural changes) to tables which are replicated from your end. They can obviously add more tables at their end and it wont be reflected at your end unless you manually create them.

    Replication is done per database and not for the whole instance as such.

    Mirroring is not a good choice as the secondary db will never be readable.

    HTH


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com

    Monday, February 20, 2012 12:03 PM
  • Ashwin is correct - mirroring is a bad choice as the mirror (the principal-source server will mirror data to the Mirror - destination database) will be inaccessible until failover.

    Transactional Replicaton is the best option here - but keep in mind that subscriber changes will be wiped out at the next iniitialization. You will have to be careful how you set this up to avoid this.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Monday, February 20, 2012 3:07 PM
  • The impression I got was that they will only need new tables to contain site-local data (the software we manage handles several sites), but I have contacted them to find out for certain.

    If that is the case (i.e. the data that is replicated from our end remains unchanged, they just add new tables or whatnot of their own), I could simply set it up as transactional replication and that would take care of it, correct? Or would their new tables be wiped out at the next initialization as well? And if it is the case that they would be wiped out, how would that be avoided?


    • Edited by cosmic_cow Monday, February 20, 2012 4:49 PM clarified my question
    Monday, February 20, 2012 4:49 PM
  • You probable need to set the article properties to something like keep existing object unchanged in the action if in use setting.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Monday, February 20, 2012 6:15 PM