Solved

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

Posted on 2008-06-17
6
155 Views
Last Modified: 2010-04-21
Given:
- 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

Goal/Variables:
- 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.
0
Comment
Question by:RainAbbA
  • 3
  • 3
6 Comments
 
LVL 25

Expert Comment

by:slam69
ID: 21801099
Hi,

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
0
 
LVL 1

Author Comment

by:RainAbbA
ID: 21806426
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?
0
 
LVL 25

Accepted Solution

by:
slam69 earned 500 total points
ID: 21810781
Hi,

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.

HTH

Jay
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 1

Author Comment

by:RainAbbA
ID: 21810947
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?
0
 
LVL 25

Expert Comment

by:slam69
ID: 21810996
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)
0
 
LVL 1

Author Closing Comment

by:RainAbbA
ID: 31467883
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@compteknet.com (michael at compteknet commercial in case it was filtered)

Take care
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now