I recieved the following question yesterday, and I have had it before, so I decided to blog the answer.

Hiya James

I am not sure if you would be willing to help answer a question on sharepoint?

Sounds really stupid but just using MOSS and I have not had to deal with any SQL database ... so where

can I actually view it or find it?

Thank you

Ray


First up, it's far from a stupid question!  especially is Windows Internal Database (a.k.a SQLEE is used).  Let me explain:

So, there are a couple of choices for setting up the database:

1. Windows Internal Database

It is really easy to set the system up with this option, and frankly, I don't like it for my systems.  The reason I don't like it is because it splits my DR plan.  

For every other application I have a nice unified backup & recovery strategy in SQL Server Management Studio.  Using WIDB for Sharepoint introduces exceptions to the rule, and from my experience, the systems with the most moving parts are the ones that are most likely to fail under pressure, and DR can have pressure!  Of course YMMV.

What is Windows Internal Database?  It is an instance of SQL Server Express configured to be accessed only by services over named pipes, although it is possible to connect to it with SQLCMD or Management Studio Express.  It works just as well as SQL Express, but I feel like I am never fully in control of it.

If you are using SQLEE for your Sharepoint installation and you want to have a look at the database, I'd suggest the following:

1. Backup your Sharepoint instance using Central Administration
2. Setup a test instance of Sharepoint using SQL Express
3. Restore your Sharepoint backup into your new test instance
4. Use Management Studio to work on your new test database
   

2. Using "real" SQL Server. 

This is a much easier case.  Log into the Sharepoint Central Admin section and view the content database settings.  That will give you the server & database names that you can connect to with Management Studio.  (please work on a copy, not the production database)

...but...

...is it wise to get hands-on with the MOSS/WSS database?

Well yes, for general curiosity to see how the Sharepoint team decided to implement a system where entities can be so customizable, there is some good, subtle design to learn from.

However for reporting and other fiddling, I'd suggest not.  Once you see the tables I think you will agree that it was not a design goal for the developer community to be accessing them directly.

A good alternative would be learning some CAML or the Object Model first and see if you can leverage that.  Those ways to access the data abstract out the complexity of the physical database and are quite user friendly.

How to setup Sharepoint to use SQL Express rather than SQLEE/WIDB?

It is easy and free to set up a testing environment for Sharepoint:

All you need to do is download the following:

• Windows Server 2008 Standard Trial
• Virtual PC
• WSS 3.0 (incl SP1)
• WSS SDK
• SQL Server Express Edition
  

How to setup Sharepoint to use SQL Express rather than SQLEE/WIDB - Part II, The Install...

When installing Sharepoint, choosing Basic/Stand Alone will leave you with a WIDB install of Sharepoint.  To install a it with a "regular" SQL Server, or with SQL Express, first choose Advanced from the installation wizard:

When asked if you want to create a Web Front End server, or a Stand-alone install, choose Web Front End - even if it is your only server:



Continue the install and launch the Sharepoint Product and Technologies configuration wizard.   Choose to create a new server farm.  Enter the database server and database name settings into the wizard when prompted...




Now you have configured Sharepoint to not use WIDB/SQLEE.

If you have already installed SQLEE and now wish to remove it, please see my prior post on uninstalling SQLEE.