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?
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
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
sqlcmd -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query –E
You can then run your administrative T-SQL scripts once connected to this database instance.
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.
Reference: Internal DB Access
We need to check the profile name by executing query,
@profile_name = ‘ ‘ ‘ + name + ‘ ‘ ‘,
@recipients = ‘ ‘firstname.lastname@example.org’ ‘,
@subject = ‘ ‘ Test’ ‘,
@body = ‘ ‘Message’ ‘,
@body_format = ‘ ‘HTML’ ‘;’ AS TestSQL
The master database records all of the system level information for a SQL Server system. It records all login accounts and all system configuration settings. master is the database that records the existence of all other databases, including the location of the database files. master records the initialization information for SQL Server; always have a recent backup of master available.
tempdb holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQL Server. tempdb is a global resource; the temporary tables and stored procedures for all users connected to the system are stored there. tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database. Because temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down, there is never anything in tempdb to be saved from one session of SQL Server to another.
By default, tempdb autogrows as needed while SQL Server is running. Unlike other databases, however, it is reset to its initial size each time the database engine is started. If the size defined for tempdb is small, part of your system processing load may be taken up with autogrowing tempdb to the size needed to support your workload each time to restart SQL Server. You can avoid this overhead by using ALTER DATABASE to increase the size of tempdb.
The model database is used as the template for all databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, then the remainder of the new database is filled with empty pages. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.
The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and recording operators.
To enable and change FILESTREAM settings
- On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
- In the list of services, right-click SQL Server Services, and then click Open.
- In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
- Right-click the instance, and then click Properties.
- In the SQL Server Properties dialog box, click the FILESTREAM tab.
- Select the Enable FILESTREAM for Transact-SQL access check box.
- If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
- If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.
- Click Apply.
- In SQL Server Management Studio, click New Query to display the Query Editor.
- In Query Editor, enter the following Transact-SQL code:
EXEC sp_configure filestream_access_level, 2