Building a Demo Server - Part 2 Moving Data
Introduction
Ahh, the demo server. If your company is selling things, you probably will get asked to setup a system that allows the sales people to demonstrate your products without affecting live or production systems. Sort of a playground where salespeople and customers can make changes, try out the system and simulate any customizations you offer for potential customers or clients.
This article continues my series on building a demo server and deals with moving data from one server to another and the problems and choices that you have. Part 1 dealt with determining with picking a server.
What's Involved?
I was tasked with trying to keep the products my company sells on both the live database (called Production) and the demo database (called Demo) in a loose synchronization. For most companies that have to sell something, this will probably be a familiar scenario. There are people whose job it is to enter new products into the system. In my case, we have two different methods, manual data entry and automated bulk loads of products. Both of these processes bascially work the same in that a product is added to a series of tables in the database.
A fairly straigtforward set of data needs to be moved, but there are a couple twists. First, there is no good candidate key for a product in our system. The best key, the name, doesn't work because of a few business rules, like two different vendors can offer the same product (by name) but they would be different products and we cannot change the name. As a result, we have chosen a numeric identifier as the key for this table and changing that is not practical at this point.
The second twist is that the demo server allows updates. Salespeople want to be able to show customers that we can customize the system for them, including selling the customers own products to their employees or customers using our system. This makes more sense in my current job as some of our clients have their own training content and we allow them to manage that content along with our content through our system.
Choices
Moving data.
It's what I do most every day of the week in one fashion or another. Take data from here, send it there, clean it up, etc. However a demo system has some unique challenges. The data needs to be fresh, but not too fresh. It has to be clean (can't have real orders showing up on a demo system), and it has to be correct. And most important, it has to fit the salespersons' needs.
One option that I have used is backup and restore from the production system. Usually on some schedule like monthly. For one company, this was ideal. Every 5th of the month, we'd move backup the production database, restore it on the demo server, and fix a couple logins. I love this. It was automated, scripted, and ran without me doing anything other than reading my email to see the notification that it had completed.
The data was up to date and it was used to sell new services we had developed and piloted on demo, to existing clients. They could see their real data in the demo system used in some novel, interesting way that was designed to transform more of their revenue into our revenue. This was by far the smoothest demo system.
Great, you say. Let's all do this. Easy, post the script and we're off. Well, I suspect a few of you are shaking your heads and asking "what about new customers?". An interesting problem, but one that the salesman themselves solved.
They loaded demo data onto the production system. Didn't use it, but each salesperson had created a ficticious company that they were familiar with and loaded a ton of bogus data. Didn't demo on the production system, just did it to ensure this data went onto the demo system each month.
Problem solved, right?
Well, almost. As long as no demo crosses from the 4th to the 6th of a month that requires the demo change data. Since something you changed on the 4th would be overwritten the night of the 5th with the production data.
Not a big deal for that company, but it is for many others, including the next company I worked for.
So what's the next choice? Well, in my next company, we implemented replication. (shudder, shudder, shiver up my spine). I'm not a fan of replication, but in this case, we only wanted to move over product type data. We wanted to demo our system showing the actual products that we were selling and services available, without, however, the customer and order information.
By setting replication up on certain tables, we were able to ensure that the product data was easily moved across from production to demo. Clients could then be setup with a ficticious company, enter orders, see data move through the system, and they would be wowed, entranced, and hopefully, sign a PO to buy some software.
Wow. Replication, let's implement that. Hold on. This isn't always the best solution either. Think about your system. If it's like mine, you've got referential integrity, Primary keys, Foreign keys, etc. setup. Suppose you've got some product you're selling and it's not selling. Someone decides to delete it from the production system. No problem. The deletion gets replicated and we're all good, right?
Maybe not. On the demo server, most likely you will be allowing changes to occur and could create some foreign keys that might not exist on the production system. In my case, we have "logical" deletes with a flag so we never lose physical data. If you can implement this, you're much better off. If not, then replication may not be the best thing for you.
The last choice that I've come up with is custom scripts for moving data. I've done this in the past using DTS. In my case, I had the flexibility of having a datetime field in every table that I was moving to the demo server. I could then store the date of the last data movement and compare this to the datetime in the table to extract the rows. Usually I'd have some script like this in the DTS package data transform task:
select a.*
from mytable a
inner join processtracker p
on a.timecolumn > p.lasttime
Using something like this, I transformed the changed rows (including logical deletes) to a staging table on the demo server. I then would perform the standard update matches, delete matches from the staging table, insert new rows process for each table. Once you get it working, the packages are easy to create and work pretty flawlessly.
Conclusions
I'm sure there are other solutions out there. Perhaps even better solutions, and I'd love to hear about them. In part III of this series, I'll look at a couple innovative solutions that I've seen setup for running demos that have worked really well.
As always, I welcome feedback for this article (use the 'Your Opinion' tab below) and please take
the time to rate this article.
Steve Jones
©dkRanch.net December 2001
