The Scribe Online Replication Service (Scribe RS) has been around for a while, and is typically used to provide local database access to Microsoft Dynamics CRM data that is not directly accessible with Microsoft Dynamics CRM Online. Scribe Online Replication Services is a great way to bring the data from CRM Online home. Whether you want a local backup of the data for disaster recovery or for local reporting purposes, Scribe RS is a quick-and-easy way to bring your CRM data into a SQL Server database. You can then access the database with SQL Server Management Studio or good old Business Intelligence Development Studio (BIDS).
Traditionally, the purpose of having a local CRM database was for reporting. A few years back, and quite possibly still, there are CRM power users that aren’t Fetch XML query experts. The Fetch XML query language unquestionably lags T-SQL in terms of familiarity and robustness to most database developers, and there are so many fewer tools that work natively with Fetch XML. For SQL Server Reporting Services (SSRS), you can get by with Fetch XML if you have to, but serious database developers need direct access to their data. Local database access allows Business Intelligence (BI) experts to utilize familiar tools such as T-SQL and SQL Server Integration Services (SSIS) to slice-and-dice; and move data around.
Besides reporting and analytics, creating a local data store can provide peace-of-mind should anything ever go awry with your CRM Online system. I’ve seen firsthand two scenarios where data has been lost: twice (in seven years since CRM Online was introduced), it was a users’ slip of the mouse-click on a bulk delete job where the data selection criteria wasn’t quite right. Bang, user error and you’re out a wide swath of critical business data. No backup? No restore. Simple as that. CRM Online does not provide for point-in-time restore of lost data — especially that which is caused by user error. Maybe a suggestion for future releases. I’m sure it’ll be part of the package one day. But not today. Wouldn’t it be nice to say, No Problem, instead of No Backup / No Restore?
The other scenario I’ve only seen once, just so you know I’m not sugar-coating anything here as a Microsoft Partner. We saw the CRM Online system go down for a period of hours — in fact, I believe it was down for the better part of a whole afternoon earlier this year for one of our clients. When the system came back up the next day, it was restored to a point-in-time earlier than when the most recent data was entered. Bad news, right? Um, yes. That was bad news. We went to bat for our client and were able to gain monetary compensation for the data loss, but it’s a scenario we don’t want to find ourselves in again, with, you know, no backup…
If you’re in a situation where you want to recommend a backup for CRM Online but were afraid that the client might balk at the level of investment required to implement such a solution, fear not. Scribe Online RS has always been a fairly low-cost, easy-to-implement solution — so long as you already had SQL Server. The hardware and software for implementing an on-premise SQL Server database system can be difficult to justify if all you really need it for is backing up your CRM Online system database — which may never fail in your lifetime. But, there’s always that one power user or superstorm out there that could just ruin your day when you least expect it.
The thing to know, now, is that you can implement Scribe Online RS for $99 / month; and your SQL Server database can also be deployed via the Microsoft Azure cloud for a fraction of what it would cost to implement an on-premise database system. You don’t need a Virtual Machine in Azure to run Scribe RS. A block diagram of what the basic system looks like is shown above. You know what CRM Online is. Scribe Online RS is a subscription service from Scribe Software. The Scribe Online user interface allows you to specify the Source as CRM Online and the Target as a SQL Server database. The nice thing is that that database can be an Azure database. And the Azure database, again, doesn’t need a Virtual Machine, just the Infrastructure-as-a-Service model will do just fine. Here’s a link to the “pricing calculator” for the Azure database.
The Basic edition Azure database will only get you up to 2 GB, but Standard can go as high as 250 GB. Don’t go with either the Web or Business models as they’re being phased out. So, as you can see from the graphic below, you can get a basic (sorry, standard) SQL database for as little as $15 or $30 / month. The Azure subscription comes with no upfront costs, no termination fees, and you pay only for what you use. Once the data is in the Azure SQL Server database, you can access it with a locally installed SQL Server Management Studio (SSMS) client. So you can run T-SQL queries to your heart’s content. More importantly, you can connect to that database with another Scribe tool, say, Scribe Insight. Or SSIS, or Cozy Roc+, or whatever your preferred data migration tool is; and you can run that data right back into CRM Online if anything bad ever happens.
The details of the setup are remarkably simple. It only takes a few minutes to complete the setup:
The nicest thing about the Azure database is that you can connect to is as if it were a local database. The most admin work you may have to do is to add your local IP Address so that Azure knows to open the firewall to allow you to communicate with the SQL Server. The connection information you need is your fully-qualified Azure server name, your database name, and your username and password. These are all data items that are created during the five-minute process of creating your Azure database.
Scribe too, both Online and on-premise versions, has no problem connecting with the Azure SQL Server database.
The price of $15 or $30 / month for SQL Server is pretty nice. It’s less than I expected, and makes the whole package work for cleints small and large — especially those with no existing on-premise SQL Server database system.
Scribe Online RS is also reasonably priced, at $99 / month. Oh yeah, and I almost forget the nicest thing about Scribe RS that makes this whole thing super easy: Scribe Online RS will effectively create the details of the database for you. All you have to do is say which entities you want to backup, and Scribe RS will create all the tables in the database. There’s no mapping required!
Of course, this solution will get you jump-started on either backing-up your CRM Online data; or creating your local database for Reporting and Analytics, if they’re your objectives. Make note that this configuration doesn’t actually cover the restore of your data. You do have to do mapping for that. You need a tool, like Scribe Insight (on-premise) or Scribe Online Migration Services or SSIS with Cozy Roc +, or SSIS with Kingsway to connect the data back to your CRM Online and migrate it back. You will have to be careful, and know a bit about CRM to successfully fix broken or lost data. But, the point of this article is that the data can be backed-up easily and inexpensively. The restore would not be difficult, but would require some level of expertise and tools to port the selected data back to CRM, with the right filtering criteria and mappings, etc. You may want to ask for some assistance with restoring the CRM data, but that’s a lot better situation to be in than not having any data to restore, right?