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

adomd.net cache server object

Hi All,

I have a bit of a dilemma regarding how to cache the server object so I don't have to reconnect all the time.

I have a web service which serves AS2005 data, and whenever the cube schema changes (or sometimes just after a process), the server object doesn't return the cubes collection, and I end up having to restart IIS or the app to clear the application obj and start again. Obviously this is not the best. Is there anyway I can detect for schema changes in the server object, and serve up a fresh obj if there has been?

I'm using the following code:

Code Snippet

Dim server As OlapServer.Server = Me.Application.Get(cubeServerStorageKey)
If (server Is Nothing) Then

Dim cubeServer As String = System.Configuration.ConfigurationManager.AppSettings.Get("Analysis Server")
If (String.IsNullOrEmpty(cubeServer)) Then _
cubeServer = "."

Dim cubeServerTimeoutSeconds As String = System.Configuration.ConfigurationManager.AppSettings.Get("Cube Timeout")

server = New OlapServer.Server(cubeServer, Me.RequestData.Database, Int32.Parse(cubeServerTimeoutSeconds))
server.Connect()

Me.Application.Set(cubeServerStorageKey, server)
End If

Return server

hello,

could you please clarify a bit. I believe adomd.net does not have an OlapServer class in it (nor does AMO). So, can you please provide relevant code (i guess of how the OlapServer object returns you the cubes collection, since from the problem description you mention that this is where the problem occurs), because without that code it is hard to tell what the problem might be or how to solve it.

thanks a lot,

|||

Well why would you want to cache such a connection? 100ms routine is hardly worthy of cache. Chaching the return dataset I can see, but connection? Sorry if I misunderstood.

Also, you can use .NET's cache methods to specify an exact timeout for a cached object.