Advice on a database Replication

Hi,

I.m trying to find the best practice for my solution.

This is the situation.

My application has two databases and 2 client interface. One with a windows forms having a database (sql server 2000) running in an office (on a static IP but on a slow connection). the second one is an web application running on the net with an online database. Both these databases have to have same information. Users can add records to both databases independently but these databases should be synchronize at a point. I hope the situation is cleare. Is there a standard way of doing this. If some one knows good article of how this can be done pls forward it to me.

Many thanks.

-VJ

You can look at Merge Replication, it can handle changes at both ends, as well as handle conflicts if necessary. How many changes per sec or minute do you expect at both ends?|||

Hi Greg,

Yes I was reading on merge replication. there can be about 200 -300 changes on the office side per day. And only about 50 changes from the web side. My only worry is the slow connection from the office end but it can run over night.

By the way whne u have created a replication can reverse it back as I created a replication and I need to change the tables. It's not very easy to do that while having the replication.

Thank you.

-VJ

|||

What is considered a "slow connection", dial-up? With such a small workload, you can schedule your syncs several times a day if you need the changes sooner.

In sql 2000, schema changes can be handled only by sp_addreplcolumn and sp_dropreplcolumn. In sql 2005, this has been expanded to include the actual ALTER TABLE command as well as several other regular TSQL commands.

|||

Hi Greg,

The slow connection is a ISDN and the speed is 64Kbps upload and 128Kbps download. The changes will only be adding and modifications to records and it's all text data. Hope this connection can handle it.

Cheers,

VJ

|||if it's a reliable connection, it should handle it fine. If you expect line drops, then the merge agent retries will cause overall duration to increase.|||

One more quick thing. if the connection drops will the server drops the data or will it rollback and run the synchronization again. This is the first time I'm doing this

Cheers,

-VJ

|||merge agent will retry the data it failed, or retry from its last savepoint.|||I had implemented with more than 10,000 record sync daily on merge replication running on a very bad dsl connection that has frequent bad packet, but it work well, as long as the config. on the replication suit the environments.|||

Hi,

Sounds good. But what exactly did u implied by the config suits the environment,

Thanks,

-VJ

|||U have to config merge agent profile to suit your current environment.For example, the size of the batch to sync and others.sql

Advice on a database Replication

Hi,

I.m trying to find the best practice for my solution.

This is the situation.

My application has two databases and 2 client interface. One with a windows forms having a database (sql server 2000) running in an office (on a static IP but on a slow connection). the second one is an web application running on the net with an online database. Both these databases have to have same information. Users can add records to both databases independently but these databases should be synchronize at a point. I hope the situation is cleare. Is there a standard way of doing this. If some one knows good article of how this can be done pls forward it to me.

Many thanks.

-VJ

You can look at Merge Replication, it can handle changes at both ends, as well as handle conflicts if necessary. How many changes per sec or minute do you expect at both ends?|||

Hi Greg,

Yes I was reading on merge replication. there can be about 200 -300 changes on the office side per day. And only about 50 changes from the web side. My only worry is the slow connection from the office end but it can run over night.

By the way whne u have created a replication can reverse it back as I created a replication and I need to change the tables. It's not very easy to do that while having the replication.

Thank you.

-VJ

|||

What is considered a "slow connection", dial-up? With such a small workload, you can schedule your syncs several times a day if you need the changes sooner.

In sql 2000, schema changes can be handled only by sp_addreplcolumn and sp_dropreplcolumn. In sql 2005, this has been expanded to include the actual ALTER TABLE command as well as several other regular TSQL commands.

|||

Hi Greg,

The slow connection is a ISDN and the speed is 64Kbps upload and 128Kbps download. The changes will only be adding and modifications to records and it's all text data. Hope this connection can handle it.

Cheers,

VJ

|||if it's a reliable connection, it should handle it fine. If you expect line drops, then the merge agent retries will cause overall duration to increase.|||

One more quick thing. if the connection drops will the server drops the data or will it rollback and run the synchronization again. This is the first time I'm doing this

Cheers,

-VJ

|||merge agent will retry the data it failed, or retry from its last savepoint.|||I had implemented with more than 10,000 record sync daily on merge replication running on a very bad dsl connection that has frequent bad packet, but it work well, as long as the config. on the replication suit the environments.|||

Hi,

Sounds good. But what exactly did u implied by the config suits the environment,

Thanks,

-VJ

|||U have to config merge agent profile to suit your current environment.For example, the size of the batch to sync and others.

Advanced SQL tutorials in Problem/Answer format

I'd like some challenging practice with SQL; general SQL-92 or MS specific (sql server or access).
There's a million sources on the Web, but most are like reference manuals. I already have MSDN with my VS 6.0 and .Net, as well as help files for 3 different versions of Access. I'm looking for something set up more like assignments or challenges. I found a couple sites like this:
SqlZoo.net and SQLCourse.com

SqlZoo has too many mistakes and vague questions. SQLCourse is pretty good, but no advanced stuff (some multiple Joins, subqueries, calculated fields and such). I like the idea of having the same related tables that you keep solving different problems with. This is more like a real-world situation. I would think that there would be a lot of question/answer tutorials for the MS supplied sample db's (Northwind, Pubs, etc.), but I haven't found any.

These forums are full of good questions posed by people, but they always concern unfamiliar data sources and I have to slog through the posts with my slow dial-up connection. I would rather do one big download and work at the problems offline.
Any suggestions?if you want the same related tables that you keep solving different problems with, your best bet is to search for the more common databases like northwind and sakila, perhaps by throwing several of their tables names into a search

if you want advanced question/answers, i humbly offer my own (the actual articles are on the techtarget.com site)

see http://r937.com/sqlate.cfm|||Thanks. There's a lot of good questions in one spot; I can easily download a bunch of those pages. Since the questions are all at the top of the pages, I can read them without seeing the answers. You helped save me a lot of searching time. If I do find some Northwind-based question tutes, I'll post the link.
Thanks again.|||Ahh, I find I learn more than enough from here :p
Just subscribe to SQL/Access threads that you are interested in.
I've knocked up a test database purely for solving problems on here -
trying to recreate peoples problems and see if I can solve them.

It's often a really good way to learn when you're given an unfamiliar problem, because you try and break it down into a generic solution (which means you can use it again later!).

There's my 2 cents. :cool: