Installing and configuring a software application that depends upon other system components like a database can be a difficult and time consuming. The person who performs the installation may not have the technical skills required to complete all of the required steps properly, and if the database is not installed correctly, your application may not function. This is frustrating to the customer, and increases the support burden associated with deploying a value-added business solution.
By developing a custom setup program that automates the installation of a value-added business solution along with SQL Server 7.0 or MSDE 1.0 in a single step, a great deal of the cost and complexity involved can be alleviated. This section discusses the technical aspects associated with automating the installation of SQL Server 7.0 and MSDE 1.0.
The techniques discussed in this section can be leveraged from any development environment that provides access to the file system, the Windows registry, and the Win32 API. Examples include products like InstallShield® from InstallShield® Software Corporation, or the new Windows Installer Service, which is the preferred mechanism for installing and de-installing software in a Windows 2000 environment.
This section will refer to a sample Visual Basic 6.0 program that accompanies the MSDE Deployment Toolkit called "SQLAppSetupWiz," which was developed to illustrate the concepts and techniques associated with installing SQL Server or MSDE with a value-added business solution. To view its source code, open the Visual Basic Project File named SqlAppSetupWiz.vbp, and use the Project Explorer to navigate through the various forms, modules, and classes in the application.
Please note that the term "database server" is used in this section to refer to either a SQL Server 7.0 or an MSDE 1.0 installation.
It's a good idea to examine various aspects of the target system when installing an application to determine if the system is fully compatible with the application's system requirements. This section will discuss techniques for automating the detection of system information related to the operating system, Microsoft Internet Explorer and Microsoft SQL Server. See Appendix B: SQL Server 7.0 and MSDE 1.0 System Requirements for detailed information on hardware and software requirements for and SQL Server and MSDE.
The first step in examining the system prior to a database server installation is to determine what operating system is installed and whether it meets the system requirements such as version, service pack level, and so on. The CheckOs subroutine in modMain.bas demonstrates the use of several of the techniques described in this section.
The simplest way to check for the existence and version of Windows 95 or Windows 98 is to determine if the following Windows registry key exists and examine its contents:
HKLM\Software\Microsoft\Windows\CurrentVersion\Version
The Version value contains the operating system release number stored as a string (for example, "Windows 95" or "Windows 98").
Checking the registry for configuration information is not foolproof because users can change registry keys. The most robust way to detect this information is by way of the Win32 GetVersionEx function described later in this section.
DCOM support is required when running SQL Server or MSDE on Windows 95 or 98. To detect whether DCOM95 is installed, determine if the following Windows registry key exists and examine its contents:
HKLM\Software\Microsoft\OLE\EnableDCOM
The EnableDCOM value contains a string that indicates whether DCOM is enabled or not (for example, "Y" or "N"). The CheckWin9598Version subroutine in modMain.bas demonstrates the use of these registry keys.
The easiest way to detect whether the current operating system is Windows NT is to check for the existence and value of the OS environment variable. OS is set to "Windows_NT" for all versions of Windows NT and Windows 2000. This environment variable is not created by default on Windows 95 or 98. Because environment variables can be easily changed, this method is not foolproof.
Another simple way to check for the existence and version of a Windows NT or Windows 2000 installation is to determine if the following Windows registry key exists and examine its contents:
HKLM\Software\Microsoft\Windows NT\CurrentVersion\CurrentVersion
The CurrentVersion value contains the major operating system release number stored as a string (for example, "5.0," "4.0," "3.51," "3.5" or "3.1"). A value of "5.0" indicates a Windows 2000 installation.
To detect the service pack level, check for the existence of the following Windows registry key and examine its contents:
HKLM\System\CurrentControlSet\Control\Windows\CSDVersion
The CSDVersion value contains the service pack level stored as a DWORD (for example, "400" indicates service pack 4, and "500" indicates service pack 5).
To detect whether the Windows NT installation is a workstation or server, check for the existence of the following Windows registry key and examine its contents:
HKLM\System\CurrentControlSet\Control\ProductOptions\ProductType
The ProductType value contains a string that indicates whether the current Windows NT installation is a workstation or a server. The following table describes the various values for the ProductType registry key.
Table 1. ProductType registry key values
| Value | Description |
| WinNT | Windows NT Workstation or Windows 2000 Professional |
| LanmanNT | Windows NT or Windows 2000 Server configured as a primary or backup domain controller |
| ServerNT | Windows NT or Windows 2000 Server configured as a stand-alone server |
To detect the "Product Suite" of a Windows NT Server installation, check for the existence of the following Windows registry key and examine its contents:
HKLM\System\CurrentControlSet\Control\ProductOptions\ProductSuite
The ProductSuite value contains a string that indicates whether the current Windows NT Server installation is an Enterprise or Terminal variant. ProductSuite can contain the following values:
Table 2. ProductSuite registry key values
| Value | Description |
| Enterprise | Windows NT Server Enterprise Edition |
| Terminal Services | Windows NT Server Terminal Services Edition |
Checking the registry for configuration information is more reliable than inspecting environment variables. The Win32 GetVersionEx function provides a more robust alternative. GetVersionEx does not return information on ProductType and ProductSuite for versions of Windows NT prior to version 4.0, service pack 3, so examining the registry is the most comprehensive method for detecting system information for Windows NT 4.0. The CheckNt40Version in modMain.bas demonstrates the use of these registry keys.
The Win32 GetVersionEx and VerifyVersionInfo API calls can be used to detect information about the configuration of any 32-bit Windows operating system. They are the preferred mechanism for obtaining operating system configuration information for Windows 2000, and are more versatile and reliable than using the Windows registry. Both of these functions are called from the CheckWin2kVersion subroutine in modWin32.bas.
GetVersionEx populates an OSVERSIONINFOEX data structure with extensive operating system configuration information, including the following:
VerifyVersionInfo is a powerful function that can be used to verify that the current operating system configuration meets your system requirements. The following steps are required to use this function.
The techniques used to build a condition mask require more explanation. A Win32 macro called VER_SET_CONDITION must be called once for each OSVERSIONINFOEX element comparison to be made. The type of comparison to be made is specified, such as equality, greater than, less than, and so on. Special "AND" and "OR" operators are provided for product suite comparisons that make it possible to verify combinations of product suites on the same server, such as an "enterprise" product suite combined with a "terminal" product suite. Building a typical condition mask might require several calls to VER_SET_CONDITION for comparing product build number, service pack level and product suite.
Building the condition mask can be a bit problematic for non-C programmers. The documentation for VerifyVersionInfo recommends the use of the VER_SET_CONDITION macro for constructing the condition mask; however, this macro can only be called from C. Additionally, the condition mask itself is an 8-byte unsigned integer, with no matching data type in Microsoft Visual Basic.
The Win32 VerSetConditionMask function can be used as an alternative to calling the VER_SET_CONDITION macro when building the condition mask. As of Windows 2000 Beta 3, this function is not documented because its designers assumed that use of the VER_SET_CONDITION macro would be sufficient. The Visual Basic function declaration is provided here for the reader's convenience.
Private Declare Function VerSetConditionMask Lib "kernel32" ( _
ByVal dwlConditionMask As Currency, _
ByVal dwTypeMask As Long, _
ByVal dwConditionMask As Byte) As Currency
Note the use of the Currency data type for both the condition mask and the return value for VerSetConditionMask. The C language definition for the condition mask users the DWORDLONG data type, which translates to an 8-byte unsigned integer. The Visual Basic Currency data type can be used to store DWORDLONG values because it is also 8 bytes in size. Currency variables used to store DWORDLONG values should only be used as arguments for Win32 functions. Any other usage is invalid because manipulation of Currency values is governed by OLE Automation and was not designed for this use. See Endnote 1.
The CheckWin2KVersion function in modWin32.bas demonstrates the use of the GetVersionEx, VerifyVersionInfo and VerSetConditionMask Win32 API calls from within Microsoft Visual Basic.
To install SQL Server successfully, MSDE or SQL Server 7.0 Service Pack 1, and to connect to the database server with administrative privileges using NT Authentication, the user performing the installation must have administrative privileges on the local system. Group membership in the local Administrators group provides the necessary privileges to perform all of these operations. If the local machine is part of a Windows NT or Windows 2000 domain, the Domain Admins group is automatically added to the local Administrators group.
The CheckGroupMembership procedure in modWin32.bas is used to ensure that the current user who initiated the installation process is a member if BUILTIN/Administrators. This useful routine obtains a process handle to the current process, then retrieves a handle to the access token associated with the current process by way of the Win32 OpenProcessToken API call. This handle is then used to determine which groups the current user is a member of using the Win32 GetTokenInformation API call. GetTokenInformation returns a buffer containing the security identifiers (SIDs) and attributes for each group the current user is a member of. The Win32 LookupAccountSid function is then used to retrieve the domain and name associated with each individual group SID. These strings are compared to the desired domain and group name (for example, BUILTIN/Administrators) to determine if there is a match.
The second step in examining the system prior to a SQL Server installation is to determine whether Microsoft Internet Explorer is installed, and whether the version of Internet Explorer meets the application's system requirements. This check is not necessary for MSDE installations because MSDE 1.0 does not require Internet Explorer.
A simple way to check for the existence and version of a Microsoft Internet Explorer installation is to determine if the following Windows registry key exists and examine its contents:
HKLM\Software\Microsoft\Internet Explorer\Version
The Version registry key is a string that contains the build number for Internet Explorer. This string follows the following format:
<Major Version>.<Minor Version>.<Build Number>.<Sub Build Number>
SQL Server 7.0 requires Internet Explorer 4.01 service pack 1 or later, which translates to build number 4.72.3110.8 or later. The CheckIe and CrackIeVer subroutines in modMain.bas demonstrate the use of the techniques described in this section.
The next challenge facing a developer who wishes to automate the installation of SQL Server or MSDE is to determine whether a database server is already installed on the target system. The Windows registry provides a convenient way of checking for an existing database server installation.
A simple way to check for the existence and version of a database server installation is to determine if the CurrentVersion Windows registry key exists and examine its contents. This key resides in different locations of the registry depending upon the version of the database server that is installed on the system.
The CurrentVersion registry key for SQL Server version 4.21 resides in the following location:
HKLM\Software\Microsoft\SQLServer\Server\CurrentVersion\CurrentVersion
The CurrentVersion registry key is a string that contains the version number for Microsoft SQL Server. For release 4.21, the string follows the following format:
NT <Major Version>.<Minor Version>
For example, the shipping release of SQL Server 4.21 for Windows NT is "NT 4.2."
The CurrentVersion registry key for SQL Server version 6.0 and later and MSDE 1.0 can be found in the following location:
HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion\CurrentVersion
The CurrentVersion registry key is a string that contains the version number for the database server. For SQL Server 6.0 and later and MSDE 1.0, the string follows the following format:
<Major Version>.<Minor Version>.<Build Number>
The official build number for SQL Server 7.0 and MSDE 1.0 is "7.0.623." Some releases of MSDE 1.0 that shipped with Microsoft Office 2000 Developer Edition may be improperly identified as build "7.0.677" in the registry. This anomaly is corrected on any MSDE 1.0 installations that have been upgraded with SQL Server 7.0 Service Pack 1. The techniques described in this section are implemented in the CheckSql and CrackSqlBuild routines in modMain.bas.
In some situations, just knowing the build number of the installed database server is not enough. The term "Package Type" refers to the actual stock keeping unit (SKU) that was used to install SQL Server or MSDE. It may be important to know the package type of the database server installed on the system in order to determine its capabilities.
There are two techniques for determining the package type for an existing database server installation. The recommended technique is to interrogate the return value of the SQL-DMO IsPackage method. This approach is the only way to conclusively detect whether SQL Server Desktop Edition is installed. However, it does not distinguish between different variants of MSDE. This technique is demonstrated in the GetSqlPkgType routine in modSqlDmo.bas.
The package type can also be determined by calling the QueryValue function in a dynamic link library named SQLBOOT.DLL, which is located in the BINN subdirectory of the database server installation directory.
The C language declaration for QueryValue is as follows:
DWORD WINAPI QueryValue(DWORD dwSetting);
typedef DWORD (WINAPI *LPFNQUERYVALUE)(DWORD); // QueryValue function pointer
To determine the package type of the installed database server, call the QueryValue function with the dwSetting parameter set to the hexadecimal value 0xD7887E1E. The function will return a DWORD value that can be used to determine the package type as defined in the following table.
Table 3. Package types returned by SQLBOOT.DLL
| Package | Value | Description |
| PACKAGE_OFFICE | 1 | MSDE 1.0 Office 2000 Release |
| PACKAGE_STANDARD | 2 | SQL Server 7.0 Standard Edition or SQL Server Desktop Edition |
| PACKAGE_ENTERPRISE | 3 | SQL Server 7.0 Enterprise Edition |
| PACKAGE_MSDE | 4 | MSDE 1.0 |
The QueryValue function returns PACKAGE_STANDARD for SQL Server 7.0 Desktop Edition installations. Currently, there is no documented way to detect whether SQL Server Desktop Edition is installed. If you have detected a SQL Server 7.0 Standard Edition installation, it is safe to assume that it is actually Desktop Edition if the operating system is Windows NT Workstation, Windows 95, Windows 98, or Windows 2000 Professional. Standard Edition can be assumed if the operating system is Windows NT Server. It is possible to install SQL Server Desktop Edition on a Windows NT or Windows 2000 server. The only negative ramification of a mistaken assumption that a SQL Server installation is a Standard Edition package when it is actually a Desktop Edition package is performance-related, because Desktop Edition does not scale beyond five users.
The installation executable for Microsoft SQL Server 7.0 and MSDE 1.0 Office 2000 Release is named Setupsql.exe, and was developed using InstallShield from InstallShield Software Corporation. SetupSql operates in both an interactive mode where a user responds to several dialogs presented by an installation wizard, as well as an unattended mode where the input for each dialog box in the wizard is read from a special text file called a "Setup Initialization File," which will be discussed in detail in a subsequent section of this document.
Normally, SetupSql is used to install a database server on a local machine. SetupSql supports the installation of SQL Server 7.0 on a remote Windows NT or Windows 2000 system, provided it is the same processor platform as the system running SetupSql. However, the techniques for automating remote installations are not currently supported. If you need to automate a remote installation, refer to Microsoft Support Knowledge Base articles.
MSDE 1.0 shares a common installation code base with SQL Server 7.0, but enables single-file distribution of MSDE by way of an InstallShield "PackageForTheWeb" file that is digitally signed by Microsoft Corporation. This is a self-extracting executable that packages all of the files needed to install MSDE (including SetupSql) into a single file.
Before you install SQL Server or MSDE, its important to determine whether there is enough disk space available on the target system. The following section discusses typical disk space requirements for both database servers. Disk space requirements for both a "Typical" and "Minimum" installation types for SQL Server 7.0 are provided.
Table 4. Estimated disk space requirements for SQL Server 7.0 and MSDE 1.0
| Component | Description | Location | SQL Server 7.0 Typical |
SQL Server 7.0 Minimum |
MSDE 1.0 |
MSDE Office 1.0 |
| Program Files | Program binaries, help files, and so on. | Configurable | 76.00 MB | 15.10 MB | 15.10 MB | 23.80 MB |
| Data Files | System databases | Configurable | 31.60 MB | 22.00 MB | 17.00 MB | 17.00 MB |
| MDAC, DTC & Other (see Endnote 2) | Microsoft Data Access Components, Microsoft DTC and other system components | System | 65.29 MB | 37.01 MB | 38.01 MB | 38.01 MB |
| Working Storage | Working storage required during installation, reclaimed after installation completes | Temp | 12.00 MB | 2.00 MB | 74.00 MB | 10.00 MB |
| Total Space Required To Install: | 184.89 MB | 76.11 MB | 144.11 MB | 88.81 MB | ||
| Total Space Used After Installation: | 172.89 MB | 74.11 MB | 70.11 MB | 78.81 MB | ||
Valid sets of database server installation source files (referred to as "images") are required in order to automate the installation of SQL Server or MSDE. In some cases, you can rely on your customer to obtain their own installation image and prompt them for it at install time. In other cases, you may have obtained a license agreement with Microsoft that permits you to redistribute the database server installation files associated with either SQL Server or MSDE. See Understanding SQL Server and MSDE Licensing for more information. This section discusses how to incorporate database server installation images into an automated installation procedure.
The directory structure and files included in an installation image for SQL Server 7.0 or MSDE 1.0 Office 2000 Release must exactly mirror the structure and contents of a valid SKU. If any files or directories are missing, the setup program may terminate abnormally. The entire directory structure for a database server installation image can be located in a subdirectory on your own media or on a network file server.
In some cases the end-user may provide their own installation image, in which case it is necessary to verify that the installation image provided is of the correct package type. See Detecting the SQL Server 7.0 or MSDE Package Type for more information on package types. There are two basic techniques for determining the validity of a SQL Server 7.0 installation image.
The first technique is to examine the SQLSERVR.INI file located in either the \X86\SETUP or \ALPHA\SETUP directories of the SQL Server 7.0 installation image to determine the package type of the installation image. SQLSERVR.INI is a text file that follows standard Windows initialization file structure conventions. The "SQL Server-SkuStr" section in this file contains a key named "Data" whose value is a string that can be used to determine the package type of the installation image. The value "Standard Edition" indicates a package type of PACKAGE_STANDARD, and the value "Enterprise Edition" indicates a package type of PACKAGE_ENTERPRISE. Note that SQL Server Desktop Edition can be installed from either of these SKUs using a special command-line option as described in the section, Database Server Installation Executable Command-Line Syntax. The VerifyInstallImage function in modMain.bas demonstrates how to interrogate SQLSERVR.INI to determine the package type for an installation image. MSDE 1.0 Office 2000 Release does not incorporate a SQLSERVR.INI file.
The second technique employed by VerifyInstallImage is to verify the size of the database server executable (Sqlserver.exe) on the installation image. The file size for both Standard and Enterprise editions is 4,964,624 bytes. Sqlserver.exe is 4,907,280 bytes in size for the MSDE 1.0 Office 2000 Release.
MSDE 1.0 was designed specifically with redistribution in mind. All database server installation files have been packaged in a single PackageForTheWeb file named Msdex86.exe for Intel systems, and Msdealpha.exe for Alpha systems. This is the only file needed to redistribute MSDE 1.0 with a value-added business solution.
Because both of these executables are digitally signed using Authenticode, it is a simple matter to determine their validity using the WinVerifyTrust function in the Win32 API. Unfortunately this function is difficult to implement in Visual Basic due to the complex nature of the data structures it uses, so the sample installer resorts to executing a command-line utility called Chktrust.exe. This CryptoAPI utility is available in the download section of the Microsoft Developers Network at http://msdn.microsoft.com/. The concepts discussed in this section are demonstrated in the routine named VerifyInstallPackage in modWin32.bas. Notice that Checktrust.exe is run with the "-q" parameter. This is necessary to suppress the display of certificate information.
A Setup Initialization File is a special text file used by SetupSql to perform an unattended installation of SQL Server 7.0 or MSDE 1.0. The term "Setup Initialization File" is a Microsoft term. InstallShield developers use the term "InstallShield Silent Response File." Setup initialization files typically use a ".iss" file extension and follow standard Windows initialization file conventions. ISS files are typically created using an InstallShield feature called a "response file," which essentially records user responses during an interactive installation. You must provide a reference to a valid ISS file to perform an unattended installation of either SQL Server 7.0 or MSDE.
ISS files are divided into sections that correspond to each SetupSql dialog box that can be presented during an interactive installation. Each section contains a number of keys that contain data describing the user's responses and actions associated with that dialog.
You can either use an existing ISS file provided by Microsoft on your installation image (see Appendix D: Setup-Related Files), or you can create your own. Whether you record your own ISS file, or use one of the standard files from your installation image, you will often end up modifying this file programmatically during install time to customize a particular installation.
The easiest way to create a new ISS file is to record one from an actual interactive installation session using the steps below.
Table 5. Command-line syntax for creating a setup initialization file by database server type
| Platform | Command |
| SQL Server 7.0 Desktop Edition | <Your CD>:\x86\Setup\SetupSql.exe k=Dk k=Rc |
| SQL Server 7.0 Standard or Enterprise Edition | <Your CD>:\x86\Setup\SetupSql.exe k=Rc |
| MSDE 1.0 Office 2000 Release | <Your CD>:\SQL\X86\SETUP\SetupSql.exe k=Rc |
Note Some entries such as user name, company, and so on are obviously temporary placeholders. They can be modified later at install time by changing the ISS file.
Note Recording is only supported for SQL Server 7.0 and MSDE 1.0 Office 2000 Release. There is no way to record an MSDE 1.0 ISS file because it only supports unattended installations. See Appendix D: Setup-Related Files for information on where to locate the default ISS file for MSDE 1.0.
Modifying the values of keys in an ISS file is a fairly simple process. The Win32 WritePrivateProfileString and ReadPrivateProfileString API functions can be used to manipulate ISS file values because they follow standard Windows initialization file conventions. The ReadIni and WriteIni routines in modMain.bas demonstrate how these functions can be used from within Visual Basic.
The table below lists some of the common ISS file keys that may require modification at install time prior to running SetupSql. There are many more keys defined in an ISS file; however, these are commonly set at install time. The rest can be set when the developer initially records the setup initialization file as described in the previous section.
Table 6. ISS file key values modified at install time
| Section | Key | Description |
| SdRegisterUser-0 | szName | Name of user or owner |
| SdRegisterUser-0 | szCompany | Name of organization |
| CDKEYDialog-0 | svCdkey | CD-Key in the format: #####-###-#######-##### (see Endnote 3) |
| SetupTypeSQL-0 | szDir | Program Files Directory |
| SetupTypeSQL-0 | szDataDir | Data Files Directory |
The PrepareSetupIni routine in modMain.bas demonstrates how these CD-key values can be modified at install time. Note that PrepareSetupIni copies the appropriate setup initialization file into the Windows directory as "Setup.iss." This location and file name appears to resolve problems related to the –f1 installation executable parameter for unattended installations of SQL Server 7.0 Service Pack 1.
Now that the configuration of the system has been inspected, and an ISS file has been prepared, the final step involved in performing an unattended installation is to actually run the installation executable.
The name of the installation executable and the command-line syntax vary somewhat depending upon the database server package type being installed. The following table provides the command-line syntax for each database server package type.
Table 7. Database server installation executable command-line syntax
| Package Type | Location | Syntax |
| SQL Server 7.0 Desktop, Standard or Enterprise Editions | \X86\SETUP | setupsql [k=Dk] [k=Rc] [k=Rm] [NoSysChk] [-s] [-SMS] [-m] [-z] [-f1 [drive:][path]filename] |
| MSDE 1.0 Office 2000 Release | \SQL\X86\SETUP (see Endnote 4) | setupsql [k=Rc] [NoSysChk] [-s] [-SMS] [-m] [-z] [-f1 [drive:][path]filename] |
| MSDE 1.0 | \MSDE (see Endnote 5) | msdex86 [-s] -a -f1 [drive:][path]filename Or msdealpha [-s] -a -f1 [drive:][path]filename |
See Appendix E: Installation Executable Command-Line Parameters for a description of each command-line parameter or option.
The syntax for a few sample executions of will be noted here for the reader's convenience. The first sample is for a SQL Server 7.0 Standard or Enterprise Edition unattended installation. An ISS file named "Sql70ins.iss" is used to supply all dialog box responses and choices to Setupsql.exe in place of an actual interactive user. A status MIF file is generated upon completion
setupsql -s -SMS -m –z -f1 "c:\sqlsetup\sql70ins.iss"
The second sample is for an SQL Server 7.0 Desktop Edition unattended installation. The "k=Dk" option instructs Setupsql.exe to perform a Desktop Edition installation from either a Standard or Enterprise edition installation image.
setupsql k=Dk -s -SMS –z -f1 "c:\sqlsetup\sql70ins.iss"
The final sample is for an MSDE 1.0 unattended installation using the PackageForTheWeb installation executable for Intel-based systems.
msdex86 -s -a -f1 "c:\msdesetup\msdeins.iss"
The sample installer uses a routine called RunSetupSql in modWin32.bas that demonstrates how to initiate the database server installation executable from Visual Basic using the Shell function. RunSetupSql uses the process identifier returned from the Shell function to help keep track of the progress being made by the database server installation process. The process identifier is used to gain access to more detailed information about the process by way of the Win32 OpenProcess function, including the process exit code, which is interrogated by way of the Win32 GetExitCodeProcess function to determine when the installation executable terminates.
RunSetupSql continues to check GetExitCodeProcess in a loop until the exit code is equal to something other than STILL_ACTIVE. Inside the loop, the Visual Basic DoEvents statement is executed, which enables the sample installer to update the display with progress information instead of appearing frozen during the duration of the installation.
The first technique you can employ to determine whether the database server installation executable successfully installed the database server is to check its exit code. Unfortunately, the exit code cannot be relied upon conclusively.
The exit code can be checked in a batch file by interrogating the errorlevel environment variable, or by way of the Win32 GetExitCodeProcess command detailed in the previous section. The following table details possible exit codes that can be returned by the installation executable.
Table 8. Installation executable exit codes
| Exit Code | Description |
| 0 | Successful installation (no reboot required) |
| 3010 | Successful installation (reboot required) |
| -1 | Failed installation (logic or configuration error) |
| Other | Failed installation (Win32 error code—see Endnote 6) |
To determine the success or failure of a database server installation conclusively, the Setup.log file must be examined. This is due in part to the fact that the PackageForTheWeb installation executable does not always report a failed installation using exit codes.
Setup.log is a text file created by InstallShield in the Windows directory that follows standard Windows initialization file structure conventions. This file should be examined after the database server installation executable terminates to determine whether a database server installation succeeded or failed. In the case of a failure, it will also contain an error code that can be used to diagnose the reason for the failure. An additional file called sqlstp.log is also created in the Windows directory with detailed status information about each stage of database server installation. Together these files are a very useful tool for determining whether database server installation succeeded or failed, and diagnosing the reason for failure if a failure occurred.
It's important to delete or rename any old copies of setup.log or sqlstp.log that may have existed in the Windows directory from a previous database server installation so they are not mistaken for the files created by the current InstallShield process. It is possible for a PackageForTheWeb executable to terminate before it actually initializes these files. An unsuccessful database server installation can be assumed if these files do not exist when the database server installation executable terminates. As indicated in the table below, there are two keys located in Setup.log that must be examined to determine if the database server installation succeeded or failed.
Table 9. Setup.Log keys to inspect to determine installation success or failure
| Section | Key | Successful Installation |
Failed Installation |
| Status | Completed | 1 | Any other value |
| ResponseResult | ResultCode | 0 | Any other value (see Endnote 7) |
Setup.log also contains a key named RebootRequired in the Status section that indicates whether a reboot should be performed once installation is complete. The value 0 indicates that no reboot is required, and 1 indicates that a reboot is required.
The RunSetupSql routine in modWin32.bas determines the success or failure or the database server installation executable by first interrogating its exit code, then by examining the setup.log file and the keys mentioned in the previous table.
Your value-added business solution may be dependent on one or more fixes or enhancements that have been addressed in a database server service pack. For this reason, application of the most recent service pack is often the first task performed after a successful installation of the database server. It is an ideal time to perform this task because the database server and related services are automatically stopped by the installation executable when it completes, and it is unlikely that any database server utilities or administration tools updated by the service pack are running.
This section will discuss the unattended installation of SQL Server 7.0 Service Pack 1, because that is the most recent service pack to have been released at the time of this publication. SQL Server 7.0 service packs can be downloaded from http://support.microsoft.com/. SQL Server 7.0 and MSDE 1.0 are updated using the same service pack binaries.
It is important to note that it takes significantly longer to install a database server service pack than it takes to perform an initial database server installation. This is due to the fact that the service pack must upgrade system-stored procedures by submitting Transact-SQL batches, whereas system-stored procedures are installed using pre-built system databases during an initial database server installation.
Upgrading a database server installation to a new service pack level is quite simple when service pack installation occurs immediately after database server installation. However, if service pack installation is being performed on an existing database server installation, there are a few tasks that should be performed prior to service pack installation. This section describes those tasks.
The service pack installation executable makes changes to various system databases and updates database server binaries. For this reason, the service pack installation executable must establish a connection to the database server being updated. The user account used to connect to the database server must be a member of the SQL Server sysadmin role. If connecting to the database using SQL Server standard authentication, it is advisable to use the special sa login ID, which is a member of the sysadmin role. If connecting by way of Windows NT authentication, the user account (or a group that it belongs to) must be a member of the sysadmin role. By default, the Windows NT Administrators group is a member of the sysadmin role.
The sp_helpsrvrolemember system-stored procedure can be used to enumerate members of the sysadmin role. The sample application verifies sysadmin role membership in the CheckSysAdminRole routine in modSqlDmo.bas by inspecting the value if the IsSysAdmin property of the SQL-DMO SQLServer object.
During service pack installation, system-stored procedures and system tables are added and updated in the master and msdb databases. These changes may require additional space to be allocated for either of these databases. By default, system databases are configured for auto growth; however, it is possible that the options that control automatic storage allocation were reconfigured after the database was initially installed.
For this reason it is a good idea to check the database and transaction log files associated with the master and msdb system databases to verify that they have been configured for auto growth and that no maximum size limits have been imposed on their growth. The sample application uses the SetSysDbAutoGrowth routine in modSqlDmo.bas to iterate through each database and transaction log file associated with these two databases and check the file growth attributes. If it is determined that auto growth is not enabled or that a maximum size limit has been imposed, the file is reconfigured to allow auto growth with no maximum size limitation.
There are several database or transaction log file properties that control auto growth and maximum file size behavior. The sp_helpdb system-stored procedure reports the current settings for each of these properties, and you can change them by way of the ALTER DATABASE command or by way of the database properties dialog in SQL Enterprise Manager.
It is also possible to check and/or reconfigure these properties programmatically by way of SQL-DMO. The following table lists each of the SQL-DMO properties associated with the DBFile and LogFile objects that affect storage allocation.
Table 10. SQL-DMO properties affecting storage allocation for DBFile and LogFile objects
| Property | Comments |
| FileGrowthType | Set to 0 when growth increments are specified in MB, set to 1 when growth increments are specified as a percentage of total space. |
| FileGrowth | Value, which controls that amount of space allocated when the database runs out of free space, set in either MB or percentage depending upon the setting of FileGrowthType. |
| MaximumSize | Set to -1 when the maximum size is unlimited, or some other non-zero number when a limitation has been imposed. |
If the FileGrowth property is set to 0, auto growth is disabled for the file. To enable auto growth, set this property to a value in MB if FileGrowthType is set to 0, or to a percentage if FileGrowthType is set to 1. To disable a maximum file size limitation, set the MaximumSize property to -1.
By default, the master and msdb system databases use only a single database file in a single file group. It is possible, however, that additional databases and/or file groups could have been added for these system databases after the database server was installed. For this reason, the EnableAutoGrowth procedure in modSqlDmo.bas traverses all database files in all file groups for the system database in question to ensure that auto growth has been properly enabled for all associated files.
It is advisable that backups be made of both the system and user databases prior to service pack installation to guard against data loss resulting from an abnormal termination of the service pack installation executable. The sample application performs system database backups when the database server was pre-installed, and presents the option of performing user database backups as well. The BackupDatabase routine in modSqlDmo.bas is the core routine for performing backups. This routine utilizes the SQL-DMO Backup and BackupDevice objects to automate the backup process.
The following table details the estimated disk space requirements in order to upgrade an existing database server installation with SQL Server 7.0 service pack 1. These estimates do not include the space occupied by the SQL Server 7.0 service pack 1 installation image or PackageForTheWeb executable, or temporary storage required to unpack the installation image from the PackageForTheWeb executable.
Table 11. SQL Server 7.0 Service Pack 1 Disk space requirements
| Space Required to Install | Space Used After Installation | |
| SQL Server 7.0 | 44 MB | 7 MB |
| MSDE 1.0 | 38 MB | 5 MB |
Service pack 1 utilizes the same InstallShield technology that is used to install SQL Server 7.0 or MSDE. The service pack can be redistributed by way of a self-extracting PackageForTheWeb executable that is digitally signed by Microsoft. Unfortunately, there is no way to automate the file extraction process in which the installation image for service pack 1 is unpacked from the PackageForTheWeb executable. For this reason, this article will assume that your automated installation procedure references an unpacked SQL Server 7.0 Service Pack 1 installation image. ISVs may elect to redistribute a copy of the service pack 1 installation image on custom media, or rely on the user to provide their own installation image at install time.
The directory structure and files included in the installation image for service pack 1 must exactly mirror the structure and contents of a valid installation image. If any files or directories are missing, the setup program may terminate abnormally. The entire directory structure for a service pack installation image can be located in a subdirectory on your own media or on a network file server.
As with the SQL Server 7.0 and MSDE 1.0 installation executables, the service pack 1 installation executable utilizes an ISS file to provide the responses required to perform an unattended installation. The ISS files provided with the service pack installation image are sufficient for all unattended installations. There is no need to create your own or to modify the existing files. Two ISS files are provide, one for performing unattended installations on a Windows 95 or Windows 98 platform, and one for all Windows NT and Windows 2000 platforms. See Appendix D: Setup-Related Files for the name and location of these files.
The table below details the command-line syntax for running the service pack installation executable in unattended mode. See Appendix E: Installation Executable Command-Line Parameters for descriptions of each command-line parameter or option.
Table 12. Service pack installation executable command-line syntax
| Operating System | Syntax |
| Windows 95 Windows 98 |
setupsql sapwd=sapassword [-s] [-SMS] [-m] [-z] [-f1 [drive:][path]filename] |
| Windows NT Windows 2000 |
setupsql NTAuthentication=1 [-s] [-SMS] [-m] [-z] [-f1 [drive:][path]filename] |
The syntax for two sample executions will be noted here for the reader's convenience. The first sample is for an SQL Server 7.0 service pack 1 unattended installation on Windows 95 or 98. A blank password is used, and an ISS file named "Setup.iss" is referenced that supplies all dialog box responses and choices to Setupsql.exe in place of an actual interactive user. A status MIF file is generated upon completion.
setupsql sapwd= -s –SMS –m –z –f1 "c:\winnt\setup.iss"
The second sample is for an SQL Server 7.0 Service Pack 1 unattended installation on Windows NT or Windows 2000.
setupsql NTAuthentication=1 -s –SMS –m –z –f1 "c:\winnt\setup.iss"
The sample installer provides a routine called RunSetupSql in modWin32.bas that demonstrates how to initiate the database server installation executable from Visual Basic using the Shell function. This routine was discussed previously in Database Server Installation Executable Command-Line Syntax.
The same guidelines for determining the success or failure of the database server installation executable (described in Determining the Success or Failure of a Database Server Installation) can be applied to determining the success or failure of the service pack installation executable. The only exception to this is that a sqlstp.log file is not created during installation.
SQL Server 7.0 includes the Database Upgrade Wizard that facilitates the migration of SQL Server version 6.0 or 6.5 databases to SQL Server 7.0. The upgrade wizard can perform both interactive and unattended database upgrades; however, unattended upgrades are not currently documented or supported by Microsoft. This is due to the fact that extensive error-checking is performed during interactive execution of the wizard that would be difficult to reproduce programmatically during an unattended installation.
In order to migrate a user database using the Database Upgrade Wizard, the SQL Server 6.0 or 6.5 database server installation must have been upgraded to service pack level 3 or higher. This corresponds to SQL Server 6.0 build number 6.00.151 or higher (see Endnote 8), and SQL Server 6.5 build number 6.50.258 or higher. See Detecting and Interpreting SQL Server Build Numbers for more information on build numbers.
The first two steps involved in automating the installation of a SQL Server or MSDE value-added business solution (database server installation and configuration) have been completed. The next logical step involves installing and configuring the application database, also referred to as the "user" database.
An application database contains database objects (such as tables, views and stored procedures) utilized by a custom application. Application databases can have a number of database server configuration dependencies that can make them incompatible with a particular database server installation. See Application Database Design Guidelines for information on how to avoid these incompatibilities.
This section discusses the strategies and techniques involved in automating the installation of an application database on an existing SQL Server 7.0 or MSDE 1.0 installation.
A single database server installation can contain multiple application databases that are used by custom applications that are independent of each other. Each application may require a specific database server configuration, which may or may not be compatible with the requirements of other applications that need to share the same database server installation. Therefore, the database server configuration must be examined prior to application database installation to ensure compatibility.
The database server uses a unique name to identify each application database. When installing an application database on an existing database server installation, its important to make the name of your application database configurable in case the default database name desired is already in use by another application database.
This situation is further complicated by the fact that a logical database can encompass several different data and log files, each with their own names that may or may not be similar to the logical name of the application database. When installing an application database on an existing database server installation, its important to make the names and/or locations of your data and log flies configurable in case duplicate file names exist on the system.
The names of existing application databases can be determined interactively in SQL Server Enterprise Manager by expanding the Databases folder for the desired database server installation, or by running the sp_helpdb system-stored procedure. Database names can be enumerated programmatically by traversing the SQL-DMO Databases collection. See Application Database Naming Guidelines for recommendations naming and location of application databases as well as data and log files.
The btnVerify_Click routine of dlgWiz18.frm in the sample application demonstrates the concepts discussed in this section. This routine employs sophisticated database name and data file name detection and conflict resolution logic. If a duplicate database name is detected, the ResolveDatabaseNameConflict routine is called and the user is presented with options to resolve the conflict. The user can choose to rename the offending database, delete it, or use a different name for the application database being installed. If the user chooses to delete the offending database, a backup is automatically made before deletion. If a duplicate data or transaction log file name is detected, the ResolveDataFileConflict routine is called and the user is presented with options to resolve the conflict. The user can choose to install the application database in a different folder, rename the offending files, or delete them.
SQL Server 7.0 and MSDE 1.0 use a code page to define each character that can be stored in columns created using the char, varchar or text Transact-SQL data types. A sort order is used to determine the collating sequence to be used when sorting data in a code page. The sort order also affects string comparisons and case sensitivity of searches. Each code page supports a variety of sort orders. The code page and sort order for a particular database server installation is selected at install time, and cannot be changed without re-installing the database server because they affect the physical structure of indexes. Multiple simultaneous code pages are not supported.
Characters stored in columns created using the Transact-SQL nchar, nvarchar or ntext data types are defined by the Unicode character set, and are not affected by the database server's code page or sort order settings. See the next section for more information.
The code page and sort order for a particular database server installation can be determined by examining the sort order IDbecause these IDs are unique for each code page/sort order combination. A comprehensive list of sort order IDsand their associated code pages is provided in the SQL Server 7.0 Books Online for SQL Server 7.0. The sort order for a database server installation can be determined by running either the sp_helpsort or sp_server_info system-stored procedures, or programmatically by inspecting the SortOrder property of the SQL-DMO SQLServer.Registry object.
The GetSortOrder routine in modSqlDmo.bas demonstrates the concepts discussed in this section. Note that the SortOrder property of the SQLServer.Registry object is a lengthy string that contains more than just the sort order ID. The CrackSortOrder routine in modMain.bas demonstrates how to extract the sort order ID from this string.
The new nchar, nvarchar and ntext Transact-SQL Unicode data types were introduced in SQL Server 7.0 and MSDE 1.0. These new data types can inherit their collating sequence and comparison style from Windows locales defined in the Win32 National Language Support API (NLSAPI). The Unicode Locale ID (LCID) and Unicode Comparison Style for a particular database server installation is selected at install time, and cannot be changed without re-installing the database server because they affect the physical structure of indexes. Multiple simultaneous locale IDs and comparison styles are not supported.
The locale ID for a database server installation can be determined by examining the "Unicode locale ID" server configuration option using the sp_configure system-stored procedure. You can also examine the CurrentValue property of the SQL-DMO ConfigValue object. A comprehensive list of locale IDs supported by SQL Server 7.0 is provided in the SQL Server Books Online for SQL Server 7.0.
The comparison style is actually a bit mask value constructed by combining several hexadecimal constants using bit wise comparison. The individual constants used to construct the comparison style are listed in the following table.
Table 13. Unicode Comparison Style Constants
| Value | Description |
| 0x00001 | Case insensitive |
| 0x00002 | Accent insensitive |
| 0x10000 | kana insensitive (applicable to certain Eastern Asian languages) |
| 0x20000 | Width insensitive (applicable only to multi-byte characters) |
The comparison style for a database server installation can be determined by examining the "Unicode comparison style" server configuration option using the sp_configure system-stored procedure, or by examining the CurrentValue property of the appropriate SQL-DMO ConfigValue object. Note that both of these options are categorized as "advanced" configuration options and are not visible unless the "show advanced options" server configuration option is set to 1.
The GetUnicodeConfig routine in modSqlDmo.bas demonstrates the concepts discussed in this section.
SQL Server 7.0 and MSDE 1.0 support two different styles of authentication called "Windows NT authentication" and "SQL Server authentication." See the SQL Server Books Online for SQL Server 7.0 for a detailed description of these two different authentication mechanisms. The security mode of a database server installation controls which of these two styles of authentication will be supported when users attempt to connect to the database.
The security mode setting is of interest for two reasons. First, SQL Server 7.0 and MSDE 1.0 do not support Windows NT Authentication for database server installations running on Windows 95 or Windows 98. Second, many existing database applications rely on SQL Server Authentication as the primary means for connecting to the database. For these reasons it is useful to know how to detect and/or reconfigure the security mode for a database server installation.
The database server can be configured to support Windows NT Authentication only (known as "integrated" mode), or both Windows NT authentication and SQL Server authentication (known as "mixed" mode). The current security mode setting can be determined by running the xp_loginconfig system-stored procedure, or by inspecting the value of the SecurityMode property of the SQL-DMO SQLServer.IntegratedSecurity object. The security mode can only be changed interactively using SQL Enterprise Manager, or programmatically by changing the value of the SecurityMode property of the SQL-DMO SQLServer.IntegratedSecurity object.
The SetMixedSecurity routine in modSqlDmo.bas demonstrates the concepts discussed in this section.
There are three different types of application database installation images to choose from when deciding upon a redistribution strategy. This section discusses the pros and cons of each type of installation image and describes how to perform an automated installation using each type.
Active application databases are stored on disk using a combination of database and transaction log files. SQL Server 7.0 introduced the ability to easily attach and detach the files associated with an application database using system-stored procedures. This approach will undoubtedly become very popular because it is extremely simple and fast. File-based installation images can contain pre-built indexes, dramatically reducing the time required to install an application database. SQL Server 7.0 and MSDE 1.0 database and transaction log files are 100% compatible.
The following steps describe how to create an application database installation image using this approach.
The sp_attach_single_file_db stored procedure will automatically build a new transaction log file for a newly attached application database provided there is only a single data file associated with it. This approach is particularly handy when attempting to limit the storage space requirements for your application database installation image because it eliminates the need to redistribute a transaction log file.
There are a number of issues that must be considered when using file-based installation images to avoid incompatibilities or inconsistencies that can occur at install time
See Appendix C: Application Database Design Guidelines for recommendations on how to avoid these problems. The InstallDbFromFile routine in modSQLDmo.bas demonstrates the concepts discussed in this section.
Backup-based installation images have all the same characteristics of file-based installation images with a few differences, which will be noted in this section. In most cases use of a file-based installation image will be preferable to a backup-based installation image.
You can create an application database installation image by backing up a database to a file backup device. The backup file can then be copied onto the target system and restored using the Transact-SQL RESTORE DATABASE command or programmatically by way of the SQL-DMO Restore object.
Backup-based installation images have the following disadvantages in comparison to file-based installation images.
Backup-based installation images have the following advantages in comparison to file-based installation images.
The third type of application database installation image is referred to as a "script-based" installation image, which is comprised of two different types of files: Transact-SQL script files and text files that contain data to be loaded into tables, referred to as "BCP" files.
Transact-SQL script files are simply text files that contain batches of data-definition language (DDL) commands for creating database objects and setting up database security. Script files can be generated from an existing database interactively using SQL Enterprise Manager, or programmatically using the SQL-DMO GenerateScript method.
Transact-SQL script files can be executed interactively using SQL Server Query Analyzer, or in batch mode using either the Isql.exe or Osql.exe command-line utilities. More information about these programs can be found in the SQL Server Books Online for SQL Server 7.0.
When performing a script-based installation, each table in the application database that needs to have rows added to it during the installation process must be loaded from an accompanying text data file, known as a "BCP" file. Bulk Copy Program (BCP) is a data loading utility commonly used to load large quantities of data into SQL Server or MSDE tables.
BCP file formats can vary. Most follow either tab-delimited text or CSV (comma separated values) formats. They contain one line per row of data to be loaded into the destination table. BCP files can be loaded interactively using the Import Data and Export Data wizards in SQL Enterprise Manager, in batch mode using the Bcp.exe command-line utility, or programmatically using either the SQL-DMO ImportData method or the Data Transformation Services object model.
The InstallDbFromScript routine in modSqlDmo.bas of the sample application demonstrates the concepts discussed in this section. This is one of the most useful procedures provided in the sample application. It creates a new empty database, sets the database options as described in Database Option Guidelines, then loads and executes a Transact-SQL script file using the SQL-DMO ExecuteImmediate method. This script creates all of the application database objects, indexes and security roles. Once this script executes successfully, a second text file is loaded that contains a list of tables in the newly created database. Each table in the list is then loaded from a BCP data file using the SQL-DMO ImportData method in the order in which it appears in the list.
With the introduction of SQL Server 7.0, high-speed data loading using BCP interfaces can be performed on tables that have indexes. In previous releases, indexes needed to be dropped to perform high-speed data loading. This makes the process of performing a script base installation simpler because both tables and indexes can be created in a single script then data can be loaded, instead of having to create indexes in a second script after the tables are loaded.
Indexes can also be "suspended" during a load operation initiated by the SQL-DMO ImportData method by setting the SuspendIndexing property of the BulkCopy object to True. This forces all indexes on the target table to be dropped prior to importing any data, then automatically rebuilds the indexes when the operation completes.
Note that the IncludeIdentityValues property of the BulkCopy object is set to True. This suspends automatic generation of identity values for identity columns, and inserts the data values found in the data file instead. Also note that the InstallDbFromScript routine does not support the loading of image data when that data is stored in individual files.
Once an application database has been successfully installed, a variety of post-installation tasks must be performed. This section discusses these tasks in detail.
SQL Server 7.0 and MSDE 1.0 use database options to control a variety of different behaviors in individual databases managed by a database server installation. Database options are either enabled or disabled. Some options are enabled by default when a new database is created. For a full description of each database option, see the SQL Server Books Online for SQL Server 7.0.
Database options should be set after application database installation for a variety of reasons. There are a number of database options whose default settings depend upon whether the database server is installed on a server or a workstation. The setting of another database option (called "trunc. log on chkpt.") is dependent upon the backup strategy to be used for the application database. See Database Option Guidelines for guidance on how to set database options.
Database options can be set interactively using SQL Enterprise manager, in batch using the sp_dboption system-stored procedure, or programmatically using the SQL-DMO DBOption object. The SetDbOptions routine in modSqlDmo.bas demonstrates the concepts discussed in this section.
Users should be granted access to the database and assigned permissions after the application database has been installed. This is due to the fact that SQL Server 7.0 offers a rich, multilevel security architecture that grants access first to the server, then to individual databases, and finally to individual database objects. The linkages between these various levels of security can break when moving an application database between database server installations, or conflicts with existing security accounts can occur when installing an application database on a pre-installed database server installation.
Three levels of security access must be enabled to grant a user access to an application database:
Each step will be discussed in detail in the sections that follow. The GrantAccessToAccount routine in dlgWiz22.bas combines all three of these operations into a single step for the user, greatly simplifying the administration tasks involved in configuring database security. See Security Guidelines for recommendations on the most effective security strategies for application databases that are intended for redistribution.
The first step in granting access to an application database is to grant access to the database server itself. This is accomplished by creating a "login." Database server logins are created and maintained in the master system database. There are two flavors of database server logins: Windows NT logins and SQL Server logins.
Windows NT logins are the preferred way to grant access to a database server installation, and are supported on Windows NT and Windows 2000 database server installations only. Windows NT logins use Windows NT authentication to check the credentials of a user who is attempting to establish a connection with the database server (called a "trusted connection"). Administrators can create logins that are directly associated with Windows NT user and/or group accounts. These accounts can be part of a Windows NT domain, or a local user or group associated with the local operating system installation. Users can connect using their Windows NT account credentials, and do not have to maintain a separate login ID and password combination specific to the database. Trusted connections are established using existing account credentials associated with the users process, eliminating the need to obtain a separate login ID and password from the user. This simplifies the process of connecting to an application database, and reduces account and password administration overhead associated with an application database. Creating Windows NT logins associated with a local or domain group account is a very efficient way to grant access to entire groups of users in a single step.
Windows NT logins can be created interactively using SQL Enterprise manager, in batch using the sp_grantlogin system-stored procedure, or programmatically by creating a new SQL-DMO Login object and adding it to the Logins collection.
SQL Server logins are provided for backward compatibility to previous releases of SQL Server, and for compatibility with the Windows 95 and Windows 98 platforms, which do not support trusted connections. Users connecting using a SQL Server login must provide a separate database server login ID and password when attempting to establish a connection with the database server. Administrators must maintain SQL Server logins and passwords in addition to regular network account administration.
SQL Server logins can be created interactively using SQL Enterprise manager, in batch using the sp_addlogin stored procedure, or programmatically by creating a new SQL-DMO Login object and adding it to the Logins collection.
The AddLogin routine in modSqlDmo.bas demonstrates the concepts discussed in this section. This useful routine combines creation of Windows NT logins and SQL Server logins into a single routine, setting the appropriate properties of the newly created Login object depending upon the type of login being created. The SetPassword method of the Login object is automatically called after new SQL Server logins are created.
Once a database server login has been created, that login must be granted access to an application database by creating a database user. Database user accounts are created and maintained in the application database. The linkage between a database user and a database server login are installation-specific. Database user accounts residing in an application database that is moved to a different database server installation will most likely need to be deleted and re-created. This is why it is wise to create database users after the application database has been installed, and to avoid including database users in your application database installation image.
There are two special system-defined user accounts that are worth mentioning in this section as they may be useful for database security administration purposes. These are the dbo and guest database users.
Application databases include a special system-defined user account named dbo, which stands for "database owner." A database owner is a special administrative entity that can perform all administrative tasks and retains full privileges on all database objects in an application database. The dbo is associated with the database server login that initially created the database, and can be changed later using the sp_changedbowner system-stored procedure. By default, any database server login that is a member of the sysadmin fixed server role (such as "sa") assumes the identity of the dbo when accessing an application database. Database user accounts can be granted dbo privileges by adding them to the system-defined db_owner database security role.
A second special system-defined user account named guest can be added to an application database after it is created. Any database server login that attempts to use the application database will automatically assume the identity of the guest database user.
Database users can be created interactively using SQL Enterprise Manager, in batch using the sp_adduser system-stored procedure, or programmatically by creating a new SQL-DMO User object and adding it to the Users collection. The AddDbUser routine in modSqlDmo.bas demonstrates the concepts discussed in this section.
Once a database user account has been created, database object permissions can be assigned that control access to specific database objects such as tables, views, and stored procedures. There are two ways to assign permissions to a database user.
Adding users to a database security role is the preferred method due to the improved efficiency associated with maintaining permissions at the role level instead of at the database user level. Using database security roles, database object permissions can be pre-created and distributed with an application database installation image. Once a database user is added as a member of a database security role, the database user inherits all of the permissions associated with that role.
The alternative is to issue a grant or revoke statement for every database object in the database to every user account that is added, which can amount to hundreds, even thousands of grant/revoke commands. This situation should be avoided due to the administrative overhead associated with managing such a large body of permissions.
Database users can be added to database security roles interactively using SQL Enterprise Manager, in batch using the sp_addrolemember system-stored procedure, or programmatically using the SQL-DMO AddMember method of the DatabaseRole object. The AddUserToRole routine in modSqlDmo.bas demonstrates the concepts discussed in this section. The GetDbRoleNames routine in modSqlDmo.bas is used to dynamically retrieve a list of database security role names from the current application database. The procedure populates the list with all administrator defined database security roles, plus the system defined public and db_owner database security roles. The list is then used during account creation to automatically assign a user to a database security role.
Now that the application database has been successfully installed and configured, your installation and configuration routine must address database server configuration. This is the final stage in application deployment. The main goal of database server configuration is to eliminate as much manual database administration overhead as possible. This section discusses specific strategies for configuring SQL Server 7.0 or MSDE 1.0 to accomplish this goal. See Guidelines for Minimizing Administration.
A variety of database server configuration options are exposed that control resource allocation and server behaviors. A variety of these configuration options have a direct impact on performance. The database server automatically adjusts the majority of these options based upon the hardware resources available and the current workload being handled. See Automatic Configuration and Tuning for more information.
There are 43 distinct database server configuration options. By default, only 11 of these configuration options can be changed. The other 32 options are considered "advanced" options and should only be adjusted by a qualified database administrator. Improper configuration of database server options can result in less than optimal performance, and in some cases can cause unexpected behavior.
The best strategy for configuring database server options is to ensure that each performance-related option has not been altered from its original auto-tuned state by an inexperienced administrator. See Configuring Database Server Options for specific recommendations on individual database server configuration options and their default settings.
Most database server options can be configured interactively in SQL Enterprise Manager. All options can be configured in batch using the sp_configure system-stored procedure, or programmatically by way of the SQL-DMO SQLServer.Configuration.ConfigValues collection. Note that advanced options cannot be interrogated or changed unless the show advanced options server option has been enabled and the Transact-SQL reconfigure command has been issued. The SQL-DMO ShowAdvancedOptions method automatically enables show advanced options and reconfigures the server.
The ConfigServerForAutoTuning and SetServerOption routines in modSqlDmo.bas demonstrate the concepts discussed in this section.
This section examines strategies and techniques for automating the routine maintenance operations required for a typical database server installation.
SQL Server 7.0 includes a powerful and sophisticated wizard called the Database Maintenance Wizard that automates most common database maintenance procedures, including:
Maintenance plans can be built interactively in SQL Enterprise Manager, or in batch using the Sqlmaint.exe command-line utility. Maintenance plans are scheduled and executed using jobs created in SQL Server Agent.
MSDE 1.0 does not support the Database Maintenance Wizard or Sqlmaint.exe. These maintenance procedures must be implemented using custom-developed procedures if you intend to target MSDE installations. SQLAppSetupWiz does not make use of database maintenance plans for this reason. However, Microsoft is currently planning to provide the Database Maintenance Wizard in a service pack release update for MSDE.
Previous releases of SQL Server required regular maintenance of clustered indexes due to performance degradation suffered as a result of "page-split" operations. A page split occurs during an insert or update operation when a new row is being inserted into a table with a clustered index and there is not enough room on the current page to fit the new row. The old page was de-allocated, and two new pages were created to contain the existing rows and the newly inserted row. Page splits were known to cause concurrency problems and rebuilding clustered indexes to provide free space on each data page was a popular maintenance strategy used to avoid them.
SQL Server 7.0 and MSDE 1.0 introduced new on-disk storage structures and concurrency control mechanisms that avoid page-split concurrency problems altogether. This dramatically reduces the need for clustered index rebuild maintenance procedures in the typical database application.
Previous releases of SQL Server required regular maintenance of index distribution statistics. By default, these statistics are automatically maintained in SQL Server 7.0 and MSDE 1.0. Automatic maintenance of distribution statistics is sufficient for typical database applications.
SQL Server 7.0 and MSDE 1.0 support Automatic Disk Space Allocation features, including the ability to automatically shrink database and transaction log files when possible by enabling the autoshrink database option. Data and log file space can be reclaimed manually as well using the DBCC SHRINKFILE command, which is the approach used in Database Maintenance Plans. The manual approach may be preferable for high volume server-based installations so as not to unnecessarily interrupt user transactions, but the automatic approach is preferable for most typical database applications. SqlAppSetupWiz relies on the autoshrink option to reclaim unused space.
In previous releases of SQL Server, regular database integrity checks using database consistency check (DBCC) commands were recommended. This was due to known anomalies that could occur with the on-disk storage structures used for storing table and index data. SQL Server 7.0 and MSDE 1.0 implement new on-disk storage structures and a new feature called "torn page detection" that addresses these anomalies, therefore eliminating the requirement to perform database integrity checks as part of regular maintenance. DBCC is still useful for detecting data corruption problems resulting from media failures. For this reason, it is a good idea to perform periodic database integrity checks. Dramatic improvements in DBCC execution time make this kind of maintenance operation less resource-intensive. SqlAppSetupWiz does not implement database integrity checks in the current release.
The most important database maintenance operation addressed by Database Maintenance Plans is database backups. SQLAppSetupWiz implements database backups using an alternate strategy as discussed in the following section. If your application requires automation of the operations discussed in this section, they can be implemented using Database Maintenance Plans if you are targeting SQL Server, or using custom SQL Server Agent jobs if you are targeting MSDE.
Both SQL Server and MSDE include a powerful batch job scheduling and execution service called SQL Server Agent. This service is referred to in sections that follow as the "job server." The job server is useful for automating routine maintenance operations.
Jobs can be created interactively by way of SQL Enterprise Manager, in batch using various system-stored procedures, or programmatically using SQL-DMO. Creating a new job involves several steps, as demonstrated in the CreateDailyBackupJob routine in modSqlDmo.bas.
After a new job has been created, the job server will automatically initiate the job at the appropriate times as configured in the job schedule. The job created in the sample application is specifically designed to perform a daily backup of the application database using a backup routine implemented in VBScript. This routine will be discussed in detail in the next section.
Preventing catastrophic data loss using database backups is an important requirement of any database server installation. For more information on the backup capabilities supported by SQL Server and MSDE, see Online Backup.
The Database Maintenance Wizard can be used to create a database maintenance plan that includes regular backups of application databases. Because this utility is not provided with MSDE, developers who wish to target MSDE should implement daily backups using an alternative approach that is demonstrated in the sample application.
A database backup can be initiated graphically using SQL Enterprise manager, in batch using the Transact-SQL BACKUP command, or programmatically using the SQL-DMO Backup object.
The sample application automates database backups in the CreateDailyBackupJob in modSqlDmo.bas. This routine creates a single-step SQL Server Agent job designed to perform a daily application database backup. CreateDailyBackupJob first creates a SQL-DMO JobSchedule object designed to execute daily at 23:30 (11:30 PM). Next, a SQL-DMO JobStep object is created that executes a VBScript file named BackupSqlDb.vbs using the Windows Scripting Host CSCRIPT utility. The job step uses SQL Agent's CmdExec job subsystem instead of the ActiveScript subsystem to run the VBScript file because the ActiveScript subsystem only supports scripts up to 3000 bytes in size. Next, CreateDailyBackupJob creates a SQL-DMO job object. The Job.Owner property is set to "sa" for two reasons. First, Windows NT Authentication is not supported on Windows 95 or Windows 98; Second,Jobs assigned to Windows NT accounts will fail in disconnected settings when the job owner cannot be authenticated at run time. The final step performed by CreateDailyBackupJob is to append the JobStep and JobSchedule objects to the newly created Job object and set the target server to "(local)."
The script run by the newly created job is named BackupSqlDb.vbs, and is executed using the Windows Scripting Host CSCRIPT utility. BackupSqlDb.vbs implements a differential database backup strategy for an application database whose name is defined in a parameter passed to the script. The BackupSqlDb routine in the script file first checks to make sure there is enough space available to perform a backup. Next, it checks to see if a backup device exists for the application database, and if not a new device is created. If an existing device was found, it is examined to determine the number of backup sets included in the backup device. If there are 10 or more backup sets included in the backup device, a flag is set instructing the routine to re-initialize the backup device and perform a full database backup. If fewer than 10 backup sets are found, a flag is set that instructs the routine to perform a differential database backup and to append that backup to the end of the existing backup sets in the backup device.
SQL Server and MSDE support the ability to create custom alerts that can take action when an event occurs, such as when a system error condition is encountered or a specific performance threshold is reached. When an alert fires, it can execute a job and/or send a notification message to a pre-defined job server operator.
Both MSDE and SQL Server can send operator notifications using a network broadcast. Additionally, SQL Server can send notifications by way of e-mail, pager using the SQL Mail support service. See Exchange Server Integration for more information on SQL Mail. MSDE supports network broadcast notification only.
Alerts can be created graphically using SQL Enterprise Manager, in batch using Transact-SQL System-stored procedures, or programmatically using the SQL-DMO Alert object. The sample application does not make use of alerts due to the fact that MSDE does not support SQL Mail.
SQL Server, MSDE, SQL Server Agent and MS-DTC are all implemented as Win32 services on Windows NT and Windows 2000. As such, they can be controlled and configured using Win32 service control commands and/or utilities. Each Win32 service must be associated with a Windows NT account, and can be configured to start automatically when the operating system boots.
Windows 95 and Windows 98 do not support the Win32 service API, so the SQL Server Service Manager Utility (SQL-SCM) is provided to simulate service control and configuration on these platforms. This section discusses techniques for automating the configuration of each of these services at install time.
The Win32 service control manager and the SQL Server Service Manager Utility identify the database server service for both SQL Server and MSDE as the "MSSQLServer." SQL Server Agent is identified as "SQLServerAgent," and MS-DTC is identified as "Distributed Transaction Coordinator." All of these services can be configured to auto-start on Windows NT and Windows 2000 using three different mechanisms:
The sample application installer assumes that there are no dependencies on MS-DTC, so the SQL-DMO approach is used. The Registry.AutoStart property can be used to auto-start the MSSQLServer service, and the JobServer.AutoStart property can be used to auto-start the SQLServerAgent service. The AutoStartServices routine in modSqlDmo.bas demonstrates the concepts discussed in this section.
Some distributed applications, such as those that rely upon replication services or distributed transactions, require each database server to be associated with a Windows NT domain user account rather than a local user account such as LocalSystem. Although interactive executions of the database server installation executable facilitate changing service startup accounts at install-time, this capability is not supported during an unattended execution. Startup accounts must be configured after the database server is installed using the ChangeServiceConfig Win32 API call. Startup accounts do not need to be configured on Windows 95 and Windows 98 because Win32 Service Control APIs are not supported on those platforms.
SQL Server 7.0 and MSDE rely primarily upon the SQL-SCM utility to start and stop database services on the Windows 95 and Windows 98 platforms. SQL-SCM can be used to configure the MSSQLServer service to auto-start. However, SQL-SCM does not support the ability to auto-start the SQLServerAgent or Distributed Transaction Coordinator services. Other mechanisms must be used to auto-start these services if your application depends upon them.
Two issues must be considered in order to use SQL-SCM to auto-start the MSSQLServer service:
Due to these limitations, the sample installer uses an alternative approach for auto-starting services that involves creating a registry key, which launches a script that starts database services.
The CreateAutoStartRegEntry routine in modMain.bas creates a new registry key named StartDbServices in the following location:
HKLM\Software\Microsoft\Windows\CurrentVersion\Run
This is a special location of the Windows registry that is used to launch applications and utilities when a user logs on to Windows. The value of the StartDbServices key is set to a command that executes StartDbServices.vbs using the Windows Scripting Host Wscript.exe utility. Creating a shortcut in the Startup folder to execute the script is an acceptable alternative to this approach.
The script starts the MSSQLServer service using the SQL-DMO SQLServer.Start method. The Start method supports the ability to start the database server without actually connecting to it, eliminating the need to embed a login ID and password in the script that would compromise security. Another advantage of this approach is that it is transparent to the user. There are no visible indicators of the status of the MSSQLServer service to distract a user.
Starting the SQLServerAgent service is somewhat more complicated. The SQL-DMO JobServer.Start method can only be executed if a connection has been established to the database server. SQL Server authentication must be used make a connection on Windows 95 and Windows 98, which would require the StartDbServices.vbs script to include a login ID and password. This is undesirable due to security considerations.
The sample application solves this problem by starting the SQLServerAgent service using an encrypted auto-execute stored procedure. The only other alternatives are to require the user to start the SQLServerAgent service using SQL-SCM or to start the service manually by launching the Sqlserveragent.exe service executable from the command line using the "-c" parameter. This second alternative is undesirable because it requires an active MS-DOS command window that is distracting to users, and requires the user to manually terminate the Sqlserveragent.exe command window using a CTRL-C keystroke before shutting down the system.
The auto-execute stored procedure approach is completely transparent to the user and is therefore preferable to either of these alternatives. The sole disadvantage of this approach is that the auto-execute stored procedure will fail if the sa password changes. The sample installer addresses this by simply requiring the user to re-run Setup if the sa password changes.
The sample installer implements the auto-execute stored procedure approach first by creating a stored procedure named SqlAppSetupWiz_Start_Job_Server in the master database by running a Transact-SQL script file named SqlAppSetupWiz_Start_Job_Server.sql. This stored procedure takes the sa password as a parameter, then uses OLE Automation system-stored procedures to create a SQL-DMO SQLServer object, establish a connection using the sa login ID and the parameterized password, and executing the JobServer.Start method.
Next, the CreateAutoExecSp routine in modSqlDmo.bas is run, which creates an encrypted stored procedure named Run_SqlAppSetupWiz_Start_Job_Server and configures it for automatic execution. Although the sa password is embedded in this stored procedure, the text of the stored procedure is encrypted as a security precaution.
When the user logs on to the system, the StartDbServices.vbs script runs automatically and starts the MSSQLServer service. When the MSSQLServer service starts successfully, the Run_SqlAppSetupWiz_Start_Job_Server stored procedure runs because it was configured for automatic execution. This stored procedure executes the SqlAppSetupWiz_Start_Job_Server stored procedure, passing the encrypted sa password as a parameter. The SqlAppSeutpWiz_Start_Job_Server stored procedure then starts the SQLServerAgent service and the startup sequence is complete.
A final step in application database configuration is to configure replication if the application database is to participate in replication as a publisher or a subscriber. This document does not cover replication in detail because it is a large topic in its own right. The reader is encouraged to refer to the SQL Server Books Online for SQL Server 7.0 or additional information sources at the SQL Server Site Index for deeper coverage of this topic.
Data Transformation Services (DTS) offers a flexible, high performance method for import/export operations that involve SQL Server and MSDE. See Data Transformation Services for more information. Although the graphical tools for interactive DTS package creation and execution ship only with SQL Server, the DTS object model and runtime environment are available on both SQL Server and MSDE.
A DTS package is a COM object that is created programmatically at runtime. DTS packages contain a variety of objects that define the specific steps, sources, destinations, and interim transformation operations that can be performed during package execution. These objects can be created programmatically at runtime, or loaded from an OLE structured storage file, the msdb system database, or the Microsoft Repository. DTS Packages can be created and saved interactively using the DTS Package Designer in SQL Server Enterprise manager, or programmatically using the DTSPackage object model. DTS Packages can be loaded and executed programmatically at runtime using the DTSPackage object model, or in batch mode using the Dtsrun.exe command-line utility.
You can distribute DTS packages as OLE structured storage files with your application database, and "install" them on the target database server using the following approach:
If you do not wish to "install" the DTS package on the target database server, you can simply copy the DTS package file to the target system.
Once a DTS package is installed, you can execute it programmatically by loading the package into memory using the LoadFromFile, LoadFromSqlServer or LoadFromRepository methods, then execute it using the Execute method. Packages can also be executed in batch using the Dtsrun.exe command-line utility. Dtsrun.exe executions can also be scheduled using SQL Server Agent.
The Microsoft Repository provides a comprehensive set of Metadata Services for sophisticated enterprise application development. In order to be useful, the repository database on a database server installation (located in the msdb system database by default) must be populated with various types of metadata. The repository supports a wide variety of different information models that define the structure of specific types of metadata.
There are a variety of programming interfaces and utilities for populating the repository with metadata. The Repository Engine Object Model is a COM-based component used to query and modify structured metadata stored in the Microsoft Repository. Visual C++ or Visual Basic programmers can use this component to write their own repository import or export routines.
The version 2.1 release of the Microsoft Repository SDK provides a variety of useful utilities for importing and exporting metadata. The Microsoft XIF Importer and the Microsoft XIF Exporter utilities provide a way to import and export repository metadata using the XML Interchange Format of the Open Information Model. The OLE DB Scanner utility provides a way to populate the repository with database-oriented metadata from a variety of OLE DB data sources. The Repository SDK also provides utilities for populating the repository with metadata describing Microsoft English Query domains, and Microsoft OLAP Services multidimensional databases. For more information on the Microsoft Repository, see http://msdn.microsoft.com/repository/.
Most value-added business solutions require the installation and configuration of software components on both servers and workstations. The following sections will examine four different scenarios to consider when determining the appropriate software components to install on a workstation that will connect over the network to a server-based installation of SQL Server 7.0 or MSDE 1.0.
Many of today's business solutions are designed to leverage a web browser installed on the client computer as the sole interface to applications hosted on web servers, database servers and application servers. The advantage of this type of configuration is that it requires very little configuration management at the workstation to enable a user to connect and use application services. With the advent of technologies such as Dynamic HTML, XML and data binding, browser-based applications have become a standard way of deploying database oriented application services to users at their desktop.
Browser-based applications must connect to SQL Server 7.0 or MSDE 1.0 databases through middle tier application services such as Microsoft Internet Information Services, which provides several different mechanisms for communicating with a database server installation and sending results back to the client formatted as HTML or XML. Microsoft Internet Explorer 4.0 introduced support for data binding and Remote Data Services (RDS), an extension to Microsoft Active Data Objects that allows web-based applications to manipulate OLE DB rowsets in a disconnected fashion. This technology is key for manipulating larger rowsets that would be unwieldy if represented in standard HTML.
A standard 2-tier client is typically a workstation that hosts a Win32 application that establishes a direct connection to a database server installation over the network using one of the supported data access APIs such as OLE DB, ADO or ODBC. Prior to the introduction of SQL Server 7.0, Microsoft centralized all data access APIs, components and database drivers in a system component called the Microsoft Data Access Components (MDAC). Desktop applications such as Microsoft Office and many 3rd party applications redistribute various versions of MDAC to provide connectivity to databases such as SQL Server 7.0, MSDE 1.0 and other non-Microsoft databases. For more information on the Microsoft Data Access Components, see www.microsoft.com/data/.
When deploying the desktop components of your value-added business solution, you must also deploy the appropriate MDAC release to each workstation to ensure that the latest edition of the supported data access APIs as well as the SQL Server ODBC and OLE DB providers are installed. The most recent release of MDAC available at the time this document was published is version 2.1.2.4202.3 (GA). The installation executable for MDAC is self-extracting Win32 CAB file named Mdac_typ.exe that is digitally signed using Authenticode. For more information on redistributing MDAC, see the MSDN Online article entitled "Redistributing Microsoft Data Access Components" by Don Willits. Also, be sure to review the licensing terms associated with MDAC redistribution in the file entitled "MDACRedist.txt" located in the root directory of the SQL Server 7.0 installation image or the SQL Server 7.0 Service Pack 1 installation image.
Occasionally a special MDAC release will be shipped with a SQL Server 7.0 Service Pack. This is an interim release of MDAC wich installs the most recent MDAC release, then overlays that installation with new SQL Server specific files that have not been incorporated into MDAC releases. These special interim MDAC releases are provided in case a site needs to deploy fixes to the client components addressed in a service pack, for example, a file named called Sqlredis.exe shipped with SQL Server 7.0 Service Pack 1, which contains updated SQL Server client components. If your application is dependent on any of those fixes, you will want to redistribute this file instead of MDAC 2.1.2.4202.3 (GA). See the readme.txt file in the root directory of SQL Server 7.0 SP1 for more information on Sqlredis.exe. Any new release of MDAC will incorporate the new client components that shipped as part of the latest SQL Server 7.0 service pack.
The SQL Server 7.0 installation executable includes the capability to install only the SQL Server 7.0 "Client Connectivity" component when performing a custom interactive installation. This component installs many of the SQL Server specific components and utilities that an application might use to connect to SQL Server that are not installed by MDAC, including:
If your application depends upon one or more of these components, you will need to perform an unattended installation of the SQL Server 7.0 "Client Connectivity" components using the same approach detailed in Automating Database Server Installation with a custom setup initialization file that instructs the database server installation executable to install only the client components.
As discussed in Application Programming Interfaces, DB-Library is a legacy data access interface for Microsoft SQL Server that is supported by SQL Server 7.0 and MSDE 1.0 for backward compatibility to applications written for previous releases of SQL Server. The dynamic link libraries and utilities associated with DB-Library are only installed when performing a database server installation, or an installation of the SQL Server 7.0 client utilities.
The primary dynamic link library associated with DB-Library for Win32 operating systems is ntwdblib.dll. This file is redistributable. If you wish to redistribute this file instead of installling the SQL Server 7.0 "Client Connectivity" components discussed in the previous section, simply copy the version of this DLL that shipped with either SQL Server 7.0 or SQL Server 7.0 Service Pack 1 into the Windows system directory on the target workstation. Note that you must also install MDAC because ntwdblib.dll depends upon several other components installed by MDAC, such as the SQL Server 7.0 network libraries.
As discussed in Developing Multitiered Applications (Windows DNA), SQL Server 7.0 and MSDE 1.0 provide database services for multitiered applications. This type of architecture typically avoids direct connections from workstations to the database server, relying instead upon middle-tier business logic components to route database requests and send results back to clients.
Some multitiered applications will utilize browser clients, as discussed in Internet Browser Clients. Others will be Win32 applications that use DCOM to connect to middle-tier components hosted in Microsoft Transaction Server. Still others will use store-and-forward message queuing to communicate with the Microsoft Message Queue (MSMQ). None of these configurations require SQL Server client components or MDAC to be deployed on workstations.
However, many multitiered applications take advantage of the ease of use and powerful programming capabilities exposed by ADOs Remote Data Services. If your application uses this capability to pass disconnected rowsets back and forth between a middle-tier application server and a workstation, MDAC must be deployed on the client workstations to enable this capability.
The ability to have a local database engine for direct manipulation of data by a client workstation is a popular architecture for many distributed business applications. Examples include sales force automation, call tracking applications, service and diagnostic applications and more. In this scenario, either SQL Server 7.0 Desktop Edition or MSDE 1.0 is installed on the workstation.
A variety of software components associated with a workstation-based database server installation could be invoked as part of a distributed application. The most obvious of these would be the components required to establish a connection to SQL Server either locally or over a network, such as the SQL Server OLE DB Provider or the SQL Server ODBC Driver. Other components may be invoked if the workstation's databases are part of a larger distributed database application that utilizes replication services or distributed transactions.
The build number and service pack level of the database server installed on each workstation should be the same as that of the servers that they connect to. The procedures detailed in Automating Database Server Installation can be followed to install or upgrade a database server on a workstation, or to install a database server service pack on a workstation.
Both SQL Server 7.0 and MSDE 1.0 can be easily removed from a system using the following procedures:
start /wait isuninst.exe -a -y -m -fSQLPath\uninst.isu -CSQLPath\sqlsun.dll
Note When de-installation is complete, any non-system database files, backup files, and so on will not be deleted and will remain in the SQL Server data files directory.