[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Database Syncing

Posted on 2011-03-14
11
Medium Priority
?
208 Views
Last Modified: 2012-05-11
I was wondering if somebody could point me in the right direction please?
I have the task of integrating a Dynamics CRM database on to a website and I'm trying to duplicate the CRM database on to the web server. The web server is hosted offsite, whilst the CRM database is hosted in the office. The two are connected with a standard ADSL broadband link.
Ideally I would like these two databases to be constantly identical, that is if the database is updated in the office, then it pushes the data to the webserver database. If the website is updated, then the data is pushed back in to the office. All automatically.

Is this possible within Microsoft SQL Server 2008?

Many thanks
0
Comment
Question by:terryatnexus
[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
  • 5
  • 4
  • 2
11 Comments
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 2000 total points
ID: 35130190
This is possible - since they're both using SQL Server and you want two-way synchronization, you may be able to use "Merge Replication":

http://msdn.microsoft.com/en-us/library/ms151329.aspx

However, it depends on how much control you have over the "website" database you're talking about. Is it on your server, or is it hosted somewhere? If it's hosted, you may not be able to replicate the data, as there's some advanced configuration needed to do so, and I don't know of any webhosts that allow it.

Even with a restrictive web host, you may be able to set up one-way replication - something like transactional replication - that's lighter weight and since it's one-way, it doesn't require quite as intensive a configuration.

0
 

Author Comment

by:terryatnexus
ID: 35130549
Thanks Ryan - I have administrator RDP access to both CRM and Web Servers. I'm progressing slowly through the configuration. Do I have to push from the publisher to the subscriber, and then push again from the subscriber back to the publisher/distribution database or will the changes be pull back when the publisher pushes out?
0
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 2000 total points
ID: 35132349
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 30

Expert Comment

by:Feridun Kadir
ID: 35133387
Using merge replication to add data to the CRM databases is absolutely not supported by Microsoft. Microsoft Dynamics CRM is not simply a database but a CRM engine with an API that talks to the database.

Adding records to the underlying SQL database that hosts CRM data is not supported. Any records must be added via the CRM platform (web service calls) to ensure that the application works properly, honours security and so on.

If you want to add data to CRM from your website then you will need to code the website to use the CRM web services to add, delete or update records. Review the Microsoft Dynamics CRM SDK (www.microsoft.com/downloa) for a ton of information.

Similary getting data from CRM should not be done by querying the tables in the CRM database but instead using the filtered views that are provided.
0
 

Author Comment

by:terryatnexus
ID: 35137290
Thanks Feridun that would appear to make perfect sense, however won't that add a large amount of traffic to their broadband line for simple lookups? Surely I still need to replicate the database and reference it from the live web server to avoid eating up their broadband bandwidth?
0
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 2000 total points
ID: 35139037
If it's not supported, that doesn't mean it's not possible - it just means you won't get any help from Microsoft if/when something goes wrong, even if what's broken is totally unrelated to your unsupported process. Don't let it dissuade you from trying, but just know that you're on your own if it breaks, which can be shaky territory, depending on your support commitment to the users of this application.

While it makes sense that you could use two-way replication on the databases, I'd be wary - what happens if there's a conflict? What happens to data that's constantly changing in both databases and can't be resolved? Replicating the database to somewhere else for reporting is one thing - trying to maintain who copies, both with user update load, and keep them both in sync, is an entirely different beast.

When I recommended my solution earlier, I didn't really think about the implications of merging changes from both sides - after some more thought, I'd be much more eager to recommend that you build something that uses the API set to write back to the database.
0
 
LVL 30

Expert Comment

by:Feridun Kadir
ID: 35139512
I agree with ryanmccauley.

To answer the bandwidth question - you could still have a copy of CRM data on the web server and query the local database for lookups. It all comes down to how you design and partition the data.  My main point is that to work the CRM database in a supported manner you should use the APIs. But that doesn't prevent you having a copy of the data in another database. Your problem becomes how to keep them in sync when changes are made.
0
 

Author Comment

by:terryatnexus
ID: 35139537
Thanks Ryan that sounds like a great plan to me, I just worry about the bandwidth overhead on the broadband line and the uptime of the website based on any downtime of the broadband in the future.
What would would you recommend doing in my situation? How about I write changes back to the live database using the API and then have a read only database on the web server that is replicated through a one way sync (to the web server)

Would that be a workable solution? I don't think there are going to be that many writes back to the database. Definately more reads thats writes.
0
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 2000 total points
ID: 35139632
I'd favor the solution you've just suggested - the read-only copy is available for reporting, and then you have only a single copy of your changes since you're using the API for updates. I wouldn't worry about the bandwidth - after the initial sync, the ongoing changes are pretty lightweight - though if in doubt, you can always test the load and see how it goes before you start asking people to rely on it.

Though it obviously differs from case to case, I'd suspect that the impact on your bandwidth usage will be higher as a result of the replication than it will as a result of the API messages. API messages (though a webservice, I'm assuming) are small packets of XML and, unless there are thousands and thousand of messages, won't present a significant impact on your bandwidth usage. As for replication, SQL Server does offer some options for compressing the changes that are sent, and the bandwidth usage is manageable, that's the part of the process I'd keep an eye on.

For reference, we have 8 field servers doing transactional replication to our central office, and though they're not super-high-capacity servers, they are pretty regular changes, and they're using something like 8-10kbit each - about 1-2KB/second. That's significantly less than streaming even a single online radio station to your desktop :)
0
 

Author Comment

by:terryatnexus
ID: 35139685
Ok that sounds great. My remaining question is: If I send write back changes to the API, what type of replication do I need to use in order to have the copy of the database on the web server updated immediately? I forsee there being a problem if somebody say changes their password and the effects don't happen immediately. Currently I've setup 'Merge Replication' as this is the only replication service support by the web servers database: SQL Server 2005 express. Do I need to purchase a superior version of sql server in order to achieve the immediate changes?
0
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 2000 total points
ID: 35139818
The cost overhead of "Immediate" replication is pretty large - if you force SQL to replicate transactions immediately, it will cause a lot more traffic over the connection. If you let it batch transactions up and send them in small bunches, it can do so much more efficiently, using smaller amounts of bandwidth overall.

It appears you need an upgraded version of SQL Server to support any other types of replication, including "Transactional" replication, which allows you to replicate things immediately, as they happen.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

656 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