SQL databases created by SharePoint

When we install SharePoint we have the databases created in SQL, SQL is the backbone of the SharePoint server, whenever you upload any data or any document to the site its actually uploaded to the SQL database associated with that application, the documents gets stored in SQL and they have only the reference link

Let’s look at the databases created in SQL when you install SharePoint and configure stuff like search My Sites and SSP, also when you make your very first application
Installing SharePoint on your server

When you install SharePoint on your farm, irrespective of the type of installation you choose that’s Basic or Advanced (I mean using SSEE or Full SQL version) you get two databases created on your specified SQL instance they are:

Sharepoint_Config : This is the configuration Database for the site this database holds information on farm configuration, patches and updates applied to the server, versions of sites and databases, users, custom templates, Features, etc to be specific it contains 24 tables and each tables has its own significance

1> dbo.antivirusvendors – contains 3 columns
Vendor Id
Vendor Product
Vendor Version
As you would guess it will be talking about the Antivirus properties applied on the server obviously this table has to be present on the Config database as Antivirus is a portal level application and not an application level

2> dbo.Binaries - Contains 2 columns
Object ID
File Image
I am not really sure what this table is doing here its maybe used for some customization references

1> dbo.classes- contains 3 columns
Id –
BaseClassId-
FullName
It contains the classes used to create the SharePoint dll
So the next time you face an error of a particular class not working you can check the entry here
2> dbo.customtemplates – contains 6 columns
ID
Title
Description
LCID
Content
Template ID
This table would contain the information of the custom templates applied to the site, when you run the stsadm –o addtemplate command the attributes are added to the site as well as the dbo.customtemplates
just comparing these columns with the attributes of stsadm –o addtemplate, they are filename, title, description,
so we can troubleshoot issues with the custom templates not getting updated or the custom templates not being applied to the site from these

3> dbo.databases – contains 15 columns
DatabaseID
VirtualserverID
Name
UserID
Password
SiteCount
SiteCountWarning
SiteCountLimit
DatabaseServiceID
LastModified
LastModifiedUser
LastModifiedServer
Status
Version
Properties
As seen its used to get all the details of the databases used in the SharePoint site, It also contains the warnings and limitations of the site, status their version and properties

4> dbo.dependencies – contains 2 columns
ObjectID
DependentID
Generally a list of object ID and Dependent ID mapped together

5> dbo.EmailenabledLists- contains 5 columns
Alias
SiteID
WebID
ListID
Deleted
Whenever you create an email enabled document library you can see the attributes being listed here, we configure this library on a particular site collection and have to provide ‘Alias’ of the library while creating the library, the ‘SiteId’ of the site holinding the email enabled list, Web ID of that site, ListID of the List, and status as deleted or not


6> dbo.globals- Contains 14 columns
GlobalID
SchemaVersion
UseHostHeader
UseNTAuthfordatabase
smtpserviceID
Mailcodepage
FromAddress
Replytoaddress
LastModified
LastModifiedUser
LastModifiedServer
Status
Version
Properties
These all are the global settings for the site which can be done via several links on the central administration page like Web application general settings, AAM etc

7> dbo.InstalledWebpartPackages- contains 5 columns
WPPID
url
IISInstance
ServerID
Globalinstall
It talks about the third party web parts added to the site or the custom webpart solution deployed to the server, can be a good medium to troubleshoot the issues with Out of the Box web part solutions

8> dbo.LastUpdate – contains 2 columns
Value
Version
This is an interesting thingy to look at cause it contains the information of the last update applied to the server, so In case you feel that something happened to your server because of the automatic update applied or you want to check on the last update you can check this table

9> dbo.Objects- contains 7 columns
ID
ClassID
ParentIDName
Status
Version
Properties
Contains information of the objects


10> dbo.pendingDistributionList- contains 5 columns
SiteID
WebID
GroupName
ModifiedBY
Version
Contains list of pending DL’s and some information

11> dbo.Servers- contains 9 columns
ServerID
Name
Address
LastModified
LastModifiedUser
LastModifiedServer
Status
Contains information of the servers in the farm and there details

12> dbo.services- contains 10 columns
ServiceId
ClassId
ServerID
Instance
LastModified
LastModifiedUser
LastModifiedServer
Status
Version
Properties
Contains information of the services

13> dbo.SiteCounts- contains 2 columns
DatabaseID
NumSites
It contains the count of sites in each Database

14> dbo.SiteMap- contains 9 columns
ID
ApplicationID
DatabaseID
Path
Pairing
Status
Version
Redirecturl
HostheaderIssiteName
This also gives information about the sites

15> dbo.sites- contains 9 columns
SiteId
DatabaseID
URLpath
LastModified
LastModifiedUser
LastModifiedServer
Status
Version
Properties
This one is supposed to contain the number of sites and its attributes with respect to each application

16> dbo.Timerlocks- contains 9 columns
JobID
LockedByServerName
LockedTime
This contains all the jobs Locked by the timer job

17> dbo.TImerRunningJobs - contains 10 columns
ServiceID
VirtualServerID
JobID
JobTitle
ServerName
Status
StartTime
CurrentStatus
TargetCount
CurrentTargetPageDone
Shows time and status of all the timer jobs

18> dboTimerTargetInstances- contains 2 columns
JobID
TargetInstanceID
Will contain Timer Target Instances and there list with the Job ID

19> dbo.tombstones- - contains 2 columns
ID
Version

20> dbo.Versions - contains 10 columns
VersionID
Version
ID
Username
Timestamp
Finaltimestamp
Mode
ModeStack
Updates
Notes
Contains previous versions of the site and the version History, also account that changed it the newer version (by applying updates)

21> dbo.Virtualservers- contains 12 columns
Virtualserver ID
Name
smtpserviceID
Mailcodepage
Fromaddress
Replytoaddress
LastModified
LastModifiedUser
LastModifiedServer
Status
Version
Properties
Shows the entries and attributes of virtual servers added to the farm

22> dbo.webpartpackages- contains 3 columns
WPPID
Title
Content
Similar to dbo.InstalledWebpartPackages contains information of the OOB or custom web part packages


Admin_Content : This database is created when you complete running the PS Config wizard for SharePoint, this wizard launches the Central administration application, actually in background the wizard creates an application for the Central admin page

The central admin database is names as admin_content database, this is by default and you cannot rename or edit it.

It is just like any other normal content database, but it has the Central Admin site, which is the most important site.