Microsoft products such as Windows Server Update Services (WSUS) 3.0 and Windows
Sharepoint Services (WSS) 3.0 ship with SQL Server 2005 Embedded Edition. Now called
the Windows Internal Database, more and more system administrators charged with
managing WSUS and WSS are faced with the challenge of managing these databases.
Since most of these system administrators are not full-fledged DBAs, how do they
manage the Windows Internal Database?
Solution
The Windows Internal Database is an embedded data service that can only be used
by a handful of Windows Services. It is designed in such a way that you are not
allowed to connect to and use this particular database service for non-Microsoft
products. By default, installing either WSUS 3.0 or WSS 3.0 will create the databases
on the C:\ partition and will cause administration issues such as insufficient disk
space. It is quite confusing to manage this SQL Server instance as it does not appear
as a SQL Server service nor are there management tools included with the products.
The easiest way is to use
SQL Server Management Studio Express. You can install a copy of SQL Server Management
Studio Express on the server running your WSUS 3.0 or WSS 3.0. You then register
this instance using Named Pipes as this is the only configuration for connectivity.
Use this server name when you register this instance
\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
Once you've managed to register this instance in SQL Server Management Studio
Express, you can now administer the WSUS 3.0 and WSS 3.0 databases like shrinking
the database files or moving them to a different disk partition dedicated for them
to avoid insufficient disk space issues.
If you prefer to use scripts to manage these databases, you can download and
install the SQL Server 2005 Command Line Query Utility -
sqlcmd . This tool will be installed, by default, on this directory
C:\Program Files\Microsoft SQL Server\90\Tools\binn.
To connect to the database instance, you need to run the sqlcmd.exe utility,
passing the instance name and your credentials
You can then run your administrative T-SQL scripts once connected to this database
instance.
Considerations
While these are possible reasons to administer the Windows Internal Database,
it is not recommended to do anything beyond performing backups, moving or shrinking
the database files. Modifying database schema or database properties would break
supportability of these products. Plus, any changes made to these databases can
be overwritten by the products' service packs or cumulative updates.
Next Steps
Install SQL Server Management Studio Express on your WSUS 3.0 and WSS 3.0
servers
Take control of your WSUS 3.0 and WSS 3.0 databases
About the author
Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.
Hi Edwin, I'm trying to research failover solutions for AD FS on WID database. From the application perspective, I know I can issue a PS command to promote a secondary AD FS server to Primary, but I am not sure if the WID database can be failed over, if so, how? So my question is, can a read only WID database copy, be made into a R/W WID database? Are synchronised WID databases highly available?
Currentlying running WSUS 3.0 using the Windows Internal Database (MICROSOFT##SSEE) on Windows Server 2003 R2 SP2.
I'm expierencing numerious events like the following:
Source: MSSQL$MICROSOFT##SSEE Catagory: (4) Event ID: 18456 Type: Failure Audit User: NT AUTHORITY\NETWORK SERVICE Description: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. [CLIENT: ]
Source: Windows Server Update Catagory: Update Services Service Event ID: 507 Type: Information User: N/A Description: Update Services failed its initialization and stopped.
WSUS 3.0 is configured using the default web site on ports 80/443. The IIS and WSUS files are located on a second partition E:\
I tried chaning the account for the "Update Services" service to "Local System", but as far as I could tell that wasn't working so it was changed back to "Network Service".
I tried the following commands form a command prompt: > osql -E -S \MICROSOFT##SSEE 1) sp_grantlogin 'NT AUTHORITY\NETWORK SERVICE' 2) This didn't seem like it was working as it just dropped me to line 2 I hit enter and it dropped me at 3) so I typed exit to return to the command prompt.
I have spent the last day and a half looking for an answer to this on Google but I haven't found a solution yet.
This issue ended up being a damaged/corrupt database. I was able to fix the problem with the Microsoft SQL Server Management Studio Express with the following query:
Finally I got a solution ,thanks for your screenshots which helped me a lot.
Connect the SUSDB Database and execute the below query.
EXEC sp_resetstatus 'SUSDB'; ALTER DATABASE SUSDB SET EMERGENCY DBCC checkdb('SUSDB') ALTER DATABASE SUSDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CheckDB ('SUSDB', REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE SUSDB SET MULTI_USER
We are running the "free" version of Sharepoint 2007 and trying to troubleshoot the situation but first we want to try and log in to the database (the SP person left) as we do not have any SP knowledge
Anyway, when we tried to login with an account with admin privileges on our domain, we cannot login to the sql studio manager.
I don't think Windows Search Server uses SQL Server under-the-covers. I couldn't find anything in the documentation on TechNet that specifically points to a SQL Server database. So, the steps outlined above will not work. I might also be wrong in my assumptions
A shot in the dark here but I'm trying to connect to the Windows Search Server 4.0 'database' and can't with the above examples. So I'm either barking up the wrong tree or WSS4.0 has a completely different connection model. Anyone offer anything in the way of enlightenment?
Just if someone runs into the same problem: I've tried to use your connector: \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query on a Windows Server 2012 internal database and it doesn't work.
Now it seems to be a new connector string you have to use with 2012: \\.\pipe\MICROSOFT##WID\tsql\query
When trying to do a backup of the WSUS DB, it defaults to a non-existent disk location at D:\WSUS\{2AE3D45E-09F4-4B90-B694-22AAFE208AD1}\SUSDB.bak. When I create another backup location and try to run the backup if fails with an error pointing to D:\WSUS\{2AE3D45E-09F4-4B90-B694-22AAFE208AD1}\SUSDB.bak as not being found. Is it safe to delete this default location?
We use a centralized backup solution (Tivoli Storage Manager) that cannot do a normal SQL backup of the WSUS DB remotely. The file backup fails as being in use leaving us with no way to backup the WSUS DB. I'd like to script the backup within SQL and then capture a backup of the backup file with Tivoli.
Any ideas?
Wednesday, February 6, 2013 - 7:10:39 PM - bass_player
If you are a local Administrator on the machine running these SQL Server instances, you can still regain access to them even when the sa account is disabled or even when you do not have access. Here's how you can do it
I have two of these for SharePoint, and four for VMM management. I am unable to make myself SysAdmin on these, as the only two logins are "sa" which is disabled, and BUILTIN\Users, which is crippled. Not being a SysAdmin, it's not possible to keep these patched.
You are correct. However, from a licensing and support perspective, you are not allowed to connect any line-of-business application to the WID aside from those sanctioned by Microsoft.
It is designed in such a way that you are not allowed to connect to and use this particular database service for non-Microsoft products
I am not sure this is true. Any appilcation that can connect using Named Pipes can use the Windows Internal Database. It has the same operational characteristics as SQL Express (1 CPU, limited memory, etc), except that database size is unlimited.
Before you think you can run your 1TB database on WID and avoid the Standard Edition license, remember that the CPU cores and memory avalable to Standard Edition are there to give performance, so don't expect WID to be fast if your DB is large.
Be aware that shrinking SQL Server databases - including SharePoint-related ones - is not recommended due to internal fragmentation both for the data files and the log file. If you are concerned about your log file growing and are not concerned about point-in-time recoverability for your SharePoint databases, set the database RECOVERY model to SIMPLE, shrink the log file to the smallest size possible and resize it to a size that you think would be enough for it to contain the active transactions without growing unexpectedly. You can resize your log file to 4GB and monitor accordingly. This process minimizes the amount of virtual log files while the recovery model truncates your log, thus, keeping it from growing
Great Tip but I was unable to connect with (\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query) and result was: "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. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid) (.Net SqlClient Data Provider)"
Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.