MSDE vs. SQL Server

Ask anyone who has read the Microsoft marketing material; the Microsoft SQL Server Desktop Engine (MSDE) is SQL Server with a couple of caveats. There are four big differences: database size limitations, performance limitations, replication limitations, and the lack of administrative tools. Each of these caveats is important from a deployment position.

The first major limitation with MSDE is the database is limited to two gigabytes. To be clear, Visual FoxPro has a two gigabyte limitation per table , while MSDE has a two gigabyte limitation per database ‚ in other words, for all tables, views, stored procedures, user security privileges, etc. in one database. For small databases this might not be a problem, but applications storing larger amounts of data need the full version of SQL Server.

The second big limitation is the performance being throttled when more than eight processes are running. This does not mean your applications are limited to eight users, but you are limited to eight simultaneous processes like performing a query or executing a stored procedure. If you architected an application designed to run with one to ten users and they do not access the database too heavily, your users will likely observe acceptable performance in production. We recommend testing this before committing to MSDE.

Depending on the replication scheme you deploy with your application, you may or may not be able to use MSDE. The replication limitations with MSDE are as follows :

• MSDE can act as a Subscriber for all types of replication.

• MSDE can act as a Publisher and Distributor for merge replication and snapshot replication.

• MSDE cannot act as a Publisher or Distributor for transactional replication.

Deploying a database in MSDE can be tricky because no administrative tools ship with MSDE. If you are deploying SQL Server application, you are comfortable with the SQL Server tools you installed on your development machine: SQL Server Enterprise Manager, SQL Query Analyzer, SQL Server Agent, SQL Server Service Manager, and SQL Profiler. Only SQL Server Service Manager and SQL Agent ship with MSDE. OSQL.EXE is a command line tool that executes Transact -SQL commands and BCP.EXE is a command line tool for bulk copying of data. These utilities are useful, but the interactive tools you are comfortable and productive with are missing.

From a deployment perspective, developers need to address a couple of things. These include loading the initial database, configuring users and backups , setting up jobs, and reviewing error logs.

The easiest way is to use the remote access to MSDE and SQL Server using Enterprise Manager. Enterprise Manager works with multiple SQL Server instances. All you need is a connection to the database and be registered through the Enterprise Manager. You have the full feature set available to you for all the databases. In Figure 10 , we show four different instances of SQL Server. Each one is located on a different machine. As long as we are connected to these machines (through either the local area network or the Internet), we can administer these servers and any of the databases maintained on the server. You naturally need security access to the servers to perform any maintenance.

Figure 10. SQL Server Enterprise Manager can see database servers on the workstation (local), the local area network (RASSolo9300 and PSgp6450001), and those connected through the Internet and a VPN (Alpha).

The Enterprise Manager access allows you to schedule/run backups, manually alter structures, create indexes, create views, write stored procedures, import data, and maintain the user logins. It does not let you run scripts. You can use SQL Query Analyzer ( Figure 11 ) to perform queries, run scripts to update the database to include the newest changes made in development, analyze result sets to see if there is data corruption, and other various support processes. This is not just for MSDE databases; this naturally works for full SQL Server databases as well.

Figure 11. Use the SQL Query Analyzer to run scripts and execute queries on a remote database, both network connected and those accessed over the Internet.

What happens if you cannot access your client ‚ s instance of a server through the network or VPN? You may be offsite and they do not have a VPN set up. If your client is running SQL Server you can also use Terminal Services, Citrix, or a remote desktop access tool like pcAnywhere or GoToMyPC to connect to the server. Remote access gives you access to the Enterprise Manager, Query Analyzer, Profiler, and other tools shipping with SQL Server.

If they are running MSDE, you do not have these tools available when connected remotely through Terminal Services or pcAnywhere. What can you do in this situation? There are third-party tools to help you maintain and support an MSDE installation.

Microsoft Access XP and 2003 have the ability to back up a MSDE database, restore a previous backup, transfer and copy databases from one instance of MSDE to another, and drop a database from an instance of MSDE via the Tools Database Utilities menu (shown in Figure 12 ). In addition to the database maintenance options, you can create and alter table structures by opening the tables in designer mode. The table content can be browsed, new records added, and existing records deleted or edited. You can also create and alter views (via the Queries list in Access), and browse and edit the information in the result sets. While limited in functionality, it might be great for the circumstance when no other tools are available and you cannot connect in with the SQL Server tools.

Figure 12. Microsoft Access XP and 2003 have basic tools to administer a MSDE database server.

The next tool is MSDE Admin (see Figure 13 ) available from a company called MSDE.biz ( http://msde.biz/ ). This tool performs basic administration on a MSDE database server as well as a full-blown version of SQL Server. This tool runs backups and restores , manage user IDs and logins, allows you to edit stored procedures and views, and browse and edit table data. The license cost is US$22 and includes a copy of MSDE Query, which is a SQL Query Analyzer type tool with reduced functionality. If you need to restore a blank database as part of your installation and maintain some user logins, this tool is great. It does not allow you to schedule backups, but if your users are sophisticated enough to run them interactively. this tool works well.

Figure 13. MSDE Admin allows administrators basic access to an MSDE installation.

The best third-party utility we have seen to manage MSDE is MSDE Manager (see Figure 14 ) from Vale Software ( www.valesoftware.com ). The cost is US$79, which is a bargain considering this utility is very polished and provides a developer or administrator complete control over all aspects of a database. You can add/modify structures, add/modify views, add/modify stored procedures, view and change data, set permissions, run and schedule backups, manage users, script the database or individual objects, shrink the database, and more. At the server level, you can stop the database server and restart it, attach databases, and change database properties. This tool is the closest tool we have seen comparing feature-for- feature to SQL Server Enterprise Manager.

Figure 14. MSDE Manager offers most of the functionality found in the SQL Server Enterprise Manager.

If your deployment includes the need to run scripts, you can use the TSQL utility included in MSDE Manager ( Figure 15 ). It provides all the functionality necessary to select a database, load scripts or write scripts, check the syntax, and execute the scripts. Results from the scripts display in a grid if rows are returned. Other messages display in the Server Messages section at the bottom of the window. Message history is retained until you close this utility.

Figure 15. MSDE Manager has a TSQL window utility that looks and acts just like the SQL Query Analyzer.