Advice needed - Regarding data transfer between databases on seperate servers

Hello everyone,

Here's my situation...

I'm running a web service which involves 51 seperate servers and databases.

There are fifty licensee servers (One for each US state) and one corporate server.

Each night I need to upload sales and membership data from the licensee's databases to the corporate database to compile reports.

The application platform I'm using is ASP.NET 2.0 and the the database is SQL2005 express.

I want this process to be run automatically, so I believe it's a scheduled windows service I need to setup up in .NET to make the data transfers.

If anyone has already set something up like this, or knows the steps to take? I would love to have your input.

Thanks in advance,

Robert

Did you mean 50 Databases on same server or 50 different servers?

You could set up a DTS package and schedule it to run at a particular time every day. You dont need to do anything with .NET. This can be done completely at the backend.

|||

ndinakar:

Did you mean 50 Databases on same server or 50 different servers?

You could set up a DTS package and schedule it to run at a particular time every day. You dont need to do anything with .NET. This can be done completely at the backend.

This is correct, note that SQLAgent service has to be up & running inorder to have your DTS (in SQL Server 2000) or SSIS (in SQL Server 2005) packages executed on time.

If this service was not started (stopped), any scheduled tasks in SQL Server will not work.

Conclusion: Make sure this SQLAgent service is always up & running in all your database servers (check them after batching your OS or restarting it).

One of the bad thing about this way is, you can not generate reports at anytime .. you have to wait (say till next day) in order to see the reports for all states.

How to overcome this is by using replication (for example), by replicating the data you need from source tables (in diffrent servers) into ONE server (e.g. main branch) so you can generate the reports from all 50 data sources at any time and updated;

Just a suggestion:
If the amount of data is not that much, you can export the data (say in Excel format) then send them by email to your main branch.
This might save some traffic (bandwidth) for you, but on the another hand you need to imported to SQL Server before generating the reports.
One more good thing about this way is, you will always have a copy in your email for all 50 databases.

There are many ways to do this, actullay depends on the case and how important to get the report on anytime and how large the data is.

Good luck.

|||

Hi Guys,

Thanks for the responses!

Nethi - Yes thats right - There will be 50 seperate licensee databases, which run on 50 seperate licensee servers. Each night I need to retrieve data from each of 50 seperate databases and upload it to a database on the corporate server to run reports the next day. (51 seperate servers/databases involved)

The sales data I'll be uploading nightly will be minimal, because i'll only be capturing sales that were made that day (12 am till 11:59:59 pm the licensee's server time) The membership data will be a larger file, because I need to upload all records every night, which will overwrite the prior membership data records. So in this case there will be many rows, but the actual data being uploaded from each row is minimal.

Tell me - Using DTS...

Can I fully specify what data needs to be uploaded? (Conditions such as record creation time, etc)
The servers may be in different time zones - Would this cause problems for DTS regarding capture periods?
Using DTS, will the uploads be secure?
If a scheduled data transfer fails for some reason or another, does DTS have a feature to report the failure, and what would be involved in capturing the data that wasn't transfered as scheduled? Is it complicated, difficult and time consuming?

Thanks again for your input and advice

Robert

|||

>>>Can I fully specify what data needs to be uploaded? (Conditions such as record creation time, etc)

You can use a mix of script in DTS - VB Script/T-SQL. You can call stored procedures. Google for DTS and read up some articles to get some info.

>>>The servers may be in different time zones - Would this cause problems for DTS regarding capture periods?

The DTS will run from the server its created on, on the schedule its scheduled to run. You can schedule it according to souce server timezone or target server timezone.

>>>Using DTS, will the uploads be secure?

DTS will use either Windows Authentication or SQL authentication. You can set up either way.

>>>If a scheduled data transfer fails for some reason or another, does DTS have a feature to report the failure, and what would be involved in capturing the data that wasn't transfered as scheduled? Is it complicated, difficult and time consuming?

You call the DTS package from a scheduled job. The job has additional options of reporting - email/pager and different options for success/failure.

|||

As much as I know Sql Server 2005 Express does not support DTS.

sql