Answered by:

Mirroring/Replication to a copy that will be updated

Question

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?

Answers

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.

All replies

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.

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.

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.

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?

Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.