Best way to transfer data from SQL 2005 in real time.

Question

Post reply

Best way to transfer data from SQL 2005 in real time.

Bruce Li

Old Hand

Points: 384
More actions
February 4, 2008 at 4:56 am

#181624

Hi,
I don't know if this is where I should be posting this. We are trying to design an architect to allow about 2000 clients running windows forms application to retrieve data securely from our sql2005 server. The application will be getting scorecards, leaderboards, player profiles in real time from the database? I am hoping to be using a disconnected environment and new data should be pushed to the clients only as required.
Anyone have any suggestions on how I should go about this? I am looking for low transfer time, quick updates and hopefully be able to work without problem on a 10Mbit connection. I am thinking of using DotNET remoting with some kind of caching and compression but still researching.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 1

If you're looking at high performance distributed .NET apps, have you checked out Rocky Lhotka's CSLA.NET framework?

http://www.lhotka.net/cslanet/Default.aspx

The framework code is free, but the books describing how to use the framework are not...

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

Pushing data is hard, especially disconnected. I think you'd want to pull data when you're connected and then have it available when you're not. be sure you're tracking times here, so the user knows the "age" of the data.

Secure transmission is more a transport item. connect securely to SQL Server, and you'll have secure transfer. If you want encryption of the data, be sure you need to encrypt it and you're not just doing it blanketly. It's quite a load.

I'd look at some of the SQL CE stuff, not necessarily to use that version of SQL Server, though it might work well for you, but more because those apps are not always connected.

Bruce Li Old Hand Points: 384 More actions · Answer 3

Thanks Jeremy and Steve.

I have a look into it.

We actually using SQL CE to capture the score from live Golf tournaments and using RDA to synchronize that to our SQL 2005 over the internet space. I will look into using SQL CE for the client desktop application DB if it allows quick synchronisation in a disconnected environment.

Cheers

Smendle Hall of Fame Points: 3969 More actions · Answer 4

Since your using SQL 2005 CE why not use Merge Replication?

Bruce Li Old Hand Points: 384 More actions · Answer 5

We are using merge replication with RDA over IIS. It is a bit slow. I was wondering if this is because we are doing replication via IIS instead of direct replication. Can we do direct replication or some kind of replication with compression over the internet without going through IIS?

Smendle Hall of Fame Points: 3969 More actions · Answer 6

Without knowing more about how your clients access data I will assume(bad I know ;)) that your clients only need to read data, if this is true then you want to set up peer-to- peer replication. You can also use peer-to-peer if you can maintain data stewardship who changes what data in a database replicated to many SQL servers) between all clients.

Peer to Peer Replication via MSDN:

Peer-to-Peer replication is new in SQL Server 2005, and it propagates changes made to any copy of the data to all other copies. Peer-to-Peer replication does not provide conflict resolution, and therefore it is recommended only in configurations where only one copy of a given data element is updated. For example, if Peer-to-Peer replication is used to maintain the inventory of a chain of food stores, only the store that owns an inventory entry would be allowed to update that entry. This means that, even though all stores would be able to see the inventory of every other store, they are only able to change the inventory of their store.

The rules that allow only the owner of a data item to update that data item are known as data stewardship. Stewardship is a very useful way to avoid data update conflicts. In situations where data stewardship isn't practical, merge replication can be used to handle conflicts. Merge replication imposes more overhead than Peer-to-Peer replication, because it must deal with conflicts; therefore, Peer-to-Peer replications is more desirable if conflicts can be avoided. Peer-to-Peer replication requires that replication be set up from every copy of the database to every other copy of the database, so that managing this can be a burden when many databases are involved. A single master copy that uses transactional replication to keep the other copies up-to-date is the simplest and most efficient solution if the updates to the database can be limited to a single copy of the database.