Microsoft SQL Database Basics

New Boundary Support
Install/Upgrade - General Information

The information in this article applies to the following products:

• Prism Deploy 6.0 and above


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: 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)

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


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


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

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


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.

Also In This Category

On a scale of 1-5, please rate the helpfulness of this article

Not Helpful
Very Helpful
Optionally provide your comments to help us improve this article...

Thank you for your feedback!

Add Your Comments
Email Address:
RadEditor - HTML WYSIWYG Editor. MS Word-like content editing experience thanks to a rich set of formatting tools, dropdowns, dialogs, system modules and built-in spell-check.
RadEditor's components - toolbar, content area, modes and modules
Toolbar's wrapper  
Content area wrapper
RadEditor's bottom area: Design, Html and Preview modes, Statistics module and resize handle.
It contains RadEditor's Modes/views (HTML, Design and Preview), Statistics and Resizer
Editor Mode buttonsStatistics moduleEditor resizer
RadEditor's Modules - special tools used to provide extra information such as Tag Inspector, Real Time HTML Viewer, Tag Properties and other.
Verification Code:
Last Modified: 7 Years Ago
Last Modified By: New Boundary Support
Type: INFO
Rated 1 star based on 26 votes.
Article has been viewed 7.5K times.
Customer Support Software By InstantKB 2015-2
Execution: 0.000. 8 queries. Compression Disabled.