Database Considerations
Microsoft SQL Server Notification Services creates databases when you deploy an instance of Notification Services. Other than defining database properties in the instance configuration and in the application definition, you do not define databases; however, you must configure the Database Engine properly for optimal performance. Use the following guidelines when you configure the Database Engine.
Database Recommendations
For optimal performance, security, and recoverability, we recommend the following for Notification Services databases.
Log Files
Processing events and notifications creates high activity in log files. This activity can decrease read and write times for other operations, potentially reducing the performance of the entire system. To optimize performance, put log files on a dedicated physical disk when you define the instance database and application databases.
tempdb
Notification Services makes extensive use of the tempdb database. For example, every time events are matched to subscriptions, SQL Server creates temporary tables. Because tempdb is critical and is heavily used, it is important to minimize resizing by defining this database with an adequate initial size and to reduce resource contention by placing the database on a separate physical disk.
The tempdb database is re-created at its initial size each time SQL Server starts. If tempdb has an initial size that is too small and the database is set to auto-grow, the auto-grow process will use more system resources than the database uses during normal operation, which can impair system performance. You can avoid this overhead by altering the database to increase the initial size of tempdb and to increase the autogrow percentage.
For more information, see System Databases.
Indexes
Indexes are critical for optimal Notification Services performance. First, indexes can greatly improve the performance of joins. Second, after an event and subscription match occurs, data is retrieved from other tables to generate the actual notification with delivery instructions. Indexes can help SQL Server retrieve this data faster.
When Notification Services databases are created, you should define indexes in the ADF for most tables. However, additional indexes may enhance the performance of those queries.
To define additional indexes, you can examine the Transact-SQL code that is defined in the ADF, and either create your own indexes or use the SQL Server Database Engine Tuning Advisor, which suggests indexes.
For more information, see Designing Indexes.
Database Recovery Model
To ensure that data is not lost, back up all Notification Services databases frequently using the full recovery model. This allows you to create a complete database backup periodically, and then create differential and transaction log backups as required.
To ensure that the transaction log does not fill up, back up the database files and the log files on a frequent and regular schedule. For more information about database backups, see Backing Up Notification Services Databases and Operational Files and Backing Up and Restoring Databases in SQL Server.
You can also monitor database and transaction log files using System Monitor, a part of the Microsoft Windows Performance tool. For example, the SQL Server: Databases performance object contains the Percent Log Used performance counter to monitor how full the transaction log is. For more information about SQL Server performance objects, see Using SQL Server Objects.
User Connections
Connections are a critical resource. Data for Notification Services instances and application is stored in SQL Server databases and, as events are processed, applications request and receive data from SQL Server. If there are not enough connections available, errors can occur.
You can leave the Maximum Concurrent User Connections value to zero, meaning that the number of connections is limited only by the SQL Server maximum. For more information, see user connections Option.
Database Authentication
Notification Services supports Windows Authentication as well as SQL Server Authentication; it does not require SQL Server Authentication for any functionality. Windows Authentication has certain benefits that make it superior to SQL Server Authentication in most applications, such as secure authentication, password encryption, auditing, password expiration, minimum password length, and account lockout after multiple invalid login requests.
For these reasons, we strongly recommend that you use Windows Authentication. For more information about security configuration, see Securing Notification Services.
If you use SQL Server Authentication, you must specify a SQL Server user name and password to be used by the Notification Services engine to connect to the Database Engine. For more information, see Configuring SQL Server Permissions for an Instance of Notification Services.
See Also
Concepts
Database Resource Planning
Defining the Application Database
Defining the Instance Database
Planning a Notification Services System