SharePoint creates SQL Databases

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.

Access Denied issue after creating new configuration database

This is one of the rare issues I was facing with my farm.

Scenario

Due to domain crash I have created a new domain with the same name and created all users which were there in old domain.

Issue

• After creating a new domain I have tried to connect with the old configuration database which failed with invalid user credentials issue.
  
• So I have created a new Configuration Database as I have Content DB of the site with me.
  
• PSConfig was completed successfully and attach content DB worked fine.
  
• Now the main issue occurred, site was accessible fine if I use farm admin credentials but it gave "Access Denied" error for all other users which I have added to the site earlier.
  

Resolution

• From my research I found it was due to Schema mismatch, so when I delete and re add that user it worked fine.
  
• Or you can use STSADM -o migrateuser command
  

Step by step SharePoint migration

Steps to migrate one moss server to other server
1. Take back up of all databases from SQL management studio (in .bak format)
2. You can also copy the .mdf and .ldf files of the content databases
3. Must remember the versions of the databases.
4. Copy the backed up files to some safe location
5. Create a new MOSS server
5.1 Install .NET 3.0 or later version
5.2 Install SQL Server 2005 or later version with SQL management studio
5.3 Install MOSS
6. Do not run PSconfig after installation of MOSS
7. Before that we have to copy the backed up databases files to the new server
8. Restored the databases form the .bak files to the newly installed SQL server
9. You can attach .mdf files to the new databases.
10. After copying the backed up data to the newly created MOSS machine run PSConfig wizard
11. In PSConfig wizard if you want the same customization as it was in the previous one then connect the MOSS to the existing farm and in the existing farm select the old configuration database.
12. Wait till the PSConfig wizard will be completed.
13. Open the Central admin page and check whether you get the same customization or not.

Now how to up your previous sites back in to the new MOSS machine.
1. Create a new site in new MOSS machine
2. Go to the content databases option in Application management of the central admin page.
3. Dropped the existing database , now your site comes down
4. Open the command prompt and type the following command to attach the content DB of the previous machine

Stsadm –o addcontentdb –url -databasename

5. The database which you want to attach must be restored in the SQL server