Microsoft SQL Server: The Database Behind SharePoint

Published by on February 19th, 2012

… THE NEXT BYTE

A high-level overview of what databases are, and how SQL Server fits the mold.

SQL-Server-2012What is a database?
Why do we need one?
How does SQL Server fit the mold?

In my last blog, I outlined the series of discussions I want to have about SharePoint and SQL Server. I said I would start at the beginning, which is ‘what is a database?’ (To complicate life,”Database” often refers both to the management program and to the data itself. Hopefully my usage will indicate which we’re referring to.)

The best generic definition of a database I’ve found is in Wikipedia. I’ll copy it here as it is succinct and spares me the trouble of trying to say in many of my own words what is already stated in fewer words:

“A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality (for example, the availability of rooms in hotels), in a way that supports processes requiring this information (for example, finding a hotel with vacancies). This definition is very general, and is independent of the technology used.

“The term ‘database’ may be narrowed to specify particular aspects of organized collection of data and may refer to the logical database, to physical database as data content in computer data storage or to many other database sub-definitions.

“The term database is correctly applied to the data and their supporting data structures, and not to the database management system (referred to by the acronym DBMS). The database data collection with DBMS is called a database system.

“The term database system implies that the data is managed to some level of quality (measured in terms of accuracy, availability, usability, and resilience) and this in turn often implies the use of a general-purpose database management system (DBMS).[1] A general-purpose DBMS is typically a complex software system that meets many usage requirements, and the databases that it maintains are often large and complex. The utilization of databases is now spread to such a wide degree that virtually every technology and product relies on databases and DBMSs for its development and commercialization, or even may have such embedded in it. Also, organizations and companies, from small to large, heavily depend on databases for their operations.

“The design, construction, and maintenance of a complex database requires specialist skills: the staff performing these functions are referred to as database application programmers and database administrators. Their tasks are supported by tools provided either as part of the DBMS or as stand-alone software products. These tools include specialized database languages including data definition languages (DDL), data manipulation languages (DML), and query languages. These can be seen as special-purpose programming languages, tailored specifically to manipulate databases; sometimes they are provided as extensions of existing programming languages, with added database commands. Database languages are generally specific to one data model, and in many cases they are specific to one DBMS type. The most widely supported database language is SQL, which has been developed for the relational data model and combines the roles of both DDL, DML, and a query language.”

Very interesting, but the passage above doesn’t inform as to why we have databases.

Yet I do like the reference to database administrators (DBAs for short) and their specialist skills. The article should have elaborated on DBAs, mentioning their intense devotion to their jobs, rare skills, great importance to their organizations, and chronic status of being under-salaried.

Prior to the dawn of humanity, or at least before about 1975-80, most information stored and used in computers was stored in files. Files are specific places on a storage media. While not normally stored contiguously, all the data in a given file could be accessed or retrieved by using the name of the file. The data would be presented in a continuous stream. It would be maintained by a simple application to add, change or delete data. Typically that application provided very little help in this process. It seldom informed as to what the data was to look like, or when it was in error.

But each file was stored separately from, and independently of, other files. They might be stored in the same folders or directories, but each file was independent of the others.  And that meant each file had to be managed independently. In order to retrieve, add to, delete from, update or archive data, the user usually had to know the name of, and usually also the location of, each file being managed.

That was just to manage the data. The application typically manipulated only the data. It wasn’t capable of adding or summarizing or correlating its data to data in other files. Typically these apps could only retrieve data by searching the entire file, start to finish, to find a given ‘bit’ of data—pun intended but unavoidable.

This was ok if a given program only used one, two or even three or four files. But as applications became more complex, and the data more voluminous, the number and size of files grew exponentially. As programs grew, the number of files could go into the hundreds or even thousands. Managing these numbers of files became a nightmare.

And keep in mind this was only to manage the data, not to make use of it.

Using data required one or more other applications, usually uniquely developed to process only these files just to develop the end products of reports etc.

Gradually users demanded easier ways of managing and using data. Someone came up with the idea of having a file management program. Such a program would know the name, location, owner, age, structure, backup status and all other significant data required to manage hundreds of files. This program could alter, update, archive and otherwise maintain all the files it was responsible for. Software tools were built into the overall program to assist in all of these activies.

These management programs eventually became known affectionately as “databases” or “database management systems” – DBMS for short.

There are many types of databases, and many different manufacturers. Someday, if I run out of more germane topics, I’ll describe some.

Microsoft SQL Server, the backbone for SharePoint, is such a database. It was derived from another DBMS—Sybase—in the early 1990s. SQL Server provides a means for storing, retrieving and manipulating massive amounts of data. It helps in creating and remembering the structure it stores. Its front end simplifies in adding deleting, changing and archiving the data stored within it. It helps secure the data against unauthorized access, and provides a host of other capacities we’ll talk about soon.

So we’ve described what a database is, why we need them (to manage hundreds and thousands of files), and that SQL Server helps do these. Next time we’ll get deeper into the structure of SQL Server. And- in case you were worried- yes, we will eventually get into how it interfaces with SharePoint.

Thanks for reading! Interested in boosting the performance of your SharePoint environment? Fpweb.net is the world leader in SharePoint cloud hosting. Please contact us to learn more about how we can optimize your SharePoint and SQL Server infrastructure.

Until the next byte …

About Don Conrad

Don Conrad has written 11 posts in this blog.

Don is our resident database wizard. His rural upbringing shifted from threatening Vietnam backdrops to corporate number games and after accumulating 20 years of experience with SQL Server in all its various versions and aspects, it eventually landed him on Fpweb.net’s front porch. A man of many hobbies, figure skating being at the top, Don takes pride in making each day count and sharing his wisdom whenever he can. A bit of Don's background: Born Lexington, KY BS/Commerce University of Kentucky US Army 1966-1971 Vietnam '67-'68 & '70-'71 MBA Washington University, 1973 w Beta Gamma Sigma Adjunct Professor, Washington University, 1974-1977 CPA Missouri 1975 MIS, Washington University, 1984 MCDBA, Presenter, the Professional Association for SQL Server, London, 2000 Symposium Past Board Member, St Louis SQL Server Users Group. MCITP, SQL Server 2005 MCITP, SQL Server 2008 Data Base Administration MCITP, SQL Server 2008 Data Base Developer

VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)

 

Leave a Reply

Your email address will not be published. Required fields are marked *


eight + = 13

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>