Id like to take some time today to explain some of the seemingly arbitrary limits placed on the XML data type, specifically those related to ID/IDREF validation, complex XML Schema types, the depth limit for XML data, and the enigmatic "XSD schema too complex" error.
ID/IDREFValidation
If your typed XML document has attributes of type xs:ID and/or xs:IDREF, SQL Server will enforce referential integrity on these attributes: Within a given document, no two attributes of type xs:ID may have the same value, and all attributes of type xs:IDREF must have the same value as some attribute of type xs:ID.
This requires the validator to remember which ID values it's seen. Since the validator does only one pass, it must also remember any IDREF values for which it has not yet encountered a corresponding ID. It is thus possible to construct an XML document which requires an arbitrary amount of memory to validate correctly. In order to prevent denial of service attacks, we capped the amount of memory available for this purpose at one megabyte. If you try to validate a document which exceeds this limit, validation will fail with error 6969:
ID/IDREF validation consumed too much memory. Try reducing the number of ID and IDREF attributes. Rearranging the file so that elements with IDREF attributes appear after the elements which they reference may also be helpful.
There's no simple way to describe the precise conditions necessary to produce this error, but the relevant factors are the number of ID and forward-referencing IDREF attributes and the lengths of their values. The cap is the same for the 32-bit and 64-bit versions of SQL Server, so there are some documents which will validate on the 32-bit version but fail to validate on the 64-bit version due to the larger pointer size.
ComplexXMLSchemaTypes
When submitting a schema to be added to an XML Schema Collection, you may see message 6998:
Type or content model '[TypeName]' is too complicated. It may be necessary to reduce the number of enumerations or the size of the content model.
When a type is needed for validation, the validator loads its definition from metadata and compiles it into a format suitable for quick validation. In order to prevent any one type from using too much memory, SQL Server caps the size of a compiled type at one megabyte. SQL Server compiles all types and performs this check when the schema is imported in order to avoid accepting types which exceed the limit.
As with the ID/IDREF limit, there's no simple way to describe precisely the conditions necessary to exceed this limit. Having a large number of attributes, a content model with many particles (xs:sequence, xs:choice, xs:all, xs:element, or xs:any), or many enumeration facets are the most likely causes. Note that the properties inherited from the base type or imported via xs:group or xs:attributeGroup references are expanded in the compiled type definition, so it's possible for a type to exceed the limit just by adding a few attributes to its base type, if the base type is near the limit.
The types of child elements, however, do not contribute to the limit. For example, you should have no problem defining a type whose content model contains several child elements, each of which has a different type whose compiled representation is 500K. If you find yourself running up against this limit, it may be helpful to split the type's properties between two or more sub-types.
"Schema Too Complex"
When adding a schema to an XML Schema Collection, you may occasionally run into error 2362:
XSD schema too complex.
This is somewhat misleading; what it actually means is that SQL Server is running low on stack space. We rely heavily on recursion for parsing and semantic validation of XML Schema documents, and in rare (and usually intentionally pathological) cases, this presents a danger of stack overflow, which would kill the process and crash the server. To prevent this, we check the remaining stack space at recursion points and abort the transaction if it's low enough to cause concern.
If you encounter this error and your schema is not intentionally pathological, you may be able to make some semantically insignificant changes that will allow SQL Server to process your schema. The most common causes of recursion are nesting and forward references. If you have several anonymous types nested in the Russian-doll style, it may help to unnest them and move the local element or type definitions up to the global level. Additionally, it may help to rearrange schema components to eliminate forward references--that is, try to make sure that component definitions precede their references in document order.
XMLDepthLimit
Finally, SQL Server limits the depth of any XML instance, typed or untyped, to 128 levels. Conversion of a string with deeper nesting to XML will fail with error 6335:
XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.
We impose this limit in order to guarantee that we will be able to create an XML index for any XML column. In SQL Server, The primary key of an XML index consists of the primary key of the base table and the ordpath. The maximum length for an index key in SQL Server is 900 bytes, so the combined length of the base table's primary key and the ordpath must be 900 bytes or less. We decided to impose a limit of 128 bytes on the primary key of the base table, leaving 772 bytes for the ordpath. Based on the properties of ordpath, we decided that 128 levels would be a good upper limit to ensure that the ordpath never exceeds the maximum size.
We'd like to know if any of these limitations are causing problems, so please leave a comment if you've run into any of these. That the depth limit causes problems for SHOWPLAN_XML is a known issue, but we're not aware of practical problems with any of the other limitations.
SQL Server 2005 Express Edition Overview
SQL Server 2005
Rajesh George
November 2005
Applies to:
Microsoft SQL Server 2005 Express Edition
Microsoft Visual Studio 2005
Summary: SQL Server Express Edition, a free product based on SQL Server 2005 technology, includes the unique Application XCopy feature, and networking and security that differ from other SQL Server 2005 editions. This article discusses these topics and the integration of SQL Server Express with Visual Studio 2005. We also compare this product with the existing Microsoft free databases like MSDE and Jet. (19 printed pages)
Contents:
Introduction
Target Scenarios
Key Features in SQL Server Express 2005 Express Edition
Engine Specifications
Tools Support
Networking Support
Data Access Support
Security
Replication Support
SQL Service Broker
User Instances
Setup and Deployment
Language Support
Support Considerations
Hardware/Software Requirements
64-bit Support
Features Not Present in SQL Server Express
Visual Studio Integration
Comparison with Other Free Microsoft Databases
Comparison with MSDE
Comparison with Jet
Conclusion
Introduction
SQL Server Express is a free and easy-to-use database product that is based on SQL Server 2005 technology. It is designed to provide a database platform that offers superior ease of use, enabling fast deployments for its target scenarios. The ease of use starts with a simple and robust graphical user interface (GUI) setup that guides the user through the installation process. The GUI tools that come for free with SQL Server Express include SQL Server Management Studio Express Edition (Technical Preview version available at launch), Surface Area Configuration Tool, and SQL Server Configuration Manager. These tools simplify the basic database operations. The design and development of database applications are made easier by the integration with Visual Studio projects. In addition, I introduce the ability to deploy database applications by moving them like typical Windows files. The servicing and patching are also simplified and automated.
SQL Server Express uses the same reliable and high-performance database engine as the other versions of SQL Server 2005. It also uses the same data access APIs such as ADO.NET, SQL Native Client, and T-SQL. In fact, it is differentiated from the rest of the SQL Server 2005 editions only by the following:
Lack of enterprise features support
Limited to one CPU
One GB memory limit for the buffer pool
Databases have a 4 GB maximum size
Features like Auto-Close and the ability to copy databases as files are enabled by default in SQL Server Express, while the high availability and business intelligence features are absent. It is very easy to "scale up" if that becomes necessary, as Express applications will work seamlessly with SQL Server 2005 Workgroup, Standard, or Enterprise editions. The Web download enables a free, fast, and convenient deployment.
This white paper covers the components and features unique to SQL Server Express, such as Application XCopy (User Instances), networking, and security. It also provides guidelines for common usage scenarios. In addition, I cover the ease of data application development using Microsoft Visual Studio 2005. This discussion is targeted at:
Hobbyists and other nonprofessional developers
Database developers, administrators, and operations specialists
Business analysts
Technical decision makers
Target Scenarios
SQL Server Express was developed with two distinct uses in mind. The first is as a server product, especially as a Web server or a database server. The second is as a local client data store where the application data access does not depend on the network. Ease of use and simplicity are key design goals.
The three main usage scenarios for SQL Server Express are:
Nonprofessional developers building Web applications
ISVs redistributing SQL Server Express as a low-end server or client data store
Hobbyists building basic client/server applications
SQL Server Express provides an easy-to-use and reliable database platform that is "feature-rich" to cover these scenarios. Special consideration is given to the ease and reliability of setup and deployment to make it easy for ISVs to use and redistribute.
Key Features in SQL Server 2005 Express Edition
SQL Server Express uses the same database engine as the rest of SQL Server 2005, and all the programmatic features are the same. For additional information in these areas, see SQL Server 2005 Books Online. The features that are unique to SQL Server Express and/or have higher customer impact are described in detail below.
Engine Specifications
The SQL engine supports 1 CPU, 1 GB RAM, and a 4 GB database size. This mechanism permits easy differentiation from other SQL Server 2005 editions by having well defined cut-off points. Otherwise, there is no workload throttle and the engine performs as in other editions. There is no hard-coded limit to the number of users that can attach to SQL Server Express but their CPU and memory limits impose practical limits on the number of users that can achieve acceptable response times from a SQL Server Express database.
SQL Server Express can install and run on multiprocessor machines, but only a single CPU is used at any time. Internally, the engine limits the number of user scheduler threads to 1 so that only 1 CPU is used at a time. Features such as parallel query execution are not supported because of the single CPU limit.
The 1 GB RAM limit is the memory limit available for the buffer pool. The buffer pool is used to store data pages and other information. However, memory needed to keep track of connections, locks, and so on is not counted toward the buffer pool limit. It is therefore possible that the server will use more than 1 GB in total, but it will never use more than 1 GB for the buffer pool. Address Windowing Extensions (AWE) or 3 GB data access is not supported or needed.
The 4 GB database size limit applies only to data files and not to log files. However, there are no limits to the number of databases that can be attached to the server. There are some minor changes to the startup of SQL Server Express. User databases are not automatically started, and the distributed Transaction Coordinator is not automatically initialized. For the user experience, though, there should be no difference other than a faster startup. Programmers intending to use SQL Server Express are recommended to keep these changes in mind when designing their applications.
Multiple SQL Server 2005 Express installations can coexist on the same machine along with other installations of SQL Server 2000, SQL Server 2005, or Microsoft Desktop Engine (MSDE). In general, it is best that SQL Server 2000 instances be upgraded to Service Pack 4 (SP4). The maximum limit to the number of SQL Server Express instances is 16 on the same machine. These instances must be uniquely named for the purpose of identifying them.
SQL Server Express by default installs as a named instance called SQLEXPRESS. This particular instance may be shared among multiple applications and application vendors. We recommend that you use this instance unless your application has special configuration needs.
The APIs available to program against SQL Server Express are the same as those for SQL Server 2005, so that users have a seamless experience if they choose to move to other editions of SQL Server 2005. All of the new features in SQL Server 2005, such as common language runtime (CLR) integration, new data types such as VARCHAR(MAX) and XML, user-defined types, and user-defined aggregates are supported. Also, SQL Server Express databases can attach to SQL Server 2005, and applications programmed with a SQL Server Express instance will work equally well with a SQL Server 2005 instance. Replication and SQL Service Broker functionality is also available, and will be described in detail later.
Tools Support
SQL Server Express is designed to be easy to use, and the graphical user interface (GUI) tools make it easy even for database novices to use the basic database functionalities in SQL Server Express. A new GUI tool called SQL Server Management Studio Express Edition will be freely available as a separate Web download. SSMS-EE will allow easy database management and query analysis capabilities and will be freely redistributable.
SSMS-EE will support connections to SQL Server Express and other SQL Server 2005 editions, SQL Server 2000, and MSDE 2000. A simple connection dialog will guide the user through the selection of the instance and the authentication methods to be used. Both local and remote connections will be possible. Object Explorer will enumerate and display the common objects used, such as the instance, tables, stored process, and so on, in a hierarchical manner and will help the user visualize access to the database.
All the database management functionalities will be available by invoking the right-click context menu from Object Explorer. SSMS-EE features like creating and modifying databases, tables, views, logins, and users are identical to the full SQL Server Management Studio available in other editions. This allows you to immediately leverage the skills you learn with SSMS-EE once you upgrade to the full version of SSMS.
Many database users prefer to manage their servers using T-SQL, since this approach offers finer-grained control than using the graphical user interface. The Query Editor in SSMS-EE will allow users to develop and execute T-SQL statements and scripts. The Query Editor will have rich features such as keyword color-coding and a results pane that returns results in a data grid. The error messages, if any, will also be shown in the results pane. SSMS-EE supports all the query editor functionality of SSMS including graphical query plans.
Figure 1. Query Editor (click image to enlarge)
SSMS-EE will be distributed through a separate Web download. Users will simply click the executable to launch the setup. If SSMS is already installed, SSMS-EE will fail to install because it is unnecessary. Similarly, users will be able to uninstall SSMS-EE by re-launching the setup or using the Add or Remove Programs menu in Control Panel.
Some of the tools that ship with SQL Server Express include the SQL Computer Manager, SQL Command, and BCP. SQL Computer Manager is used for starting and stopping the SQL Server service, and for enabling and disabling network protocols. SQL Command is used for connecting and querying using the command line, while BCP is used for bulk copying data.
SQL Computer Manager allows administrators to configure basic service and network protocol configurations. This is the SQL Server 2005 equivalent of older tools like Server Network Utility, Client Network Utility, and Service Manager. It is not intended to adjust performance characteristics or operations of SQL Server.
In Computer Manager, there is a node for "Microsoft SQL Servers" under which all services, server network protocols, and client network protocols will exist. The services node lists all the available Express services and gives details like the name of the server instance, the service status, and the startup type. You can select a particular service and perform operations like start, stop, pause, or restart of the service. The Server Network Protocols node enumerates the list of protocols for each instance on the machine. Right-clicking the protocol (e.g., TCP) will allow you to enable or disable the protocol, or change its properties. Similar options are available in the client network protocols node. Please note that the Server Network Protocols node works directly on the protocol settings on the SQL Server instance, while the Client Network Protocols node deals with the protocol settings of clients like MDAC or SQL Native Client provider. The Client Network Protocols node also allows you to create an alias that is an alternate name for SQL Server, and can contain information such as the server name, protocol used, connection string, and encryption information.
SQL Commandis an OLE DB version of the existing osql tool. It not only attempts to maintain functional compatibility with osql, but also includes support for new SQL Server 2005 data types. All command line options output information to standard output, except error messages that may appear when an operation failed. The SqlCmd -? command displays the syntax summary of sqlcmd switches.
SQL Computer Manager and SQL Command are the same for SQL Server Express as for the other editions of SQL Server 2005.
Networking Support
Only the shared memory connection type on the local machine is accessible by default for SQL Server Express, although the user can explicitly turn on other supported protocols such as TCP/IP and Named Pipes. VIA and HTTP protocols are not supported in SQL Server Express. With only shared memory available by default, connections from a remote machine to SQL Server Express will fail unless the networking is turned on. To turn networking on, there are the following options:
Use the Surface Area Configuration tool to enable networking and enable and start the SQLBROWSER service.
Use SQL Server Configuration Manager to enable relevant protocols and start SQL Browser. Figure 2 shows the usage of this tool to enable the networking protocols.
Use DISABLENETWORKPROTOCOLS=0 in the setup command line, if you know in advance that networking support is needed.
Use SMO-based scripting to enable the protocols.
SQL Browser is a new service in SQL Server 2005 that is used to identify the ports that named instances listen on. Since shared memory does not use it, this service is turned off in SQL Server Express by default. This means that the user will have to start this service so that network access can work.
Note One interesting fact is that SQL Browser listens on UDP port 1434. However, pre-SP3 versions of SQL Server 2000 holding port UDP 1434 may result in failure of SQL Browser name resolution, since they may refuse to give up the port. The workaround is to upgrade all SQL Server 2000/MSDE instances on the machine to SP3 or higher.
Figure 2. Using SQL Sever Configuration Manager to enable the protocols (click image to enlarge)
Data Access Support
SQL Server 2005 Express supports the same native and managed providers as the rest of SQL Server 2005. This has the huge advantage that an application written for SQL Server Express will work seamlessly with other SQL Server editions.
SQL Server 2005 Express supports ADO.NET for managed access. We recommend using the SqlClient data provider for developing new applications, since most of the application XCopy features are available only with SqlClient. The ADO.NET 2.0 data providers (available in Visual Studio 2005) support the new SQL Server data types like varchar(MAX) and XML, as well user-defined types.
Starting with SQL Server 2005, the logical sessions in the server are detached from the physical connections. Both the client and server transport layers are updated to provide multiplexing capabilities so that multiple logical sessions can go over a single physical connection. This enables clients to have multiple active result-sets (MARS) against the same connection. Note that MARS is not targeted at removing the need for multiple connections in general. MARS is off by default in SQL Server 2005, and enables you to interleave SQL operations. For example, you can operate on a result-set and also be able to execute statements against the database while processing it, without opening a new connection. MARS can effectively replace server cursors in many of these scenarios and is particularly useful when the data retrieval and update operations all happen within the same transaction.
SQL Server 2005 clients also support asynchronous Input Output (I/O) so that the application threads will no longer be blocked by data transfer operations, and the client application is as responsive as possible. In this model, the I/O calls will return immediately and the completion will be asynchronously notified to the application. This enables the application to continue with other processing and check for the I/O completion events at a later time.
The data access components in the SQL Server 2005 time frame will be divided into two parts: the MDAC stack that is part of the operating system, and the SQL Native Client provider giving SQL Server specific data access library for native data access. The SQL Native Client is targeted at SQL OLEDB, SQL ODBC, and ADO customers who are writing new applications or enhancing existing applications to take advantage of new SQL Server 2005 functionality.
SQL Native Client contains the SQL Server 2005 additions to the SQL OLE DB, SQL ODBC, SQL BCP, and SQL Networking Interface. The SQL Server 2005 features such as MARS, User Defined Types, XML data type, etc. are available via SQL Native Client only for C or C++ programmers. SQL Native Client is comprised of new components (new class IDs) that are separate and distinct from the existing components in MDAC. For example, the OLE DB Progid used is SQLNCLI; the ODBC Driver Name is SQL Native Client; and the Header file used is SQLNCLI.h.
SQL Native Client comes as a single dll (SQLNCLI.dll). Not being an operating system component means simplified servicing, and easier redistribution and deployment of applications. There will be an updated SQL Native Client release with each new release of SQL Server and the applications can explicitly change their configuration manifest to use a later version of this provider.
There are some interesting dynamics based on the interactions between MDAC providers and the SQL Native Client providers. For instance, MDAC 2.5, 2.7, or 2.8 cannot connect to SQL Server 2005 using Shared Memory. This affects any native application using SQL OLE DB or SQL ODBC and includes not only existing native applications, but also the managed OLE DB or ODBC applications since they use MDAC internally. Typically for SQL Server, if the shared memory connection fails, networking protocols such as TCP/IP are used. However, for SQL Server Express, since the networking is turned off by default, these applications would simply fail to connect. The workaround is to change the apps to use the SQL Native Client provider or enable networking Transmission Control Protocol (TCP) and start SQL Browser.
Security
For SQL Server Express, one of our goals is to provide secure defaults for the different components. For instance, the networking protocols such as TCP/IP and Named Pipes are turned off. SQL Browser service is not started unless the user explicitly asks for this in the setup command line. The SA or System Admin account is disabled by default if Windows Authentication is used. Normal users on the machine have almost no privileges on the SQL Server Express instance. A Local administrator on the server must explicitly grant relevant permissions for normal users so that they can use SQL functionality.
The SA login is a special login in SQL Server, and is a member of the system administrator (sysadmin) role. It is used predominantly in configurations that use the SQL authentication mode, and is not used in Windows authentication mode. SQL requires a strong SA password for security reasons, and during GUI installs and silent SQL authentication mode installs, the user must provide a strong SA password. However, for silent Windows authentication installs, the SA password is not a requirement. The reason is that when using Windows authentication mode, the silent SQL Server Express setup provides a random strong SA password if the password is not specified by the user. The setup also disables the SA account in this case, so that you must explicitly enable SA at a later stage using T-SQL if you wish to use it. This is done so that the ISVs do not have to provide the password when using Windows authentication, so that the mass deployment scenarios are not blocked. In future releases, this functionality may be extended to GUI Windows-based installs also.
Replication Support
Replication allows the user to maintain copies of data at multiple sites using a publisher-subscriber model with synchronization of the copies at user-defined intervals. SQL Server Express supports subscriptions to merge, snapshot, and transactional publications, but does not permit publications itself. Replication subscriptions in SQL Server Express are fully functional. However, since SQL Server Express does not ship SQL Agent, scheduling the subscriptions is more challenging. You can sync SQL Server Express subscriptions by the following methods:
Programmatically sync using Replication Management Objects (RMO).
Use Windows Sync Manager for scheduled sync.
SQL Service Broker
SQL Service Broker (SSB) is the new reliable messaging infrastructure in SQL Server 2005. The service programs can choose to communicate through peer-to-peer message exchange contracts called dialogs. This feature is accessible via extensions to the T-SQL language.
