When you create a SharePoint Web Application, you also specify the name of content database to be used with the web application. By default SharePoint creates and associates only one content database with each web application created. And all the site collections within a web application are also created within a signle content database.
But we can create and associate multiple content databases within a single web application. Each content database can contain multiple site collections. But do remember that a site collection can not be associated with two content databases.
Some days back I came up with a requirement to create multiple content databases within a single web application. I was aware of its feasibility but I didn't know how to do that. I tried reading SharePoint help and found that it can be done using SharePoint Central Administrator.
Following are the various steps to create multiple Content Databases with a Web Application:
Step-1: Open Sharepoint Central Administration application, select "Application Management" tab and click on "Content Databases" link
Step-2: The "Manage Content Databases" page displays all the content databases associated with the selected content database. You can see the below screen show only one content database for selected web application and this is the default content database which has been created with the Web Application.
Now select the web application within which you want to create multiple content databases.
Step-3: After selecting the web application, click on the "Add a content database" link
Step-4: Enter all the details for the new content database and click "OK"
Step-5: Its done, new content database is created and associated with the selected web application. We can see in the below screen it shows two content databases, first the default one and second one is the new content database we have just created.
Now we know how to create and associate multiple content databases with a web application. But the next question comes in mind is how to associate a site collection with a particular content database. When you create a site collection, SharePoint doesn't provide an option to choose a content database.
To get a better insight into this, I created multiple site collections within the above web application. It created first site collection under first content database and then the second site collection under the second content database. The third site collection has been created under first content database and fourth one again under the second content database. The same cycle continued when I created few more site collections.
Then I tried to find a way to force a site collection to be created under a particular content database. I found that each content database has a "Status" property which is either "Ready" or "Offline". This property is the solution for our problem. We just need to set all the content databases "Offline" and only make the content database "Ready" under which we have to create the site collection. When we create a new site collection, it will be associated with the content database whose status is "Ready".
To change the status of a content database, click on the content database, select its status either "Ready" or "Offline" and click "OK".