Windows internal database accessing

Problem:

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

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.

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.

 

Reference: Internal DB Access

Database Full, Differential, Incremental Backups

Type	Definition	Benefits	Drawbacks
Full Backup	A complete backup of everything you want to backup.	Restoration is fast, since you only need one set of backup data.	The backing up process is slow. High storage requirements.
Differential Backup	The backup software looks at which files have changed since you last did a full backup. Then creates copies of all the files that are different from the ones in the full backup. If you do a differential backup more than once, it will copy all the files, or parts of files that have changed since the last full backup, even if you already have identical copies of those files in a previous differential backup. For restoring all the data, you will only need the the last full backup, and the last differential backup.	Faster to create than a full backup. Restoration is faster than using incremental backup. Not as much storage needed as in a full backup.	Restoration is slower than using a full backup. Creating a differential backup is slower than creating an incremental backup.
Incremental Backup	The backup software creates copies of all the files, or parts of files that have changed since previous backups of any type (full, differential or incremental). For example if you did a full backup on Sunday. An incremental backup made on Monday, would only contain files changed since Sunday, and an incremental backup on Tuesday, would only contain files changed since Monday, and so on.	This method is the fastest when creating a backup. The least storage space is needed.	Restoring from incremental backups is the slowest because it may require several sets of data to fully restore all the data. For example if you had a full backup and six incremental backups. To restore the data would require you to process the full backup and all six incremental backups.

Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42 profile name is not valid Error came when execute test mail

Error:

We need to check the profile name by executing query,

SELECT [profile_id]
,[name]
,[description]
,[last_mod_datetime]
,[last_mod_user]
,’EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘ ‘ ‘ + name + ‘ ‘ ‘,
@recipients = ‘ ‘abc@abc.com’ ‘,
@subject = ‘ ‘ Test’ ‘,
@body  = ‘ ‘Message’ ‘,
@body_format = ‘ ‘HTML’ ‘;’ AS TestSQL
FROM [msdb].[dbo].[sysmail_profile]

All about system databases in SQL Server

master

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

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.

model

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.

msdb

The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and recording operators.

Enable Filestream in SQL Server

Error:

To enable and change FILESTREAM settings

1. 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.
2. In the list of services, right-click SQL Server Services, and then click Open.
3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
4. Right-click the instance, and then click Properties.
5. In the SQL Server Properties dialog box, click the FILESTREAM tab.
6. Select the Enable FILESTREAM for Transact-SQL access check box.
7. 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.

1. 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.

1. Click Apply.

1. In SQL Server Management Studio, click New Query to display the Query Editor.

1. In Query Editor, enter the following Transact-SQL code:

EXEC sp_configure filestream_access_level, 2

RECONFIGURE