1

I have a website with admin panel, which people can upload files and data. My website is on 2 servers and use nlb, and also two servers are synced together with dfs, also I have 2 SQL Server on both servers and they are synced too.

The problem is syncing files on both servers take sometimes, Now just imagine I have a table with these fields:

Name, price, fileName

File address is point to somewhere of physical disk which is synced in 2 servers.

Now imagine the website run this query:

Select * from myTable

How can I tell SQL that just show that records which the fileName field exists in physical disk?

Note: I want it to do it with SQL, not in my application.

Improve this question
4
  • See this question stackoverflow.com/questions/11740000/… Commented Feb 10, 2016 at 7:09
  • 1
    "I want it to do it with sql not in my application" - yes, forget about layering and separation of concerns. Your application naturally cares about various different types of resources, whereas your databases should only care about the data. The natural and logical place to perform such checks is in the application. Commented Feb 10, 2016 at 7:11
  • @Damien_The_Unbeliever I have to do it with sql because i want to paging records Commented Feb 10, 2016 at 7:20
  • So, assuming a page size of 10 and you want page 2. Files for rows 11-15 are currently absent so you return rows 16-25. Then, when you want page 3, it turns out that those files have now appeared, and page 3 will contain rows 21-30 (repeating 5 of the rows). Whatever you do is going to be messy here and I'd still suggest it's better solved in the application. Commented Feb 10, 2016 at 7:42

1 Answer 1

Reset to default
2

There are two options:

  1. You already mentioned it. It is a good idea to add a column (FileExists bit (Y/N)) which tells you directly the status of physical copy.

  2. Second option is tedious as you need to create custom logic which identifies the physical copy at runtime as per the first commented link given by @MichałKomorowski.

Ideally a database is used to store data and communicate with the application. Outer world interfaces within a database will be tedious work as well as decrease performance.

For example, if you implement a function to check physical file and in select query as above you want, you used that function, so extra headache to checking the status will definitely increase the time and performance decrease. Just think again.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.