Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

A Tool for the Job Expand / Collapse
Author
Message
Posted Monday, November 19, 2012 9:58 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 5:03 AM
Points: 579, Visits: 2,520
Comments posted to this topic are about the item A Tool for the Job


Best wishes,

Phil Factor
Simple Talk
Post #1386674
Posted Tuesday, November 20, 2012 1:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:07 AM
Points: 2,901, Visits: 1,805
Search is one of those things where you'd expect an RDBMS to be much stronger than it actually is. Any form of data repository is only as good as the mechanisms by which you can find and retrieve stuff.

I haven't had time to experiment with SQL2012 Semantic Search yet but certainly up to SQL2008R2 full-text search didn't do quite what I wanted it to do.

The other point is are the products that we think of as RDBMS just RDBMSs?
Yes they retain that core strength but are they now becoming metadata management systems, in which case

I agree with the author on the following

  • Log Analysis

  • Email

  • ACL

  • High Frequency Training - That's why M$ Stream Insight is so compelling



I think the following fall under the "It Depends" category:-

  • Search - Lots of pain with external search and keeping it up-to-date

  • Media Repository - Remote Blob Storage

  • Recommendations - It really depends what you are trying to do

  • Product Catalog - Didn't understand his point



The rest of it I don't have an opinion on.

As a general comment I've had developers say to me "this can't be done in SQL Server" or "SQL Server isn't good at doing 'x'". When I've looked at the particular problems not only can it be done but it can be done well it was just a case that they lacked the knowledge/experience to solve their problem.

One thing you have to watch out for with NoSQL are the hidden costs. Yes they can scale to Jupiter and back but expect to have to scale out much earlier and with the associated costs.

Obviously you can run your systems in the cloud but that decision brings its own complexities.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #1386723
Posted Tuesday, November 20, 2012 2:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 4:51 AM
Points: 1,854, Visits: 3,451
Looking at the list from an SQL Server RDBMS view:

Search: I agree that there are products that are much better than the built in FT-engine in SQL Server, but if your FT needs are simple (simple stemming and ranking) then the built in is good enough. I have played a little bit with Apache Solr and it does have a LOT mure functionality (better stemming, faceting, ranking, typing correction, suggestions etc) than SQL Server FT, but it takes a lot more time to configure (xml files, xml files, xml files). Then there is the issue of keeping an external FT engine in sync with the RDBMS.

Times series/high frequency logging: Definately not a job for a RDBMS. Sure you can get decent perfomance for the actual logging for a small number of tags, but when it comes to reading this data and performing calculations on them, performance goes down the drain. A product like OSIsoft PI can easily store 10, even 100 of thousands of values a second on reasonable hardware. It is designed for this purpose only, and it does it well.

Storing large amouns of unstructede data: Agreed. Not something a RDBMS can do well.

ACL: This is actually a bit funny. We implemented ACL (groups and accounts synced from AD with FIM every five minutes) on our internally developed document management system (running SQL Server 2005) about nine months ago.
The requirements: A user should be able to grant or deny permissions (read, write, read ACL, write ACL) to multiple groups or users on any case, document or file (not a real file, just our internal representation of a file). A document belong to a case, and a file to a revision on a document. Permissions should be inherited from case to document to file if the "inherit permissions" on the document or file is set. Both direct and indirect group memberships should work. The hardest part: Performance from a user view must not be affected, and the ACL inheritance from case to document to file should be done instantly.
An ACL change on a case can propagate to as much as 50.000 documents and a few houndred thousand files. How can this be done without affecting performance (again, from a user view). We didn't want the user to wait until the ACL had propagated to all objects that inherit from this object. Have you ever tried making a change to a root folder with thousands of subfolders and files in Windows? Takes forever.
The solution: We decided to use Service Broker. When ACL on a case is modified we simply send a message with the case id (or document id) to a Service Broker service. The message is processed and document ACLs to be updated are split in to batches of 100 docs. When a batch of document ACLs are modified more messages are send and processed to update ACL on the files. This solved the user performance problem and the "instantly" requirement was good enough. Propagating ACL to 50.000 documents and files only take a few seconds.
Post #1386755
Posted Tuesday, November 20, 2012 3:11 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:07 AM
Points: 1,664, Visits: 1,091
I look at articles like this and must admit I think the author is somewhat full of it. I just wish I had the experience to really assess the NoSQL technologies and how they operate. @David Poole I think you are approaching what he says fairly reasonably - I've tackled the issues on most points in SQL Server and not had too much of a nightmare. Guess my data has just not been big enough *shrug*

Admittedly search is terrible in SQL Server. I've sidestepped that often (not tried 2012 facilities as of yet). But product cataloguing? There's plenty of adequate approaches - from the canonical Celko to my own simpler version(s). High frequency trading, well, I've not been there.
Post #1386779
Posted Tuesday, November 20, 2012 4:42 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 5:03 AM
Points: 579, Visits: 2,520
Search is tough but can be made to work well in an RDBMS. I was once tasked with doing a database that took every major news story from around the world published on the internet and archived them so the text could be displayed on a website. I was asked to provide 'google-style' search, and an automatic feature that listed the most widely covered stories in the day. It was SQL Server 2000 at the time, and the archive was immense. I won't say it was easy, but it worked eventually. With five years of news stories and just under a terabyte of data, it was never taking more than a third of a second. It became easier with SQL Server 2005, of course, and I used the hard-won techniques for several other applications.


Best wishes,

Phil Factor
Simple Talk
Post #1386835
Posted Tuesday, November 20, 2012 7:21 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:17 PM
Points: 1,651, Visits: 4,709
SQL Server has actually had NoSQL data storage and analysis products bundled with it for over a decade. Take for example, Analysis Services. People don't generally think about OLAP products being a NoSQL database engine, but they actually are. Also, Full-Text Search, FileStream, and Common Language Runtime services can maintain huge unstructured data stored external to the relational (mdf, ndf) data files, but are tightly integrated with the RDMS engine and T-SQL language.
If your database is storing huge unstructured chunks of junk in varchar columns, then SQL Server itself has other services bundled with it that can offer a better solutuon, without resorting to a 3rd party product.
Post #1386898
Posted Tuesday, November 20, 2012 7:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 20, 2012 7:35 AM
Points: 1, Visits: 6
So email makes the list. To be fair to author I am in the analysis business and storage or platform beyond baseline criteria are of little interest. My objective is to get information out of data and with email (or other unstructured data) the approach I take is exactly the same as with structured data. I start by drawing the decision trees my customers are going to use to drill into the data. These make it really easy for me to define the 'meta data' or architecture for the data I'm going to warehouse. The final step is coding up the load process which I quite like because I'm working with a two field table. Just in case I've lost anybody here's an example; my users inquiries will be for the most part related to master data; that is to say they will be looking for email with SKU number, shipment, purchase order, contract etc. When I load the data I search for text containing the master data key field and populate results to the index tables. My email is now firmly in the relational space. I understand the "what if" but as long as I monitor search traffic and make sure the vast majority of requests are within my structure I've dodged the pink slip for another day.
Post #1386906
Posted Tuesday, November 20, 2012 7:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:04 AM
Points: 179, Visits: 278
Saying that you are implementing a NoSQL approach really means that you are removing an abstraction layer from your data model. The only way I can think of to describe this is to say that you are instead depending on services provided by the operating system. I agree completely that loading a server log into a database table is not the best way to access it. Notice then that you are going to be using disk file IO.

Of course such services, being lower level, can easily be faster. They will also be more complex and require more time and skill to implement and maintain. They will also be less robust in terms of failures and error reporting. In my world of business software development, such systems are implemented by engineers. When I need such functionality, I look for a packaged product, and I personally would not touch such a system, professionally, with a ten foot pole.
Post #1386924
Posted Tuesday, November 20, 2012 8:41 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
RDBMS or NoSQL is irrelevant when you get the wrong answers
Recommendations e.g. People who bought torches often bought batteries too

we all know they need matches not batteries.


<><
Livin' down on the cube farm. Left, left, then a right.
Post #1386962
Posted Tuesday, November 20, 2012 8:59 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:34 AM
Points: 1,566, Visits: 1,851
Thank you for the interesting topic, and the link to Andrew C. Oliver's article, which had other links I followed. I have almost zero experience in any of these tasks, and have been wondering what other databases are out there for NoSQL solutions. I am looking forward to reading more comments on this thread.
Post #1386982
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse