Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-06-17
6
Medium Priority
?
169 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 2000 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

916 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