December 15, 2015 at 2:32 am
Hi
we have about 1.000.000 pics related to our products in our web site.
Now these Pics are maintained in a file server.
we have some problems to control transactions that change product properties and their pics. more over data seperated in 2 servers and Define a new Products and ...
there is another solution , Transmit these pics to Sqlserver near main data. in FileStream technology.
(sql server 2014 - sp1 - windows server 2008 R2 and Avg size of pics is 50 KB)
Will we achive any benefits in this transmition ?
(How ever we see at amazon web site and we guss that this site maintain product pics on file server. we are not sure about it . )
Thank you
December 19, 2015 at 10:57 pm
:doze:
A best Practice Or GuidLine ?
:crying:
December 21, 2015 at 6:45 am
I think filestream may make it impossible to change the files without doing inside a transaction from SQL.
http://www.sqlservercentral.com/articles/SQL+Server+2008/64088/
December 23, 2015 at 11:40 am
With the exception of secure document storage, I don't see the point in containing BLOBs in a relational database. With what you're suggesting, the website would hit your database whenever it needs to render an image, and also your page buffer cache would be mostly image data rather than product records. You will need to run some tests to see how much of an increase in number of reads this will result in.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 1, 2016 at 9:22 am
Eric M Russell (12/23/2015)
With the exception of secure document storage, I don't see the point in containing BLOBs in a relational database. With what you're suggesting, the website would hit your database whenever it needs to render an image, and also your page buffer cache would be mostly image data rather than product records. You will need to run some tests to see how much of an increase in number of reads this will result in.
FILESTREAM data does not affect the pages in the buffer pool. The data streams straight through from the OS memory. I think of the feature as a way to turn SQL Server into an image-server. Comingling heavy image-serving with demanding data workloads could require additional admin oversight but with properly-sized hardware and the right percentage of memory left for the OS things should work just fine.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 1, 2016 at 9:26 am
MotivateMan1394 (12/15/2015)
Hiwe have about 1.000.000 pics related to our products in our web site.
Now these Pics are maintained in a file server.
we have some problems to control transactions that change product properties and their pics. more over data seperated in 2 servers and Define a new Products and ...
there is another solution , Transmit these pics to Sqlserver near main data. in FileStream technology.
(sql server 2014 - sp1 - windows server 2008 R2 and Avg size of pics is 50 KB)
Will we achive any benefits in this transmition ?
(How ever we see at amazon web site and we guss that this site maintain product pics on file server. we are not sure about it . )
Thank you
For what you want I would recommend you look into FileTable. It is an included feature within SQL Server built atop FILESTREAM that allows you to manage the images in the database as if they were exposed to the network via a Windows Share. Depending on how you need to link the image to other data in your database you may need to engineer some relationship tables but compared to using FILESTREAM directly it greatly lowers the bar to get binary data into your database.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 10, 2016 at 4:25 am
Thank you Orlando
January 11, 2016 at 3:50 am
Depending on how you need to link the image to other data in your database you may need to engineer some relationship tables but compared to using FILESTREAM directly it greatly lowers the bar to get binary data into your database.
Based on your Reply , I have an idea about this :
1- Create Another Database near main DB and enable Filestream on server.
2- With FileTable I would Receive all pictures to tables in new database.
3- I would create some relations tables (not with FK , only conceptual) on main DB which connect Data (From main DB) to Images (in New DB)
4- our developr team create new forms to handle these images in database way, besides current method (work with images on os)
In this way :
1- we would not have increased size on main DB.
2- we have 2 parallel method with least cost to change between them.
January 11, 2016 at 9:28 am
I think you are on a good track. Inwould start out watching your memory usage really closely to make sure you leave enough for the OS to do file streaming and enough for your SQL Server process especially buffer pool.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply