Deploying and Maintaining Microsoft SQLServer 7.0 in an In-Store Environment

Contents

Introduction......

Infrastructure......

Network Infrastructure......

Network Architectures and Protocols......

WindowsNT Domain Models......

Single Domain Model......

Master Domain Model......

Multiple Master Domain Model......

Complete Trust Domain Model......

No Domain (Workgroup) Model......

Domain Models with a BDC in Each Store......

Recommended Domain Model......

Hardware Infrastructure......

Configuration......

Character Sets and Sort Order......

master and tempdb Databases......

Database Growth Parameters......

Logging......

SQLServer Configuration Parameters......

Implementation......

Deployment......

Remote Access Service......

Remote Control Software......

Software Distribution and Management Software......

Recommendations......

Operations......

Remote Administration......

Jobs......

Operators......

Alerts......

Recommended Centralized Administration Scenario......

Backup and Recovery......

Full Database Backup......

Differential Backup......

Transaction Log Backup......

Recommended Backup Scenario......

Recommended Recovery Scenario......

Database Maintenance......

Polling......

Snapshot Replication......

Merge Replication......

Transactional Replication......

Replication Implementation Details......

Recommended Replication Techniques......

Connected Stores......

Disconnected or Occasionally Connected Stores......

Pros and Cons for Replication as a Polling Solution......

Pros......

Cons......

bcp Utility......

Data Transformation Services (DTS)......

Two-Phase Commit......

Troubleshooting......

Support Infrastructure......

Automated Alerts......

Remote Monitoring......

Licensing......

Conclusion......

Finding More Information......

Introduction

Traditionally, in-store systems have been developed using proprietary, nonrelational database platforms. With the advent of strong competition in the retail industry, companies are forced to focus on increasing efficiencies within the store. These efficiencies translate directly into better customer service, greater throughput of customers, greater revenue, and increased availability of data for the corporate merchandising departments.

The database platform plays a critical role in accomplishing efficiencies through the use of technology within the in-store environment. The database needs to provide strong online transaction processing (OLTP) performance in a concurrent environment to handle the transactions during the day, and also strong decision support performance for end-of-day and reporting activities. In addition, the ability to automate and customize the transfer of data to corporate warehouses is mandatory.

Price/Performance ratios are also important factors in selecting an in-store database platform. Organizations need to consider the number of licenses required for supporting all of their stores and the performance required for achieving all of their business goals. Perhaps the most critical characteristic of an ideal in-store database platform is its ability to support automated and remote administration because each outlet in most organizations will not be staffed with technically oriented employees. Microsoft SQLServer version 7.0 delivers in all of these areas. It is a compelling choice for an in-store database platform.

This document is designed to provide the development and implementation teams of a retail organization with the information needed to deploy and maintain SQLServer 7.0 successfully in an in-store environment. General recommendations for the optimal solution are provided wherever applicable and various options are discussed in each section, along with the respective pros and cons. If any of the recommendations conflict with the specific business needs of an organization, they should be overridden for a more appropriate choice.

Infrastructure

Before SQLServer 7.0 is deployed, the network and hardware infrastructure within the store should be examined. This topic discusses the various options available within each infrastructure and provides some general recommendations. For the purposes of this topic and the rest of the document, it is assumed that the Microsoft WindowsNT version 4.0 operating system is the operating system used on both the server and the client.

Network Infrastructure

The issues discussed in this topic include network architecture and protocols, and the WindowsNT domain model.

Network Architectures and Protocols

  • Ethernet is the most popular LAN architecture in use today and is a good choice for an in-store LAN because it provides good performance with low startup and administrative costs.
  • Fast Ethernet implemented on Unshielded Twisted Pair Category 5 cabling is a good solution for increasing in-store performance significantly (to 100 Mbps) at a reasonable cost.
  • Analyze the network for points of failure. Fault tolerance should be built into the architecture to eliminate single failure points whenever accommodated by the budget.
  • All in-store communication, as well as Internet/WAN access, should use the TCP/IP protocol. This protocol is supported on most operating systems and is considered a universal standard for internetwork connectivity. It also allows the in-store computers to support Microsoft Internet Information Services (IIS), HTTP, FTP, SMTP, and streaming media applications.
  • Static IP addressing is preferred over Dynamic Host Configuration Protocol (or DHCP) for the distribution of IP addresses to devices in a small in-store environment because this reduces the administration workload significantly. However, in-store environments with a larger number of in-store devices would prefer using DHCP for distribution of IP addresses to static IP addressing due to ease of administration. Windows Internet Name Service (WINS) and Domain Name Service (DNS) should be implemented to provide name resolution services.
  • In general, standardize on as few network protocols as possible within the store in order to maximize communication efficiency.

WindowsNT Domain Models

Selecting the proper domain model is critical to the overall network performance of the organization. This topic discusses the issues involved in implementing WindowsNT Server version 4.x domains. Microsoft Windows 2000 Server should scale much higher than the limits specified in supporting larger domains. Furthermore, the limits specified in this topic represent recommended limits as opposed to hard and fast rules for implementation.

Single Domain Model
  • Consists of one domain for the entire organization. While this centralizes account and resource management, thus maximizing ease of administration, it limits the number of accounts to approximately 40,000 (the recommended Windows NT 4.x domain limit).
  • Requires the domain controllers to have additional processing power due to the increased workload.
  • Requires the least amount of administration among all of the domain models because there are no trusts to be maintained.
Master Domain Model
  • User accounts are managed centrally in a master domain while decentralized resource domains manage the resources. The number of accounts is limited to approximately 40,000.
  • Domain controller in the master domain will need additional processing power.
  • Each resource trusts the master domain (one way trust); therefore, administration cost is more than the single domain model, but less than the other domain models.
Multiple Master Domain Model
  • Similar to the master domain model except that multiple master domains are allowed. Therefore, this model supports a virtually unlimited number of accounts.
  • Domain controllers in the multiple master domains share the workload of managing the accounts.
  • More trusts to manage than the master domain model; therefore, higher centralized administration costs are incurred.
Complete Trust Domain Model
  • Decentralized account and resource management. Supports an unlimited number of users.
  • Unlike the other three models, domain specific account policies are supported.
  • Highest administration costs due to the large number of trusts involved. This is the least desirable model in an enterprise environment and is suited more for a departmental implementation.
  • No performance penalties are involved due to localized authentication for accounts and resources.
No Domain (Workgroup) Model
  • There are no domains; each store has its own workgroup instead.
  • Decentralized account and resource management. Supports an unlimited number of users.
  • Corporate administrators have to log on to each workgroup individually to apply changes; therefore, the administration costs can be high if all the workgroups need the same changes.
  • No performance penalties are involved due to localized authentication for accounts and resources.
Domain Models with a BDC in Each Store
  • In order to avoid the performance penalty of incurring a WAN round trip for each authentication request, the domain models listed earlier require the configuration of the in-store server as a Backup Domain Controller (BDC).
  • Configuring the in-store server as a BDC adds some overhead (based on the size of the account database) because it needs to synchronize the security accounts database with the Primary Domain Controller (PDC) on a regular basis across a WAN or dial-up link.
  • Configuring each in-store server as a BDC does not support disconnected or occasionally connected stores well. Upon restoration of the link to corporate, the workload on the in-store servers can be large because the accounts database has to be synchronized in addition to sending transactions and receiving lookup table information. Performance could be affected on slow dial-up links.
Recommended Domain Model
  • The workgroup model (or the no domain model) is the preferred choice for in-store environments that involve only a small number of client devices, primarily due to its low cost and decreased maintenance complexity.
  • This model does incur high administration costs when changes have to be distributed throughout all the stores. It is imperative that the account and resource setup be planned prior to deploying workgroups to the stores so that the number of future changes is minimized. Generic logins should be used to anticipate future in-store usage because the primary user-based security will be handled by the in-store front-end application.
  • Except for the cases where changes need to be distributed to all the stores, the no domain model incurs extremely low administration costs because there are no trusts to manage and each store operates within its own workgroup.
  • In organizations with a larger number of in-store devices or a dynamic account list, a workgroup model may increase administration costs; hence, a domain model is preferred. In these cases, the single/multi master domain model provides the centralized account management and decentralized resource management best suited for such an in-store environment. The master domain would reside in the corporate location, and each store would contain a server configured as a backup domain controller.

Hardware Infrastructure

  • Memory and processor speed on the server should be maximized based on the budget. They also depend on the number and nature of applications running on the in-store server.
  • If the budget accommodates it, implement hardware (or software) RAID Level 1 (disk mirroring) or RAID Level 5 (disk striping with parity) to prevent single point of failure in the disk drives, and to maximize the performance of the system. Mirroring provides better write performance (which is critical for OLTP applications) whereas disk striping provides better read performance (which is critical for Decision Support Systems (DSS) applications). However, striping requires a minimum of three drives to implement versus two for mirroring. In general, mirroring is the preferred fault tolerance solution for an in-store system. Although implementing RAID is highly recommended, it is not required for SQLServer and it may not be practical for many retailers because it adds to the per-store costs significantly.
  • Invest in the fastest disk drives supported by the server because they are always the bottleneck in any computer configuration. The different versions of the SCSI interface standard (SCSI, SCSI-2, FAST SCSI-2, WIDE SCSI-2) provide superior data transfer performance compared to the disk drives using the IDE interface. FAST SCSI-2 is the most popular choice on modern servers, but may have a higher cost associated with it compared to the IDE drives.

For more information about hardware configurations for SQLServer computers, see the Compaq white papers listed in “Finding More Information.”

Configuration

There are several configuration options that need to be set in SQLServer 7.0 during and after the installation process in order to maximize the database performance. This section details the configuration options and their respective settings that maximize performance and minimize ongoing administration.

Character Sets and Sort Order

Character sets, also known as code pages, control the way character-based data is stored internally in SQLServer. Each character set consists of 256 characters (128 standard printable characters followed by 128 language-specific letters and symbols). Sort orders control the collation and presentation of data in response to database queries. A sort order determines how character data is compared, and in what sequence it is returned, from a query. Both of these configurations are determined during the installation process.

Alternately, the Unicode standard for character representation handles 65,536 characters and covers virtually every character in use today in the different alphabet sets. This representation takes up twice the storage as a character, but provides support for storage of different alphabets such as the European alphabet or Japanese Kanji. Generally, language independence can be provided by the front-end application and the database should continue to store the data in a single character set representation. The Unicode standard should be used if the application involves storage of internationalized data. If the Unicode standard is selected, a Unicode Collation method must also be provided during the installation process.

  • Character set should be used, instead of Unicode standard, as the data representation method to optimize storage and performance, provided there are no requirements to store internationalized data. The Unicode standard should be used if such requirements exist.
  • Standardize on the same character set and sort order throughout all of the SQLServer installations in the organization in order to facilitate direct data transfer between servers.
  • The default character set (1252 ISO or ANSI) and sort order (case-insensitive dictionary sort order) work well and should be used unless they conflict with specific business needs.
  • Do not use character sets to adhere to the language requirements; that should be controlled by the front-end applications and not by the database.

master and tempdb Databases

The master database is a system database that stores information about all of the other databases, as well as system configurations and all login accounts. The tempdb database is a system database that provides temporary storage for user transactions.

  • During installation, the size of the master database defaults to 7.5 MB and the size of the tempdb database defaults to 8 MB. If the number of user databases and login accounts is limited, the default size for the master database should be sufficient. It is a good idea, however, to increase the size of the master database to account for future growth and avoid the expansion penalty (explained in the next topic).

  • If the application uses temporary tables, either by explicitly creating them or by using the ORDER BY and GROUP BY clauses on large result sets, the initial size of tempdb should be increased appropriately to avoid the expansion penalty. The average amount of space used by each tempdb operation, multiplied by the average number of concurrent users, is a good approximation of the ideal size of tempdb. Calculate the space used in a tempdb operation by multiplying the number of rows being loaded into temporary tables by the record size.
  • Given the limited amount of RAM available on the in-store servers, and the limited number of users in an in-store environment, it is best to avoid creating tempdb in RAM. RAM is best used by assigning it to SQLServer and using it primarily for data and procedure caching.

Database Growth Parameters

SQLServer 7.0 automatically expands the size of a database as required during operation. Either a percentage of the existing size or a fixed number of megabytes, as specified by the administrator, controls the amount of expansion. While this feature significantly reduces ongoing maintenance, it creates a performance penalty if the database is expanded frequently Specify a larger initial database size instead of accepting the default (1 MB) when creating the user database. Approximate the size based on minimum anticipated amount of data storage.

  • After the initial size of the database is filled, specify a number of megabytes (or a percentage) by which to expand. Do not accept the defaults (10 percent of the existing size, or 10 MB). The reasons for the initial size filling up must be analyzed prior to making this decision.

Logging

In-store systems require up-to-the-minute recovery, for example, until the last committed transaction. This functionality is provided in SQLServer 7.0 through transaction logging.

  • The database configuration option truncate log on checkpoint should be set to False so that the transactions can be retained in the log file, which can then be backed up periodically.
  • The transaction log should be backed up periodically to facilitate better recovery options.
  • Furthermore, if any nonlogged operations such as SELECT INTO or bcp are performed on the database, differential backups must be done immediately to protect those transactions. For information about the preferred backup strategy, see “Operations.”
  • The initial size of the transaction log file should be approximated using the number of transactions estimated between each time the log file is backed up. This avoids the penalty associated with constantly expanding the log file when the default of 1 MB is accepted.

SQLServer Configuration Parameters

SQLServer 7.0 allows users to determine the values for a number of configuration parameters that drive its overall performance. Although this level of tuning may be required for installations involving Very Large Databases (VLDs) running on high-end servers with large disk subsystems, most in-store installations do not require it. The default parameter values work well for in-store databases and should be maintained on all of the store servers. For more information about setting configuration parameters, see the white paper titled “Microsoft SQLServer 7.0 Performance Tuning Guide” at

  • max async IO: The default value of 32 is sufficient for in-store systems. The overall goal is to control the speed of “checkpointing” the database, for example, writing modified data or log pages from the buffer cache to the disk subsystem and also truncating the inactive portion of the log.
  • max worker threads: The default value of 255 is sufficient for in-store systems. This controls the number of Windows threads used by SQLServer to service the commands being submitted to the database at a given time.
  • Boost SQLServer priority on WindowsNT: This allows SQLServer to run at a higher priority than other WindowsNT processes and should be used only if the computer is dedicated to SQLServer. The default value is zero, which forces SQLServer to run at the same priority as the other processes.
  • In general, in-store servers should take advantage of the autotuning and autoconfiguration features built into SQLServer 7.0 and not change the default configuration parameter values.

This topic outlines the database design and implementation issues that affect performance, administration, and the ability to troubleshoot from a remote location. These are general recommendations and should not serve to override any conflicting business requirements.