SQL, Sharepoint and the Windows Internal Database – an interesting saga

  • Article

This one is for all my friends out there who use Sharepoint. A default Sharepoint installation enables/installs the Windows Internal database, and creates its databases on it. The Windows Internal Database is, in a way, a special edition of SQL Server, in the sense that it’s not a Full version, but does not have the data file limitations of SQL Server Express either (yes, you heard that right). Anyways, the focus of this post is going to be on the following things:

  1. How to connect to the Windows Internal Database (to see what's going on at the back-end)
  2. How to troubleshoot common issues such as log file growth for Sharepoint databases attached to Windows Internal database (from a purely SQL perspective)
  3. How to set up automated SQL backups for your Sharepoint databases (remember, Windows Internal database does not have SQL Server Agent, and normal Windows scripts for taking backups will not work either).

Okay, so let’s get started:

Connecting to the Windows Internal Database

If you open the SQL Server Configuration manager on a machine that has Windows Internal database enabled, you will see a service named “Windows Internal Database (MICROSOFT##SSEE)” (also visible on the services console). Right click on the service in SQL Server Configuration manager, go to “Properties”, and click on the “Advanced” tab. Here, select the “Startup Parameters” option, and you will see a drop down next to it. In the drop down, look for the path to the Errorlog. Typically, it will be something like this:

C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\LOG\ERRORLOG

So now we have the path to the Errorlog for the Windows Internal Database. Open the errorlog in a text editor (notepad or anything else of the sort), and look for the pipe name. Typically, the pipe name looks something like this:

Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query ]

This is what we will use to connect to the WI database (yeah, I’m feeling lazy). So we just start up SQL Server Management Studio (on the local box, as you cannot connect to the Windows Internal Database over the network), and fill in the pipe name there, which is “\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query” in our case, and hit Connect, and voila, you’re connected.

Troubleshooting log file growth

Now, if you’re facing issues with, say, log file growth with your Sharepoint databases (which are attached to the Windows Internal Database instance, of course), then as usual, the first thing to check would be the log_reuse_wait_desc column in sys.databases

select log_reuse_wait_desc,* from sys.databases

This should give you a fair idea if there’s anything preventing your log files from reusing the space inside them. From a SQL perspective, perhaps the best thing would be to put the databases in Simple recovery model, so that you can stop worrying about Log file space reuse altogether. I have done this successfully for a couple of my customers, without any adverse impact whatsoever to their environments. But that’s not to say that it will work fine for your environment as well. Please do take a full backup both before and right after you make the change, to be safe. It might also be a good idea to restore the db on another server and test it after changing the recovery model to Simple.

Setting up Automated backups

This is by far the most interesting part of the post, or at least, the one that took me the maximum amount of time to accomplish. My customer wanted to set up automated backups from inside SQL for the Sharepoint databases. After a lot of time and effort in preparing and testing, we finally got the script ready (SQL_WIDB_Backup.sql, see attached).

You need to customize the script according to you database names and file paths, and then configure a bat file which calls the sql script. The bat file will have a command like this (again, please configure according to your environment):

sqlcmd -S\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -i c:\SQL_WIDB_Backup.sql -o c:\SQL_WIDB_Backup_Report.txt

The bat file can then be configured to run at specific times using the "Task Scheduler" (Start->Accessories->System Tools).

Hope this helps.

SQL_WIDB_Backup.sql

Comments

  • Anonymous
    April 07, 2012
    5 stars even if it is missing 2 useful informations ( it is only my own feeling )
  • the version of SQL Server from which the Windows Internal Database is dericated
  • the version of Sharepoint ( what i am not understanding is why , when a new version of SharePoint is releasing , it is not using the latest version of SQL Server  ? ) Really interesting article as it is a new information for me ( about backup/restore of this database and the use of SSMS to control the size of the Windows Internal Database : interesting, yes, useful surely )

  • Anonymous
    April 07, 2012
    Thanks for showing interest in the blog, Papy..!!! And thank you for appreciating the effort...always good to know...!!! :) Now, as far as the version of SQL is concerned, I've tested this on Windows 2008 R2, and the version used for the Windows Internal Database is SQL 2005 (the path to the ERRORLOG gives you a hint). As far as Sharepoint not using the latest version of SQL, well, the thing is, Windows Internal Database is a Windows feature, and Sharepoint does not install it, it merely enables it if it's not already enabled. So, to be fair, Sharepoint can only use the version of Windows Internal Database available, it cannot install it's own version (coz it's a Windows feature, remember?) Hope this answers your question. Do let me know if you need more info. And thanks again for the feedback Papy. It's lovely to hear from from folks like you, and such great feedback is what keeps me going...!!! :)

  • Anonymous
    April 08, 2012
    Thanks for your reply. I am not a specialist of Sharepoint. The rare times i am interested by Sharepoint is because there is a thread related to Sharepoint in the SQL Data Access Forum. You have given me valuable informations, and thank to your reply which will help me to understand these thread on one of my favorite forums. I did not know that the Windows Internal Database is depending of the Windows Server.I am less stupid now... Thanks again for this article and i will wait the one impatiently as i think it will be as good as this one.

  • Anonymous
    April 08, 2012
    Thank you so much Papy. Even I did not have any idea about Sharepoint and how it relates to SQL till I got the opportunity to work on this issue, so I guess we're pretty much in the same boat there. Thank you again for your feedback Papy...!!! I will try my level best to live up to your expectations on the next post...!!!! :) :) Regards, Harsh

  • Anonymous
    September 04, 2013
    Hi Harshdeep, Really very interesting information for shrepoint database. I need to know is SQL Server security patch required to install for the Windows Internal Database. As you sais thatWindows Internal Database is depending of the Windows Server not ont the SQL Server. I am asking this becuase my clent told me to install the security patch on WIDB but when I am doing patching WIDB is not getting detected to SQL Server pathc installer. Please let us know. Thanks & Best Regards.

  • Anonymous
    September 05, 2013
    Thanks for appreciating Shashi...!!!! Patches for the windows internal database are released by the windows team, and should show up as part of Windows updates. SQL patches cannot be directly applied on windows internal db. Hope this helps.

  • Anonymous
    August 28, 2014
    very old thread.... but very nice and intersting information. Best Regrads.