Thursday, February 26, 2009

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.

11 comments:

Sandeep Silicus said...

Thanks Nidhi,
I have 3 SharePoint environments with single database server. I am not able to identify SharePoint_Admin_Content_xxxxxx databases. Can you please help me?


Regards,
Sandeep

Nidhi said...

hey sandeep

to recognize the Admin database used by your site, go to Central Admin page > Application management tab > Content database
from the drop down situated on right side change the application to Central Admin page
IT will show u the attached database

kathir said...

Hey Nidhi,

Can we delete an entry from InstalledWebPartPackages table?? Will it cause any issues?

In this table for each webpart i have three entries. Out of that one entry is denoting an IIS instance but that actually not exist in IIS. Can i delete that through SQL Query??

Kathir

Anup said...

Hey Nidhi
I wanna use my own sql tables with share point. Is it possible and how?

Vishesh Sharepoint Professional , MCTS said...

hi Nidhi,

I want to know in which table the custom columns names are stored in sharepoint database.

sandy said...

Hello,
I have a medium sharepoint 2007farm,the database table in contnt DB ,Alldocversions growing with 20 GB in a day.Not able to trace through event and trace logs.
please suggest.

Regards
Sandeep mw

Jim A said...

Nidhi,

We have a SharePoint portal running on our intranet with secure socket layer. We opened it in designer, added a custom CSS file (very small) and linked to it in a couple of master pages. We immediately got an information bar and now users get a dialog asking to view only secure content. We rolled it back and still the same thing. MOSS 2007 with IE8. Question - could something have been saved/cached in the database that we still have the problem after removing our changes? If so, where might we need to look. Great article. Thanks.

SLPS said...

Which table are custom master pages stored in? Thanks!

Anonymous said...

Hi Nidhi,

Nice blog.

I want to have another copy of same WA on same farm.

MOSS 2007.

I have followed below mentioned steps -

Application Server-
Create new WebApplication
Create a site collection
SQL Server -
Check the site id WSS_Content_3001 089C180F-989F-40CB-A0B8-FAA53DCC547C
WSS_Content_4001 5E753633-12ED-4F79-B266-3E291ADABC7B
Application Server-
Create second WebApplication
Create a site collection
Remove the WSS_Content_4001 Note: The WSS_Content_4001 still exists on SQL server.
SQL Server -
Backup the WSS_Content_3001_20121205.bak
Restore the WSS_Content_3001_20121205
Check site collection's GUID - 089C180F-989F-40CB-A0B8-FAA53DCC547C
Assign new GUID to site collection - D2666A81-F4A4-46ED-BF29-22FCAA6C06A6
Application Server-
addcontentdb and assignnewdatabaseid to http://cph26bt10:4001
Observe the number of sites displayed. It is 0
SQL Server -
Check site collection's GUID - D2666A81-F4A4-46ED-BF29-22FCAA6C06A6

Still at http://xyz:8080/_admin/cntdbadm.aspx, "Current Number of Sites" is zero.

Please help ASAP.

My email id is ash202_2000@yahoo.com

Dani Rodríguez said...

More info: http://msdn.microsoft.com/en-us/library/dd624757(v=office.11).aspx

Karthika Shree said...

That was a great message in my carrier, and It's wonderful commands like mind relaxes with understand words of knowledge by information's.
Sharepoint Training in Chennai