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:
- How to connect to the Windows Internal Database (to see what's going on at the back-end)
- How to troubleshoot common issues such as log file growth for Sharepoint databases attached to Windows Internal database (from a purely SQL perspective)
- 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:
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.