找到你要的答案

Q:Scheduled, long running user queries

Q:排定的,长时间运行的用户查询

I need some suggestions from the community to a requirement I have. Below is the requirement and I need some approach suggestions.

Users from the client need to retrieve data from my source database (Let say SQL database in my production server). The users access the data by a intermediary service layer (WCF Rest service). On another server (Info Server) I have a SQL Database (Info DB) which will hold all queries that can be requested. Since in some cases my data is huge, I give the option to user to schedule the data retrieval and look at the data later. The schedule information per user would also be stored in the Info DB. I also allow user to retrieve data real time in case he wants.

In both cases I want to Query data from Source (Production DB), store them in file format (May be CSV or excel) and then when user wants the data I would send the data over to the client.

Since the queries are stored in InfoDB. I let the admin define schedule run time for the every Query. This is to enable Admin to adjust long running queries run at night time when calls to server is low. In case the user demands a query to be run at real time, I would allow that.

As a solution architecture I have thought of this :

  1. I will have a WCF rest service which would be installed on Info Server. This service will act as calling point for the Users. When user calls a query real time, the service will get the results, save to a file format and transfer it over. If the user schedules the query, the service will add an entry for the user/ for the query in the info database.
  2. I will have a Windows Service on the Info Server. This Windows Service will periodically check the Info DB for Scheduled Queries entries and if the queries fall within the scheduled time , it will start running the query, get the data and save that to a file location and add the file location entry to the Schedule entry. This will enable me to track which schedules are finished and where the data is available (File path).

Now here are my issues with this:

  1. My data can be huge, will a WCF rest service be good enough to transfer large files over the wire ? Can I transfer files over wire or I need to transfer data as JSON ? What is the best approach.
  2. If I use a windows service, is this a good approach or is there a better alternative ? The reason I am asking is because as per my understanding Windows Service will have to run always , because I need to figure out the entries which are scheduled. This means at specific interval the Windows Service would check the info database and see if the schedule entry should be run or not. In ideal scenario the windows service will run through out the day and check the database periodically without much action because preferably all schedules would be at night time.
  3. I have used an intermediary service approach because if I need to move to cloud tomorrow, I can easily move this solution. Am I right in my assumption ?
  4. If I move to cloud tomorrow, would I be bale to encrypt the data transfer (may be data encryption or file encryption). I have no idea on data encryption/decryption.

Need your suggestion(s) to this.

我需要一些来自社会的建议。以下是要求,我需要一些方法建议。

从客户端的用户需要从源数据库中检索数据(让我说我在生产服务器的SQL数据库)。用户访问的数据通过一个中介服务层(WCF REST服务)。在另一个服务器(服务器)我有一个SQL数据库(信息数据库),将所有的查询,可以要求。由于在某些情况下,我的数据是巨大的,我给用户的选项来安排数据检索和以后看数据。每个用户的日程信息也将存储在信息数据库中。我还允许用户实时检索数据,以防他想要。

在这两种情况下,我想从源查询数据(产品数据库),将它们存储在文件格式(可以是CSV或excel),那么当用户想要的数据,我将数据发送到客户端。

由于查询存储在infodb。我让管理员为每个查询定义进度运行时间。这是为了使管理员能够调整在服务器调用时在夜间运行的长运行查询。如果用户要求实时运行查询,我将允许。

作为一个解决方案架构我想到了这个:

  1. I will have a WCF rest service which would be installed on Info Server. This service will act as calling point for the Users. When user calls a query real time, the service will get the results, save to a file format and transfer it over. If the user schedules the query, the service will add an entry for the user/ for the query in the info database.
  2. I will have a Windows Service on the Info Server. This Windows Service will periodically check the Info DB for Scheduled Queries entries and if the queries fall within the scheduled time , it will start running the query, get the data and save that to a file location and add the file location entry to the Schedule entry. This will enable me to track which schedules are finished and where the data is available (File path).

下面是我的问题:

  1. My data can be huge, will a WCF rest service be good enough to transfer large files over the wire ? Can I transfer files over wire or I need to transfer data as JSON ? What is the best approach.
  2. 如果我使用Windows服务,这是一个好办法还是有更好的选择?我之所以这么问是因为按照我的理解,Windows服务必须一直运行,因为我需要找出预定的条目。这意味着在特定的时间间隔Windows服务将检查信息数据库,看看是否应该运行的时间表条目。在理想的情况下,Windows服务将运行一天,并定期检查数据库没有太多的动作,因为最好所有的时间表将在夜间。
  3. 我使用中介服务的方法,因为如果我需要移动到云明天,我可以很容易地移动这个解决方案。我的假设是正确的吗?
  4. 如果我移动到云明天,我会加密数据传输包(可能是数据加密或文件加密)。我不知道数据加密/解密。

需要你的建议。

answer1: 回答1:

My data can be huge, will a WCF rest service be good enough to transfer large files over the wire ? Can I transfer files over wire or I need to transfer data as JSON ? What is the best approach.

When you say huge, how huge? Are we talking gigabytes, megabytes, or kilobytes. I regularly have 100mb rest responses (you will probably have to tweak some things, to increase your MaxMessageLength, but this should be enough to get you going. I would take their advice and use a streaming API though, especially if you are talking several megs of content.

If I use a windows service, is this a good approach or is there a better alternative ? The reason I am asking is because as per my understanding Windows Service will have to run always , because I need to figure out the entries which are scheduled. This means at specific interval the Windows Service would check the info database and see if the schedule entry should be run or not. In ideal scenario the windows service will run through out the day and check the database periodically without much action because preferably all schedules would be at night time.

Beware writing your own scheduler. You might be better off dropping things onto a queue for processing, then just firing up workers at the appropriate time. That way you can just invoke the worker directly for your realtime call. Plus you can run it whenever the database is idle, not on a scheduled basis. It's tricky "knowing" when a service will be idle. Especially in a world of round-the-clock users.

I have used an intermediary service approach because if I need to move to cloud tomorrow, I can easily move this solution. Am I right in my assumption ?

Yes, wrapping an endpoint in a rest service (WCF) will make moving to the cloud much easier.

If I move to cloud tomorrow, would I be bale to encrypt the data transfer (may be data encryption or file encryption). I have no idea on data encryption/decryption.

HTTPS is your friend here. Read this. Don't invent your own here, or use proprietary encryption. HTTPS is old, straightforward and good.

My data can be huge, will a WCF rest service be good enough to transfer large files over the wire ? Can I transfer files over wire or I need to transfer data as JSON ? What is the best approach.

当你说巨大,有多大?我们说的是字节,兆字节或字节。我经常有100mb休息的反应(你可能需要调整一些东西,来增加你的maxmessagelength,但这应该足以让你去。我会接受他们的建议和使用API,特别是如果你是在几兆字节的内容。

如果我使用Windows服务,这是一个好办法还是有更好的选择?我之所以这么问是因为按照我的理解,Windows服务必须一直运行,因为我需要找出预定的条目。这意味着在特定的时间间隔Windows服务将检查信息数据库,看看是否应该运行的时间表条目。在理想的情况下,Windows服务将运行一天,并定期检查数据库没有太多的动作,因为最好所有的时间表将在夜间。

小心写自己的调度程序。你最好把事情扔到队列上去处理,然后在适当的时候解雇工人。这样你就可以直接调用你的实时调用工人。此外,您可以运行它时,数据库是空闲的,而不是在预定的基础上。当服务空闲时,“知道”是很棘手的。尤其是在一个24小时用户的世界里。

我使用中介服务的方法,因为如果我需要移动到云明天,我可以很容易地移动这个解决方案。我的假设是正确的吗?

是的,封装在一个休息服务端点(WCF)将移动到云更容易。

如果我移动到云明天,我会加密数据传输包(可能是数据加密或文件加密)。我不知道数据加密/解密。

HTTPS是你的朋友。读这。不要在这里发明你自己,或者使用专有的加密。HTTPS是旧的,简单的,好的。

c#  wcf  encryption  service  architecture