How to install the Microsoft SQL Server Desktop Engine (Legacy KB ID CNC TS17989 )

Document ID : KB000052225
Last Modified Date : 14/02/2018
Show Technical Document Details
The Ivize MSDE Installer has the following software requirements:

?         Windows NT4 with Service Pack 6, Windows 2000, or Windows XP (Workstation, Professional, and Server are all acceptable)

?         Internet Explorer 5

The Ivize Database Utility has the following software requirements:

?         Windows NT4 with Service Pack 6, Windows 2000, or Windows XP (Workstation, Professional, and Server are all acceptable)

?         Internet Explorer 5

?         SQL Server or MSDE

?          Ivize


MSDE InstallerThe MSDE redistributables provided by Microsoft do include an installer.  However, this installer has virtually no user interface, and installs to a set of defaults, only changeable via command line parameters.  To solve this problem, the Ivize MSDE Installer (\MSDE\MSDESetup.exe) provides a user interface to gather the necessary information from the user, and then uses that information to generate the correct command line parameters for the Microsoft MSDE installer.

One limitation of MSDE that is worth noting is that the installer provided by Microsoft offers no actual means of setting the SA password during installation.  While this behavior is documented, it is not actually supported, and is ignored "for security reasons".  Thus, upon installation the SA password will be blank.  After the installation of Ivize, the password can be changed using the Ivize Database Utility (see section 3.2.9).


The DialogsThe first dialog displays the typical intro screen that all installations have.  Nothing important here, other than shutting down most Windows programs.


Destination Folder: The second dialog prompts the user for a destination folder for the MSDE engine files.  The database files will go in a subfolder of the folder the user selects.  This dialog defaults to C:\Program Files\Microsoft SQL Server.


Summary: This dialog shows a summary of the information gathered by the installer.  Clicking Install will launch the Microsoft MSDE installer with the appropriate command line, and will close the Ivize MSDE Installer.  The Microsoft MSDE installer will display its own small dialog that shows the progress of the installation.  When the installation is complete, the user should reboot, although the Microsoft installer will not always tell the user this.


Errors We Check ForIn the course of execution, the Ivize MSDE Installer performs a number of validity checks.  Upon loading, it checks if the Ivize MSDE already exists on the user?s system.  If it does, the user is given the option of repairing his MSDE installation.  If MSDE is not yet installed, then the installer proceeds to gather information from the user.  As it does this, it checks that the maximum number of SQL instances (16) has not been met on this system.  These checks are all performed before launching the Microsoft MSDE installer, and additional checks are performed by the Microsoft MSDE installer.


Ivize Database Utility

Since MSDE does not include any graphical tools, nor really, any way at all for a user to interact with it directly, it would not be possible, using only MSDE, to run the Ivize installation script (InstallIvize.sql).  To solve this problem required the creation of an Ivize Database Utility to take over some of the jobs of both the Query Analyzer and the Enterprise Manager.

Upon startup, the DB Utility prompts the user for a SQL login with administrator privileges.  This will occur both during the Ivize installation, and .any time the user runs the DB Utility manually.  If the user does not have SQL admin privileges, the options he can successfully perform in the DB Utility will be limited.  It is expected that under normal circumstances, any user that runs the DB Utility will have SQL Admin privileges.


The Ivize Database Utility as Enterprise Manager as I see it, the main purpose of Enterprise Manager is to create, delete, backup, and restore databases.  Of course, for all of these actions, it gives the user a plethora of options.  The Ivize Database Utility provides these same four actions, but with some customizations.  For one, since this is for Ivize only, it uses the Ivize DSN, only.  It will only perform the above four actions on the Ivize database.  Additionally, the options it reveals to the user are far more limited than the corresponding actions in Enterprise Manager.


Of the more advanced functions that Enterprise Manager offers, the Ivize Database Utility offers the ability to attach or detach a database, to compact the database or truncate the transaction log, and to change user passwords.  These advanced features are available through the Advanced menu on the UI, and not through the main area of the GUI.


Creating the Database: If the Ivize database does not yet exist, then the Create Database button in the Ivize Database Utility will be enabled.  Clicking the button will launch the creation process, which will prompt the user for the location to create the database files in, and then proceed to reate a database based on the system defaults.

NOTE: If the files "ivize_data.mdf" and "ivize_log.ldf" already exist in the selected location, the creation will fail.  Also note that the error message SQL returns for this problem is misleading.

Deleting the Database: If the Ivize database already exists, then the Delete Database button in the Ivize Database Utility will be enabled.  Clicking the button will prompt the user for confirmation, and then delete the database from the system.

Backing up the Database: If the Ivize database already exists, then the Backup Database button in the Ivize Database Utility will be enabled.  Clicking the button will open a dialog which prompts the user for information similar to what the Enterprise Manager requires.  The backup allows for Full, Differential, or Transaction Log backups, all currently only to a disk file.  Backups to tape are not supported.  For this feature, the customer should upgrade to the full-fledged SQL Server.  The backups can be given a name and description, and can be set to expire on a certain date, or within a certain number of days.

Additionally, this dialog offers the user the option of saving the current backup settings.  After saving the settings, they can be used at any time to perform an automatic backup of the database (Full, Differential, or Transaction Log, based on the settings) via an NT Scheduled Task which executes

"IvizeDBUtility.exe -backup"It should be noted that each time an automatic backup is performed it will overwrite the old backup (assuming the user does not change the settings in the Database Utility).  If the user wishes to save more than one backup, he should create a batch file that will automatically copy or move the backup file once the backup completes.  It should also be noted that the registry entry that stores the settings is in the Ivize key, called "MSDEBackup".  The settings are stored as a single SQL statement which is used to perform the backup.

Restoring the Database: If the Ivize database already exists, then the Restore Database button in the Ivize Database utility will be enabled.  Clicking the button will open a dialog which prompts the user for information similar to what the Enterprise Manager require.s.  The restore operation allows for any combination of Full database, Differential database, and Transaction Log restoration, and allows multiple Transaction Logs to be selected for sequential application.  The restoration can be forced or can be processed up to a specific moment in time.  All restorations must include a Full Database restoration, and can include Differential DB or Transaction Logs, or both, restorations in addition to the Full DB restoration.  Thus, the valid combinations are as follows:

?         Full Database

?         Full Database and Differential Database

?         Full Database and one or more Transaction Logs

?         Full Database, Differential Database, and one or more Transaction Logs

 

As with the backup option, only restoring from a disk file is supported.


Attaching the DatabaseIf the Ivize database does not yet exist, then the Attach Database option in the Advanced menu will be enabled.  Selecting this menu item will prompt the user for the location of the database files he wishes to attach (a single MDF and a single LDF).  The files will be attached from that location as the Ivize database.

Detaching the DatabaseIf the Ivize database already exists, then the Detach Database option in the Advanced menu will be enabled.  Selecting this menu item will immediately detach the database, allowing it to be moved or copied elsewhere.  This action is non-destructive, and the database can be reattached immediately afterwards if need be.

Compacting the DatabaseIf the Ivize database already exists, then the Compact Database option in the Advanced menu will be enabled.  Selecting this menu item will immediately begin compacting the database, so that there will be 10% free space.  This process may take a long time, but since there is no way to track the progress of the compacting, no progressbar is shown.

Truncating the Transaction LogIf the Ivize database already exists, then the Truncate Transaction Log option in the Advanced menu will be enabled.  Selecting this menu item will immediately truncate the transaction log, freeing up needed hard drive space.  A complete backup should be performed immediately afterwards.


Changing a PasswordThe Advanced menu offers the option for the user to change passwords.  The password for any user may be changed so long as the old password is known.  The user must enter the user name whose password needs changing, as well as the old password once followed by the new password twice for confirmation.  If the user is not logged in as SA, the ability to change passwords may be limited.

 

The Ivize Database Utility as Query AnalyzerThe purpose of the Query Analyzer is simple: to take a prewritten SQL script, and process the individual statements located therein.  The Ivize Database Utility takes this ability one step further.  Using special comments located in the first two lines of the script, which contain version information, the Ivize Database Utility can determine which script is most appropriate to run.  Thus, upon loading, the utility scans the Ivize\Install directory for all SQL scripts.  It checks the version comments for each script and ranks the scripts accordingly.  It then displays the scripts in a drop down list.  The first item in the list is the most appropriate, the second is the second most appropriate, and so forth.  The first item in the list is automatically selected.

Database StatusThe Ivize Database Utility shows the status of the database in two ways.  Most noticeably, it identifies where the user is in the process of creating and co.nfiguring his database in the graphic on the right.  The first icon indicates that the SQL Server (or MSDE) itself is up and running.  The second icon indicates that the Ivize database has been created.  The third icon indicates that the Ivize database has been configured to match the version of the script that is selected in the list.  As the user selects different scripts, the third icon may change from gray to blue and back, indicating which scripts are for an older or a newer version.

Secondly, the status is described in the pane towards the bottom of the dialog.  This pane includes instructions corresponding to the step that the user is on (identified by the right panel).  It also displays the SQL error messages any time a transaction (creation, deletion, backup, or restoration of the database) fails.


Autoinstall FeatureThe Database Utility is automatically called during the installation or upgrade of Ivize, to perform whatever actions are necessary to bring the database up-to-date with the new version of Ivize.  In a new installation, the DBUtility will create the database and then configure the database with the InstallIvize.sql script.  In an upgrade installation, the DBUtility will examine the database version number in the UserSettings table, and compare it to the version data in each of the present *.sql scripts.  It will select the most appropriate one and apply it to the Ivize database.

In order for the autoinstall feature to succeed, the installation must be a Database Manager configuration, and SQL Server must be running.  Additionally, the DBUtility will prompt the user for login information before proceeding to update the database.  If the user does not know the login information for an Administrative account, then the DBUtility may not be able to complete the update successfully.  If any problems occur, the user will be notified, and instructed to run the DBUtility again manually after the rest of the Ivize installation completes.


Database Location ConcernsMany of the operations above make use of the local filesystem on the SQL Server itself.  As a result, if the Database Utility is not located on the same physical system as the SQL Server that contains the Ivize database, those operations will fail.  The operations that are affected are Create, Delete, Backup, Restore, Attach, and Detach.

In order to minimize the problems that users encounter due to non-local databases, the Database Utility attempts to determine the name or IP address of the SQL Server system in the Ivize DSN (which is created during Ivize installation), and compares that to the name or IP address of the local computer system.  If the two match, then all options are enabled.  If the two do not match, or any errors are encountered in the process of getting the information, then the operations listed above will be disabled.

An important related note impacts the AutoInstall feature.  If the database is not local, the attempt to Create the database will actually create the database in the path C:\Program Files\Vitel Software\Ivize\sqldata on the SQL Server system.  The configuration will then proceed as expected.  However, this may not be what the user expects to happen.  If the user changes the default path, then the path used on the SQL Server system will be what the user chooses.  Again, this will not be what the user expects to happen.

In unlikely circumstances, a similar problem can happen during AutoBackups.  If the original configuration has the database on the local system, the user will be able to set up an AutoBackup.  If the database is then detached, and moved to another system, and the DSN is updated, then AutoBackups can still occur, but will save the file to a path on the SQL Server system instead of on the local system.


TroubleshootingDatabase TimeoutsDatabase timeouts can occur on very large databases, and on databases that are not local.  They are most likely to occur on Compact and Backup operations, although it is also possible that they could occur on Restore and Truncate Log operations as well.  Timeouts during these operations generally do not indicate any significant error; they simply indicate that the operation is taking a very long time due to database size or network congestion.  In the event that a user has a timeout issue, the default timeout for these operations (60 minutes) can be overridden by a registry entry.  Create a new registry entry in the Ivize registry key, of type String, named "DBUtilLongTimeout".  This entry holds the custom timeout, in minutes.  Set the value to a number greater than 60 (since the default timeout is already 60 minutes).  Close the DBUtility, and reopen it, and attempt the operation again.  If timeouts still occur, you may need to further increase the timeout value, or check other issues such as network connections.

.

Related Issues/Questions:
How to install the Microsoft SQL Server Desktop Engine

Problem Environment:
eHealth for Voice
Ivize Database Utility 
MSDE Installer

Additional Information:
The MSDE, or Microsoft SQL Server Desktop Engine, is a free, stripped down version of SQL Server.  It contains the services necessary to serve a SQL database, but no front-end utilities, such as the Query Analyzer and Enterprise Manager that come with the full fledged SQL Server.  Thus, MSDE is only accessible through custom written software that uses transactional SQL statements to perform tasks, rather than user-controlled wizard interfaces like the Query Analyzer and Enterprise Manager.  However, as the core of MSDE is the same as the core of SQL Server, the same transactional SQL commands work interchangeably on both database servers, and if SQL Server coexists with MSDE, then Enterprise Manager and Query Analyzer can be used on the MSDE server via a LAN.


While developers are allowed to distribute MSDE free of charge with their software, Microsoft does not make this a simple task.  The installation and configuration of MSDE must be done through custom software.  To this end, Vitel Software provides the Ivize MSDE Installer and the Ivize Database Utility.


(Legacy KB ID CNC TS17989 )