Summary: SQL Server Express, 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. These topics and the integration of SQL Server Express with Visual Studio 2005 are discussed. Compare this product with the existing Microsoft free databases like MSDE and Jet. (20 printed pages)
ContentsIntroduction
Target Scenarios
Key Features in SQL Server Express 2005 Technical Preview
Engine specifications
Tools support
Networking support
Data Access Support
Security
Replication support
SQL Service Broker
Application XCopy
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
Copyright
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 simplicity of use, enabling the fastest 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 Express Manager (Alpha version) and Computer Manager. These tools simplify the basic database operations and are for hobbyist developers. 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 4GB 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 Standard or Enterprise editions. The Web download enables a free, fast, and convenient download.
This white paper covers the components and features unique to SQL Server Express, such as Application XCopy, networking, and security. It also provides guidelines around the common usage scenarios. In addition, the ease of data application development using the Microsoft Visual Studio 2005 is covered. This discussion is targeted at:
· Hobbyists and other non-professional developers
· Database developers, administrators, and operations specialists
· Business analysts
· Technical decision makers
Target ScenariosSQL 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:
· Non-professional 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 Express 2005 Technical PreviewSQL 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 specificationsThe SQL engine supports 1CPU, 1GB RAM and a 4GB 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.
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 1GB 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, etc. is not counted toward the buffer pool limit. It is therefore possible that the server will use more than 1GB in total, but it will never use more than 1GB for the buffer pool. Address Windowing Extensions (AWE) or 3GB data access is not supported.
The 4GB 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 DTC is not automatically initialized. For the user experience, though, there should be no difference other than a faster startup. Applications planning 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 SP3a or higher. The maximum limit to the number of SQL instances is 50 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 is to be shared among multiple applications and application vendors. We recommend that you use this instance unless your application has special configuration needs. Some configuration needs, such as the requirement of the Secure Socket Layer (SSL) authentication, affect the installation as a whole and hence need a separate named instance. In all other cases, the shared instance should suffice. Another advantage of using the shared instance is that the application vendors need not worry about installing SQL Server Express along with the application, which simplifies application installation.
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 supportSQL 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 Express Manager (XM) will be freely available as a separate Web download. XM will allow easy database management and query analysis capabilities, will have a small download size, and will be freely redistributable.
XM will support connections to SQL Server Express and other SQL Server 2005 editions, SQL Server 2000, and MSDE 2000. A simplified 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 using XM. Object Explorer will enumerate and display the common objects used, such as the instance, tables, stored process, etc., 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. Some of the database management options to be exposed include creating and modifying databases, tables, logins, and users. Many of these common database operations will be available as task wizards that guide the user through the process, while many others are available as tabbed window documents. For instance, XM will provide a New/Edit Database document for creating new databases and editing existing databases.
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 XM 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.
Figure 1. Workbench Query Editor
XM will be distributed through a separate Web download—users will simply click the executable to launch the setup. Similarly, users will be able to uninstall XM by re-launching the setup or using the Add/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 is an MMC snap-in that 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 will be 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 Command is 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 supportOnly the shared memory 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 SQL Computer 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 Computer Manager to enable the protocols
Data Access SupportSQL 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 on 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 statement against the database while processing it, without opening a new connection. MARS can effectively replace 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 also supports 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 is available via SQL Native Client only for C or C++ programmers. SQL Native Client is comprised of new components (new class-id's) 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 the 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 the 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 is used. However, for SQL Server Express, since the networking is turned off by default, these applications would simply fail to connect. The work around is to change the apps to use the SQL Native Client provider or Enable networking Transmission Control Protocol (TCP) and start SQL Browser.
SecurityFor 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 or the Express Manager tool 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 supportReplication 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 subscription 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).
· Express Manager exposes on-demand sync, but this feature will not be included in SQL Server Express Technical Preview, but will be freely available as a separate Web download.
· Use Windows Sync Manager for scheduled sync.
SQL Service BrokerSQL 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.
SQL Server Express can use Service Broker only in combination with other SQL Server 2005 editions. If SQL Server Express receives a Broker message from another Express instance, and if another SQL Server 2005 edition has not processed the message, then the message is dropped. So the message can originate from an Express instance and end up at one, but it must be routed through a non-Express instance if that is the case. You can check a Message Drop trace event that is accessible from the Profiler or use tracing stored procedures to track this type of occurrence. Typically the error message associated with the dropped message includes verbiage to this effect: "This message has been dropped due to licensing restrictions."
Consider a scale-out sample scenario. The application has 100 SQL Server Express instances deployed on cash registers. They are connecting to a non-SQL Server Express server in the back end. This scenario works as long as the back-end server is involved in all the dialogs. The SQL Server Express instances cannot engage in successful dialogues with each other without going through the back end.
Application XCopyApplication XCopy is a new feature in SQL Server Express that provides the ability to treat databases like files. The local database can now be moved, copied, or e-mailed along with the application. At the new location, no additional configuration is needed to make it work. There are three main features that enable the Application XCopy support in SQL Server Express: the AttachDBFilename option in the connection string, the lack of a requirement to specify the logical database name, and Auto-Close.
There are some assumptions implicit in Application XCopy. We assume that SQL Server Express with instance name SQLEXPRESS is already installed on the machine. Also this is a managed-stack-only solution, and you must use the .NET SQL Server Data Provider to develop XCopy-deployable applications. In other words, you cannot use SQL Native Client or MDAC to develop applications that can be used by Application XCopy. In SQL Server Express Technical Preview, you have to be an administrator on the box or a system administrator on SQL Server to use this functionality.
Typically the application developer would copy just the user database and the log file along with the application. However, in SQL Server there are some configuration entries present in a special system database called master. The features that rely on entries in master include SQL authentication (we advise using Windows authentication whenever possible). If your application relies on any entries in master, the application developer will have to make sure these configuration entries are replicated in the target system. One way to ensure this is to include an SMO, DMO, or T-SQL configuration script along with the application that runs when the application is installed on the target machine. For most applications running with Windows authentication, replicating information from the master database should not be an issue.
AttachDBFileNameYou can specify a relative or absolute file path for the AttachDBFileName connection string entry. The specified database file is attached when the connection is opened and this database is used as the default database for that connection. If the database is already attached when AttachDBFileName is invoked, then attach does nothing. This key word supports a special string called |DataDirectory| that points at runtime to the data directory of the application where database files are stored. This special string should be at the beginning of the file path, works only against a local file system, and checks are done for \..\ syntax so that the final file path is not higher than the directory pointed to by the substitution string.
There are certain variations in the usage of log files when using AttachDBFileName. The log file name must be of the format: <database-File-Name>_log.ldf, and there is no option to specify the log file name when using AttachDBFileName. For instance, if the database file name is myDb.mdf and is at location c:\myApp, then the log file name should be myDb_log.ldf. If SQL cannot find this file in the same directory as the database file, then a new log file will be created during attach. This means that we do not support user-defined log file names when using AttachDBFileName.
SQL Server supports using multiple data and log files for a database and these files can be distributed in multiple file groups. This is not supported in the AttachDBFileName syntax. Also the network share, HTTP paths, or Universal Naming Conventional (UNC) remote databases are not supported.
Logical database nameWhen the logical database name is not specified in the connection string, an automatic name is generated for the database that is being attached. The name generated is based on the relative file path of the .mdf file. For instance, if the file is at c:\myDocuments\Myapp\myDB.mdf, the logical database name will be based on the full path, and is obtained by invoking the built-in intrinsic function fn_GenDBNameFromPath. If the file path is longer than 128 characters, then this function will use the existing path and a hash to generate the logical database name. This is new in SQL Server Express, since not specifying the database name in SQL Server 2000 would result in an error. The supported syntax includes database=; or initial catalog=; or the user can also omit them totally in the connection string.
This feature is useful when moving or copying the database on the same machine, since the file-path–based logical name is unique. If this feature was not present, there will be naming conflicts within SQL Server if the same logical name is used to open databases in two different directories. Application XCopy is also supported across machines.
Note that it is still possible to explicitly specify the logical database name using the keywords database or Initial Catalog. Users may want to explicitly specify the logical database name when using replication, SQL Service Broker, multiple-part names in T-SQL queries, or cross-database scenarios.
Auto-CloseAuto-Close existed in SQL 2000 and is enabled by default in SQL Server Express. This feature releases the file locks on the user databases when there are no active connections to it. Thus, the database is ready to be moved or copied after the application that uses it is closed.
However, from the user's perspective, Auto-Close does not always work, especially during unexpected client termination or when using a connection pool. For unexpected client terminations, the connections are cleaned up eventually by the time-out mechanism. When connection pooling is enabled, connections are not actually closed when the user invokes the connection close; they are returned to the pool instead. In order to close all connections, typically the application domain has to be shut down or all the open connections have to be closed. ADO.NET also contains functions like ClearPool for manual resets.
Setup and DeploymentSQL Server Express setup is a simplified version of the SQL Server 2005 process. It is robust, reliable, and supports both GUI and the silent install modes. Only Windows Installer-based (.msi) technology is supported.
The SQL Server Express Web download is packaged as a single executable using Microsoft installation technology called SFXCab. The download size is less than 36MB. Double-clicking the .exe automatically starts the install process. The SQLRun.msi contains 50 product codes within it so that it can support 50 SQL instances on a single machine. (SQLRun.msi is the main .msi for SQL Express install. Others such as msxml3.msi, and sqlrun_tools.msi, sqlrun_sql.msi, etc., are part of the Web download.) ISVs should not worry about the particular product code they are using. Changing the product code from one of the 50 supported ones makes the .msi not serviceable by Microsoft, and you are strongly recommended not to change the product code.
The Visual Studio 2005 version of the .NET Framework is a prerequisite for the installation of SQL Server Express, and the setup produces an error if the correct version is not found on the machine. The setup has a component called System Configuration Check (SCC) that gets run before the actual setup begins. This component verifies that the system meets the minimum criteria for installation; the criteria includes minimum hardware requirements, minimum operating system requirements, pending reboot requirements, etc. Many users could run into the pending reboot requirement failure, which means that the user has to reboot the machine before the SQL Server Express installation can proceed. The main advantage of the SCC is that common installation errors due to bad machine configuration are identified early and appropriate warnings and error messages are given to the user. For instance, the SCC will give a warning but proceed with the install if only 256MB memory is present on the machine. A sample screen shot of SCC is given in Figure 3 below.
Figure 3. System Configuration Check
Once the setup starts, you can see that the screens are simplified to be accessible to developers of all levels of experience. For instance, some complicated dialogs such as those handling collations and service accounts are hidden by default, using the Hide Advanced Configuration check box. If the user clears this option, then the advanced configuration dialogs are shown. Setup logging is turned on by default so that the user can easily figure out any installation failures by using the log files created in the \Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG directory.
SQL Server Express installs as a named instance called SQLEXPRESS by default and we recommend that applications use the shared SQLEXPRESS instance. Also by default, all the features are turned off in SQL, so you must explicitly choose the components you want to install. The feature component tree screen is shown below, where you will manually check the items to be installed. The setup command line ADDLOCAL=All will select all the components for installation. When selecting the components, you should realize that repair and maintenance via Add/Remove Programs is not supported in the SQL Server Express Technical Preview release. The feature component tree GUI entries, as well as the corresponding command line options, are shown in Table 1 below.
Table 1. SQL Server Express Feature Components tree
Feature Components GUI
Feature Components Command Line Parameters: Provide a comma-delimited list with no spaces of the features to install after ADDLOCAL.
SQL Server Database Services
SQL_Engine
Data Files
SQL_Data_Files
Replication
SQL_Replication
Client Components
Client_Components
SQL Command Line Tools
Server_tools
Connectivity components
Connectivity
Programming Models
ProgrammingModels
Management Tools
SQL_Tools90
SQL Server Computer Manager
SQL_ComputerManagement
The GUI setup mode is recommended when you are installing the product yourself. Typically, you'll get SQL Server Express as a Web download from Microsoft site, or from the application CD.
We recommend using the silent setup mode when you are installing SQL Server Express as a part of an application install. In this mode, you can verify if the instance name SQLEXPRESS is present on the machine, and if not, install it silently. There are two easy ways of identifying SQL Server Express instances on the machine:
· ServerInstance in SQL Windows Management Instrumentation (WMI) Provider contains the SQL edition and version information.
· The Select SERVERPROPERTY( "ENGINEEDITION") T-SQL command returns a value of 4 for SQL Server Express.
You can use a Setup bootstrap or Visual Studio "Click Once" technology to include the SQL Server Express Microsoft Windows Installer (MSI) or Web download exe, which is especially helpful for ISVs. You should not proceed with installing SQL Server Express if the SQLEXPRESS instance is already present. One important thing to keep in mind is that .msi chaining is not supported by Microsoft, and should not be used. Also, merge modules are not supported with SQL Server Express. Any existing MSDE merge module customers planning to use SQL Server Express Technical Preview must uninstall the MSDE, and then perform a clean install.
Some of the commonly used command line parameters are given below.
· SAPWD is used to provide the password for the SA account. This is predominantly used if SECURITYMODE=SQL is also used. SECURITYMODE=SQL enables Mixed mode authentication and it is important to provide a strong SA password. For Windows authentication, this parameter is not required.
· ADDLOCAL=ALL selects all the SQL Server Express components for installation.
· DISABLENETWORKPROTOCOLS is used to enable/disable networking protocols. By default the value is 1, which means that the networking is disabled by default. Use DISABLENETWORKPROTOCOLS=0 to enable networking in the instance
· /qn is used to install silently
· The INSTANCENAME parameter is used to specify the name of the named instance. The default value recommended is SQLEXPRESS.
Upgrades are not supported in SQL Server Express Technical Preview. This means that any upgrades from MSDE to SQL Server Express or from Express to SQL Server Developer Edition are not enabled. The upgrade support may change in future releases. As mentioned, repair and maintenance scenarios are also not supported in SQL Server Express Technical Preview, and the corresponding button in Add/Remove Programs is grayed out. If you want to add an extra component like replication, you will have to uninstall the existing instance and reinstall with the new component selection. Please note that for changing networking settings there is no need to go through the maintenance and repair option, and the networking support section has additional details about this.
Language supportSQL Server Express Technical Preview is localized only for English and Japanese. Additional languages will be added in later releases, and the plan is to support all of the 12 languages that MSDE supports by final release of all SQL Server 2005 editions. The targeted languages are English, Brazilian Portuguese, Dutch, Swedish, Simplified Chinese, Traditional Chinese, French, German, Italian, Japanese, Korean, and Spanish.
Support ConsiderationsSQL Server Express Technical Preview is supported via newsgroups, as well as Web-based documentation and resources such as SQL Books Online and white papers. Product Support Services support that you must pay for is also available.
Hardware/Software Requirements· Hardware Requirements
· Processor Minimum 600mHz
· Processor Recommended 1GHz
· RAM Minimum 256MB (setup warning)
· RAM Recommended 512MB
· HD Space Minimum 170MB(with Microsoft .NET as prerequisite)
· Video Minimum 800 x 600, 256 colors
· Video Recommended 1024 x 768, High Color - 16-bit
· Software requirements:
· Microsoft Internet Explorer 6.0 SP1 or higher
· Microsoft .NET Framework 2.0
· Operating system
· Microsoft Windows 2000 SP4 Professional
· Microsoft Windows 2000 SP4 Server
· Microsoft Windows 2000 SP4 Advanced
· Microsoft Windows 2000 SP4 Data Center
· Microsoft Windows XP SP1 Professional or higher
· Microsoft Windows 2003 Server or higher
· Microsoft Windows 2003 Enterprise or higher
· Microsoft Windows 2003 Data Center or higher
· Microsoft Windows Small Business Server 2003 Standard or higher
· Microsoft Windows Small Business Server 2003 Premium or higher
64-bit supportSQL Server Express will support Windows on Windows (WOW) on 64-bit platforms. WOW essentially means running a 32-bit Express on 64-bit machines. However, this is expected to happen in a future release, and is not available in the SQL Server Express Technical Preview timeframe. Hence, the 64-bit Visual Studio 2005 Beta 1 will not have an option to install SQL Server Express.
Features not present in SQL Server ExpressSome of the features available in other SQL Editions, but not in SQL Server Express, include the following:
· Availability features such as data mirroring, clustering, etc.
· Full-text search
· SQL Agent
· Reporting Services
· Business Intelligence Platform, such as Notification and Analysis Services
· SQL Management Studio, which is the new GUI tool replacing SQL 2000 Enterprise Manager
Visual Studio IntegrationSQL Server Express is installed with all the 32-bit editions of Visual Studio. Visual Studio installs SQL Server Express using the instance name SQLEXPRESS. In SQL Server Express Technical Preview, the applications rely on the SQLEXPRESS instance name, but this could change in future. The goal of Visual Studio and SQL Server Express integration is to make database access with SQL Server Express as simple and easy as working with Jet. This is true not only for client applications but also for ASP.NET Web server scenarios.
For instance, a database object, which is a template SQL Server database, is introduced in the Visual Studio projects. This object is displayed when the user chooses the Add New Object option in a Visual Studio project. Once a database is added to the project, the connection management to the database happens automatically so that the user can click the database to see all the objects inside the database such as tables, stored procedures, etc. We offer the ability to drag and drop relevant tables directly to a form. In the case of Visual Web Developer, the GridView and SqlDataSource are created automatically without the user typing in a line of code. You can also automatically bind a control such as a text box to a database value by just dragging and dropping. All the connection string settings are stored in a central Web.config, so that the user has to go to a single location to modify.
Visual Studio uses the Application XCopy support in SQL Server Express so SQL Server database files can be treated just like Windows files within the Visual Studio project. For instance, you can rename, delete, copy, or move the database (.mdf) file in the Data Directory just like Jet files. The associated log file (.ldf) goes through the same operation as the .mdf because Visual Studio maintains an association between these files. Visual Studio manages these operations and they succeed even while an ASP.NET page/process is running against the database. This means that the typical Visual Studio application developer need not worry about log files in a design and development scenario. However, during deployment, it is recommended that the log files be transported along with database files. One point to note is that the user must be a local administrator on the computer to use the Visual Studio database features in SQL Server Express Technical Preview.
Comparison with Other Free Microsoft Databases Comparison with MSDESQL Server Express replaces Microsoft SQL Server Desktop Engine (MSDE) in SQL Server 2005 and provides numerous ease-of-use features that enable it to be used by a non-professional developer or hobbyist. MSDE is based on SQL 2000 technology and is recommended for use with Windows 9x platforms, while SQL Server Express is based on SQL Server 2005 technology. SQL Server Express has features such as Application XCopy, Robust Setup UI, CLR support, GUI tools, and Visual Studio Integration that is not present in MSDE. However, some features are removed from SQL Server Express compared to MSDE. These features include DTS, replication publishing, and SQL Agent.
The use of merge modules for deployment has been a servicing problem for MSDE, and this functionality is not available in SQL Server Express. The workload throttle in MSDE was sometimes difficult to understand and use. In SQL Server Express, the throttle is removed and instead the engine uses CPU, RAM, and database size limitations to differentiate it from the other editions. The table below shows the comparison of these products.
Table 2. Comparison of MSDE with SQL Server Express
SQL Server Express 2005
MSDE 2000
Application XCopy support
Feature not present
No DTS
DTS runtime present
Easy deployment because of no MDAC
MDAC is part of install
MSI only, good servicing story
MSI and MSM, servicing of MSM hard
Client Replication for Transactional, Merge, and snapshot
Merge/snapshot publication supported in addition to replication subscription
Robust Setup UI
Basic setup UI
No agent
Agent present
Supports Windows 2000 SP4, Windows XP SP1, and Windows 2003
Supports Windows 98, Windows Me, Windows 2000, Windows XP, Windows NT4, and Windows 2003
CLR support
No CLR support
GUI tools provided
No GUI tools
Database size limit: 4G
Database size limit: 2G
1 CPU, 1GB RAM
2 CPU, 2GB RAM
No throttle
Workload throttle enforced
Deep integration with Visual Studio
Basic integration with Visual Studio
Comparison with JetSQL Server Express is based on the latest SQL Server 2005 technology, while Jet has been in service pack and maintenance stage for quite some time. All the latest and greatest features, such as CLR integration and XML support, are available only in SQL Server Express. The reliability and scalability story of SQL Server Express is also great. Applications written to SQL Server Express can easily move to SQL Server Standard or Enterprise editions, while Jet is more difficult to scale up. SQL Server Express also provides finer-grained security control over its database files. With the Visual Studio 2005 integration and the Application XCopy feature, we are bringing the ease of use of Jet to SQL Server.
ConclusionSQL Server Express Technical Preview is a significant Microsoft product release, since this product is designed specifically for non-professional developers, ISVs, and hobbyists. It is free, easy to use, packed with powerful features, and provides a seamless upgrade path to other editions of SQL Server. There are features such as Application XCopy that are unique to this SQL Server edition, and the installation and deployment is secure by default. It has big advantages over the existing Microsoft free databases like Jet and MSDE. The integration of SQL Server Express with Visual Studio 2005 also provides a simple database design and development experience.
CopyrightThis is a preliminary document and may be changed substantially prior to final commercial release of the software described herein.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, email address, logo, person, place or event is intended or should be inferred.
© 2004 Microsoft Corporation. All rights reserved.
Microsoft, Windows, Visual Studio, and SQL Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.
本文地址：http://com.8s8s.com/it/it19558.htm