SQL data export to end customer setup ?

Question

SQL data export to end customer setup ?

scott_lotus

SSCommitted

Points: 1504
More actions
March 13, 2009 at 8:04 am

#128574

Hi,
Had a request from a cutomer to allow them to "download" data so they can import locally and create their own reports.
My system runs SQL 2005 Standard 64 in active/passive cluster. I cannot allow these exports to be run on the existing sytem due to the high number of inserts during busy running hours.
The way i see it i have 2 problems:
1. what data source to use
2. what download medium to use
Point 1: the current thinking is SSIS, Replication or mirroring to a new instance of SQL on a new box. At this stage i beleive the customer will only need a small percentage of tables but large amounts of data from these few tables.
>SSIS seems to me that it would provide a smaller set of data. i.e just tables requied.
>Replication my ISP tell me can be difficult to maintain.
>Mirroring would mean a full copy and it may not all be used.
I would expect that once the new database is created it would be "topped up" daily with the days transactions.
The avilability of the reporting DB is not a big worry.
Point 2: once the data source is in place i need to think about the best way to deliver to the end user.
Will probably use a web app to allow customers to select the date range. Will need high timeouts however. Guess it could be created on a scheule and the customer could download a file.
Anyone else have a simaler problem or can suggest a recomended solution ?
Thank you for any advice
Scott

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

I've done something similar, but made sure it was an application that happens to conenct to SQL server, not do it via SQL server itself.

.NET DataSource object has the ability to export data to XML, and import it as well.

In my case, I connect to a SQL database, Load a bunch of datatables into my DataSet, and then use the MyDataset.WriteXml(c:\myPathToTheFile\XMLData.xml)

then i publish that file out to the web. I used to use FTP until I figured out how to download via HTTP. both work just fine.

my application knows exactly where that file is on the web, so if they run the "Download data" function from my app, it downloads that xml file, and calls the MyDataSet.ReadXml(C:\LocalPathWhereitWasCopied\XMLData.xml) to load that into a local dataset.

I can then present anything in the dataset, or use the application to insert that data into their local copy of the database, so they get all the changes based on the PK's of the table.

so it sounds like in your case, you could "publish" XML whenever convenient for you, and a local application could download that XML copy of the data so they can fiddle with it in a report.

Is that kind oif what you are asking, or you really want to keep it all SQL server side?

scott_lotus (3/13/2009)
Hi,
Had a request from a cutomer to allow them to "download" data so they can import locally and create their own reports.
My system runs SQL 2005 Standard 64 in active/passive cluster. I cannot allow these exports to be run on the existing sytem due to the high number of inserts during busy running hours.
The way i see it i have 2 problems:
1. what data source to use
2. what download medium to use
Point 1: the current thinking is SSIS, Replication or mirroring to a new instance of SQL on a new box. At this stage i beleive the customer will only need a small percentage of tables but large amounts of data from these few tables.
>SSIS seems to me that it would provide a smaller set of data. i.e just tables requied.
>Replication my ISP tell me can be difficult to maintain.
>Mirroring would mean a full copy and it may not all be used.
I would expect that once the new database is created it would be "topped up" daily with the days transactions.
The avilability of the reporting DB is not a big worry.
Point 2: once the data source is in place i need to think about the best way to deliver to the end user.
Will probably use a web app to allow customers to select the date range. Will need high timeouts however. Guess it could be created on a scheule and the customer could download a file.
Anyone else have a simaler problem or can suggest a recomended solution ?
Thank you for any advice
Scott

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

scott_lotus SSCommitted Points: 1504 More actions · Answer 2

Thanks for the reply.

I woulnt allow direct connection to SQL 🙂 but thanks for pointing it out the "no no's" 🙂

Possibly allow a web app to managed the seleciton of data, schedule the export to XML dataset (or any file format and on a per customer request bases) during quiet hours, then allow a web app to manage the user download.

When testing a large range i have seen XML files of 75MB which causes our web app to be really sluggish in response.

I cant allow this process in anyway to infulence normal running i.e ether the export or the download.

Interesting idea though, have i understood what you mean correctly ?