Solved

Database Syncing

Posted on 2011-03-14
11
198 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
  • 5
  • 4
  • 2
11 Comments
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 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 500 total points
ID: 35132349
0
 
LVL 29

Expert Comment

by:feridun
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 500 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 29

Expert Comment

by:feridun
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 500 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 500 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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

19 Experts available now in Live!

Get 1:1 Help Now