This section covers the installation of SQL Server in your customer's environment. The first section presents techniques for embedding SQL Server installation within your own application's installation process. The remaining topics are presented to help the process of integrating SQL Server and your application go as smoothly as possible for the end user. These issues are common to any SQL Server installation, whether you manage its installation or not, such as:
Because in some cases it may be undesirable for the end user to install SQL Server using the Setup program, it is often a good idea to embed SQL Server's installation in your application's setup routine. Instead of the end user having to respond to prompts from the Setup program, you can create an initialization file that contains the settings for each installation option. After this file is created, you can then start SQL Server setup with command-line options that enable it to run in unattended mode to configure the new installation using the settings specified in the initialization file. This is called unattended setup. Unattended setup makes it possible to install SQL Server without the end user being aware of its existence. Unattended setup supports local installation only; it does not permit remote server installation.
To incorporate an unattended SQL Server installation into your application:
This procedure provides a solid installation process for standard and simplified installation of a vertical application and SQL Server.
Creating a setup initialization file is the first step to incorporating unattended SQL Server installation in your application's installation process. The setup initialization file contains the information that is used to set installation configuration parameters when SQL Server is installed. You can create a setup initialization file using any text editor. The setup initialization file is divided into sections that correspond to the Setup program's information prompts. You can enter this information into the file manually or use the WritePrivateProfileString Microsoft Win32® API function to write the information to the file for you. For example:
/* Assume lpszKeyValue is the user's name string from your application's installation interface*/
LSTR lpszKeyValue
// Needs to be written to [License]FullName in SETUP.INI
WritePrivateProfileString("License", "FullName", lpszKeyValue, "SETUP.INI")
The lpszKeyValue variable contains a string corresponding to the username that is retrieved during the installation of your application. If you do not prompt for a username during the installation process, you can hard-code any name that you want.
Some sections of the setup initialization file can have a significant impact on the installation process. A brief overview for each section follows. These sections are:
For more information about the setup options, see SQL Server Books Online or Microsoft SQL Server Setup.
This section corresponds to the SQL Server Setup information prompts for the name of the licensed user and organization, the licensing mode, and the number of users. If you prompt for a username for licensing purposes during the installation process, you can use it for the FullName entry; otherwise, you can supply any string. The Mode entry refers to the SQL Server Licensing Mode you are using for SQL Server. The PerServerUsers number corresponds to the number of users licensed to use SQL Server in Per Server Mode. The example [License] section is taken from the Setupini sample application's Setup.ini file:
[License]
FullName=XYZ Automated Install
OrgName=Unknown
ProductID=0000000000
Mode=0
PerServerUsers=20
The example indicates that SQL Server will be installed in Per Server Mode with 20 licensed users.
In this section, you configure SQL Server and the SQLExecutive service to start automatically each time the server computer is restarted. You can also install SQL Server Books Online. Because you are performing SQL Server administration in your application, you must ensure SQL Server and its related services are running during business hours. The best approach for performing this task is to configure SQL Server and the SQLExecutive service to autostart when the computer is restarted.
To minimize administration overhead, both SQL Server and the SQLExecutive service should run using the LocalSystem Windows NT account, unless you use replication with your application. Replication requires that SQL Server and the SQLExecutive service run using an administrative account. This configuration ensures that SQL Server will always be running after the computer has restarted, unless it is manually stopped. The sample is taken from the Setupini sample application's Setup.ini file:
[NewOptions]
AutoServerService=CHECKED
AutoExecutiveService=CHECKED
BooksOnline=3
The sample shows that both SQL Server and the SQLExecutive service will start automatically when the server computer restarts. The BooksOnline value of 3 indicates that the SQL Server Books Online will not be installed.
These sections refer to the SQL Server character set and sort order options. These options are dependent on the needs of your application. Choose these options wisely because only one sort order and character set can be installed. The default character set and sort order values work well in most cases. The default SQL Server character set is ANSI (ISO 8859-1), and the default sort order is "Dictionary order case insensitive" (nocase.iso). Use nondefault sort orders or characters sets only if your application requirements cannot be satisfied with the defaults. For example, case-sensitive searches cannot be performed using the default sort order; therefore, you have to install a case-sensitive sort order instead. International and localization issues may require you to install a nondefault sort order and character set as well.
The examples are taken from the Setupini sample application's Setup.ini file:
[CharSet]
CharSet=cpISO
[SortOrder]
SortFileName=nocase.iso
SortConfigValue=52
The examples demonstrate the format for these sections using the default character set and sort order values. In the [Charset] section you specify the name character set only, using the values specified in the documentation. In the [SortOrder] section you specify the name of the sort file that will be installed and its corresponding sort configuration value.
This section corresponds to the Setup program's information prompt for the SQL Server network library options. SQL Server's network libraries pass network packets back and forth between clients and a SQL Server. Using specific interprocess communication (IPC) mechanism protocols, these network libraries perform the network operations required to communicate and to enable simultaneous connections from multiple clients. For managing communication with client connections, SQL Server can listen on multiple network libraries or one at a time. Table 1 lists the major network libraries supplied by SQL Server.
Table 1. SQL Server Network Libraries
| Network Library | Features | Comments |
| Named pipes | Integrated security Local connections Fast |
The default and the simplest |
| TCP/IP Sockets | Fastest Uses WINS or DHCP |
May be the favorite in corporate networks |
| IPX/SPX | Uses Novell bindery for name resolution | Use in Novell shops with DOS and Windows 3.x clients |
| Multiprotocol | Integrated security Encryption |
Simpler, more features, but slower than others |
A named pipe is the default network library, and SQL Server listens on the standard pipe \\computer name\pipe\sql\query. You can change the pipe name SQL Server listens to or drop support for named pipes and set SQL Server to listen only on the other network libraries. Named pipes works best in most single SQL Server sites because of its ease of use and good performance. It is also the network library of choice for installations using integrated security. If your end user's environment involves multiple SQL Servers or sites, choose all of the network libraries that are applicable and accept the default parameters.
The example is taken from the Setupini sample application's Setup.ini file:
[Network]
NetLibList={"SSNMPN60","SSMSRP60", "SSMSSP60", "SSMSSO60", "SSmSSAD60"}
ServerNMPipe=\\.\pipe\sql\query
MultiProtEncrypt=NOTCHECKED
SPXServiceName=SPXName
TCPIPSocketNumber=1433
VinesStreetName=VinesName
ADSPObjectName=ADSPName
DecNetObjectID=DecID
The example shows how you can install all of the network libraries SQL Server supports and the configuration information for each one. The default named pipe and TCP/IP socket are used. For more information about SQL Server network libraries and their options, see SQL Server Books Online or Microsoft SQL Server Setup.
Based on your application's requirements, you may have to run custom installation scripts while installing SQL Server and your product. These scripts can be used to perform tasks such as:
You can execute your own custom scripts during the SQL Server installation process. In the [Scripts] section of the setup initialization files, specify the name and paths of the files to be installed. Using this option, you can seamlessly integrate creating your application's custom object into the installation process. The example shows how the custom script Test.sql can be executed during the SQL Server installation process:
[Scripts]
CustScPath=c:\install
CustomScriptList={"test.sql"}
ForceReboot=FALSE
The example specifies the path and name of the file, and also indicates that SQL Server does not have to be rebooted after the script has been completed.
To perform an unattended installation, run setup from the command line using this syntax:
Setup /t IniFilePath = filename
where filename is a fully qualified file name including the path (for example, C:\Sql\Setup.ini) of the setup initialization file to use for the unattended installation. The path can be a local path or one redirected to a network drive, but it cannot be a UNC name. The example, taken from the Setupini sample application, demonstrates this concept:
' The following line starts SQL Setup using the modified Setup.ini file as
' input. For demonstration purposes, it runs SQL Server Setup from the
' default installed location rather than from a distribution CD.
SQLCmd$ = "c:\mssql\binn\setup.exe /tIniFilePath = .\setup.ini "
ExecCmd SQLCmd$
The example uses the Visual Basic ExecCmd to execute SQL Server setup using the flag for unattended setup. The example assumes that SQL Server Setup program resides in its default installed location assigned for demonstration purposes. You can change this sample to reflect the drive and path information for a distribution CD, as would normally happen during your application's installation process.
SQL Server has very robust security options that allow you protect the server and the data that is stored on it. You can determine who can log on to the server, their administrative status, and what database(s) they have access to. Each SQL Server must be configured to use one of the three available security modes:
With one of these options, you control access to SQL Server in a way that best fits the needs of your application and the end user's environment. This section provides a brief overview for each login security option, as well as information about trusted connections and configuring the security mode.
Standard security is SQL Server's default login security mode. Standard security uses SQL Server's own login validation process for all connections. Standard security has no coordination with Windows NT accounts or passwords. Each SQL Server user connection must provide a valid login ID and password when it connects to the SQL Server. When a login attempt is made, SQL Server checks the syslogins table in the master database for the user's login ID and password. If the login ID and password are valid, the user is connected to the SQL Server.
Standard security is most useful in application environments where one or more of the following conditions apply:
For more information about implementing standard security, see SQL Server Books Online or the Microsoft SQL Server Administrator's Companion.
Integrated security links SQL Server login security to Windows NT security. Using this security mode, SQL Server relies on Windows NT authentication of the client at logon time. This allows a network user to log in to SQL Server without supplying a separate login ID or password. Users can maintain one login ID and password for both Windows NT and SQL Server. You can incorporate Windows NT security functionality such as secure password handling, password change rules, and password expiration into your application environment.
SQL Server achieves login security integration with Windows NT by using the Windows NT security attributes of a network user. A user's network security attributes are established at network logon time and are validated by Windows NT through a sophisticated password encryption mechanism. When a network user tries to connect to SQL Server, SQL Server uses Windows NT facilities to verify that the network username has been validated on the network. SQL Server then permits or denies login access based on the network username alone; a separate login name and password are not required in the client connection request.
Login security integration operates over network protocols that support authenticated connections between clients and servers. These connections are referred to as trusted connections. Trusted connections include Multi-Protocol and named pipes sessions from other Windows NT-based workstations, from Windows for Workgroups-based clients, from Windows 95 clients, and from LAN Manager clients running under the Microsoft Windows or MS-DOS® operating systems. Using the Multi-Protocol network library, trusted connections can also be made over the NWLink IPX/SPX protocol from Windows 3.1x clients using Novell software.
When a user logs in to SQL Server over a trusted connection, the user is mapped to a SQL Server login ID, to a default SQL Server login ID (usually called guest), or to SA if the user has been granted SQL Server administrative privileges. After the login process is complete, all other SQL Server security checks are handled as usual. Access to individual SQL server tables, for example, is managed through permissions granted within a SQL Server database.
Windows NT integrated security is useful in application environments where one or more of the following conditions are applicable:
When using integrated security, you must implement in your application the ability to add and drop user accounts to both the network and SQL Server by using your own user management interface. You can use the xp_grantlogin or xp_revokelogin extended stored procedures to grant or revoke SQL Server login access to Windows NT-based users or groups. Using these extended stored procedures in your application, you manage user validation to SQL Server. The end user does not have to perform it manually by using the SQL Security Manager or by calling these extended stored procedures using isql.
The group or user must already be defined in Windows NT to use xp_grantlogin. If the group or username does not include a domain name and a backslash (\), Windows NT initially searches for a matching user on the local computer, then on the primary domain controller, and then on trusted domains. The example demonstrates the use of the xp_grantlogin extended stored procedure:
xp_grantlogin 'sqlusers', 'user'
This example grants SQL Server access to the sqlusers group and gives the group user privileges to SQL Server. You can also change accounts from one privilege level to another by executing xp_grantlogin.
xp_revokelogin revokes SQL Server access from a Windows NT-based group or user. If the group or username does not include a domain name and a backslash (\), Windows NT initially searches for a matching user on the local computer, then on the primary domain controller, and then on trusted domains. You can revoke permission only on an account that has been explicitly granted permission through xp_grantlogin. The example demonstrates the use of the xp_revokelogin extended stored procedure:
xp_revokelogin 'sqlusers'
This example revokes SQL Server access from the sqlusers group.
Windows NT integrated security can help you create a powerful and secure application environment for your end users. For more information on implementing integrated security, see SQL Server Books Online or the Microsoft SQL Server Administrator's Companion.
Mixed security allows user login requests to be validated using either integrated or standard security methods. Both trusted and non-trusted connections, as used by integrated security and standard security respectively, are supported. When SQL Server's login mode is set to mixed and a user attempts to log in to the server over a trusted connection using Multiprotocol or named pipes, SQL Server examines the login name:
For more information on mixed integrated security, see SQL Server Books Online or the Microsoft SQL Server Administrator's Companion.
ODBC and DB-Library applications can be configured to always use trusted connections regardless of SQL Server's login security mode. This feature allows the SQLExecutive service to connect to remote servers as long as it is running under a Windows NT account that has been granted SA access to SQL Server. This feature forces a trusted connection, but the appropriate user-access privileges must exist if the login is to succeed. The example uses the ODBC API to demonstrate the trusted connection concept:
// Declare the variables for the environment and connection handles and // the return code.
HENV henv; // Environment handle
HDBC hdbc; // Connection handle
RETCODE retcode;
retcode = SQLAllocEnv(&henv); /* Environment handle */
if (retcode == SQL_SUCCESS) {
retcode = SQLAllocConnect(henv, &hdbc); /* Connection handle */
if (retcode == SQL_SUCCESS) {
/* Configure the connection to be trusted.*/
SQLSetConnectOption(hdbc, SQL_INTEGRATED_SECURITY, SQL_IS_ON);
/* Connect to data source.*/
retcode = SQLConnect(hdbc, "EmpData", SQL_NTS, "JohnS", SQL_NTS, "Sesame", SQL_NTS);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
/* Process data after successful connection.*/
.
.
.
The example allocates environment and connection handles, sets the trusted connection option using the SQL_INTEGRATED_SECURITY option and the SQL_IS_ON flag, and makes the connection to the "EmpData" SQL Server data source. The code requests a trusted connection to SQL Server regardless of the server's security mode. By default, SQL Server ODBC options are not trusted, so this option must be set for each connection handle before a connection is made using it.
For more information about the SQL Server login security mode and trusted connections, see SQL Server Books Online, Microsoft SQL Server Programming ODBC for SQL Server, or Microsoft SQL Server Programming DB-Library for SQL Server.
The end user can use the SQL Server Setup information prompts during installation to choose the login mode, or you can configure it manually by embedding SQL Server installation in your application's installation process. The login security mode cannot be configured using the setup initialization file. Therefore, if you install SQL Server using the setup initialization file, SQL Server is configured to use standard security.
SQL Server's security mode is stored as the LoginMode value in the Windows NT Registry as part of the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer key. If you use a setup initialization file to install SQL Server, you must manually change this Registry value to configure SQL Server to use either integrated or mixed security. The valid values for this registry key are 0,1, and 2 (corresponding to standard, integrated, and mixed security). You can manually change this key by using the RegSetValueEx Win32 API function.
For more information, see the Win32 API Programmer's Reference Guide.
Minimizing your application's footprint reduces the free-disk space that an end user must have to install and use your product and SQL Server. After installation, there are several directories and files that can be removed to reduce the amount of disk space that SQL Server uses. By default, SQL Server requires approximately 90 MB. You can reduce your application's footprint by doing some or all of the following:
Whether you require the end user to install SQL Server or you embed the installation in your own installation process, you must eventually create database(s) and database objects. Also, you may need to import initial application-specific table data for a new installation of your application.
During the development and testing of your application, you determine the initial database and device size for a new application installation. While installing the application, you must use these predetermined sizes when you create the database(s) and the corresponding database devices you need. There are two ways to perform this task:
Choose the method that fits the needs of your application's installation architecture.
Option A requires you to use either the isql command-line interface or the setup initialization file to create these items. The command-line utility is most often used when you are not embedding SQL Server's installation in your application's installation routine. Both methods in Option A require you to run a script that creates your application's database(s) and devices after SQL Server has been installed. The script must contain the appropriate DISK INIT statement for each database device that you want to create and the CREATE DATABASE statement for each database. The example demonstrates using of the isql command-line utility to perform this task:
Isql –SmyServer –E –ic:\install\createDvDB.sql –oc:\install\createDvDb.out
In the example, isql executes with the –E option to log on to the SQL Server myServer as an administrator using a trusted connection. The –i option indicates the location of the script that is to be run, and the –o option indicates the location of the output for the script.
For more information about the isql command-line utility, see SQL Server Books Online or the Microsoft SQL Server Administrator's Companion.
Option B does not require scripts for creating the database(s) or database device(s). Instead, it uses the SQL Server Distributed Management Objects (SQL-DMO) API function calls to create these items. SQL-DMO is a set of 32-bit COM objects for the Windows 95 or Windows NT operating systems. These objects, collections, methods, and properties are used to write scripts and programs that can administer a single or multiple SQL Servers. The example demonstrates using SQL-DMO to implement this task:
Public Function CreateDevice(DevName As String, DevPath As String, DevSize As Integer) As Boolean
On Error GoTo CreateDeviceError
'Create a SQL-DMO Database Device Object.
Dim DbDevice As New sqlole.Device
' Fill in the property information for the new Database Device using the
' SQL-DMO device object properties.
'Assign a name using the .Name property.
DbDevice.Name = DevName
'Assign the device path.
DbDevice.PhysicalLocation = DevPath
'Mark the device as a database device.
DbDevice.Type = SQLOLEDevice_Database
'Assign a size.
DbDevice.Size = DevSize
' The following code creates a device on the LocalServer using the
' .Devices.Add method for the SQL-DMO DbDevice object defined above.
LocalServer.Devices.Add DbDevice
CreateDevice = True
Exit Function
.
.
.
' Get the drive and path information the user has entered from the drop
' down lists on the screen, and then create the database device for the
' XYZ database.
DevPath$ = drivecombo(0).List(drivecombo(0).ListIndex) + xyzdbpath_list.Text + "\xyzdb.dat"
Succeed = CreateDevice("xyzdb", DevPath$, 5)
.
.
.The first section of the example presents the function definition for the CreateDevice() taken from the Setupini sample application. This function uses SQL-DMO to create a database device using the name, path, and sizing information passed into it as parameters. The function performs these tasks:
The last section shows calls to create a 5-MB database device called "xyzdb". The path and drive information passed to the function for this device is retrieved from the Setupini application's user interface. Both of these options are extremely flexible and efficient. Choose the method that fits your application's needs and architecture.
For more information about SQL-DMO, see SQL Server Books Online or Microsoft SQL Server Programming SQL Distributed Management Objects.
The same options that are available for creating database devices and databases can be used to create tables, indexes, stored procedures, views, triggers, and so on. If you use unattended setup, you can run the scripts necessary to create these objects by using either the [Scripts] section or the isql command-line utility. If you are not embedding SQL Server's installation in your application, you can use the isql command-line utility or SQL-DMO to create these objects. The method you choose depends on the requirements and architecture of your application's installation routine.
After you have created the database objects, you may also need to import data into the application's database(s). There are multiple options available if you must import data as part of a new installation of your application:
The example demonstrates the use of bcp to import data:
BCP xyzdb.dbo.importtab in c:\imptab.dat /c /o c:\imptab.out /SmyServera /Usa /Pdba
In the example, bcp is used to connect to the SQL Server myServer using the SA account and password. The utility is shown using its character mode facilities to import a tab-delimited text file, Imptab.dat, into the importtab table in the xyzdb database.
For more information about the bcp command-line utility, see SQL Server Books Online or the Microsoft SQL Server Administrator's Companion.
After SQL Server is installed, the next step is to perform some basic system configuration tasks. Because your application is managing SQL Server in the end user's environment, you must adjust SQL Server's configuration settings to address the requirements of both the application and the end user.
SQL Server's default configuration settings are designed to address the needs of a variety of customer environments and applications. However, in some instances it may be necessary to adjust some of these configuration settings to satisfy the specific requirements of your application and the end user's environment. The example demonstrates how you can adjust SQL Server's configuration settings during your application's installation process:
Public Function ConfigureServer(Users As Integer) As Boolean
' Install function error handling.
On Error GoTo ConfigureServerError
' Define handle to SQL Server's configuration block.
Dim config As sqlole.Configuration
' Define temporary variable for a configuration setting.
Dim cvalue As sqlole.ConfigValue
.
.
.
' Define variables to hold the original SQL Server locks, open objects,
' and user connections configuration setting values.
Dim OrigUsers As Long, OrigLocks As Long, OrigOpenObjects As Long
' Define variable used to hold flag indicating whether SQL Server is or
' not running.
Dim ServerRunning As Boolean
' Assumed coming in to this function. Server must be running to configure it.
ServerRunning = True.
.
.
.
' Get the original user connections, locks, and open objects configuration
' settings to revert to in case of problems, and then adjust these settings
' using new values.
Set cvalue = config.ConfigValues("user connections")
OrigUsers = cvalue.CurrentValue
' Passed in as a parameter to the function
cvalue.CurrentValue = Users
' Adjust Locks and Open Objects to the needs of the application.
Set cvalue = config.ConfigValues("locks")
OrigLocks = cvalue.CurrentValue
' Predetermined application value
cvalue.CurrentValue = 5500
Set cvalue = config.ConfigValues("open objects")
OrigOpenObjects = cvalue.CurrentValue
' Predetermined application value
cvalue.CurrentValue = 100
.
.
.
The example uses SQL-DMO functionality to adjust the user connections, open objects, and lock SQL Server configuration settings based on the needs of the sample application. First, the sample defines the variables OrigUsers, OrigLocks, and OrigOpenObjects for holding the present SQL Server configuration settings. These values are needed to restore the settings to their original values in the event a problem is encountered during the configuration adjustment process. Then, for each one of the target configuration values, the sample performs the following tasks in order:
You can use the same methods for adjusting any of the other SQL Server configuration options based on the needs of your application.