Microsoft SQL Server and SharePoint: The Next Byte


The Next Byte is our series of posts about the relationship between SQL Server 2008 and Microsoft SharePoint, written by database administrator Don Conrad. Missed the last post? Please read “Microsoft SQL Server: The Database Behind SharePoint”.

Up for discussion:

  • A high-level description of SQL Server
  • What are the major components of SQL Server?
  • What are the core functions of SQL Server?
  • What do I have after installing SQL Server?

SQL Server is a database server product. It’s arguably one of the three most popular ones on the market (the other two being ORACLE, manufactured by the ORACLE corporation, and DB2 by IBM). All three provide very similar functions albeit on three different hardware platforms. SQL Server runs on Microsoft Windows servers, ORACLE primarily on Unix/Linux platforms and DB2 generally on IBM mainframes.

While all three perform mostly similar activities, Bill G. and I can probably agree that SQL Server is the premier product. After all, administering SQL Server has helped me pay most of my bills these past years!

The SQL Server database performs various functions:

  • Stores data in a highly protected environment. Its architecture helps ensure the data is retrieved in essentially the same form as it was inserted. That makes Microsoft SQL Server an ideal storage platform for SharePoint sites.
  • Provides a reasonably reliable technique for restoring data that has been inadvertently lost or deleted. The same technique can be used to restore all the server contents if the hardware is lost or becomes inoperable.
  • Provides the means for securing access to the data, at times down to the field level if so desired.
  • Provides tools for managing the various functionalities and reviewing and adjusting data if needed.
  • Provides a means for creating and managing jobs for processing or otherwise manipulating the data stored in it.
  • Reports errors, failures and selected events to the administrators.
  • Contains facilities for communicating with and transferring data to/from other database servers and devices. In some cases, these other servers are not even SQL Servers, or even Microsoft products.
  • Provides tools and techniques for ensuring automatic data duplication, distribution and high-availability.
  • Provides other tools and techniques for monitoring and de-bugging its own activities.

Yes, on some rare, extremely rare occasions SQL Server ‘misbehaves’ and does unfathomable things. Normally this misbehavior results from being abandoned, forgotten about, and ignored by its human guardians.  Careful, tender-loving and affectionate care goes a long way to ensuring SQL Server doesn’t get its feelings hurt.

But most importantly, SQL Server can store massive amounts of data; data that it can retrieve very fast if it has been appropriately architected and configured. It stores this data in a way that significantly reduces the chance of inadvertent loss or damage.

The above list is by no means exhaustive of all the capabilities and services within SQL Server.

… Just exhausting to read maybe.

Upon installation, SQL Server creates

  • Four system databases (master, model, msdb and tempdb)
  • Icons and tools for administering the server (SQL Server Management Studio ‘SSMS’)
  • A login interface to gain access to various database servers
  • A tool to directly access, see and change the data: ‘Query Analyzer’
  • The database engine service
  • A database discovery service: ‘Browser’
  • A job-management tool and service
  • Error logs for reporting both errors and events
  • Security verification processes
  • Communications protocols
  • Various and sundry other tools and capacities

I’ll eventually discuss all of these in one degree or another.

One other item installed is not always found in other DBMSs. Each database is comprised of a data file and a log file. The data files are obviously where all the data is ultimately stored. However the log file is how the data gets into the data files. Except for bulk loading operations, all data inserted into the data files first goes thru the log files. The log files serve as the means of achieving point-in-time restores of data. They also can serve to warn when a database experiences a runaway process.  If a runaway process is left to its own devices, often it can consume all available space. Without an alert, a user’s first awareness of this situation is when the database locks up, unable to store or process additional data.

This can be embarrassing.

The log files will be discussed at length in several blogs. Understanding how they work is vital in avoiding many problems, including perceptions of slow response times, unanticipated losses of data, database lockups, and the common cold. Well at least everything before the cold.

We’ve discussed the major components of SQL Server, its major functionalities, and the significant components created during its installation.

Next time, we’ll cover the functionality of the major system components in greater detail.

Please use the comment section below to send along any questions.  Until the next byte …

2012-02-27T07:14:39+00:00 February 27th, 2012|

Leave A Comment