MS SQL Server expert needed

[Drizzt]

Anyone here?
I'm having huge problem with my server, and I need some help...
(and the problem is so complicated that I don't want to spend the entire morning typing it if no one can help me )

[Marshmallowman]

I am not an "expert", but have been doing a fair bit of ms SQL stuff lately.

Whats kind of problem do you have?

[schmosef]

Ditto for me.

I spend about 40% of my time doing TSQL programming. I shy away from calling myself an expert though because I know that there's advanced indexing and other tricks I don't use.

I'll help if I can. What's up?

[Drizzt]

Ok, this is the problem:

I've got an on-line SQL Server on wich resides a database.
I've got 25 offices, each one with his own copy of the main database. They work on the local database, and at the evening they export the differences to the on-line DB. This, via a procedure I wrote.
The procedure is a set of commands (a DTS, for who knows) that reside on the on-line server: the office that need to export needs first to d'load the export procedure from it (d'load of the package is automatic)




Problem is, often during night the DB's datas change by themselves!!!




So, to make it short, I've ended for inserting a few things in the DB to help me solve the problem:

1) In the most important tabel I've inserted a field "ExportedBy" that record who has exported that record.

2) I've put in the export procedure a default vale, "XX", for the ExportedBy field.
As the real office ID is inserted only from the local software during the exporting procedure, I can understand if there is someone who is doing something dirty: simply, I check for record exported by XX.

3) Each record has a field, DateExported, that in a totally automatic way registers the exporting date/moment for that record. And, there's no way to alter that field.

4) Each time an Office exports an automatism records it onto a tabel, recording the Office ID and the moment when the exporting begun. Again, the normal office cannot override this behaviour.



Well:
Records kept changing, and I didn't find a way to stop this. After a bit of further investigating, I've decided to however insert something to at least save offices from having their own database ruined (other than exporting, the offices in the morning import the new records inserted on the server by the other offices...)

The thing is
5) An office can only export record who owns.
Each record is marked with the office ID in a field, OfficeID, to know who owns that record. The exporting procedure only export those record that have the OfficeID field equal to the one of the office who is exporting.
This way, no one can modify my records and thus I'm not going to re-import them corrupted.




This is the system beahviour.
Now, what happened?
Yesterday I was looking at the DB status, just to do a bit of checking, and found that:

1) The 27/08/2004, starting at 6:00 AM (when they are closed), ALL the offices at the SAME time was exporting records into the main table.

2) None of this offices was logged to the table that record the exporting operations. (so, how can I know they where exporting? Simply, with the DateExported and ExportedBy fields in the main table)

3) Each "office" was exporting records that couldn't export, as they where marked as owned from others offices.

[schmosef]

From your explanation, I'm not entirely clear where the problem is. Is the local data being corrupted? Or just not copied properly to the central DB?

Some general thoughts...

Maybe there's something wrong with your dts package, which downloads the latest version of your stored procedures. I've seen this myself sometimes when I’ve created a DTS package with the wizard and forgotten to "uncheck" the option to automatically included related tables in the copy process. Actually, come to think of it, I've accidentally overwritten an entire week's worth of customer payroll info with that mistake! Good thing we had a backup.

Also, based on your last sentence, it sounds like your remote DBs are forgetting their Office ID. I normally use a parameter table for local settings. How are you indicating to each database what its ID is?

If I was in your position, I'd want more precise info on what was going on at each site. I would create a local "log" table that can be used to insert records to indicate process status and parameter values. Then add insert statements into all my stored procedures to track what is going on throughout. That will be your best way to find out exactly what's happening.

Finally, I'd suggest looking to MS's documentation on Merge Replication for SQL 2000. Maybe there's a different strategy you can use for this?

We've recently done something similar in concept using SQL, ASP.Net, and VB.Net. The system sends encrypted factory production data from multiple cities to a central web service on the internet. The web service inspects the incoming data, accepts or rejects it, and appends it as appropriate to a central database, which my customer uses with their efficiency analysis and reporting software. The data comes from bar code scanners located throughout their facilities and is stored locally at each factory in a MSDE database. Every ten minutes, it attempts to send all new data to the central web service. If it cannot make an internet connection or if the data is rejected, it queues the info until it can try again.

The scanners are all tied to a central PC at each factory. We wrote a windows service in VB.Net to listen for incoming com port activity, inspect the incoming data against some basic rules, and append the data to an MSDE database. We wrote another VB.Net service to run every 10 minutes to push the data over the internet to the web service. And the web service runs under ASP.Net and was written in VB.Net.

It all works really well.

[Dilitante1]

why not use remote access (terminal server/citrix) to access ONE DB on one server, then there is no replication issues to deal with. I know there is bandwidth/connection issues for isolated locations, but most do have some sort of connection that would allow a satisfactory connection, and licencing would be cheaper as well in the long run

my $0.02 worth

[schmosef]

I can't speak for issues in Italy but the problem with that here is that general internet access isn't mission critical and would go down too often to be considered reliable. Site independance is a good thing if it can be maintained properly.

[Marshmallowman]

We have done a few the "terminal services" way, and they seem to work well.

All I can think of when copying/exporting the database is make sure that any apps/services using the db are stopped.

Maybe backing up the db and transfering then merge at the other site?.

Using temrinal services cetainly makes these kind of issues disappear.

[Kurt]

Makes me think there's a not so obvious bug in the export procedure.

You should check what logs you have and see if there's a pattern in how/when the records change (and shouldn't), then refuse the invalid ones.

http://vyaskn.tripod.com/sql_server_dts_best_practices.htm

[Drizzt]

Thx all, the problems seems solved now.

@Kurt: exactly what you said, a not so obvious bug in the export procedure. One of the SQL query was updating the Exporting date of too much records.


@Dilitante1: there's a huge difference on working over a 100mbit network or a 5-10 kbyte/s line...
Because, yes, I have fast DSL lines here in Italy...but do you forget that you need a 25 times faster line for the server?

For an office to be able to work, It needs at last a 10mbit. That means about 300-500 €/month.
So, the server should need AT LEAST a 150mbit/s output, that's somehow not cheap



@Marshmallowman: Each database is at least 250 MByte. And each office should send and receive, so it's about 500 MByte a day...

[Kurt]

That's a lot of data. Is it because the records are many (many fileds too?) or because you have multimedia content (pics, audio, whatever)?

[Drizzt]

That's not easy to explain.
Let's say that even if the fields and tables are not that much, the clients move a lot of datas 'cause the work is basically search and browse over the database, with each office having from 2 to 5 PCs doing it all the day.
Plus, there are a lot of photos. But, even without them, it's not a good way to work.

[Kurt]

The records that are marked for export, are they really changed? Or are they new records?

Couldn't you use a sort of "base catalog" (like an inventory) where you'd keep model records and add new fields that'd only reflect the changes to be made to the model?