My motive here is to backup the transaction log file. Ever
wondered how to save space on VM with SQL transaction log files. I have a
Symantec Backup Exec 2010 which backs up my VMs and its priority to keep the
VMs slim and trim for faster backups. A GB of space saved is a GB less you have
to backup. So I came across the Windows Internal database which is a bare
version of SQL which unfortunately does
come with Maintenance plans feature. Just to get access to the databases you
will need to install SQL management studio. As you may know Windows Internal
database are used in Team Foundation and Windows update services among others.
My main objective is to reduce the VM size and backup of sql
, since I do the whole backup using Symantec. All I need to do is control the
transaction log size using a automated process.
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):
Below are my scripts for Batch file
Bat file
erase /Q c:\Backup\*.trn
sqlcmd -S\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -i
c:\Backup\SQL_WIDB_Sharepoint_Backup.sql -o c:\SQL_WIDB_Backup_Report.txt
exit
The bat file can then be configured to run at specific times using the
"Task Scheduler" (Start->Accessories->System Tools).
Below are my scripts for sql query.
Sql query file
SQL_WIDB_Sharepoint_Backup.sql
use master
go
DECLARE @dateTime NVARCHAR(20)
DECLARE @BackupFile varchar(100)
DECLARE @sqlCommand NVARCHAR(1000)
SET @dateTime = REPLACE(CONVERT(VARCHAR,
GETDATE(),101),'/','') + '_' +
REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
Set @BackupFile =
'''C:\Backup\SharePoint_AdminContent_b4380613-dc8b-49ab-bd27-d6dd0726f6f6_LOG_'+@dateTime+'.trn'''
set @sqlcommand = 'BACKUP LOG
[SharePoint_AdminContent_b4380613-dc8b-49ab-bd27-d6dd0726f6f6] TO DISK
='+@BackupFile
exec (@sqlcommand)
GO
use master
go
DECLARE @dateTime NVARCHAR(20)
DECLARE @BackupFile varchar(100)
DECLARE @sqlCommand NVARCHAR(1000)
SET @dateTime = REPLACE(CONVERT(VARCHAR,
GETDATE(),101),'/','') + '_' +
REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
Set @BackupFile =
'''C:\Backup\SharePoint_Config_2b4b85fc-fdf9-47ca-893f-a60e24043c2a_LOG_'+@dateTime+'.trn'''
set @sqlcommand = 'BACKUP LOG
[SharePoint_Config_2b4b85fc-fdf9-47ca-893f-a60e24043c2a] TO DISK ='+@BackupFile
exec (@sqlcommand)
GO
No comments:
Post a Comment