Advice on which replication method to use for our requirements/hopes

- 2 instances of SQL 2005 Standard connected to each other over a secure 1Mb WAN link
- Instance "A" is production now and has been for a few months
- Instance "B" is fresh and has no databases on it yet
- Current (full and incremental) backups of A already exist on the server for Instance "B" (roughtly 7GB) and are being kept current by the hour
- DB in question has 2 schemas which are tightly related, but still distinctly different and for seperate apps
- Identity columns are used heavily in both Schemas

- High volume (potentially BULK), but small (< 1k per row) inserts and updates WILL frequently occur at instance "A" for schema "1". Low volume (and never at the same time) updates should be possible at instance "B" for schema "1".
- Moderate volume inserts/updates should be possible at instance "B" for schema "2" and will rarely occur at instance "A", but should be possible.

With the "given" facts, and stated "goals/variables", which replication method makes the most sense? Any additional factors, steps, considerations, directions provided would be greately appriciated. I feel capable of setting up any replication scenario, but I'm not confident in my ability to determine which would be the most appropriate.

Also, if our requiremets don't appear feasable for SQL 2005, but would be with SQL 2008, feel free to enlighten me. Upgrading isn't out of the question and this replication scenario can be put on hold if there's just cause.
Who is Participating?

the setup you are going ofr makes things quite tricky as your looking at doing the bulk of the inserts on either instance depending on the schema

Do you have 2 servers you can do this on as that is a requirement of merge replication

no real special considerations you need to make as both instances will be seeing lots of activity as both are going to be taking the bulk of inserts depeding on schema, however you wioll need to pay special attention to lock resolution rules when configuring the replication.

By the very nature of merge replication data can be entered requesting things like the same auto number field at the same time and 2 rows could be attributed the same auto number, one at each replication point, an dhow you define the rules will govern how the merge resolves it.

Its not amazingly tricky to set up but you will porbably see some locking in the first couple of weeks whilst you tweak the rules

if you havent configured this before you may want to get someone in to assist you with some wexperience in the configuration.

basically you set up one machine as teh publisher/ distributor and one as the subscriber, 2 choices rom here firstly the subscriber holds teh database but it is read only, not useful in your scenario, or secondly the subscriber recieves the database from teh distributor, updates it pushes it back to the distributor/publisher and merges it with teh other live database on teh system which is what you will need to do.



littl ebit more information needed to be able to advise which method of replication you require.

is instance B going to be used purely as a backup in case of instance 1 fail or are you going to use instance b for pulling information?

I beleive in reading your goals that you want to have inserts occurring at both instances then you are going to have to employ merge replication, be careful as you need to get your head around possible lock issues due to inserting into the same dfatabase at 2 locations. merge replication is the only type of replication that will allow dual inserts
RainAbbAAuthor Commented:
We do want inserts and update to be possible at both ends and we will be reading from both in a very similiar manner.

For Schema "1":
The nature of our application is that reads generally happen on rows quite some time (hour minimum i'd say) after inserts/updates are complete. Column-by-colum per row, data is actually pretty static. IOW, once a row is submitted, it will see 1 update for 1 column, then shortly there-after, another update for a different column, then no more updates. Data in this schema will be used at both ends, but far more inserts will happen at Instance "A". Per my last description, each row inserted generally goes through the same update process.

If Instance "A" were to become a loss, instance "B" would  hopefully be able to pick up the slack, though we will likely still maintain backups for Instance "A" at the instance "B" location so there is no chance of total loss.

For Schema "2":
This schema acts more like a typical CRM application, but inserts/updates will happen almost entirely at instance "B" and instance "A" for this schema will be more like a backup.

Does that anwer your questions and help clear up our intentions?

Any special considerations I should look into more than others for merge replication?
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

RainAbbAAuthor Commented:
I am looking for someone to help out a bit more by email, phone, or even RDP (for pay of course). That something you'd be interested in? If so, is there a way to exchange contact info that is allowed by Experts-exchange and wouldn't be public?
Whilst id be happy to help, id be extremely limited as to the extent i could. Not sure if you have checked my profile but Im based in the UK so our time differences would make the set up extremely difficult to co-ordinate, also I have a 38 week pregnant gf at home and so im expecting to be on paternity leave any time soon which would also be a bit of a problem.

if you do have a small budget for this my advise would be contact a local computer jobs agency and arrange for a contractor to come in and assist you with the configuration. At worst you should be looking at 3 days work to get this configured.

if you take care of all the installation of the instances firstly and then get the consutlant in to assist with the setup I would be epxecting to pay no more than $1000, which in order to get a correctly installed and future proofed set up is a relatively small amount.

if this isnt a huge urgent proect to accomplish though and you have a couple of machines to set up a test environment in then that would be the ideal way forward, get yourself a good sql book and work through configuring this yourself as supporting something you have setup yourself is a hell of a lot easier than trying to work out what some else has done in your environment.

Apologies for not being able to commit beyond this space but i dont want to offer something i might not be able to deliver upon :o)
RainAbbAAuthor Commented:
Thanks for the help and best wishes to you, your family, and the new addition on the way :)  If you'd be willing to provide even limited support via email, drop me a line and say hi. The time difference isn't an issue for me. We have family in England, family from England, and I work very late typically so it wouldn't be anything new or troubling. Time is money and even if I'm doing the work on my own, having email contact with someone like yourself (congrats on your sql certification) could save me lots of time which is worth money for us both :)

My email is (michael at compteknet commercial in case it was filtered)

Take care
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.