Summary:

Prism Deploy 6 (and above) uses a Microsoft SQL database as the repository for storing Channel information, such as Client status, group membership and detailed deployment results. Version 6 will install the Microsoft SQL database engine (MSDE) if necessary, or it can use an existing MSDE installation. Alternately, it can use (but will not install) a Microsoft SQL Server 2000 installation.

The technote is meant to give you a very brief overview of Microsoft’s SQL database architecture and its use. Microsoft makes available for download the “Books Online” resource for learning about SQL databases. We strongly recommend you download a copy: http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp. In addition, there are several links within this document that go directly to Microsoft’s site for in-depth and definitive articles.

The technote will briefly introduce the following topics:

• What is a SQL instance?
• MSDE (Microsoft Database Engine)
• MSDE Workload Governor
• When to upgrade from MSDE to SQL Server
• SQL licensing basics
• Differences between SQL Server 2000 Standard and Enterprise Editions

What is a SQL instance?

An instance is a unique installation of SQL. Each instance has four system databases that are automatically created (master, model, tempdb, and msdb). Each Prism Channel creates its own database within the instance. Each instance operates as a service (sqlsver.exe) that is separate from Prism Deploy’s services.

Up to 16 instances of either Microsoft Database Engine (MSDE) or SQL Server 2000 can be running at the same time on the same computer. Each instance is installed, started, stopped, configured, and patched independently of the other instances. Each instance can contain 32,767 databases. Multiple SQL instances are used by organizations that have several applications running on a SQL, but want them to run in isolation so that any problem in one instance will not affect the other instances.

MSDE (Microsoft Database Engine)

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_67ax.asp

MSDE is the redistributable, “free” version of SQL Server 2000 that will be installed if necessary with the Prism 6 installation expert. MSDE does not include the graphical database management tools such as Enterprise Manager or Query Analyzer that are included with SQL Server 2000. MSDE can support a maximum of 2 gigabytes (GB) of physical memory on the system where it is installed.

MSDE Workload Governor

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_0ciq.asp

Excerpts:

MSDE contains a workload governor designed to limit performance of an instance any time more than eight operations are active at the same time. The workload governor works by counting active operations (not active connections). When there are more than eight active operations at the same time in the same instance, the governor implements a slight wait before each logical read or write to a data file. For the amount of work typical in databases used by Prism Deploy, the cumulative effect of the waits is not noticeable. In systems that are reading and writing large amounts of data, the cumulative affect of all the waits slows the performance of the database engine. For customers with larger enterprises, the workload governor may hinder Prism’s performance, and upgrading to SQL Server 2000 may be recommended.

When to upgrade from MSDE to SQL Server 2000

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_5fs6.asp

Excerpts:

You should consider upgrading from MSDE to SQL Server 2000 when performance is constrained by the workload governor. The only factor that activates the workload governor is having more than eight active operations at the same time. There are two ways to find out how often the workload governor is activated:

1. Use the DBCC CONCURRENCYVIOLATION statement to monitor how often the workload governor is activated. This will also give you an idea of how many active operations are being processed by the instance at the times the governor is active. Books Online contains instructions for using this command.

2. The Event Viewer on the SQL server shows frequent message type 3629.

SQL Licensing Basics

http://www.microsoft.com/sql/howtobuy/faq.asp

If you decide to upgrade from MSDE to SQL Server 2000, New Boundary Technologies recommends that you purchase a processor license of either Standard or Enterprise Edition, rather than purchasing individual CALs (client access licenses) for SQL. Since the minimum number of Prism licenses you can purchase is 100, processor licensing makes more sense than buying up to 100 CALs.

Note: For more information on Microsoft SQL Server licensing and pricing, visit the Volume Licensing site.

Differences between SQL Server 2000 Standard and Enterprise Editions

http://www.microsoft.com/sql/techinfo/planning/SQLResKChooseEd.asp

Excerpts:

Standard Edition: This is an affordable option for small-sized and medium-sized organizations that do not require the advanced scalability, availability, performance, or analysis features of SQL Server 2000 Enterprise Edition. Standard Edition can be used on symmetric multiprocessing systems with up to 4 CPUs and 2 GB of RAM.

Enterprise Edition: It scales to the performance levels required to support the largest Web sites and enterprise online transaction processing (OLTP) and data warehousing systems. Its support for failover clustering also makes it ideal for any mission critical line-of-business application. Additionally, this edition includes several advanced analysis features. If you need more than 2 GB of physical memory (RAM) in the database server, you must use Enterprise Edition.