sql server - Sql Database analyse, where should files go? -


i'm analyzing system has routine accounting actions , data plus customer relation management (crm). in crm part of system record customers' calls , save them somewhere, may save customers' pictures, logos, signatures, scanned documents , on. should deal wide range of files (sound, image, pdf, word documents, etc ...)

i need deciding keep files.

in old system kept files on hard disk drive space , saved path database, , @ time of need open file using address. think (correct me if i'm wrong) not solution keep files on hdd because :

  1. we lose data integrity. file names may changed (renamed, moved, deleted, overwritten) reason, resulting wrong path in database.

  2. moving whole data (moving server) time consuming process, let's have 1,000,000 files totally reaching 20 gb. if want move 1 million files 1 computer another, assuming pc tolerates , not burn, take long time move files (i/o time copying lot of small files more copying big file) moving single file of 20 gb data (the database file) faster.

  3. it's easier backup data in database compared copying files. using full backup , differential backup can backup right portion of data @ once, , can make regular schedule us.

and maybe other reasons ...

here questions go.

  • does storing files in db , growing db infect on regular db actions? selects, updates, querying tables , on. mean if store files (crm data) in same database (as accounting data) accounting system slower?

  • where should keep files? in normal table? or should separate database 2 files? 1 typical data , 1 files?

  • does sql server 2012 have space limit? if file part of db grows , example reaches 500 gb (assuming disk drive has enough space) sql server handle it?

  • what cons may there using database storing files? talked pros, , there may cons. if there is, they?

there's paper microsoft research called to blob or not blob.

their conclusion after large number of performance tests , analysis this:

  • if pictures or document typically below 256k in size, storing them in database varbinary column more efficient

  • if pictures or document typically on 1 mb in size, storing them in filesystem more efficient (and sql server 2008's filestream attribute, they're still under transactional control , part of database)

  • in between two, it's bit of toss-up depending on use

if decide put pictures sql server table, recommend using separate table storing pictures - not store employee foto in employee table - keep them in separate table. way, employee table can stay lean , mean , efficient, assuming don't need select employee foto, too, part of queries.

for filegroups, check out files , filegroup architecture intro. basically, either create database separate filegroup large data structures right beginning, or add additional filegroup later. let's call "large_data".

now, whenever have new table create needs store varchar(max) or varbinary(max) columns, can specify file group large data:

 create table dbo.yourtable      (....... define fields here ......)      on data                   -- basic "data" filegroup regular data      textimage_on large_data   -- filegroup large chunks of data

check out msdn intro on filegroups, , play around it!


Comments

Post a Comment

Popular posts from this blog

apache - Remove .php and add trailing slash in url using htaccess not loading css -

i have add following code htaccess file removing .php extension , add trailing slash urls. worked fine reduced page loading speed , css code not loading @ all. pages ugly. please help rewriteengine on rewritebase / ## hide .php extension snippet # externally redirect /dir/foo.php /dir/foo rewritecond %{the_request} ^[a-z]{3,}\s([^.]+)\.php [nc] rewriterule ^ %1/ [r,l] # add trailing slash rewritecond %{request_filename} !-f rewritecond %{request_uri} !/$ rewriterule . %{request_uri}/ [l,r=301] # internally forward /dir/foo /dir/foo.php rewritecond %{request_filename} !-d rewritecond %{request_filename}.php -f rewriterule ^(.*?)/?$ $1.php [l] we can rewrite urls using .htaccess files. before writing in .htaccess files need verify kind of server using. can check server adding a <?php phpinfo(); ?> in php page. ensure mod_rewrite module enabled in apache server. can identified checking phpinfo included page. common methods used url rewriting follows ...
Read more

javascript - jQuery show full size image on click -

pretty self explanatory not workking . i want display full image when user click on image... yes know there tons of plugins but i want keep simple. any idea ? css : .full { display: none; width: 500px; height: 500px; margin-left: 100px; margin-top: -300px; position: fixed; background-color: #f00; z-index: 6; } .thumbnail { width: 50px; height: 50px; } html: <img class="thumbnail" src="http://4.bp.blogspot.com/-uhbdp2esxng/txneptvi92i/aaaaaaaaaky/upfqis7zto0/s1600/rihanna-wallpaper.jpg"> jquery: $(document).ready(function() { $('.thumbnail').on('click', function() { var img = $('<img />',{src:this.src,'class':'full'}); $('.full').html(img).show(); console.log('done!'); }); }); try looking how jquery works. $(document).ready(function() { $('.thumbnail').on('click', function(...
Read more