ideas on replication 1

[sqlpro]

Dear friends
need some idea on replication.
we have an application in our office MSDE8 as backend and visual foxpro as frontend.now
we want to enable replication.i mean users can take our application on their laptop and do some changes to data in offline mode.when they relogin to our main database server we need to update latest data from laptop.
its a new concept for me
i wonder if u could suggest any article or some sort which get me some idea on this issue.
my initial idea is to install MSDE on every laptop that'll b going offline and copy data from main server to these offline databases.
once they relogin to main server then i need to write a program that transfers newly added data /modified data to main database server.
am i going right way?
thank you so much for ur suggestion.

 

[eramgarden]

I did something like this at work. After much pain, long sleepless nights and finishing the app, big boss decided we dont need it...

Anyway, some issues to consider..
1. are u replacing ALL the main database table info with what's in the "laptop" info? for example, if laptop1 updates a field in a table, laptop2 updates the same field, then would you replace the main database field with which of these data? which one takes precedence?

2. I used a VB app to do this. For each field I wanted to replace, I created a temp table, trigger. That's how i kept track of which fields were changed. I looked at date time to take the most recent change and update the main database.

3. Someone told me to look into "Log Shipping".

4. Look at

http://www.sql-server-performance.com.

I think i saw a software developed for this kind of job.

5. Dont think u can use replication for this. You're taking laptops offline. Then everytime, u need to setup replication on each table, connect to the main table and transfer the data..hmm..dont think it's doable with replciation.

6. I'd say really think this over. Are there risks to the main database tables? In our case, it was soooo painful to get this done. Nightmare!

 

[sqlpro]

Hi friend
Thank u for ur reply.
first of all i've not implemented this replication.
still our boss uses our main database thr interet connection
if he goes out with his laptop.so his laptop does not have
any data on it.
You raised very good issues abt
(if laptop1 updates a field in a table, laptop2 updates the same field, then would you replace the main database field with which of these data? which one takes precedence?)
i still have think of those issues.
i am still initial stage of this process.
i am trying 2 find some sensible solution to make our data availale for offline mode.

 

[mrdenny]

Replication would totaly work for this.

You would need to upgrade your main server from MSDE to full SQL 2000 however. Here is how the setup would work.

Setup Merge Replication on the table that need to be feed to the laptops. Merge replication allows the most flexability with multible subscribers updating data, and pushing that data back to the main server.

Then setup pull subscriptions from the laptops to the Main server. This way everytime the laptops startup they will attempt to sync with the main server.

In the event that two people update the same record, in the same table, the SQL Server will handle this internally, and record the conflict in the conflict tables. You'll see them get created on the main server. The entire thing is very slick and seamless to the users.

I've had other people on tek-tips do this same thing, and it's always worked well.

When you are setting up the publication and the subscriptions you will have a chance to specify the conflict resolution paramaters to deturmin who will override who.

If you have any questions, I'll be happy to help where I can.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[sqlpro]

Thank you very much Denny.you gave me hope
I'll update u with latest info as soon as i start working on replication.
Thanks once again
cheers
rajani

 

[sqlpro]

Dear Denny
I need some clarifications..

1)our fronend application(developed in visual foxpro) uses
a text file to connect 2 sql server.this text file has following values
"server=sun,database=tracker_db"

so when a laptop goes offline what value do i need set for
server in the above text file?

2)do i need 2 install sql server or MSDE on laptop that'll b going offline or how it works?
I just created a merge replication for whole database(i.e tracker_db) and created publisher and subscriptions.ofcourse in this case both are same(i.e server=sun).
now what should i do on our laptop?

you advice greatly appreciated
cheers
rajani

 

[mrdenny]

1. For the connection string change server=sun to server=localhost. That way it will always connect to the local database. That way the user doesn't need to change the connection string every time they switch. When ever the laptop is connected, it will be getting it's updates in near real time from the main server.

2. The laptop can be MSDE. The only reason that the main server needs to be full SQL is that MSDE doesn't support being the publisher of a Merge replication.

One thing that I forgot, when it asks you if there is a schedule or to run continuously, set it to run continuously. That way it will start on boot, and continue to attempt to connect. And if it is connected, it will be sure to keep the data in sync the entire time it's connected.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[sqlpro]

Thanks for that Denny.
now how do i use this merge replication on laptop?
u got my point?i mean
I created Merge replication on main server and what settings
do i need to make on laptop in order to make it work in offline mode?
cheers

 

[mrdenny]

The MSDE service will start and run normally when connected to the network, or disconnected. It doens't really care either way.

Check BOL, there's some pretty good info in there about how to get it all setup.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[eramgarden]

Denny-

wow, i really didnt know it can be done via merge replication. I inherited the leftover VB app from another developer. But our issue was that we didnt want to update the whole row..only columns that were changed by the laptop users.

This is good to know.

 

[mrdenny]

Hey, no problem. Replication is a powerfull tool, that people don't give enough credit (at least according to me). Let me know if you have any more questions, or need any help.


Denny

--Anything is possible. All it takes is a little research. (Me)

 

[borsk]

Hi guys.

Denny it sounds like you have done this before....

I have a problem with this exact scenario.

We have a Windows 2K SQL server and a number for XP Laptops that needs to have MSDE on them.

Then we want the replication set up just as you describe in the beginning of this thread.

But i keep getting back to the same problem.

The MSDE can make the subscription but not replicate, i found 2 different errors depending on the way i try.

Error 1: Pull Subscription Problem - OS Error 5
Error 2: The schema script '\\ServerName\snapshot\unc\ServerName_TestSync_TestSync\20050119164123
could not be propagated to the subscriber.

I have given everyone full rights on the Share and in the file system

Do you know why this happens?

Thanks

Borsk

 

[mrdenny]

What account is running the MSDE databases? A domain account or the local system account?

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[donutman]

Mrdenny, you make it sound very easy. How does merge replication handle each laptop adding new rows to tables with identity values along with child tables that use them as foreign keys?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrdenny]

The replicaiton assigns blocks of numbers to each machine within the publication.

Look up sp_adjustpublisheridentityrange in BOL, then click on "Managing Identity Values" at the bottom. There's a good writeup in there on how it all works.

The simplest way of handling identity ranges across replicas is to allow SQL Server 2000 to manage identity range handling for you. To use automatic identity range handling, you must first enable the feature at the time the publication is created, assign a set of initial Publisher and Subscriber identity range values, and then assign a threshold value that determines when a new identity range is created.

For example, assigning an identity range from 1000 through 2000 to a Publisher, and a range from 2001 through 3000 to an initial Subscriber, works as follows when combined with a threshold value of 80 percent:

Newly inserted Publisher rows are assigned identity values from 1000 through 2000. Newly inserted rows on the initial Subscriber will sequence from 2001 through 3000.


When 80 percent of either the Publisher identity values or the Subscriber identity values are used, a new identity range is created for forthcoming inserts. In this example, if rows from 1001 through 1800 are used on the Publisher, the threshold has been reached. A new identity range, from 3001 through 4000, is created on the Publisher, and the next inserted row at the Publisher is assigned an identity value of 3001. After the Subscriber reaches the threshold (assuming the Subscriber reached threshold after the Publisher), a new identity range is created on the Subscriber, from 4001 through 5000, and the next inserted row at the Subscriber is assigned an identity value of 4001. The process is repeated as identity ranges are used.


As each Subscriber is added, an identity range that is the same size as the initial Subscriber range is added, using the next available starting point.

Because of this, if you plan to have the laptops disconnected for large periods of time, some thought will need to go into the size of these ranges when setting everything up. If a machine runs out of numbers all hell can break loose.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[mrdenny]

What I meant to also include is that:

In order to prevent the laptops from running out of ids they need to connect and replicate with the publisher. This will give them more ids and tell the other machines not to use them.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[donutman]

Wow! I had used replication years ago for my order entry program, but discontinued using it once we got T1s between locations. I never came across that info, wish I had. Instead I used a system whereby the PK was assigned by a Key table that used an identity column with an increment of 100, then the PK was computed to be the identity plus the location number. Each location had it's own Key table to generate the PK. Those tables didn't need to be replicated.
Sort of dumb that I didn't trust that the identity issue hadn't been resolved by the designers of replication. Guess I didn't realize that "Anything is possible. All it takes is a little research."
It doesn't stop there. Because I mistakenly used the PK as a real world value (the invoice number) and the increment was so large, the numbers got into 8 digits. So I abandoned that and have since gone to a system which fills in all of the missing numbers. Now it will take 50 years to reach the max value.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]