April 13, 2004 at 8:05 pm
Hello,
We have a need to produce data as real time as possible. This will be a sports/stats application. Basically as stats change in game, this data will be sent to us from an external source in near real time and we are to import the data into the database as quickly as possible. How the data will be sent to us and in what format has not been defined yet. We are using SQL 2000 Enterprise and Windows 2000 Advanced Server. Replication is not an option for this solution.
In the past we have had the need to produce data that could be behind by a few minutes. In this case, files were FTP'd to us and I created DTS packages that were scheduled to look up on that directory every one minute and import those files into the database if they were there. This was adequate as there was no requirement for real time data.
Now that we have this real time requirement, I'm wondering what my options are, what the most optimal and scalable solutions are from the database perspective, and how this is typically implemented.
If anyone has any experience with real time data feeds, any input or advice would be very much appreciated. We have programmers here that can likely come up with a solution, I would just like to get some feedback and ideas on this from some people who have done this before or are currently managing this type of solution.
Let me know if more info is needed.
Thanks,
Greg
April 13, 2004 at 11:06 pm
You really need an external process that can accept the files and move them into SQL Server. It could run a DTS package, bulk import, simple insert, whatever.
As far as the app is concerned. Haven't done anything this real time, but have setup secondary servers that function as downstream replicas for the non realtime stuff. Despite there being some people with realtime needs, lots of people will accept a few minutes or older and replication can help spread the load.
April 15, 2004 at 6:19 am
Depending on the speed at which transactions are coming in, and whether they are being logged (bulk inserts are not logged by default), a trigger can be used to "summarize" incoming data to a separate table.
One warning, if the trigger is complex, and the transactions are coming in fast and furious, SQL Server can back up causing the application to crash.
We do several things to make data available:
1. We have a master database server that uses MERGE REPLICATION to two onsite servers, and two offsite servers. The master server is responsible for incoming transactions only.
2. One of the onsite servers handles customer service functions. It receives data in near real-time with merge replication and continuous updates both ways to the master. The other onsite server handles batch processes such as bulk acknowledgement e-mails to customers and nightly processes. Both servers replicate back to the master with conflicts resolved nicely.
3. The offsite servers serve the same purpose as the onsite servers, but are used as hot-backup servers. They receive data from the onsite servers using MERGE replication. The nice thing here is that if we must, we can use these servers for "load balancing" (not in the SQL Server or Win2K sense) for other clients - in other words - some clients use onsite servers, one or two (for now) actually use the offsite servers.
The beauty of this is that all servers are synchronized with MERGE replication, the data is practically real time (less than 5 minutes). The only drawback? The link between here and the offsite servers goes down or one of the remote servers goes down (hasn't happened yet, but "Murphy" lurks always)... in this case, Replication will go into retry mode and will "pick up" where it left off when the connection is re-established. In one case, when retries timed out (after 10 attempts, 5 minutes between each), the agent had to be restarted, and it of course, picked up where it left off.
All in all - it's been a wonderful solution that of course is being tweaked as needed.
-- Joe
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply