Database Engine recommendations and differences between the SQL Server version and the free database (Jet) version.
The decision to go with an SQL Server 2008/2008 R2/2012 version or stick with the free database (Jet) version largely depends on the number of DM users, if you are running 5-8 users then SQL server is definitely overload for your Company…have more users than that? Then some version of SQL server should be considered. In a nut shell, if you can live with waiting a few minutes for some larger reports and live with the occasional message that says another user is using a portion of the database and retry later, then Jet is fine and costs much less. Please see here for more information on the Jet Database Engine. If you demand that Design Manager be up 24 hours, have need for more than 40-50 users, cannot wait a minute or two for complex reports or a large number of record to post, then the SQL server is better, but cost much more. The following are some performance and technical issues to consider:
- SQL Server needs less maintenance, can be backed up while users are in the program. Jet requires a server re-boot at night and a nightly database maintenance program to run (this can be scheduled to run automatically) along with a backup. Jet has a user limit of 64 users but is also limited to the number of concurrent users on the Terminal Server (RDS)…a dual processor (not dual core, 2 real processors), 64-bit 2003 or 2008 server with 8 GB of RAM should handle about 30-45 users on the Jet version. SQL Server has no limit to the size of the database; where as Jet can only handle up to 2 GB of data. For an average company, with about 10 users, this about 8-10 years of data. The only acceptable method of running the MS Jet Database with more than 8 users, is using Terminal Services or RDS. However, SQL Server usually does require and expert to setup and maintain…all databases need maintenance and backups including SQL Server, this is the responsibility of an expert in your company that can manage the SQL Server and setup the proper backups and maintenance plan. If your company has no such expert or feels it too expensive to buy/run SQL Server yourself, DM Cloud is a great alternative (contact DM sales for more information).
- When editing a single record or asking for just a few records, Jet is quicker, but we are talking fractions of a second…in other words, calling up a single vendor to edit, Jet reads the database directly and the window flashes up immediately. In SQL there is always some overhead to request the data…in other words, in SQL a request must be sent to the SQL server, a query processed, and a record returned, this means that you may see a .25 to .5 second delay in retrieving a single record depending on the connection speed to the SQL server and the load on the server.
- SQL Server is much quicker (100s or 1000s of times) when asking for complex data sets, reports, posting complex transactions, and large numbers of records. For example a grid that must list all invoices for a particular vendor in date order might take Jet 10-20 seconds to retrieve but SQL would only take about .25 to .5 seconds. Complex reports such as WIP might take Jet 5 or 6 minutes to complete while SQL would less than 5 seconds.
- SQL handles multiple users and record locking better…with Jet if too many users hit the DB at exactly the same time, write cache can build up and some users can be blocked out, have to hit retry or a wait for the record until the system catches up. This means that Jet can have many records or even pages of records locked which causes a greater chance of conflict. See here.
- Terminal Server or a Citrix Server is required when using the Jet database for over 8-10 users. SQL server does not require Terminal Server/RDS, but your workstations still must meet the minimum system requirements to run DM. You will still need Terminal Server/RDS in combination with SQL server for remote users, if you want to make use of workstations that are older and do not have the speed or RAM to run DM locally, or need to run on a slower or wireless network. Each user of DM requires a Terminal Server/RDS CAL and an SQL Server CAL.
Microsoft does offer a free version of SQL Server called SQL Express. Microsoft placed the following limits on SQL Express 2008:
- CPUs: Only 1 CPU. If a system has more than 1 SQL, Express will still run but limit itself to 1 CPU.
- RAM: 1 GB. More RAM can exist, but again SQL Express will only make use of a maximum 1 GB. Microsoft recommends a maximum of 5-12 concurrent users; this number depends greatly on demand, query complexity and size.
- Database Size: 4 GB. This limitation provides for the storage of a considerable amount of data while protecting the domain of the higher-end SQL Server versions. Note: SQL Server databases will be larger even though they hold the same amount of data as Jet…for DM if you are using the Jet version, a good rule of thumb is to take the total size of all of your .mdb files and multiply it by 2.5, this will give the approximate size of the SQL Server database once the data is converted and give some room for expansion.
Design Manager requires a SQL Server CAL for each user of DM plus a Terminal Server/RDS CAL for each user of Terminal Server. Design Manager recommends the following when choosing a database engine for Design Manger Pro 7.0 (these are only guidelines based on an average Design Firm and average load):
- 1-4 users – Jet database, peer to peer wired network running at 1.0 Gbps is fine. Each workstation must meet or exceed minimum system requirements for DM. May want main computer to be more powerful.
- 5-7 users – Jet database, dedicated file server. Each workstation must meet or exceed minimum system requirements for DM. The network must be wired and running at 1.0 Gbps.
- 8- to 100s of users – SQL Server Standard Edition 2008/2012 or higher. Each workstation must meet or exceed minimum system requirements. For wireless networks or computers with an older OS, Terminal Server/RDS can be used or in combination with SQL Server. For example, Modern and wired workstations can run the software locally and connect directly to the SQL server while older workstations and remote connections can connect to a Terminal Server/RDS and in turn connect to the SQL Server.