Friday, February 22, 2008

Accessing the WSS/MOSS Content Databases

There's a myriad of information to be garnered from your WSS content databases. Although you'd very seldom want to change any of that data there's quite a few stored procedures and select statements (if you understand the table infrastructure) that can give you quite a bit if insight as to why a WSS instance is behaving a certain way.

But before you can do any of that though, you need to be able to connect to the SQL Server instance that's housing everything! Before we get started there's a couple things you'll need.

Connecting to the WSS OfficeServers Database

  1. The first thing you'll need to do is allow remote connections to the SQL Server instance. If we don't, when we try to connect to the
    [ServerName]\OfficeServers instance from another machine we'll get the following error.
    An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
  2. We can enable remote connections (on the machine that's running SQL Server) by running the SQL Server Surface Configuration Wizard (usually under Start->Programs->Microsoft SQL Server 2005->Configuration Tools->SQL Server Surface Area Configuration.SQL Server Survace Area Configuration Tool
  3. From there we click Surface Area Configuration for Services and Connections, choose the SQL Server Instance that we want to enable remote connections for (OFFICESERVERS in this case) and click the Local and remote connections under Remote Connections.Allowing remote connections to WSS SQL Server OFFICESERVERS Instance
  4. Now stop and start the service for that SQL Server Instance so that you're settings can take effect.
  5. At this point we can use the SQL Server Management Studio to connect to the WSS SQL Server instance from another machine so long as your windows credential is an administrator on the SQL machine. Otherwise you'll have to connect to connect to the database the first time from the same SQL Server machine that's hosting it. The name of the WSS SQL Server instance is [ServerName]\OFFICESERVERS. Connecting to OFFICESERVERS SQL Server Instance
  6. Like I said above, it's important to note that by default the server instance only allows Windows Integrated authentication so your windows account needs to be an administrator on the machine, otherwise you're better off connecting locally from the same machine. You can enable Mix Mode authentication (which lets you use a username/password) after you connect by right clicking on the server instance clicking Propertie->Security and then clicking the SQL Server and Windows Authentication mode radio button.Enabling Mix Mode authentication in SQL Server Management Studio

I know these are pretty easy tasks for most developers but I wanted to make sure there was a starting point for a post I'm going to make in a day or so about troubleshooting deleting content types that continue to throw the error "Content Type in Use" when it's not obvious where in use it is.

Keep on keeping on,


Bob C said...

Have you found a way of locating the form libraries where a content type is in use?

Tyler Holmes said...

You bet. There was a post shortly after this one that shows how to find all the lists/libraries that a content type is being used in.


server management said...

I am also searching location of libraries