Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-06-17
6
Medium Priority
?
166 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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

Technology Partners: 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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

660 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